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


SET @id = 1

WHILE @id <= 10000


INSERT INTO testtable

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

SET @id = @id + 1


SELECT data,

Count(* )

FROM testtable

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



AND data LIKE '%4%'


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

No comments:

Post a Comment

Popular Posts