SQL Server Management Studio (SSMS) in Denali (CTP1) has got a makeover and is now based on Visual Studio 2010 Shell. This will enable us to utilize lot of great features available in Visual Studio like Improved Intellisense, Dragging Query/Code Window to a different monitor etc.

Another interesting feature introduced in SSMS in Denali CTP1 is the ability to zoom the query/design surface which will enable you to have a bigger size font for your queries and making your experience close to having a bigger size desktop/monitor.

Below is a normal query window with Normal or 100% Zooming.

As you can see there is an option at the Left Bottom Corner to Zoom the Query/Design Surface. Now if we change the Zoom Level to 150% then the Query Window looks something like the one shown below.

As you can see now when we changed the Zoom Level to 150%, we are getting bigger font for our query there by giving us an experience similar to having a bigger size desktop/monitor.

Also, note that the Zoom Level is applied only to the Query/Design Surface and is not applied to any other areas like Results Pane, Object Explorer etc.

There are many more such cool features introduced in Denali CTP1. So stay tuned to learn more new things about Denali CTP1.

Here is a really cool feature which not many of you would be aware of. All of us write simple SELECT * FROM queries in SQL Server Management Studio (SSMS). And many times, we copy the data to excel spreadsheets for additional analysis / sorting etc. Here is a simple trick to copy select columns from the results window once the results are rendered.

Go to SQL Server 2005 / 2008 Management Studio (SSMS) and open a new query window. Type a SELECT * FROM TableName query on one of the tables and run the query as shown below.

Now let us say that you want to select only the following columns in the same sequence as specified below:

• GeographyKey
• EnglishCountryRegionName
• StateProvinceName
• City

If you observe the results displayed in the previous screenshot, you can see that the columns are not in the same sequence as listed above and the query does not contain the column names, so you cant change the change the sequence of columns in the query. If you have to change the sequence of columns using the query, then you need to type in the column names. But here is a simple way to do it and no need to type the columns.

Now click on the column EnglishCountryRegionName, hold it and drag it next to GeographyKey column till you see a red color line next to the GeographyKey column header as shown below.

Now, similarly move the StateProvinceName column after EnglishCountryRegionName and before City columns as shown below.

Once all the required columns are re-arranged, you can easily copy all the required columns and copy them to excel easily as shown above.

Hope you like this trick. If there is a better and quicker way of achieving the same thing in SSMS, then let me know by leaving a comment below.

Related Articles:

• Shortcuts for SQL Server Client Applications
• Commonly used Keyboard Shortcuts in SQL Server Management Studio
• Colored Connections in SQL Server 2008
• T-SQL Coding Made Easy with Inbuilt SQL Server Templates

I have seen many people, even though they know what function / clause / keyword to be used for a particular T-SQL / Other type of query / operation, they don't remember the exact syntax [Including me at times :-)]. From my perspective as long as we know what keywords / functions / clauses need to be used for performing a specific operation using T-SQL / Other language (like MDX, DMX etc), we can always get the syntax from various sources like msdn, SQL Server Books Online (Local / Online version) etc. and get the task done.

However, if you have an option of getting the well formatted code snippet with the syntax of all the commonly performed T-SQL / MDX / DMX etc. operations right in the query editor window, then nothing like it. I would love such a feature.

Microsoft as always known for offering most powerful and user friendly tools has provided a separate section / explorer in SQL Server Management Studio 2005 / 2008 which contains the templates / code snippets for all the commonly performed operations in T-SQL, MDX, DMX etc. This feature / explorer is called as Template Explorer. Few of you reading this post might already be knowing about this feature, but explaining it in detail for those readers who have not used this feature before.

Following is a snapshot of Template Explorer in SQL Server 2008 Management Studio showing the list of various well formatted code snippets grouped into various categories. Below snapshot shows all the available categories of code snippets for T-SQL operations in SQL Server 2008 with the list of code snippets expanded for commonly performed T-SQL operations on tables.

Template Explorer in SQL Server Management Studio (SSMS) can be accessed using the following ways:

• SSMS Menus - View -> Template Explorer
• SSMS Shortcut - Ctrl + Alt + T

Following screenshots show the comparison of Template Explorer between SQL Server 2005 & SQL Server 2008 for T-SQL / SQL Server, MDX, Mobile / Compact Editions.

Now let us take a look at how to generate the table creation script for a sample table using the Create Table template available in Template Explorer.

Go to template explorer and expand the Tables folder under SQL Server section in Template Explorer as shown in the above figure 1. Now double click on the Create Table template to open it in a new query editor window.

Following code snippet is present in the Create Table template:

-- ========================================= -- Create table template -- ========================================= USE <database, sysname, AdventureWorks> GO IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table> GO CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table> ( <columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>, <column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>, <column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>, CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>) ) GO

Now go to Query -> Specify Values from Template Parameters... in SSMS. This brings up a pop-up window "Specify Values from Template Parameters". In this window specify the parameters like Column Names, Column Data Type, NULLability, Constraints etc. as shown in the below screenshot.

Now when you click OK, the Table Creation Script for the SampleEmployeeTable gets generated as shown below.

-- ========================================= -- Create table template -- ========================================= USE MyTechnoBook GO IF OBJECT_ID('dbo.SampleEmployeeTable', 'U') IS NOT NULL DROP TABLE dbo.SampleEmployeeTable GO CREATE TABLE dbo.SampleEmployeeTable ( EmployeeId INT NOT NULL, EmployeeName NVARCHAR(255) NULL, EmployeeAddress NVARCHAR(512) NULL, CONSTRAINT PK_SampleEmployeeTable_EmployeeId PRIMARY KEY (EmployeeId) ) GO

Using Template Explorer we can perform the following tasks:

• Edit the existing templates to include additional details like parameters, fields, comments, formatting etc.
• Create new templates for commonly used scripts in a module / project so that they can be reused by other team members and also across various modules / projects.
• Rename existing folders / templates as per your convenience (Though this is not required 99.9% of the times as all the folders which ship with SQL Server are named appropriately).
• Create new folders / Renaming existing folders to add new or existing templates.
• Re-organizing the templates by moving them across folders.

Following are few of the advantages / benefits of using Template Explorer:

• These templates act as a quick reference for getting the syntax in the absence of locally installed SQL Server Books Online or Access to Internet.
• Saves time required for formatting the T-SQL code since the templates are pre-formatted hence ensures consistency in terms of formatting across various modules / projects.
• We can share commonly used scripts across all the team members working across modules / projects.

Please let me know your opinion about this article / feature by leaving a comment below.

Here are couple of related links and articles that I have previously published.

• T-SQL - Coding Standards & Best Practices
• Template Packages in SSIS

Have you ever heard of a colored connection in SQL Server? Well, may be few but not everyone reading this blog post would have come across colored connections feature of SQL Server 2008. Lets take a closer look at colored connections feature of SQL Server 2008 Management Studio in this article.

Well, as we all know while laying electric cables / wiring the entire house / campus, various colored cables / wires are used. The main purpose of this is to be able to differentiate the cables using colors both at the time of initial setup as well as during repair / maintenance. Colored Connections in SQL Server also work pretty much on the similar lines.

In SQL Server 2008 Management Studio (SSMS), while connecting to a server / database in the "Connect to Server" window where we enter the server details and the credentials, there is an "Option" button at the Right-Bottom corner as shown in the below window.

In the above window click on "Options>>" button & go to "Connection Properties" tab and check the "Use Custom Color" check box. Click on "Select..." button to select any color of your choice from the list of available colors or you can define your own custom color as shown in the below screenshot.

In the above screenshot I have selected the color green since its a development server and one can do anything & everything as long as the database does not conflict with other developers.

With the use of this feature one can give distinct color to each of the servers / databases that are frequently used like Dev, Test, UAT / Pre-Prod, Prod etc as shown in the below screenshot.

This color coding / coloring a connection in SQL Server Management Studio reminds the developers / testers etc about the environment in which they are working, so that they can pay necessary attention before firing any query.

Lastly, there is a feature in SQL Server Management Studio (SSMS) which is called "Registered Servers" / "Server Registration" which helps us in registering most commonly used servers / databases. I would prefer to keep all my frequently used servers / databases registered and grouped into separate Server Groups with a different color for each type of connection as shown in the below screenshot.

Well, that is all about Colored Connections, which is a very nice feature introduced in SQL Server 2008 Management Studio and also a little bit on the best practices of Registering / Grouping frequently used Servers / Databases in SQL Server.

Let me know if you liked this post by leaving a comment below.

Q. How to Start SQL Server Management Studio (SSMS) from Run Prompt?

A. Go to Start -> Run. Type "sqlwb" and click "Ok". This will launch SQL Server Management Studio.

More Shortcuts on SQL Server Client Applications Here.

Q. How to comment multiple lines of SQL statements in SQL Server Management Studio?

A. Select the line(s) of code to be commented and then press "CTRL+K" & "CTRL+C" one after the other. This will comment the selected line(s) of SQL Statements.

Q. How uncomment multiple lines of SQL statements in SQL Server Management Studio?

A. Select the line(s) of code to be commented and then press "CTRL+K" & "CTRL+U" one after the other. This will uncomment the selected line(s) of SQL Statements.

Q. What is the Shortcut for parsing SQL Queries in SQL Server Management Studio?

A. Parsing an SQL Server will check for any syntactical errors in the selected set of SQL Statements. To parse SQL Statement(s) in SQL Server Management Studio select the SQL Statement(s) and then press "CTRL + F5"

Q. What is the Shortcut for running/executing SQL queries in SQL Server Management Studio?

A. Select the SQL Statement(s) to be executed and then press any of the following to execute the selected SQL Statement(s):

1. F5

2. CTRL + E

Q. How to navigate between query pane and results pane in SQL Server Management Studio?

A. The following are the shortcuts for navigating between the query pane and the results pane in SQL Server Management Studio:

To move from Query pane to the results pane press "F6"

To move from results pane to the Query pane press "SHIFT + F6"

Note:- The above listed shortcuts are applicable for SQL Server 2005 Management Studio. Some of these shortcuts might not work with SQL Server 2008 Management Studio.