May 15, 2017 at 1:49 pm
Truncate logs the page deallocations
What, if anything, does that get you from a practical point of view? Does that allow the undoing of a truncation?
May 15, 2017 at 1:53 pm
If you run In a transaction without committing, yes
Try it for yourself
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 15, 2017 at 1:59 pm
If you run In a transaction without committing, yes
Try it for yourself
I can't think that I've ever run truncates in a transactions. I only ever truncate ETL staging tables. By their nature I don't have to undo that action. But I thought that a truncation could not be undone, and wasn't sure if your comment was simply pointing something out that was technically true but operationally meaningless, or if there was an advantage to knowing that.
May 15, 2017 at 2:06 pm
As I said try it for yourself
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 15, 2017 at 2:14 pm
RonKyle - Monday, May 15, 2017 1:59 PMIf you run In a transaction without committing, yes
Try it for yourselfI can't think that I've ever run truncates in a transactions. I only ever truncate ETL staging tables. By their nature I don't have to undo that action. But I thought that a truncation could not be undone, and wasn't sure if your comment was simply pointing something out that was technically true but operationally meaningless, or if there was an advantage to knowing that.
It is not "operationally meaningless". Joe Celko stated that truncates are not logged which is simply flat out not true. The truncate operation logs the page deallocations and is not concerned with the data.
Consider a process where maybe you would want to "clear out" a table but restore the data if something goes wrong. This is not all that uncommon for the "permanent temp table". If truncates weren't logged you would be forced to use delete which can be magnitudes slower than a truncate. I realize the example in incredibly contrived and I would never support using a design like that but it is possible to do. And if this is a system you use on a regular there very much should be some advantage in having knowledge of how the system works.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2017 at 2:21 pm
I did some testing on a dev box and set stats on and ran a truncate and delete in a transaction to quick check timings, truncate won.
Performing the change in the next hour as per change window and going to truncate both tables and reseed the ID columns.
Thanks for the informative posts
May 15, 2017 at 2:33 pm
stephen.plant - Monday, May 15, 2017 2:21 PMI did some testing on a dev box and set stats on and ran a truncate and delete in a transaction to quick check timings, truncate won.
Performing the change in the next hour as per change window and going to truncate both tables and reseed the ID columns.
Thanks for the informative posts
If you use truncate there is no need to reseed the identity columns, unless you need the values to be a value other than the defaults. But I wasn't quite clear your requirements there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2017 at 2:40 pm
I believe Stephen intends to retain the seed value hence my previous recommendation
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 15, 2017 at 6:45 pm
jcelko212 32090 - Monday, May 15, 2017 1:34 PMThe major difference between delete and truncate is that delete is a standard SQL command, but it does logging so that the deletion can be backed out. Truncation, on the other hand, is an old Sybase extension that moves a pointer to the very start of the physical storage of the data, so the storage can be overwritten. It doesn't do any logging. It also doesn't do any porting that was important to you.You might want to reconsider using identity as a key. It can never be a proper relational key since it's a count of physical insertion attempts on one machine, to one table in one particular SQL product. You'd be better off using the CREATE SEQUENCE structure instead. Essentially, this creates a generator for well-controlled numeric values in an increasing sequence. It is not a table property! Since its external it can be referenced in other places in your SQL.
You need to study SQL Server and T-SQL a bit more before saying such things, Joe. Truncate does, in fact, do logging. It logs the fact that the pages have been de-allocated and, if used in a transaction, can indeed be rolled back as with any logged action.
For single table usage, a SEQUENCE is no better than using IDENTITY and, in fact, can be worse because everyone has to remember to get the next value for a SEQUENCE. Of course, a SEQUENCE does make the infamous Invoice/Invoice Detail problem a bit easier and is the absolute berries when using a common incremental value across multiple tables, but that would also fly in your face as to what a surrogate key should actually be used for (you normally say it shouldn't generally be used at all).
And, to date, you have not identified what would be used as a natural key for a "personel" table that relies on no externally available key such as a license or certification number that would actually survive the test of time as a Primary Key.
As for true portability, that's a myth.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2017 at 7:57 pm
RonKyle - Monday, May 15, 2017 1:59 PMIf you run In a transaction without committing, yes
Try it for yourselfI can't think that I've ever run truncates in a transactions. I only ever truncate ETL staging tables. By their nature I don't have to undo that action. But I thought that a truncation could not be undone, and wasn't sure if your comment was simply pointing something out that was technically true but operationally meaningless, or if there was an advantage to knowing that.
Here's the code I normally use to demonstrate that a TRUNCATE can be rolled back if it was done in a transaction along with a bunch of other things. Best viewing results are achieved in the "Results to Text" mode. Details are in the comments and the output caused by the RAISERROR lines of code. I'm with you, though... I've never used it in a transaction and probably won't ever need to.
IF OBJECT_ID('tempdb..#JBMTest','U') IS NOT NULL
DROP TABLE #JBMTest
;
--===== Create and populate the test table on the fly. We don't need a PK for this
-- demonstration so we can use SELECT/INTO to keep it simple.
-- Not to worry... this only takes about 4 seconds or less.
SELECT TOP (1000000)
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeMoney = ABS(CHECKSUM(NEWID()))%10000 /100.0,
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex09 = RIGHT(NEWID(),09)
INTO #JBMTest
FROM sys.all_columns t1
CROSS JOIN sys.all_columns t2
;
--===== Start a transaction, do a truncate and a couple of other tests.
-- Report on each step.
BEGIN TRANSACTION;
PRINT REPLICATE('=',110);
RAISERROR ('Rowcount and MAX SomeID before the TRUNCATE...',0,0) WITH NOWAIT;
SELECT COUNT(*), MAX(SomeID) FROM #JBMTest;
---------------------------------------------------------------------------------------------------
PRINT REPLICATE('=',110);
TRUNCATE TABLE #JBMTest;
RAISERROR ('Rowcount and MAX SomeID after the TRUNCATE...',0,0) WITH NOWAIT;
SELECT COUNT(*), MAX(SomeID) FROM #JBMTest;
---------------------------------------------------------------------------------------------------
PRINT REPLICATE('=',110);
--===== Insert a row to see what happens to the identity column
INSERT INTO #JBMTest
(SomeInt,SomeLetters2,SomeMoney,SomeDate,SomeHex09)
SELECT SomeInt = ABS(CHECKSUM(NEWID()))%50000+1
,SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
,SomeMoney = ABS(CHECKSUM(NEWID()))%10000 /100.0
,SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
,SomeHex09 = RIGHT(NEWID(),09);
RAISERROR ('Content of table after single insert...',0,0) WITH NOWAIT;
SELECT * FROM #JBMTest;
---------------------------------------------------------------------------------------------------
PRINT REPLICATE('=',110);
ROLLBAcK ;
RAISERROR ('Rowcount and MAX SomeID after the ROLLBACK...',0,0) WITH NOWAIT;
SELECT COUNT(*), MAX(SomeID) FROM #JBMTest;
---------------------------------------------------------------------------------------------------
--====== Insert another row to see what happens to the identity column
PRINT REPLICATE('=',110);
INSERT INTO #JBMTest
(SomeInt,SomeLetters2,SomeMoney,SomeDate,SomeHex09)
SELECT SomeInt = ABS(CHECKSUM(NEWID()))%50000+1
,SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)
,SomeMoney = ABS(CHECKSUM(NEWID()))%10000 /100.0
,SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME)
,SomeHex09 = RIGHT(NEWID(),09);
RAISERROR ('Rowcount and MAX SomeID after the post ROLLBACK insert...',0,0) WITH NOWAIT;
SELECT COUNT(*), MAX(SomeID) FROM #JBMTest;
---------------------------------------------------------------------------------------------------
--===== Display the top 10 rows in reverse order, just to be sure.
PRINT REPLICATE('=',110);
RAISERROR ('Bottom10 rows after everything is done...',0,0) WITH NOWAIT;
SELECT TOP 10 * FROM #JBMTest ORDER BY SomeID DESC;
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2017 at 1:20 am
I agree, you probably won't even use it but the statement that a truncate does not log is untrue and I corrected this.
Personally I don't care two hoots whether you truncate\dekete or drop all your tables, I'm concerned with facts!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 16, 2017 at 2:47 am
Perry Whittle - Tuesday, May 16, 2017 1:20 AMI agree, you probably won't even use it but the statement that a truncate does not log is untrue and I corrected this.Personally I don't care two hoots whether you truncate\dekete or drop all your tables, I'm concerned with facts!
You don't have to convince me. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2017 at 3:16 am
Jeff Moden - Tuesday, May 16, 2017 2:47 AMPerry Whittle - Tuesday, May 16, 2017 1:20 AMI agree, you probably won't even use it but the statement that a truncate does not log is untrue and I corrected this.Personally I don't care two hoots whether you truncate\dekete or drop all your tables, I'm concerned with facts!You don't have to convince me. 😉
Hi Jeff
not trying to buddy, i was thinking more of ronkyle and joe cakehole 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 16, 2017 at 9:49 am
Perry Whittle - Tuesday, May 16, 2017 3:16 AMJeff Moden - Tuesday, May 16, 2017 2:47 AMPerry Whittle - Tuesday, May 16, 2017 1:20 AMI agree, you probably won't even use it but the statement that a truncate does not log is untrue and I corrected this.Personally I don't care two hoots whether you truncate\dekete or drop all your tables, I'm concerned with facts!You don't have to convince me. 😉
Hi Jeff
not trying to buddy, i was thinking more of ronkyle and joe cakehole 😉
BAAAA-HAAAAA-HAAAA!!!! I came pretty close on my guess there! Thanks for the laugh! I definitely needed it! 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2017 at 10:04 am
Jeff Moden - Tuesday, May 16, 2017 9:49 AMPerry Whittle - Tuesday, May 16, 2017 3:16 AMJeff Moden - Tuesday, May 16, 2017 2:47 AMPerry Whittle - Tuesday, May 16, 2017 1:20 AMI agree, you probably won't even use it but the statement that a truncate does not log is untrue and I corrected this.Personally I don't care two hoots whether you truncate\dekete or drop all your tables, I'm concerned with facts!You don't have to convince me. 😉
Hi Jeff
not trying to buddy, i was thinking more of ronkyle and joe cakehole 😉BAAAA-HAAAAA-HAAAA!!!! I came pretty close on my guess there! Thanks for the laugh! I definitely needed it! 😀
😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply