G2A Many GEOs
SHARE
TWEET

Example of ordering in inserts

a guest Aug 9th, 2019 122 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  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 -- comment this out before asking SSMS for the query plan or it'll blow up somewhat!
  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
RAW Paste Data
Ledger Nano X - The secure hardware wallet
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top