聚类索引要不要加 order by

数据库要不要加order by给大家两个例子一看就知道了

CREATE TABLE Beatles

     (LastName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,

      FirstName varchar(20) NOT NULL UNIQUE NONCLUSTERED);

INSERT INTO Beatles (LastName, FirstName)

SELECT 'Lennon', 'John'

UNION ALL

SELECT 'McCartney', 'Paul'

UNION ALL

SELECT 'Harrison', 'George'

UNION ALL

SELECT 'Starr', 'Ringo';

SELECT LastName FROM Beatles;

DROP TABLE Beatles;

go

 

LastName

--------------------

Harrison

Lennon

McCartney
Starr

CREATE TABLE Stones

     (LastName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,

      FirstName varchar(20) NOT NULL UNIQUE NONCLUSTERED);

INSERT INTO Stones (LastName, FirstName)

SELECT 'Jagger', 'Mick'

UNION ALL

SELECT 'Jones', 'Brian'

UNION ALL

SELECT 'Richards', 'Keith'

UNION ALL

SELECT 'Watts', 'Charlie'

UNION ALL

SELECT 'Wyman', 'Bill';

SELECT LastName FROM Stones;

DROP TABLE Stones;

go

 

LastName

--------------------

Wyman

Jones

Watts

Richards

Jagger

第一个表没有问题，输出即使没加 order by 也能按照正常输出，但是第二个表却是乱序，为什么那？

答案就在这个执行计划里，所以，无论何时不要忽略优化器的选择，要不然诡异的现象就会时有发生，另外你都已经有了聚类索引，多加一个order by会死人啊？效率会低啊？我说我自己。

如果有想看英文的请进 英文的