An Introduction to Sql 11 (Code Name Denali) –Part I (SSMS Features in CTP 1)

An Introduction to Sql 11 (Code Name Denali) –Part I (SSMS Features in CTP 1)

Table of Content

1. Introduction
2. Background
3. Installation
4. Launch Screen
5. SSMS Enhancements
1. Object Undocking /Multi Monitor Support
2. Cycle Clipboard Ring
3. Task List
4. Zoom/Magnify
5. Inclusion of Sequence Nodes
6. Surrounded With
7. Code Snippets
6. References
7. Conclusion

Introduction

One of the hottest and awesome developments by Microsoft in the technology field was come into picture on 8th November, 2010 when they released the Community Technology Preview 1 (CTP 1) version of Sql Server 2011(Code name Denali). The CTP1 is available both in 32-bit and 64-bit versions

As expected, Denali has brought some new features for Sql lovers may that be developers, Administrators or Business Intelligence (BI) professionals. In this series we will explore on the enhancements and new features of SSMS. About the rest of the features will look into the subsequent series.

Background

In the last few years, Microsoft has brought many technologies under the developers’ hood. A drastic change has been made in the Sql Server jargon with the advent of Sql Server 2005(code name Yukon) and in the subsequent releases like Sql Server 2008(code name Katmai) and Sql Server 2011(code name Denali), the same pace has been kept with introduction to new features and enhancements as well as improvements.

In this article we will explore more on the new features that Denali has already offer us from a SSMS perspective. The subsequent articles will focus on the enhancements made in the TSql, Administrators and BI areas.

Installation

As this article will focus on the Denali’s SSMS feature, henceforth, this is beyond the scope of this article to give a complete description of the installation. However, kindly refer to the article for the same. It gives a detailed approach of how to install Denali, the hardware and software requirements.

Launch Screen

The launch screen looks as under

After logging into the server, we encounter the user interface screen

Make sure that the database compatibility level is set to Sql Server “Denali”(110)

SSMS Enhancements

SSMS is now akin with Visual Studio.Below are some of the main features that we will delve into so far the SSMS enhancements are concern.

I.Object Undocking /Multi Monitor Support

The screen objects can be undocked e.g. the below screen shot will show the Object Explorer after undocking

II.Cycle Clipboard Ring

Another interesting feature of Denali is Cycle Clipboard Ring

We can traverse to the clipboard using Ctrl+Shift+V. This feature is already in Visual Studio and now has been incorporated in Denali too.

III.Task List

A small feature where we can add task and assign priorities to them. It can be obtained either from View->Task list or by pressing Ctrl+\,T. The Task List window will appear as under

We can set the Task’s priority as under

Once the tasks are completed, we can turn on the check box which indicates that the task is completed.

IV.Zoom/Magnify

Again a small addition, it helps to zoom the text’s based on the available enumerations. The default being 100%

V.Inclusion of Sequence Nodes

The concept of Sequence is very old in Oracle. However, Sql Server 2011 has included this feature under the hood.

So what is a sequence?

It generates sequence of numbers similar to an identity column in Sql tables. But the advantage is that it is independent of table(s).

How to create Sequence Object

Sequence can be created through two ways

a)Without T-Sql Script

b)Using T-Sql script

a)Sequence creation without T-Sql Script

In object explorer, we have the new Sequences node under Programmability

Right Click on the Sequence node and click on the New Sequence… option

And we will get the New Sequence Screen

Like every date base objects, Sequence also has a name(MySequence here) and assigned to a database schema(dbo).

If we expand the Data Type drop down, we will get the information about the supported datatypes by sequence

The Start Value is the first value returned by the sequence object

Increment By can accept both positive and negative values. It is use to increment (in case of positive value) or decrement (in case of negative values) the value of the sequence object.

The Minimum and Maximum Values are boundaries for the sequence.

We can reuse sequence numbers if the Cycle option is turn on. Else it will report error in case the sequence value reaches the Maximum limit.

b)Sequence creation with T-Sql Script

We can create sequence using T-Sql script as well.

The general syntax for creating a sequence is as under

CREATE SEQUENCE [schema_name . ] sequence_name
  [ AS { built_in_integer_type | user-defined_integer_type } ]
  | START WITH <constant>
  | INCREMENT BY <constant>
  | { MINVALUE <constant> | NO MINVALUE }
  | { MAXVALUE <constant> | NO MAXVALUE }
  | { CYCLE | NO CYCLE }
  | { CACHE [<constant> ] | NO CACHE }
</constant></constant></constant></constant></constant>

So let us first create a sequence as

CREATE SEQUENCE GenerateNumberSequence
START WITH 1
INCREMENT BY 1;

After execution of this statement, in the Sequences node we will find that out sequence object has been created

Once the sequence object is in place, next we can create the table and populate it with the values as under

Create Table tblWithSequence1
(
EmpId int not null primary key
,EmpName varchar(50) not null
);

Insert into tblWithSequence1(EmpId, EmpName)
VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Niladri'),
(NEXT VALUE FOR GenerateNumberSequence, 'Deepak')

SELECT * FROM tblWithSequence1;

How Denali’s Sequence is different from Identity of Previous version

Consider the below program written in Sql Server 2008. Simply creating a table with two columns, one being the identity.

Create Table tblWithOutSequence1
(
	EmpId int identity not null primary key
	,EmpName varchar(50) not null
)

Insert into tblWithOutSequence1
Select 'Niladri' Union All
Select 'Deepak'

Select * from tblWithOutSequence1

Likewise create another table with the same schema as under

Create Table tblWithOutSequence2
(
	EmpId int identity not null primary key
	,EmpName varchar(50) not null
)

Insert into tblWithOutSequence2
Select 'Niladri' Union All
Select 'Deepak'

Select * from tblWithOutSequence2

Drawback of Identity Approach

As can be figure out that we are forced to write the identity column in both the tables at the time of creation i.e. we cannot reuse the EmpId column of one table in another.

Nevertheless,Sequence helps us to do so.

Denali’s Sequence into action

We have already populate a table (tblWithSequence1) with the sequence GenerateNumberSequence.

Likewise if we create another table say tblWithSequence2, there we can easily use the GenerateNumberSequence as shown under

Create Table tblWithSequence2
(
EmpId int not null primary key
,EmpName varchar(50) not null
);

Insert into tblWithSequence2(EmpId, EmpName)
VALUES (NEXT VALUE FOR GenerateNumberSequence, 'Niladri'),
(NEXT VALUE FOR GenerateNumberSequence, 'Deepak')

SELECT * FROM tblWithSequence2;

N.B.~The sequence object can be used with a table variable or Temporary table.

If we look into the above programs with sequence, we can find that for inserting every row, we are using the Next Value For statement fetching a single number from the sequence. However, if we need to get a range of sequence numbers, we can use sp_sequence_get_range

Drop a sequence

As sequence is a database object, so it can be dropped.If we need to drop a sequence, we can execute

Drop Sequence GenerateNumberSequence

Note: In this part I have given very basic introduction to Sequence.It will be detailed in the next part.

VI.Surrounded With

This feature helps T-Sql programmers to enclose a block of Sql statement with IF statement, WHILE loop block or with Begin... . End blocks.

Consider we have something as under

Declare @i int
set @i = 0

print @i
set @i += 1

Now if we want to turn the above program into a while loop, instead of writing the same by hand, we can take advantage of the Surrounded With .

First Step: Select the portion that will come inside the While Block.

Second Step:, Right click and then from the popup, choose Surrounded with or press Ctrlk,Ctrl S

Third Step: Double click on the While snippet and the below code will be generated

We just need to specify the condition and run the program. So cool!!!!

VII.Code Snippets

The last but not the least feature, which we will cover here is the code snippet; another feature borrowed from Visual Studio.

Code snippets allow us to quickly insert code templates into the query window in order to perform some common TSql tasks.

The code snippet can be invoked either by Edit->IntelliSense ->Insert Snippet or by the keyboard short cut Ctrl k, Ctrl X.

If we need to insert an inline table, just double click on the Table snippet as under

And the snippet code will be generated

Creating Custom Snippet

We can however, create our own custom snippet apart from the built in code snippets.

Here we will create a custom snippet to create Sequence object

Below are the steps to be followed for creating our custom code snippet.

Step 1: Create the snippet file and save it

As an initial step, we need to create a .snippet file which is basically a XML file that has Pre-defined schema. So our snippet file looks as under

 <?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
      <Title>Sequence</Title>
      <Author>Niladri Biswas</Author>
      <Description>Code snippet for Sequence Creation</Description>
      <HelpUrl>http://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx</HelpUrl>
      <Shortcut></Shortcut>
    </Header>
	
    <Snippet>
      <Code Language="sql">
                       <![CDATA[
		CREATE SEQUENCE Sample_Sequence  		
 		START WITH 1
 		INCREMENT BY 1
 		MINVALUE -2147483648
 		MAXVALUE 2147483647 		 
		]]>
    </Code>
    </Snippet>
	
  </CodeSnippet>
</CodeSnippets>

Save it into some folder and name it as CustomSequence.snippet(Note that the file extension is .snippet) in a folder say CustomSequence. In my case the entire path is C:\CustomSequence\ CustomSequence.snippet

Step 2: Register the custom code snippet in Code Snippet Manager

From the SSMS, Tools->Code Snippets Manager or invoke the short cut key as Ctrl K, Ctrl B. The code snippet manager window will open.

Click on the Add… button and choose the folder (here CustomSequence) where we have generated the CustomSequence.snippet file. In this case it will be C:\ CustomSequence

As can be seen that, the folder has been added. Click OK.

Step 3: Invoking the code snippet

We can invoke the code snippet either by Tools->Code Snippets Manager or by the short cut key as Ctrl K, Ctrl B

Double click on the Sequence and the code snippet will be generated as under in the Query Editor

CREATE SEQUENCE Sample_Sequence 
 		
 START WITH 1
 INCREMENT BY 1
 MINVALUE -2147483648
 MAXVALUE 2147483647 

N.B.~ In the Code Snippet Manager, there is an Import Button which will basically import the snippet file instead of the folder.

Instead of writing the code snippet by hand in the .snippet xml based file, we can use the Snippet Designerfound in Code Plex.

It ia an elegant tool for generating code snippets.

References

1. SQL Server Denali – New Features
2. First look at SQL Server Management Studio
3. SQL Server v.Next (Denali) : The New SSMS

Conclusion

Denali has brought a lot to us. The features we have discussed about are only a few of the strength that has been offered to us in CTP1. In CTP2, they are introducing Juneau - for making a consistency in developing application for SQL Azure and SSMS.In the next series we will look into the new and enhanced features of TSql in Denali CTP1.So stay tune and share your opinion about the article.