NEERAJPRASADSHARMA

1 Fragmented Table Creation

Jul 20th, 2017
60
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2.  
  3. BEGIN TRAN
  4.  
  5.  
  6.  
  7.  
  8.  
  9. select  top 1000000
  10. row_number () over ( order by (Select null)) n into Numbers from
  11. sys.all_columns  a cross  join  sys.all_columns
  12.  
  13.  
  14.  
  15.  
  16. CREATE TABLE [DBO].[FRAG1] (
  17. Primarykey int NOT NULL ,
  18. SomeData3 char(300) NOT NULL )
  19.  
  20.  
  21.  
  22. INSERT INTO [DBO].[FRAG1]
  23. SELECT
  24. n ,
  25.  
  26. 'Some text..'
  27.  
  28. FROM Numbers
  29.  
  30. Where N/2 = N/2.0
  31.  
  32.  
  33.  
  34.  
  35. ALTER TABLE DBO.[FRAG1]
  36. ADD CONSTRAINT PK_FRAG1 PRIMARY KEY (Primarykey)
  37.  
  38. --500000
  39. Update DBO.[FRAG1] SET Primarykey =  Primarykey-500001
  40. Where  Primarykey>500001
  41.  
  42.  
  43.  
  44.  
  45.  
  46.  
  47.  
  48.  
  49.  
  50.  
  51. GO
  52.  
  53. drop table [DBO].FRAG2
  54.  
  55. CREATE TABLE [DBO].FRAG2 (
  56. Primarykey int NOT NULL ,
  57. SomeData3 char(500) NOT NULL )
  58.  
  59.  
  60.  
  61. INSERT INTO [DBO].FRAG2
  62. SELECT
  63. n ,
  64.  
  65. 'Some text..'
  66.  
  67. FROM Numbers
  68.  
  69. Where N/2 = N/2.0
  70.  
  71.  
  72.  
  73.  
  74.  
  75.  
  76.  
  77. ALTER TABLE DBO.FRAG2
  78. ADD CONSTRAINT PK_FRAG2 PRIMARY KEY (Primarykey)
  79.  
  80. --500000
  81. Update DBO.FRAG2 SET Primarykey =  Primarykey-500001
  82. Where  Primarykey>500001
  83.  
  84.  
  85.  
  86.  
  87.  
  88.  
  89.  
  90.  
  91. GO
  92.  
  93.  
  94. CREATE TABLE [DBO].FRAG3 (
  95. Primarykey int NOT NULL ,
  96. SomeData3 char(1000) NOT NULL )
  97.  
  98.  
  99.  
  100. INSERT INTO [DBO].FRAG3
  101. SELECT
  102. n ,
  103.  
  104. 'Some text..'
  105.  
  106. FROM Numbers
  107.  
  108. Where N/2 = N/2.0
  109.  
  110.  
  111.  
  112.  
  113. ALTER TABLE DBO.FRAG3
  114. ADD CONSTRAINT PK_FRAG3 PRIMARY KEY (Primarykey)
  115.  
  116. --500000
  117. Update DBO.FRAG3 SET Primarykey =  Primarykey-500001
  118. Where  Primarykey>500001
  119.  
  120.  
  121.  
  122.  
  123.  
  124.  
  125.  
  126. GO
  127.  
  128.  
  129.  
  130.  
  131. CREATE TABLE [DBO].FRAG4 (
  132. Primarykey int NOT NULL ,
  133. SomeData3 char(2000) NOT NULL )
  134.  
  135.  
  136.  
  137. INSERT INTO [DBO].FRAG4
  138. SELECT
  139. n ,
  140.  
  141. 'Some text..'
  142.  
  143. FROM Numbers
  144.  
  145. Where N/2 = N/2.0
  146.  
  147.  
  148.  
  149.  
  150. ALTER TABLE DBO.FRAG4
  151. ADD CONSTRAINT PK_FRAG4 PRIMARY KEY (Primarykey)
  152.  
  153. --500000
  154. Update DBO.FRAG4 SET Primarykey =  Primarykey-500001
  155. Where  Primarykey>500001
  156.  
  157.  
  158.  
  159.  
  160.  
  161.  
  162.  
  163.  
  164.  
  165.  
  166.  
  167. COMMIT
RAW Paste Data