Executing Stored Procedure with Result Sets in SQL Server Denali

WITH RESULT SETS”is new enhanced feature comes with SQL Server “DENALI”. In many scenarios, wewant to return the result sets from SP with changed column name and withdifferent data type. In these cases we have been using Temporary table. 
Create temporary table, Insert data in temp table byexecuting Stored Procedure and display data from temp table, what if we can doit with simply executing stored procedure? Isn’t it awesome???
Yes, it is…. Now SQL Server Denali makes it possible. Let ussee it how…..
–createMember’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 * FROMMemberPersonalDetail
go
Now, I will make one simple stored procedure to return allcolumns of this table in SQL Server Denali CTP1 which we used to do since verylong 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 executethis stored procedure with regular method which we used to do and after that, Iwill execute the same stored procedure with “WITH RESULT SETS”,which will havechanged column name of few column and changed data type of column.
–executing SP
EXECgetMemberPersonalDetail
GO
–Executing SPwith “WITH RESULT SETS”
–MemberNamewill become “Name” from Varchar(20) to Varchar(6)
–both datecolumn name will also be changed.
EXECgetMemberPersonalDetail
WITH RESULT SETS
(
      (
            ID INT,
            Name Varchar(6),
            DateOfRegistration date,
            DateOfExpiration date
      )
);
Here is the screen shot which shows results of both theexecution of stored procedure.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a defaultreference of all articles but examples and explanations prepared by RiteshShah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Executing Stored Procedure with Result Sets in SQL Server Denali

WITH RESULT SETS”is new enhanced feature comes with SQL Server “DENALI”. In many scenarios, wewant to return the result sets from SP with changed column name and withdifferent data type. In these cases we have been using Temporary table. 
Create temporary table, Insert data in temp table byexecuting Stored Procedure and display data from temp table, what if we can doit with simply executing stored procedure? Isn’t it awesome???
Yes, it is…. Now SQL Server Denali makes it possible. Let ussee it how…..
–createMember’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 * FROMMemberPersonalDetail
go
Now, I will make one simple stored procedure to return allcolumns of this table in SQL Server Denali CTP1 which we used to do since verylong 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 executethis stored procedure with regular method which we used to do and after that, Iwill execute the same stored procedure with “WITH RESULT SETS”,which will havechanged column name of few column and changed data type of column.
–executing SP
EXECgetMemberPersonalDetail
GO
–Executing SPwith “WITH RESULT SETS”
–MemberNamewill become “Name” from Varchar(20) to Varchar(6)
–both datecolumn name will also be changed.
EXECgetMemberPersonalDetail
WITH RESULT SETS
(
      (
            ID INT,
            Name Varchar(6),
            DateOfRegistration date,
            DateOfExpiration date
      )
);
Here is the screen shot which shows results of both theexecution of stored procedure.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a defaultreference of all articles but examples and explanations prepared by RiteshShah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

MERGE statement in SQL Server 2008 and later version

MERGE is really a fantastic improvement in SQL Server 2008which is really underutilized, I have seen many time recently that developersare still using separate DML statement for Insert / Update and Delete wherethere 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 isgoing to read data and process it in one shot rather than performing singlestatement to table each time you write.
I will give you one small example so that you can see howone can use MERGE statement or which situation we can use MERGE statement in???
Suppose we have one Member’s personal Detail table where wecan find Memberid, member name, registration date and expiration date. There isone more table there for Member’s user name and password.
Now, we want to delete those users from memberLogin tablewhose expiration date has been met, we want to set default password for thosemember who are not expired right now and we want to make entry of those userwho are just registered and id/password is not set yet.
–createMember’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 * FROMMemberPersonalDetail
go
–createMember’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 * FROMMemberLoginDetail
go
–MERGEstatement with Insert / Update / Delete…..
–if you justneed Insert / update or Insert / delete or Update / Delete anyting
— you can useany combo
— I haveexplained all three DML in one MERGE statement to demonstrate it.
MERGEMemberLoginDetail AS mld
USING (SELECT MemberID,MemberName,ExpirationDateFROM 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 thetable whether operation is successfully done or not.
SELECT * FROMMemberLoginDetail
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

MERGE statement in SQL Server 2008 and later version

MERGE is really a fantastic improvement in SQL Server 2008which is really underutilized, I have seen many time recently that developersare still using separate DML statement for Insert / Update and Delete wherethere 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 isgoing to read data and process it in one shot rather than performing singlestatement to table each time you write.
I will give you one small example so that you can see howone can use MERGE statement or which situation we can use MERGE statement in???
Suppose we have one Member’s personal Detail table where wecan find Memberid, member name, registration date and expiration date. There isone more table there for Member’s user name and password.
Now, we want to delete those users from memberLogin tablewhose expiration date has been met, we want to set default password for thosemember who are not expired right now and we want to make entry of those userwho are just registered and id/password is not set yet.
–createMember’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 * FROMMemberPersonalDetail
go
–createMember’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 * FROMMemberLoginDetail
go
–MERGEstatement with Insert / Update / Delete…..
–if you justneed Insert / update or Insert / delete or Update / Delete anyting
— you can useany combo
— I haveexplained all three DML in one MERGE statement to demonstrate it.
MERGEMemberLoginDetail AS mld
USING (SELECT MemberID,MemberName,ExpirationDateFROM 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 thetable whether operation is successfully done or not.
SELECT * FROMMemberLoginDetail
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Find missing Index with DMVs in SQL Server 2005/2008/Denali

In the previous articles I saw how to find unused index sothat 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 inproduction server.
Today I will be exploring the script to find the missingindex, after finding that index; you can decide whether to create it or notbased 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 youneed 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 indexyou need to create. For more information on this, please click here.
sys.dm_db_missing_index_group_stats”: This DMV returns thesummary 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 returnsinformation about what missing index are contained in what missing index grouphandle. For more information on this, please click here.
sys.dm_db_missing_index_columns(Index_Handle)”:  This DMV gives you an idea about what columnsare 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 clickhere.
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 oneof the I have copided from my “Index_Handle” column of
–sys.dm_db_missing_index_detailsDMV, 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 missingindex, we are going to use first three of the above DMVs to find our missingindex.
Here you go!!!!
SELECT
      avg_total_user_cost *avg_user_impact * (user_seeks +user_scans) ASPossibleImprovement
      ,last_user_seek
      ,last_user_scan
      ,statementAS Object
      ,‘CREATEINDEX [IDX_’ + CONVERT(VARCHAR,GS.Group_Handle) + ‘_’ + CONVERT(VARCHAR,D.Index_Handle) + ‘_’
      + REPLACE(REPLACE(REPLACE([statement],‘]’,),‘[‘,),‘.’,) + ‘]’
      +‘ ON ‘
      + [statement]
      + ‘ (‘+ ISNULL (equality_columns,)
    + CASE WHENequality_columns IS NOTNULL ANDinequality_columns IS NOTNULL THEN ‘,’ ELSE END
    + ISNULL (inequality_columns, )
    + ‘)’
    + ISNULL (‘ INCLUDE (‘ + included_columns + ‘)’, )
      AS Create_Index_Syntax
FROM
      sys.dm_db_missing_index_groups ASG
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 ASD
ON
      G.index_handle = D.index_handle
Order By PossibleImprovement DESC
This is just a basic advice from DMVs regarding what indexesare missing and you have to create it, finally it’s up to you based on your requirementwhether to create index or not. You have to see the table name and columnwhether it has any selectivity or not then decide whether to create that or notas more index on table might improve performance of your SELECT but it willharm other DML statements so it is always advisable to use your human skills todecide rather than leave everything on DMVs.  
These DMVs could keep information for maximum of 500indexes.
Enjoy Indexing!!!!
If you want to refer all other articles related to index, clickhere.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Find missing Index with DMVs in SQL Server 2005/2008/Denali

In the previous articles I saw how to find unused index sothat 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 inproduction server.
Today I will be exploring the script to find the missingindex, after finding that index; you can decide whether to create it or notbased 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 youneed 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 indexyou need to create. For more information on this, please click here.
sys.dm_db_missing_index_group_stats”: This DMV returns thesummary 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 returnsinformation about what missing index are contained in what missing index grouphandle. For more information on this, please click here.
sys.dm_db_missing_index_columns(Index_Handle)”:  This DMV gives you an idea about what columnsare 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 clickhere.
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 oneof the I have copided from my “Index_Handle” column of
–sys.dm_db_missing_index_detailsDMV, 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 missingindex, we are going to use first three of the above DMVs to find our missingindex.
Here you go!!!!
SELECT
      avg_total_user_cost *avg_user_impact * (user_seeks +user_scans) ASPossibleImprovement
      ,last_user_seek
      ,last_user_scan
      ,statementAS Object
      ,‘CREATEINDEX [IDX_’ + CONVERT(VARCHAR,GS.Group_Handle) + ‘_’ + CONVERT(VARCHAR,D.Index_Handle) + ‘_’
      + REPLACE(REPLACE(REPLACE([statement],‘]’,),‘[‘,),‘.’,) + ‘]’
      +‘ ON ‘
      + [statement]
      + ‘ (‘+ ISNULL (equality_columns,)
    + CASE WHENequality_columns IS NOTNULL ANDinequality_columns IS NOTNULL THEN ‘,’ ELSE END
    + ISNULL (inequality_columns, )
    + ‘)’
    + ISNULL (‘ INCLUDE (‘ + included_columns + ‘)’, )
      AS Create_Index_Syntax
FROM
      sys.dm_db_missing_index_groups ASG
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 ASD
ON
      G.index_handle = D.index_handle
Order By PossibleImprovement DESC
This is just a basic advice from DMVs regarding what indexesare missing and you have to create it, finally it’s up to you based on your requirementwhether to create index or not. You have to see the table name and columnwhether it has any selectivity or not then decide whether to create that or notas more index on table might improve performance of your SELECT but it willharm other DML statements so it is always advisable to use your human skills todecide rather than leave everything on DMVs.  
These DMVs could keep information for maximum of 500indexes.
Enjoy Indexing!!!!
If you want to refer all other articles related to index, clickhere.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

Find unused index in SQL Server 2005/2008/Denali

If you are aware with Index well or if you have read all myprevious articles related to Index, you might aware that Index can increasethe speed of SELECT statement but can reduce INSERT/UPDATE/DELETE performanceso 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 requirementof deleting unused Index, right? But how to find which index has never beenused? Well I have written one small snippet of TSQL for the same which I amgoing to share with you.
Note: statistics you are going to see with below givenscript, would be refreshed and start collecting data again from zero if yourestart you server instance or database.  So, first decide your business cycle, let SQLServer collect data and then run the following script to know exact situationotherwise it may happen that some query runs regularly so you can see its statsand few run only once or twice in a month or a quarter and you don’t see itsstats and based on that you drop the index which affect the query when it startrunning at its regular time after a month or quarter. 
Here is the code which I was talking about, earlier:
–followingquery 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_statsindUsage
            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
–followingquery will show you list of ALL index in database
–along withdata 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_statsindUsage
            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 thoughtbefore deleting it. This is usually a good practice if you are doing this onproduction 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 adefault reference of all articles but examples and explanations prepared byRitesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile