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.