Log in

Introduction to SQL Server 2005 - CodeSource [entries|archive|friends|userinfo]

[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

Introduction to SQL Server 2005 [Feb. 2nd, 2006|11:56 am]



1. Introduction
Each successive release of SQL Server has given developers new and enhanced tools for writing better Transact-SQL (T-SQL) code and SQL Server 2005 is certainly no exception. It brings many improvements for the developer, from incorporation of common language runtime (CLR) coding to some simple yet powerful enhancements to the T-SQL language.

The document covers the following contexts or features of SQL Server 2005.

. SQL Management Studio.
. New features added for TSQL.
. Integration of the .NET Common Language Runtime (CLR).

Let's discuss each of the above features in a greater detail.

2. SQL Management Studio
SQL Server Management studio is an integrated studio that comprises of tools for database management and application development. It has a visual studio-style interface, hence this will be new to the database administrator but comfortable for the developer. The traditional SQL Enterprise manager and query analyzer which was clumsy due to its modal windows and non - sizable dialog boxes are no longer into existence and are replaced by the SQL Server management studio.
The Visual-Studio-Style interface of SQL Server Management Studio should be a welcome change for developers. Almost all of the important and frequently used features of the Enterprise Manager are migrated into the visual studio environment. The console root tree structure of enterprise manager is replaced by two toolbox-style windows in SQL Server Management Studio; Registered Servers shows the servers that are currently registered for use and Object Explorer provides the drill-down action of the server contents.

Nearly all the features of query analyzer are migrated into the Query windows of the SQL Server management studio. The mode of result output has several options like text, grid or file. The query window properties has options to to set the input/output (I/O) statistics, showing the execution plan as text, discarding the results, limiting characters per column etc. As we know, all the above features are available in Query Analyzer.

The Query Windows of the SQL Server Management Studio has some new features that are not available in the query analyzer. Tab style windows (of the Visual studio interface) helps in displaying multiple result sets (or grids) in various tabs, which demands a mouse click to navigate between the different result panes. The concept of code regions is introduced, which helps to block, expand, collapse and bookmark SQL script.

The other important features are
. Integrated help.
. Built-in debugging capabilities.
. Source control.
. Task list.

Fig 2.1. SQL Server Management Studio database-exploration tools.

3. TSQL Enhancements
Microsoft has introduced a number of new features and enhancements in the T-SQL language in SQL Server 2005. We'll look into the enhancements in a greater detail.
3.1 Ranking functions
The concept of paging data was indeed a difficult task for any TSQL developer for many years together. Extracting a single page required complex queries, which in turn demanded efforts of many man-hours. Data paging is made very simple with the Row_Number function. The beauty of this function is the fulfillment of all the data-paging dreams.

3.1.1 Row_Number
In simple terms, it is a sequential row number for each row of data returned from a SELECT statement. The Row_Number function uses an OVER clause, which determines the basis for the numbering of the rows of data. The following example uses the row_number function.

Select row_number() over (order by SalesOrderDate desc) as RowNum,
OrderID, CustomerID, SalesOrderDate
From Sales.OrderHeader
Order by SalesOrderDate Desc

The table below demonstrates the results

RowNum OrderID CustomerID SalesOrderDate
1 75084 11078 2004-07-31
2 75085 11297 2004-07-31
3 75086 28789 2004-07-31
4 75087 11794 2004-07-31
5 75088 14680 2004-07-31
This code could be extended to implement data paging by adding a WHERE clause to limit the returned rows. The code would look like something like this
(SELECT Row_Number() OVER (ORDER BY OrderDate DESC)
as RowNum, OrderID, CustomerID,
FROM Sales.OrderHeader) O
WHERE RowNum BETWEEN 101 and 120 ORDER BY OrderDate DESC

3.1.2 Rank, Dense Rank and NTile
The Rank function gives a rank order similar to Row_Number (discussed in 3.1.1) except that ties receive the same number as the item prior. So, if five people were racing and the second and third people tied, their respective ranks would be 1, 2, 2, 4 and 5.

The Dense_Rank function works like Rank in that rows with equal values from the OVER clause return equal values for this function, but gaps in sequence are also removed in the results. Those same five people would have dense ranks of 1,2,2,3 and 4.

NTile breaks the rows into equal parts (or as close to equal as possible), with earlier rows being weighted more than later ones. This is useful to determine in what percentile rank something falls. For example, if you had 100 rows being returned and used NTile with a value of 5 as its parameter, NTile for rows 1 - 20 would be 1, rows 21 - 40, 2 and so on.

The following listing has example for the ranking functions.

SELECT Row_Number() OVER (ORDER BY City) as RowNum,
Rank() OVER (ORDER BY City) as Rank,
Dense_Rank() OVER (ORDER BY City) as DenseRank,
NTile(3) OVER (ORDER BY City) as NTile_3,
NTile(4) OVER (ORDER BY City) as NTile_4,
City, StateProvinceCode
FROM Person.Address as Address
INNER JOIN Person.StateProvince as StateProv
ON Address.StateProvinceID = State.StateProvinceID
WHERE CountryRegionCode = 'US'
AND StateProvinceCode = 'AZ'

RowNum Rank DenseRank NTile_3 NTile_4 City State
1 1 1 1 1 Chicago AZ
2 1 1 1 1 Chicago AZ
3 1 1 1 1 Chicago AZ
4 1 1 1 1 Chicago AZ
5 5 2 1 1 Denver AZ
6 6 3 1 2 Gilbert AZ
7 6 3 2 2 Gilbert AZ
8 8 4 2 2 Mesh AZ
9 9 5 2 2 Phoneix AZ
10 9 5 2 2 Phoneix AZ
11 9 5 2 3 Phoneix AZ
12 9 5 2 3 Phoneix AZ
13 9 5 3 3 Phoneix AZ
14 14 6 3 3 Scottsdale AZ
15 14 6 3 3 Scottsdale AZ
16 16 7 3 4 Surprise AZ
17 17 8 3 4 Tucson AZ
18 17 8 3 4 Tucson AZ

3.2 New Data Types
In SQL Server 2005, varchar, nvarchar and varbinary have been enhanced to support up to 2 gigabytes of data - varchar(max), nvarchar(max) and varbinary(max) respectively. Unlike their predecessors, text, ntext and image, the information does not have to be chunked in and out. As a matter of fact, these latter three will be deprecated in a future version of SQL Server.

3.3 Common Table Expression
Common table expression (CTE) is a new feature that allows to define a virtual view to be used in another data manipulation language (DML) statement (such as SELECT). CTE offers additional features (such as recursive query support) that, until now, were not only difficult to implement, but usually required very complex T-SQL coding to achieve.

The WITH clause is the definition of a CTE. Like a derived table, WITH contains a SELECT statement, allowing is to create a temporary view that can be used in other DML statement. And, like views and derived tables, it also has several restrictions; the following clauses cannot be used in a CTE.

. ORDER BY (unless TOP is also used)
. OPTION clause with query hints

The following listing shows examples for derived table and CTE.

-- Derived Table
SELECT * FROM Production.Product AS P
INNER JOIN (SELECT ProductModelID, Avg(listPrice) as AvgPrice
FROM Production.Product
ProductModelID) as C
ON P.ProductModelID = C.ProductModelID
AND P.ListPrice > C.AvgPrice
-- CTE Example
WITH C (ProductModelID, AvgPrice)
AS (SELECT ProductModelID, Avg(listPrice) as AvgPrice
FROM Production.Product GROUP BY ProductModelID)
FROM Production.Product AS P
ON P.ProductModelID = C.ProductModelID
AND P.ListPrice > C.AvgPrice

3.4 EXCEPT and INTERSECT Set Operators
EXCEPT and INTERSECT are new set operators that allow a user to find records that are common to two sets of data (table, view, and so on) or records in one set of data that are not in another set of data. These new set operators follow the same rules as the UNION set operator.

INTERSECT returns results in a fashion similar to a standard INNER JOIN that joins two sets of data on all columns in the SELECT clause. For example, suppose that you have two tables (Orders and ImportedOrders), both of which represent order information. Both tables have the following columns: OrderID, CustomerID, OrderDate and ShippedDate. If you want to find out which orders in ImportedOrders table are exact duplicates of orders already existing in the orders table, you can write a query as shown in listing 3.6.

SELECT CustomerID, OrderDate, SalesPersonID FROM Sales.SalesOrderHeader INTERSECT
SELECT CustomerID, OrderDate, SalesPersonID FROM ImportedOrders

Listing 3.6

To find orders in the ImportedOrders table that did not exist in the Orders SalesOrderHeader table, the following Query can be used.

SELECT CustomerID, OrderDate, SalesPersonID FROM Sales.SalesOrderHeader EXCEPT
SELECT CustomerID, OrderDate, SalesPersonID FROM ImportedOrders

3.5 Pivot Operator
The pivot operator helps in pivot data in tables without much use of complicated TSQL Scripts.

For example, if the data in a table below needs to be pivoted with the "year" column for the years 2002, 2003 and 2004. The query in shown below can be used. The query results are available following the code.

ProductID ProductName Year TotalDue
782 Mountain-200, 38 2004 42.34
779 Mountain-200, 38 2002 75.34
746 Mountain-200, 46 2002 54.34
746 Mountain-200, 42 2002 54.34
784 Mountain-200, 46 2002 45.34
780 Mountain-200, 42 2004 75.34
782 Mountain-200, 38 2003 33.34
783 Mountain-200, 42 2002 44.34
781 Mountain-200, 46 2003 34.34
779 Mountain-200, 38 2004 21.34
782 Mountain-200, 38 2002 77.34
784 Mountain-200, 46 2003 65.34

SELECT * FROM ProductOrderDetails
PIVOT (SUM (TotalDue) FOR Year IN ([2002], [2003], [2004]))

Addressing the FOR clause of the PIVOT, Year actually becomes three columns in the results: 2002, 2003 and 2004. SQL Server takes these three values and creates three columns that will hold the pivoted data values. Because these values are being transformed into columns, they need to adhere to the naming conventions of SQL Server. In this case, because columns cannot begin with a numeric character, you need to use bracketed identifiers to make the new column names valid. If the FOR clause column contained values that properly follow the naming conventions, you would not need the rackets.

The first column addressed in the PIVOT operator, extended, is the aggregate that you want to return. Your either must use one of the built-in aggregate functions, COUNT, MIN, MAX, SUM or AVG, or your can create you r own user-defined aggregate in a .NET language and use it.

ProductID ProductName 2002 2003 2004
779 Mountain-200, 38 422.23 456.62 356.23
780 Mountain-200, 42 522.23 556.62 456.23
781 Mountain-200, 46 622.23 656.62 556.23
782 Mountain-200, 38 722.23 756.62 656.23
783 Mountain-200, 42 822.23 856.62 756.23
784 Mountain-200, 46 622.23 656.62 556.23

3.6 Insert with Merge ("Upsert")
The mythical "Upsert" command allows inserting new data while updating existing data. The MERGE statement is used to implement "Upsert", as seen below.

USING MyTempTable
ON MyTempTable.MatchingField1 = MyTable.MatchingField2
UPDATE UpdateField1 = MyTempTable.UpdateField1
INSERT VALUES (MyTempTable.MatchingField1,


Merge specifies to which table you will "upsert" the data, and USING specifies from which table this data comes. The ON clause, which can contain one or more fields, determines which fields will be used for matching - if the field has the same value, the code in the WHEN MATCHED THEN section will be executed; otherwise, the code in the WHEN NOT MATCHED THEN section will be executed.

3.7 Top Clause
In SQL Server 2000, TOP could be used only in SELECT statements and had to use a constant value. This limitation of constant value is relaxed in SQL Server 2005; Variables can be assigned to the TOP statement.

3.8 Exception Handling
Exception handling was certainly lacking with T-SQL. Exception handling abilities have been improved and can help take some of the load of managing error s from the data access code and allow SQL Server to resolve certain issues before sending results or messages to the client.


The lack of exception handling has made some development difficult in previous versions of SQL Server. However, the exception handling introduced in SQL Server 2005 is not as robust as C# or VB.NET, but it certainly does give you better control in handling errors in T-SQL.

The introduction of the TRY.CATCH methodology allows you to catch certain non-critical exceptions (for example, constraint violations) and properly manage the data if exceptions occur. An example of TRY.CATCH statement is provided here.

CREATE PROCEDURE prTransferToChecking
@CustomerID int, @TransferAmount money
UPDATE Checking SET Balance = Balance + @TransferAmount
WHERE CustomerID = @CustomerID


4. Integration of .NET Common Language Runtime
First impressions of the CLR integration led to the belief that the .NET-compliant languages (C# and Visual Basic .NET) would be interchangeable with Transact-SQL (T-SQL), both possibly being used within the same routines. This is not how the CLR integration works.

It is your choice: Write the guts of your code in T-SQL or write those guts in a supported .NET compliant language. You will need to define the procedure, trigger and so on in T-SQL, which, in essence, registers the CLR code for use in SQL SERVER. So, you will always have some T-SQL to be able to use CLR-based code.

For example, if you want to write a stored procedure in C#, you first write the C# code and compile the assembly. Next, you register the assembly in SQL Server using the T-SQL CREATE ASSEMBLY statement to load the assembly code into your database. Finally, you use the T-SQL CREATE PROCEDURE statement to "connect" the stored procedure to the function inside you registered assembly. Hence T-SQL still plays an important part in the process.

There are also certain features that simply require T-SQL, for example, selecting data in code. The T-SQL SELECT statement has no equivalent in .NET; even a stored procedure written in C# still needs to call back to T-SQL to select the data from the database. It does this via ADO.NET and special features of it that take advantage of the fact that the C# code is running in the context of SQL Server. Again, the point is that you will not do away with T-SQL. Therefore, .NET is really an extension to T-SQL, not a replacement-similar in some respects to the way extended stored procedures were used in previous versions of SQL Server. Database objects are still solely defined in T-SQL; functionality of several data manipulation language statements, such as SELECT and INSERT, exist only in T-SQL. And as for the actual code of the procedure, function and so on; the choice is left to the developer: .NET or T-SQL. However a good design will have lots of T-SQL code complemented with CLR-based code.

The following are three simple guidelines to make the correct language choice.

. If the code primarily accesses or modifies data, it should be written in T-SQL. CLR code must "connect" back to SQL Server to get data. It can never match the data access speed of T-SQL.
. Of the code primarily does processor-intensive operations (mathematics, encryption, etc), the code base should be in .NET.
. If the capabilities of the .NET .Framework need to be leveraged then the code base needs to be in .NET.

Combinations of these conditions can, however, raise a flag of ambiguity. If the code needs to both access data and utilize .NET Framework features, should the portion of the code that needs to use framework be isolated and written separately in .NET? Is it wiser to write the entire procedure in .NET, or is to better to write a function in C# and call that function from a T-SQL procedure? If the procedure accesses very little data, the former is the best; if the procedure also needs to access lots of data, the latter seems to be the better choice.

Furthermore, if the procedure would benefit from using the CLR because of processor-intensive operations, but also performs large amounts of data access, it is even more difficult to determine in which language the code should be written. If you need to access a lot of data and leverage .NET performance capabilities, some code needs to be written in .NET language. Making this determination is a matter of testing and experience.

4.1 Registering Assemblies and Creating Database Objects:
In previous versions of SQL Server, to register an external procedure (extended stored procedure), you needed to create the procedure with a reference to the external dynamic link library (DLL) file. When this was done, SQL Server called out to the DLL file when it was used in T-SQL code, requiring that the DLL file be additionally maintained if you backed up a database, the DLL file was not part of that backup because it existed in an external file. Moving a database that used and extended stored procedure to a different server also required that the DLL file be moved and registered on the new server.

In a similar fashion, you still need to register .NET assemblies, but unlike their more primitive ancestor, the .NET assembly becomes part of the database, and it is backed up with the database.

4.2 User Defined Functions:
The wonderful thing about UDFs is that they enable the developer to create functions that normally would be difficult or impractical to implement in T-SQL for coding or performance reasons. For example, if a set of cryptography functions is needed for a database, using the types available in the .NET Framework System.Security.Cryptography namespace makes light of this work; whereas it would be quite an undertaking, If at all possible, to create equivalent code if confined only to the realm of T-SQL.

Next is a simple example. First, we see the C# code for the Encryption word function that takes a string as its sole argument and returns a hashed binary value.

Using System;
Using System.Data.Sql;
Using System.Data.SqlTypes;
Using System.Text;
Using System.Security.Cryptography;

Public struct MySecurity
Public static SqlBinary EncryptPassword(SQLstring Password)
UnicodeEncoding uniEncoding = new UniccodeEncoding();
Byte[] passbytes= uniEncoding.GetBytes((string)password);
SHA1 sha = new SHA1CryptoServiceProvider();
Byte[] passHash = sha.ComputeHash(passBytes);
Return (SqlBinary) passHash;

The listing below is the T-SQL code that registers the assembly and function. Following that is a table definition and a stored procedure that uses the functions to encrypt the password before placing it in the table.

CREATE ASSEMBLY SecurityFunctions
FROM 'C:\PROJECTS\SQL2005\SecurityFunctions.dll'
(@password nvarchar(32))
RETURNS varbinary(32)
EXTERNAL NAME SecurityFunctions:MySecurity::EncryptPassword
UserName Nvarchar(32) NOT NULL,
Password Varbinary(32) NOT NULL)
INSERT INTO Membership (Username, Password)
VALUES ('USERNAME',dbo.EncryptPassword('PASSWORD'))

There are several important items here. The first concerns the mapping of SQL Server's various data types to their managed equivalents. As seen in the preceding code the SQL SERVER nvarchar data type maps to the managed SqlType of SqlString, which then can be easily cast to and from to the more familiar managed type of string. The return value of the function also demonstrates that the SqlType of SqlBinary maps back to SQL Server's varbinary, and that sqlBinary can be cast to and from the familiar .NET byte array.

Next, this function demonstrates something that has been difficult to achieve for the typical developer. In previous version s of SQL Server, a developer would normally perform the encryption before handing the data to TSQL Server itself. Now the encryption algorithm can be embedded within the database itself. You could have used extended stored procedures in previous versions of SQL Server, but extended stored procedures are more difficult to implement and generally written in C or C++ requiring a programmer or consultant with that skill set. Instead, you can now leverage the .NET Framework to implement code that would have been next to impossible to achieve via T-SQL alone. The .NET Framework has myriad types to work with regular expressions, perform string manipulation and mathematical functions, and make calls to operating system, all of which are easier to code using .NET and execute faster than the equivalent (if possible) T-SQL code.

Finally, this example shows a nice balance between coding in >NET and coding in T-SQL. T-SQL is being used here to do what it does best, which is manipulate data, and, more specifically in this case, to insert data. C# is being used for its capability to access the cryptography types and leverage them for a quick solution from both a development and performance standpoint.

4.3 SQL Server Managed Provider
There are some new features introduced in ADO.NET and, more specifically, the In-Process SQL Server Managed Provider. In a nutshell, this new provider executes in a SQL Server process (hence the name) and is optimized for such work. This provider can be found in the System.Data.SqlServer namespace, which is similar to the System.Data.,SqlClient namespace which is already in use by the ADO>NET developers, but this is designed to work in the context of SQL Server itself.

4.3.1 SqlContext
When you use ADO.NET from an external application, you must first establish a connection to the database before being able to do any work within that database. When you execute ADO.NET code in a CLR-based procedure or function within SQL Server, the code is running within the context of an already established connection. The SqlContext type represents this connection and is the secret to this provider's capability to work so efficiently. SqlContext provides access to several object types: SqlCommand,SqlPipe, SqlResultSet, SQlTransaction and SqlConnection itself.

4.3.2 SQLResultSet
For scalar UDFs, the .NET code needs to return one of the SqlTypes such as SqlInt32 or SqlString, but table-values UDFs are expected to return a set of records. For this type of function the return type of the .NET method must be SqlResultSet. SqlResultSet is quite unique in its functionality, deriving its structure from the UDF definition in T-SQL and made accessible via the GetReturnResultSet method of SqlContext.

CREATE FUNCTION GetBasicOrderInfo (@SalesOrderId int)
RETURNS TABLE (SalesOrderID int, CustomerID Nchar(5), SalesPersonID Int,
OrderDate Datetime, DueDate DateTime)
EXTERNAL NAME AwFunctions: AdventureworksFunctions:: GetBasicOrderInfo

The UDF returns a table with five columns specified. This table definition is important, not just because it defines the return table, but because it provides the table metadata from the innards of the .NET code so that it can write the appropriate data to this table. Now, take a look at the method definition in Listing 4.3.

Public struct AdventureworksFunctions
[SqlFunction(DataAccess = DataAccessKind.Read)]
Public static SqlResultSet GetBasicOrderInfo(SqlInt32 SalesOrderID)
String s = "SELECT SalesOrderId, CustomerID, SalesPersonID, OrderDate, DueDate
FROM Sales.SalesOrderHeader where SalesOrderId = @SalesOrderID";
SqlCommand cmd =
Cmd.CommandText = s;
SqlDataReader dr = cmd.ExecuteReader();
SqlResultSet results = SqlContext.GetReturnResultSet();
While (dr.Read())
Return Results;
The call to SqlContext.GetReturnResultSet returns a SqlResultSet with the table definition described previously in the T-SQL. The SqlDataReader is not providing the structure for this, but instead it is written to match the structure defined in the CREATE FUNCTION code (and thus the structure of the SqlResultSet). And because all three have matching structures, the code can iterate through the SqlDataReader and insert the records into the SqlResultSet. When this is done the SqlResultSet is returned, which in turn populates the return table in the T-SQL UDF.

4.3.3 SqlPipe:
A SqlPipe can send data directly to the calling application. To Send data back down the pipe you can pass this send method of SqlPipe a SqlDataRecord or SqlDataReader, in which the former sends a single record and the latter sends a set of records. In addition, you can also send a string that represents a message or a SQLError type to throw an exception.

Public struct AdventureWorksFunctions
String S = "Select * from Sales.SalesOrderHeader where CustomerID = @CustID";
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = s;
SqlDataReader dr = cmd.ExecuteReader();
SqlPipe pipe =

Like the previous example, this method uses the CustomerId as its sole parameter. However, in this case, instead of getting a single record with a single column, this method returns multiple records with multiple columns and thus a SqlDataReader is used to get these rows. Next, a SqlPipe is accessed from within the context of this connection, and it sends the SqlDataReader back.

4.3.4 Stored Procedures:
Stored procedures are by far the simplest of all the .NET based objects that you can create in SQL Server. First, the .NET method is limited in what type it can return. Because a stored procedure can return only int or nothing, only the following return types are valid: SqlInt32, SqlInt16, System.Int32, System.Int16 and void. All four integer types are compatible with the SQL Server int data type; void, of course, represents no return type.

The listing below is presented with a new twist in Listing 4.5, this method is still intended to be used as a stored procedure , but now it returns an int indicating whether any records were found (1) or not (0).

Public struct AdventureWorksFunctions
String S = "Select * from Sales.SalesOrderHeader where CustomerID = @CustID";
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = s;
SqlDataReader dr = cmd.ExecuteReader();
Int32 ret = dr.HasRows ? 1 : 0;
SqlPipe pipe =
Return (SqlInt32) ret;

Using it in T-SQL, you might code something like this.

Declare @ret Int;
Execute @ret = prOrderByCustomer ''
If @ret = 1
PRINT 'Success'

From: http://www.sqlservercentral.com/columnists/fFinney/introductiontosqlserver2005.asp