VarcharKeying.sql

/***************************************************************************
* SETUP
***************************************************************************/

SELECT @@VERSION
--Microsoft SQL Server 2014 - 12.0.4213.0 (X64) 
--	Jun  9 2015 12:06:16 
--	Copyright (c) Microsoft Corporation
--	Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
GO

-- Misc hardware info
	-- Laptop with Quad core i7 2.7GHz, 64 bit
	-- SQL on dedicated disk, but it's a slow spinning drive

-- Set server memory to ~16GB; don't run in production!
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'16000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

-- Create a table of 10MM rows of string values with the proper VARCHAR lengths
CREATE TABLE dbo.testVarchar (
	s1 VARCHAR(18) NOT NULL,
	s2 VARCHAR(75) NOT NULL,
	s3 VARCHAR(9) NOT NULL,
	s4 VARCHAR(15) NOT NULL,
	s5 VARCHAR(123) NOT NULL,
	s6 VARCHAR(5) NOT NULL
)
GO
INSERT INTO dbo.testVarchar WITH (TABLOCK) (s1, s2, s3, s4, s5, s6)
SELECT LEFT(s,18), LEFT(s, 75), LEFT(s, 9), LEFT(s, 15), LEFT(s, 123), LEFT(s, 5)
FROM (
	SELECT TOP 10000000 CONVERT(VARCHAR(MAX), v1.name) +
			CONVERT(VARCHAR(MAX), v2.name) +
			CONVERT(VARCHAR(MAX), v3.name) AS s
	FROM master..spt_values v1
	CROSS JOIN master..spt_values v2
	CROSS JOIN master..spt_values v3
	WHERE v1.name IS NOT NULL
		AND v2.name IS NOT NULL
		AND v3.name IS NOT NULL
) x
GO

-- Create another table with the same data, but all VARCHAR(256)
CREATE TABLE dbo.testVarchar256 (
	s1 VARCHAR(256) NOT NULL,
	s2 VARCHAR(256) NOT NULL,
	s3 VARCHAR(256) NOT NULL,
	s4 VARCHAR(256) NOT NULL,
	s5 VARCHAR(256) NOT NULL,
	s6 VARCHAR(256) NOT NULL
)
GO
INSERT INTO dbo.testVarchar256 WITH (TABLOCK) (s1, s2, s3, s4, s5, s6)
SELECT *
FROM dbo.testVarchar
GO

-- Check the sizes of the two tables
SELECT t.name, ps.row_count, (ps.reserved_page_count*8.0) / (1024.0) AS sizeMb
FROM sys.tables t WITH (NOLOCK)
JOIN sys.dm_db_partition_stats ps WITH (NOLOCK)
	ON ps.object_id = t.object_id
WHERE t.name IN ('testVarchar','testVarchar256','testVarcharChangeTo256')
--name				row_count	sizeMb
--testVarchar		10000000	1492.7656250
--testVarchar256	10000000	1492.7578125
GO

/***************************************************************************
* TEST CASE
*	On my machine, the VARCHAR(CorrectLength) example completes
*	almost 10x faster than the VARCHAR(256) example
***************************************************************************/

SET STATISTICS TIME, IO ON
GO

-- CPU time = 37674 ms,  elapsed time = 19206 ms.
CREATE CLUSTERED INDEX IX_testVarchar
ON dbo.testVarchar (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO

-- CPU time = 29827 ms,  elapsed time = 180581 ms.
CREATE CLUSTERED INDEX IX_testVarchar256
ON dbo.testVarchar256 (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO

/***************************************************************************
* DROP INDEXES (if you want to try again without rebuilding full data set)
***************************************************************************/

DROP INDEX testVarchar.IX_testVarchar
GO
DROP INDEX testVarchar256.IX_testVarchar256
GO