“WITH RESULT SETS” is new enhanced feature comes with SQL Server “DENALI”. In many scenarios, we want to return the result sets from SP with changed column name and with different data type. In these cases we have been using Temporary table. 

Create temporary table, Insert data in temp table by executing Stored Procedure and display data from temp table, what if we can do it with simply executing stored procedure? Isn’t it awesome???

Yes, it is…. Now SQL Server Denali makes it possible. Let us see it how…..

--create Member's personal detail table and insert data in it.

Create Table MemberPersonalDetail

(

MemberID INT Identity(1,1),

MemberName Varchar(20),

RegisterDate date,

ExpirationDate date

)

GO

INSERT INTO MemberPersonalDetail

SELECT 'Ritesh Shah','01/01/2000','12/31/2015' Union ALL

SELECT 'Rajan Shah','02/07/2005','06/20/2011' Union ALL

SELECT 'Teerth Shah','06/22/2011','12/31/2015'

GO

SELECT * FROM MemberPersonalDetail

go

Now, I will make one simple stored procedure to return all columns of this table in SQL Server Denali CTP1 which we used to do since very long back. There is nothing new in this stored procedure.

CREATE PROC getMemberPersonalDetail

AS

      SELECT 

            MemberID

            ,MemberName

            ,RegisterDate

            ,ExpirationDate

      FROM

            MemberPersonalDetail

GO

After making this simple stored procedure, I will execute this stored procedure with regular method which we used to do and after that, I will execute the same stored procedure with “WITH RESULT SETS”,which will have changed column name of few column and changed data type of column.

--executing SP

EXEC getMemberPersonalDetail

GO

--Executing SP with "WITH RESULT SETS"

--MemberName will become "Name" from Varchar(20) to Varchar(6)

--both date column name will also be changed.

EXEC getMemberPersonalDetail

WITH RESULT SETS

(

      (

            ID INT,

            Name Varchar(6),

            DateOfRegistration date,

            DateOfExpiration date

      )

);

Here is the screen shot which shows results of both the execution of stored procedure.

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”

MERGE is really a fantastic improvement in SQL Server 2008 which is really underutilized, I have seen many time recently that developers are still using separate DML statement for Insert / Update and Delete where there is a chance they can use MERGE statement of they can use condition based Insert / Update and Delete in one shot. 

This will give performance advantage as complete process is going to read data and process it in one shot rather than performing single statement to table each time you write.

I will give you one small example so that you can see how one can use MERGE statement or which situation we can use MERGE statement in???

Suppose we have one Member’s personal Detail table where we can find Memberid, member name, registration date and expiration date. There is one more table there for Member’s user name and password.

Now, we want to delete those users from memberLogin table whose expiration date has been met, we want to set default password for those member who are not expired right now and we want to make entry of those user who are just registered and id/password is not set yet.

--create Member's personal detail table and insert data in it.

Create Table MemberPersonalDetail

(

MemberID INT Identity(1,1),

MemberName Varchar(20),

RegisterDate date,

ExpirationDate date

)

GO

INSERT INTO MemberPersonalDetail

SELECT 'Ritesh Shah','01/01/2000','12/31/2015' Union ALL

SELECT 'Rajan Shah','02/07/2005','06/20/2011' Union ALL

SELECT 'Teerth Shah','06/22/2011','12/31/2015'

GO

SELECT * FROM MemberPersonalDetail

go

--create Member's login detail table and insert data in it.

CREATE TABLE MemberLoginDetail

(

MemberID INT,

UserName varchar(20),

UserPassword varchar(20)

)

GO

INSERT INTO MemberLoginDetail

SELECT 1,'Ritesh Shah','TestPassword' UNION ALL

SELECT 2,'Rajan Shah','goodluck'

GO

SELECT * FROM MemberLoginDetail

go

--MERGE statement with Insert / Update / Delete.....

--if you just need Insert / update or Insert / delete or Update / Delete anyting

-- you can use any combo

-- I have explained all three DML in one MERGE statement to demonstrate it.

MERGE MemberLoginDetail AS mld

USING (SELECT MemberID,MemberName,ExpirationDate FROM MemberPersonalDetail) AS mpd

ON mld.MemberID = mpd.MemberID

WHEN MATCHED AND mpd.ExpirationDate<getdate() THEN DELETE

WHEN MATCHED THEN UPDATE SET mld.UserPassword = 'DefaultPassword'

WHEN NOT MATCHED THEN

INSERT(MemberID,UserName,UserPassword)

VALUES(mpd.memberID,mpd.MemberName,'DefaultPassword');

GO

--check the table whether operation is successfully done or not.

SELECT * FROM MemberLoginDetail

go

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”

In the previous articles I saw how to find unused index so that you can find it and drop it to improve performance of your Insert /Update /Delete statement and claim some disk space which is really useful in production server.

Today I will be exploring the script to find the missing index, after finding that index; you can decide whether to create it or not based on the requirement of your application. 

There are many important DMVs (Dynamic Management View) there in SQL Server 2005 and higher version which are keeping information you need to know to find missing index. Following is the list of those DMVs.

“sys.dm_db_missing_index_details”:  This DMV returns details about missing index you need to create. For more information on this, please click here.

“sys.dm_db_missing_index_group_stats”: This DMV returns the summary of benefit you would have received if you would have the particular index. For more information on this, please click here.

“sys.dm_db_missing_index_groups”: This DMV returns information about what missing index are contained in what missing index group handle. For more information on this, please click here.

“sys.dm_db_missing_index_columns(Index_Handle)”:  This DMV gives you an idea about what columns are missing in Index, it is based on the “Index_Handle” field of “Sys.dm_db_missing_index_details” DMV. For more information on this, please click here.

Let us run all these four DMVs to see what it has for us:

select * from sys.dm_db_missing_index_details

select * from sys.dm_db_missing_index_group_stats

select * from sys.dm_db_missing_index_groups

--43816 is one of the I have copided from my "Index_Handle" column of

--sys.dm_db_missing_index_details DMV, you might get something else than 43816

select * from sys.dm_db_missing_index_columns(43816)

So these are the DMVs which will be useful in order to find missing index, we are going to use first three of the above DMVs to find our missing index.

Here you go!!!!

SELECT

      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS PossibleImprovement

      ,last_user_seek

      ,last_user_scan

      ,statement AS Object

      ,'CREATE INDEX [IDX_' + CONVERT(VARCHAR,GS.Group_Handle) + '_' + CONVERT(VARCHAR,D.Index_Handle) + '_'

      + REPLACE(REPLACE(REPLACE([statement],']',''),'[',''),'.','') + ']'

      +' ON '

      + [statement]

      + ' (' + ISNULL (equality_columns,'')

    + CASE WHEN equality_columns IS NOT NULL AND inequality_columns IS NOT NULL THEN ',' ELSE '' END

    + ISNULL (inequality_columns, '')

    + ')'

    + ISNULL (' INCLUDE (' + included_columns + ')', '')

      AS Create_Index_Syntax

FROM

      sys.dm_db_missing_index_groups AS G

INNER JOIN

      sys.dm_db_missing_index_group_stats AS GS

ON

      GS.group_handle = G.index_group_handle

INNER JOIN

      sys.dm_db_missing_index_details AS D

ON

      G.index_handle = D.index_handle

Order By PossibleImprovement DESC

This is just a basic advice from DMVs regarding what indexes are missing and you have to create it, finally it’s up to you based on your requirement whether to create index or not. You have to see the table name and column whether it has any selectivity or not then decide whether to create that or not as more index on table might improve performance of your SELECT but it will harm other DML statements so it is always advisable to use your human skills to decide rather than leave everything on DMVs.  

These DMVs could keep information for maximum of 500 indexes.

Enjoy Indexing!!!!

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 aware with Index well or if you have read all my previous articles related to Index, you might aware that Index can increase the speed of SELECT statement but can reduce INSERT/UPDATE/DELETE performance so it is better to remove Unused index, it will not only give benefit to INSERT/ UPDATE/ DELETE but it will free up some disk space resources too.

So, after this clarification you understand the requirement of deleting unused Index, right? But how to find which index has never been used? Well I have written one small snippet of TSQL for the same which I am going to share with you.

Note: statistics you are going to see with below given script, would be refreshed and start collecting data again from zero if you restart you server instance or database.  So, first decide your business cycle, let SQL Server collect data and then run the following script to know exact situation otherwise it may happen that some query runs regularly so you can see its stats and few run only once or twice in a month or a quarter and you don’t see its stats and based on that you drop the index which affect the query when it start running at its regular time after a month or quarter. 

Here is the code which I was talking about, earlier:

--following query will show you which index is never used

select

      ind.Index_id,

      obj.Name as TableName,

      ind.Name as IndexName,

      ind.Type_Desc,

      indUsage.user_seeks,

      indUsage.user_scans,

      indUsage.user_lookups,

      indUsage.user_updates,

      indUsage.last_user_seek,

      indUsage.last_user_scan,

      'drop index [' + ind.name + '] ON [' + obj.name + ']' as DropCommand

from

      Sys.Indexes as ind JOIN Sys.Objects as obj on ind.object_id=obj.Object_ID

      LEFT JOIN  sys.dm_db_index_usage_stats indUsage

            ON

                  ind.object_id = indUsage.object_id

                  AND ind.Index_id=indUsage.Index_id

where

      ind.type_desc<>'HEAP' and obj.type<>'S'

      AND objectproperty(obj.object_id,'isusertable') = 1

      AND (isnull(indUsage.user_seeks,0)=0 AND isnull(indUsage.user_scans,0)=0 and isnull(indUsage.user_lookups,0)=0)

order by obj.name,ind.Name

--following query will show you list of ALL index in database

--along with data how many times it get seek, scan, lookup or update 

select

      ind.Index_id,

      obj.Name as TableName,

      ind.Name as IndexName,

      ind.Type_Desc,

      indUsage.user_seeks,

      indUsage.user_scans,

      indUsage.user_lookups,

      indUsage.user_updates,

      indUsage.last_user_seek,

      indUsage.last_user_scan

from

      Sys.Indexes as ind JOIN Sys.Objects as obj on ind.object_id=obj.Object_ID

      LEFT JOIN  sys.dm_db_index_usage_stats indUsage

            ON

                  ind.object_id = indUsage.object_id

                  AND ind.Index_id=indUsage.Index_id

where

      ind.type_desc<>'HEAP' and obj.type<>'S'

      AND objectproperty(obj.object_id,'isusertable') = 1

order by obj.name,ind.Name

Be sure before droping any index, give it a second thought before deleting it. This is usually a good practice if you are doing this on production server.

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”

I have already mentioned quite a few times since now in few of my previous articles that Index could be the key to boost up performance of your “SELECT” query but highly fragmented Index could degrade the performance of your query.

When you created an index, sorting are done and in case of clustered index, physical data get stored page wise but after regular Insert/Update/Delete in the same table, fragmentation comes into the picture where your physical data order doesn’t match up with your logical data order exists in data page.  If there is any heavy fragmentation you find for any index, you should try to remove this by using Index reorganize or Index rebuild.

Let us understand what the exact meaning of Reorganize and Rebuild is.

Reorganize Index defrag the fragmented pages at leaf level, in simple language, it arrange data in leaf page level and if it find any empty space in any page, it removes it so free space could be claimed. This action is online action; means while doing this action on live server, it won’t block any objects (like table) for long time and you can query your object (database Table) while this operation in progress. Reorganize Index consume less resource as compared with Rebuild Index but Reorganize Index is ideal for low fragmented Index, let us say if your Index fragmented percentage is between 5% to 40%, you can use Reorganize Index otherwise go for Rebuild Index to gain proper benefit.

BTW, if you index fragmentation is less than 5%, then don’t need to do anything as even after reorganizing or rebuilding, you won’t get any more boost up as less than 5% fragmentation is not really a big deal and that is why, you don’t need to add any overhead on your server by doing reorganizing or rebuilding.

Rebuild Index drops current index and recreate Index again, this consumes high resources of the servers but it is worth doing if you Index fragmentation percentage is higher, let’s say more than 40%. While doing Rebuilding, objects get locked so you won’t be able to query (if you have not used WITH (ONLINE=ON) option). 

Based on my personal experience I personally prefer to “Rebuild Index” on off hours or may be in weekend if table is very big because sometime, it may take few hours or a day.

Now, question comes into the picture that how can I decide the fragmentation of the Index? Well it is fairly very simple, you have to query system function “sys.dm_db_index_physical_stats” and you have to pass your database name and table id in this function and it will return with very crucial information about your indexes on the specified table but it will not return Index name but it will return, Index ID and that is why, we have to make one JOIN of this “sys.dm_db_index_physical_stats” function with “sys.Indexes” system catalog.

SELECT

      sysin.name as IndexName

      ,func.avg_fragmentation_in_percent

FROM

      sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'orders'),NULL, NULL, NULL) AS func

JOIN

      sys.indexes AS sysIn

ON

      func.object_id = sysIn.object_id AND func.index_id = sysIn.index_id;

I wanted to look the index details of my table “Orders” so I used my table name but you can replace the table name you want.

Hope you are now clear when to use rebuild and when to use reorganize. It is very simple, if you see values between 5% to 40% in your “avg_fragmentation_in_percent” field of above query, go for reorganize, if you see >40% then go for rebuild and if less than 5%, get back to your chair and have rest, there is nothing to do in this matter.  LOL

Once you decide whether to do Rebuild or Reorganize, you have use very simple query to do this operation on your table. Have a look at below queries.

--I wanted to reorganize my index, named "idx_refno" on orders table

ALTER INDEX idx_refno ON Orders REORGANIZE

GO

--I wanted to reorganize all my indexes which are there for orders table

ALTER INDEX ALL ON Orders REORGANIZE

GO

--I wanted to REBUILD my index, named "idx_refno" on orders table

ALTER INDEX idx_refno ON Orders REBUILD

GO

--I wanted to REBUILD all my indexes which are there for orders table

ALTER INDEX ALL ON Orders REBUILD

GO

BTW, I have created Index "idx_refno" and "orders" table in one of my previous article of Index, if you want to use the same object, have a look at the table and index script at here.

Do drop your comments about this concept!!!

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

Happy Indexing!!!

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”