How to use an index in a SQL query

Question

Well, I'm new to this stuff .. I created an index in my SP at the beginning, as follows

Create Index index_fab ON TblFab (Fab_name) 

Now I have a request under this

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

Now you need to use this index name in the select clause or it will be automatically used to speed up queries

Do I need to use something like

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

or any other method of using this index in a query

as well as how to use the index if we use the join in this table?

Answer 1

First, do you mean that you are creating an index in a stored procedure? This is a bad idea - if you run the stored procedure twice, it will fail because the index already exists.

Secondly, your query does not use the column specified in the index, so it will have no effect.

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

Finally, to speed up the query, create an index for the artc and atelr columns.

Answer 2

The SQL Server Query Optimizer will decide if this index is appropriate for the query. You cannot use force to use a specific index. You can give tips on which you want to use it, but this will not guarantee that he will use it.

Answer 3

The index should be used by default if you are using a table query using it. But I think that in the query that you published, it will not be used because you are not filtering your data by the column on which your index was created. I think you will need to create an index for the artc and atelr columns in order to profit from this. To see how your index is being used, take a look at the execution plan used by SQL Management Studio.

Additional Index Information : Use the luke Index

Answer 4

You do not need to include the index in your query. It is managed by sql server. Also, you do not need to include the index in select if you want to make a join to this table. Hope this is understandable.

Answer 5

The FileName column uses an index that you do not filter in your select statement, so it is not needed.

Since you are new to this, you can use 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 many subtleties to study.

Answer 6

For best performance: List of frequently used columns / tables. Create an index for these tables / columns only.

Answer 7

How other people answered your question to help you better understand, in my opinion, you should first understand why you need to use indexes. Since we know that indexes improve performance, they can also cause performance problems. It is better to know when you need to use indexes, why you need to use indexes instead of using indexes.

You can read almost every little thing from here .

As for your example, your query index is not affected. Because it does not have the column mentioned in your query where clause.

You can also try:

CREATE INDEX yourIndexName ON yourTableName (column_you_are_looking_for1,column_you_are_lookingfor2 )

It is also useful to know: if the index does not exist in the table, a table scan should be performed for each table referenced by the database query. The larger the table, the longer the table scan takes place, because the table scan requires each row of the table to be accessed sequentially. Although a table scan can be more efficient for a complex query that requires most of the rows in the table, for a query that returns only some rows of the table, index scanning can more effectively access the rows of the table. (source here )

Hope this helps.

Answer 8

If the index is configured correctly, the optimizer will use it automatically. Correctly configured, I mean that he is quite selective, can effectively help the request, etc. Read about it. You can check it yourself if the index is used using the "include actual execution plan" option in ssms. Usually it is not recommended to use hints (index ()), and let the optimizer decide on his own, except in special cases when you only know better;).