SQL Server : clustered and nonclustered indexing

Question

I am trying to get the difference between clustered and nonclustered indexes. I want to show that this indexes place rows in different order in table. But my query always shows the same result:

CREATE TABLE test_table (ID tinyint)
GO

INSERT INTO test_table VALUES (2), (1), (3)

--CREATE UNIQUE CLUSTERED INDEX Clustered_Index
CREATE INDEX Nonclustered_Index
ON test_table (ID);
GO

SELECT *
FROM test_table;
GO

DROP TABLE test_table;
GO

What I have to do to fix this difference?

@David, I read articles about indexes and I want to find if is it possible to show that order of rows is different in this two cases using SELECT query — user3443227, Mar 20 '14 at 17:40
It might be easier to create a two column table, with a clustered index on column 1, and a non-clustered index on on column 2. That will be a better approximation of real table structure. You can then drop and re-create indexes and switch the clustered/non-clustered column to show reordering. — Mike Gardner, Mar 20 '14 at 17:51

score 2 · Answer 1 · answered Mar 20 '14 at 17:48

I see what you're trying to demonstrate, but I don't really see why. And I think the reason for that is probably why the demonstration isn't working.

A clustered index determines the order in which records are stored on the disk. However, it does not guarantee that any given query is going to return records in that exact order every time. They coincidentally might be returned in that order (for example, if you inserted them in that order after having specified the index during table creation, which happens in most cases), but it's not guaranteed.

A SELECT operation should specify the order in which data is to be returned if that order is supposed to be important. (And database engines are really good at optimizing that based on the indexes.) Don't worry about the order on the disk, the clustered index takes care of that. Barring those two things, order is not guaranteed. It's often coincidentally ordered as expected, but not guaranteed.

score 1 · Accepted Answer · answered Mar 20 '14 at 18:00

When you create a non-clustered index on a table, it is a pointer to the clustered index, if one exists. However if one does not, then the data in the table will be ordered by the non_clustered index. The only way I can think to illustrate this to have two indexes on the table, and show the query order by having them both be unclustered, and then make one clustered, and then make the other clustered (note you can only have one clustered index):

CREATE TABLE test_table (ID tinyint, ID2 tinyint);
GO

---CREATE NONCLUSTERED INDEX non_clustered_Indexa
CREATE INDEX non_clustered_Indexa
ON test_table (ID);

--CREATE NONCLUSTERED INDEX non_clustered_Indexb
CREATE INDEX non_clustered_Indexb
ON test_table (ID2);

INSERT INTO test_table VALUES (2, 1);
INSERT INTO test_table VALUES (1, 2);
INSERT INTO test_table VALUES (3, 3);
GO

SELECT * FROM test_table;
GO

SQL Server : clustered and nonclustered indexing

2 Answers2