Knowledge share 2014-11

Traceflag 3226:
In my production environment, we take transaction log backups every 10 minutes for all 40+ databases. There are thousands or Successful backup messages in my logs which is very annoying. SQL Server 2008 introduced traceflag 3226 to disable entering successful backup log entries.

.
Choose between OR , IN:
When have to search for a value in multiple columns I have been using WHERE Col1 = x AND Col2 = x AND Col3 = x, Col4 = x syntax but I learns about using it differently WHERE x in (Col1, Col2, Col3, Col4)

Both these queries will give same result set

SELECT * FROM TableA 
WHERE Col1 = 12 OR Col2 = 12 OR Col3 = 12 OR Col4 = 12

SELECT * FROM TableA 
WHERE 12 in (Col1, Col2, Col3, Col4)

.
Halloween protection:
Halloween protection refers to the mechanisms designed to prevent an update statement from selecting the same rows for change over and over again.
Example: We have a table TableA with non-clustered index on Col1 and I am executing an update statement as follows:

 UPDATE TableA SET Col1 = Col*2 WHERE Col1 < 10 

In this update statement same non-clustered index can be used to select and update. In such situations, SQL introduces SPOOL operation in the Execution plan to make sure that same row is not selected for multiple updates.
Reference:
http://blogs.msdn.com/b/craigfr/archive/2008/02/27/halloween-protection.aspx
http://sqlblog.com/blogs/paul_white/archive/2013/02/21/halloween-protection-the-complete-series.aspx

Advertisements
This entry was posted in knowledge, SQL and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s