Using SQL Server DMVs to Identify Missing Indexes

Problem
If I know which indexes I can drop, what is the process to identify which indexes I need to create.  I can run the Database Tuning Advisor or examine the query plans, but is there any easier way to determine which indexes may be needed?
Solution
As with a lot of the new features we have seen with SQL Server 2005 and now with SQL Server 2008, Microsoft introduced a few more dynamic management views to also assist with identifying possible index candidates based on query history.
The dynamic management views are:

• sys.dm_db_missing_index_details - Returns detailed information about a missing index
• sys.dm_db_missing_index_group_stats  - Returns summary information about missing index groups
• sys.dm_db_missing_index_groups - Returns information about a specific group of missing indexes
• sys.dm_db_missing_index_columns(index_handle) - Returns information about the database table columns that are missing for an index. This is a function and requires the index_handle to be passed.

Like most of the statistics that are tracked for the DMVs, these basically work the same where once the instance of SQL Server is restarted the data gets cleared out.  So if you are working in a test environment and restart your instance of SQL Server these views will probably return no data.

---

To get started we are going to use an example from SQL Server 2005 Books Online, that queries a table from the AdventureWorks database where there is no index on the StateProvinceID as shown below.

USE AdventureWorks; GO SELECT City, StateProvinceID, PostalCode FROM Person.Address WHERE StateProvinceID = 1; GO

Once we have run the above query, data should now be available in our management views.  Let's take a quick look at each of these.
The first query gets data from the sys.dm_db_missing_index_details view.  This is probably the most helpful, since this shows us the object_id and the equality_columns and the inequality_columns. In addition we get some other details about included columns. 

SELECT * FROM sys.dm_db_missing_index_details

So for the query we ran above, we can see the following:

• equality_columns = "StateProvinceID", this is because this column is used in the WHERE clause with an equals operator.  So SQL Server is telling us this would be a good candidate for an index.
• inequality_columns = "NULL", this column will have data if you use other operators such as not equal, but since we are using equals there are no columns that could be used here 
• included_columns = this is additional columns that could be used when the index is created.  Since the query only uses City, StateProvinceID and PostalCode, the StateProvinceID will be handled in the index and the other two columns could be used as included columns when the index is created.  Take a look at this tip for more information about included columns.

---

The next query gets data from sys.dm_db_misssing_index_group_stats.  This query gives us additional insight into other stats such as compiles, user seeks, user scans etc...  So from here we can tell how often this query is being called.  This will help us to determine how much use an index may get if we do create a new index based on this information.

SELECT * FROM sys.dm_db_missing_index_group_stats

Since we only ran this query one time, our unique_compiles = 1 and our user_seeks = 1.  If we run this again, our user_seeks should increment.. 

---

The next view, sys.dm_db_missing_index_groups gives us information about the index_group_handle and the index_handle. 

SELECT * FROM sys.dm_db_missing_index_groups

These output from the above query is basically used to get data from sys.dm_db_missing_index_columns function.  The index_handle value is passed on to the next query as shown below.

SELECT * FROM sys.dm_db_missing_index_columns(1)

---

To get all of the data displayed in one result set, the following query from SQL Server 2005 Books Online gives us this data.

SELECT mig.*, statement AS table_name, column_id, column_name, column_usage FROM sys.dm_db_missing_index_details AS mid CROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle ORDER BY mig.index_group_handle, mig.index_handle, column_id;

---

Summary

• Based on this one example we can see that we can create a new index on table AdventureWorks.Person.Address on column StateProvinceID and also include columns City and PostalCode.
• One thing to note is that when you add or drop indexes on a table all stats for missing indexes get cleared for this table.
• Although this may not be perfect and there are some limitations, this does at least give us some additional insight we never had before with prior versions of SQL Server.

Next Steps

• This may not be an optimal approach for managing your indexes, but this does give you some additional insight as to what is occurring and what indexes may be helpful.  Take a look at these views to see if you can identify some additional indexes that may be useful.
• Although it would be nice if this gave you the definitive answer on how and what to create, it still takes time to understand your applications and manage your indexes
• For a list of limitations for these new views, take a look at this article
• To view a list of unused indexes take a look at this tip: Deeper insight into unused indexes for SQL Server

How to Use Pivot table concept in SQL.

Unit_id	Unit_price	Abbr
1	250	S
1	300	A
1	350	T

DROP TABLE TestTable
GO
CREATE TABLE TestTable
(
ID INT IDENTITY(1,1) PRIMARY KEY,
UNIT INT,
Price INT,
Abbr VARCHAR(1),
)
GO
INSERT TestTable
SELECT 1,250,'s' UNION
SELECT 1,300,'a' UNION
SELECT 1,350,'t'
GO
SELECT * FROM TestTable
GO


Display Result as:

Unit_id	S	A	T
1	250	300	350

“I do not use Transactions in SQL as I use Stored Procedure“.

I just realized that there are so many misconceptions about this subject. Transactions has nothing to do with Stored Procedures. Let me demonstrate that with a simple example.

USE tempdb
GO
-- Create 3 Test Tables
CREATE TABLE TABLE1 (ID INT);
CREATE TABLE TABLE2 (ID INT);
CREATE TABLE TABLE3 (ID INT);
GO
-- Create SP
CREATE PROCEDURE TestSP
AS
INSERT INTO TABLE1 (ID)
VALUES (1)
INSERT INTO TABLE2 (ID)
VALUES ('a')
INSERT INTO TABLE3 (ID)
VALUES (3)
GO
-- Execute SP
-- SP will error out
EXEC TestSP
GO
-- Check the Values in Table
SELECT *
FROM TABLE1;
SELECT *
FROM TABLE2;
SELECT *
FROM TABLE3;
GO

Now, the main point is: If Stored Procedure is transactional then, it should roll back complete transactions when it encounters any errors. Well, that does not happen in this case, which proves that Stored Procedure does not only provide just the transactional feature to a batch of T-SQL.

Let’s see the result very quickly.

It is very clear that there were entries in table1 which are not shown in the subsequent tables. If SP was transactional in terms of T-SQL Query Batches, there would be no entries in any of the tables. If you want to use Transactions with Stored Procedure, wrap the code around with BEGIN TRAN and COMMIT TRAN.

The example is as following.
CREATE PROCEDURE TestSPTran
AS
BEGIN TRAN
INSERT INTO TABLE1 (ID)
VALUES (11)
INSERT INTO TABLE2 (ID)
VALUES ('b')
INSERT INTO TABLE3 (ID)
VALUES (33)
COMMIT
GO
-- Execute SP
EXEC TestSPTran
GO
-- Check the Values in Tables
SELECT *
FROM TABLE1;
SELECT *
FROM TABLE2;
SELECT *
FROM TABLE3;
GO
-- Clean up
DROP TABLE Table1
DROP TABLE Table2
DROP TABLE Table3
GO

Error Converting Data Type Money to Decimal

Run following script and you will see that it will thrown an error.
DECLARE @mymoney MONEY;
SET @mymoney = 12345.67;
SELECT CAST(@mymoney AS DECIMAL(5,2)) MoneyInt;
GO

The datatype of money is also visually look similar to the decimal, 

why it would throw following error:

Msg 8115, Level 16, State 8, Line 3
Arithmetic overflow error converting money to data type numeric.

Solution:

DECLARE @mymoney MONEY;
SET @mymoney = 12345.67;
SELECT CAST(@mymoney AS DECIMAL(7,2)) MoneyInt;



 
Explanation: 
you have 7 digits in @mymoney (including decimals) 
5, 2 means that it’s 5 digits in total with 2 after the decimal point. 
To represent the number you show, it needs a total of 7 with 2 
after the decimal place and 5 before.

Problem while using MAX function

create table emp
(e_id nvarchar(50) primary key,
ename varchar(50))

e_id | ename
a1 x
a2 y
a.. n..
a15 z

select max(e_id) from emp

output :
a9

you can notice that above out put is wrong
the answer as to be a15….

Problem:
You will get an insight by running this query:
SELECT e_id from Emp ORDER BY e_id

You will see that e_id is being sorted by the character’s sequence and not like numbers. So, a10 will be followed by a1.
So, as per sort logic, a9 is the max value.

Answer:

SELECT LEFT(e_id, 1) + CAST(MAX(CAST(SUBSTRING(e_id, 2, LEN(e_id)) AS INT)) AS NVARCHAR(50)) FROM Emp GROUP BY LEFT(e_id, 1)

Please note that this query is based on the first character of the e_id column values. If let say someone inserts another 15 records starting from b1 , x to b15, z then the output of this query will be of 2 rows:
a15
b15

Warm Regards,

Siddhesh

SQL SERVER – What is Denali?

“What is Denali?”

Denali is code name of SQL Server 2011.

Here is the list of the code name of other versions of SQL Server.

In 1988, Microsoft released its first version of SQL Server. It was developed jointly by Microsoft and Sybase for the OS/2 platform.

• 1993 – SQL Server 4.21 for Windows NT
• 1995 – SQL Server 6.0, codenamed SQL95
• 1996 – SQL Server 6.5, codenamed Hydra
• 1999 – SQL Server 7.0, codenamed Sphinx
• 1999 – SQL Server 7.0 OLAP, codenamed Plato
• 2000 – SQL Server 2000 32-bit, codenamed Shiloh (version 8.0)
• 2003 – SQL Server 2000 64-bit, codenamed Liberty
• 2005 – SQL Server 2005, codenamed Yukon (version 9.0)
• 2008 – SQL Server 2008, codenamed Katmai (version 10.0)
• 2010 – SQL Server 2008 R2, Codenamed Kilimanjaro (aka KJ)
• Next – SQL Server 2011, Codenamed Denali

Any guess' what should be the next version after 2011 should be codenamed?

SQL Server - Select All Rows rather than Select TOP 1000

When you right Click on a table, you simply find the first option Select TOP 1000 rows.

But you can change it to ALL rows i.e. all the rows will be displayed without limitations.

Just follow these steps.

GoTo:

Make sure that SQL Management studio is open.

Then goto Tools --> Options --> SQL Server Object Explorer

Same as in image.

Then select Commands in it

& then set all the values 0

Click on OK

That's it !!