Skip to content

Instantly share code, notes, and snippets.

@MarkSS

MarkSS/gist:1323037

Created Oct 28, 2011
Embed
What would you like to do?
Clustered Index Inserts - Sort Tests
:setvar MyTablePreLoadRows 1000
:setvar InsertRows 10
:setvar RowFillerSize 50
SET NOCOUNT ON;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET STATISTICS XML OFF;
GO
/*
Create two tables, staging and target
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTableStaging]') AND type in (N'U'))
DROP TABLE [dbo].[MyTableStaging]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
CREATE TABLE dbo.MyTableStaging
(
col1 INT IDENTITY(1,1)
, col2 INT NOT NULL DEFAULT 0
, col3 CHAR($(RowFillerSize)) DEFAULT REPLICATE ('A', $(RowFillerSize))
, CONSTRAINT PK_MyTableStaging PRIMARY KEY CLUSTERED (col1 ASC)
)
GO
CREATE TABLE dbo.MyTable
(
col1 INT
, col2 INT NOT NULL
, col3 CHAR($(RowFillerSize)) NOT NULL
, CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED (col1 ASC)
)
GO
/*
Number of rows in target table does not impact sort/no-sort execution plan
*/
INSERT dbo.MyTableStaging DEFAULT VALUES
GO $(MyTablePreLoadRows)
UPDATE
dbo.MyTableStaging
SET
col2 = ABS(CHECKSUM(NEWID()))
GO
INSERT
dbo.MyTable
SELECT
col1
, col2
, col3
FROM
dbo.MyTableStaging
GO
DELETE dbo.MyTableStaging
GO
/*
Populate staging, randomise contents of col2 for out-of-order test later
Tipping point for when sort required will vary, in my case 26 requires sort, 25 doesn't
*/
INSERT dbo.MyTableStaging DEFAULT VALUES
GO $(InsertRows)
UPDATE
dbo.MyTableStaging
SET
col2 = ABS(CHECKSUM(NEWID()))
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;
GO
/*
Test 1 - Staging table clustered index as target clustered index
*/
INSERT
dbo.MyTable
SELECT
col1
, col2
, col3
FROM
dbo.MyTableStaging
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET STATISTICS XML OFF;
GO
TRUNCATE TABLE dbo.MyTable
GO
ALTER TABLE dbo.MyTableStaging
DROP CONSTRAINT PK_MyTableStaging
GO
ALTER TABLE dbo.MyTableStaging
ADD CONSTRAINT PK_MyTableStaging PRIMARY KEY CLUSTERED (col1 DESC)
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;
GO
/*
Test 2 - Staging clustered index is reverse order to target
*/
INSERT
dbo.MyTable
SELECT
col1
, col2
, col3
FROM
dbo.MyTableStaging
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET STATISTICS XML OFF;
GO
TRUNCATE TABLE dbo.MyTable
GO
ALTER TABLE dbo.MyTableStaging
DROP CONSTRAINT PK_MyTableStaging
GO
CREATE CLUSTERED INDEX IX_MyTableStaging_col2 ON dbo.MyTableStaging (col2 ASC)
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS XML ON;
GO
/*
Test 3 - Staging clustered index is on col2 (random INT)
*/
INSERT
dbo.MyTable
SELECT
col1
, col2
, col3
FROM
dbo.MyTableStaging
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.