SQL Server – Splitting a Deck of Cards and Returning Shuffled Results

sql servert-sql

Lets say I have a table with a deck of cards, numbered 01-52. I could return the top and bottom cards, as if I held each side of the union's select query in my left and right hands by doing a:

select top 26 * from DeckOfCards order by CardNumber desc
union all
select top 26 * from DeckOfCards order by CardNumber asc

It would be an even split.

But how could I have SQL Server intertwine the results returned, as if I had taken both portions of that union, one half in my left hand, and the other in my right, and shuffled them once like a deck of cards?

IE:
CardNumber 52, followed by 1, in the following sequence:
52, 1, 51, 2, 50, 3, 49, 4, etc…

This is not a homework question, just one of those things that passes through my mind when trying to get some shut eye. 🙂

Best Answer

A little math can help achieving this

SELECT CardNumber
FROM   DeckOfCards 
ORDER BY (1 - CAST(CardNumber / 27 as bit)) * (CardNumber* 2)
       + (CAST(CardNumber/ 27 as bit)) * (1 + (52 - CardNumber) * 2)

CAST(CardNumber / 27 as bit) returns 0 for the card number below 27 and 1 for the number above 26, using this it's possible to create a different order for the two different block:

(1 - CAST(CardNumber / 27 as bit)) * (CardNumber* 2) put the first 26 card in the even position, as the first member will be 1 for those card and 0 for the other
(CAST(CardNumber/ 27 as bit)) * (1 + (52 - CardNumber) * 2) will put the second 26 card in the odd position, e.g. (1 + (52 - CardNumber) * 2) will return odd values in descending order

SQLFiddle example with the order formula as a second column to see how it works

Related Solutions

SQL Server – Main Causes of Deadlocks and Prevention

tracking deadlocks is the easier of the two:

By default, deadlocks are not written in the error log. You can cause SQL to write deadlocks to the error log with trace flags 1204 and 3605.

Write deadlock info to the SQL Server error log: DBCC TRACEON(-1, 1204, 3605)

Turn it off: DBCC TRACEOFF(-1, 1204, 3605)

See "Troubleshooting Deadlocks" for a discussion of trace flag 1204 and the output you will get when it is turned on. https://msdn.microsoft.com/en-us/library/ms178104.aspx

Prevention is more difficult, essentially you have to look out for the following:

Code Block 1 locks resource A, then resource B, in that order.

Code Block 2 locks resource B, then resource A, in that order.

This is the classic condition where a deadlock can occur, if the locking of both the resources is not atomic, the Code Block 1 can lock A and be pre-empted, then Code Block 2 locks B before A gets processing time back. Now you have deadlock.

To prevent this condition, you can do something like the following

Code Block A (psuedo code)

Lock Shared Resource Z
    Lock Resource A
    Lock Resource B
Unlock Shared Resource Z
...

Code Block B (pseudo code)

Lock Shared Resource Z
    Lock Resource B
    Lock Resource A
Unlock Shared Resource Z
...

not forgetting to unlock A and B when done with them

this would prevent the deadlocking between code block A and code block B

From a database perspective, I'm not sure on how to go about preventing this situation, as locks are handled by the database itself, i.e. row/table locks when updating data. Where I've seen the most issues occur is where you saw yours, inside a cursor. Cursors are notoriously inefficient, avoid them if at all possible.

Sql-server – way to list all tables and when they were last accessed

Jon gives you some links to SQL Server audit information in the comments. That is a definite way to see who is doing what and when on a server. Depending on what you are looking for that may be a good enough answer. Auditing isn't free, though. Capturing some of the data required can be expensive to trak and take some time to track.

One approach here - depending on what you are looking to do with this information is to look at the DMV - sys.dm.db_index_usage_stats this DMV shows you various statistics about access (insert, update, delete, scan, seek and lookups) for your indexes. You can join this DMV to tables that contain information about your tables or views like sys.tables or sys.indexes to see the details.

There are some caveats about this approach (data gets reset when SQL is restarted, there is a bug in SQL 2012 where the stats are apparently reset after an index rebuild, and other caveats described by Joe Scan in a couple posts) You can see an example of querying this data in this post from 2007 written for SQL 2005 but still most applies today.

Best Answer

Related Solutions

SQL Server – Main Causes of Deadlocks and Prevention

Sql-server – way to list all tables and when they were last accessed

Related Question