Earlier this month, I presented a webcast on the “Underappreciated features of Microsoft SQL Server” during the “Exclusive Webcast series on Microsoft SQL Server”. As one of the developer productivity features provided with the SSMS, I presented the Template Explorer.

The session received a very good reception, and I thank-you – the community for the same. Last week, I also discussed a question that was asked to me during the session – “How can I deploy a template to all of my developer workstations?”. While writing last week’s post, I noticed that some things had changed in SQL 11 (Code Named: “Denali”) CTP01 when compared to SQL 2008 as far as the available templates are concerned.

Location of the Templates

Templates are deployed on the user workstation (i.e. the machine where SSMS is running) when the user uses the Template Explorer for the first time. These templates are located at the following locations:

In Microsoft SQL Server 2008: %APPDATA%MicrosoftMicrosoft SQL Server100ToolsShellTemplates

In SQL 11 (“Denali”) CTP01: %APPDATA%MicrosoftSQL Server Management Studio11.0Templates

No Shortcut key

The immediate difference is that SQL 11 CTP01 does not have a shortcut key for the Template Explorer. Ctrl+Alt+T no longer works with Denali.

SSMS – SQL Server 2008	SSMS – SQL 11 (“Denali”)

New & Updated Templates!

SQL 11 (Code Named: “Denali”) CTP01 comes with new templates! SQL 2008 SSMS came with 367 templates, whereas SQL 11 CTP01 SSMS comes with 399 templates as of now.

Here is a detailed listing of the changes. Please note that all of these may not be available in the RTM release, but it is safe to assume that this is a fair indication of the changes. I have not considered differences of default paths and/or white spaces.

1. No Templates for “SqlCe” in SQL 11 (“Denali”) CTP01
2. ..SqlRole has been renamed to ..SqlDatabase Role
3. New set of templates for ..SqlServer Role have been introduced
4. The template to create a new Service Broker queue – Create Queue with Activation.sql – now has the POISON MESSAGE HANDLING turned ON by default
5. Brand new set of 38 templates added for SQL Azure database!
6. DROP STATISTICS template now checks for pre-existing statistics (IF EXISTS check)
7. Create Users scripts (Create Data Reader User.sql, Create User as DBO.sql) no longer have a reference to sp_addrolemember. sp_addrolemember has been replaced with ALTER ROLE/ADD MEMBER
8. Create User as DBO.sql – template now uses square brackets ([]) to enclose identifiers
9. There are no changes to the templates for Analysis Services and for the templates for “Connections”

I hope that you are as excited and looking forward to newer templates as I am. Do let me know if this helped you in implementing template explorer in your organization.

Until we meet next time,

Be courteous. Drive responsibly.