4

I have the following table structure:

enter image description here

When I set the Id column as primary key it automatically created an index named PK_TestIndexTbl (Clustered) in the Indexes folder. My table contains about 1,300,000 records and when I execute the following query it take about 5 seconds:

SELECT [Id], [Name], [Family], [Score]
FROM   [TestIndexTbl]

But when I remove the primary key from table and of course it deletes the index I expect that my query takes more than 5 seconds because I haven't any index now. But it has no difference and I get the result in 5 seconds as before. Why?

My Question: How can I see the Index usage? What query should I run to see the difference with or without index here? Also the Where has no effect. The following query is executed less than one second with or without index:

SELECT [Id], [Name], [Family], [Score]
FROM   [TestIndexTbl]
where Id = 602145
Improve this question
6
  • To see the difference in access time with and without index use the where clause on id. Commented Mar 7, 2017 at 7:14
  • @Adil When I use where it returns the looking record very fast in both states. Commented Mar 7, 2017 at 7:15
  • Most probably the way data is arranged physically wound be kept as it was after deleting the the index which would be resulting fast retrieval even without index. Try to play around with non-clustered index to see the difference. Commented Mar 7, 2017 at 7:24
  • @Adil When I could get my favorite record very fast without index why I even should any clusterd or non-clustered index. I'm really confused. Commented Mar 7, 2017 at 7:27
  • @Adil And how can I get rids of cache and see the difference? By restarting My sql server? or restarting my OS? Or what? Commented Mar 7, 2017 at 7:29

4 Answers 4

Reset to default
4

Since you are selecting all of the entries in your DB, indexing doesn't matter. It takes the same time to get all of the books out of a library when they are stored in order and when they are not.

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

8 Comments

How can I see the Index usage? What query should I run to see the difference with or without index here?
Try specifying a WHERE clause, get an entry based on the primary key you selected. Something like SELECT * FROM TestIndexTbl WHERE id = 500000;
I did it already. When I use where it returns the looking record very fast in both states
Isn't it any faster? You might be shaving off just a couple of ms.
I have no index right now and removed PK and I have 1,300,000 records. But when I use where it retrieves the searching record immediately.
|
1

If you are going to select all data with no WHERE clause it will select all rows, a clustered index would have no impact on that. It might even be faster to select all rows without a clustered index as your not as likely to have fragmentation.

When your table is missing a clustered index its called a heap.

Try to select with a WHERE ID = @Id and you will se a major difference in speed between a table with a clustered index and one without.

For more information about the differences look here

Improve this answer

6 Comments

No. Check my updated question please. The Where has no effect. The query is executed less than one second with or without index when I use where.
@user5032790 use SET STATISTICS IO ON, SET STATISTICS TIME ON to measure difference, trust me there is a difference!
I have no index right now and removed PK and 1,300,000 records. But when I use where it retrieves the searching record immediately.
@user5032790 as i said add SET STATISTICS IO ON, SET STATISTICS TIME ON before your query and look in the message tab, it will show you how much data the sql server had to read.. in humans terms 1 second is a short amount of time but for a computer 1 second is a long time.. and my guess is that management studio rounds up to one second for every thing between 500-999 ms.
In the message tab I just see this (1 row(s) affected)
|
1

The index you mentioned before is related in how the data are saved. Basically an index in SQL Server is a B+ tree structure. When you declare a primary key structure, then a clustered index is created, which contains your primary key and has all the data stored in a B+ tree structure.

Basically, there are two kinds of indexes

  • Clustered
  • Non Clustered

A table can have only one at most Clustered index, while it can have more than one Non Clustered Indexes.

A table with a Clustered Index is called Clustered table, while a table without a clustered index is called Heap. There is a big difference in the way the data are stored in database's data files.

However, if your data aren't too many, whether you have a heap or a clustered it does make any difference.

In any case if you request all your data (SELECT * FROM ...) the time would be the same, since the operation that would be done would be a table scan. The execution engine of the sql server would have to visit all the data pages that contain your data. Therefore you will notice any difference.

If you run the following query:

SELECT [Id], [Name], [Family], [Score]
FROM   [TestIndexTbl]
WHERE Id = 602145

and you have a primary key then the operator that would be used to retrieve your data is an index seek on the clustered index. If you don't have a clustered index, then the operator would be a table scan. If you have a million or records then you will notice a difference in the retrieval of your data.

Improve this answer

4 Comments

I have no index right now and removed PK and 1,300,000 records. But when I use where it retrieves the searching record immediately.
drop and create your table (without a PK) from the start and give it a go. I think that you will not notice that you mentioned. I refer to the case with WHERE. Without a WHERE you will not notice any difference.
Are you sure about drop and recreate? And then how to insert 1,300,000 records into the new table again? Isn't there any other easy way to test it? how can I get rids of cache and see the difference? By restarting My sql server? or restarting my OS? Or what?
Sure you could try this: Execute DBCC FREEPROCCACHE and then this: DBCC DROPCLEANBUFFERS
0

After creating index do not hope that it will take effect automatically.. Index is waiting to call after executing second or more times take a look at the time executed it going to be change more faster than the first one.

See Index Type for more details

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.