Contents

1 SQL Server 2000 Group By 覓伎^蟇 讌
2 ろ 譴觜
3 SQL Server 2005
4 蟆磯
5 谿瑚襭


SQL Server 7.0 Group By 覓伎^蟇 一一 . 讌襷 2000覯 覓伎^蟇 讌 朱襦 蠎 覈伎 . 2005覯 覿覿 覦. (2008.08.15) 2008 覯 豢 伎 覿覿 伎螳 讌 覈襯願.

1 SQL Server 2000 Group By 覓伎^蟇 讌 #

MSSQL Server 2000 牛磯伎 7.0 牛磯伎 蠏碁襦 磯殊 蟆 . 螳 襷 朱 蟆螻 蟆 覦襦 GROUP BY 覿企. 蟆磯覿 襷覃 Group By 蠍磯 螻, 讌 . 覃 牛磯伎螳 磯殊 企 一一 螳 れ蠍 覓語企. 覓朱 ろ 一一 り る 覈襯 讌 .

USE NORTHWIND
GO
 
SELECT CUSTOMERID FROM ORDERS
GROUP BY CUSTOMERID
ordered01.jpg

蟆郁骸襯 覲企 Stream Aggregate 一一 . 蟆郁骸襯 覲企 , 願 蠏碁9一一 蠍 覓語企. 襷 CustomerID 碁煙り る 讀, 碁煙るゼ 讌 朱 磯 觜 蠍企.

SELECT CUSTOMERID FROM ORDERS (INDEX = 0)
GROUP BY CUSTOMERID
ordered02.jpg

蠏碁 れ螻 螳 貎朱Μ襯 蟆 覃 Stream Aggregate 伎螻 Hash Match 一一 蟆 .
SELECT CUSTOMERID, SUM(FREIGHT)
FROM ORDERS
GROUP BY CUSTOMERID
ordered03.jpg

蟆郁骸襯 覲企 蟆讌襷 蟆郁骸螳 讌 . 蠏碁覩襦 MSSQL Server 2000 伎 覯 Group By襯 り 伎 覦 蟆 覩襦 Order By襯 覈伎 . 覓朱 碁煙るゼ MSSQL Server螳 伎. 碁煙るゼ 伎 覦レ朱 曙 蟆語 覦レ朱 曙 蟆語 Order By 覈 襦 讌碁. 企 蟆 覲企 蟒 .

2 ろ 譴觜 #

朱 ろ碁ゼ 譴觜襯 .

USE AdventureWorks2008;

IF OBJECT_ID('dbo.TEST') IS NOT NULL
	DROP TABLE dbo.TEST;
GO

CREATE TABLE dbo.TEST
(
	CustID varchar(20)
,	NM varchar(20)
,	Dumy varchar(50)
,	RegDT datetime
);

CREATE CLUSTERED INDEX CIX ON dbo.TEST(RegDT);
CREATE INDEX NIX ON dbo.TEST(CustID);

螳螳 語1,2,3 れ ろ襴渚碁ゼ 1覿螳 ろ.
--Session 1
USE AdventureWorks2008;

SET NOCOUNT ON 
SET STATISTICS IO OFF

WHILE(1=1)
BEGIN
	INSERT dbo.TEST 
	SELECT
		LEFT(NEWID(),20)
	,	LEFT(NEWID(),20)
	,	NEWID()
	,	GETDATE()
	FROM Person.Person
END

--Session 2
USE AdventureWorks2008;

SET NOCOUNT ON 
SET STATISTICS IO OFF

WHILE(1=1)
BEGIN
	INSERT dbo.TEST 
	SELECT
		LEFT(NEWID(),20)
	,	LEFT(NEWID(),20)
	,	NEWID()
	,	GETDATE()
	FROM Person.Person
END

--Session 3
USE AdventureWorks2008;

SET NOCOUNT ON 
SET STATISTICS IO OFF

WHILE(1=1)
BEGIN
	INSERT dbo.TEST 
	SELECT
		LEFT(NEWID(),20)
	,	LEFT(NEWID(),20)
	,	NEWID()
	,	GETDATE()
	FROM Person.Person
END

3 SQL Server 2005 #

SQL Server 2005 覦 所 覦. SQL Server 2000 危覯 蠍磯蓋朱 企 企ろ磯 碁煙り 朱 企ろ磯 碁煙るゼ 伎 れ . 企ろ磯 碁煙り 企 る IAM(Index Allocation Map) 伎 れ . 伎 SQL Server れ螻 螳 2螳讌 覦 れ . 2螳讌? れ覦 磯ジ 焔レ姶願 譟伎蠍 覓語企.

  • Index-Order Scan
  • Allocation-Order Scan

螳螳 企 覦讌 危エ覲伎.

SELECT TOP 10000 CustID FROM dbo.TEST
ordered01_1.jpg

貎朱Μ 蟆郁骸 碁煙れ 螻朱 覓願. 蠏碁覩襦 Ordered:False企.襷 ORDER BY CustID螳 豢螳螳 る Ordered:True螳 蟆企. 讀, 碁煙るゼ 伎 襦 .

SELECT TOP 10000 * FROM dbo.TEST(NOLOCK)
--Table 'TEST'. Scan count 1, logical reads 136, physical reads 0
ordered02_1.jpg

覯讌 貎朱Μ 碁煙れ 螻朱 覓願り SQL Server . 企ろ磯 碁煙れ 襦 TOP 10000 螳 蟆郁骸襦 覦 覲 蟆 讌襷 SQL Server NOLOCK 碁 螳 觜襯 蟆郁骸襯 覦 覲 れ覦 "Allocation-Order Scan" .

SELECT TOP 10000 * FROM dbo.TEST
--Table 'TEST'. Scan count 1, logical reads 144, physical reads 0
ordered03_1.jpg

覯讌 貎朱Μ 襷る誤. れ襦 一危磯ゼ 誤企慨 企ろ磯 碁煙るゼ 伎 覦 覿蟲螻 Ordered:False襦. 伎 伎 企 覓語 谿場 覈詩.

SELECT TOP 10000 * FROM dbo.TEST ORDER BY RegDT
--Table 'TEST'. Scan count 1, logical reads 144, physical reads 0
ordered04.jpg

覯讌 貎朱Μ ORDER BY RegDT襯 覈朱襦 SQL Server 碁煙るゼ 伎 り . 蠏碁 Ordered:True襦. Ordered:False Ordered:True れ 一危磯ゼ 觜蟲企慨.

SELECT TOP 10000 * FROM dbo.TEST(NOLOCK)
SELECT TOP 10000 * FROM dbo.TEST ORDER BY RegDT
--Table 'TEST'. Scan count 1, logical reads 136, physical reads 0
--Table 'TEST'. Scan count 1, logical reads 144, physical reads 0
ordered05.jpg

NOLOCK 碁ゼ 譴 "Allocation-Order Scan" 覦(Ordered:False)朱 れ 螻, ORDER BY RegDT襯 覈 貎朱Μ "Index-Order Scan" 覦(Ordered:True)朱 れ . "Allocation-Order Scan" 螻朱 覓願覩襦 貎朱Μ 蟆郁骸 蟆郁骸 襴蟆 る 蟆 誤 . 螳 譬朱 貎朱Μ 蟆郁骸 螳 . IAM螻 企ろ磯 碁煙れ 朱Μ 郁屋 (伎 襷 襴ろ )螳 螳 蟆曙郁 螳 蟆郁骸襯 覲 蟆曙磯.

4 蟆磯 #

貎朱Μ 蟆郁骸 覈 狩. 讌襷 SQL Server 2005覯覿磯 殊. 覓伎^蟇 Index-Order Scan 蟆 覩襦 蟆郁骸襯 る 覦 ORDER BY襯 覈伎 . 企ゼ 覓伎る 麹 蟆郁骸螳 朱 譯殊伎 .

5 谿瑚襭 #

SQL Server 2005 覲蟆曙 SQL Server Read-Consistency Problems, Part 1 (SQL襷り碓讌, Itzik Ben-Gan)襯 谿瑚給. Ordered 伎 れ狩蟷 譯殊給.