Category Archives: Sql Server

Stored Procedures Optimization Tips

Use stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

Call stored procedure using its fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

Don’t use the prefix “sp_” in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.

The prefix “sp_” is used in the system stored procedures names. Microsoft does not recommend to use the prefix “sp_” in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with “sp_” in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix “sp_” in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

Use sp_executesql stored procedure instead of temporary stored procedures.
Microsoft recommends to use the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

Try to avoid using temporary tables inside your stored procedure.
Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the “Stored Procedures” category called “SP:Recompile”. You can also trace the event “SP:StmtStarting” to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

Advertisements

Run a .sql script files in C#

If you have placed in situation to execute .sql script files from .NET code on SQL Server, you will see that SqlCommand class is not that useful. In other words, you can’t execute batch commands that contains ‘GO’ (batch finalizer command). So you can think of several options, like split that script to several commands, and execute one by one. But you realize how complex is that task.
If you wanted to do that prior to SQL Server 2005, the only option is using osql utility. Since SQL Server 2005 there is another option which is much better and preferred. That option is using SMO library which comes with SQL Server and can be used for managing everything on SQL Server 2005. You can backup, restore databases, configure permissions, replication, etc.
I plan to post about how to backup and restore database using this SMO library in near future. Here will write only about how to read sql file and execute the content on SQL Server.

using System.Data.SqlClient;

using System.IO;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

string sqlConnectionString =

“Data Source=(local);Initial Catalog=AdventureWorks;Integrated Security=True”;

FileInfo file = new FileInfo(“C:\\myscript.sql”);

string script = file.OpenText().ReadToEnd();

SqlConnection conn = new SqlConnection(sqlConnectionString);

Server server = new Server(new ServerConnection(conn));

server.ConnectionContext.ExecuteNonQuery(script);

}

}

}

A Reference should be added to the following DLLs.

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo

Access Deleted Row Columns / Information in DataTable – ADO.NET

Sometimes you may need to access the information in a deleted row in a DataTable. If you access the deleted row direcly, you will get an exception saying that the row has been deleted.

To avoid the exception, but still access the information in the deleted row of the DataTable, you just need to specify that you want to get the information from the original version of the row by specifying DataRowVersion.Original as follows:

if (dataRow.RowState == DataRowState.Deleted)
id = (string)dataRow[“CustomerID”, DataRowVersion.Original];

You can get only the deleted rows from a DataTable by iterating through all the rows and checking the DataRowState, or you could simply create a DataView that filters on DataViewRowState.Deleted:

// Get Only Deleted Rows in DataTable
DataView dv = new DataView(sourceDataTable,
null, null, DataViewRowState.Deleted);

You can also convert that DataView to a DataTable using the new DataView.ToTable() method:

DataTable dt = dv.ToTable();

The rows in the new DataTable, dt, will not be marked as DataRowState.Deleted but rather DataRowState.Added, because this is a new DataTable. You can easily iterate through this table now as none of the rows will be mark deleted and you will have full access to the information without specifying DataRowVersion.

How SQL Query works?

SQL Server performs a couple of internal steps before executing a query. The steps that interest us here are compilation and execution.
When SQL Server receives a query for execution, its execution plan should already be present in memory (the procedure cache); if not, SQL Server will have to compile the query before executing it.
The compilation process is divided into four parts: parsing, normalization, compilation and optimization.
Parsing:
During this stage, SQL Server checks the query for Syntax errors and transforms it into a complier-ready structure that it will use later to optimize the query. It does not check for object names or column names.
Normalization:
At this stage, SQL Server checks all references to objects in the query. This is where we typically get the “Object not found” message when an object referenced in the query is not found in the database. SQL Server also checks to see if a query makes sense. For example, we cannot execute a table or select from a stored procedure.
Bear in mind that while we can optimize select, insert, and update statements, there is no way to optimize if, while, and for operators.
Compilation:
This is where we start building the execution plan for the query we passed to SQL Server. First, we create a sequence tree. The sequence tree is normalized, again, which includes adding implicit conversions if necessary. Also during this phase, if the query is referencing views, a view definition is placed in the query. If a statement is a DML statement, a special object is created called the query graph. The query graph is the object on which the optimizer works to generate an optimized plan for the query. This is the compiled plan that is stored in the procedure cache for reuse.
Optimization:
SQL Server Optimizer is a cost-based optimizer, which means that it will come up with the cheapest execution plan available for each SQL statement. For each SQL statement to run we need to use resources like CPU, memory, hard disk, etc. The cheapest plan is the one that will use the least amount of resources to get the desired output. For optimizing DML statements, SQL Server will test different indexes and join orders to get the best plan for executing the query. Your index definition helps optimizer by reducing/minimizing resource usage. If the index has a high selectivity then it is most suitable for optimization. Because a complex query will take into account all indexes and joins, there can be many paths to take to execute the query. In such cases, determining the best path for optimization can take a long time. The longer this process takes, the higher the cost that is involved.
So first, a trivial plan is generated. This plan assumes that cost-based optimization is costly; if there is only one path for execution possible, there is no point optimizing the query. For example, when placing a simple insert statement into a table, there is no way that your indexes or join orders can increase optimization, so the trivial plan is used.
For any particular query, SQL Server will use statistics to understand the distribution of data. Statistics are stored in the statblob column of the sysindexes table in each database. Join orders can also be optimized based on the number of rows that are fetched by each join condition.

Microsoft Sql Server 2005 new features.

(1)     Reporting services :-

In SQL Server Reporting Services increase there benefit to customers. These include centralized report storage and management, control over how reports are processed or administered, and also have ability to fastest changing reports formats (HTML, Excel, PDF, etc.). This entire capabilities make reporting Services is valuable. So Web-based reporting has ability to make up-to-date information available to a distributed group of users. Reporting Services provides a impressive way to find and view reports, the Reporting Services interface may not be the most appropriate one for all report users.

(2) Partitioned Tables and Indexes:-Sql Server 2005 has ability to partition tables and indexes have always been a design to improve performance and manageability in larger databases. Microsoft SQL Server 2005 has many new features that simplify the design of using this feature.

(3) Xml Web services: – SQL Server 2005 provides a standard method for getting the database engine by using SOAP via HTTP. With the help of this methods, we can send SOAP/HTTP requests to SQL Server to execute stored procedures, extended stored procedures, T-SQL batch statements and scalar-valued user-defined functions which can have with or without parameters .When we use SQL Server as a Web Service that can for HTTP SOAP requests requires must create an HTTP endpoint and defining the methods that the endpoint exposes. When we create HTTP endpoint, that endpoint must be created with a unique URL that we uses to listen for incoming HTTP requests.

(4) Database Mirroring: – Database mirroring provides solution to provide high availability at the database level where all things depends upon the database. In Sql Server we can maintained synchronized copies of a database on two separate servers allows switching between them. And its plays its role on failure of primary server.

(5) CLR Integration: – Microsoft SQL Server 2005 has adopt the database programming model hosting by the Microsoft .NET Framework 2.0 Common Language Runtime (CLR). .Net CLR hosted inside SQL Server to improve performance, security and reliability. This helps developers to write procedures, triggers, and functions in any of the CLR languages.

 

 

Article 2:

1. T-SQL (Transaction SQL) enhancements
T-SQL is the native set-based RDBMS programming language offering high-performance data access. It now incorporates many new features including error handling via the TRY and CATCH paradigm, Common Table Expressions (CTEs), which return a record set in a statement, and the ability to shift columns to rows and vice versa with the PIVOT and UNPIVOT commands.

Pivot and Unpivot are relational operators which can be used to change a table valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column into multiple columns in output, and performs aggregations where they are required on any remaining column values that are needed in the output.

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

 

2. CLR (Common Language Runtime)
The next major enhancement in SQL Server 2005 is the integration of a .NET compliant language such as C#, ASP.NET or VB.NET to build objects (stored procedures, triggers, functions, etc.). This

 

 

 

 

good URLs:

 

part1->  http://www.c-sharpcorner.com/UploadFile/skumaar_mca/sqlserverfeatures2005part109182009015945AM/sqlserverfeatures2005part1.aspx

 

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.

 

(or) Common Table Expressions (CTEs) are one of the most exciting features to be introduced with SQL Server 2005. A CTE is a “temporary result set” that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

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

 

 

Common Table Expressions offer the same functionality as a view,  but are ideal for one-off usages where you don’t necessarily need a view defined for the system

 

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

 

 

Recursive Common Table Expressions

The recursion includes a query on the CTE itself. The following shows the CTE – with both the base case and recursive step – along with a SELECT query that returns the rows from the CTE:

WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ReportsTo, HierarchyLevel) AS
(
-- Base case
SELECT
EmployeeID,
LastName,
FirstName,
ReportsTo,
1 as HierarchyLevel
FROM Employees
WHERE ReportsTo IS NULL

UNION ALL

-- Recursive step
SELECT
e.EmployeeID,
e.LastName,
e.FirstName,
e.ReportsTo,
eh.HierarchyLevel + 1 AS HierarchyLevel
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON
e.ReportsTo = eh.EmployeeID
)

SELECT *
FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName

 

 

CTEs can also be used in place of views. The use of CTEs provides two main advantages. One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work wit data such as a te h derived tables normally requires a separate definition for the derived mporary table or a table-valued function, using CTEs make it easier to see the definition of the derived table with the code that uses it. The other thing is that CTEs significantly reduces the amount of code required for a query that traverses recursive hierarchies.

 

Note:

 

1. The query using the CTE must be the first query appearing after the CTE

 

2. Unlike a derived table, CTEs can be defined just once, yet appear multiple times in the subsequent query.


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 TRY CATCH 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.

 

 

part 2à  http://www.c-sharpcorner.com/UploadFile/skumaar_mca/SqlServerFeaturesPart209292009012558AM/SqlServerFeaturesPart2.aspx

 

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.

PIVOT is a relational operator used to provide a more readable or simpler view of a table’s data. This is achieved by rotating a rows data into columns using the query response to simply the query results.

UNPIVOT is the opposite. It rotates columns into rows.

PIVOTING can make the sales data below

Employee Jan Feb Mar
John 1000 2000 3000
Mike 1500
Tom 800 650

 

EmployeeName Month Sales
John Jan 1000
John Feb 2000
John March 3000
Mike Feb 1500
Tom Jan 800
Tom Mar 600

 

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.

 

 

 

Sql Server 2005

 

Last Identity Value in the Table :

There is one more identity-related function in SQL Server 2000. IDENT_CURRENT returns the last identity value set on a specified table (in any scope of any process). To use it, just supply the table name as a parameter :

select IDENT_CURRENT('Equipment')

The value may be equal to values obtained using other methods (using other identity or MAX functions), but it may also be different.