SHARE
TWEET

Untitled

SQLSoldier Sep 13th, 2014 210 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Demo 1: 0 rows due to CREATE TABLE or TRUNCATE
  2. USE master;
  3.  
  4. IF db_id('TestIAM') IS NOT NULL
  5.         DROP DATABASE TestIAM;
  6. GO
  7.  
  8. CREATE DATABASE TestIAM;
  9. GO
  10.  
  11. USE TestIAM;
  12. GO
  13.  
  14. CREATE TABLE dbo.TestIAM (TestID INT IDENTITY(1,1) NOT NULL PRIMARY KEY);
  15. GO
  16.  
  17. -- Returns 0 rows
  18. SELECT *
  19. FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
  20. GO
  21.  
  22. INSERT INTO dbo.TestIAM DEFAULT VALUES;
  23. GO
  24.  
  25. -- Returns 2 rows
  26. SELECT *
  27. FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
  28. GO
  29.  
  30. TRUNCATE TABLE dbo.TestIAM;
  31. GO
  32.  
  33. -- Returns 0 rows
  34. SELECT *
  35. FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
  36. GO
  37.  
  38. -- Demo 2: 0 rows due to DELETE FROM
  39. USE master;
  40.  
  41. IF db_id('TestIAM') IS NOT NULL
  42.         DROP DATABASE TestIAM;
  43. GO
  44.  
  45. CREATE DATABASE TestIAM;
  46. GO
  47.  
  48. USE TestIAM;
  49. GO
  50.  
  51. CREATE TABLE dbo.TestIAM (TestID INT IDENTITY(1,1) NOT NULL PRIMARY KEY);
  52. GO
  53.  
  54. -- Returns 0 rows
  55. SELECT *
  56. FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
  57. GO
  58.  
  59. INSERT INTO dbo.TestIAM DEFAULT VALUES;
  60. GO
  61.  
  62. -- Returns 2 rows
  63. SELECT *
  64. FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
  65. GO
  66.  
  67. DELETE FROM dbo.TestIAM;
  68. GO
  69.  
  70. -- Returns 2 rows
  71. SELECT *
  72. FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 1, NULL, 'detailed');
  73. GO
  74.  
  75. -- Demo 3: 0 rows due to SELECT INTO WHERE 0 = 1
  76. USE master;
  77.  
  78. IF db_id('TestIAM') IS NOT NULL
  79.         DROP DATABASE TestIAM;
  80. GO
  81.  
  82. CREATE DATABASE TestIAM;
  83. GO
  84.  
  85. USE TestIAM;
  86. GO
  87.  
  88. SELECT *
  89. INTO dbo.TestIAM
  90. FROM sys.DATABASES
  91. WHERE 0 = 1;
  92. GO
  93.  
  94. -- Returns lots o' rows
  95. SELECT *
  96. FROM sys.dm_db_database_page_allocations(db_id(), object_id('dbo.TestIAM'), 0, NULL, 'detailed');
  97. GO
RAW Paste Data
Top