What version to go for (updgrade) after SQL Server 2000???

I have developed one project very long back in .NET Framework 2.0 and SQL Server 2000 for one doctor in USA. He is still using that software but since than many rules gets changed in USA so he come back to me with those changes and also told me that I want to use latest technology for my project and database.
He might have searched internet for getting knowledge about the latest version of .NET and SQL Server. Talking about SQL Server, he told me that the latest version of SQL Server is “Denali” and I want to use it in my project. While talking about this, I made him understand that we should go for SQL Server 2008 R2 rather than “Denali” by giving some very strong and valid reason and one of that strong reason is “Denali” is not in production, it is in CTP (Community Technology Preview).
But after this discussion, I felt to share some of my personal opinions on this blog.
After SQL Server 2000 version, you have three choices for upgrade.
1.)    SQL Server 2005
2.)    SQL Server 2008
3.)    SQL Server 2008 R2
I am excluding “SQL Server Denali” from this as it is still CTP version, not good to choose it for production as of now though it has eye catchy and flashy features which attract you.
Out of these three versions, I would, personally, like to go for “SQL Server 2008 R2”. Let me tell you why I am thinking this.
SQL Server 2005 is not a good deal at this time, though it has upgraded engine and many new features as compared with SQL Server 2000, SQL Server 2005 is now in “Extended Support”. It means you will neither get any “No-Charge” support nor claim any warranty. Soon after release of “Denali”, Microsoft may end even “Extended Support” for SQL Server 2005 (may be in 2014/2015).
While compare SQL Server 2008 with SQL Server 2008 R2, R2 gives much more facility than SQL Server 2008, mainly multiserver management, greater security, self service BI and many more.
Apart from that, SQL Server 2008 was released in 2008 and SQL Server 2008 R2 is released in 2010 so you will get longer support in “SQL Server 2008 R2” then “SQL Server 2008”, means better reward of your money. 
Generally in any SQL Server, you get support for your version for minimum 10 years. Suppose SQL Server 2008 R2 releases in 2010, Microsoft will definitely give you support for SQL Server 2008 R2 till 2020. For detailed understanding of support life cycle, refer Microsoft website from here. You have to go to “SUPPORT LIFECYCLE” tab in that link.
This is what I am thinking; this article is totally based on my personal view. You have to decide the proper version by looking at your need; this article is just the guideline to make you aware with some of the facts which may help you in your decision.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Msg 7301 Cannot obtain the required interface (“IID_IDBSchemaRowset”) from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “LinkToAceess”

While executing example given in my previous article, one of the reader faced an issue so he sent an email to me by complaining that he is facing following error:
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “LinkToAceess” reported an error. Access denied.
Msg 7301, Level 16, State 2, Procedure sp_tables_ex, Line 41
Cannot obtain the required interface (“IID_IDBSchemaRowset”) from OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “LinkToAceess”.
This is really very simple error regarding the setting of the provider you have used in your linked server. In our case, we have used “Microsoft.ACE.OLEDB.12.0” so we have to set the property of the same.
In your SSMS, go to “Object Explorer” and follow the below path:
Server->Server Objects->Linked Server->Providers->Microsoft.Ace.Oledb.12.0
Right click on it and click on “property” from popup menu, then set “Allow process” to true.
Click on “Ok” button and enjoy you OPENQUERY.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

OpenRowSet and OpenQuery in SQL Server 2005/2008

Said both of the techniques (OpenRowSetand OpenQuery) are almost same and may confuse many developers. I have been asked many time personally by few of my team member in recent years as well as I have seen the same question in few forums like Experts-Exchange and Forums.Asp.Net.
Basically both commands requires to access remote data from other data source like other SQL Server or may be Oracle or may be Access or Excel or anything else. Basically both of these commands requires connection information of source database, table information etc.
OpenQuery gets all these information from Linked Server where as in OpenRowSet, you have to provide all these information at run time. There is no other difference.
Syntaxes of both the commands from Microsoft’s website:
OPENROWSET
( { ‘provider_name’ , { ‘datasource’ ; ‘user_id’ ; ‘password’
   | ‘provider_string’ }
   , {   [ catalog. ] [ schema. ] object
       | ‘query’
     }
   | BULK ‘data_file’ ,
       { FORMATFILE = ‘format_file_path’ [ ]
       | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )
::=
   [ , CODEPAGE = { ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’ } ]
   [ , ERRORFILE = ‘file_name’ ]
   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]
   [ , MAXERRORS = maximum_errors ]
   [ , ROWS_PER_BATCH = rows_per_batch ]
   [ , ORDER ( { column [ ASC | DESC ] } [ ,…n ] ) [ UNIQUE ]
OpenSource:
OPENQUERY ( linked_server ,'query' )
Let us see how does it works, we are going to create one access file named “acc1.accdb” and will create one Employee table inside “acc1.accdb” file which will be stored in D drive of computer. You can have your own access file at your desired location.
Here is the query for the same:
–add link server
exec sp_addlinkedserver
@server=‘LinkToAceess’,
@srvproduct=‘AccessDatabase’,
–if you have older version of access then kindly use old jet provider
–Microsoft.Jet.OLEDB.4.0
@provider=‘Microsoft.ACE.OLEDB.12.0’,
@datasrc=‘D:\db1.accdb’
GO
–add MDB’s credental, if any
EXEC sp_addlinkedsrvlogin ‘LinkToAceess’, ‘false’
exec sp_serveroption [LinkToAceess],‘Data Access’,‘true’
–check whether ‘LinkToAccess’ has been added
select * from sys.servers
–list all tables available in Testing.MDB
exec sp_tables_ex ‘LinkToAceess’
–now query with OPENSOURCE
SELECT * FROM OPENQUERY(LinkToAceess, ‘SELECT id,empname FROM employee’)
–now query with OPENROWSET,
–which will not use our linked server
SELECT *
   FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
      ‘D:\db1.accdb’;
      ;,Employee);
     
–if you get following error
–Msg 15281, Level 16, State 1, Line 1
–SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.
–you have to enable “Ad Hoc Distributed Queries”
sp_configure ‘show advanced options’,1
reconfigure
GO
sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure
GO
–now try again same query
SELECT * FROM
OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
      ‘D:\db1.accdb’;
      ‘admin’;,Employee);
Hope you have enjoyed!!!!
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Audit Trail with OUTPUT clause in SQL Server 2005/2008/Denali

Audit Trail is one of the task client asked for in most of the project so that they can keep watch on crucial fields regarding its change.
I have seen many small & few medium sized companies is still not hiring SQL Server professional and used to go ahead with .NET developer. .NET developer generally keep their knowledge updated with .NET technology but they lack of knowledge in SQL Server domain so for Audit Trail, they still rely on old pseudo table (INSERTED, DELETED) accessible in Trigger. But in SQL Server 2005 and later, you don’t even need to rely on Triggers for Audit Trail but you can use new “OUTPUT” clause.
“OUTPUT” clause is still much underutilized feature even after 7 years. That is the reason I am writing on Audit trail and OUTPUT clause once again.
Within “OUTPUT” clause, you can access pseudo table and generate your “Audit Trail” log. I will show you with an example.
We will have one table of Test comes to an environmental company to analyze, if client change the Test s/he wants to perform, we have to keep trail on that, if he cancel any test, we have to keep trail on that too.
–we will keep “Audit Trail” for below given table
create table ClientOrder
(
      OrderID varchar(5)
      ,ClientID varchar(5)
      ,Test varchar(20)
      ,OrderDate datetime default getdate()
)
GO
–following is the table, we will keep “Audit Trail” in.
–this will keep track of all data changed
CREATE TABLE AuditOfOrder
(
      Id INT Identity(1,1)
      ,OrderID varchar(50)
      ,OldTest varchar(20)
      ,NewTest varchar(20)
      ,DMLPerformed varchar(15)
      ,ChangeDate datetime default getdate()
)
GO
–inserting data in “ClientOrder” table
–all insert will be stored in audit trail table too via “OUTPUT” clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES(‘A1001’,‘CHEM1’,‘VOCMS Group1’)
GO
–inserting data in “ClientOrder” table
–all insert will be stored in audit trail table too via “OUTPUT” clause
INSERT INTO ClientOrder(OrderID,ClientID,Test)
Output Inserted.OrderID,Inserted.Test,Inserted.Test,‘Insert’ intoAuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
VALUES(‘A1001’,‘CHEM1’,‘Pesticide Group1’)
GO
–let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
–we will perform UPDATE on “ClientOrder” table
–which will be recorded in “AuditOfOrder” table too
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
UPDATEClientOrder
      SET Test =‘SVOC Stars’
OUTPUT
      inserted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      inserted.Test AS NewTest,
      ‘UPDATE’ as DMLPerformed
WHEREClientOrder.Test=‘VOCMS Group1’
) t
GO
–let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
–Finally the log of Delete will be stored in the same way.
INSERT INTO AuditOfOrder(OrderID,OldTest,NewTest,DMLPerformed)
SELECT
t.OrderID,
t.OldTest,
t.NewTest,
t.DMLPerformed
FROM
(
DELETE FROM ClientOrder
OUTPUT
      deleted.OrderID AS OrderID,
      deleted.Test AS OldTest,
      deleted.Test AS NewTest,
      ‘DELETE’ as DMLPerformed
WHEREClientOrder.Test=‘SVOC Stars’
) t
GO
–let us see what we have in both tables now.
select * from ClientOrder
select * from AuditOfOrder
go
Isn’t this interesting? Start using it.
BTW, I have previously written some articles regarding Audit Trail techniques (old & new both), if you want to refer it, have a look at below links:
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find third highest salary from Employee table

I had discussion regarding few of the TSQL techniques with our .NET developer team and in-between one of the new .NET developer girl, who have just joined the team, told me that she has been asked in few of the interviews regarding this question “Find third highest salary from Employee table” and she had provided solution with big query and calculation.
After listening this, it comes to my mind that we don’t need any big query with calculations, neither we need Rank or row_number etc., it could be achieved with very short and simple query and don’t need any version specific functions like Row_Number, Rank, Dense_rank.
Let us see how we can achieve this:
create table tblEmp
(
ID INTIDENTITY(1,1)
,FirstName varchar(10)
,LastName varchar(10)
,JoiningDate datetime defaultgetdate()
,Salary numeric(10,2)
)
GO
INSERT INTO tblEmp (FirstName,LastName,Salary)
SELECT ‘Rushik’,‘Shah’,21000 UNION ALL
SELECT ‘Prapa’,‘Acharya’,21000 UNION ALL
SELECT ‘Kalpan’,‘Bhalsod’,35000 UNION ALL
SELECT ‘Ashish’,‘Patel’,18000 UNION ALL
SELECT ‘Hetal’,‘Shah’,18000
GO
SELECT * FROM tblEmp
GO
–solution given by new .NET developer
declare @maxsal float
set @maxsal = (select  max(salary) from tblEmp
where salary not in (select max(salary) from tblEmp))
select distinct salary fromtblEmp
where (salary != @maxsal) and (salary != (select max(salary) from tblEmp))
GO
–this could be easily achieved by Dense_Rank function
Select Salary FROM(
SELECT distinct Salary,dense_rank() over (order by salary desc) as rn FROM tblEmp
) as t where rn=3
GO
–even easy then windows partioning function
–like Dense_Rank
–especially this is not a SQL Server version specific query
SELECT top 1 Salary FROM
(
      select distincttop 3 salary fromtblEmp order bySalary
) as t
GO
You can compare all three different queries as performance point of view via execution plan.
Have fun!!!
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Understand SET STATISTICS IO as a first step for performance tuning in SQL Server

SET STATISTICS IO” provides us very crucial information about the query we run, like scan count, logical read, physical read etc. but this command is really very ignored. While it’s coming to query optimization, many newbie or less experience person used to talk about DTA, profiler etc. but they JUST IGNORE simple yet powerful command “SET STATISTICS IO” whereas they first need to look at the information provided by “SET STATISTICS IO” so that you can move ahead and dig more details with the information provided by “SET STATISTICS IO”.
Let us see how it practically useful for us.
We are going to create one database, named “SQLHub” and one table, named “Orders” under “SQLHub” database. “Orders” table would have approx 1,00,000 rows dynamically inserted.
–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
Now we are ready to see how query works. Let us create one simple “SELECT” T-SQL query with “SET STATICTICS IO”.
SET STATISTICS IO ON
–you might have different refno in your table, so please check it first
SELECT * from orders where refno=23
SET STATISTICS IO OFF
–here is the stats info came via “SET STATISTICS ON” in “Message” tab.
–(1982 row(s) affected)
–Table ‘orders’. Scan count 1, logical reads 409, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
After running this query, you would get its results in result tab and some stats in “Message” tab, look at the screen capture.

You can see “Logical Read 409”. To find out all rows which has refno 23, SQL Server has to go for 409 pages internally. Now let us create an Index on RefNo column and see stats.
–creating clustered index, however it is not meeting the criteria I have
–given in article, but this is ok for example
CREATE CLUSTERED INDEXidx_refno ON orders(refno)
GO
After creating “Clustered Index” on “Orders” table for “refno” column, let us see the same query.
SET STATISTICS IO ON
SELECT * from orders where refno=23
SET STATISTICS IO OFF
–here is the stats info came via “SET STATISTICS ON” in “Message” tab.
–(1982 row(s) affected)
–Table ‘orders’. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
GO

See screen capture of this query:

 

You can see now “Logical Read 14” rather than “409”, you see how much difference clustered index have made? And you don’t need to go to profiler to see this difference, even no need for execution plan in this case. However I am not against the use of profiler and execution plan. They both are very intelligent and useful tools but just wanted to clear it up that; we can get important information from simple command like “SET STATISTICS IO” too.
Apart from “Logical Read”, it provides you many more information in “Message” tab which you can see above like “Scan Count”, “Physical Read”, “Read-Ahead reads”, “log logical reads”, “lob physical reads” etc. you can get description of all these from Microsoft’s MSDN.
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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, 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 * FROMMemberPersonalDetail
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
EXECgetMemberPersonalDetail
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.
EXECgetMemberPersonalDetail
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

MERGE statement in SQL Server 2008 and later version

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 * FROMMemberPersonalDetail
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 * FROMMemberLoginDetail
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.
MERGEMemberLoginDetail 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 * FROMMemberLoginDetail
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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

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!!!!

[sourcecode language=”sql”]
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[/sourcecode]

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find unused index in SQL Server 2005/2008/Denali

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_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
–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_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 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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah