Advertisement
Guest User

Example of ordering in inserts

a guest
Aug 9th, 2019
94
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.83 KB | None | 0 0
  1. CREATE DATABASE TestInsertOrder;
  2. GO
  3. USE TestInsertOrder;
  4. GO
  5.  
  6. CREATE TABLE SourceData (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, SomeData VARCHAR(500), ArbOrd INT);
  7.  
  8. INSERT SourceData (SomeData)
  9. 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'
  10.   FROM sys.objects so1 CROSS JOIN sys.objects so2 --CROSS JOIN sys.objects so3
  11.      ;
  12.  
  13. WITH AddArbitraryOrder AS (SELECT *, NewOrd = ROW_NUMBER() OVER (ORDER BY NEWID()) FROM SourceData)
  14. UPDATE sd
  15.    SET ArbOrd = aao.NewOrd
  16.   FROM SourceData        sd
  17.   JOIN AddArbitraryOrder aao ON aao.ID = sd.ID
  18.      ;
  19.  
  20. CREATE TABLE ByRowInIncOrder (ID INT NOT NULL PRIMARY KEY CLUSTERED, SomeData CHAR(500));
  21. CREATE TABLE ByRowInArbOrder (ID INT NOT NULL PRIMARY KEY CLUSTERED, SomeData CHAR(500));
  22. CREATE TABLE InOneInIncOrder (ID INT NOT NULL PRIMARY KEY CLUSTERED, SomeData CHAR(500));
  23. CREATE TABLE InOneInArbOrder (ID INT NOT NULL PRIMARY KEY CLUSTERED, SomeData CHAR(500));
  24.  
  25. GO
  26. INSERT ByRowInIncOrder SELECT ID, SomeData FROM SourceData WHERE ID     = (SELECT COUNT(*) FROM ByRowInIncOrder)+1
  27. INSERT ByRowInArbOrder SELECT ID, SomeData FROM SourceData WHERE ArbOrd = (SELECT COUNT(*) FROM ByRowInIncOrder)+1
  28. GO 9000
  29.  
  30. INSERT InOneInIncOrder SELECT TOP 9000 ID, SomeData FROM SourceData ORDER BY ID    
  31. INSERT InOneInArbOrder SELECT TOP 9000 ID, SomeData FROM SourceData ORDER BY ArbOrd
  32.  
  33. EXEC sp_spaceused 'ByRowInIncOrder' -- 4800 KB data, 600 pages, ~15r/p
  34. EXEC sp_spaceused 'ByRowInArbOrder' -- 7048 KB data, 881 pages, ~10r/p, showing fragmentation due to splits as rows hit the index out of order
  35. EXEC sp_spaceused 'InOneInIncOrder' -- 4800 KB data, 600 pages, ~15r/p
  36. EXEC sp_spaceused 'InOneInArbOrder' -- 4800 KB data, 600 pages, ~15r/p
  37.  
  38. GO
  39. USE master
  40. GO
  41. DROP DATABASE TestInsertOrder
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement