My previous article was based on “Non clustered Index” and this article focuses on “Included Column” feature of Index which was introduced in SQL Server 2005 and still there in newer version.

As long as architecture of “Included Column” concerns, whatever columns you have defined under “Include” clause under the index, those will be stored on the Leaf pages, it won’t get stored on the Root page or Intermediate page of the index.

Now, let us talk little bit about the benefit we are going to get out of this feature.

The main feature is that, the columns you have under “Include” clause of “Create Index” statement would not affect the size of the index. Index has limitation that you can have it on maximum of 16 column / 900 bytes. So no matter how big columns you are going to use in your “Include”, you will get benefit for sure.

You should keep those columns in “Include” clause which generally comes under “SELECT” clause and not being used much in “WHERE”, “GROUP BY” or “ON” clause of “JOIN”.

We are going to create one table with big column size and will try to create Non Clustered Index on that.

--create dummy table and see whether we are able to create index on that or not.

create table NonClustCheck

(

Col1 char(500),

col2 char(400),

col3 char(10)

)

GO

--if you will try following idex, you will greeted with error message as follows:

--Msg 1944, Level 16, State 1, Line 1

--Index 'idx_NonClustCheck' was not created. This index has a key length of at least 910 bytes.

--The maximum permissible key length is 900 bytes.

create nonclustered index idx_NonClustCheck on NonClustCheck(col1,col2,col3)

go

--you can create following index on the same table

create nonclustered index idx_NonClustCheck1 on NonClustCheck(col1)

INCLUDE(col2,col3)

go

we finally we have created non clustered index with one key column (Col1) and two included column (Col2 and Col3). Let us check whether this index gets scan or seek or optimizer decides not to use this.

--well there is no data in this table yet,

--even just wanted to see whether Non clustered index is having any effect or not.

--run following query with execution plan and you can see Index Seek

-- Ctrl + M and than F5 to run query with execution plan.

select * from NonClustCheck where Col1='SQLHub.Com'

GO

--you can see Col2 is not as the Index Key,

--even you can see that our non clustered index is getting SCAN.

select * from NonClustCheck where Col2='hello'

GO

Hope this will be helpful to you. Do drop comments; it will encourage me for sure.

if you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

After finishing two articles on Index “Some basics about Index in SQL Server” and “Clustered Index in SQL Server”, this is now time to explore nonclustered index in SQL Server. There are only two major difference between Clustered and Nonclustered Index are as follows:

1.)    You can have only one Clustered index per table and 249 (till SQL Server 2005) and 999 (after SQL Server 2005) Nonclustered Index per table

2.)    Clustered Index stores actual row data in the leaf level and nonclustered index would stores only those columns which are included in nonclustered index and pointer to the actual row which may be in clustered index or in heap (know more about leaf, heap, RID etc. from here).

Like clustered index, you can have one or multiple columns defined in Nonclustered Index too. Order of the column defined in Nonclustered plays an important role to meet Index seek, as I told you in previous article too that Index seek is good than Index Scan, so one should try to meet up Index seek as long as possible.

We are going to use same database and “Orders” table defined in previous article “Clustered Index in SQL Server”.

--creating nonclustered index,

CREATE NONCLUSTERED INDEX idx_orderdate on Orders(orderdate,orderid)

--run following query with execution plan and see the results in execution plan

--you can see execution plan with the following steps

--first select below given query

--Press Ctrl+M

--press F5

SELECT OrderDate,orderid from orders where OrderDate = '2010-02-01 00:04:00.000'

You will see your nonClustered Index Seek in your execution plan.

 

There are few more indexes under the same category like INCLUDE columns, Filtered Index, Covering Index which we will be exploring very soon.

if you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

As per the promise I have made in “Some basics about Index in SQL Server” article, I am coming up with each Indexes of SQL Server and first and most important is “Clustered Index”. 

Understanding of Clustered Index:

As I told in previous article too that Index is the key of performance, good and managed Index could boost up your speed of retrieving of data from table.

Clustered Index contains actual data of the table in leaf level pages in logically sorted order (to understand root and leaf level page, click here). Since it is logically sorted, it doesn’t need to go for all data present there in Index. 

For example if you are looking for the phone number of “Ritesh Shah” in telephone directory, you can move to a page which has phone number of the person whose first name starts with “RI” and once all instance of “RI” over in directory, you don’t need to move on as there is NO CHANCE, you will get “Ritesh” anywhere in directory except those particular pages.

BTW, if you don’t have clustered index on your table, than your table would be called “HEAP”, which wouldn’t not have logically sorted data so if you are searching for “Ritesh Shah”, you can to check complete table as you never know, where you will find “Ritesh Shah”, just one method, go each and every row of table and check for matching criteria.

Like any other columns, you can define clustered index in more than one field too and all the columns covered up under the index, called key column.

While choosing a prime candidate for Clustered Index column in your table, you have to select the columns which meet few of the general criteria defined below. (you can say following criteria as a best practice while choosing index candidate)

--> You key column or combination of key columns should be unique and not null. If your You key column or combination of key columns are not unique than SQL Server has to add one more hidden column of 4-byte INT to make it unique. However, you can’t see that hidden column neither can query it directly; it would be purely for SQL Server’s internal use.

--> It should be short as wide key value would increase the depth of Clustered Index and will reduce the performance a bit and also increase the size of non-clustered index as it is being there as a reference in all non-clustered index.

--> Select less changing or no changing fields for you clustered index as Key value indicates the location of page where actual data resides, if you change this key value, row has to be deleted from that page and has to move to another appropriate page which reduces the performance and increase unnecessary overhead to IO.

Generally whenever you make Primary Key in any of your table, SQL Server itself create clustered index on it but if you want to keep clustered index on any other column(s) due to high selectivity on those column(s), you can do it.

Have you got bored of so long theory? Let us do some practical and check it out?

--create one database which you can delete after running this example

create database SQLHub

GO

USE SQLHub

GO

--if orders table is already there. you can delete it than create new one with name "Orders"

IF OBJECT_ID('orders', 'U') IS NOT NULL BEGIN

      DROP TABLE orders

END

GO

--creating table

CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)

GO

--inserting 100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERT script from one of his demo to generate big table)

INSERT INTO orders (OrderDate, Amount, Refno)

SELECT TOP 100000

      DATEADD(minute, ABS(a.object_id % 50000 ), CAST('2010-02-01' AS DATETIME)),

      ABS(a.object_id % 10),

      CAST(ABS(a.object_id) AS VARCHAR)

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

GO

--creating clustered index, however it is not meeting the criteria I have

--given in article, but this is ok for example

CREATE CLUSTERED INDEX idx_refno ON orders(refno)

GO

--run both of the following query with execution plan and see the results in execution plan

--you can see execution plan with the following steps

--first select both of the below given query

--Press Ctrl+M

--press F5

SELECT TOP 10 * from orders where refno=4

SELECT TOP 10 * from orders where OrderDate = '2010-02-01 00:04:00.000'

GO

--if you wish, you can uncomment below code and delete SQLHub database

--use master

--go

--drop database sqlhub

If you execute both the query with “Actual Execution Plan”, you will see first query is having Index Seek and second query is having Index Scan. 

Seek and Scan is really interesting topic which I will cover later but just keep in mind that, Seek is good, Scan is bad as it will check all records of the index.

if you want to refer all other articles related to index, click here.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”

If you are working with SQL Server 7.0 or SQL Server 2000 since long and jump into SQL Server 2005 and/or 2008 suddenly you may be finding the option to open DTS package of SQL Server 2000 in SSMS. It was very easy from Enterprise manager of SQL Server2000. You can expand “Data Transformation Service” table under the “Database” tab, right click on that and click on “Open Package “ command. Have a look at screen capture.

But you will not be able to see “Data Transformation Service” command in SSMS directly. Isn’t it there? Answer is, it is there but at other location.

 Expand the Management object.

 Open the Legacy object.

 Right click on Data Transformation Services.

 Click on "Open Package File"

Have a look at screen capture:

However, you can open old .DTS packages in SSMS but it is better to upgrade it to .DTSX package. There is one nice article at simple-talk.com. Have a look.

http://www.simple-talk.com/sql/sql-server-2005/dts-to-ssis-migration/

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

If you are SQL Server geek, you must be aware with Stored Procedure. While question comes to implement stored procedure in front end language, many developers makes mistake or feel confusion as per my recent observation so I am tempted to write one article which can show how to handle stored procedure in C# when it return some value and when it return some record sets.

--CREATE TABLE

CREATE TABLE empDemo

(

ID INT NOT NULL,

NAME VARCHAR(50)

)

GO

--INSERT DATA

INSERT INTO empDemo

SELECT 1,'Ritesh' UNION ALL

SELECT 2,'Rajan'

--create SP which will return list of employee

--NOTE: this is just basic SP, you can create

--your own for your custom need.

CREATE PROC ListEmp (@id INT)

AS

BEGIN

SELECT * FROM empDemo WHERE ID=@id

END

--check SP whether it works.

EXEC ListEmp 2

Now, we will see C# code (in asp.net code behind) to return the list of employee:

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

string strConn = "Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa";

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = "ListEmp";

command.Parameters.AddWithValue("@id", "2");

//create one data adapter which will execute the command and fill the data into data set

System.Data.SqlClient.SqlDataAdapter recordAdp = new System.Data.SqlClient.SqlDataAdapter();

recordAdp.SelectCommand = command;

System.Data.DataSet recordSet = new System.Data.DataSet();

recordAdp.Fill(recordSet);

command.Dispose();

conn.Close();

//bind gridview1 with our dataset

GridView1.DataSource = recordSet.Tables[0].DefaultView;

GridView1.DataBind();

}

This seems basic but really useful for newbie, now what if we have some other DML command and we want to return value from SP and handle it in our C# ASP.NET.

--create SP to insert record and retun value

--we will handle return value in C# code behind

CREATE PROC InsertEmp(@ID INT, @Name VARCHAR(20))

AS

BEGIN

BEGIN TRY

INSERT INTO empDemo(ID,Name) VALUES(@ID,@Name)

return 1

END TRY

BEGIN CATCH

return 0

END CATCH

END

--check SP works or not

EXEC InsertEmp 3,'Alka'

Now let us move to ASP.NET C# code behind.

protected void Button1_Click(object sender, EventArgs e)

{

//create connection string and assign it to SqlConnection object

string strConn = "Data Source=.;Initial Catalog=AdventureWorks;Persist Security Info=True;User ID=sa;Password=sa";

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(strConn);

conn.Open();

//setup SqlCommand and assign SP name along with input parameter variable name and value

System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand();

command.CommandType = System.Data.CommandType.StoredProcedure;

command.Connection = conn;

command.CommandText = "InsertEmp";

command.Parameters.Add("@ReturnValue",System.Data.SqlDbType.Int);

command.Parameters["@ReturnValue"].Direction = System.Data.ParameterDirection.ReturnValue;

command.Parameters.AddWithValue("@id", "4");

command.Parameters.AddWithValue("@Name", "Bihag");

command.ExecuteScalar();

int i = Convert.ToInt32(command.Parameters["@ReturnValue"].Value);

if (i == 1)

{

Response.Write("Successfull!!!!");

}

else

{

Response.Write("Not Successfull!!!!");

}

}

Hope you have enjoyed it!!!!

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

I have written many articles on differences and comparison on various topic of Microsoft SQL Server. Here is one more difference between Stored Procedure and User Define Function. Many times I have seen new developer in confusion about usage of functions so after finishing the series of articles in stored procedures and user define functions I thought to write one article for these differences.

Actually before Microsoft SQL Server 2000, there was no concept of UDFs and it has first been revealed in Microsoft SQL Server 2000 to wrap up your complex T-SQL logic but initially people were not started adopt it but I guess they have started focusing in it with Microsoft SQL Server 2005 when UDFs comes up with new features like table valued function and CLR function implementation. Now, the situation is, UDFs become one powerful tool in SQL Server kit.

The main difference I feel between SPs and UDFs is usage in SELECT. One can use UDFs anywhere in JOIN, FROM, WHERE and HAVING clause whereas SPs are not that much flexible.

UDFs are simple to invoke in any T-SQL statement then SPs.

Some DML statements like INSERT, UPDATE and DELETE are not permissible in UDFs whereas you can use it in SPs.

You can not call non-deterministic function of SQL Server inside the UDFs whereas you can call them in SPs. For example GETDATE() function, it is non-deterministic and can’t be called within UDFs.

Stored Procedure can call function but function can't call stored procedure.

User defined function can have only input parameter whereas SPs can have input as well as output parameter.

You can use Try...Catch in SPs whereas UDF can't support it.

If you are more interested to learn about SPs and UDFs than you can sear http://www.sqlhub.com for more details as I wrote dozens of article on this topic with so many real life examples.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

This is continuous topic of CURSOR. You can refer my past article on cursor at

http://ritesh-a-shah.blogspot.com/2009/03/what-is-cursor-definition-of-cursor-in.html

http://ritesh-a-shah.blogspot.com/2009/03/forwardonly-and-fastforward-cursor.html

http://ritesh-a-shah.blogspot.com/2009/03/dynamic-cross-tab-query-with-cursor-in.html

I gave definition of CURSOR and explained different type of CURSOR in my first article given above. Second article was for FORWARD_ONLY cursor and third one was for dynamic cross tab query with FAST_FORWARD cursor. Now this article will show you example of dynamic cursor which can move forward and backward as well. It will get fresh record set with every FETCH statement so that while fetching the record, we will be able to get fresh modified record by another user for our SELECT statement in SQL Server 2005.

Let us see it practically.

--create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

--INSERT records

INSERT INTO emps

SELECT 'Ritesh','MIS','echem' UNION ALL

SELECT 'Bihag', 'MIS', 'CT' UNION ALL

SELECT 'Rajan', 'account','Marwadi' UNION ALL

SELECT 'Alka','account','tata' UNION ALL

SELECT 'Alpesh','Chemical','echem'

GO

DECLARE @Counter INT

SET @Counter =1

DECLARE @strName VARCHAR(200)

DECLARE @empCursor CURSOR

--declaring SCROLL cursor

--which will move first, last, forward and backward

SET @empCursor = CURSOR SCROLL FOR

SELECT Name FROM emps ORDER BY NAME

OPEN @empCursor

FETCH NEXT FROM @empCursor INTO @strName

--user of FETCH NEXT

WHILE (@@FETCH_STATUS = 0)

BEGIN

PRINT @strName + ' NEXT'

FETCH NEXT FROM @empCursor INTO @strName

SET @Counter = @Counter + 1

END

--user of FETCH PRIOR

WHILE (@Counter > 1)

BEGIN

FETCH PRIOR FROM @empCursor INTO @strName

PRINT @strName + ' PRIOR'

SET @Counter = @Counter - 1

END

--user of FETCH FIRST

BEGIN

FETCH FIRST FROM @empCursor INTO @strName

PRINT @strName +' FIRST'

END

--user of FETCH LAST

BEGIN

FETCH LAST FROM @empCursor INTO @strName

PRINT @strName +' LAST'

END

CLOSE @empCursor

DEALLOCATE @empCursor

GO

Note: this is very resource consuming cursor so think twice before use it.

Reference: Ritesh Shah/Rashmika Vaghela