Logging just the columns you are interested in

So you want to log the changes to a table in MS SQL Server, but you are only interested in some columns.

Writing some SQL to compare the updated and inserted tables in a TRIGGER using ORs might seem the way to go, but its really, really slow.

I found this better solution on stackoverflow and I really like the readability and speed.

Correcting errors/root cause analysis

Mistakes happen, that is normal and perfectly acceptable.

The same mistake happening again is not acceptable.

There are many well established techniques for preventing the same mistake happening again. I like the sites below. Remember these key points

• Processes fail, not people.
• back to standard fixes are just fire fighting. e.g. fix a puncture
• improving the standard is what you want. e.g. puncture proof tyres, new route with less debris.

SQL server cached plans

If you are using OpenQuery or sp_execute_sql a lot then your server is probably loaded with cached execution plans, for adhoc queries that will never be used again.

These two grate blogs deal with the issue nicely
http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries 
http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/ 

Backup Backup Backup

Yup I said it three times, because with backups once is never enough.

For home I take three backups of my files

• Local direct mirror on a different hard drive in the same PC- if the primary drive fails I have all my files just as there were before.
• The mirror to a remote site - if the PC dies or is destroyed and takes the primary and secondary drives with it, then I have all my files on a remote site.
• Local incremental on the same drive as the mirror - if I accidentally delete something, this is where I go to get it back.

All of this isn't perfect, but its a doddle to get working at home and needs pretty much zero management.

Note I'd never rely on this level of backup for a companies data, but this is home, its my data, and how often do you remember to change backup destinations and swap them offsite?

Continue reading after the break on how to set this up

Windows 2000 on Hyper-V 2012 R2

So you have a Windows 2000 machine that you really need to keep working even though you know support ended in 2005. It does happen, more often than you think.

You decide to migrate it into a virtual machine and your chosen hyper visor is Microsoft's Hyper-V 2012 R2. This is an excellent platform, not quite as adaptable as VMware, but I really like it.

However your Windows 2000 VM just wont start or install, and its unsupported. Where do you go from here?

TSQL - Timing SQL Queries

Sometimes the best way to narrow down performance issues is just to time the various statement blocks in  TSQL. This little piece of code will do just that for you.

SET STATISTICS TIME OFF--This method allows you to pick when the stopwatch starts and ends which can be more meaningful
--Start the stop watchDECLARE @Time1 DATETIME, @Time2 DATETIME, @i INT
SELECT @Time1 = GETDATE(), @i=0--Use this to stop the stopwatchSELECT @Time2 = GETDATE(), @i=@i+1 PRINT 'TIME IS ' + CONVERT(CHAR, @Time2, 14) + ', i = ' + CONVERT(CHAR, @i) + ', TIMEDIFF = ' + CONVERT (CHAR, DATEDIFF(ms, @Time1, @Time2))--Reset the stop watchSET @Time1 = @Time2