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

• Design Guidelines (Very Very Good)
• SQL Server Code Review Checklist
• Ten Things I Hate to See in T-SQL

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!!