Full T-SQL code

         
DROP DATABASE Shuffle
GO

-- Create the DB
CREATE DATABASE Shuffle
GO

-- Allow snapshot isolation
ALTER DATABASE Shuffle
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

-- Enable accelerated database recovery
ALTER DATABASE Shuffle
SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

-- Use Shuffle DB
USE Shuffle
GO

-- Create the Deck
CREATE TABLE Cards
(
    Card CHAR(5) PRIMARY KEY
)
GO

INSERT INTO Cards
VALUES
    ('Ace'),
    ('2'),
    ('3'),
    ('4'),
    ('5'),
    ('6'),
    ('7'),
    ('8'),
    ('9'),
    ('10'),
    ('Jack'),
    ('Queen'),
    ('King')
GO

CREATE TABLE Suits
(
    Suit CHAR(8) PRIMARY KEY
)
GO

INSERT INTO Suits
VALUES
    ('Clubs'),
    ('Diamonds'),
    ('Hearts'),
    ('Spades')
GO

SELECT Id = IDENTITY(INT, 1, 1),
    Suits.Suit,
    Cards.Card
INTO Deck
FROM Cards
CROSS JOIN Suits
GO

ALTER TABLE Deck ADD CONSTRAINT Deck_PK PRIMARY KEY (Id)
GO

-- User Defined Function 'PickACard'
CREATE FUNCTION PickACard (@RAND FLOAT)
RETURNS INT
AS
BEGIN
    RETURN (
			SELECT FLOOR(@RAND * (52 - 1 + 1)) + 1
			)
END
GO

-- Stored Procedure 'SwapCardsV1'
CREATE PROCEDURE SwapCardsV1
    @Card1 INT = 1,
    @Card2 INT = 52
AS
BEGIN
    
    BEGIN TRY
		BEGIN TRANSACTION

		SET NOCOUNT ON

		-- Store the first card's details
		SELECT *
        INTO #Temp
        FROM Deck
        WHERE Id = @Card1

		-- Move second card into first card's place
		UPDATE Deck
		SET Card = CardDetails.Card,
			Suit = CardDetails.Suit
		FROM (
			SELECT d.Card,
            d.Suit
        FROM Deck d
        WHERE Id = @Card2
			) CardDetails
		WHERE Id = @Card1

		-- Move first card into second card's place
		UPDATE Deck
		SET Card = CardDetails.Card,
			Suit = CardDetails.Suit
		FROM (
			SELECT Card,
            Suit
        FROM #temp
			) CardDetails
		WHERE Id = @Card2

		COMMIT TRANSACTION;
	END TRY

	BEGIN CATCH
		-- PRINT 'We were unable to make the swap rolling back [' + ERROR_MESSAGE() + '].';
		ROLLBACK TRANSACTION;
        THROW 51000, 'Unable to swap card.', 1; 
	END CATCH
END
GO

-- Stored Procedure 'SwapCards'
CREATE PROCEDURE SwapCards
    @Card1 INT = 1,
    @Card2 INT = 52
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

    BEGIN TRY
		BEGIN TRANSACTION

		SET NOCOUNT ON

		-- Store the first card's details
		SELECT *
        INTO #Temp
        FROM Deck
        WHERE Id = @Card1

		-- Move second card into first card's place
		UPDATE Deck
		SET Card = CardDetails.Card,
			Suit = CardDetails.Suit
		FROM (
			SELECT d.Card,
            d.Suit
        FROM Deck d
        WHERE Id = @Card2
			) CardDetails
		WHERE Id = @Card1

		-- Move first card into second card's place
		UPDATE Deck
		SET Card = CardDetails.Card,
			Suit = CardDetails.Suit
		FROM (
			SELECT Card,
            Suit
        FROM #temp
			) CardDetails
		WHERE Id = @Card2

		COMMIT TRANSACTION;
	END TRY

	BEGIN CATCH
		-- PRINT 'We were unable to make the swap rolling back [' + ERROR_MESSAGE() + '].';
		ROLLBACK TRANSACTION;
        THROW 51000, 'Unable to swap card.', 1; 
	END CATCH
END
GO

-- Stored Procedure 'SwapCardsAtRandom'
CREATE PROCEDURE SwapCardsAtRandom
AS
BEGIN
    DECLARE @Rnd1 FLOAT

    SET @Rnd1 = RAND()

    DECLARE @Card1 INT

    SET @Card1 = (
			SELECT dbo.PickACard(@Rnd1)
			)

    DECLARE @Rnd2 FLOAT

    SET @Rnd2 = RAND()

    DECLARE @Card2 INT

    SET @Card2 = (
			SELECT dbo.PickACard(@Rnd2)
			)

    -- 'No need to swap a card with itself'
    IF @Card1 != @Card2
	BEGIN
        EXECUTE dbo.SwapCards @Card1,
			@Card2
    END -- IF @Card1 != @Card2
END -- CREATE PROCEDURE SwapCards
GO

-- Call SwapCardsAtRandom Multiple times
DECLARE @Counter INT
SET @Counter = 0
DECLARE @ErrorCounter INT
SET @ErrorCounter = 0
WHILE (@Counter < 1000)
BEGIN

    BEGIN TRY
		EXECUTE SwapCardsAtRandom
	END TRY

	BEGIN CATCH
		SET @ErrorCounter = @ErrorCounter+1
        PRINT 'We were unable to swap cards [' + ERROR_MESSAGE() +']. Error Count ['+LTRIM(STR(@ErrorCounter,10))+']'  
	END CATCH
    
    SET @Counter = @Counter + 1
END
GO

-- Review results
SELECT COUNT(*)
FROM Deck
GROUP BY Suit
GO

SELECT *
FROM Deck
ORDER BY Card,Suit
GO