SQL Server 2012 is best known for its bombastic new features: AlwaysOn, Columnstore indexes, Window functions enhancements, Extended Events enhancements, and more.
But there are other little and less known tweaks that might seem less revolutionary, but can make our lives much easier. Below are a few of them:
1. Adding a column with a default value is now a metadata operation
Until SQL Server 2012, this task could take some time for big tables, because SQL Server had to update all of the rows in the table. From now on, adding a column with a default value makes SQL Server “remember” this default value at the metadata level, eliminating the need to write the value in each row. The value will be written the next time the record is updated.How many times have you written a script that adds a non-nullable column to a big table?
Unfortunately, this feature works only in Enterprise Edition.
2. Code Snippets in Management Studio
The new Management Studio supports code snippets. You can create your own snippets and save a-lot of repetitive work.
I have written about it thoroughly here.
3. SSIS Management And Execution Using T-SQL
SSIS 2012 introduces a new T-SQL API. Using this API, you can manage, deploy, execute SSIS packages and more. For instance, you can pass the parameters from a stored procedure. This opens up a-lot of possibilities for easy management and smooth integration between SSIS packages and other database code. For more details, check out this post by Mike Davis, and this one, by Matt Masson.
4. Indexes with LOB Can Now Be Built Online
SQL Server 2005 introduced the option for online index operations. This was a giant advance from the situation in SQL Server 2000, but in case of we had an index with LOB columns, an online operation could not be performed and we had to (re)build it the old fashioned way. From now on, online index operations support indexes with LOB columns, making our lives easier.
Unfortunately, online index operations are still an Enterprise Edition only feature.