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?

Solution 1

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.

Solution 2

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.

Solution 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.

Solution 4

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

Solution 5

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

View more solutions

Comments

[Admin]

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]

@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