MS Support Script for Creating Missed Indexes in all Tables

PRINT 'Missing Indexes: ' 

PRINT 

'The "improvement_measure" column is an indicator of the (estimated) improvement that might '

PRINT 

'be seen if the index was created. This is a unitless number, and has meaning only relative '

PRINT 

'the same number for other indexes. The measure is a combination of the avg_total_user_cost, '

PRINT 

'avg_user_impact, user_seeks, and user_scans columns in sys.dm_db_missing_index_group_stats.'

PRINT '' 

PRINT '-- Missing Indexes --' 

SELECT CONVERT (VARCHAR, Getdate(), 126)                              AS runtime 

       , 

       mig.index_group_handle, 

       mid.index_handle, 

       CONVERT (DECIMAL (28, 1), migs.avg_total_user_cost * migs.avg_user_impact 

                                 * ( 

                                 migs.user_seeks + migs.user_scans )) AS 

       improvement_measure, 

       'CREATE INDEX missing_index_' 

       + CONVERT (VARCHAR, mig.index_group_handle) 

       + '_' + CONVERT (VARCHAR, mid.index_handle) 

       + ' ON ' + mid.statement + ' (' 

       + Isnull (mid.equality_columns, '') + CASE WHEN mid.equality_columns IS 

       NOT NULL 

       AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 

       + Isnull (mid.inequality_columns, '') + ')' 

       + Isnull (' INCLUDE (' + mid.included_columns + ')', '')       AS 

       create_index_statement, 

       migs.*, 

       mid.database_id, 

       mid.[object_id] 

FROM   sys.dm_db_missing_index_groups mig 

       INNER JOIN sys.dm_db_missing_index_group_stats migs 

               ON migs.group_handle = mig.index_group_handle 

       INNER JOIN sys.dm_db_missing_index_details mid 

               ON mig.index_handle = mid.index_handle 

WHERE  CONVERT (DECIMAL (28, 1), migs.avg_total_user_cost * migs.avg_user_impact 

                                 * ( 

                                        migs.user_seeks + migs.user_scans )) > 

       10 

ORDER  BY migs.avg_total_user_cost * migs.avg_user_impact * ( 

                    migs.user_seeks + migs.user_scans ) DESC 

PRINT '' 

go 

How to Select the Best Women

SELECT
ThePerfectWoman

FROM   AllTheWomenInTheWorld

WHERE  HerInterests
LIKE Mine

      
AND HerMate IS
NULL

      
AND LikesToLive IN (
'Where',
'Ever',
'I',
'Want',
'to',
'live' )

GROUP  BY
CASE

         
WHEN EverMyFeelings>=Sad

                
THEN SheCanMakeMeHappy
ELSE IwillMakeHerHappy
* 2

        
END

HAVING Max(Attitude)
> Good

MS SQL CTE wonders Display 1 to 100 without Looping

I happened to solve a puzzle.
Display 1 to 100 in SSMS without using any loop or table or table variable or even cursor.

I gave up, then got the solution from requester.

WITH cte 

     AS (SELECT 1 Num          UNION ALL          SELECT num + 1          FROM   cte          WHERE  num < 100) SELECT * FROM   cte 


Always wonder with common table expressions. happy querying

Use of #tmp in Staging tables

there were thousands of records in one staging table., what we want is to keep only one record in it and to test one huge query
Two possible ways
1. copy and paste one record in notepad / clipboard., then delete or truncate entire staging table and insert the copied record into staging --> this looks traditional and time consuming
2. here is my way.

SELECT TOP 1 into #tmp 
FROM   [staging table] 

TRUNCATE TABLE [staging table] 

INSERT INTO [staging table] 
SELECT * 
FROM   #tmp 

Cheers,

Vivek

Beware of Timestamp in Where Clause

I have had to delete records for few dates., I tried the below query and deleted

Select * from [Some Table] where UPDATED_ON_DT = '01-Jan-2011'

but it has deleted rows only for '01-Jan-2011 00 00 000' and left other records
so, the below query solved my issue
Select * from [Some Table] where convert(Date,UPDATED_ON_DT) = '01-Jan-2011'