SQL Server Articles

SQL Server 2005 Enhancements

New features in SQL 2005 in brief:

  1. SQL Server 2005 as .NET runtime host: enhancing security, reliability, and performance
  2. Writing procedures, functions, and triggers in .NET languages
  3. Leveraging powerful new enhancements to T-SQL
  4. XML Support
  5. SQL Server 2005 as a Web Services platform

Enhancements in T-SQL

New Data Types:

  1. Varchar(max),Varbinary(max),Nvarchar(max) -Alternative to Text, Image
  2. XML Data Type– Untyped and Strongly Typed

TOP ():

SQL 7.0 and 2000 provided TOP (n) with constant expression

SQL 2005 provides TOP ()
It can also be used with INSERT, UPDATE, and DELETE (in addition to SELECT)

Output Clause :
Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

Example :
USE AdventureWorks;




DECLARE @MyTableVar table( ScrapReasonID smallint,


Name varchar(50),


ModifiedDate datetime);


INSERT Production.ScrapReason




INTO @MyTableVar


VALUES (N’Operator error’, GETDATE());


–Display the result set of the table variable.


SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;


–Display the result set of the table.


SELECT ScrapReasonID, Name, ModifiedDate


FROM Production.ScrapReason;



CTE & Recursive Queries

  1. An expression that produces a table that is referred to by name within the context of a single query
  2. Ability to traverse recursive hierarchies in a single query

 Typical scenarios:


Hierarchy in a table (MGRID-EMPID, Part-Subpart)


Find all employees reporting to a manager or


Find all parts required to assemble a product

Recursive, when references itself


Recursive form of CTE





Categories: SQL Server Articles

Leave a Reply