texts:
Does anyone have a good link to documentation regarding how the syshobtcolumns and syshobt system tables are populated.  The reason for asking is that we are receiving pagelatch_sh and pageliolatch_ex blocking on tempdb.sys.syshobtcolumns.clust (lead blocker).  I realise that the tables are undocumented.

This is caused by a very poor sharepoint query which does a lot of work in the tempdb.  The query is generated by the sharepoint API's.  I understand that our developers can make alterations to the query to reduce the amount of work which it performs.  Lazy coding means that hundreds of irrelevant columns and rows are being returned.

I'm interested to find out how this system table is being blocked.  The query generates a table variable, inserts a bunch of rows and then performs a god awefull query against the data involving spooling etc.  I would attach this but the query is over 60000 chars.  I know that this is causing the blocking but understanding the syshobtcolumns system table will enable me to determine if it is these sharepoint queries causing issues or if we have a more serious issue with our configuration(s).

I have identified 6 sharepoint queries for the developers to look into reducing the number of rows and columns returned.  All are called throughout the day and all have similar workloads in the tempdb.  The blocking and subsequent slow down of all queries on the sharepoint SQL instance occur when the CheckDB weekly job executes.  We see an increase in disk waits during this period once a week.  We are yet to determine if we have exhibiting a tipping point on the SAN and we are effectively hitting a bottle neck when our maintenance tasks execute at the same time of these I/O intensive queries.

We most certainly need to improve these sharepoint queries to reduce contention, however the issues occur at regular times when the maintenance task (checkdb full) executes.

Our setup is on a DL580 a 16 core server, 4 quads, 32Gb ram, 3par san, tempdb has 8 data files, sql server 2005 SP3 cu8 x64 bit enterprise, windows server 2003 R2 x64 bit enterprise, separate luns for data, logs, tempdb etc.  Performance monitoring through SQL Sentry (lead blocker found here along with intensive queries and disk waits).


texts: How can I import/export a MySQL database without redirection?

texts: I've been told that redirection via shell (e.g. < and > ) can (not always) affect the collation of a database (.e.g utf8). How can I import an export without redirecting so that I don't have to worry about this.


texts: Database schema - performance v correctness information

texts:
I have a database that stores information about the users of my website (username, password, etc). I also store information about their activity on the site.

For example, I have a large number of questions on my site. I have a few topics, and each topic has a number of questions.

Whenever a user answers a question, I store the result in a table containing the tuples: (user_id, question_id, timesAnswered, timesCorrect)

I also have a table called topics_and_questions containing (question_id(PK), topic_id(PK)) which can be used to determine which topic(s) the question belongs to.

Based on the above, it is possible to determine how many questions a user has answered on a particular topic. However, this would involve searching through the first table for all question_ids answered by the user, then searching the topics_and_questions table. (The first table could get rather large)

Does it make sense to instead create another table users_and_topics which contains (user_ID, topic_id, questionsAnswered) and update this whenever a question is answered? It would seem as though this would be bad database practise as data would essentially be replicated (this table can be derived from current tables). However, it could provide performance benefits as it would mean less work for the server. I would expect around 1 request for this information every 5 minutes per user of my website.

I'm using innoDB, MySQL database if it matters


texts: Function to return value dependant on seconds

texts:
I am trying to create a function to return value from column dependant on seconds.
-- Function: ead_credit_within_sla(character, integer, integer)
-- DROP FUNCTION ead_credit_within_sla(character, integer, integer);

--CREATE OR REPLACE FUNCTION ead_credit_within_sla(
    "hours_past_sla$" character,
    "seconds_from$" integer,
    "seconds_to$" integer)
  RETURNS character AS
$BODY$

SELECT
    credit_due_%
FROM
    odwh_system.ead_charge_rate
WHERE
    hours_past_sla = hours_past_sla$
precision)::integer;

Not sure what I need to add in the where clause to ensure if seconds fall between seconds_from and seconds_to then return credit_due_%?
odwh_system.ead_charge_rate table:


texts: Why does CURRENT_USER() show the root@::1 when I login to MySQL?

texts:
I've set 3 different passwords for the following accounts: 'root'@'localhost', 'root'@'127.0.0.1' and 'root'@'::1'.
By typing: mysql -u root -p I can only login with my 'root'@'::1' account password and CURRENT_USER() returns root::1. Why is that so? How can I login with the password that I set for the 'root'@'localhost' account?


texts:
I have a stored procedure which is called by Console Application (C#), the sp do nothing much just checking the existing of the record and update it:

    ALTER PROCEDURE [dbo].[sp_Update_StaffTable]
        @OldStaffId nvarchar(100)
       ,@NewStaffId nvarchar(100)
       ,@FirstName  nvarchar(100)
       ,@LastName nvarchar(100)
       ,@Description nvarchar(100)
       ,@WorkPhone nvarchar(100)
       ,@Photo varbinary(MAX)
       ,@UserType nvarchar(100)
AS
BEGIN

SET NOCOUNT ON;
if EXISTS(SELECT [StaffId] FROM [dbo].[StaffTable_Temp]  WHERE [StaffId] = @OldStaffId)
    BEGIN
        UPDATE [dbo].[StaffTable_Temp]
           SET [FirstName] = @FirstName
              ,[LastName] = @LastName
              ,[Description] = @Description
              ,[WorkPhone]  = @WorkPhone
              ,[Photo] = @Photo
              ,[UserType] = @UserType
              ,[StaffId] = @NewStaffId
         WHERE [StaffId] = @OldStaffId;
    END
END

The console application calls this sp and gets intermittent timeout issue when it updates the first record (we update for the whole table which contains around ~5k items).

I don't think 'parameter sniffing' is the root cause as the estimated number of rows is always = '1' for all scenarios, is it correct?

any help would be much appreciated


texts:
MySQL Update/Insert trigger auto-increment over group by

I have a form in which a user can click on a flag (=1 if yes or 0 if not) to create a receipt number (receiptno). I want this to be automated through the database - thus triggers came to mind.

I want to create an auto-increment over a group.

Schema:

 CREATE TABLE booking
    (BuchungId int, `category` int, `year` int, `flag` int, receiptno int)
;

INSERT INTO booking
    (BuchungId,`category`, `year`, `flag`, receiptno)
VALUES
    (1, 1, 2016, 1, NULL),
    (2, 1, 2017, 1, NULL),
    (3, 1, 2017, 0, NULL),
    (4, 2, 2017, 1, NULL),
    (5, 2, 2017, 1, NULL),
    (6, 1, 2017, 1, NULL),
    (7, 1, 2017, 0, NULL),
    (8, 1, 2017, 1, NULL)
;

I have created the body of a trigger:

    CREATE TRIGGER trigger1
        BEFORE UPDATE
        ON booking
        FOR EACH ROW
    BEGIN
      IF (new.Flag = 1) THEN

      SET New.receiptno = COALESCE(old.receiptno,0)+1;

      end IF;

end

Now I would like to update (or insert) the receiptno over a group by on category and year.

Thus eventually resulting in a table like:

(1, 1, 2016, 1, 1),
(2, 1, 2017, 1, 1),
(3, 1, 2017, 0, NULL),
(4, 2, 2017, 1, 1),
(5, 2, 2017, 1, 2),
(6, 1, 2017, 1, 2),
(7, 1, 2017, 0, NULL),
(8, 1, 2017, 1, 3)

http://sqlfiddle.com/#!9/05d7b


texts:
I am (unsuccessfully) using a correlated subquery on an Invoices  table:

Invoices(InvoiceID,  VendorID, InvoiceTotal, PaymentTotal, CreditTotal,.... ),

to find the sum of largest unpaid invoices by all vendors, where the unpaid condition is given by InvoiceTotal-PaymentTotal-CreditTotal <0:

 Select Sum(LargestUnpaid) from
 (Select   Max(InvoiceTotal) AS LargestUnpaid from Invoices
  where InvoiceTotal-(PaymentTotal+CreditTotal)<0 group by vendorID ) ;

The inner query runs , which is good and bad, since the query is supposed
   to be correlated , but the query as a whole does not run, and I get the  error message:

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near ')'.

What am I doing wrong?


texts:
I am working on a Data warehouse. I have tables with up to 200M records. Some of these tables have around 20+ indexes (I can't provide a reason why they have been created in the first place). This is making the job of maintaining these indexes too painful and has a direct impact on the DWH import job in both performance and run time.

How can I find the least used indexes on each table? (in order to get rid of them)


texts: After installing SQL Server 2019 CU2, SQL Server Agent stops working

texts:
After installing SQL Server 2019 CU2, SQL Server Agent starts consuming too much CPU resulting into skip job schedules.

Although the agent service is running but it looks like hang and didn't process / kick off any jobs.

Once I re-start the Agent service it starts working again (cpu = 0), but then again after some time 1-2 hours CPU intake in high and jobs do not run.

Is anybody else facing same issue with SQL Server 2019 CU2?

Any help would be much appreciated.

Thanks,


texts:
We have a process which selects a record from a progress table, then issues an update against that record.

If we do not do the select, then the update works all day long.  If we do the select, then the update times out.

The select query is pretty simple and looks like:

select fg."alphakey", n."first-name", n."last-name"
from pub.name n
inner join pub."family-guardian" fg
  on (fg."name-id" = n."name-id")
where fg."alphakey" = 'somevalue'

We've confirmed that if the above statement is not present then it works.  However, if the above statement is present then the UPDATE fails.  The update goes against the name table.  Unfortunately, I don't have that code as it executes through a third party.

Two questions:

Is this normal for progress?
What is the best way to get around the issue?


texts: Help with a table having fixed number of self-referencing columns

texts:
I'm trying to design an electronic toll collection system, where I have stumbled on decision how to design road section. Each road section is designed in a way that it has references to other road sections that lie ahead(RSA) , behind(RSB) and across(ORS). I tried to visually demonstrate them in figure below.

Visual presentation of Road Section with their primary keys is given below.

   |     |    || Road     |
   |          || Section  |
   |     |    || Ahead    |
   |    (1)   ||   (6)    |
------------------------------
   |     |    ||    |     |
   |          ||          |
   | Opposite || Road     |
   | Road     || Section  |
   | Section  ||          |
   |     |    ||    |     |
   |    (2)   ||   (5)    |
---------|----------|---------
   |          ||          |
   |     |    || Road     |
   |    (3)   || Section  |
   |     |    || Behind   |
   |     |    ||   (4)    |

I have two approaches in mind in designing such entity. The first one is using same table and have 3 columns referencing other rows in the same table.

Road Section Table
---------------------------------------------------
| PK_RS | FK_ORS | FK_RSA | FK_RSB | Other data
---------------------------------------------------
|   5   |   2    |   6    |   4    |  ...
|   4   |   3    |   5    |  null  |  ...

The second one is to create separate table for road section relationships, called Road Section Mapping

Road Section Table
-----------------------------
| PK_RS | Other data
-----------------------------
|   4   |  ...
|   5   |  ...
Road Section Map Table
------------------------------------
| PK_RS | FK_ORS | FK_RSA | FK_RSB |
------------------------------------
|   5   |   2    |   6    |   4    |
|   4   |   3    |   5    |  null  |

In the first case I will need to create rows with null references at first and update foreign keys later. In second a can create relations after creating road sections first.

What is the best practice in such kind of models?


texts: How to dump whole database from remote server on sequel pro

texts:
I have a database connection to a remote host on SequelPro Mac OSX. How can i dump whole database. I need a dump like what mysqldump command does:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

I found the table dump option, but i need full database dump.


texts: If the DBAs restored a single table from backup, would there be evidence anywhere in the data dictionary?

texts: In Oracle 12.1, if the DBAs restored a single table from backup, would there be evidence anywhere in the data dictionary?  If so, what would it be?


texts: Add a flag when certain conditions are met

texts:
I have a table where when an employee works LEAVE1 and LEAVE2 paycodes on the same day then in the new column called 'FLAG' we need to put a 1 for LEAVE1 and a 2 for LEAVE2, when these paycodes are not there on the same day then flag will be NULL as for other paycodes.

Tried using the CASE statements but it always puts a 1 or 2 for the respective paycodes regardless of the fact whether they were together on the same day or not.

Thank you and really appreciate all the help I get from this wonderful group.

Sample Table

WITH SampleData
    (PERSON,TRANSACTDATE, STARTDATE, END_DATE, IN_PUNCH,OUT_PUNCH,HOURS, PAYCODE)
AS
(
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '06:00','09:00','3', 'REG1'
    UNION ALL
    SELECT 1234,'08/03/2015','08/03/2015','08/03/2015', '09:00','13:00','4','REG2'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '09:00','13:00','4','LEAVE1'
    UNION ALL
    SELECT 1234,'08/04/2015','08/04/2015','08/04/2015', '14:00','16:00','2', 'LEAVE2'
    UNION ALL
    SELECT 1234,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'LEAVE1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '08:00','09:00','1', 'REG1'
    UNION ALL
    SELECT 4553,'08/05/2015','08/05/2015','08/05/2015', '10:00','12:00','2','LEAVE2'
)
SELECT Person
    , TransactDate, STARTDATE, END_DATE
    , MIN(IN_PUNCH) OVER (PARTITION BY Person,TransactDate)  AS IN_PUNCH
    , MAX(OUT_PUNCH) OVER (PARTITION BY Person, TransactDate) AS OUT_PUNCH
    ,HOURS
    ,PAYCODE
FROM SampleData

Current Results

Person  TransactDate STARTDATE  END_DATE    IN_PUNCH OUT_PUNCH HOURS PAYCODE
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     3     REG1
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     4     REG2
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     4     LEAVE1
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     2     LEAVE2
1234    08/05/2015   08/05/2015 08/05/2015  08:00    09:00     1     LEAVE1
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     2     LEAVE2
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     1     REG1

Desired Results

Person  TransactDate STARTDATE  END_DATE    IN_PUNCH OUT_PUNCH HOURS PAYCODE FLAG
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     3     REG1    NULL
1234    08/03/2015   08/03/2015 08/03/2015  06:00    13:00     4     REG2    NULL
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     4     LEAVE1  1
1234    08/04/2015   08/04/2015 08/04/2015  09:00    16:00     2     LEAVE2  2
1234    08/05/2015   08/05/2015 08/05/2015  08:00    09:00     1     LEAVE1  NULL
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     2     LEAVE2  NULL
4553    08/05/2015   08/05/2015 08/05/2015  08:00    12:00     1     REG1    NULL

Thank you for the quick help. Unfortunately. it's a 2008 server and I'm not familiar with OUTER APPLY. Can a solution be posted without the OUTER APPLY?


texts: Find multiple rows that share one field value but have one unique value

texts:
I have tables cards, decks, and a join table card_decks. I want to find the count of a unique set of pairs of cards where the a given attribute is true. I feel like I'm close but there is another step I am missing.

SELECT c.id card_id, d.id deck_id
FROM cards c
INNER JOIN card_decks cd
ON c.id = cd.card_id
INNER JOIN decks d on cd.deck_id = d.id
WHERE planeswalker = true OR signature_spell = true;

This gives me data that is a step closer.

 card_id | deck_id
---------+---------
    9137 |    2787
    9138 |    2787
    9182 |    2788
    9183 |    2788
    9207 |    2789
    9218 |    2789
    9233 |    2790
    9234 |    2790
    9285 |    2791
    9286 |    2791
    9360 |    2793
    9361 |    2793

What I don't understand is how to make 9317 and 9138 a unit that I can see if the pair exists in any other decks. I know there is a CONCAT function, but I'm not sure how to CONCAT two different rows like this.

Or more simply I want to find the count of all pairs of cards where a pair is one card with planeswalker true and one card with signature_spell true per deck.

EDIT 1:

Sorry about the poorly stated original question.

Here is the sample data:

create table cards (
    id INT,
    name VARCHAR(50),
    planeswalker BOOLEAN,
    signature_spell BOOLEAN
);
insert into cards (id, name, planeswalker, signature_spell) values
    (1, 'Norby', false, true),(2, 'Johanna', true, false),
    (3, 'Killian', false, true), (4, 'Madalyn', false, false),
    (5, 'Queenie', true, false),(6, 'Mort', true, false),
    (7, 'Nedda', false, true),(8, 'Pennie', true, false),
    (9, 'Fabien', false, true),(10, 'Fiann', true, false);

create table decks (
    id INT,
    name VARCHAR(50)
);
insert into decks (id, name) values
    (1, 'Buzzshare'),(2, 'Mymm'),(3, 'Pixope'),(4, 'Zazio'),
    (5, 'Shufflester'),(6, 'Youbridge'),(7, 'Avavee'),
    (8, 'Thoughtblab'),(9, 'Skiptube'),(10, 'Eare');

create table card_decks (
    id INT,
    card_id INT,
    deck_id INT
);
insert into card_decks (id, card_id, deck_id) values
    (1, 1, 1),(2, 2, 1),(6, 7, 2),(4, 6, 2),
    (3, 3, 3),(5, 5, 3),(7, 1, 4),(8, 2, 4);

The result I would like for the given sample data:

 pw_id   | ss_id   |  count
---------+---------+----------
    1    |    2    |    2
    6    |    7    |    1
    5    |    3    |    1

Where pw_id is a card id with planeswalker = true and ss_id is a card id with signature_spell = true, and the count is how many decks each pair of cards exist.

Let me know if I need to add anything else.


texts:
I want to deploy backup features using mongodb ops manager version 1,8. I have already installed ops manager and it's running but while i am clicking on backup tab it's showing below message. Please suggest how can i fix it.

"Backup is an optional module, which has not been configured for Ops Manager your installation"


texts: The performance of mysql fulltext depend on order of keyword

texts:
I am having problem with mysql fulltext search. When I change the order of keyword, the search speed changes. For example I have 4 queries, all of them return same result:

FAST (1s)

SELECT *
FROM   table
WHERE  (MATCH (column_data) AGAINST('+english* +php*' IN BOOLEAN MODE)

SLOW (10s)

SELECT *
FROM   table
WHERE  MATCH (column_data) AGAINST('+php* +english*' IN BOOLEAN MODE)

FAST (1s)

SELECT *
FROM   table
WHERE  MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE)
AND    (MATCH (column_data)) AGAINST('+english*' IN BOOLEAN MODE))

SLOW (10s)

SELECT *
FROM   table
WHERE  (MATCH (column_data) AGAINST('+english*' IN BOOLEAN MODE))
AND    (MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE))

How can I optimize it?


texts: Why we use low numbers for concurrent in MySQL benchmarks or HTTP_LOAD?

texts:
I've seen so many times that we use numbers like 5,6 or 10 at most. What is this concurrency? Doesn't it refer to number of simultaneous users in bench?

EDIT
I'm not talking about any specific benchmarking tools, I mean in general. In benchmarks for databases OR for apache servers we use low concurrent numbers. why is that the case?


texts: PostgreSQL/PostGIS: Query seems inordinately time-consuming

texts:
I'm doing what I would consider a pretty straightforward query on two tables that aren't huge (~626k records in one; ~47k records in the other).

Both tables have GIST indices on their spatial columns (a.g1 and b.g1 in the query below): these are the columns being used for the conditional part of the query.

The spatial columns are WKB MultiPolygons: they have been checked using ST_IsValid(), and are all lovely and clean.

The offending query looks like this:

# Query 1 (elapsed time: 90 minutes without completing)
CREATE TABLE se_add_mz AS
SELECT b.zone_code,
       b.g1 AS zone_geom,
       a.*
  FROM se_add_tmp AS a    /* this is the 600k record table */
  JOIN plan_zone AS b     /* this is the 47k record table */
    ON (ST_Overlaps(b.g1, a.g1));

The logic of the query is as follows: each se_add_tmp.g1 is known to have a portion of its area in more than one plan_zone.g1.

The aim is to find all the plan_zone.g1 that each se_add_tmp.g1 touches. Eventually the proportion of se_add_tmp.g1 area in each zone will be calculated, but I decided against trying to do that inside this query on the basis that the thing was already taking too damn long. That can happen intra-table (and hence be more faster) once this query finishes.

The se_add_tmp.g1 are known to be multi-zone because the se_add.g1 that are ST_Within() a plan_zone.g1 have already been identified in a query that only took 14 minutes (which still seems a long time):

# Query 2 (elapsed time: 13 min 55 sec; matches 2827611/3455218 rows)
CREATE TABLE se_add_z AS
SELECT b.zone_code
       a.*,
  FROM se_add AS a,     /* this table has 3.45 million records */
       plan_zone AS b   /* this one has 47k again/still */
 WHERE ST_Contains(b.g1, a.g1);

That query looks an awful lot like the one above, but was working with a 3.45 million-row table (se_add_tmp is the unmatched data from se_add).

I first tried Query 1 with no JOIN ... ON, using WHERE as per Query 2: that ran for upwards of an hour without producing results.

I have VACUUM (FULL+ANALYZE)ed, ANALYZEd, and REINDEXed both tables (nothing remotely interesting was found).

And now - finally - my question: is there some 'trap for young players' that I have fallen into here?

SPEC:

PostgreSQL 9.3.5 64-bit; PostGIS 2.1.3 r12457 on localhost under Win7 Ultimate. Machine has 16GB RAM, quad i7-4770 @3.4GHz, and a 500GB SSD (PostgreSQL data is on an internal 2TB HDD).

My machine is not under any perceptible load when the query is happening: the postgres process hovers at 5-9% of CPU and 15MB RAM, tops... I would be happy to hand it 8GB if it would just get the job done.

SPEC-related question: I have a CUDA-capable card (nVidia Titan X with 12GB RAM of its own - oh hell yeah). I've read that PGStrom can 'smartly' figure out how to push calc load to the GPU. Has anybody here had a crack at that sort of thing? I can't be bothered installing PostgreSQL 9.5dev if it's not going to give a tangible boost.

I ask mainly because I've CUDA-fied some Python stuff (big aerial-image analyses and some number crunching) with mind-boggling results. I would be happy to chuck the whole of postgresql and the related data at the GPU and let it whizz around there until it finishes, if that were possible.


texts: Installing SQL Server and Visual studio side-by-side

texts:
Is there any problems that I should be aware of when installing SQL Server and Visual Studio side-by-side? In which order should I install them?

My head tells me to install Visual Studio and then to install SQL Server second but will any problem occur if I install SQL Server 1st and Visual Studio at 2nd?


texts: How to relate a "parent common" table with its (possibly many) "subtype" tables?

texts:
I am having a bit of a brain freeze. I have a table whose properties are the root of four distinct elements, for example:

I need to relate SystemType to a SystemCommon, where one SystemCommon can have only one of the subtypes. How do I do this?

Should I add a SubType table and then add a SubTypeID to each SystemType?

CREATE TABLE SystemCommon (
    ID    int identity(1,1) not null,
    Name  varchar(20) not null,
    Descriptor  varchar(20) not null,
    CommonInfo  varchar(20) not null
    CONSTRAINT   PK_Parent Primary Key CLUSTERED (ID)
)

CREATE TABLE SystemType1 (
    ID                  int identity(1,1) not null,
    UniqueSystem1Field  varchar(100) not null
    CONSTRAINT   PK_SystemType1 Primary Key CLUSTERED (ID)
)

CREATE TABLE SystemType2 (
    ID                  int identity(1,1) not null,
    UniqueSystem2Field  varchar(100) not null
    CONSTRAINT   PK_SystemType2 Primary Key CLUSTERED (ID)
)

CREATE TABLE SystemType3 (
    ID                  int identity(1,1) not null,
    UniqueSystem3Field  varchar(100) not null
    CONSTRAINT   PK_SystemType3 Primary Key CLUSTERED (ID)
)

Clarifications for comment requests:

Perhaps "Parent" is really a poor choice of wording, and "SystemCommon" might be better.

a. SystemCommon is a table that contains columns that are common to all SystemTypes.

b. SystemTypes are the unique parameters for each of the SystemTypes - each SystemType is its own table. There are four Types currently.

c. A SystemCommon can have only one SystemType (any 1 of the 4).

d. Many SystemCommons can have the same SystemType. So SystemType1 can belong to SystemCommon (where id=1..n).

I am trying to model accordingly. A main group of elements that correlates to a given system (just for example as it is the best analogy I can come up with)
A computer has a Name and an IpAddress (all computers - SystemCommon). Each of these computers can have program[x1] or a program [x2] or a program[x3] but it can have only one of those program types, but it can have as many instances of that program type as it likes (so this is a one-to-many relationship); the hard part is to relate which SystemType (x1, or x2 or x3). It seems like a case of inheritance, as suggested via comments.


texts:
I am reading the paper Making geo-replication fast as possible, consistent when necessary by Li et al. in which they propose a geo-replication system called "RedBlue Consistency". They have a table which compares a couple of systems that have implemented geo-replication with several desired properties.
One of the properties is called "stable histories", and their description is:

  the system should provide a set of stable histories, meaning that user actions cannot be undone;

I don't fully understand this part, what do they mean by "user actions cannot be undone"?

Here is a link to the paper and a video of the talk: https://www.usenix.org/conference/osdi12/technical-sessions/presentation/li


texts: How to check if databases are consistent in pgsql replication?

texts: There is some tool for mysql (from percona I think) which could compare databases by making md5 sums of tables data. We used it in our mysql replication system to check if data is correctly replicated. Do you know something similar that I can use in pgsql environment ?


texts: Azure SQL Database and Database Maintenance

texts: If SQL Agent isn't available for Azure SQL Database then is it possible to maintain (indexes, statistics etc) the databases with Ola Hallengren's maintenance scripts, for example?


texts: Is WAL Database log on separate disk good idea on RAID10?

texts:
I have a RAID10-Server and have Postgres writing the log(pg_xlog a.k.a WAL) onto the same RAID10-Array.

I use the WAL in sync-mode with a big buffer and do a lot of bulk inserts and updates, so that buffer is hopefully used.

Is it an good idea to split the log onto a extra disk, not in the raid10? I could stuff in a RAID with 2 disks, but would that really improve anything?

I looking forward if someone could name a few pros and cons.


texts: PostgreSQL compare a timestamp with timezone with a timestamp

texts:
How do you compare a timestamp with a timezone with a timestamp in postgresql?
I am trying to cast one of the 2 to the type of the other but it does not work for me. Eg.

Having

TS1: 2019-01-07 21:43:00

and

TS2: 2019-01-07 20:43:37+01

I need to compare them and check if they are the same not including the seconds. So I have tried with:

select attimestamp, attimestamp_tz, attimestam=attimestamp_tz::timestamp from attendance where atid=1070

but this will always return false.


texts: Can I have a sql 2017 read-scale AG on different Windows server OSs; i.e. Windows server 2016/2019?

texts: I hope the title is self-explanatory. We want to add another node to our sql server 2017 read-scale AG. I would like to go with Windows server version 2019, and cannot find any information that says I cannot. Any concerns, comments or experience on this?


texts: Create nonclustered index on uniqueidentifier type

texts:
CREATE TABLE [dbo].[session]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user_id] [int] NOT NULL,
    [session_id] [uniqueidentifier] NOT NULL,
    [supervisor_id] [int] NULL,
    some columns ..

    CONSTRAINT [PK_session]
      PRIMARY KEY CLUSTERED ([id] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
                ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tbl2]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [close_date] [datetime] NULL,
    [edit_date] [datetime] NULL,
    [session_id] [uniqueidentifier] NOT NULL,
    some columns ..

    CONSTRAINT [PK_tbl2]
    PRIMARY KEY CLUSTERED ([id] ASC)
       WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
             ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

I have those two tables which I can join on the session_id column. There are more than 20 000 rows in each table, and the join is too slow. Is it a good idea to create a nonclustered index on session_id columns which are of uniqueidentifier type? Or there is another way to do the join faster ?

Edit:

For example query is like :

SELECT some_columns...
FROM [dbo].[session] s
INNER JOIN [dbo].[tbl2] t2 on s.[session_id] = t2.[session_id]
WHERE t2.some_column = something


texts: UPDATE table SET value which SELECT from other table

texts:
I need to update the column on Table_1, and then SET the value which select from Table_2

UPDATE top(3) a
SET col_1 = col_2_From_Table_2
// Problem here, select col_2 from Table_2 and update into col_1
FROM Table_1

Table_1

ID  | col_1
-------------
11  | null
11  | null
11  | null
11  | null
11  | null

Table_2

col_2
-----
AA
BB
CC

Expected Result of Table_1 (Update top 3 records which select form Table_2)

ID | col_1
----------
11  | AA
11  | BB
11  | CC
11  | null
11  | null


texts: Why don't databases create their own indexes automatically?

texts: I would have thought that databases would know enough about what they encounter often and be able to respond to the demands they're placed under that they could decide to add indexes to highly requested data.


texts: Using existing SQL Server credentials for log shipping reporting purposes

texts:
I have set up log shipping, but I don't see how I can reset orphaned SQL Server credentials on the destination server as databases are in standby/read-only mode. I am getting push-back on implementing this functionality. In light of this, I would like to make it as easy as possible for developers to use the log shipping databases for reporting as opposed to the live transactional databases.

I would prefer to use Windows credentials which wouldn't have to be reset, but wouldn't that somehow require all users (of reports) to be validated ?


texts: Script to find the list of stored procedures in all databases

texts:
I need to pull out the list of stored procedures which are available in my instance. I used the following T-SQL statement to get the stored procedures in a given database.

select *
from MyDatabase.information_schema.routines
where routine_type = 'Procedure'

Is there is any script to obtain the all stored procedures or to check the database name of the stored procedure by using the stored procedure name?


texts: How to split tables without affecting all stored procedures

texts:
We have merge replication setup between corporate database and remote clients. Lately we realized that people working on the corporate server do not need data to be tracked by merge replication as this data does not need to flow down to remote clients. We have decided to split tables between remote clients and people working on the corporate server.

Eg: Table1 has all data for remote clients as well as for people on corporate server. After table splitting, we want it to be Table1 for remote clients and Table2 for people on the corporate server.

To accomplish this I will have to change all my stored procedures for inserting and updating records in table1 and table2. First I have to find records in table1 and in case I don't find records in table1, I have to search records in table2 and then perform crud operations on the record.

Is there any easy way to accomplish this? A way where I don't have to change every stored procedure? I am aware of creating a view over table1 and table2, then create an instead of insert trigger on the view and write logic in trigger to insert into table1 and table2. Is anybody aware of any other way of accomplishing this kind of change?


texts:
I need to import data from an old database to a new one, with slightly different structure. For example, in the old database, there is a table recording employees and their supervisors:

CREATE TABLE employee (ident TEXT PRIMARY KEY, name TEXT, supervisor_name TEXT)

Now, the new database is as follows:

CREATE TABLE person (id BIGSERIAL PRIMARY KEY, name TEXT, old_ident TEXT);
CREATE TABLE team (id BIGSERIAL PRIMARY KEY);
CREATE TABLE teammember (person_id BIGINT, team_id BIGINT, role CHAR(1));

That is, instead of plain table of employees with their supervisors' names, the new (more generic) database enables to create teams of people. The employees are members with role 'e', supervisors with role 's'.

The question is how to easily migrate the data from employee to the new structure, one team per employee-supervisor pair. For example, employees

employee: ('abc01', 'John', 'Dave'), ('abc02', 'Kyle', 'Emily')

are to be migrated as

person: (1, 'John', 'abc01'), (2, 'Dave', NULL), (3, 'Kyle', 'abc02'), (4, 'Emily', NULL)
team: (1), (2)
teammember: (1, 1, 'e'), (2, 1, 's'), (3, 2, 'e'), (4, 2, 's')

I would consider using a data-modifying CTE, inserting the employees and supervisors first, then teams among them. However, CTE may only return data from the inserted table row. Thus, I am not able to match who was the supervisor of who.

The only solution I can see is using plpgsql, which would simply iterate over the data, hold the inserted team IDs in a temporary variable, and then insert the appropriate teammember rows. But I am curious whether there are simpler or more elegant solutions.

There will be roughly several hundreds to several thousands of employees. Although it is generally a good practice, in my case, I wouldn't like to generate the new IDs based on the old ones, as the old IDs are strings like *.GM2. I store them to the old_ident column for reference.


texts: How to get a column of the select to be "cumulative" (like in excel)?

texts:
This simple query (written in MySQL) is counting the number of WP posts published every day. But I need another information. I want to know the cumulative number of post for each date returned by the query.

If there are 100 posts published on this blog at date X I want the query to show 100. If I publish another post at date X + 1, I want to read X+1 => 101 posts

How can I achieve this objective?

SELECT DATE(post_date)    AS date,
       concat(post_title) AS titles,
       COUNT(*)           AS count
FROM   wp_posts
WHERE  post_status = 'publish'
AND    post_parent = 0
AND    post_type = 'post'
GROUP  BY DATE(post_date)
ORDER  BY DATE(post_date)


texts: Does the creation of constraints affect the views that have been created earlier?

texts:
Does the creation of constraints(foreign key) affect the views that we have created earlier?

create view allreservations (fno, customer) as
SELECT f.flight_number, customer_number
FROM flight f, flight_reservation fr
WHERE f.flight_number = fr.flight_number;

ALTER TABLE <tablename>
ADD FOREIGN KEY (<attribute>)
REFERENCES <tablename>(<attribute>);


texts: How can I make a copy of a table from information_schema?

texts:
I would like to extract meta information from information_schema like count of rows in a table and store the result table into "meta" database. However, while I can query tables in information_schema, I can't store the result into a table. Following query always returns "ok", but it doesn't produce the table (I can rerun the query and MariaDB still returns "ok"):

create table meta.copy as
select *
from information_schema.`TABLES`

What is more interesting is that if I thoroughly avoid databases (TABLE_SCHEMAs) with a view, I can save the partial copy of information_schema.TABLES. For example, following query works:

create table meta.copy as
select *
from information_schema.`TABLES`
where TABLE_SCHEMA = 'performance_schema'

Is this a quirk of MariaDB 10.0.15? Or is there some rationale behind this behavior? And is there a query that can make complete copies of tables from information_schema?

PS: I know I can extract count of rows other ways but I also want to extract other information from information_schema than count of rows.


texts: MongoDB: Approach for storing frequently modified references

texts:
Need a piece of advice for storing a large number of references between Mongo entities that are frequently updated.

Some background:

1 event can have 5000 logs.
1 event can have 10 assets associated (assignment object).
Logs are created per asset per event.
Query pattern: find all logs by assignment (eventId and assetId).

Entities with their relations:

EVENT <-1--------------N-> LOG
EVENT <-N--ASSIGNMENT--N-> ASSET

Mongo collections and documents:

event: { id, name, ... }
asset: { id, name, ... }
log: { id, eventId, metadata, ... }
assignment: { id, assetId, eventId, metadata, ... }

Here comes the question:

What is the best way to store associations between logs and assignments (events and assets)? Note that those associations are updated frequently and concurrently.

Approach 1

Store logIds in assignment entity:

assignment: { id, assetId, eventId, logIds, ... }

Approach 2

Create a separate entity for holding a single reference between log and assignment:

assignmentLog: { assignmentId, logId }

Another approach ?

Any advice will be appreciated. Thanks.


texts: How can I find the stored binary value of a DOUBLE value in MySQL?

texts:
Given the following table, backed by MySQL 5.7/InnoDB:

CREATE TABLE mytable(mydouble DOUBLE) SELECT 0.005 `mydouble`;

how can I find what exactly InnoDB is storing on the disk for the value 0.005?

I know that this varies depending on the architecture, so I'm looking for a methodology rather than a certain value. I'm a programmer/dba, so I'm handy with any tool, if required.


texts:
Is there any difference between a SEQUENCE declared using NO CACHE and one declared using CACHE 1 in SQL Server 2012+?

Sequence #1:

CREATE SEQUENCE dbo.MySeqCache1
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    CACHE 1;
GO

Sequence #2:

CREATE SEQUENCE dbo.MySeqNoCache
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    NO CACHE;
GO

Is there any difference between the two? Will they behave differently when being used in a SQL Server 2012+ environment?


texts: Column name or number of supplied values does not match table definition error

texts:
I have a table, with two foreign keys defined on two columns of that table. The problem is that, when I insert the values from SQL Server, I get the following error:

  Column name or number of supplied values does not match table definition

Please see the below image for the definition of the table. Can you please tell me why the value is not getting inserted?


texts:
Topic/Questions: Oracle Character Set/Database 11 to 12 Migration Options

Task:
Migrating production Oracle EE 11.2.0.4 single instance database with NLS_CHARACTERSET WE8ISO8859P1 to existing remote server hosting Oracle EE 12c (12.1.0.2.0) RAC DB with NLS_CHARACTERSET AL32UTF8
(The desired outage window is 4 hours or less.)

Technical Details:

Current production environment is as follows:

Oracle EE 11gR2 (11.2.0.4) single instance DB with 1 physical standby implementing DR/Active Data Guard
2 Terabyte Database, Oracle Linux Server release 7.3
NLS_CHARACTERSET WE8ISO8859P1, NLS_NCHAR_CHARACTERSET AL16UTF16

We plan on moving this production environment to new server Oracle 12c two node RAC environment (Oracle Data Appliance [ODA X62 HA] ) which is our current testing environment consisting of:

Oracle EE RAC 2 node 12c (12.1.0.2.0) Database
Oracle Linux Server release 6.8
NLS_CHARACTERSET    AL32UTF8
NLS_NCHAR_CHARACTERSET  AL16UTF16
(NOTE: There is second identical 12c RAC ODA appliance that is slated to be the EE 12c RAC 2 node physical standby in a DR/Active Data Guard configuration.)

OPTIONS BEING CONSIDERED/EVALUATED FOR ORACLE 11G SINGLE INSTANCE TO 12C RAC DATABASE MIGRATION:
(Note: Outage window is planned to be 4 hours or less)

(1) Transportable Database - this is a feature of 12c and appears N/A because source database is 11gR2 (please confirm).

(2) Transportable Tablespaces - NLS_CHARACTERSETS are different (source 11gr2 DB is NLS_CHARACTERSET WE8ISO8859P1 and destination 12c RAC database is NLS_CHARACTERSET AL32UTF8)
Both databases have the same NLS_NCHAR_CHARACTERSET (AL16UTF16).
Oracle states the Database Migration Assistant for Unicode tool (DMU) can be used to convert
WE8ISO8859P1 to ALT32UTF8.)
If this tool/approach is used - it would have to be on a live 11gR2 production system which has its inherent risks (yes, do the full backup first before using the tool).
Does the database have to be stopped to use this tool and how long, in general, would a 2 Terabyte DB
conversion take (concern about the limited 4-hour outage window)?
If this method is chosen - would I just need to transport all user tablespaces only (not system/sysaux)?

(3) Creating an additional transient 12c physical standby and conducting an immediate switchover to the 12c RAC database once synched and turning off the production 11gR2 (former primary) database and existing 11gR2 standby.
(The first step would be to drop the existing 12C RAC test database via DBCA and then run the RMAN Active Duplicate Database command and immediately complete the physical standby configuration process and synch the new 12c RAC standby. The next step would be to set up DR/Active Dataguard with the 2nd 12c RAC ODA.)

Please confirm since the existing 12C RAC test database was dropped and a 12c transient standby database was created there would not be any NLS Character set differences.

Please confirm that there would be no need for a 11gR2 to 12c database upgrade processsince the standby was created with 12c binaries.

Any other options or advice/recommendations would be appreciated.


texts: Postgres tsrange index for small sliver of time

texts:
I have a table (events) that has two timestampz columns (start_time and end_time). My program queries the table every minute for all records that are happening right now:
select * from events
where tstzrange(start_time, end_time, '[)') && tstzrange(:start, :end, '[)')
and status = 'PUBLISHED'

Where the bound variables for start and end are sent by the program tied to the current minute (truncated to the minute) and the next minute (truncated to the minute). Ex: 12:00:00.000000 and 12:00:01.000000.
The index for this, as per this excellent answer, is as follows:
create index concurrently
    events_activity
    on events USING gist
    (tstzrange(start_time, end_time, '[)'))
    where status = 'PUBLISHED';

I believe this should serve my purpose well -- the index has taken 90+ minutes to create so far, so I can't be certain. But assuming it does, I have one concern: it seems really wasteful. I base this on the assumption that it is keeping all records in the index for all of eternity, but my use case only requires the time range query for a small window of time.
So my question is: given that I only query for ranges of time that are really close to "now", is there some way to keep the index much smaller to reflect my narrow needs? Is there some where to constrain the index to just reflect records that I'm expecting to query for? Would adding the following to the end of the index automatically keep it small and constrained to my target window?
tstzrange(start_time, end_time, '[)')
  && tstzrange(now() - interval '30 minutes', now() + interval '30 minutes', '[)')

UPDATE: No, that won't work... functions in index predicate must be marked IMMUTABLE.
Thank you!


texts:
I have a database and it is in full recovery model. Transaction log file is restricted to 100 MB.

I inserted some rows to a table in the database. When I see the logspace used using dbcc sqlperf(logspace) it showed the log space used is 60 percent.

Next I did a full backup of the database and restored with new database name. When I see the logspace used using dbcc sqlperf(logspace) it showed the log space used is 9 percent.

Full backups do not truncate the log file. I do not understand why the space used by log files in these two DBs are not the same?


texts: Rollback Committed transaction in SQL Server

texts:
Is it possible to rollback a committed transaction?

I have deleted all the data in a table. The delete statement is inside Begin Transaction and I have committed the transaction with a commit transaction command. Now is it possible to rollback and get the data back?


texts: SQL InMemory Natively Compiled SP - unsupported constructs

texts:
We're looking at converting several normal T-SQL Stored Procedures into Natively Compiled SPs for performance.

The SPs use a ton of #tmp tables - select into, delete from, update from, and several unsupported constructs like SUM/MAX/ROW_NUMBER OVER PARTITION BY, WITH, LIKE etc.

We have figured workarounds for many of these :
Select into - Create table type ahead of time
Delete from - use while loop as cursor
...

I was able to work around SUM/MAX OVER PARTITION BY.. but don't know how to do it for ROW_NUMBER OVER PARTITION BY. Is there a way ?

Also, has anyone done large-scale conversion of SPs to Natively-Compiled and any roadblocks you encountered ?


texts: SQL Server 2016 SP 1 on HP 3PAR environment causes weird head blocker

texts:
My client is on HP 3PAR hardware. Three servers are on different geographical locations, connected with a 40 gbit fibre connection. The SAN is SSD only, the machines have lots of cores.

The ETL process starts a single select query joining 2 tables. The execution plan shows the use of a non-clustered index combined with a heap scan for both tables.

This simple query becomes a head blocker for all other following queries, even those that are referencing completely different tables.

My first guess is that something with the 3PAR could be involved, but I'm not really sure. Besides that, it's really weird that a

select a.[value] from table a, inner join table b where table a.column = 12345

manages to block

update table c set column a = [value].

AFAIK there are no lock hints.

There are no triggers of foreign keys on the used tables. There are no functions or other creative extra's on the database.

I'm wondering if the HP 3PAR failover can in some way cause a query to lock a database or table or data partition or HOBT.
Any thoughts? If more info is needed, let me know.

== Edit 12/28/2017
Answered the question :)


texts: Difference between database backup and data-tier application export

texts: I don't know if this is a bad question, but I don't understand what the difference is between normal DB backups and data-tier applications in SQL Server. I know backups can't be imported from SQL Server to an older version of SQL Server, but data-tier applications do work for this, and I've been using this to move some of our databases between servers. What are the differences from a backup perspective?


texts: Sizing the SQL Agent Job Activity Monitor screen

texts:
Does anyone know how to reset the initial (default) window size that is used when opening the SQL server Agent Job Activity Monitor screen?
When opening the Job Activity Monitor we always need to drag the window open to see the relevant columns.
Can this not be set/saved to always open at the width that I want?


texts:
I wanted to update the ranks of my table Performance and I found this solution, but I do not understand why this SQL code works:

WITH cte AS (
    SELECT AllTimeRank, r = RANK () OVER (PARTITION BY Distance ORDER BY TimeInSeconds)
    FROM dbo.Performance
)
UPDATE cte
SET AllTimeRank = r FROM cte;

The statement says UPDATE CTE but in fact, it really updates the table (dbo.Performance).

Can someone explain me this magic?

The following statement seems to do exactly the same:

WITH cte AS (
     SELECT Id, r = RANK () OVER (PARTITION BY Distance ORDER BY TimeInSeconds)
     FROM dbo.Performance
)
UPDATE dbo.Performance
SET AllTimeRank = r
FROM cte INNER JOIN dbo.Performance p ON p.Id = cte.Id;


texts: Dropped Index during Database Engine Tuning Advisor

texts:
I was analyzing a query using "Database Engine Tuning Advisor". During this time the database started to get blocking, long running queries and ended by restarted the instance. When I checked after that I found a few drop index commands had been executed.

drop index [dbo].[Profile].[_dta_index_Profile_7_1563152614__K15_K1]

Why did this happen? The query I was tuning was not on profile table!
How can I check if this index was already in the system before or was created during the analysis?


texts: High page fault, slow query/ insert during background index creation in MongoDB

texts:
I was trying to create two compound indexes in background in mongodb. As per mongo doc and other sources, background index creation should not affect read/write.
But severe performance issue was observed. Page fault jumped to 180/s from 4/s, queries that takes 300ms to complete, started taking more than 2 seconds.
Db size : 40GB
Documents : 40 million
Previous index size : 24GB
Compound index1 : {field1 : 1, field2 : -1}
Compound index2 : {field3 : 1, field4 : -1}

DB stats   :
{
    "db" : "response",
    "collections" : 4,
    "objects" : 42253780,
    "avgObjSize" : 1008.5294166817738,
    "dataSize" : 42614180096,
    "storageSize" : 43648786160,
    "numExtents" : 44,
    "indexes" : 14,
    "indexSize" : 22113921536,
    "fileSize" : 79352037376,
    "nsSizeMB" : 16,
    "extentFreeList" : {
        "num" : 53,
        "totalSize" : 5861306368
    },
    "dataFileVersion" : {
        "major" : 4,
        "minor" : 22
    },
    "ok" : 1
}

Free -m :
             total       used       free     shared    buffers     cached
Mem:         64314      63944        370          1        867      56077
-/+ buffers/cache:       7000      57314
Swap:         3999        434       3565


texts:
In our dbms class, these were the four properties of a primary key discussed for Oracle.

Unique
Not NULL
Fully functional dependency
Indexed

I understand all the properties except the 3rd one (fully functional dependency). Doesn't unique property ensure this?


texts:
The software company I work for is planning to migrating our infrastructure into the cloud. Our current focus is to find the best database replication method.

Current Servers:

SQL Server 2008 SE - Located in NY (In house - Master)

SQL Server 2012 SE - Located in Chicago (Rackspace - Slave)

We have been considering log shipping, but we are also aware of mirroring and replication.
 Can someone please provide some insight on what our best approach for this task may be? We simply want to keep both databases in sync with each other. Real time does not matter yet, but it will in the near future.

Note: Our long term goal is to make Rackspace our primary, and NY our secondary.


texts: JSON subquery using WITHOUT_ARRAY_WRAPPER returning odd data?

texts:
SQL Server 2016, I'm attempting to work with some regular data and return a JSON object for processing by another system. The other system does not recognize the array wrapper, and so I am attempting to use WITHOUT_ARRAY_WRAPPER to get rid of this. When used in a subquery odd results get returned...

SELECT @@SERVERNAME AS [Servername],
( SELECT [Name], [Recovery_Model_Desc]
    FROM sys.databases
    WHERE name in ('master', 'model', 'msdb')
    FOR JSON PATH
    ) AS d
FOR JSON PATH, ROOT('ServerInformation')

This produces expected data, with the array wrapper...

  {"ServerInformation":[{"Servername":"MyServer","d":[{"Name":"master","Recovery_Model_Desc":"SIMPLE"},{"Name":"model","Recovery_Model_Desc":"FULL"},{"Name":"msdb","Recovery_Model_Desc":"SIMPLE"}]}]}

However, WITHOUT_ARRAY_WRAPPER produces...

SELECT @@SERVERNAME AS [Servername],
( SELECT [Name], [Recovery_Model_Desc]
    FROM sys.databases
    WHERE name in ('master', 'model', 'msdb')
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS d
FOR JSON PATH, ROOT('ServerInformation')

  {"ServerInformation":[{"Servername":"MyServer","d":"{\"Name\":\"master\",\"Recovery_Model_Desc\":\"SIMPLE\"},{\"Name\":\"model\",\"Recovery_Model_Desc\":\"FULL\"},{\"Name\":\"msdb\",\"Recovery_Model_Desc\":\"SIMPLE\"}"}]}

whereas I would expect it to produce

  {"ServerInformation":{"Servername":"MyServer","d":{"Name":"master","Recovery_Model_Desc":"SIMPLE"},{"Name":"model","Recovery_Model_Desc":"FULL"},{"Name":"msdb","Recovery_Model_Desc":"SIMPLE"}}}

Bug or expected results?

Edit: adjust expected results


texts: How to purge old files from pg_xlogs directory

texts:
I have an archive command copying WAL files from pg_xlogs to my archive directory.  However, I notice that the pg_xlogs directory is growing in size with (but is limiting itself to around 7 files).   What controls this "7" value?  Is there a risk it will grow to a huge number of files?

How do I limit the number of log files, or even better, limit to no more than 48 hours worth?  I found a similarly titled question here but it doesn't really apply.  I'm using PostgreSQL version 8.4.20 (so settings/commands from v9 won't apply)

Can I just create a cron job that deletes files from pg_xlogs older than 48 hours?  Will this crash/corrupt the pgsql server?  Is there a better way?

Can I use my archive_command to delete the source file after moving to my archive directory?


texts: Google Cloud Spanner UNIQUE Index Consistency

texts:
As far as I understand Google Cloud Spanner is globally consistent and also fast.

Let's say I have a table with a unique username field.

User A in USA inserts user johndoe to a US Spanner node
User B in China inserts user johndoe to a Chinese Spanner node

Does one of them fail?
If not, how fast are the queries? Or do they both succeed but later on decide that one of them is a duplicate? Or do writes with UNIQUE fields go to 1 server?

Thanks!


texts: InnoDB, Writing TRIGGER to INSERT into tables

texts:
Table1 "user_address"
Field "u_id"
Field "street_name"
Field "locality_name"

Table2 "street_master"
Field "s_id"
Field "s_name"

I am supposed to write a trigger which BEFORE INSERT ON user_address(table1) checks if NEW.street_name already exists in s_name in table2, and
if it does not exist then NEW.street_name is inserted in table2.

I don't know how to do it . It has to be done by trigger only.


texts: Mongodb tree structure questions and answers

texts:
I'm looking to build a question answer survey system where some questions will be based on the answer of the parent question. The hierarchy level of the questions can go any number of depth based on the questions. The questions and answers will be like the diagram shown here.

I'm looking to build this in mongodb. I have checked the tree model in mongodb and I'm clueless on how to structure it as the next set of questions are depends on the answers. The questions should show based on what answer the user have selected and some answers may have multiple question sets below that as well (E.g.: Ans B).

Can someone help me on the mongodb collection structure and how can I query the next set of questions and answers based on the previous answer given by the user?
Thanks in advance.


texts: Dataguard parameter changed after DB bounce

texts:
I have a production environment of Oracle 11g R2 patchset 4 primary database configured with 2 standby machines.

Primary database unique_name is DBIDI
1st standby database unique_name is DBIFI
2nd standby database unique_name is DBIGI.

These unique_names are configured in parameter

log_archive_config=DG_CONFIG=(DBIDI,DBIFI,DBIGI)

This parameter is configured through Enterprise Manager with My USER with dba grant. Last night we bounced the primary database. After bounce back, the parameter log_archive_config is changed and DBIGI is removed from this parameter:

log_archive_config=DG_CONFIG=(DBIDI,DBIFI)

Can any one tell what is the reason for this?


texts: SQL Server 2008 R2 Express password overwrite

texts:
I'm using a program that requires SA log-in to SQL Server. We had issues where some 3rd party support numpty changed the SA password and never informed us, so the program couldn't log-in.

I've accessed our information via the SQL Server Management Studio. Fortunately, the 'remember password' option had been checked on the machine.

Via the object explorer, I've accessed the SA log-in credentials and changed the password accordingly. However, every time I re-load the SQL Server Management Studio, the password re-sets to the original one.

When I test the connection of the program that uses the SA log-in by manually entering the new password it appears to work OK. But when I let the program start-up automatically, Windows services is detailing that the log-in is failing due to incorrect log-in details - which I'm guessing is because SQL Server keeps reverting back to the old password?

Can anyone advise how I stop it from reverting back to the old password and overwrite with the new password definitively? (SQL Server 2008 R2 Express) I'm hoping that should help fix my more specific problem with the other program?


texts: How to partition a table without time data?

texts:
I am using postgresql to store information about event ticket sales.

I have two tables: event_sale and event_sale_seat:

event_sale (id integer, created_at timestamp with time zone)
event_sale_seat (id integer, event_sale_id integer REFERENCES event_sale(id), event_seat_id integer REFERENCES event_seat(id))

The event_sale table is 200M records.
The event_sale_seat table is 1.5bn records.

Every time event_sale record is created, the corresponding event_sale_seat records are created as well, i.e. the latter table identifies which seats were sold.

The most common operation is getting all the event_sale_seat records for some event_sale criteria, e.g.

select
  es1.id event_sale_id,
  ess1.event_seat_id
from event_sale es1
inner join event_sale_seat ess1 on ess1.event_sale_id = es1.id
where es1.created_at BETWEEN now() - interval '1 month' and now();

As both tables are growing really large, I want to partition them, starting with event_sale_seat table.

I am considering of using https://www.timescale.com/.

My understanding that to partition a time-series database, I should have a column with a time value.

As I want to convert event_sale_seat to a hypertable and this table does not have a column carrying a time value, my questions are:

event_sale_seat does not have a column with meaningful time value. What column should be used for partitioning?
suppose that I must/ should have a time column, would I just copy event_sale.created_at to event_sale_seat?
how will the latter join example be performed when the table is partitioning by created_at?

Regarding the last question, would this be how the join is performed?

select
  es1.id event_sale_id,
  ess1.event_seat_id
from event_sale es1
inner join event_sale_seat ess1 on ess1.created_at = es1.created_at and ess1.event_sale_id = es1.id
where es1.created_at BETWEEN now() - interval '1 month' and now();


texts: Reaching a Microsoft SQL Server 2017 cluster from another device

texts:
Let say I have a python service in one instance. This service needs to reach a MSSQL cluster of 5 nodes that is in an other location, with different IP addresses. Cluster creds:
user: my_user
password: my_password
Database: my_database
What is the IP address that I should provide to my python code so that it can reach this cluster? Indeed, there are many different IP addresses in this cluster (one for each node).
In many storage engines, there are the notions of master/primary and slave/secondary, but I don't know much more, especially for Microsoft SQL Server.


texts: Is a clustered index on a child table in a parent/child relationship the most optimal index?

texts:
A clustered index will sort row data in a table according to the ordering of the fields in the index. If so, why would you (or would you not) want to create a clustered index on the child table in a parent/child relationship?

Lets assume the classical order-with-orderlines example where you never fetch the orderlines without first going through the associated order. I would think that having a clustered index on both the orderid and the orderlineid column (as clustered indexes must be unique) forces all orderlines for a single order to be physically located adjacent to each other, which would make reads very performant. Is this correct? And if so, are there downsides to this approach?


texts:
I have read and heard that you cannot perform online clustered index rebuild for tables that have a varchar(max) type. This (as I have read) also should be true for nonclustered indexes that contain varchar(max) types. http://msdn.microsoft.com/en-us/library/ms190981.aspx. However when proving this out (in both versions of SQL2012 and SQL2014) I do not run into errors when rebuilding these indexes online. Is this an error in the documentation?

As a side note the online rebuilds fail on 2008R2.

Test script is included below ...

CREATE TABLE BIGMAX ( X INT IDENTITY(1,1), Y VARCHAR(MAX), FILLER BIT DEFAULT 1, FILLER2 BIT DEFAULT 1);

INSERT INTO BIGMAX ( Y ) SELECT top 1000 replicate('1',10000) from sys.all_columns c , sys.all_columns c2

CREATE CLUSTERED INDEX [CIDX_BIGMAX] ON BIGMAX(X) WITH (ONLINE = ON);

CREATE NONCLUSTERED INDEX [NIDX_BIGMAX_FILLER] ON BIGMAX(FILLER) WITH (ONLINE = ON);

CREATE NONCLUSTERED INDEX [NIDX_BIGMAX_FILLER2] ON BIGMAX(FILLER) INCLUDE (Y) WITH (ONLINE = ON);

ALTER INDEX [CIDX_BIGMAX] ON [dbo].[BIGMAX]
REBUILD WITH (ONLINE = ON);

ALTER INDEX [NIDX_BIGMAX_FILLER] ON [dbo].[BIGMAX]
REBUILD WITH (ONLINE = ON);

ALTER INDEX [NIDX_BIGMAX_FILLER2] ON [dbo].[BIGMAX]
REBUILD WITH (ONLINE = ON);


texts:
I have read many documentation including percona and MariaDB documentation where they recommend setting innodb_buffer_pool_size to 70-80% of RAM. My question is, is that total RAM or available RAM? For example, when i run free -m on my CentOS-6.5, i get the following:

[root@master5 ~]# free -m
             total       used       free     shared    buffers     cached
Mem:         19991        999      10992          0         38         92
-/+ buffers/cache:        867      19123
Swap:         1999          0       1999

So is it 75-80% of 19991 or that of 10992?


texts: VLDB is stuck in recovery state. It's a permission issue, now what should I do?

texts:
SQL Server database which is very large (4TB) is stuck in recovery state. Reason: Data center had a planned downtime and we had to turn the physical machine off and when it turned on, as I read in error logs, SQL Server couldn't access the files, because files were on a SAN machine, and probably things didn't went smooth.

  FCB::Open failed: Could not open file E:\SomeFolder\SomeDatabase.mdf for file
  number 1.  OS error: 5(Access is denied.).

Now that I've made sure that SAN is up and running, and permission are OK, how can I tell SQL Server to continue recovering? Since this database is very large, I don't want to interrupt the course of recovering and start from the beginning. And also, any backup would take hours to complete.


texts: How to change disk for "There is insufficient free space on disk volume"?

texts:
Restore of database 'Configurations_6.2.2.25' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  ------------------------------ ADDITIONAL INFORMATION:

  System.Data.SqlClient.SqlError: There is insufficient free space on
  disk volume 'E:\' to create the database. The database requires
  22758293504 additional free bytes, while only 10663624704 bytes are
  available. (Microsoft.SqlServer.SmoExtended)

I am getting the above error when trying to restore a bak file I was given. It is not an option for the database to be shrunk before creating the bak.

I have 9.99GB free on E:\ and I have 59.7GB free on D:\

How can I change this to point to D:\ instead of E:\? and will changing the disk solve my problem? Thanks

EDIT: Solved my problem. In Restore Database -> File -> I changed the Log file folder path to D:\, then under Options I checked Override


texts: Postgres View with content from another Databaseserver

texts:
Is it somehow possible to create a view on a Postgres database which gets its original data from a table of a SQL Server database on another server?

I want to have the data from a table of the SQL Server database on server A inside a table on my Postgres database on server B, but I can't use export import, also no scripted solution because I need them nearly realtime.


texts: Can somebody please tell what is the difference between these two date comparisons?

texts:
So I have 2 queries as follows:
f.DATE >= (GETDATE() - 3)

f.DATE >= CAST(CONVERT(VARCHAR(10),GETDATE() - 3,101) AS DATETIME)

I think they are the same but the second one gives me more rows than the first one does.
The data type of f.DATE is date though.
Can you please tell me where I get it wrong? Thank you much!!


texts: passing variable values to procedure in postgresql via psql

texts:
basically I have written a code to pass variable values via psql to my procedure is it supported?
and if supported any pointers to get it done : my code looks like below :
do $$
<<main_block>>
declare
  err_msg text;
BEGIN
        insert into mig.qb_items (qb_id , sync_token , qb_updated_at , qb_created_at , qb_expired_at , name , sku , description , active , qb_parent_id , taxable , unit_price , transaction_type , category_type , qb_income_account_id  , qb_expense_account_id , purchase_description  , tax_included , purchase_cost , qb_asset_account_id , track_quantity , inventory_start_date  , quantity_on_hand , qb_sales_tax_id , qb_tax_code_id , abatement_rate , reverse_charge_rate , service_type , level, created_at , updated_at , expired_at , ancestor_id , signature , financial_signature , qb_object , print_grouped_items, company_id )
select qb_id , sync_token , qb_updated_at , qb_created_at , qb_expired_at , name , sku , description , active , qb_parent_id , taxable , unit_price , transaction_type , category_type , qb_income_account_id  , qb_expense_account_id , purchase_description  , tax_included , purchase_cost , qb_asset_account_id , track_quantity , inventory_start_date  , quantity_on_hand , qb_sales_tax_id , qb_tax_code_id , abatement_rate , reverse_charge_rate , service_type , level , created_at , updated_at , expired_at , ancestor_id , signature , financial_signature , qb_object , print_grouped_items, :tenant_id as company_id from :schema.qb_items;
update mig.migration_status set status='COMPLETED' WHERE schema=:tenant and table_name='qb_items';
EXCEPTION WHEN others THEN
            GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT;
            update mig.migration_status set status='FAILED' WHERE schema=:tenant and table_name='qb_items';
raise notice 'EXCEPTION %', err_msg;
END main_block;
$$;

bt i am getting error as below :
psql:/home/ec2-user/scripts/qb_items.sql:14: ERROR:  syntax error at or near ":"
LINE 7: ...cial_signature , qb_object , print_grouped_items, :tenant_id...


texts: mysql query gets slower with increasing page number

texts:
I use laravel 5.2 with mariadb (ARIA engine) and found that my query for creating a gallery gets slower with increasing page number.

select `pics`.`id`,
       `pics`.`title`,
       `pics`.`hits`,
       `pics`.`created_at`,
       `users`.`username`,
       `users`.`displayname`
from `pics`
left join `users` on `pics`.`user_id` = `users`.`id`
order by `pics`.`id` desc
limit 48
offset 7776

This is the query for the last page (163) (offset 7776) which takes about 20ms while page 1 (offset 0) needs only 700µs. This is possibly still fast (?), but also  a difference.

Why does it get slower and what could I do to improve it?
Both tables have gaps and get UPDATE, INSERT and DELETE.

pics table:

id  int(11) unsigned Auto-Inkrement
url varchar(100)
title   varchar(200)
text    text
jahrprod    year(4) NULL
cat varchar(50)
hits    int(11) [0]
username    varchar(30)
user_id smallint(5) unsigned
location_id int(11)
created_at  datetime
updated_at  datetime

pics.username is old and will be removed in the future.


texts: select string that doesn't contain a substring/field

texts:
TableA.content is varchar2(4000), which was populated from a CLOB value that contains markup and text for a website, paragraphs actually.
TableB.keyword contains a string that I want to find in TableA.content.

I want to do something like this, but it is not quite right.  What am I missing?

select tableA.content from TableA
where (select distinct(TableB.keyword) from TableB) not in TableA.content;

So I want all the rows in TableA that don't have any of the values in TableB.keyword.

I was checking out MATCH or CONTAINS but I can't get it right using those.


texts: RMAN Backups - Can more than one media management software solution be used?

texts:
Does anyone know if it's possible for two different backup utilities to be used to backup the same Oracle database? Both utilities would use RMAN.
So, for example, take an Oracle 11g standby DB with a daily online backup to tape via Netbackup (using the RMAN recovery catalog) and then also take another separate daily backup via TIVOLI (also using the RMAN catalog), could they co-exist in some way? Obviously it's a not a sensible approach, this is just a question of what is or is not possible. Many thanks.


texts: Unattended install of SQL Server 2008 R2 cluster fails with error - "Illegal characters in path."

texts:
I am trying to build an .ini file for unattended installations of SQL Server 2008 R2. I followed this blog post to build an initial .ini file: http://blogesh.wordpress.com/2008/09/23/silent-install-of-sql-server-2008/

After cancelling the install and trying to install from the command line
(\server\store\SQL\setup.exe /ConfigurationFile="\server\store\SQL\Config.ini"), I get this error - "Illegal characters in path."

I get this error after resolving a number of simpler errors (such as paths showing up with no "\") because the created .ini file had some bugs (such as listing the path as "R:" in the .ini)

I'd like to know if anyone has been successful with installing a cluster using an ini file. Any help is appreciated.

Update:
I tried to install the cluster using the ini created from sirsql.net's powershell script. SQL does install now, but fails to bring the network name online (in the Failover Cluster Manager). All the settings look identical to our existing (functional) clusters, but a Wireshark capture shows that the new cluster is using the "realm" of the parent domain instead of the child domain (the server's domain, and the domain of all the service accounts, including the account I am using to install with). Our functional clusters show the "realm" as the child domain.
The packet that comes back from the domain controller shows this Kerberos error: KRB5KDC_ERR_S_PRINCIPAL_UNKNOWN.

I'd really like to get a Failover cluster setup without having to go through the gui since we are build a couple new clusters each week. Any ideas on what could be causing this or how to fix it?


texts: Why does this trigger slow down updates so much

texts:
I have a table. Lets call it table tableA. There's another table which keeps data about the history of tableA. Let's call that tableA_records.

On tableA, there's a trigger on update which looks similar to this:

CREATE TRIGGER upd_tableA AFTER UPDATE ON tableA
FOR EACH ROW
if @disable_triggers is null then
BEGIN
 SET @now = unix_timestamp(now());
 UPDATE tableA_records
 SET valid_to = @now
 WHERE valid_to IS NULL AND tableA_id = old.id;
 INSERT INTO tableA_records (
   tableA_id, column2, column3, ...., column27,
   valid_from, valid_to
 ) VALUES (
   new.id, new.column2, new.column3, ...., new.column27,
   @now, NULL
 );
 END;
end if

The issue I'm having is that the trigger slows down updates a lot. Without the trigger, an update on 100 rows of tableA takes 0.2 seconds. Inserting those 100 rows on tableA_records takes 0.25 seconds. The combined actions of the trigger (update tableA_records, insert into tableA_records) and the triggering action (update tableA) should take 0.65 seconds if we sum the times for each. With the trigger, it takes 35 seconds.

Anyone have an idea of what's causing the slowdown?


texts: We are using Access .adp (Access 2010) frontend in combination with SQL Server 2014 backend. At the moment we have a big performance problem with the SQL function permissions(). We have ca. 400 Users on our application. The logon takes 50+ Seconds for an normal user. Sysadmins logon takes 3 seconds. Do you have a solution for this problem?


texts:
How do I store the sensitive information on SQL server?. We have policy guidelines, DBA and Development engineers are not allowed to view the data as plain text.

I am aware of the encryption technologies and data masking technologies but the problems is, except the person who is directly responsible for the data, no one else can access the data as per the policy.

When developers need to perform some calculation on sensitive information, how is it possible to do that, without decrypting? while still allowing SysAdmin users to view the encryption key details? I am sure, I am not the first one to implement this system. How do I design this solution? Thank you!

Edit 1:

Is it possible to create a reference column based on the original column and give it to developer for calculations? In that case, how to avoid direct logics or how to avoid people reverse engineer the logic and how is it possible to keep the encryption / hashing logic away from DBA.

I am open to any other suggestion or workarounds. Thank you!


texts: how to get ALL the objects in a database that are NOT FOR REPLICATION?

texts:
“Not for Replication” is a property which can be set for different objects like check constraints,
Foreign Key constraints,
Triggers , Identity columns
etc while using SQL Server Replication.

What are ALL object types that can be NOT FOR REPLICATION?
How can I find them?

For those object types I have mentioned above I am putting a script together:

  identity columns

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
, i.name AS [Index]
, p.partition_number
, p.rows AS [Row Count]
, i.type_desc AS [Index Type]
,K.increment_value as IncrementValue
,K.last_value as LastValue
,K.seed_value as SeedValue
,k.is_nullable
,k.is_identity
,k.is_filestream
,k.is_replicated
,k.is_not_for_replication

FROM sys.partitions p

INNER JOIN sys.indexes i
ON p.object_id = i.object_id
AND p.index_id = i.index_id

LEFT OUTER JOIN sys.identity_columns K
ON P.object_id = K.object_id

where  1=1
  AND  i.index_id < 2 -- GET ONLY THE CLUSTERED INDEXES - IF EXISTS ANY
ORDER BY [Schema], [Table], [Index]

  triggers

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
,t.*

FROM sys.partitions p
INNER JOIN sys.triggers t
ON p.object_id = t.parent_id

  check constraints

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
,c.*

FROM sys.partitions p
INNER JOIN sys.tables t
        ON p.object_id = t.object_id

INNER JOIN sys.check_constraints c
        ON t.object_id = c.parent_object_id

  foreign key constraints

SELECT OBJECT_SCHEMA_NAME(p.object_id) AS [Schema]
, OBJECT_NAME(p.object_id) AS [Table]
,fk.*

FROM sys.partitions p
INNER JOIN sys.tables t
        ON p.object_id = t.object_id

INNER JOIN sys.foreign_keys fk
        ON t.object_id = fk.parent_object_id

Please note that I have used sys.partitions and that is not a requirement for this question, but I like to have an idea of the space each object occupies and where it is physically located.


texts: How to Rename Table or Field With Merge Replication

texts:
I am using merge replication with SQL 2012. I am trying to rename a published table, and rename a published field.

These operations are not permitted in merge replication.

However I can think of an action plan if these things are needed. For instance renaming a field would include,

Adding the new field
Migrating the data from the old field to the new field
Wait for a while, perhaps a month
Delete the old field

The problem is after step 2. We can force a software upgrade to the clients so that data is written into the new field.

But some data may have been modified in the old field at one of the subscribers before they do the sync which makes the schema change. This would also be before they were forced to do the update.

The migration could be done more than once, but by the time you get to step 4 its hard to know which modifications have been made to the old field, and which have been made to the new field.


texts: CheckDB on Partitioned Table - Insufficient Memory in resource Pool default

texts:
We are experiencing an issue on one of our servers where CheckDB is blowing the resource pool 'default' ... let me elaborate..

Windows 2012 R2 Standard - 16GB RAM
SQL Server 2012 Enterprise [11.0.5532.0] MAX memory 14GB RAM, MIN memory 0GB
This is a dedicated SQL Server running 1 default instance of Microsoft SQL Server. Vanilla install with no special requirements or configuration.

We can CheckDB all the other databases (max size of 3GB per database, 9 user databases) however when we CheckDB the 5GB database it fails after 10 minutes reporting

  "There is insufficient system memory in resource pool 'default' to run
  this query. [SQLSTATE 42000] (Error 50000)  DBCC execution completed.
  If DBCC printed error messages, contact your system administrator."

This is a vendor database and cannot be altered .. it consists of 2 filegroups, both filegroups contain 248 tables that each contain 1,000 partitions.

I have tried DBCC CheckDB with combinations of ALL_ERRORMSGS NO_INFOMSGS in case that made a difference to memory requirements but there was no change in behaviour. We cannot use TABLOCK as we will not be able to lock tables. I have tried DBCC CheckFileGroup and this returns the same error message.
We can try running PhysicalOnly for a lightweight check but we want to do a full CheckDB once a week.

The total disk space used (not allocated) to all the databases is 8,797 MB .. the RAM allocation to SQL Server and available is 14GB [14,336 MB]

My question is .... the entire 5GB database should fit into memory (and I have checked that more than 5GB of RAM is available..... would this volume of partitions and partitioned tables in a database using 1 or 2 filegroups cause excessive memory consumption? I cannot find any Microsoft documentation that says partitioned tables require more memory...only that partitioned tables over many filegroups can help reduce CheckDB time/resource requirements for VLDB.

I am running DBCC CheckDB WITH ESTIMATEONLY now and will update once I have the results.
UPDATE: ESTIMATEONLY returns the following messages:

  DBCC results for ''. CHECKDB found 0 allocation errors
  and 0 consistency errors in database ''. Msg 701, Level
  17, State 123, Line 1 There is insufficient system memory in resource
  pool 'default' to run this query.

Any input will be greatly appreciated!


texts: Way to obtain full list of column dependencies for stored procedure?

texts:
I've built a stored procedure (which we can call sproc_deps) that uses sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. I want it to list out all of the tables and columns used by a stored procedure of the user's choice. This stored procedure will have its name passed as an argument to sproc_deps.

The problem is that I'm getting columns that the stored procedure doesn't actually use when I combine sys.sql_expression_dependencies and sys.dm_sql_referenced_entities. In order to get the information I want, I've JOINed a few other things on as well:

sys.objects(for object IDs, and type_desc)
sys.tables(to match against tables contained in sys.sql_expression_dependencies)
sys.views (because I'm interested in both views and tables)
sys.columns (to pull columns for each table or view involved)

Here is the actual JOIN:

sys.sql_expression_dependencies AS sed
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id
LEFT OUTER JOIN sys.tables t on sed.referenced_entity_name = t.name
LEFT OUTER JOIN sys.views v on sed.referenced_entity_name = v.name
LEFT OUTER JOIN sys.columns c on (c.object_id = t.object_id OR c.object_id = v.object_id)
INNER JOIN sys.dm_sql_referenced_entities (N'dbo.DummySprocName', 'OBJECT') s
ON s.referenced_entity_name = sed.referenced_entity_name

Using just sys.sql_expression_dependencies nets a small list of tables that I can't really decipher, and using sys.dm_sql_referenced_entities yields a partial list of tables and columns used by the procedure.

Is it possible for sproc_deps to return the correct list of tables and columns used by a procedure using just T-SQL? If so, how?


texts: Percona and MariaDB-10.0 ignoring not null on table fields

texts:
On the percona-5.6 and MariaDB-10.0 servers, the NOT NULL declared on table field are being ignored. I am able to leave fields declared as NOT NULL empty when inserting data and the servers are not throwing an error or stopping the insertion from going on. When i try it on MySQL-5.6 and MariaDB-5.5, it works fine as expected and does not allow yo to insert empty values on fields declared as NOT NULL.

Please, advise why this is the case on mariaDB-10.0. Thanks


texts: User Mapping on a DB that's part of an AG. DB is READ only?

texts:
I'm a sys admin being tossed in to a DBA role. I currently have two SQL Server boxes, 2016 Enterprise, in an AG. The DB has synchronized without any issues.

A third party vendor needs to have an identical user on both servers with the same User Mapping. But when I try to adjust the mappings on the secondary I get an error stating that the DB is in READ ONLY mode. How can I create this user with the correct mappings?


texts:
Given:

 CASE
     WHEN A IS NULL THEN 1 ELSE 0
 END AS [A]
, CASE
     WHEN B IS NULL THEN 1 ELSE 0
 END AS [B]
, CASE
     WHEN C IS NULL THEN 1 ELSE 0
 END AS [C]

How can I SUM  A+B+C being the results of 3 different CASE WHEN


texts: Rebuild Indexes on SQL Server Standard edition

texts:
We have SQL Server 2008 R2 Standard on a Windows Server cluster. I want to defrag all indexes on a database.

What is the best approach to disable all connections and allow the rebuild of indexes?

I might not be able to shutdown web servers. I doubt single user mode would work as my connection might be kicked out. I also doubt restricted user mode would work as the application logs in as dbo.

One solution that comes to mind is disabling the SQL Server port but not quite sure. Any suggestions?

Edit: I think of setting the database as single_user\restricted_user and connect via DAC and run the defrag job from the command line. Do you think this would work?


texts: We have a alert table which have deviceID as a foreign key. At present one alert just refers to one devive so things works smoothly with join. The problem start now when one alert now is to refer to more then one device. Thus we plan to keep a separate table with this alertID as foreign keys and devices ID in a new table say alertDetails. The issue now comes with this method is that there is no direct method to gain all the devices for a particular alert. Is there any solution or we have to run two separate queries where first one check if a particular type of alertCode(referring to more then one device) then run next query.


texts:
I'm trying to set the server to force encryption when I hit apply the message saying that it will take affect after restarting pops up, but when I open the box again its back to NO as nothing was.

Also my self signed trusted certificate is not in the list. I'm not sure if thats the same issue or not.


texts: select from mulitple values using ANY in PostgreSQL

texts:
I am running a query on Postgres table as below:
      Column      |            Type             |     Modifiers
------------------+-----------------------------+--------------------
 status           | character varying(16)

select FROM incoming_requests WHERE COALESCE(TRIM(status), '') IN ('','OK','ERROR');

This seems to working fine but below not
select FROM incoming_requests WHERE COALESCE(TRIM(status), '') = ANY ('','OK','ERROR');

ERROR:  syntax error at or near ","
LINE 1: ...quests WHERE COALESCE(TRIM(status), '') = ANY (" ","OK","ERR...

select FROM incoming_requests WHERE COALESCE(TRIM(status), '') = ANY (" ","OK","ERROR");

Also suggest if there is any difference in performance using ANY or IN


texts: Creating an incremental database copy in PostgreSQL

texts:
Virtual machine software like VirtualBox allow one to make incremental VM clones. That is data, once "touched" (opened writable), will be copied and stored in the incremental cache of the new clone.

I am searching for the same option for a Postgres DBMS.

How can I setup an incremental database copy, where entries are read from the original database, and touched/modified rows from the local copy?

If not on the DBMS level, how can I emulate such behavior at the file-system/storage level using a separate DBMS instance?

Background: The idea is to utilize the powerful database-server yet without incurring much resource overhead for a staged/developer database-copy.


texts: Storing multi-select data with single-select data

texts:
I've got a database with some multi-select fields.  To keep it simple, let's just say those fields are MatchGender and Interests.  I want users to be able to find people who match (hence Gender is a multi-select; they can choose to match against one or both genders) and interests/hobbies.  Along with those fields, there are several "single-select" fields such as the user's city, state, their gender, etc...

I'm trying to figure out the best way to store this data so it's easily searchable.  Do I store the multi-select data in a single field that is comma delimited?  Do I store it in a separate table with a UserID?  Pros/Cons of each?

By the way, this is a SQL Server back-end database that will be accessed through an ASP web interface.


texts: Cross-Database Impersonation Fail for Service Account

texts:
I would like to enable an application that dumps a big ol' heap of data into a Staging area to be able to kickoff the stored procedure that transforms & loads the data into the Production area. If possible, I'd like to not grant the application any access to the production area.  Unfortunately, EXECUTE AS seems to be failing me. For reference, the DBs in question are running on 2008 R2, but I'm recreating the behavior on my 2016 localhost. As well, the service account in question is a domain credential, but I've recreated the behavior with a SQL Auth credential for portability.

The Setup

use [master]
create database Prod;
create database Stage;
create login ServiceAccount with password='Password1234';
go
use Prod
create table tbl1 ( i int );
insert tbl1 select 1;
go
use Stage
create user ServiceAccount for login ServiceAccount;
go
create proc spLoadStageToProd as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go
grant execute on spLoadStageToProd to ServiceAccount;
go

The Testing

From the Stage database, exec spLoadStageToProd; succeeds. Predictably, exec as login = 'ServiceAccount'; exec spLoadStageToProd; fails with the error...

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 21]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

Okay, so I'll try execute as owner so that the proc has the permissions it needs to do the work but the (developer who has the password for) ServiceAccount doesn't get to muck about in my Production area.

alter proc spLoadStageToProd
with execute as owner
as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go

Shoot! Well now a simple test from Staging of exec spLoadStageToProd; returns the error:

Msg 916, Level 14, State 1, Procedure spLoadStageToProd, Line 5 [Batch Start Line 35]
The server principal "DOMAIN\peter" is not able to access the database "Prod" under the current security context.

"DOMAIN\peter" is of course me: the doof with sysadmin server role...

Well that's weird enough... I tried alter authorization on object::spLoadStageToProd to dbo; but the same error (Msg 916) pops up. What's more... exec as login = 'ServiceAccount'; exec spLoadStageToProd; now returns...

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 37]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

Screw it! alter authorization on object::spLoadStageToProd to sa;...

Msg 15151, Level 16, State 1, Line 41
Cannot find the user 'sa', because it does not exist or you do not have permission.

...

...

...sad.

So... clearly I'm misunderstanding something fundamental. I tried enabling cross-db ownership chaining just in case that was the sticky wicket but no luck. After fruitless googling and a quick scan of these two dba.se questions, I'm pretty stuck. Where am I going wrong?

How do I get this SProc to run on elevated cross-database permissions regardless of who calls it?

The project that precipitated this question is an iterative deprecation so I think cert-signing may end up being a rather large administrative overhead as time goes on.

If it is possible to use this stored proc as a wrapper to execute restricted actions agnostic of who calls it, that's the end-goal.

I am confident that the SProc cannot be improperly modified, so anything that executes from inside it may be highly permissioned - in the hopes that I don't need to administer perms for the ServiceAccount one-by-one.

Courtesy Cleanup Script

revert;
use [master]
go
drop database Prod,Stage;
drop login ServiceAccount;
go


texts: Postgresql: percentile_cont interpolation of values

texts:
I have a couple of questions regarding percentile_cont in Postgresql. I would like to know which algorithm it uses for interpolating values when using percentile_cont ?
My second question is why is it that we have to use order by ? It just seems weird to me that it doesn't order values by default when using percentile_cont? I fail to see why this choice has been made. Would someone please explain the reason behind such a choice?


texts: Multiple plans (with/without missing index) in query store for same query

texts:
I'm a newbie regarding the query store and have some problems understanding what I'm seeing.
We have a third-party application (running on SQL Server 2016 Enterprise SP1 CU7) that uses the query-hints KEEP PLAN and KEEPFIXED PLAN. When I do some monitoring in the querystore I see that some queries has multiple plan ids in the plan summary window.

Why can a query that uses KEEP PLAN and KEEPFIXED PLAN have multiple plans (sometimes completely different, sometimes the same)?

And the second question, if there are plans that look exactly the same (same physical operators, same set options, same queryhash), how can one plan have a missing index and the second plan not?

This are two anonymised plans: Plan 1 Plan 2


texts: How can I migrate data from on PostgreSQL database to another PostgreSQL database?  Is there any technique to migrate data with SQL scripting?


texts: How to link Similar-but-distinct Models of Medical Imaging Data?

texts:
I'm tracking a set of medical images in a logbook-type application, and am having a problem with how to model my records. The problem is that for every group of images collected, there are two similar, but distinct ways of recording data about a given scan: the first being done by a study coordinator at the scanner, writing description, order and comments about each image, and the second by scanning the actual images' metadata a day or so later when they are transferred to us and have become available. Both models (the image metadata and the hand-written log) are about the same thing (each image), but are entered at different times.

My question is: How should I connect the two models? Since they would both be the same order belonging to the same scan, I can match them up programmatically, but that seems like it would be a lot of overhead, and simultaneously not very flexible. I could connect them with a join table or a foreign key for one or the other, but that might lead to some internal inconsistencies. I can't model them as exactly the same model, because there are some extra tasks that behave like images but aren't images, in the hand-written log, and they don't have images or metadata.

Is it better just to be rigid and link them programmatically, without any keys relating them? The link would depend on the data being correct (but I guess that's always the case).


texts: "To many rows being processed at one. Reduce and try again." error in SQL Server Management Studio

texts:
I get this error when trying to update some rows from SQL Server Management Studio v18.5:

Msg 50000, Level 16, State 1, Procedure UPDATElimit, Line 7 [Batch
Start Line 0] To many rows being processed at one.  Reduce and try
again. Msg 3609, Level 16, State 1, Line 1 The transaction ended in
the trigger. The batch has been aborted.

How can I bypass this limitation?
Trigger code:
ALTER TRIGGER [dbo].[UPDATElimit]
on [dbo].[table]
FOR UPDATE
AS
BEGIN
    IF (select count(*) from inserted) > 1000
    BEGIN
        RAISERROR('To many rows being processed at one. Reduce and try again.', 16, 1)
        ROLLBACK
    END
END


texts: How to find if SQL server backup is encrypted with TDE without restoring the backup

texts:
Is there a way to find from the SQL Server Backup file or MSDB tables if the backup is encrypted with TDE without trying to restore the backup file?

Thanks


texts:
I have a application development requirement to lock a database record for a user while the user is inside the record. Then unlock it after they are done. Is there a way to do this at the database level instead of the code level? Should this be done at the database level or code level?

When I say lock I mean other will be able to view but not edit.


texts: Exclusive lock vs atomic(?) increment/decrement in MySQL

texts:
We're working on implementing an account with virtual currency in our app and we need to update account balance (classic, yeah?).

Let's say we have an accounts table with user_id and balance fields.
user_id is unique identifier for the sake of demonstration here.

First, classic solution with placing an exclusive lock to user's account row:

START TRANSACTION;
-- select user's account and lock it
SELECT * FROM accounts WHERE user_id = :userId FOR UPDATE;

-- Check balance in app.
-- If balance is not enough to do the transaction
-- -- Rollback. Show user an error with remaining balance.
-- else
-- -- update account balance in db
UPDATE accounts SET balance= :newBalance WHERE user_id = :userId;
-- insert transaction
INSERT INTO transactions (...) VALUES (...);
COMMIT;

Now what if we try something different, like this:

-- calculate amount to be withdrawn from user's account in app

START TRANSACTION;

-- Update balance only if new balance will be positive
-- Can be done with IF, CASE or an extra WHERE clause
UPDATE account
SET balance = IF(balance >= :transactionAmount, balance - :transactionAmount, balance)
WHERE user_id = :userId;

-- Process result of above query in app.
-- If 0 rows were updated (balance was not enough)
-- -- Rollback transaction, read new balance and show an error to user with updated balance.
-- else
-- --insert transaction and commit
INSERT INTO transactions (...) VALUES (...);
COMMIT;

I tried the 2nd solution with 2 connections running queries manually, seems to work as expected. Transaction isolation level was the default one - repeatable reads.
Is it reliable? Any pitfalls? What about performance? Why the 1st solution is more widely suggested?

UPD: modified 1st example to be more clear about the logic executed in app between SELECT and UPDATE queries.


texts: Why didn't this statistics object on a filtered index auto update?

texts:
In a SQL Server 2012 SP1 instance, I have a filtered index on the PK column of a table that looks like this:

CREATE INDEX [RF_IXF_Orders_OrderNumber] ON [dbo].[Orders]
(
    [OrderNumber] ASC
)
WHERE [OrderSource]='MO'
AND [Cancelled]=(0)
AND [NumItems]>(0)
AND [OrderDate]>'2014-05-15';

The Row Count (dm_db_partition_stats.row_count) of the index is 8416, the Rows Changed (sysindexes.rowmodctr) is 16803 (193.6%), and Auto Update Statistics is True. There have been 8400 user_scans and 50,088 user_updates. The last user_scan was today, yet the statistics haven't been updated in about a week.

Why wouldn't the statistics have auto updated?


texts: Applying Index happens in memory or disc?

texts:
Say I have 10k records in Customer table (MySQL or oracle) which has 10 columns. One is ID(primary key) and another is name(created index on it)

Now I search for word customer whose name is Alex. My understanding is first DBMS will determine the row location for record name Alex and then fetch those
rows in memory or it is other way round(where rows are fetched in memory then indexes are applied ?)


texts:
I am attempting to find a reliable service that does remote MySQL Database Backup.

Price is no object, unless it's absurdly expensive. I understand this question doesn't really have an answer, and I apologize for that.

Iam just looking for suggestions on ways I can check out for myself.

If this isn't the right place for this question, you can, you know, insult me or something :)

Thank you!
Quintis