CREATE TABLE [dbo].[ExamTest](
	[Col1] [int] NOT NULL,
	[Col2] [int] NOT NULL,
CONSTRAINT [PK_ExamTest] PRIMARY KEY CLUSTERED 
(
	[Col1] ASC,
	[Col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_ExamTest] ON [dbo].[ExamTest]
(
	[Col2] ASC,
	[Col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

INSERT INTO dbo.ExamTest
(Col1, Col2)
VALUES
(CAST(RAND()*1000 as int), CAST(RAND()*1000 as int))
GO 20000

SELECT 
	COUNT(*), Col1, Col2
FROM
	dbo.ExamTest WITH(INDEX(PK_ExamTest))
GROUP BY
	Col1, Col2;

SELECT 
	COUNT(*), Col2, Col1
FROM
	dbo.ExamTest
GROUP BY
	Col2, Col1;

quote:

---

The first off thing that I run into is the fact that if you simply do a select * from this table, it uses the IX index, not the PK. The results will be ordered by the IX.

Why? Why doesn't the optimizer use the PK by default?

---

quote:

---

Originally posted by TG

Two things you may not be considering:
1) set statistics io on for both and you may see that the clustered index requires more reads (for the same amount of data).
2) the non-clustered index covers all needed data. Sql server does not require the table to perform the query. All it needs to scan is the nonclustered index.

Be One with the Optimizer
TG

---

quote:

---

Originally posted by Sachin.Nand

For this part of your question I need to know whether you have any knowledge on IAM pages ?

If no then I think I will be wasting my time explaining why the optimizer went for a unordered scan and not an ordered scan.

---

quote:

---

Originally posted by namman

Could you post the full test question, malachi151?

---

quote:

---

Originally posted by malachi151

quote:

---

Originally posted by Sachin.Nand

For this part of your question I need to know whether you have any knowledge on IAM pages ?

If no then I think I will be wasting my time explaining why the optimizer went for a unordered scan and not an ordered scan.

---

Index Allocation Maps, yeah. Actually I'm reading the Query Tuning chapter in Inside SQL Server: T-SQL Querying by Itzik Ben-Gan right now, which covers the topic.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

---