SQL Server 2012 is around the corner, perhaps you are ready to upgrade and perhaps you are not. Maybe you just have upgraded to SQL Server 2008 R2 without software assurance and thus you don’t qualify for SQL Server 2012 upgrades.. Even though most people are on SQL Server 2005 or higher these days, I see plenty of database code that is being written today in SQL Server 2000 syntax; I guess old habits are hard to break indeed! In the next 25 days I want to take a stab to identify 25 of these habits and see if we can bring them to this century. Think of it as the “SQL Server upgrade your code” advent calendar.

Another reason I want to do is because some of the 2000 syntax or pre 2000 syntax won’t work anymore in SQL Server 2012 Are you still using old style left joins with *= and =*? If so then you will be greeted with the following message: Incorrect syntax near '*'.

Most people who upgrade do something like this: backup the database, restore on the new server, sometimes set the compatibility level to whatever is the current one and they are done. This will work of course but you can do much better, why not changing some of the code to take advantage of new features, maybe all you want is the date instead of datetime, just making this change will save 5 bytes per row.

Another advantage of using new functionality is that it is better for your career. I interview plenty of people who have SQL Server 2005 and 2008 on their resume, I have yet to find one person who can write the ROW_NUMBER() syntax on the whiteboard for me. The snapshot isolation level is another mystery to the people I have interviewed. The blessing of an IT worker is that you will never be bored, there is always something to learn. The curse is the same as the blessing, if you don’t keep up then you become obsolete. Your job really doesn’t end when you get home, you have to spend an additional 10 -20 hours a week upgrading your skills, this can be done by listening to podcasts, messing around with the latest CTPs, answering question in forums , reading blogs and maintaining your own technical blog

Hopefully I didn’t ramble on too much about keeping your skills up to date but it is very important!!

December 1st I will have part 1 out of 25 posted, keep coming back every day and we will do a recap after day 25.

Day 1: Date and time
Day 2: System tables and catalog views
Day 3: Partitioning
Day 4: Schemas
Day 5: Common Table Expressions
Day 6: Windowing functions
Day 7: Crosstab with PIVOT
Day 8: UNPIVOT
Day 9: Dynamic TOP
Day 10: Upsert by using the Merge statement
Day 11: DML statements with the OUTPUT clause
Day 12: Table Value Constructor
Day 13: DDL Triggers
Day 14: EXCEPT and INTERSECT SET Operations
Day 15: Joins
Day 16: CROSS APPLY and OUTER APPLY
Day 17: varchar(max)
Day 18: Table-valued Parameters
Day 19: Filtered Indexes
Day 20: Indexes with Included Columns
Day 21: TRY CATCH
Day 22: Dynamic Management Views
Day 23: OBJECT_DEFINITION
Day 24: Index REBUILD and REORGANIZE