SHARE
TWEET

1 Fragmented Table Creation

NEERAJPRASADSHARMA Jul 20th, 2017 37 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
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