Overview:
In this article I would like to give you an overview on the some of then new features of T-SQL in SQL Server 2005. This article is mainly for beginners for more information either you can visit MSDN or refer BOL.
Introduction:
One of the common questions in many of the interview(s) is what are the new features of SQL server 2005? Interviewers are not expecting you tell all the features but they want to know whether you are upgrading yourself on the latest things in the IT or not. This article perfectly suits to the people who are preparing for the interviews or want to know what is inside in T-SQL SQL Server 2005.
What are we waiting for, let’s get into the details.
Top keyword Enhancements
In SQL Server 2000, you were forced to use a constant value in the TOP clause. With SQL Server 2005 the TOP function now gives an option to use an expression in conjunction with the TOP clause. Now you are no longer have to hardcode the number with the TOP. The TOP clause is also supported in the INSERT, UPDATE, and DELETE statements.
Let's look at the below example
USE Pubs
DECLARE @MyTop INT
SET @MyTop = 24
SELECT TOP (@MyTop) EmployeeID, Ename FROM HR.Employee
Here, i have declared a variable and assigned a Number to it.
PIVOT and UNPIVOT
There is a million dollar question i.e. How to convert rows into columns? We use all sorts of queries like sub-query, in-line queries with group by to convert rows into columns. PIVOT and UNPIVOT are new features are available in SQL Server 2005’s T-SQL with this you can easily do it.
These operators are mainly useful for OLAP world, where you’re dealing with tabular data rather than relational data and need to produce summary information. The PIVOT operator transforms a set of rows into columns. The UNPIVOT operator reverses the PIVOT operator i.e. transforming the pivoted columns back into rows.
Let's look the below example
Let's create table Order_Info
CREATE TABLE ORDER_INFO
(
OrderId INT,
CustomerID INT,
OrderYear INT
);
and insert following data.
OrderId CustomerID OrderYear
----------- ----------- -----------
1000 111 2001
1001 111 2001
1002 111 2001
1003 111 2002
1004 111 2002
1005 111 2003
1006 111 2004
1007 111 2005
Now you want to generate a report like this
CustomerID 2001 2002 2003 2004 2005
----------- ----------- ----------- ----------- ----------- -----------
111 3 2 1 1 1
If you use PIVOT then you can write this query in just 3 lines.
SELECT * FROM ORDER_INFO
PIVOT (COUNT(OrderID)
FOR OrderYear IN([2001], [2002], [2003], [2004], [2005]))
AS P
WHERE CustomerID = 111
DDL Triggers
In SQL Server 2000 allows triggers to be defined for data manipulation events such as inserting or updating a row. SQL Server 2005 extends this by allowing triggers to be defined on DDL events such as creating and dropping tables, views, procedures and logins. DDL triggers can be associated with CREATE, ALTER, and DROP statements.
This is very good feature to the DBA. Now if anybody issues any DROP statement then DBA has the rights to stop it by defining these kinds of triggers.
Let’s look at the below example:
CREATE TRIGGER NO_DROP_TABLE
ON DATABASE FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You cant issue DROP Table or ALTER Table commands'
ROLLBACK
Here, you can see how the new DDL trigger can be used to restrict the use of the DROP TABLE and ALTER TABLE statements. If an ALTER TABLE or DROP TABLE statement is issued, the NO_DROP_TABLE trigger will print an error message and rollback the attempted DDL operation.
DML Output
Normally when you issue DELETE statetment it will tell you how many records got updated, but will not tell you which records. With this feature you can easily see the records that are affected with DML operations.
Let's look at the below example.
DECLARE @temp_ORDER_INFO TABLE(
OrderID int,
CustomerID int,
OrderYear int);
DELETE FROM ORDER_INFO
OUTPUT DELETED.* INTO @temp_ORDER_INFO
SELECT * FROM @@temp_ORDER_INFO
Here the OUTPUT DELETED.* clause specifies that log all the deleted records into @temp_ORDER_INFO.
(8 row(s) affected)
OrderId CustomerID OrderYear
----------- ----------- -----------
1000 111 2001
1001 111 2001
1002 111 2001
1003 111 2002
1004 111 2002
1005 111 2003
1006 111 2004
1007 111 2005
(8 row(s) affected)
varchar(max) Data Type
This new data type provides an alternative to text/image data type. This is an extension to the varchar, nvarchar and varbinary data types. This data type supports up to 2GB of data.
CREATE TABLE Test_VARCHAR_MAX
(
ID INT IDENTITY NOT NULL,
Data VARCHAR(MAX) NOT NULL
)
Summary
Happy Learning….
|
No responses found. Be the first to respond and make money from revenue sharing program.
|