SQL Advent 2011 has come to an end. I had lots of fun writing these but I wished I started earlier instead of 2 days before December 1, on some days I was really racing against the clock and I feel like I made some posts much shorter than I had in mind initially. But it is what it is, I hope you enjoyed them, maybe I will do this again next year.
Here is a recap of all the 24 SQL Advent 2011 posts.
Day 1: Date and time
In this post I covered the new date, datetime2 and time datatypes
Day 2: System tables and catalog views
In this post we took a look what the replacements are for the all system tables and also gave you a table with the new catalog view/compatibility view equivalent of the old system table
Day 3: Partitioning
In this post I looked at partitioning in pre sql 2005 days by showing you how to create partitioned views. I also showed you how to user partitioned function in sql 2005 and up
Day 4: Schemas
In this post I show you what schemas are and how they can help with security and logical grouping of objects
Day 5: Common Table Expressions
The Common Table Expressions post showed you what Common Table Expressions are and how they can be used to simplify your code
Day 6: Windowing functions
The Windowing functions post showed you how to do different kinds of rankings
Day 7: Crosstab with PIVOT
This post was all about pivoting/transposing/crosstabbing data with the PIVOT operator, also was shown how to do it dynamically
Day 8: UNPIVOT
This post showed you how to use UNPIVOT to get the reversed effect of PIVOT
Day 9: Dynamic TOP
The dynamic TOP post showed you how to do dynamic TOP without dynamic SQL or SET ROWCOUNT
Day 10: Upsert by using the Merge statement
This post was all about how to use MERGE to do an UPSERT (Update if it exists otherwise insert)
Day 11: DML statements with the OUTPUT clause
This post showed the usefulness of the OUTPUT clause
Day 12: Table Value Constructor
This post showed you how to use Table Value Constructor
Day 13: DDL Triggers
The DDL trigger post showed you how to use DDL triggers and also explained why you might want to use them
Day 14: EXCEPT and INTERSECT SET Operations
This post was all about the two new SET Operations EXCEPT and INTERSECT
Day 15: Joins
This post showed you how to use the newer ANSI SQL JOIN syntax and also showed you what was deprecated
Day 16: CROSS APPLY and OUTER APPLY
Shown was how to use APPLY with derived tables as well as functions
Day 17: varchar(max)
In this post I showed you why varchar(max) is much better than the text data type
Day 18: Table-valued Parameters
I showed you how to use Table-valued Parameters to pass around tables
Day 19: Filtered Indexes
In this post I showed you how to create a filtered index and why it can be beneficial in your database
Day 20: Indexes with Included Columns
On this day I showed you how to cover you query by using Indexes with Included Columns
Day 21: TRY CATCH
Error handling go better in SQL Server 2005 and I show you how to use TRY CATCH
Day 22: Dynamic Management Views
In this post I show how you can use Dynamic Management Views to get all kinds of information about your server and databases
Day 23: OBJECT_DEFINITION
The OBJECT_DEFINITION covers ways to get the text of an object and also show you why it is better than sp_helptext or syscomments
Day 24: Index REBUILD and REORGANIZE
This post is all about rebuilding and reorganizing(defragmenting) indexes
There are tons of other things that I did not cover, here is just a small list of them
SQLCLR
ServiceBroker
FILESTREAM
Geometry, Geography and HierarchyID data types
Transparent Data Encryption
DB Mirroring
Policy Management
Resource Governor
Tomorrow we will finally look at some new and shiny T-SQL enhancements in SQL Server 2012, I will provide a list of all the new SQL Server 2012 post I have written in the last 6 months or so
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
