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