Introduction:
I have written the sql server features part 1. Here I write the continus of the previous article. If you have not come acrossed the sql server features part1, I suggest you to read the part 1.
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/sqlserverfeatures2005part109182009015945AM/sqlserverfeatures2005part1.aspx?ArticleID=2425e2ae-6a8d-4bba-aa7e-a6eb71d3465a
In my previous article, we have seen the new features like the Exception handling, CTE, CROSS APPLY, XML data types,etc., As I promised, I submit the second part ofthis article.Here I have explained the table varialbe, RAISERROR, SYNONYM, Custom Data types, INTERSECT and EXCEPT ,etc.., Lets see one by one.
1.Table variable
The table introduced in the sql server 2005. In the previous versions of the sql server temp table has used. As you know it will be stored in the tempdb. It has to be dropped and that will access the data across the database.It will affect the performace.
In the sql server 2005 has introduced the table variable. It will be there in the processor memory for the certain size and then it will be moved into the tempdb.
Let us see an example of the Table variable.
DECLARE @Student TABLE
(
iStudentID INT IDENTITY(1,1) PRIMARY KEY
,vStudentName VARCHAR(MAX)
,vGrade CHAR(1)
)
INSERT INTO @Student(vStudentName,vGrade)
VALUES('Senthil','B')
INSERT INTO @Student(vStudentName,vGrade)
VALUES('Kumar','A')
SELECT * FROM @Student
-
The table variable will be working upto that scope of the stored procedure or functions.
-
Normally, temp table cannot be created in the functions. If you want to perform any custom table operations in the functions then it was difficult. But here the table variable can be used to DML operations.
-
However the functions will not allow us to do the DML operations.But we can do it in the table variable.
-
The table variable can be return as table in the functions. That is called multi valued functions.
-
The transactions cannot be done in the table variable.
2. New DataTypes and Large Data Types
The new data types like nchar, nvarchar, ntext, uniqueidentifier introduced in the sql server 2005.
* The MAX key word has introduced to set the maximum limit of the size to the variables.
3. INTERSECT AND EXCEPT
The INTERSECT statement is one of the new features introduced in the sql server 2005 edition. It helps us to retrieve the common records in the two tables.
Normally it can be acheieved in the IN or EXISTS operators to retrieve the common records based on the key field.
But here it considers the entire record.
The EXCEPT statement just opposite to the INTERSECT statement. If you use the EXCEPT statement between the two tables then it will returns the
Except common records in the both table.
Let us see an example.
I have created the two tables called Student1 and Student2
CREATE TABLE Student1
(
iStudentID INT,
vStudentName VARCHAR(25)
)
CREATE TABLE Student2
(
iStudentID INT,
vStudentName VARCHAR(25)
)
Insert few sample records in the both table.
INSERT INTO Student1(iStudentID,vStudentName) VALUES(1,'A')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(2,'B')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(3,'C')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(4,'D')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(5,'E')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(6,'X')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(7,'Y')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(8,'Z')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(1,'U')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(2,'V')
INSERT INTO Student1(iStudentID,vStudentName) VALUES(3,'W')
INSERT INTO Student2(iStudentID,vStudentName) VALUES(6,'X')
INSERT INTO Student2(iStudentID,vStudentName) VALUES(7,'Y')
INSERT INTO Student2(iStudentID,vStudentName) VALUES(8,'Z')
When you do the INTERSECT statement, then the columns should be equal in the both tables.Event changing the column name will give you the error. Its applicable for INTERSECT,EXCEPT and UNION statement.
SELECT iStudentID,vStudentName FROM Student1
INTERSECT
SELECT iStudentID,vStudentName FROM Student2
The output of the INTERSECT query is the following
iStudentID vStudentName
6 X
7 Y
8 Z
Let us see the EXCEPT statement query.
SELECT iStudentID,vStudentName FROM Student1
EXCEPT
SELECT iStudentID,vStudentName FROM Student2
The output of the above query is
iStudentID vStudentName
1 A
1 U
2 B
2 V
3 C
3 W
4 D
5 E
Here the Except the common fields it returns to the unmatching records in the both table.
4.Custom Data types
The custom data type can be created for the existing datatypes.
Newly created custom data type can be used in the CREATE, ALTER or Stored procedure parameter.
The following tables will be used to list the data types in the sql server.
SELECT * FROM SYS.TYPES
If we want to add the data type into the sql then we can use the following system stored procedure.
sp_addtype 'SenText',TEXT
Here SenText is the custom data type and TEXT is built in data type.
We cannot create the custom data type for XML, table and timestamp.
And also we cannot create the data type with MAX in the VARCHAR,NVARCHAR data type.
The following stored procedure is used to drop the type from the SYS.TYPES
sp_droptype 'SenText'
5.RAISERROR
The RAISERROR function can be used to send the server error message to the calling application.It can send the dynamic message or message defined in the SYS.MESSAGE catalog view.
SELECT * FROM SYS.MESSAGES
SYS.MESSAGE catalog view allow us to insert our own message.This message id can be referenced to throw the error in the RAISERROR function.
sp_addmessage
The message id should be greater than 50000.
sp_dropmessage procedure is used to delete the user defined message in the SYS.MESSAGE catalog view.
The syntax of the RAISERROR function.
RAISERROR ('Example RAISERROR message.', -- Message text.
16, -- Severity.
1 -- State.
)
Wherever we use this function in the stored procedure then it will send this message to the calling program.
Let us see the adding new message in the SYS.MESSAGE catalog view.
sp_addmessage 50002,16,'Sample Message'
Now, you can see the added message.
SELECT * FROM SYS.MESSAGES WHERE message_id=50002
Let us see dropping message.
sp_dropmessage 50002
Let us see an example.
BEGIN TRY
SELECT 10/0
END TRY
BEGIN CATCH
RAISERROR('Divide by Zero Exception',16,1)
END CATCH
6.Synonym
The SYNONYM is one of the new object in the sql server 2005. It is used to create the alias to the existing object.
If you have very complex or long names then you can create the synonym to that table or view.
So, you can access like the table.
You can create the SYNONYM like the following.
CREATE SYNONYM SynProducts
FOR {DOB].Products
We have created the SYNONYM. We can access the SYNONYM like parent table.
SELECT * FROM SynProducts
You can update record through the SYNONYM table.
UPDATE SynProducts SET ProductName = 'Chais' WHERE ProductName='Chai'
If you want to see the created SYNONYMS, then you can use the following tables.
SELECT * FROM SYS.SYNONYMS
SELECT * FROM SYS.OBJECTS WHERE type='SN'
7.PIVOT and UNPIVOT
PIVOT and UNPIVOT will be used to summarise the report data. PIVOT means convert the rows into columns. UNPIVOT means rotating the columns into rows.
I will be explaining this feature in the separate article.
8.New Functions
There are few new functions introduced in the sql server 2005. These are very useful when we writes the complex queries.
ROW_NUMBER()
This function generates the sequential id to the oartioned result sets. When we write the normal queries we cannot iterate the identity numbers.
But this function can be set the row number to the result set which starts from integer value 1.
RANK()
This RANK function can be used to set the rank to the partioned result, like students class rank.
If the two values are same then it will fix it as 1 each. Next rank will be reckon from the 3.
DENSE_RANK()
This DENSE_RANK() function will do the similar kind of operation, but there is a difference in the ranking style. If the two partioned results are same then it will put the 1 each. Next its start from 2.
Let us see an example for these functions.
CREATE TABLE Students
(
iStudentID INT IDENTITY(1,1),
vName VARCHAR(25),
iTotal INT
)
SELECT * FROM Students
INSERT INTO Students(vName,iTotal) VALUES('A',400)
INSERT INTO Students(vName,iTotal) VALUES('B',450)
INSERT INTO Students(vName,iTotal) VALUES('C',400)
INSERT INTO Students(vName,iTotal) VALUES('D',450)
INSERT INTO Students(vName,iTotal) VALUES('E',475)
SELECT
iStudentID,vName,iTotal,
ROW_NUMBER() OVER (ORDER BY vName) AS [Row_Number],
RANK() OVER(ORDER BY iTotal DESC) AS [Rank],
DENSE_RANK() OVER(ORDER BY iTotal DESC) AS [Dense_Rank]
FROM Students
ORDER BY iStudentID ASC
9. CLR Integration
Sql server added very essential feature of .Net CLR integration. The triggers, stored procedure can be written in the .net code.
The constructed code in the .net environment can be compiled and registered into the sql server.
The stored procedure, functions,etc., can call that register .net code in the sql server, it runs under the sq server .net environment.
It helps us to write the complex coding in the .net which cannot be done in the sql server.Those code can be called in the sql server.
The following sql server catalog views is used to list the registered assemblies in the sql server.
SELECT * FROM SYS.ASSEMBLIES
SELECT * FROM SYS.OBJECTS WHERE TYPE='A'
Once you have written code in the .net environment then it should be build as dll.
This library has to be registered in the sql server.
CREATE ASSEMBLY Name
FROM Path
Before that we have to enable the sql clr.
EXEC sp_configure 'clr enabled',1
or it can be done through the sql server configuration surface tool in the configuration tools.
Let us see the calling of the assembly.
EXTERNAL NAME Assembly name.Namespace name.program name
CONCLUSION:
I hope that you have enjoyed the reading of the sql server features in the 2005. I was planning to write the sql server new features in the 2008 edition. But I know few of us not clear about what was introduced in the sql server 2005. I will write the new article for the features introduced in the sql server 2008. Please post your suggestions, corrections or addition points about my article. Thank you.