Sql Server 2005 Features Part I

Introduction

In this article, I have discussed about the features in the microsft sql server 2005. Sql server 2005 has added many features like CLR integration, CTE, PIVOT/UNPIVOT,DDL Triggers, Indexed Viewes, etc.,
There are many features introduced in the sql server 2005 edition. Let us few features in the part 1 article.

1.XML Data Type:

XML Data Type included in the Sql server 2005. Before that we have used other data type like varchar and text to store the xml content

CREATE TABLE tab_SampleXMLData
(
iSNo INT IDENTITY(1,1)
,vXmlContent XML
,dCreatedTime DATETIME DEFAULT GETDATE()
)
GO

Here I have listed few methods to auto generate the xml data from the table using the sql query.

SELECT * FROM Products FOR XML AUTO

It will return the record in the Product with its details in the attributes style.

For example, the output of the xml data will be like the following.

<Products ProductID="1" ProductName="Chai" SupplierID="1" CategoryID="1" QuantityPerUnit="10 boxes x 20 bags" UnitPrice="18.0000" UnitsInStock="39" UnitsOnOrder="0" ReorderLevel="10" Discontinued="0" />

SELECT * FROM Products FOR XML RAW

It will return the record with the row as the element name for the every record.For example, the following xml content was generated from the above select query. 

SELECT * FROM Products FOR XML AUTO, ELEMENTS

It will show in the parent, child node format. The result will be in the format of the XML tree.

For example, the following xml data generated from the above select query.

<Products>

<ProductID>1</ProductID>

<ProductName>Chai</ProductName>

<SupplierID>1</SupplierID>

<CategoryID>1</CategoryID>

<QuantityPerUnit>10 boxes x 20 bags</QuantityPerUnit>

<UnitPrice>18.0000</UnitPrice>

<UnitsInStock>39</UnitsInStock>

<UnitsOnOrder>0</UnitsOnOrder>

<ReorderLevel>10</ReorderLevel>

<Discontinued>0</Discontinued>

</Products>

Example for insert an XML data into the XML column in the table.As I said in the previous versions there was no data types like XML. Programmers have used the varchar or text column for storing the xml content.

DECLARE @Prods VARCHAR(MAX)
SET @Prods = (SELECT * FROM Products FOR XML PATH('SaleProducts'))

INSERT INTO tab_SampleXMLData(vXmlContent)
SELECT @Prods

DECLARE @Customs VARCHAR(MAX)
SET @Customs = (SELECT * FROM Customers FOR XML PATH('NorthwindCustomers'))

INSERT INTO tab_SampleXMLData(vXmlContent)
SELECT @Customs

 Here I have used the XML PATH(‘SaleProducts’).What it does means it will take the name given in the XML PATH will be the parent node for the retieved results.Generally it will take the table name.

SELECT * FROM tab_SampleXMLData FOR XML AUTO, ELEMENTS

In the above query generated the xml, which will have the sub xml nodes. Because already a column have the XML content. So this XML content will be nested under a column in the newly generated xml document.


2. CTE (Common Table Expressions)

The Common Table Expressions(CTE) is the new features in the sql server 2005.
It will returns the result set.It works like the views.This can be join with the tables, views,etc.., like tables.This will be working up to the scope of the program.It can be used only once.

Consider this example,MyCTE will return the resultset that can be combined with the other tables.

WITH MyCTE(PID)
AS
(
SELECT ProductID FROM Products WHERE CategoryID = 2
)
SELECT * FROM [Order Details] WHERE ProductID IN
(SELECT PID FROM MyCTE)

This example shows, how to combine the two CTE resultset.

WITH ProductCTE(PID)
AS
(
SELECT ProductID FROM Products WHERE CategoryID = 2
),
OrderProductCTE(PID,Total)
AS
(
SELECT ProductID,SUM(UnitPrice * Quantity) AS [Total Sale]
FROM [Order Details] GROUP BY ProductID
)
SELECT P.PID,O.Total FROM ProductCTE P
INNER JOIN OrderProductCTE O
ON P.PID = O.PID

3. CROSS APPLY

The cross apply is one of the new feature that will do the cartician product.
If there are two tables name called table1 and table2

There are two tables name called table1, table2

Let us see the First table

Table - table1

No Name
1 A
2 B
3 C

Table - table2

Grade
A
C
B

Then the possibility of the output will be table1 X table 2

Here table1 have 3 rows and table2 has 3 rows, so the final result table will have 9 rows.The possible number of columns will be table1 column + table2 column.

No Name Grade

1 A A
1 A C
1 A B
2 B A
2 B C
2 B B
3 C A
3 C C
3 C B

Here it will combine the rows in the first table in the first row in the table2.Again it will combine the first row in the table1 with the next row in the table2.Similary it will combine all the rows with the first row in the table1.Similarly it will process for the remaning rows in the table1.

SELECT * FROM Products
CROSS APPLY "Orders"

SELECT * FROM Products, Orders

4. Exception handling using TRY...CATCH

In the previous version we had the @@ERROR property. That has stored the last occurance of the errors.
Here, they have introducted the error handling method called TRY...CATCH.

It will work like in the programming languages.

BEGIN TRY

// Sql Statements

END TRY

BEGIN CATCH

//Handle the exception details

END CATCH

There are some error details methods avaiable. That will return the error description about the occured error.

ERROR_NUMBER()
ERROR_STATE()
ERROR_SEVERITY()
ERROR_LINE()
ERROR_PROCEDURE()
ERROR_MESSAGE()

The following procedure will show the practical approach the error handling in the sql server. This TRY..CATCH exception handling cannot be implement in the SQL server fucntions.

Let us see an example of TRYCATCH in the stored procedure.

 

CREATE PROCEDURE Proc_ExceptionHandlingExample
AS
BEGIN
/*
Purpose : Sample procedure for check the Try...Catch
Output : It will returns the error details if the stored procedure
throws any error
Created By : Senthilkumar
Created On : September 17, 2009
*/
SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRY
SELECT 15/0
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER()
,ERROR_STATE()
,ERROR_SEVERITY()
,ERROR_LINE()
,ERROR_PROCEDURE()
,ERROR_MESSAGE()
,SUSER_SNAME()
,Host_NAME()
END CATCH
END
GO

5.Indexed Views

As you know, the views in the sql server 2005 is normal view.It is an virtual table. When you executed the script of the view stored as schema object in the database. When you retrieve or touch the views then it gets execute and fill the table.We cannot create any index on that. In Sql server 2005 they have introduced the view called Indexed view or permanent view. Actually it stores the data permanentely.It will not support the after or for trigger.
But it will allow the instead of trigger.

You may have doubt like how to set the view as normal or indexed view.
There an option like SCHEMABINDING while creating the view. That will decide the view must be normal view or indexed view.

Let us see an example in the Indexed Views

 

CREATE VIEW [DBO].VW_ProductsReport
WITH SCHEMABINDING
AS

SELECT
P.ProductID,P.ProductName,P.SupplierID,
O.OrderID,O.CustomerID,C.CompanyName,
C.ContactName,C.Address,O.EmployeeID,
O.OrderDate,O.ShipName,O.ShipCountry
FROM [DBO].[Order Details] OD
INNER JOIN [DBO].Orders O ON O.OrderID = OD.OrderID
INNER JOIN [DBO].Products P ON P.ProductID = OD.ProductID
INNER JOIN [DBO].Customers C ON C.CustomerID = O.CustomerID

There are some restrictions in the indexed view.

- It must be the two name part in the table.It must be like [DBO].tablename.
- We cannot write SELECT * FROM TABLENAME.Moreover it will be useless.
- Covering index can be created on this. It can be the combination of 32
columns.
- View can be nested in the 32 levels.


6. New Triggers in sql server 2005

Instead of Trigger on Index Views,In the sql server 2005 has added the instead of trigger on the Views.
Normally we cannot write the trigger on the views. But the new feature added in the sql server 2005, instead of trigger can be written on the indexed views.

CREATE TRIGGER [DBO].Tr_VW_ProductReport_Delete
ON VW_ProductsReport
INSTEAD OF DELETE
AS
BEGIN
PRINT ''
END

Check the output using the following statement.

DELETE FROM VW_ProductsReport WHERE ProductID = 1

The output, when you delete the record, it wont delete. Instead of that it will print that message.
If you want to write any other logics you can do there in the trigger.


DDL Triggers

The Data Definition Triggers (DDL) can be created on the Server or Database.Normally this is used to track the user ddl events like creation of database or
create the table, drop the table, etc..,There are many DDL Events avaiable 

CREATE_TABLE
ALTER_TABLE
DROP_TABLE
CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE

For example i have created one dll trigger

CREATE TRIGGER Tr_DDL_DROPTABLE
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON

ROLLBACK
PRINT ''
END

After compilation of this trigger under any database, if you tried to drop the table then it will say a message “You cannot drop the table”. The table couldnot be dropped unless until if you drop the trigger or disable the trigger.

Conclusion

So far, we have seen the features in the sql server 2005 part 1. I will be writing the second part of this article soon. Please post your feedback, suggestions or any corrections about this article.