Advertisement
S_Madanska

transaction examples

Nov 14th, 2021 (edited)
1,169
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ALTER DATABASE TEST
  2. SET RECOVERY SIMPLE;
  3.  
  4. use TEST;
  5.  
  6. CREATE TABLE dbo.Table1
  7. (ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  8.  detail varchar(10))
  9.  
  10. --Initial IAM, PFS Page Allocation and etc
  11. INSERT INTO dbo.Table1 VALUES ('test0');
  12. --Clear the transaction log
  13. CHECKPOINT;
  14.  
  15. INSERT INTO dbo.Table1 VALUES ('test1');
  16. INSERT INTO dbo.Table1 VALUES ('test2');
  17.  
  18. DBCC LOG (TEST, 0);
  19.  
  20. --Clear the table
  21. TRUNCATE TABLE dbo.Table1;
  22.  
  23.  
  24. --Initial IAM, PFS Page Allocation and etc
  25. INSERT INTO dbo.Table1 VALUES ('test0');
  26. --Clear the transaction log
  27. CHECKPOINT;
  28.  
  29. SET IMPLICIT_TRANSACTIONS ON;
  30. INSERT INTO dbo.Table1 VALUES ('test1');
  31. INSERT INTO dbo.Table1 VALUES ('test2');
  32. COMMIT;
  33. SET IMPLICIT_TRANSACTIONS OFF;
  34.  
  35. DBCC LOG (TEST, 0);
  36.  
  37. --Explicit Transaction
  38. TRUNCATE TABLE dbo.Table1;
  39.  
  40.  
  41. --Initial IAM, PFS Page Allocation and etc
  42. INSERT INTO dbo.Table1 VALUES ('test0');
  43. --Clear the transaction log
  44. CHECKPOINT;
  45.  
  46. BEGIN TRAN
  47. INSERT INTO dbo.Table1 VALUES ('test1');
  48. INSERT INTO dbo.Table1 VALUES ('test2');
  49. COMMIT TRAN;
  50.  
  51. DBCC LOG (TEST, 0);
  52.  
  53.  
  54. BEGIN TRAN
  55. SAVE TRAN POINT1
  56. TRUNCATE TABLE TABLE1
  57. ROLLBACK TRAN  POINT1
  58. COMMIT TRAN
  59.  
  60. SELECT * FROM TABLE1
  61.  
  62. BEGIN TRAN
  63. SAVE TRAN POINT1
  64. TRUNCATE TABLE TABLE1
  65. COMMIT TRAN
  66.  
  67.  
  68. insert into Table1
  69. values('Table2')
  70.  
  71. save tran point1
  72.  
  73. insert into Table1
  74. values('Table3')
  75.  
  76. save tran point1
  77.  
  78. insert into Table1
  79. values('Table4')
  80.  
Advertisement
RAW Paste Data Copied
Advertisement