Database Reference
In-Depth Information
CREATE TABLE dbo.Test1
(
C1 INT,
C2 INT,
C3 INT,
c4 CHAR(2000)
);
CREATE CLUSTERED INDEX i1 ON dbo.Test1 (C1);
WITH Nums
AS (SELECT TOP (10000)
ROW_NUMBER() OVER (ORDER BY (SELECT 1
)) AS n
FROM master.sys.All_Columns ac1
CROSS JOIN master.sys.All_Columns ac2
)
INSERT INTO dbo.Test1
(C1, C2, C3, c4)
SELECT n,
n,
n,
'a'
FROM Nums;
WITH Nums
AS (SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM Nums
WHERE n < 100
)
INSERT INTO dbo.Test1
(C1, C2, C3, c4)
SELECT 41 - n,
n,
n,
'a'
FROM Nums;
To determine the number of logical reads required to retrieve a small result set and a large result set from this
fragmented table, execute the two
SELECT
statements with
STATISTICS IO
and
TIME
set to
ON
:
--Reads 6 rows
SELECT *
FROM dbo.Test1
WHERE C1 BETWEEN 21 AND 23;
--Reads all rows
SELECT *
FROM dbo.Test1
WHERE C1 BETWEEN 1 AND 10000;
