SQL Server 2005 features - Part I


Introduction:
 

In this article, I have discussed about the features in the Microsoft sql server 2005. Sql server 2005 has added many features like CLR integration, CTE, PIVOT/UNPIVOT,DDL Triggers, Indexed Views, 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.

 

   <row 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 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 result set 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 Cartesian product.


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 remaining 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 occurrence of the errors.
Here, they have introduced 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 available. That will return the error description about the occurred 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 implementing in the SQL server functions.


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 permanently. 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 '<< You have tried to delete the record in the view VW_ProductsReport. Sorry! You cannot delete the record..>>'

END


Check the output using the following statement.


DELETE
FROM VW_ProductsReport WHERE ProductID = 1
 

The output, when you delete the record, it won't 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 '<< You cannot drop the table >>'

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 couldn't 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.


Similar Articles