Non Clustered Index not working sql server

I have a table that doesn't have any primary key. data is already there. I have made a non clustered index. but when i 开发者_如何转开发run query, actual execution plan is not showing index scanning. I think non clustered index is not working. what could be the reason. Please Help Me

First of all - why isn't there a primary key?? If it doesn't have a primary key, it's not a table - just add one! That will help on so many levels....

Secondly: even if you have an index, SQL Server query optimizer will always look at your query to decide whether it makes sense to use the index (or not). If you select all columns, and a large portion of the rows, then using an index is pointless.

So things to avoid are:

• SELECT * FROM dbo.YourTable is almost guaranteed not to use any indices
• if you don't have a good WHERE clause in your query
• if your index is on a column that doesn't really select a small percentage of data; an index on a boolean column, or a Gender column with at most three different values doesn't help at all

Without knowing a lot more about your table structure, the data contained in those tables, the number of rows, and what kind of queries you're executing, no one can really answer your question - it's just way too broad....

Update: if you want to create a clustered index on a table which is different from your primary key, do these steps:

1) First, design your table 2) Then open up the index designer - create a new, clustered index on a column of your choice. Mind you - this is NOT the primary key !

3) After that, you can put your primary key on the ID column - it will create an index, but that index is not clustered !

Without having any more information I'd guess that the reason is that the table is too small for an index seek to be worth it.

If your table has less than a few thousand rows then SQL Server will almost always choose to do a table / index scan regardless of the indexes on that table simply because an index scan is in fact faster.

An index scan in itself doesn't necessarily indicate a performance problem - is the query actually slow?