CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-proceduresindexing
omkar patade
omkar patade

Reputation: 1591

How to use index in SQL query

Well i am new to this stuff ..I have created an index in my SP at start like follows

Create Index index_fab
ON TblFab (Fab_name)

Now i have query under this 

select fab_name from TblFab where artc = 'x' and atelr = 'y'.

now Is it necessary to use this index name in select clause or it will automatically used to speed up queries

Do i have to use something like 

select fab_name from TblFab WITH(INDEX(index_fab)) where artc = 'x' and atelr = 'y'.

or any other method to use this index in query

and also how to use index if we are using join on this table?

Upvotes: 8

Views: 61022

Answers (8)

curiousBoy
curiousBoy

Reputation: 6834

As the other people answered your question to help you to understand better, my opinion is, you should first understand why you need to use indexes. As we know that indexes increase the performance , they could also cause performance issues as well. Its better to know when you need to use indexes, why you need to use indexes instead of how to use indexes.

You can read almost every little detail from here .

Regarding your example, your query's index has no impact. Because it doesn't have the mentioned column in your query's where clause.

You can also try:

CREATE INDEX yourIndexName ON yourTableName (column_you_are_looking_for1,column_you_are_lookingfor2)

Also good to know: If no index exists on a table, a table scan must be performed for each table referenced in a database query. The larger the table, the longer a table scan takes because a table scan requires each table row to be accessed sequentially. Although a table scan might be more efficient for a complex query that requires most of the rows in a table, for a query that returns only some table rows an index scan can access table rows more efficiently. (source from here )

Hope this helps.

Upvotes: 4

Nrusingha
Nrusingha

Reputation: 21

For better performance: List out the columns /tables which are frequently used, Create index on those tables/columns only.

Upvotes: 2

Serge
Serge

Reputation: 6692

You're index use "Fab_name" column which you don't filter on in your select statement, so it's of no use.

Since you're new to this, you might benefit from an index like this :

Create Index index_fab
ON TblFab (artc, atelr)

or maybe like this

Create Index index_fab
ON TblFab (atelr, artc)

...yes there are a lot of subtleties to learn.

Upvotes: 2

Neville Kuyt
Neville Kuyt

Reputation: 29629

Firstly, do you mean you're creating the index in a stored procedure? That's a bad idea - if you run the stored procedure twice, it will fail because the index already exists.

Secondly, your query doesn't use the column mentioned in the index, so it will have no impact.

Thirdly, as JodyT writes, the query analyzer (SQL Server itself) will decide which index to use; it's almost certainly better at it than you are.

Finally, to speed up the query you mention, create an index on columns artc and atelr.

Upvotes: 12

DevelopmentIsMyPassion
DevelopmentIsMyPassion

Reputation: 3591

You dont need to include index in your query. Its managed by sql server. Also you dont need to include index in select if you want to make join to this table. Hope its clear.

Upvotes: 2

DrCopyPaste
DrCopyPaste

Reputation: 4117

An index should be used by default if you run a query against the table using it. But I think in the query you posted it will not be used, because you are not filtering your data by the column you created your index on. I think you would have to create the index for the artc and atelr columns to profit from that. To see wether your index is used take a look at the execution plan that was used in the SQL Management Studio.

more info on indices: use the index luke

Upvotes: 2

AdamL
AdamL

Reputation: 13151

If index is properly set up, optimizer will use it automatically. By properly set up, I mean that it's selective enough, can effectively help the query etc. Read about it. You can check by yourself if index is being used by using "include actual execution plan" option in ssms. It's generally not advised to use with(index()) hints and let optimizer decided by itself, except from very special cases when you just know better ;).

Upvotes: 1

JodyT
JodyT

Reputation: 4412

The Query Optimizer of SQL Server will decide if it the index is suitable for the query. You can't force it to use a specific index. You can give hints on which you want it to use but it won't be a guarantee that it will use it.

Upvotes: 3

Related Questions