In this blog I’ll walk you through three new enhancements in SQL Server 2012

1. Changing Startup Parameters

Changing\adding startup parameter was always considered to be a complex task but in SQL Server Denali Microsoft gave a new Tab to make it easy. Now you can configure Database Engine startup options using a new Startup Parameters tab of SQL Server Configuration Manager. For all those who are always confused how to separate two startup parameters and where to place that semi-colon (;) no need to worry, all you need to remember is which parameter to use and the rest of the stuff will be taken care by this new tab.

1_Enhancements_in_SQL_Server_2012_aka_Denali_Part 1

To configure startup options
1. In SQL Server Configuration Manager, click SQL Server Services.
2. In the right pane, right-click SQL Server Service, and then click Properties.
3. On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter, and then click Add.
For example, to start in single-user mode, type -m in the box and then click Add.
4. Click OK.
5. Restart the Database Engine.

1. When you restart SQL Server in single-user mode, stop the SQL Server Agent. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user.
2. After the troubleshooting don’t forget to remove those extra Parameters.

For Clustered Instances:
On a cluster, changes must be made on the active server when SQL Server is online, and will take effect when the Database Engine is restarted. The registry update of the startup options on the other node will occur upon the next failover.

Loved the feature but want to use same in SQL 2008 or lower version?
Don’t worry, Install SQL Server Denali and use its configuration Manager to configure rest of your SQL instances with the same feature.

2. Insert Snippet

This one is very similar to Templates, in previous versions you were having templates of SP, view, trigger etc which you can easily import and replace the variables to make use of templates in your development.
In SQL Server Denali Insert Snippet does the same thing, all you need to do is right click on query window and you’ll get this as a option from the context menu. You can easily select from variety of available options, here take a look at one of the examples.

Just right click & Select Insert Snippet (should be the 4th option)

2_Enhancements_in_SQL_Server_2012_aka_Denali_Part 1

After choosing Insert Snippet as an option, you’ll get a new menu with different categories to choose from like: Functions, views, stored procedures, triggers etc. Choose the one you want.

3_Enhancements_in_SQL_Server_2012_aka_Denali_Part 1

I opted for Stored Procedure, which then shows three more options, these are basically different type of stored procedures you can create.

4_Enhancements_in_SQL_Server_2012_aka_Denali_Part 1

Then I decided to create a Cursor OUTPUT Parameter Procedure. As you can see in the image below I got the complete template for SP that too customizable, now I can easily change the variables to meet my requirements, needless to say you’ve to add your own business logic but this really helps to provide the skeleton of the object.
If you notice on the second part of the image below, if you place your mouse\cursor on the text highlighted yellow in color you’ll also get to know more about the variables.

5_Enhancements_in_SQL_Server_2012_aka_Denali_Part 1

3. Surround With
Third new option you’ll notice is “Surround with”. There is not much to talk about this feature but this may help you a bit. Let’s say you’ve some piece of code and you want to put that in If clause or want to repeat a step in While loop or just want to place a Begin End block, then you can achieve this with Surround With option. This option is again in the context menu, just Select the script you want to surround & right click over it and you’ll get “Surround with” as the 5th option.

6_Enhancements_in_SQL_Server_2012_aka_Denali_Part 1

After selecting “Surround with” this will again give you three options to choose: Begin, if and While.
Begin representing Being End Block.
If representing If conditional Block
And While representing While Loop.
7_Enhancements_in_SQL_Server_2012_aka_Denali_Part 1

8_Enhancements_in_SQL_Server_2012_aka_Denali_Part 1

Hope you learned something new out of this blog post.



Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook