What is the best way to check for matching sets of rows? I was watching this video yesterday on how unlikely it is to shuffle a deck of cards the same way twice:

https://youtu.be/czSdEhtXwm4?t=300

And I thought to myself "My SAN adminsitrator still has some hair left."

So I built a quick deck of cards:

        declare @card int
        create table deck (card int)

        set @card = 1
        while @card <= 52
        begin
        insert into deck (card)
        values (@card)
        set @card += 1
        end

Shuffled them up:

        declare @shuffleID bigint
        create table shuffles (shuffleID bigint, position int, card int)

        select @shuffleID = isnull(max(shuffleid),0)+ 1 from shuffles

        while @shuffleID <= 10000

        begin

        insert into shuffles (shuffleID, position, card)
        select @shuffleID, rank() over (order by newid()), card from deck
        order by newid()

        set @shuffleID += 1

        end

Then saw how closely I could match them:

        select s1.shuffleID, s2.shuffleID, count(1) as match_count from shuffles s1
        inner join shuffles s2 on s1.position = s2.position and s1.card = s2.card
            and s1.shuffleID != s2.shuffleID
        group by  s1.shuffleID, s2.shuffleID
        order by count(1) desc

The whole script looks like this:

        if object_id('dbo.deck') is not null begin drop table deck end
        if object_id('dbo.shuffles') is not null begin drop table shuffles end

        create table deck (card int)
        create table shuffles (shuffleID bigint, position int, card int)
        CREATE NONCLUSTERED INDEX [huehuehue]
        ON [dbo].[shuffles] ([position],[card])
        INCLUDE ([shuffleID])

        declare @card int,
        @shuffleID bigint,
        @sql int
        set @card = 1
        while @card <= 52
        begin
        insert into deck (card)
        values (@card)
        set @card += 1
        end

        select @shuffleID = isnull(max(shuffleid),0)+ 1 from shuffles

        while @shuffleID <= 1000

        begin

        insert into shuffles (shuffleID, position, card)
        select @shuffleID, rank() over (order by newid()), card from deck
        order by newid()

        set @shuffleID += 1

        end

        select s1.shuffleID, s2.shuffleID, count(1) as match_count from shuffles s1
        inner join shuffles s2 on s1.position = s2.position and s1.card = s2.card
            and s1.shuffleID != s2.shuffleID
        group by  s1.shuffleID, s2.shuffleID
        order by count(1) desc

The highest match I've got out of 1,000 shuffles is 8.

Question: What would be the fastest way to shuffle a deck of cards the same way twice?

What is the best way to check for matching sets of rows?

Generally, inner join on a key

  Probability of of the shuffling being the exact same?

A big number as described in a few places

1 in 806,581,751,709,438,785,716,606,368,564,037,669,752,895,054,408,832,778,240,000,000,000

  What would be the fastest way to shuffle a deck of cards the same way
  twice?

One way would be to just use a single table. I use a tally table for the initial insert to speed that up, and also use two CTE's for the shuffle. Is it the fastest? Not sure, but it's pretty quick!

drop table deck;
create table deck ([card] int primary key, pos int);

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
cteTally(N) AS
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
    )

insert into deck (card,pos)
select   card = N
        ,pos = rank() over (order by newid())
from cteTally where N < 53;

;with shuffle1 as(
select card
       ,pos =rank() over (order by newid())
from deck),

shuffle2 as(
select card
       ,pos =rank() over (order by newid())
from deck)

select s1.card, s1.pos as s1, s2.pos as s2
from shuffle1 s1
inner join shuffle2 s2 on s1.card = s2.card
where s1.pos = s2.pos

Takes about 4 milliseconds on my machine for the initial and follow on shuffles, on average.

STATISTICS TIME FOR ENTIRE SCRIPT

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 2 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 4 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(52 row(s) affected)
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 4 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


How to make most of aws? I'm new to aws (just one day old). I'm impressed with aws free tier and want to utilize to its best. As of now I'm done with installing mongodb on aws micro instances and mongod EC2 instances are running fine. Now, I want to test my use case and unfortunately couldn't find much details on use case implementation related to mongodb shards, replica sets, traffic, load balancing, logs, cluster monitoring, metrics etc.,

It would be great if someone who has deployed mongodb databases on aws clusters and can share their tips and best practices here.

use case: I'm planning to deploy ProductCatalog and geojson apps on aws windows and linux micro instances.

Thanks in advance!

AWS has an excellent guide for deploying MongoDB on AWS.

https://d0.awsstatic.com/whitepapers/AWS_NoSQL_MongoDB.pdf

If you are looking for specific instructions on how do deploy MongoDB on AWS be sure to check this out:
https://docs.mongodb.org/ecosystem/platforms/amazon-ec2/

Also bear in mind that AWS micro instances are too small for any production use of MongoDB. If your goal is simply learn MongoDB and AWS the micro instances maybe fine, but for any performance/stress testing the results for a micro  instance would not be realistic.

Good luck!


How are the system health extended events files rolling over? I see the default system health session events within SQL Server are 5 MB each with 4 files in total.

We are trying to fetch data from the system health in historical events but for some critical production servers they just keep rolling over very fast. For some they stay there even for weeks

How is the mechanism working in SQL Server of rolling over the files and is there a way I can check when and how this is happening?

Edit@ Per Answer

Event is not restarted
Server is also not restarted and it happens per scheduled once a month

Therefore i am thinking there could be possibly lot of unwanted events being captured in system health. How can i find them and filter them out in this system health ?

Also, should we be increasing file sizes say 200 MB 4 files or just keep small size files like 10 MB 80 files? Which one is better?

Update@ I've already marked this an answer, but while checking another thing that popped out:

The files are not filling in sequential order ; say i increased to 10 files to 10 MB each. we see 1-6 files couple of days old seems all filled with 10 MB, while file 7 and 8 for the same day is 2 MB and file 9 and 10 filled up completely 10 MB. I guess this is expected, just curious why it's like this?

How is the mechanism working in SQL Server of rolling over the files

The files will roll over when any of the following occurs:

the file reaches its max size (in this case, 5 MB)
the XE session is stopped and restarted for any reason

This could be someone actually running the command:
ALTER EVENT SESSION [session_name]
ON SERVER
STATE = STOP;

Or it could be that the server or SQL Server instance was restarted (this seems unlikely given you mentioned this is a critical production server).

and is there a way I can check when and how this is happening?

If all of your files are at or around 5 MB, your server is just generating a lot of events.  I have a production server that's not very busy, and it still takes less than 24 hours to fill up one of the 4 XE files with 5 MB of events.  Here's a screenshot, check out the modified dates on those files:

To see what events are included in the event session in SSMS, you can go to Management → Extended Events → Sessions → right-click "system_health" and choose "Script Session as" → "CREATE to" → "New Query Editor Window".
However, note this from the Microsoft docs article Use the system_health Session:

We recommend that you do not stop, alter, or delete the system health session.

So your best bet would be to create a new XE session with just the subset of events you want, and the specific Event File Target settings that work for your system.
If the files are less than 5 MB, then the server or event session is being restarted.  You can check the SQL Server error log for when restarts are happening, and you'll have to check with those who have CONTROL SERVER permissions to see who is stopping the XE session if that's the case.

Also, should we be increasing file sizes say 200 MB 4 files or just keep small size files like 10 MB 80 files? Which one is better?

It depends.  Querying (reading) from a bunch of different files can be inconvenient.  So I'd lean towards a lower total number of files, and just increasing the size of each one.
That being said, I've never tried parsing through a really large XE file, so there might be issues with that.  You'll have to test and see which one is better for your system.


Can inserting new rows make the RDB not use an index? Say I have a table with 10000 records. I then delete them and create them again. This is done with "normal" sql, row by row (not some sort of bulk insert (BCP in sqlserver lingua)).

Does the RDB(Oracle in my case) still consider the indices on said table usable, or has it noticed many rows changed and stops using it?

(If the example above seems wierd here is a similar: Say I have a table with 10000 records with nice evenly divided data. I then insert 10000 new records with similarly evenly divided data.)

First, let me say that you haven't listed the version of your RDBMS, so I'm going to assume you're running a version that utilizes the Cost-Based Optimizer (CBO), as opposed to the Rule-Based Optimizer. The CBO was introduced in the 10g series of databases, so I think this is a safe assumption. If you're still running a 9i or earlier version, my answer won't help you.

So your question is "will inserts stop my RDB from using an index?". The easy answer here is no, they won't. Your database will continue to use the index it used before, as a_horse_with_no_name mentioned in the comments that the rows will be maintained automatically in the index without any extra effort from you.

However, there is a chance that the volume of changes could impact the performance of using that index, which is something that you need to be aware of. How?

Take the total row count of all rows that could be included in an index (remember we cannot index nulls). Then consider the number of inserts you've done - is this number >= 10% of the existing rows? If the answer is yes, then your statistics are now considered out-of-date. Luckily, by default Oracle's later versions installs an overnight job (usually running around 10pm local server time) that will automatically gather statistics on any table & indexes where it senses a 10% or greater change in the count of rows.

But, if you are doing something outside of ordinary processing, say adding a significant amount of rows due to a one-time data load, you may very well need to gather stats yourself sooner than the automatic job. Also, in my experience, the automatic job only has a certain window of time in which to gather stats, so it's possible that it won't be able to gather stats on all objects if large amounts of data was manipulated across many objects.

My point here is that changing data volumes significantly in a short period of time can lead to performance problems not because your indexes will be bad, but because in a Cost-Based world, the Optimizer will have bad information, and will make bad decisions.

I bring this up because you seem focused on indexes, which are only one tool in a much larger toolkit to having a well performing database.

HTH.


How to enter records with keys to an initially empty B+ tree? Show the result of entering the records with keys in the order (1, 2,
  3, 4, 5) to an initially empty B+ –tree  of order m = 3. In case of overflow, split the node and do not re-distribute keys to neighbors. Is it possible to enter the records with keys in a different order to have a tree of less height?

  From Relational DBMS Internals, chapter 5: Dynamic Tree-Structure Organizations, p.50

I'm not good on this yet I tried to do ≤ on the left and > on the right :

Until insertion of 1,2 :

Then as far as we have to split the node and not to redistribute keys to neighbors (I understand it as son-nodes) I inserted only on the right of cell with 2 :

And I continued doing the same with when inserting 5 :

But this is quite odd, I never saw empty nodes like these ... And I don't know if it respects some very basic B-trees properties :

each node has at most (m-1) keys and at least (⌈(m/2)⌉-1) keys unless a key can be empty and I would understand the key as a "pointer".

First attempt : error on the order revealed an ambiguous tree

At the beginning I misunderstood what an "order" was (the max number of children per node). So I thought a node could have three spaces (and therefore 4 children. I was creating a tree of order 4 I think :

Until insertion of 1,2,3 :

Insertion of 4, as far as we have to split the node and not to resdistribute keys to neighbours (which seems contradictory) I would let 1,2,3 and 4,5 on the right leaf after 3 :

I think you have your page creation upside down. When a node splits it doesn't create more nodes down the hierarchy (son nodes in your nomenclature). Instead it creates more upwards, toward the root. As the book says

  Note that the growth is at the top of the tree, and this is an intrinsic characteristic of a B–tree to ensure the important properties that it always have all the leaves at the same level, and each node different from the root is at least 50% full.

(My emphasis.)

From the linked ebook:

  Definition 5.1 A B–tree of order m (m ≥ 3) ... each node contains at most m − 1 keys

The exercise is for m=3, so at most 2 keys per node.

The first two keys are easy -- they go into the first page:

A:[1,2]

I'm going to use ASCII art. I'll label each page in the sequence they're created and show the keys/ pointers within the page. So page P containing key values k1 and k2 will be P:[k1,k2].

Now key 3 comes along. According to Section 5.2.1 ... Insertion, the first task is to search. This determines key 3 should be on page A - the only page we have. Further "if [that node] is full, it will be split into two nodes." The page is full so it must split. We now have

A:[1,2]    B:[3, ]

But this is not a tree! As the book says:

  the pointer to [the new node], .. is inserted into the father node .. of [the current node], repeating the insertion operation in this node [i.e. the father node]. This splitting and moving up process may continue if necessary up to the root, and if this must be split, a new root node will be created ..

(My emphasis to show processing continues up the tree toward the root, not down toward leaves.)

So we must put a pointer to the new page (B) into the father of the current page (A). There must be a new root node:

      C:[2,3]
      /     \
A:[1,2]    B:[3, ]

I have the pointers in non-leaf pages pointing to the highest value in a child (son) node. Your linked text may do this differently but the result will be equivalent.

Key value 4 arrives; following the algorithm we search to find which page it should be on. It should be page B. There is room for it so we update that page and the pointer on page C:

      C:[2,4]
      /     \
A:[1,2]    B:[3,4]

Next we insert key 5. It should go in page B but it's full. Therefore it splits

      C:[2,4]
      /     \
A:[1,2]    B:[3,4]   D:[5, ]

We must update the father node. It, too, is full so it splits:

      C:[2,4]    E:[5, ]
      /     \         \
A:[1,2]    B:[3,4]   D:[5, ]

The split propagates up and a new root node forms:

            F:[4,5]
            /     \
      C:[2,4]    E:[5, ]
      /     \         \
A:[1,2]    B:[3,4]   D:[5, ]

By growing upwards the tree maintains identical depth in every branch. This is important for predictable performance. (Some say the B in B-Tree stands for "balanced" for this very reason.)

As for the second part -- "Is it possible to enter the records with keys in a different order to have a tree of less height?" With 5 keys and two keys per node we need at least 3 leaf nodes to hold all values and a height of 3 to form the tree. So my arrangement is optimal for the given data, sequence and algorithm.

The book uses a very different pointer arrangement to what I use, and a different page split arrangement. This will be significant, leading to part-full pages. That there is a section on page 42 called "Data Loading" that shows how fuller pages can be achieved by loading out of key sequence supports my hunch. However, I hope I've given you sufficient pointers and you'll be able to use the book's pointer structure to work that out for yourself.

I've come across this interactive simulation of how a B-Tree grows.


How can I start the SSRS 2016 Report Server? I restarted my SSRS 2016 server. When I navigate to the Web Portal URL I get a "Service Unavailable - HTTP Error 503. The service is unavailable." message. How do I start the SSRS 2016 Report Server?

I found the solution. There is a "SQL Server Reporting Services (MSSQLSERVER)" service that manages, executes, renders, schedules and delivers reports. It was set with an "Automatic" Startup Type. After a reboot the service was indeed running but I was still getting a 503 Service unavailable error. I found a blog that says the Startup Type needs to be "Automatic (Delayed Start)". Once I changed the Startup Type to "Automatic (Delayed Start)" and restarted the service the report server started working.


ORA-24237: object id argument passed to DBMS_UTILITY.INVALIDATE is not legal I have had invalid public synonyms in my database. Some of them references to ordinary schema objects and others to sys schema objects.

I compiled all these public synonyms by the following command:

alter public synonym mysynonym_name compile;

My supervisor suddenly told me that I should not have compiled SYS schema synonyms. I tried to invalidate them by the following way:

exec dbms_utility.invalidate(444849);

Where 444849 is the synonym object ID. But got the following error:

ORA-24237: object id argument passed to DBMS_UTILITY.INVALIDATE is not legal
ORA-06512: at "SYS.DBMS_UTILITY", line 1053
ORA-06512: at line 2

Why?

Can compilation of these synonyms cause some problem?

Thank you a lot, I am waiting for your recommendations and answers. Please share your knowledge with us.

You don't have the create privileges on the object being invalidated. But generally it is a very bad idea to invalidate objects in the sys schema. This will also invalidate all objects that reference them, so you might end up with lots of invalidated objects.


Translating ERD diagrams into SQL DDL statements I am currently establishing a database for Students and Courses using ERD.  The style of ERD I am using is similar to the diagram below from the ERD Diagram Question:

Note: The picture above is NOT my ERD, this is just the style of ERD I am using.

A simple CREATE statment could be used for the attributes of each entity, but what other DDL statements enforce relationships, weak entities, and multi-way relationships?

The way you can enforce relationships between tables is by creating a foreign key which is referencing a primary key from other table. Both keys are defined either creating or modifying the table by using CREATE statement or ALTER statement, respectively.


How to implement table purge with partition on SQL Server 2012? I've read this question:
How to remove a table partition

But it's not what I want.

I'd like to setup a table, using partitioning on a date/time column to have 1 month partitions. Afterwards, configure a job that executes montlhy and drops the partition(s) that have records over 9 months old.

Can I set this up using SQL Server 2012 Enterprise?

The goal is to reduce the amount of transaction log by only updating metadata.

When data is move or remove from a partition, it Insert and Delete rows which will result in (tons of) LOB_INSERT_ROWS and LOB_DELETE_ROW in the transaction logs.

The only option would be to truncate the partition but this option does not exist.
We can avoid it by only using Merge and Split on empty Partitions.

In the sample below, I will keep things shorter and only create data for the past 3 months (ie. August, September and October) but you can easily expand it to 9 months. August will be removed once data starts being added to November and so on with September and December...

Create Files and Filegroups:

I first create 6 files and file groups [Part_0] to [Part_5]:

Alter Database [Test] Add Filegroup [Part_0];
...
Alter Database [Test] Add Filegroup [Part_5];
Alter Database [Test] Add File( NAME = N'Part_0', FILENAME = N'...\Part_0.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_0];
...
Alter Database [Test] Add File( NAME = N'Part_5', FILENAME = N'...\Part_5.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_5];

Create Function and Scheme:

Create Partition Function [DateKeyPartFunction] (datetime2)
as Range Right For Values ('20150801', '20150901', '20151001', '20151101', '20151201');
Create Partition Scheme [DateKeyPartScheme] as Partition [DateKeyPartFunction]
To ([Part_0], [Part_1], [Part_2], [Part_3], [Part_4], [Part_5]);

Again there are 6 partitions. This will be explained later but this is mostly due to the need to have empty partitions.

Create Table and Clustered Index:

Because I don't know the exact design of your table, I will be working with this table:

Create Table dbo.DataPart(id int identity(0, 1), name char(1000), name_date datetime2);

and this Clustered Index:

Create Clustered Index IDX_Part On dbo.DataPart(name_date) On DateKeyPartScheme(name_date);

Dummy data:

This code creates over a million record in a range of dummy dates every 6 seconds from October to August(now):

With inc(n) as(
    Select ROW_NUMBER() over(order by (select 1))-1 From (
        Select 1 From (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x3(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x4(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x5(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x6(n)
    ) as x(n)
)
Insert into dbo.DataPart(name, name_date)
Select TOP(1000000) '', DATEADD(second, -n*6, getdate()) From inc;

Partitioned Data:

This will partionned as follow:

Id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150801'    0
2  [Part_1] >= '20150801'   < '20150901'    184042
3  [Part_2] >= '20150901'   < '20151001'    432000
4  [Part_3] >= '20151001'   < '20151101'    383958
5  [Part_4] >= '20151101'   < '20151201'    0
6  [Part_5] >= '20151201'                   0

[Part_1], [Part_2], [Part_3] contain data for August, September and October.
[Part_0] (ie. <'20150801') only exists because it needs an empty partition before we can empty [Part_1] (August) once in November. It would be data for July and before but must remain empty.
It make things easier to already have an empty partition for next month although it will remain empty until it reach November. This is the purpose of [Part_4] for November.
Partition Scheme needs an extra partition for everything outside of the right boundary. This is [Part_5] for December and beyond and it must remain empty as well.

Add November:

Once in November, new rows will go to [Part_4] and August data can be removed from [Part_1].
The only way to remove it without having to delete hundred of thousands of rows is to move [Part_1] away from the table:

Create Table dbo.DataPart_Temp(id int identity(0, 1), name char(1000), name_date datetime2);
Create Clustered Index IDX_Part_temp On dbo.DataPart_temp(name_date) On [Part_1];
Alter Table DataPart Switch Partition 2 to DataPart_temp Partition 1;

DataPart_Temp must be identical to DataPart (columns, indexes)
Because [Part_1] is move from DataPart to DataPart_temp, the clustered index on DataPart_temp must be created on the same filegroup: [Part_1]
[Part_1] is the second partition of DataPart and is switch  to the first and only Partition of DataPart_temp. All August rows are now in DataPart_temp.

the table is now partitioned as follow:

id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150801'    0
2  [Part_1] >= '20150801'   < '20150901'    0
3  [Part_2] >= '20150901'   < '20151001'    432000
4  [Part_3] >= '20151001'   < '20151101'    383958
5  [Part_4] >= '20151101'   < '20151201'    0
6  [Part_5] >= '20151201'                   0

Merge Partition:

[Part_0] and [Part_1] are now empty and can be merge:

Alter Partition Function [DateKeyPartFunction]() Merge Range ('20150801');

[Part_1] has been removed:

id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150901'    0
2  [Part_2] >= '20150901'   < '20151001'    432000
3  [Part_3] >= '20151001'   < '20151101'    383958
4  [Part_4] >= '20151101'   < '20151201'    0
5  [Part_5] >= '20151201'                   0

Add Next month:

Now that [Part_1] is not used anymore, it can be added to the Partition Scheme as the next available partition:

Alter Partition Scheme [DateKeyPartScheme] Next Used [Part_1];

And then [Part_5] (December, >= '20151201') can be splited:

Alter Partition Function [DateKeyPartFunction]() Split Range ('20160101');

Since [Part_5] is empty, nothing has to be move. The other half of [Part_5] will go to the next available partition which is [Part_1]:

Id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150901'    0
2  [Part_2] >= '20150901'   < '20151001'    432000
3  [Part_3] >= '20151001'   < '20151101'    383958
4  [Part_4] >= '20151101'   < '20151201'    x rows in November
5  [Part_5] >= '20151201'   < '20160101'    0
6  [Part_1] >= '20160101'                   0

Clean Up:

DataPart_temp can now be truncated and then dropped (or at least drop its Clustered Index).

Add Following months:

In December, Partition ID 2 must be move away ([Part_2]), merged with partition 1 and then added back before January split.

In order to automate this process, you will have to look for the name of the filegroup for Partition 2:

And then create dynamic SQL to:

Add Clustered Index to DataPart_temp on [Part_X] (similar to DataPart)
Switch [Part_X] from DataPart to DataPart_temp
Merge Partition 1 and 2
Add [Part_X] to DataPart
Split the last partition
Truncate DataPart_temp
Drop DataPart_temp or its Clustered Index

If you use fn_dblog, you should see that transaction logs are minimal.


How do I enable md5 authentication for only one user? I just installed Postgres 9.2 on CentOS 7. I want to keep peer authentication for all my users except one, which I would like to be able to access via "psql" through any user's account. I tried editing my /var/lib/pgsql/data/pg_hba.conf to be this

local   all             all                                     ident
local   all             scale                                   md5

but I'm still unable to login with the user "scale" unless I'm in that user's account. What do I need to do to set up md5 authentication for this one user only?

pg_hba.conf looks only for the first match. Try switching the order..

local   all             scale              md5
local   all             all                ident

You'll have to reload the conf

SELECT pg_reload_conf();

Or, using something like

service postresql reload


Abstract classes in SQL Server. Are they even possible? I have a very specific question that I am surprised no one has yet asked. It involves a concrete class model and SQL Server 2016 DDL. The main point is:
In my model, some classes inherit from a base class, let's say "BaseInfo".

This class is abstract. In SQL terms it means I do not (or do I?) want to create a separate table with rows (eg. CREATE TABLE "BaseInfo"..... I do however want some of my tables (actually quite a lot of them) to have the same columns (plus some of their own). In OOP terms I want some of my classes to inherit from that table definition. Is there a way to define this in DDL, or do I have to implement this manually?

Notes:

I have read all the articles that are to be found on TPH, TPT, TPC. They are too simplistic, they all feature the same Person-Student-Educator Model and do not match my case. What I probably want to do is TPC, but I want changes made to BaseInfo table to automatically apply to all child classes.
I will not be querying BaseInfo. I'd rather do a (rare) complex query involving some (or all) of the children.
Changes made to BaseInfo DDL (ALTER TABLE BaseInfo...) have to be reflected to the children (preferably automatically).
PostgreSQL shines here with the INHERITS statement. Is there any equivalent in SQL Server (I have done research and found nothing yet)?
In case I get no answer or have some other breakthrough, I will go the manual approach. Save a code snippet with the BaseInfo DDL and add it to the CREATE TABLE statement of each child table.
The answer must respect the conceptual model definition!

There is nothing like this in SQL Server.

What tool are you building and maintaining your schema in though, will you be using database projects? If you went the "manual" method, while there is no SQL specific templating for this in SSDT, you might be able to use T4 templates to do something similar.

If that interested you then another alternative for templating is to use BimlScript. It is used to programmatically generate SSIS packages but they've very recently started doing something similar to generate SQL DDL so you could check into that.


How do you update one side of a MySQL master-master replication setup? I have a master-master (circular) replication configured between two MySQL servers, call them West and East.  There is also a web app that interfaces with each DB, also on its own West and East server, such that the West web app talks to the West database and the East web app talks to the East database.  This all works fine during normal operations, but the problem arises when it comes time to do quarterly deployments.
In these deployments, almost always, there are database content updates and frequently there are database structure changes as well (e.g., adding a table, adding columns to a table, etc).  The web app is also updated at the same time so that it stays in sync with the database structure.
Thus far, we have not been able to perform a successful deployment without breaking replication, leading to a very messy situation and potential loss of data.  The way we planned to do deployments is as follows:

Shut down the West side web app and direct all traffic to the East side
Stop the East side slave
Deploy the West side web app + DB updates
Turn back on the West side web app, shut down the East web app, and direct all traffic to the updated West side
Start the East side slave
Deploy to East side web app + DB updates
Return to nominal

The issue seems to be around step 3.  When users are using the web app on the East side (thus modifying the DB) and the deployment is occurring on the West side (modifying that DB), this often stops replication from working, necessitating manual intervention, data copying, altering the pointers to the master logs, etc.  Is there a better way to do this to allow each master to be updated in isolation without risking replication getting out of sync?  I fear our setup is not correct and/or sustainable.
Important considerations:

There can be no downtime for the web app
The updates the users may be making while directed to one side must be preserved when both sides go back online

Also, please note that I am not a DBA, so I apologize if I failed to include any important details about the configuration settings.  I will try to update the question ASAP if needed.

At my company we run MySQL with circular replication. We do not stop replication during a deploy of either the app or schema changes.
We must restart MySQL Server during upgrades or some configuration changes, and replication naturally stops when the server stops, but we bring it back up immediately.
We use pt-online-schema-change to run schema changes without making the app lose access to the database. Apps can still run read or write queries while the schema change is running. It's not perfect; it requires a brief lock at the beginning and end of the task, and you can't have triggers on your table. Also it adds a significant query load to the database server while it's restructuring a table, and this may be enough to slow down the app (it depends on how sensitive the app is to load). But mostly it allows the app to continue working without requiring any downtime.
For the app deployments, we use multiple app servers in each datacenter. A deployment knows how to stop the app instances in a "rolling" manner so there's always a minimum number of them running during the deploy. So there's no need to swing traffic to the opposite datacenter.
Because app deployments and schema deployments are both asynchronous and hard to run absolutely together, the code and schema changes must be designed carefully so both the old and new code versions will work with both the old and new schema versions. For example:

No code can use SELECT * ... or INSERT INTO mytable VALUES(...) because if the schema change adds or drops a column, it would break the code.
Some schema deployments must be done in stages, for example add a new column and finish that deployment before any code can reference the new column.
Renames of columns or tables are forbidden, because it causes a chicken-and-egg problem.

With redundant app servers, online schema change tools, and a few modest restrictions on code style, almost all deployments can be done without downtime.


MySQL Workbench, how to refresh EER diagram? I'm using MySQL Workbench to design my MySQL database and model how entities would look like and how they would relate to each other. Now, sometimes I use Doctrine ORM. When using Doctrine ORM, I create classes/objects that are domain objects and can be persisted to a database. And through DocBlock Annotations I can express how to relate entities to each other.

With MySQL Workbench I can view an EER Diagram that shows me how objects are related, and by looking at symbols I can see relations clearly. The problem with this approach is that I need to generate database schema using Doctrine CLI. And then in MySQL Workbench I must create an EER model from an existing database. The thing is I change the database schema a lot, and to see the relations in MySQL Workbench I must create an EER model every time. This is time consuming.

Is there a way to just load an EER diagram from an existing database, and then, when the database schema changes, just refresh and see changes, so I don't have to generate the EER diagram for every little change?

I have used Navicat in the past, and there you can do that. When the database changes, just press F5 and relations change immediately in the ER diagram. The only problem with Navicat is there are no symbols between tables that show their relation. Only lines.

Is it possible to have that functionality in MySQL Workbench? If not, are there other tools with which I can see ER diagrams, and that refresh the diagrams as soon as I change the database schema?

From the MySQL Workbench 5.2 window, select Database -> Synchronize model which will allow you to synchronize the model (EER diagram) with the database (you need to create a saved connection) for it.

I recommend this method over just reverse engineering the model from the database since it maintains the layout of the EER diagram. One caveat is that you need to add new tables to the EER diagram manually.


Compare SQL Server high availability methods I want to provide high availability for my SQL Server. It is important that the service is back as soon as possible, more than making sure that data isn't lost.
My SQL Server 2008 R2 is running on a VMware machine.

Which of the following ways will provide the highest availability?

SQL cluster
AlwaysOn (is it worth upgrading)?
VM level cluster (HA / VMotion)
Windows server cluster
Any other method

Regarding mirroring, I see this comment:

  Database Mirroring (SQL Server)
  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use AlwaysOn Availability Groups instead.

What is replacing it?

Database mirroring is the fastest HA method for SQL Server - you can expect as little as 10 seconds (maybe less) downtime when manually failing over. Perhaps 40 seconds when an automatic failover occurs.  Automatic failovers take longer since they rely on the "timeout mechanism" to detect a problem.  Manual failovers are very quick since you are initiating the process, thereby removing the timeout.

Mirroring consists of two entirely separate SQL Server instances, utilizing a "shared-nothing" approach.  This means none of infrastructure required by mirroring is shared by more than a single instance.  The mirrored database(s) served by the instances involved in the mirroring partnership typically reside on separate storage subsystems - the mirroring process sends each and every transaction from the primary to the secondary instance thereby providing two physically independent copies of each mirrored database.

If you have a requirement for automatic failovers without data loss, which I highly recommend, you will need a mirroring witness instance (this can be a SQL Server Express instance, by the way).  The witness acts as a quorum to allow SQL Server to determine which of the two instances will actually provide access to the mirrored databases.

Database Mirroring has been deprecated since SQL Server 2012.  What this really means is at some point in the future, Microsoft will remove Database Mirroring from the product.  The pertinent point is "at some point"; Microsoft is almost never explicit about when they will actually remove support for deprecated items.  One good example is they removed support for the antiquated RAISERROR @errno @errmsg syntax in 2012.  This syntax has been on the "deprecated" list since around the year 2000 - it took them 12 years to truly disable support for this syntax.  Database Mirroring is supported in the as-yet unreleased SQL Server 2016 product.  Presuming Microsoft removes mirroring support from the next version of the product, which may come out in 2018, you could expect to be officially supported by Microsoft running on SQL Server 2016 until at least 2021, since Microsoft typically provides support for at least 5 years after the product is released to manufacturing.

One downside to Mirroring is each client needs to be aware that there are two possible hosts for the database; and needs to be coded to handle that.  There is a special entry you can provide through the connection string, namely "failover partner", that helps automate some of that; however the client still needs to be coded in such a way that it will re-try transactions gracefully during the failover period.
SQL Server Clustering provides high-availability by allowing one of several instances to "serve" the databases residing on the clustered instance.  SQL Server Clustering relies on Windows Server Failover Clustering, and as such they are essentially one-and-the-same for the purposes of comparing HA methodologies.  The process of failing over a clustered instance consists of:

  a. Shutting down the currently-running instance.

  b. Moving resources (disks and network connections, etc) from the currently-running instance to the next-to-run instance.

  c. Starting up the SQL Server instance on the next-to-run instance.  This startup process is exactly like starting any other SQL Server instance; the recovery process must be ran against each database on the instance, including roll-back and roll-forward of any transactions that were in-flight at the time the prior instance shut down.

Because of the steps I outlined above, the process of failing over a clustered instance is generally slower than failing over a mirrored database.
Always On is the technology Microsoft is pushing as the replacement for Database Mirroring.  It is essentially a hybrid of both mirroring and clustering, providing both the faster failover speeds of mirroring along with the useful management aspects of clustering.  With Always On, clients do not need to be aware of the multiple instances of SQL Server involved; they simply "speak" to the virtual network interface.  Always On is a great technology, however it can be a bit daunting to setup, and it requires Enterprise Edition, among a number of other prerequisites.
HA at the hypervisor level - SQL Server does not have control over any aspect of this process.  If vMotioning a server causes its response time to slow, you may get client timeouts, etc.  If the SQL Server is part of critical infrastructure, which it almost certainly is if you are looking for HA, why would you take a chance on some process that SQL Server cannot understand?


Sync two databases on different servers I can't pick the right solution for my below requirement. Can you people please advise?

We have two SQL Server 2012 database servers:

First one is in Texas (USA). Our client will use this for UAT.
Second one is in Chennai (India). This one we will use for development/local testing.

While testing our application, the client will find issues/bugs in UAT environment, so we need to replicate to our development environment for further analysis, and to develop fixes for the bugs.

We are planning to sync two databases present in two different places. I am looking to synchronize the database in single direction. It should support write operations.

I have found a few potential solutions:

Transaction Log Shipping

In Transaction Log Shipping the secondary database is always in read only/restoring mode, so we can not perform any write operations. I need to do write operations to provide fixes for the issues/bugs. So I think log shipping will not be useful.
Database Mirroring and Always On High Availability

As a junior DBA, I don't have much knowledge of these. Please advise if these would fit for me.
Scheduling a daily job

Taking a backup of the UAT server and restoring it to our local development server, copying the database backup via FTP.

Can you please advise which solution will be the best fit for this?

Edit:-
           I would like to move data changes happening in Database 1(Server 1) to Database 2(Server 2). After moving changes to database 2(Server 2) it should be writable mode. And i don't want to move changes happening in database 2(Server 2) to database 1 (Server 1). So it's a single direction sync.

Below image will gives better understanding.

User acceptance testing (UAT) usually involves clicking around in an application, which usually means making changes and testing them.

This is especially true if you think you're going to "replicate those issues/bugs to our development environment" - it sounds like you're thinking about synchronizing in both directions.

That requires a writable database, and unfortunately, that rules out transaction log shipping, database mirroring, and Always On Availability Groups. All of those scenarios are designed for read-only secondary databases.

If both sides are going to be changing the data, then in theory, you could use SQL Server's replication techniques (merge, peer-to-peer, bidirectional transactional replication) to push data in both directions. In practice, your developers will be making table schema changes to the development database, and that's likely going to break replication.


Oracle: How to create a "reverse" index primary key? I can create a primary key for a table and then reverse it with the command

alter index T_PK rebuild reverse online;

How can I create it reversed?

The primary key index (including index parameters) can be specified with the using index clause.  You can include the reversed specification there.

create table T(X number primary key
                 using index (create unique index T_PK on T(X) reverse));


Pivot time series into date period columns with array aggregate cells Given a time series of events where each event has a successful or unsuccessful outcome, how do I pivot the ratio of events by entity and time period columns with an aggregate array cell value? I suspect this can be done with crosstab query and array_agg.

Akin to uptime status reports, I'm trying to calculate something like this in SQL:

The volume of data is small enough that I could do the reduction client-side in a general purpose language, but it would be nice to do this efficiently in the database for a larger amount of data.

Time Series Example

+-----------------------------------------+
|  DateTime             Entity    Result  |
+-----------------------------------------+
| 2016-01-01 11:00...  :a        :success |
| 2016-01-01 17:00...  :a        :success |  -- two events for :a on same day
| 2016-01-01 11:01...  :b        :fail    |
| 2016-01-01 11:03...  :c        :success |
| 2016-01-01 13:00...  :d        :success |  -- only one event for :d
| 2016-01-02 11:00...  :a        :success |
| 2016-01-02 11:01...  :b        :fail    |
| 2016-01-02 11:03...  :c        :success |
| ...                                     |
+-----------------------------------------+

Desired Aggregate

Where each value cell after the key column is an array with shape [cnt_total cnt_success cnt_fail]:

+-----------------------------------------------+
| Entity     2016-01-01  2016-01-02  2016-01-xx |
+-----------------------------------------------+
| :a            [2 2 0]     [1 1 0]         ... |
| :b            [1 0 1]     [1 0 1]         ... |
| :c            [1 1 0]     [1 0 1]         ... |
| :d            [1 1 0]     [0 0 0]         ... |
+-----------------------------------------------+

To keep things simple, this report will never need more than 10 date window columns and I can dynamically template the SQL pivot output.

If I had to decompose this transformation:

Aggregate time series by date window (hour/day/week/month/quarter/year) and Result.
Accumulate the counted aggregate into some data structure like a hash-map or array of [count_total count_success count_fail]
Return the accumulated two-dimensional result as [entity period1 period2 ...] for displaying % on the client.

This question is old, but you haven’t accepted any answer yet, so I will add another one.

You need some aggregation of your data, and a pivot table. The most elegant way to do the former is by means of a CTE, and the most elegant way to do the latter is with CROSSTAB; however, as of Postgres 9.6, and unlike in other DBMS, you cannot reference a CTE from CROSSTAB. I will show an example for each of the two possible ways out: 1) Use a CTE, and reimplement a poor man’s pivoting on your own 2) Instead of a CTE, create a view once for all and reference it in your CROSSTAB queries. In both cases you’ll have to issue only one query per report, and you won’t have to create any temporary table.

The general problem of pivots is that, in pure SQL, you cannot define a query whose result has a variable number of columns, and you cannot define column headings dynamically. If you want that, you have to build your query with a procedural language, either server-side (plpgsql, as in Abelisto’s answer) or client-side (PHP, java, whatever...). My examples below are in pure SQL, so they have a fixed number of days (three, as in your example data), with fixed column headings ("day 1", "day 2", "day 3"), but they are built in a way that minimizes the needed edits when you change them.

First, the initial data. I started from the ones joanolo used, but my approach is different, because I use SMALLINT instead of BOOLEAN for result. My reason for doing this will become clear in the following.

CREATE TABLE time_series (
  date_time TIMESTAMP NOT NULL,
  entity TEXT NOT NULL,
  result SMALLINT DEFAULT 0 -- 1 means success, 0 failure.
);

INSERT INTO time_series VALUES
  ('2016-01-01 11:00', 'a', 1),
  ('2016-01-01 17:00', 'a', 1),
  ('2016-01-01 11:01', 'b', 0),
  ('2016-01-01 11:03', 'c', 1),
  ('2016-01-01 13:00', 'd', 1),
  ('2016-01-02 11:00', 'a', 1),
  ('2016-01-02 11:01', 'b', 0),
  ('2016-01-03 11:03', 'e', 1),
  ('2016-01-03 11:04', 'e', 1),
  ('2016-01-03 11:05', 'e', 1),
  ('2016-01-03 11:06', 'e', 0);

You only really need an array of two integers (a in my examples): a[1] (total count) and a[2] (success count). The failure count is simply a[1] - a[2] and the success percentage is 100*(a[2]::float)/a[1]. You can compute the total count by COUNT(result); if you define result SMALLINT you can simply use SUM(result) to keep track of the success count. If you store result as BOOLEAN, you have to use SUM(CASE WHEN result THEN 1 ELSE 0 END). If you store them as strings, SUM(CASE WHEN result = 'success' THEN 1 ELSE 0 END). If you cannot change your time_series table, edit the code below as appropriate.

Solution 1

This is inefficient ad rather ugly, but it’s worth being shown to illustrate how to use a CTE and what pains we had to suffer before CROSSTAB came along. When changing intervals, it has to be modified in four places: initial day, final day, rows in the main selection list, and rows in the list of joined tables. However, using a numeric column rn allows not to write explicit dates in the joined tables, which simplifies the task.

WITH ct AS (
  SELECT EXTRACT('days' FROM day - MIN(day) OVER()) + 1 AS rn, sub.*
    FROM (
     SELECT
          entity,
          DATE_TRUNC('day', date_time) AS day,
          ARRAY[COUNT(result), SUM(result)] AS a
       FROM time_series
      WHERE date_time BETWEEN TIMESTAMP '2016-01-01'                 -- initial day
                          AND TIMESTAMP '2016-01-03 23:59:59'        -- last second of final day
      GROUP BY 1,2
    ) AS sub
)
SELECT e.entity
       , d1.a AS "day 1"                                             -- add as many as you need
       , d2.a AS "day 2"
       , d3.a AS "day 3"
  FROM (SELECT DISTINCT entity FROM ct) e
  LEFT JOIN (SELECT entity, a FROM ct WHERE rn = 1) d1 USING(entity) -- add as many as you need
  LEFT JOIN (SELECT entity, a FROM ct WHERE rn = 2) d2 USING(entity)
  LEFT JOIN (SELECT entity, a FROM ct WHERE rn = 3) d3 USING(entity)
  ORDER BY e.entity;

 entity | day 1 | day 2 | day 3
--------+-------+-------+-------
 a      | {2,2} | {1,1} |
 b      | {1,0} | {1,0} |
 c      | {1,1} |       |
 d      | {1,1} |       |
 e      |       |       | {4,3}

Solution 2

The code for the view is essentially the CTE code of the previous example, but it is simpler, because CROSSTAB allows us using GENERATE_SERIES with timestamp values, so we don’t need a numeric rn column to categorize the data. Note that this view, once created, will not need modifications.

CREATE VIEW ts_view AS
  SELECT
      entity,
      DATE_TRUNC('day', date_time) AS day,
      ARRAY[COUNT(result), SUM(result)] AS a
    FROM time_series
    GROUP BY 1,2;

This is the main query. When changing intervals, it has to be modified in three places: initial day, final day, and output columns. Formatting is best done client-side, but in this case I have done it server-side. Instructions on how to change this are in the comments

SELECT * FROM CROSSTAB ($$
    SELECT
        entity,
        day,
        -- You have to repeat the result type of the following expression
        -- as the type of the "day N" columns below.
        -- e.g.  a --> INTEGER[] ,  100*(a[2]::FLOAT)/a[1] --> FLOAT , etc.
        -- In TO_CHAR, D is changed to your locale's decimal point
        TO_CHAR(100*(a[2]::float)/a[1], '990D99')||'%'
    FROM ts_view ORDER BY 1
  $$,$$
    SELECT GENERATE_SERIES (
      TIMESTAMP '2016-01-01',  -- initial day
      TIMESTAMP '2016-01-03',  -- final day
      '1 day'
    )
  $$
) AS (
    entity TEXT
    , "day 1" TEXT             -- add as many as you need
    , "day 2" TEXT
    , "day 3" TEXT
);

 entity |  day 1   |  day 2   |  day 3
--------+----------+----------+----------
 a      |  100.00% |  100.00% |
 b      |    0.00% |    0.00% |
 c      |  100.00% |          |
 d      |  100.00% |          |
 e      |          |          |   75.00%

As a final remark, the second argument of CROSSTAB can be something like

$$
WITH n(ow) AS (VALUES(DATE_TRUNC('day', NOW())))
SELECT GENERATE_SERIES(n.ow + '-2 days', n.ow, '1 day') FROM n
$$

and the query would always return a dynamic report for the last three days (today included).


Are junction tables a good practice? Say I have a large table that holds the user's info and another table that holds several locations. Then I use another table that holds the user_id and the location_id.

In order to retrieve the data I have to use Left Join query. Doesn't that make the whole process longer to retrieve rather than having it all in one table? Eg I could have the location as text on the same table.

EDIT: Here is an example.

CREATE TABLE  `user` (
`id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `gender` enum('M','F') DEFAULT NULL
);

CREATE TABLE `user_location` (
  `user_id` int(11) NOT NULL,
  `location_id` int(11) NOT NULL
);

CREATE TABLE `location` (
`id` int(11) NOT NULL,
  `location` varchar(45),
  `parent_id` varchar(45)
);

Note: Please assume that  all related fields are properly indexed between them.

Edit: I currently have a large database with users that retrieve their location via a junction table as described above. I was asked to optimize the database because the search results are slow. I've added memcache and it improved significantly but now I am just wondering about Left Joins.

For example, The current query is something like that:

SELECT * FROM users
LEFT JOIN user_location
ON user_location.user_id = user.id
LEFT JOIN location
ON location.id = user_location.location_id;

And that is just to get the location. They have several other fields that are retrieved through junctions and they are all needed to view a user's profile. We have phone numbers, addresses, passwords, D.O.B and many others all in different tables.

In order for me to create a page for the user profile I have to send the server a large query. Now after the first time it gets cached and it's fine. But I was just wondering why would someone build their database like that?

If you put everything in one table, you will have a bigger, redundant table.

If all the tables are properly indexed, the 3 tables solution will be fast, because a small number of rows will be read for each query.


Alternative to Mysql that handles increment/decrement with new or existing keys In our application we're looking to store values by ID. In mysql, I might make a table like this:

create table balances {
  address bigint NOT NULL PRIMARY KEY,
  balance double NOT NULL
}

The problem is how we would interact with it. If I move 15 units from address=5 to address=12, address 12 may not exist. So it takes two or three operations:

begin transaction;
subtract 15 from balance where address=5
if address 12 exists
  add 15 to address 12
else
  insert balance=15, address=12
end transaction

Is there a database or storage system that is designed for this sort of tracking that could do this sort of operation more atomically without if-checks?

I'm not sure I fully understand the use-case, but in MySQL you can use:

    INSERT INTO balances (address, balance) VALUES (12, 15)
    ON DUPLICATE KEY
    UPDATE balance = balance + 15;

... so you may not need a specialized DBMS for this sort of thing.

This type of statement is known as 'merge' (as in the SQL standard) or 'upsert'. See this Wikipedia article for more info, including details about the implementation in other relational database systems.


SQL Server 2008 R2 - Memory Allocation We have a SQL Server 2008 R2 instance that is running on a Windows Server 2008 R2 Enterprise server that has been allocated 128GB of memory. However, it maxes out at 70GB. Can anyone tell me how I can allow the SQL server to utilize the majority of its memory?? Thanks in advance.

If you use SQL Server 2008 R2 Standard Edition, you are limited to 64GB RAM per SQL-Server instance.

Memory Supported by the Editions of SQL Server


SQL Server Memory for Backup Buffer In  SQL Server 2012 memory for backup buffer comes from buffer pool or non buffer pool (VAS)? Is this behavior changed between SQL server 2008 R2 and SQL 2012?

EDIT: Does MAX Server Memory configuration setting now controls memory for backup buffer? Historically, this was coming out of MemToLeave (or Virtual Address Space) outside SQL Server buffer pool?

In SQL Server 2012 nothing comes from buffer pool anymore, except data pages. This is a change from SQL Server 2008R2. See Memory Manager surface area changes in SQL Server 2012. The buffer pool is just another client of the Memory Manager client for allocations, just like any other memory consumer. So the question is basically unasked in SQL Server 2012.


Can a SELECT with SELECT Subquery cause deadlock with Update query in SQL Server? I am using entity framework with table per type. I have a table called ins_GameAppInstances that has a discriminator column for the type of object I am returning. There is another table that has some settings that have a foreign key to ins_GameAppInstances called ins_DefaultBankAccountInstances.

When I have high throughput to the database a deadlock is happening between a select with a select subquery and an update to the same ins_GameAppInstance table. The sql server is choosing to kill the select of course as a victim.

I am wracking my brain to this point. If you look at the page locks, there is Page ID 6122 and then Page ID 4537. Could this be happening because the first select locks on S, the update gets inbetween the subquery select and locks on IX and then the subquery locks a later page? This causes the deadlock? It's really the subquery select that is causing this mess?

I know that the update is not updating anything to do with the select query results. Would it be safe to just allow READ_COMMITTED in SQL Server. I don't really want to do that but I also don't want to change the way my domain logic works because of Entity Framework and entity data mapping.

UPDATE:
I was thinking that as a last resort I could just deal with it by grabbing in a try catch sql exception. Throw the exception up the stack. When I run my integration test with a console app just smashing the db there is significant wait though for the deadlock to happen.

catch (SqlException ex)
        {
            if (ex.Number == 1205)
            {
                if (LogManager.Enabled)
                    LogManager.GetLogger(GetType()).Debug("Deadlock");
            }
            else
                throw;
        }

Here is the ins_GameAppInstance table

Here is the DefaultBankAccountInstance table. The GameAppInstanceID is the foreign key to ins_GameAppInstances.

Here is the deadlock that is happening. Request and owner S on the left and IX Request and owner on the right.

Here is the select and select subquery that entity framework is building:

exec sp_executesql N'SELECT
[Join1].[CurrentWeek] AS [CurrentWeek],
[Join1].[Discriminator] AS [Discriminator],
[Join1].[ID1] AS [ID],
[Join1].[Name] AS [Name],
[Join1].[WeekHasStarted] AS [WeekHasStarted],
[Join1].[Created1] AS [Created],
[Join1].[GameAppID] AS [GameAppID],
[Join1].[GameInstanceID] AS [GameInstanceID],
[Join1].[ID2] AS [ID1]
FROM  [dbo].[ins_GameAppInstances] AS [Extent1]
INNER JOIN  (SELECT [Extent2].[ID] AS [ID1], [Extent2].[Name] AS [Name], [Extent2].[CurrentWeek] AS [CurrentWeek], [Extent2].[WeekHasStarted] AS [WeekHasStarted], [Extent2].[Created] AS [Created1], [Extent2].[Discriminator] AS [Discriminator], [Extent2].[GameAppID] AS [GameAppID], [Extent2].[GameInstanceID] AS [GameInstanceID], [Extent3].[ID] AS [ID2]
    FROM  [dbo].[ins_GameAppInstances] AS [Extent2]
    LEFT OUTER JOIN [dbo].[ins_DefaultBankAccountInstances] AS [Extent3] ON [Extent2].[ID] = [Extent3].[GameAppInstanceID] ) AS [Join1] ON [Extent1].[ID] = [Join1].[ID1]
WHERE ([Extent1].[GameInstanceID] = @EntityKeyValue1) AND ([Join1].[Discriminator] IN (N''BankingAppInstance'',N''BillsAppInstance'',N''CashboxAppInstance'',N''CreditCardsAppInstance'',N''EmailsAppInstance'',N''ExpensesAppInstance'',N''RandomEventsAppInstance'',N''TextMessagesAppInstance'',N''GameAppInstance''))',N'@EntityKeyValue1 uniqueidentifier',@EntityKeyValue1='55E2A02B-73AF-4CC9-BC09-7BD46473CF4F'

Here is the update that is being sent at the same time:

    exec sp_executesql N'UPDATE [dbo].[ins_GameAppInstances]
SET [CurrentWeek] = @0, [WeekHasStarted] = @1
WHERE ([ID] = @2)
',N'@0 int,@1 bit,@2 uniqueidentifier',@0=4,@1=0,@2='FD00CB51-A8DD-4705-87E9-B1DD34322264'

UPDATE
XML DEADLOCK FILE

http://pastebin.com/mRNm0Rje

From my experience, when you use READ COMMITTED and the concurrency is high, deadlocks will happen. The frequency of deadlocks will grow with concurrency and length of transactions.

Reducing the transaction isolation level could improve things to the point that you will not have deadlocks due to SELECT statements, but still, there might be a chance to have deadlocks due to the UPDATE statements.

The best solution is to just assume that deadlocks are possible and will happen and to allow for a certain number of retries.


Running Total which Resets when Customer changes (keeping existing RowNumbers)- attempt included I have a table where the RowNumber is of essence.
It has a range of clients, and a running total for that client - which resets every time the Client changes.
e.g.

client  rownr   amount  runtotal
Company A   1   1.00    1.00
Company A   2   1.00    2.00  1+1 = 2
Company A   3   5.00    7.00  2+5 = 7
Company B   4   3.00    3.00  Reset Because Customer B <> Previous Row Customer A
Company A   5   1.00    1.00  Reset Because Customer A <> Previous Row Customer B
Company B   6   2.00    2.00  Reset Because Customer B <> Previous Row Customer A
Company B   7   1.00    3.00  2+1 = 3
Company B   8   5.00    8.00  3+5 = 8

I tried using Partition, but it always Groups Client A together, and Client B together- it removes the crucial part of the Row Numbers.

Any help please?

 drop table #Checks
CREATE TABLE #Checks
(
  Client VARCHAR(32),
  RowNr int,
  Amount DECIMAL(12,2),
  RunTotal DECIMAL(12,2),
  Part int
);

INSERT #Checks(Client, RowNr, Amount)
          SELECT 'Company A', '1', 1
UNION ALL SELECT 'Company A', '2', 1
UNION ALL SELECT 'Company A', '3', 5
UNION ALL SELECT 'Company B', '4', 3
UNION ALL SELECT 'Company A', '5', 1
UNION ALL SELECT 'Company B', '6', 2
UNION ALL SELECT 'Company B', '7', 1
UNION ALL SELECT 'Company B', '8', 5;

-- gets the first entries per client - these amounts are
-- the base amounts and the other entries are tallied up

with cte as (
            select
                    c1.client
                  , c1.amount
                  , c1.rowNr
                  , case when c1.client <> c2.client then c1.amount else null end amt
            from #Checks as c1
            left join #Checks as C2 on c1.rownr = (c2.rownr + 1)
  )

select
client
, rownr
, amount
, case when isnull(amt,0) > 0 then amt else total end as runtotal
from cte
cross apply (
                select
                       sum(x.amount) as Total
                from cte as x
                where x.rownr <= cte.rownr and cte.client = x.client
            ) as rt
;

Drop table #Checks

This should do what you want:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER(PARTITION BY Client ORDER BY RowNr)
    FROM #Checks
), CTE2 AS
(
    SELECT  *,
            RN2 = RowNr - RN
    FROM CTE
)
SELECT A.Client, A.RowNr, A.Amount, B.RunTotal
FROM CTE2 A
CROSS APPLY (SELECT SUM(Amount) RunTotal
             FROM CTE2
             WHERE Client = A.Client
             AND RN2 = A.RN2
             AND RowNr <= A.RowNr) B
ORDER BY RowNr

Results:

╔═══════════╦═══════╦════════╦══════════╗
║  Client   ║ RowNr ║ Amount ║ RunTotal ║
╠═══════════╬═══════╬════════╬══════════╣
║ Company A ║     1 ║ 1.00   ║ 1.00     ║
║ Company A ║     2 ║ 1.00   ║ 2.00     ║
║ Company A ║     3 ║ 5.00   ║ 7.00     ║
║ Company B ║     4 ║ 3.00   ║ 3.00     ║
║ Company A ║     5 ║ 1.00   ║ 1.00     ║
║ Company B ║     6 ║ 2.00   ║ 2.00     ║
║ Company B ║     7 ║ 1.00   ║ 3.00     ║
║ Company B ║     8 ║ 5.00   ║ 8.00     ║
╚═══════════╩═══════╩════════╩══════════╝


Galera wih MariaDB master-master to support write enable on disconnect I'm trying out a distributed app using Galera replication topology that needs to support writes if nodes are disconnected from the cluster.  The idea was to have an extra MariaDB that is connected to each Galera node through master-master replication:

dbc0 <--> db0 <--> app
 |
dbc1 <--> db1 <--> app
 .
 .
 .
dbcn <--> dbn <--> app

Where the vertical replication between dbc's is Galera and needs to support 100's of nodes and WAN. The horizontal replication is MariaDB master-master within the same linux server but between different lxc containers.  The idea is the app would only communicate to the non-Galera db's, and writes would have keys containing the node #, so be guaranteed unique.  The sync between nodes can be slow, but the app (in another container) needs to interact quickly with its local db.

After setting it up with server_id's the same on the dbc's and unique on the db's, and log_slave_updates = 1 on the dbc's, it almost works:  creating a new table on any dbc creates the tables in all the dbc's and db's.  Adding a row in any dbc also shows up everywhere.

Adding a row in a db though successfully replicates to all the dbc's, but doesn't get to the other db's.  However, this topology does allow writes to a db when its dbc is turned off, and sync's nicely when the dbc is turned back on to all the other dbc's (but not the db's).  Hope this makes sense.

Is there a way to make a write in a db node replicate through both the Galera cluster dbc nodes and their corresponding MariaDB master-master replicated db's?  Or any other way to accomplish this?

First, Galera should not be used for more than about 5 nodes.  This is because each node talks to every other node on each write.  This means 100 nodes would have 9900 'connections' among them.  And each write involves 99 roundtrip messages.

You have solved the main 'key' problem by ensuring uniqueness.  But there are other possible ways of having conflicts.

Furthermore, Galera's approach to re-acquiring a node will not work for you.  It will assume the node is corrupt.  It will not assume you have inserted valid new records.

Your dual-master setup does not affect what will eventually happen on the Galera nodes.

Are you more vulnerable to server outage?  Or network outage?


Will TRUNCATE stop the increase of the DB2 database size? we are periodically doing the TRUNCATE of some large tables in database.
After that in order to release disk space we are doing the command:

alter tablespace MAXDATA reduce max

and everything works smoothly.

I have a question if for example database size iz 100 GB, and one table size is 5GB and we execute TRUNCATE command on that table, I understand that it will not release database size back to 95 GB in order to decrease it for 5 GB we must execute ALTER
but will it stop the growth of database size for new data which will be populated?
So does it mean that database size will be 100GB and after new 5GB data in that same table is being generated it will not trigger additional 5GB of space (105 GB) but it will keep the 100GB size.

Please provide me explanation for this.

Thanks,

The fact that you are using the alter tablespace ... reduce max syntax indicates that you're using automatic storage (or DMS) tablespaces.

Knowing that, truncating a table only frees up the space that the table occupies within a tablespace. (i.e., Db2 releases the extents that are assigned to the table.)

The reason Db2 can't just give back the space that was freed in the tablespace automatically is because there is no guarantee that the extents that were allocated to the table were located contiguously at the "end" of the tablespace.

Therefore, when you deallocate space in the tablespace (via alter tablespace ... reduce max), Db2 must first "fill in the holes" by moving other in-use extents into the unused extents that were freed when you truncated the table.

See the documentation for reclaimable storage for more details and some pictures of this.


What index(es) should I use for three columns join? I have a table which has the following columns:

CREATE TABLE example (
   exid INT,
   extype INT,
   studentid INT,
   (more columns)
)

The table will have hundreds of thousands rows, and each record will have distinct value of exid, extype and studentid. While studentid and exid will have plenty of distinct values, extype will have max. 6 values. The table will be joined in the following way:

SELECT ...
FROM example ex

LEFT JOIN otherTable1 ot1 ON ex.studentid = ot1.studentid
    AND ex.exid = ot1.exid
    AND ex.extype = 1

LEFT JOIN otherTable2 ot2 ON ex.studentid = ot2.studentid
    AND ex.exid = ot2.exid
    AND ex.extype = 2

Can you suggest me what index(es) on example table will be the most suitable in regards of performance here (I should also add other tables used for joins will go into millions of rows)?

You should benchmark it using autotrace (http://betteratoracle.com/posts/10-using-autotrace) for example.

If you expect millions of rows to be returned, then I do not think indexing will help at all. Simple full table scan and hash joining is the most efficient way of executing this kind of query (https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9422487749968).

Putting the least selective column first and using key compression can decrease the index size and reduce IO and CPU consumption  (http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:8806017524595).

The following index should be quite sufficient if you fetch only few rows:

create unique index test1 on example(extype,studentid,exid) compress 1;


How can I configure a connection string in SSRS 2012 using named pipes? I have SQL Sever 2012 and I want to create a data source that uses named pipes rather than TCP.

This is when I have tried:

Data Source=np:\\.\pipe\MSSQL$MSSQLSERVER02\sql\query;Initial Catalog=cleverdataout

If I test the connection, I get an error message, with error code 25, indicating that the connection string is not valid.

Is there a way to use named pipes? Or am I forced to use TCP?

You'll need to indicate that you want to use the named pipes provider in the connection string.

Consider this VB.Net code that connects to my local SQL Server 2012 instance via Named Pipes:

Module Module1

    Sub Main()
        Dim cb As New SqlClient.SqlConnectionStringBuilder With {
            .DataSource = "\\.\pipe\MSSQL$MV2012\sql\query",
            .NetworkLibrary = "dbnmpntw ",
            .IntegratedSecurity = True
            }
        Using conn As New SqlClient.SqlConnection(cb.ConnectionString)
            conn.Open()
            Using cmd As SqlClient.SqlCommand = conn.CreateCommand
                cmd.CommandText = "SELECT ServerName = @@SERVERNAME;"
                cmd.CommandType = CommandType.Text
                Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader
                While reader.Read
                    Console.WriteLine(reader.GetString(reader.GetOrdinal("ServerName")))
                End While
            End Using
        End Using
    End Sub

End Module

The .NetWorkLibrary = "dbnmpntw" indicates that I'm going to use the Named Pipes Provider, as detailed in this Microsoft Docs page.

I validated that the pipe name, \\.\pipe\MSSQL$MV2012\sql\query, is correct by looking at the SQL Server Configuration Manager under "SQL Server Network Configuration -> Protocols for MV2012 -> Named Pipes Properties".

I'd say your connection string needs to be (I've wrapped the text simply for clarity):

Data Source=\\.\pipe\MSSQL$MSSQLSERVER02\sql\query;
Network Library=dbnmpntw;
Initial Catalog=cleverdataout

FYI, the \\. piece indicates the name of the server where SQL Server is installed.  In this example, that indicates the localhost server.  If you are connecting via named pipes remotely, you'd need to replace the . with the name of the server.  So, if the server was MySQLServer, then you'd need the pipe name to be:  \\MySQLServer\pipe\MSSQL$MSSQLSERVER02\sql\query.

The MSSQL$MSSQLSERVER02 part of the named pipe in your question indicates that you are attempting to connect to a SQL Server Named Instance named MSSQLSERVER02.  If your SQL Server is installed as the default instance, i.e. with no name, then you'd need to specify the pipe like:

\\MySQLServer\pipe\sql\query

Note, for this to work you'll need to enable the File and Printer Sharing (SMB-In) Windows firewall rule that allows inbound TCP traffic via port 445 at the server.


SQL, sum one column where another column is the same and display as the third column I have the following data:

a  b
1  23
1  14
1  3
2  45
2  16

and what I would like is to sum column b where column a is the same and have this displayed in a third column, like so:

a  b  c
1  23 40
1  14 40
1  3  40
2  45 61
2  16 61

Thanks.

You didn't specify your RDBMS, but here is a solution using SQL Server

Declare @T table (a int, b int)
INSERT INTO @T
    (a, b)
VALUES
    (1, 23),
    (1, 14),
    (1, 3),
    (2, 45),
    (2, 16)
;

select
    a,b,sum(b) over (partition by a)  as c
from @T

| a | b  | c  |
|---|----|----|
| 1 | 23 | 40 |
| 1 | 14 | 40 |
| 1 | 3  | 40 |
| 2 | 45 | 61 |
| 2 | 16 | 61 |

If your RDBMS doesn't support Window Functions, you could use a common table expression to do the sum/group and then join that cte to the original table.  The results are the same.

;WITH _cte
AS (
    SELECT a
        ,sum(b) AS c
    FROM @T
    GROUP BY a
    )
SELECT t.a
    ,t.b
    ,c.c
FROM @T t
JOIN _cte c ON c.a = t.a


how to setup percona pmm monitoring of percona 5.7 in docker container I want to monitor percona running in docker with the percona monitoring and management client.

I have percona monitoring and management installed, running and monitoring other percona instances (https://www.percona.com/doc/percona-monitoring-and-management/deploy/index.html). I recently added percona (currently version 5.7) running in docker container (pulled from https://github.com/docker-library/percona).

I found an open feature request to add the pmm-admin executable from inside PMM Server at https://jira.percona.com/browse/PMM-627

Is there way to setup to monitor my percona instance running docker?

Can you show us the command you used to launch the Percona Server for MySQL 5.7 container? You need to ensure you are allowing access to 3306/tcp.

Otherwise from PMM Server perspective there will be nothing unique to your use case of running Percona Server for MYSQL from docker or via yum/apt-get, the mysqld_exporter will connect to 3306/tcp and collect MySQL metrics as needed.

You will be missing collection of node_exporter data so you won't have anything on your Disk Performance, Disk Space, etc graphs.


How do you Audit Login Failures? Solutions? How do you Audit Login Failures? Solutions?

For SQL Server, Brian Kelly did a great write up at MSSQLTips.com. You can find it here: https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/

You can enable login auditing (and specify the level of logging) on the Server Properties screen under 'Security.'

To view the logs, execute 'EXEC SP_ReadErrorLog.' There are some parameters that can be added to filter, and look at older than current logs as well. You can also view events in Windows Event Log Viewer. The 'Source' will be the SQL Server instance name on the machine.


Is it normal for it to take up to a minute for an execution plan to be generated (even an estimated one) on simple queries? Once in a while (but uncommonly) my SQL server will take what seems like an odd amount of time to generate an execution plan. It just took 37 seconds to generate an estimated execution plan for the following fairly simple query:

SELECT *
FROM Table1
WHERE IndexedIntField1 = 12345
    AND NonIndexedVarcharField IN ('Value1', 'Value2', 'Value3')

The number of results from this query were roughly 500 rows (from a table that holds about 10 billion rows) and the execution plan was essentially a nonclustered index seek with a key look up.

Is this normal?

Edit:

Table1 is an actual materialized regular disk-based table (nothing special going on here).
It's about 30 columns wide.
There's the 1 clustered index and 4 nonclustered indexes on it.
The "IndexedIntField1" in my example is part of the index key in 2 of the nonclustered indexes, and is an included column on a third nonclustered index.
"NonIndexedVarcharField" is not a key nor included on any of the indexes.
We update statistics on the table and indexes at least once a week (and sometimes as much as once a day or more)
No fancy calculated columns are on this table
The indexes on the table are pretty simple, only a couple of fields in the key columns / included columns EXCEPT one of the indexes that have "IndexedIntField1" as a key column does include about 15 columns on it (so it's a rather unusually big index).

To process a query of this form:

SELECT *
FROM Table1
WHERE IndexedIntField1 = 12345
    AND NonIndexedVarcharField IN ('Value1', 'Value2', 'Value3')

The basic choices are to scan the whole table or to seek on IndexedIntField1, and then perform lookups for each row to see if the other predicate obtains.  If there a lots of rows IndexedIntField1 = 12345 then the table scan will be much cheaper, and if there are very few then the index seek + bookmark lookup will be much cheaper.

If the statistics necessary to decide which plan to use don't exist or are out-of-date, then, by default, SQL Server will create or update the statistics before picking a query plan.

  The Query Optimizer creates statistics for single columns in query
  predicates when AUTO_CREATE_STATISTICS is on.

https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-ver15#CreateStatistics

and

  AUTO_UPDATE_STATISTICS { ON | OFF } ON Specifies that Query Optimizer
  updates statistics when they're used by a query and when they might be
  out-of-date.

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15#auto_update_statistics

You can see the existing statistics and when they were updated like this:

select *, stats_date(s.object_id, s.stats_id) stats_date
from sys.stats s
where object_id = object_id('sales.salesorderdetail')


to copy column from one table to another in psql I want to copy column User_id from public."firstapp_offer_Vendor" to table firstapp_bid on the basis of oid, i.e. if oid in firstapp_offer_Vendor is equal to firstapp_bid.offer_id then user_id should be copied to vendor_id.
Here are my two tables:
Table firstapp_bid:
id   Bid  offer_id vendor_id
1   "123"   1       null
2   "123"   6       null
3   "123"   13      null
4   "120"   13      null
5   "140"   14      null
6   "130"   14      null
7   "140"   14      null
8   "150"   14      null
9   "123"   15      null
10  "123"   16      null
11  "120"   16      null

Table first_app_offer_vendor:
id oid user_id
1   1   3
12  6   2
26  13  3
27  13  4
28  14  2
29  14  4
30  14  6
31  14  7
32  15  7
33  16  6
34  16  7

The output table should be:
id   Bid  offer_id vendor_id
1   "123"   1       3
2   "123"   6       2
3   "123"   13      3
4   "120"   13      4
5   "140"   14      2
6   "130"   14      4
7   "140"   14      6
8   "150"   14      7
9   "123"   15      7
10  "123"   16      6
11  "120"   16      7

I tried implementing that using
INSERT INTO firstapp_bid
(
   vendor_id
)
SELECT
   user_id
FROM
   public."firstapp_offer_Vendor"
WHERE
   public."firstapp_offer_Vendor".oid = public."firstapp_bid.offer_id

but it gives me the wrong output.

You don't need an insert here, as this is about adding new rows. You want rather to make an update. Something like
create table a(id int, v varchar(10));
create table b(id int, v varchar(10));

insert into a
select 1, 'a'
union all
select 2, 'b';

insert into b
select 1, null
union
select 2, null;

update b set v = a.v
from a where a.id = b.id;


Restoring differential backup from SQL Server 2005 to SQL Server 2012 We are migrating a client to a new server. Their old server has SQL Server 2005. I have attempted to perform a differential backup however I received an error about the LSN chain being broken.

Please note I have done this many times, the full backup is restored on the new server with NORECOVERY and then when I go to restore the diff backup I get the error about the broken LSN chain.

I made a test database, got the same result. I then took the test database files and moved them to a server running SQL Server 2008 R2, the backup restored correctly.

Does anyone know why the differential backups from SQL Server 2005 will not restore properly on SQL Server 2012? And how can I fix this?

There should be no issue restoring differentials and fulls from SQL 2005 to SQL 2012.  I would validate that your backup files are compatible.  To do this, you'll want to use the RESTORE HEADERONLY command and compare the full backup's FirstLSN value with the differential's DifferentialBaseLSN:

restore headeronly
from disk='X:\BackupFiles\foo.bak'

If these values do not match, then you will need to take an appropriate full backup.


Getting the number of Saturdays and Sundays in a month I am trying to get the number of Saturdays and Sundays for a given month represented by an end_date and a start date.

I have been trying to understand the algorithm in the SO question here . I have understood the matrix they have created but I don't get the algorithm that is being used, plus the thing is just the opposite of what is needed, and this thing is pretty new to me, mostly I do normal select, insert, update and joins in mysql.

It would be helpful to get some ideas on this issue.

I'll suggest the calendar table also discussed in that thread. It will help for a lot of calculations beside this one. Your query can then be expressed as:

select count( case when DAYOFWEEK(calendar_date) in (1,7) then 1 end)
from Calendar
where calendar_date between :start_date and :stop_date

or as:

select count(1)
from Calendar
where calendar_date between :start_date and :stop_date
  and DAYOFWEEK(calendar_date) in (1,7)


Product table with possible duplicate part numbers from different manufacturers I am making a database. It has a product(aka component or part) table with a ID, ManufacturerID, and PartNumber. The ID is obviously the primary key and is unique. The ManufacturerID is pulled from the ManufacturerT. The PartNumber will be entered by the user. I am trying to decide whether to make the PartNumber field unique or not. If I do, I won't have the issue with a user entering the same product multiple times, but what if there is a product from a different manufacturer with the same PartNumber? It wouldn't allow the user to enter. What is the solution to this? Does it require some special logic? Is there a way to not allow duplicates if the ManufacturerID and PartNumber fields are identical? I am using Microsoft Access 2013(version 15).

A common solution to this problem is composite keys or indices. You can specify any key or index as unique, so combining the PartNumber and ManufacturerID into a composite and then marking it as unique has the effect of enforcing the uniqueness of each combination of PartNumber and ManufacturerID. See this thread on SO for more information.


Which is better: SQL vs NoSQL I am designing a content-based website capable of managing huge amounts of pages (mostly web pages; images, videos, etc would be stored on the file system). Which would be faster/more efficient/most stable:

A traditional database (eg. MySQL) with each web page stored as a row with indexes strategically located or

A key/value based NoSQL database (eg. MongoDB) where the key is like PAGE-ID_TITLE or PAGE-ID_BODY etc.

Either option could allow caching (either built-in or roll-your-own).

I do not want to directly compare MySQL and MongoDB but rather SQL vs key/value

Thanks

This is a very open question, with lots of considerations to be made and lots of choices that are more "opinions" than "facts". So, I won't try to provide an answer, but to point out some of the big differences and things to conisder.

If your only need is to store key/value pairs (page_id, body), a key/value datastore is a good match for your need. You can use Riak, Oracle NoSQL and several others (you can take a look at the Wikipedia for a longer list). You get very fast access to your bodies (or titles); normally you also get some easy sharding and replication (i.e.: MongoDB), which allows for really good horizontal scalability if you ever need really very huge amounts of data.

What you don't get is an easy way to join data and query it. If one day you decide to have a list of (for instance) article authors, or article comments, or whatever, the key-value store might not be that comfortable any more.

As a rule of thumb, key-value stores or document stores require that you plan very well in advance how your data is going to be used, because the structure of your documents will have to reflect this usage. A relational database is based on structured tables where you store information which can be searched and joined easily.

The majority of contemporary SQL databases allow for some combinations that can be thought as "the best of both worlds", mixing SQL with JSON (or documents). For instance, you can check PostgreSQL hstore, which gives you a key-value store within a SQL database, or use JSON with MySQL, PostgreSQL, Oracle, SQL Server, and others.

You can check previous questions such as Use SQL or NoSQL?, or Should I use SQL or NoSQL for this specific design (surveys)?.

I'd also recommend SQL vs NoSQL: The Differences


Select only records that have different values for a particular column In my table there are some records that have different name values for the same account number and I want to extract only the records that have different values in the name field, but that share the same account number. Records that always have the same name associated with the account number I want to ignore, regardless of how many times they occur.

So, from this :

ID NAME    ACCNR
1  Peter   123456
2  Peter   123456
3  Jimmy   445566
4  John    987654
5  Robert  987654
6  Robert  556644
7  Sally   112233
8  Harry   987654

I would like to extract this :

4  John    987654
5  Robert  987654
8  Harry   987654

SELECT DISTINCT t1.*
FROM table t1, table t2
WHERE t1.accnr = t2.accnr
  AND t1.name != t2.name


SQL Server 2008 R2 MERGE statement to replace single INSERT AND UPDATE statement combined for performance reasons, we're considering changing our standard stored procedure for saving data (INSERT and UPDATE combined in a single stored proc):

ALTER PROCEDURE [dbo].[spCustomerSave]
    (
        @CustomerID int = null,
        @CustomerName nvarchar(50),
        @New_ID int output
    )
    AS
    BEGIN

    IF EXISTS(SELECT 1 FROM tblCustomer WHERE CustomerID = @CustomerID)
        BEGIN
            UPDATE tblCustomer
            SET
                CustomerName = @CustomerName
            WHERE
                CustomerID = @CustomerID;
                SELECT @New_ID = @CustomerID;
        END
    ELSE
        BEGIN
            INSERT INTO tblCustomer(
                Taalnaam)
            VALUES(
                @CustomerName)
                SELECT @New_ID = scope_identity();
        END
    END

into a syntax using the MERGE statement:

ALTER PROCEDURE [dbo].[spCustomerSave]
    (
        @CustomerID int = null,
        @CustomerName nvarchar(50),
        @New_ID int output
    )
    AS
    BEGIN

    SELECT @New_ID = @idTaal;

    MERGE dbo.tblCustomer as target
        USING (SELECT @CustomerID, @CustomerName) as source (CustomerID, CustomerName)
        ON (target.CustomerID = source.CustomerID)
        WHEN MATCHED THEN
            UPDATE SET
                CustomerName = @CustomerName
        WHEN NOT MATCHED THEN
            INSERT (CustomerName)
            VALUES(source.CustomerName);
                SELECT @New_ID = scope_identity();
    END

Question:

is this going to benefit us performance wise? (avoiding the initial SELECT on the primary key)
is this proper use of the MERGE statement? Most examples show MERGE statements in a scenario to execute multiple DML operations.

I haven't done any comparative testing of the two (yet) nor seen any articles on the topic. There is an Optimizing MERGE Statement Performance article on Technet but this doesn't include any comparisons with the update/insert syntax.

I can however suggest an improvement over your original syntax which eliminates the IF EXISTS lookup:

UPDATE
    dbo.tblCustomer
SET
    CustomerName = @CustomerName
WHERE
    CustomerID = @CustomerID;

IF (@@ROWCOUNT = 1)
BEGIN
    SELECT @New_ID = @CustomerID;
END
ELSE
BEGIN
    INSERT
        dbo.tblCustomer
        (Taalnaam)
    VALUES
        (@CustomerName);

    SELECT @New_ID = SCOPE_IDENTITY();
END

You may also be interested in Mythbusting: Concurrent Update/Insert Solutions, which includes some examples of MERGE usage.


SSRS 2014 - query running fine on management studio - report timing out - how to troubleshoot? I had a problem in reporting services 2014 - all the queries in a report were running fine from management studio but timing out when running the report.

here is the message from the user:

what to do to troubleshoot a SSRS 2014 report that gets stuck on loading?

where are the SSRS logs?

the report has 3 datasets and all of them run fine when I take the query off and run it through SSMSS.

this reporting services server, has only the service, the databases (ReportServer, etc) are on a different server but that is not the problem, it has been running for months.

this is the query of the report:

USE Product
declare @SeasonId int;set @SeasonId = 16

SELECT DISTINCT
        EDP [EDP],
        '-' [PO],
        NULL [PO Type Initial/ Repeat],
        NULL [PO Status Open/ Close],
        s.StyleCode + c.ColourCode + sz.SizeCode [SKU] ,
        StyleDesc [Style Description] ,
        s.StyleCode [Style] ,
        c.ColourCode [Option] ,
        SizeCode [Size] ,
        NULL [MPLAN Segment Code],
        SeasonDesc [Season],
        DepartmentDesc [Department],
        CategoryDesc [Category],
        NULL [ALL Season, SPR Only, SUM Only, INTERNET Only],
        NULL [Fabric Type],
        NULL [Garment Composition],
        NULL [Country Of Origin],
        NULL [MID Code],
        NULL [HTS Code],
        0.00 [Cost to use],
        NULL [Exception Flag],
        NULL [Duty Rate]
FROM    [Product].[dbo].[Style] s
        JOIN Product.dbo.StyleDetail sd ON s.StyleID = sd.StyleID
        JOIN [Product].[dbo].[Category] ct ON s.CategoryID = ct.CategoryID
        JOIN [Product].[dbo].[Department] d ON ct.DepartmentID = d.DepartmentID
        JOIN [Product].[dbo].[Segment] sg ON d.SegmentID = sg.SegmentID --
        JOIN [Product].[dbo].[Item] i ON s.styleid = i.styleid
        JOIN [Product].[dbo].[ItemColour] ic ON i.itemid = ic.itemid
        JOIN [Product].[dbo].[Colour] c ON ic.colourid = c.colourid
        JOIN [Product].[dbo].[ItemSize] isz ON i.itemid = isz.itemid
        JOIN [Product].[dbo].[Size] sz ON isz.sizeid = sz.sizeid
        JOIN [Product].[dbo].[SeasonItem] si ON i.itemid = si.itemid
        JOIN [Product].[dbo].[Season] se ON se.seasonid = si.seasonid
        JOIN [product].[dbo].[ItemMPLANReference] pn ON pn.itemid = i.itemid
        JOIN [Product].[dbo].[Product_Staging_CategoryImportMap] ci ON ci.CategoryID = ct.CategoryID
        JOIN [SAPurchaseOrder].[dbo].[tblPO1Detail] pod ON pn.EDP = pod.strItemNo

WHERE se.seasonid IN (@SeasonId)
and EDP not in (SELECT skuEDP from USCarrier.dbo.WHDespatchSku)
ORDER BY 1

this problem has been solved.
how I did it?

I had a look at the query plan and noticed a missed index.

USE [SAPurchaseOrder]
GO
CREATE NONCLUSTERED INDEX [idx_strItemNo]
ON [dbo].[tblPO1Detail] ([strItemNo])

GO

After I added the index the report started to work fine again.

What I think that really happened is:
SSRS had cached the query execution plan, and there was a case of parameter sniffing.
Once I created the index the plan was removed from cache and had to be recompiled.

this is very similar to this question here:
SQL runs slow in SSRS, but fast in SSMS

One good thing I think useful from the question above is how to eliminate the parameter sniffing possibility:

  As discussed in the comments, let's get rid of the parameters to see
  if your query is getting affected by parameter sniffing.

  To do this we build the SQL statement from scratch. Most things in
  SSRS can be expressions including the SQL query so you can build it as
  a string. With the parameters, we'll convert them to a comma delimited
  list using JOIN.

questions remain:

what to do to troubleshoot a SSRS 2014 report that gets stuck on loading?

where are the SSRS 2014 logs?

You need to look at the Execution logs. On your Report Server database, there's a view called ExecutionLog3. You can query this view by date and find how the user was running the report.

USE [ReportServer]
GO

SELECT *
FROM dbo.ExecutionLog3 AS el
WHERE el.TimeStart >= '2016-08-02'


Since you are looking to INSERT an entire file into a single column. You need the LOAD_FILE function:
UPDATE jobs SET Log = LOAD_FILE('/root/copy.log')
WHERE TaskName = 'LouPrMgt004.Backup';

So that you can see just how LOAD_FILE function works, try this:
SET @mycnf = LOAD_FILE('/etc/my.cnf');
SELECT @mycnf;

You should be able to see your my.cnf loaded into a variable
Here is a sample run
mysql> SET @mycnf = LOAD_FILE('/etc/my.cnf');
Query OK, 0 rows affected (0.00 sec)

mysql> select @mycnf\G
*************************** 1. row ***************************
@mycnf: [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_password=1

key_buffer_size=1G
max_allowed_packet=128M

# These two values must be the same
tmp_table_size=16M
max_heap_table_size=16M

# Sort_buffer is too large, so try default to see if it helps
sort_buffer_size = 2M

read_buffer_size=1M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=16M
max_length_for_sort_data=2048
max_sort_length=2048
long-query-time=5
skip-name-resolve
table_cache = 6144
open_files_limit = 32768
interactive_timeout=3600
wait_timeout=3600
thread_cache = 100
max_connections=1000
query_cache_size = 0
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
ft_min_word_len=2
group_concat_max_len=5000000
query_cache_size=8M
query_cache_limit=32M

#log-output=TABLE
#slow-query-log
#slow-query-log_file=slow-query.log
#expire-logs-days=7

server-id=180
#log-bin=mysql-bin
#log-slave-updates
#relay-log=relay-bin

innodb_file_per_table
innodb_log_file_size=128M
innodb_buffer_pool_size=512M
innodb_log_buffer_size=8M
innodb_flush_method=O_DIRECT

innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=5000
innodb_thread_concurrency=0

#
#       XtraDB Cluster Options
#

user=mysql
binlog_format=ROW
wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://
wsrep_slave_threads=2
innodb_locks_unsafe_for_binlog=1
innodb_autoinc_lock_mode=2

wsrep_sst_method=xtrabackup
wsrep_cluster_name=oxygen_xtradb_cluster_test
wsrep_node_name=oxygen_xtradb_cluster_test_node180

[mysql.server]
user=mysql
#basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

1 row in set (0.00 sec)

mysql>

Give it a Try !!!
Be careful with this because some have complained that it does not work

http://forums.mysql.com/read.php?20,218333,218333#msg-218333
http://bugs.mysql.com/bug.php?id=38403


Prevent postgres function of running more than 1 instance at the same time DBMS: Postgres 9.3.4
OS: Debian 7

I have a plpgsql function that will be put in crontab to run every 15 minutes. The function should finish in about 8 minutes, but just in case it takes more than 15 minutes, it CAN'T run until the last execution has finished.

I could think in more than 1 workaround, but I was wondering if there is some best known solution for this. Maybe something native on plpgsql syntax.

Do the exclusion in the cron job definition. The handy flock shell command is intended for exactly this.

To do it inside PostgreSQL you would have the function take a lock that it holds for the duration of its run. Lots of options - LOCK TABLE ... IN EXCLUSIVE MODE, do a SELECT ... FOR UPDATE of a particular row, or use pg_advisory_xact_lock. See the explicit locking chapter. The main advantage of using an advisory lock is that you don't need to create some table/row just to lock it in the case where there's no "natural" candidate for a lock target.

You can use the try forms of the advisory lock functions, or NOWAIT locks, if you want to abort a run instead of queueing it up. If you're using psql remember that by default it continues on error instead of aborting, so use psql -v ON_ERROR_STOP=1 in combination with NOWAIT.