Binary Substring Tests

-- This script pertains to the following Stack Overflow question:
--       http://stackoverflow.com/questions/30330035/performance-of-varbinary-comparison-in-t-sql
-------------------------------------------------------------------------------------------------------------

-- RUN THE FOLLOWING SETUP ONCE:
-------------------------------------------------------------------------------------
DECLARE @Pattern VARBINARY(19),
        @SetsOf1000TestRows INT;

SET @SetsOf1000TestRows = 50;
SET @Pattern = CONVERT(VARBINARY(19), CONVERT(VARCHAR(32), CONVERT(BINARY(16), NEWID()), 2) + '0A0B0C', 2);
SET CONTEXT_INFO @Pattern;

SET NOCOUNT ON;

IF (OBJECT_ID(N'tempdb..#BinaryStuff') IS NOT NULL)
BEGIN
  DROP TABLE #BinaryStuff;
END;

IF (OBJECT_ID(N'tempdb..#BinaryStuffWithPCC') IS NOT NULL)
BEGIN
  DROP TABLE #BinaryStuffWithPCC;
END;

IF (OBJECT_ID(N'tempdb..#BinaryStuffMax') IS NOT NULL)
BEGIN
  DROP TABLE #BinaryStuffMax;
END;

IF (OBJECT_ID(N'tempdb..#BinaryStuffMaxWithPCC') IS NOT NULL)
BEGIN
  DROP TABLE #BinaryStuffMaxWithPCC;
END;

CREATE TABLE #BinaryStuff(ID INT IDENTITY(1, 1) PRIMARY KEY,
                          Payload VARBINARY(100) NULL
                         );

;WITH TestSets AS
(
  SELECT TOP (@SetsOf1000TestRows) sac.[column_id] AS [Repeats]
  FROM   [master].sys.all_columns sac
)
INSERT INTO #BinaryStuff (Payload)
  SELECT tmp.Payload
  FROM   TestSets ts
  CROSS APPLY (
    SELECT TOP (999)
           CONVERT(VARBINARY(100),
                   CONVERT(VARCHAR(100), NEWID()) + CONVERT(VARCHAR(100), COALESCE(sac.collation_name, 'bob') + sac.[name])
                  ) AS [Payload]
    FROM   [master].sys.all_columns sac
    UNION ALL
    SELECT CONVERT(VARBINARY(100),
                   CONVERT(VARCHAR(50), @Pattern, 2) + REPLICATE(SUBSTRING(CONVERT(VARCHAR(36), NEWID()), 1, 8), ts.[Repeats]), 2
                  )
  ) tmp

---
CREATE TABLE #BinaryStuffWithPCC(ID INT PRIMARY KEY,
                          Payload VARBINARY(100) NULL,
                          [Prefix] AS SUBSTRING([Payload], 1, 19) PERSISTED);

INSERT INTO #BinaryStuffWithPCC (ID, Payload)
  SELECT tmp.ID, tmp.Payload
  FROM   #BinaryStuff tmp;
---
CREATE TABLE #BinaryStuffMax(ID INT PRIMARY KEY,
                          Payload VARBINARY(MAX) NULL
                         );

INSERT INTO #BinaryStuffMax (ID, Payload)
  SELECT tmp.ID, tmp.Payload
  FROM   #BinaryStuff tmp;
---
CREATE TABLE #BinaryStuffMaxWithPCC(ID INT PRIMARY KEY,
                          Payload VARBINARY(MAX) NULL,
                          [Prefix] AS CONVERT(VARBINARY(19), SUBSTRING([Payload], 1, 19)) PERSISTED);

INSERT INTO #BinaryStuffMaxWithPCC (ID, Payload)
  SELECT tmp.ID, tmp.Payload
  FROM   #BinaryStuffMax tmp;
---

ALTER TABLE #BinaryStuff REBUILD WITH (FILLFACTOR = 100);

ALTER TABLE #BinaryStuffWithPCC REBUILD WITH (FILLFACTOR = 100);

CREATE NONCLUSTERED INDEX [IX_BinaryStuffWithPCC_Prefix]
  ON #BinaryStuffWithPCC([Prefix])
  WITH (FILLFACTOR = 100);

ALTER TABLE #BinaryStuffMax REBUILD WITH (FILLFACTOR = 100);

ALTER TABLE #BinaryStuffMaxWithPCC REBUILD WITH (FILLFACTOR = 100);

CREATE NONCLUSTERED INDEX [IX_BinaryStuffMaxWithPCC_Prefix]
  ON #BinaryStuffMaxWithPCC([Prefix])
  WITH (FILLFACTOR = 100);


SELECT @Pattern AS [SamplePattern];

SELECT *
FROM   #BinaryStuff
WHERE  [ID] % 1000 = 0;
GO
-------------------------------------------------------------------------------------
-- END OF SETUP


-- Selecting the VARBINARY field tests (highlight and run individually):

DECLARE @Pattern VARBINARY(19),
        @DevNull VARBINARY(19);
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.Payload)) -- @DevNull = tmp.Payload
FROM   #BinaryStuff tmp
WHERE  CONVERT(VARBINARY(19), tmp.Payload) <> @Pattern;

SET STATISTICS TIME OFF;
GO

PRINT '-------------------';

DECLARE @Pattern VARBINARY(19),
        @DevNull VARBINARY(19);
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.Payload)) -- @DevNull = tmp.Payload
FROM   #BinaryStuff tmp
WHERE  SUBSTRING(tmp.Payload, 1, 19) <> @Pattern;

SET STATISTICS TIME OFF;
GO

PRINT '-------------------';

DECLARE @Pattern VARBINARY(19),
        @DevNull VARBINARY(19);
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.Payload)) -- @DevNull = tmp.Payload
FROM   #BinaryStuffWithPCC tmp --WITH (INDEX = [IX_BinaryStuffWithPCC_Prefix])
WHERE  tmp.[Prefix] <> @Pattern;

SET STATISTICS TIME OFF;
GO

-------------------------------

-- Selecting the ID field tests (highlight and run individually):

DECLARE @Pattern VARBINARY(19),
        @ID      INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM   #BinaryStuff tmp
WHERE  CONVERT(VARBINARY(19), tmp.Payload) <> @Pattern;

SET STATISTICS TIME OFF;
GO

PRINT '-------------------';

DECLARE @Pattern VARBINARY(19),
        @ID      INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM   #BinaryStuff tmp
WHERE  SUBSTRING(tmp.Payload, 1, 19) <> @Pattern;

SET STATISTICS TIME OFF;
GO

PRINT '-------------------';

DECLARE @Pattern VARBINARY(19),
        @ID      INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM   #BinaryStuffWithPCC tmp
WHERE  tmp.[Prefix] <> @Pattern;

SET STATISTICS TIME OFF;
GO

-------------------------------

-- Select the ID field against MAX type tests (highlight and run individually):
--   First run-through is with data size <= 100 bytes
--   Second run-through (after running UPDATE statements that follow) is with data size just over 14k bytes

DECLARE @Pattern VARBINARY(19),
        @ID      INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM   #BinaryStuffMax tmp
WHERE  CONVERT(VARBINARY(19), tmp.Payload) <> @Pattern;

SET STATISTICS TIME OFF;
GO

PRINT '-------------------';

DECLARE @Pattern VARBINARY(19),
        @ID      INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM   #BinaryStuffMax tmp
WHERE  SUBSTRING(tmp.Payload, 1, 19) <> @Pattern;

SET STATISTICS TIME OFF;
GO

PRINT '-------------------';

DECLARE @Pattern VARBINARY(19),
        @ID      INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM   #BinaryStuffMaxWithPCC tmp
WHERE  tmp.[Prefix] <> @Pattern;

SET STATISTICS TIME OFF;
GO

PRINT '-------------------';

DECLARE @Pattern VARBINARY(19),
        @ID      INT;
SET @Pattern = CONTEXT_INFO();
--SELECT @Pattern;

SET STATISTICS TIME ON;

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(tmp.ID)) -- @ID = tmp.ID
FROM   #BinaryStuffMax tmp
WHERE  tmp.Payload < @Pattern
OR    tmp.Payload > @Pattern + 0xFF;

SET STATISTICS TIME OFF;
GO

-----
-- Run the following two UPDATEs to test off-page lookups
-- Run the two SELECTs before and after the UPDATEs to see the physical effects
-- After the UPDATEs, re-run the "Select the ID field against MAX type tests"
SELECT AVG(LEN(tmp.Payload)) AS [AvgBytes], MAX(LEN(tmp.Payload)) AS [MaxBytes]
FROM   #BinaryStuffMax tmp;

SELECT so.[name], *
FROM tempdb.sys.dm_db_partition_stats stat
INNER JOIN tempdb.sys.objects so
        ON so.[object_id] = stat.[object_id]
WHERE stat.[object_id] < 0
AND   so.[name] LIKE N'#BinaryStuff%'
ORDER BY so.[name];


UPDATE tmp
SET    tmp.Payload = (tmp.Payload + CONVERT(VARBINARY(MAX), REPLICATE(CONVERT(VARCHAR(MAX), 'ThisIsSomeTest'), 1000)))
FROM   #BinaryStuffMax tmp

UPDATE tmp
SET    tmp.Payload = (tmp.Payload + CONVERT(VARBINARY(MAX), REPLICATE(CONVERT(VARCHAR(MAX), 'ThisIsSomeTest'), 1000)))
FROM   #BinaryStuffMaxWithPCC tmp

ALTER TABLE #BinaryStuffMax REBUILD WITH (FILLFACTOR = 100);

ALTER TABLE #BinaryStuffMaxWithPCC REBUILD WITH (FILLFACTOR = 100);


SELECT AVG(LEN(tmp.Payload)) AS [AvgBytes], MAX(LEN(tmp.Payload)) AS [MaxBytes]
FROM   #BinaryStuffMax tmp;

SELECT so.[name], *
FROM tempdb.sys.dm_db_partition_stats stat
INNER JOIN tempdb.sys.objects so
        ON so.[object_id] = stat.[object_id]
WHERE stat.[object_id] < 0
AND   so.[name] LIKE N'#BinaryStuff%'
ORDER BY so.[name];