Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE DATABASE TestInsertOrder;
- GO
- USE TestInsertOrder;
- GO
- CREATE TABLE SourceData (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, SomeData VARCHAR(500), ArbOrd INT);
- INSERT SourceData (SomeData)
- SELECT 'Actual content of this does not matter for this test, we just want some data to reduce rows/page to make the fragmentation easier to see'
- FROM sys.objects so1 CROSS JOIN sys.objects so2 --CROSS JOIN sys.objects so3
- ;
- WITH AddArbitraryOrder AS (SELECT *, NewOrd = ROW_NUMBER() OVER (ORDER BY NEWID()) FROM SourceData)
- UPDATE sd
- SET ArbOrd = aao.NewOrd
- FROM SourceData sd
- JOIN AddArbitraryOrder aao ON aao.ID = sd.ID
- ;
- CREATE TABLE ByRowInIncOrder (ID INT NOT NULL PRIMARY KEY CLUSTERED, SomeData CHAR(500));
- CREATE TABLE ByRowInArbOrder (ID INT NOT NULL PRIMARY KEY CLUSTERED, SomeData CHAR(500));
- CREATE TABLE InOneInIncOrder (ID INT NOT NULL PRIMARY KEY CLUSTERED, SomeData CHAR(500));
- CREATE TABLE InOneInArbOrder (ID INT NOT NULL PRIMARY KEY CLUSTERED, SomeData CHAR(500));
- GO
- INSERT ByRowInIncOrder SELECT ID, SomeData FROM SourceData WHERE ID = (SELECT COUNT(*) FROM ByRowInIncOrder)+1
- INSERT ByRowInArbOrder SELECT ID, SomeData FROM SourceData WHERE ArbOrd = (SELECT COUNT(*) FROM ByRowInIncOrder)+1
- GO 9000
- INSERT InOneInIncOrder SELECT TOP 9000 ID, SomeData FROM SourceData ORDER BY ID
- INSERT InOneInArbOrder SELECT TOP 9000 ID, SomeData FROM SourceData ORDER BY ArbOrd
- EXEC sp_spaceused 'ByRowInIncOrder' -- 4800 KB data, 600 pages, ~15r/p
- EXEC sp_spaceused 'ByRowInArbOrder' -- 7048 KB data, 881 pages, ~10r/p, showing fragmentation due to splits as rows hit the index out of order
- EXEC sp_spaceused 'InOneInIncOrder' -- 4800 KB data, 600 pages, ~15r/p
- EXEC sp_spaceused 'InOneInArbOrder' -- 4800 KB data, 600 pages, ~15r/p
- GO
- USE master
- GO
- DROP DATABASE TestInsertOrder
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement