How to use index in SQL query

Question

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?

Neville Kuyt · Accepted Answer · 2013-03-21 11:19:54Z

12

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.

answered Mar 21, 2013 at 11:19

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

curiousBoy · Accepted Answer · 2015-12-10 18:26:52Z

4

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.

edited Dec 10, 2015 at 18:26

answered Nov 4, 2013 at 20:50

curiousBoy

6,8846 gold badges52 silver badges60 bronze badges

2 Comments

user724198 Over a year ago

Sorry, former English teacher here. "Every little details" in paragraph II should read "every little detail". The noun premodifier "every" refers to its possessed noun as singular, therefore "details" is invalid in its plural form. HTH

curiousBoy Over a year ago

@Jason If that is the only mistake you could find as a former English teacher, I'm so glad as an ESL speaker/writer :D

JodyT · Accepted Answer · 2013-03-21 11:14:58Z

3

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.

answered Mar 21, 2013 at 11:14

JodyT

4,4222 gold badges22 silver badges31 bronze badges

Comments

DrCopyPaste · Accepted Answer · 2013-03-21 11:19:22Z

2

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

answered Mar 21, 2013 at 11:19

DrCopyPaste

4,1552 gold badges24 silver badges58 bronze badges

Comments

DevelopmentIsMyPassion · Accepted Answer · 2013-03-21 11:19:33Z

2

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.

answered Mar 21, 2013 at 11:19

DevelopmentIsMyPassion

3,6014 gold badges36 silver badges61 bronze badges

Comments

Serge · Accepted Answer · 2013-03-21 11:24:12Z

2

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.

answered Mar 21, 2013 at 11:24

Serge

6,7425 gold badges33 silver badges58 bronze badges

Comments

Nrusingha · Accepted Answer · 2013-10-15 07:59:27Z

2

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

answered Oct 15, 2013 at 7:59

Nrusingha

211 bronze badge

Comments

AdamL · Accepted Answer · 2013-03-21 11:18:10Z

1

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

answered Mar 21, 2013 at 11:18

AdamL

13.3k5 gold badges57 silver badges79 bronze badges

Collectives™ on Stack Overflow

How to use index in SQL query

8 Answers 8

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related