"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

June 08, 2011

SQL Tip of the Day

Tip #1

Difference between TRUNCATE and DELETE Operation
Blog post SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged provides clarity and good answer. Summarizing the points.
  • TRUNCATE TABLE removes the data by deallocating the data pages
  • DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row
  • TRUNCATE doesn’t preserve the identity value but delete does
  • You can’t TRUNCATE tables that are referenced by a foreign key constraint
  • TRUNCATE can be rolled back if it is implemented as TRANSACTION. This was a good learning.
Tip #2
TSQL Coding Tips are provided in posts. Below 3 posts are useful additions for code review. Bookmarking them for future reference
Tip #3

Estimating Disk Space for SQL Server
Sizing Lounge - Found this tool in one of msdn blog posts. Example snapshot after providing parameters.

More Reads
Estimating the Size of an SQL Server Database

Tip #4

SQL Diag is another Tool for Analyzing SQL Performance. Tool was recently released in codeplex.
SQL Diag Configuration Tool (SDCT)

Tip #5

SQL Server Community FAQs eBook download - Consolidated Common FAQ

Tip #6

Forcing a table lock in SQL Server - Manually enforce table lock

Happy Reading!!

No comments: