2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mohan Sharma
  • 29
  • 1
  • 2
  • 2
    You need to provide more detail! There are several reasons why the query optimizer may decide to ignore the index (if the table is very small, if the index key used is deemed not selective enough, if the query includes additional predicates etc.) Without your showing the table structure (eg. create table script), the index creation script, the query and the query plan, and also without some statistics about the data, it will be very difficult to help you beyond general considerations. – mjv Dec 07 '10 at 06:41

2 Answers2

6

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 !

alt text

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

alt text

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1

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?

Justin
  • 84,773
  • 49
  • 224
  • 367
  • table has around 2000 rows. We can only make clustered index on primary key. but if i create index using table designer, there is an propert Create as Clustered. What is this? The Column on which i am making index is not primary... – Mohan Sharma Dec 07 '10 at 07:28
  • @Mohan [this link](http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx) is a reasonably a good introduction article on clustered indexes - in simple terms the clustered index determines what order the records are stored in. Note that the clustered index (tables may have at most one) and the primary key are completely different concepts and serve different purposes, however in 99.9% of cases the same columns are used for both. – Justin Dec 07 '10 at 07:36
  • @Mohan Also, 2000 rows probably is small enough to make a table scan / index scan the most efficient query plan (depending on the size of the columns) - unless the query is slow I wouldn't worry too much about it. – Justin Dec 07 '10 at 07:38
  • @Kragen thank you very much for your response. I want to send you print screen of my problem. How can i do this? – Mohan Sharma Dec 07 '10 at 08:33
  • @Mohan Sharma: no, you can put your clustered index on any column(s). It's on the primary key **by default**, but it doesn't have to be.... – marc_s Dec 07 '10 at 08:50
  • @marc_s thanks a lot. If i create index using table designer. there is a property 'Create as Clustered'. using this clustered column is made on non primary key. how is this possible? clustered index are made on primary columns only. – Mohan Sharma Dec 07 '10 at 09:10
  • @Mohan Sharma: **NO!** the clustered index can be created on any column - that's totally up to you. SQL Server will create a clustered index on the primary key (when you create a primary key) by default - but it **doesn't have to be that way**! You can totally create a clustered index on a separate column - just try it!! – marc_s Dec 07 '10 at 09:22
  • @marc_s thank you very much again and again. visit me at http://www.google.com/profiles/mail2mohanpyare – Mohan Sharma Dec 07 '10 at 10:15