What is the best way to check for matching sets of rows?
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 &lt; 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.