Advertisement
Guest User

Untitled

a guest
Mar 27th, 2017
422
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.86 KB | None | 0 0
  1. CREATE TABLE #AGE_BUCKETS (AGE INT NULL, CNT INT NOT NULL);
  2.  
  3. INSERT INTO #AGE_BUCKETS
  4. VALUES
  5. (NULL,6266612),
  6. (-971,1),
  7. (5,1),
  8. (13,19),
  9. (14,299),
  10. (15,740),
  11. (16,1190),
  12. (17,2419),
  13. (18,3842),
  14. (19,5615),
  15. (20,8375),
  16. (21,12414),
  17. (22,17768),
  18. (23,23478),
  19. (24,29857),
  20. (25,36860),
  21. (26,42358),
  22. (27,48682),
  23. (28,44090),
  24. (29,40243),
  25. (30,35525),
  26. (31,31584),
  27. (32,28900),
  28. (33,25339),
  29. (34,22813),
  30. (35,20744),
  31. (36,17656),
  32. (37,16935),
  33. (38,13313),
  34. (39,11700),
  35. (40,10383),
  36. (41,8753),
  37. (42,7838),
  38. (43,6805),
  39. (44,5701),
  40. (45,5216),
  41. (46,4698),
  42. (47,4787),
  43. (48,3710),
  44. (49,3124),
  45. (50,2741),
  46. (51,2274),
  47. (52,2019),
  48. (53,1838),
  49. (54,1454),
  50. (55,1331),
  51. (56,1159),
  52. (57,1149),
  53. (58,953),
  54. (59,756),
  55. (60,690),
  56. (61,651),
  57. (62,596),
  58. (63,487),
  59. (64,419),
  60. (65,398),
  61. (66,289),
  62. (67,330),
  63. (68,231),
  64. (69,219),
  65. (70,204),
  66. (71,167),
  67. (72,140),
  68. (73,94),
  69. (74,86),
  70. (75,86),
  71. (76,63),
  72. (77,56),
  73. (78,34),
  74. (79,27),
  75. (80,30),
  76. (81,20),
  77. (82,22),
  78. (83,13),
  79. (84,20),
  80. (85,9),
  81. (86,12),
  82. (87,41),
  83. (88,12),
  84. (89,20),
  85. (90,9),
  86. (91,10),
  87. (92,18),
  88. (93,15),
  89. (94,20),
  90. (95,42),
  91. (96,111),
  92. (97,923);
  93.  
  94. CREATE TABLE dbo.X_AGES (
  95. Id BIGINT NOT NULL IDENTITY (1, 1),
  96. AGE INT NULL
  97. );
  98.  
  99. INSERT INTO dbo.X_AGES WITH (TABLOCK) (AGE)
  100. SELECT ab.AGE
  101. FROM #AGE_BUCKETS ab
  102. CROSS APPLY (SELECT TOP (ab.CNT) NULL FROM dbo.GetNums(ab.CNT)) v(x)
  103. OPTION (FORCE ORDER, NO_PERFORMANCE_SPOOL, QUERYTRACEON 8649); -- I don't want to talk about it
  104.  
  105.  
  106. -- the real table
  107. CREATE TABLE dbo.Users (
  108.     Id BIGINT NOT NULL IDENTITY (1, 1),
  109.     Age INT NULL,
  110.     FLUFF VARCHAR(100) NOT NULL,
  111.     PRIMARY KEY (Id)
  112. );
  113.  
  114. INSERT INTO dbo.Users WITH (TABLOCK) (Age, FLUFF)
  115. SELECT AGE, REPLICATE('CHEATTOWIN', 10)
  116. FROM dbo.X_AGES
  117. ORDER BY Id % 1000 - 0.1 * COALESCE(Age, 0); -- some lazy ordering to make the data less uniform
  118.  
  119. CREATE INDEX ix_Age_id ON dbo.Users (Age) WITH (DATA_COMPRESSION = PAGE);
  120.  
  121. DROP TABLE dbo.X_AGES;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement