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;

 

GO

 

DECLARE @MyTableVar table( ScrapReasonID smallint,

 

Name varchar(50),

 

ModifiedDate datetime);

 

INSERT Production.ScrapReason

 

OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate

 

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;

 

GO

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

 

 

UNION ALL

 

Categories: SQL Server Articles

Leave a Reply