Index Suggestion by Query Execution Plan - SQL Server 2008

I hope many of us we have start to use SQL Server 2oo8. SQL Server 2oo8 comes with many silent features. We all are familiar of execution plan of a query. We are getting visual display of query execution and cost of each action. Its very useful to understand how query will be executed.



Now let us see how its more improved in SQL Server 2oo8

In SQL Server 2oo8 it also suggest missing index and shows how much it will benefit after creating index.

Lets look at Example


CREATE TABLE testtable (

id INT,

id1 INT,

data VARCHAR(100))


DECLARE @id INT


SET @id = 1


WHILE @id <= 10000

BEGIN

INSERT INTO testtable

VALUES (@id,@id * 2,'data' + Convert(VARCHAR(10),@id))

SET @id = @id + 1

END


SELECT data,

Count(* )

FROM testtable

WHERE id IN (10,34,2452,234,

234,2344,2345,653,

345)

AND data LIKE '%4%'

GROUP BY data



Now press CTRL + L




Here it shows suggested index in green and also shows how much it will benefit us after creating query.Now press left click in menu there is option to create script for index.




After clicking on it, it will show us script of suggested index