|
|
Posted by Hugo Kornelis on 05/29/06 00:22
On 28 May 2006 03:12:03 -0700, David Portas wrote:
>Ralph Ganszky wrote:
>>
>> How do you know that the OP asked exactly what you expect he have asked? Are
>> you sure that he asked what you think/want he has asked? What I meant is
>> exactly what he asked from my point of view. He asked if it is possible to
>> insert in between two other records. A clustered index does exactly this on
>> row level. It has on the other side nothing to do with the order in the
>> result set as you mentioned. But the answer is true any way for the physical
>> representation on the disk.
>
>Only partly right. A clustered index will give rows a positional order
>that can be reconstructed internally in the clustered index. However,
>that order is normally inaccessible to the user. Your statement that
>this is a physical order on disk is misleading. There need not be any
>such physical ordering and even if it exists initially there is no
>guarantee it will be maintained by a clustered index.
Hi David,
Exactly. I checked this using the following script:
USE TestDB90
-- Create table
CREATE TABLE HH
(hhno int NOT NULL PRIMARY KEY CLUSTERED,
hhname varchar(40) NOT NULL)
go
-- Insert initial population
INSERT INTO HH (hhno, hhname)
SELECT 100, 'suresh'
UNION ALL
SELECT 101, 'baba'
UNION ALL
SELECT 103, 'ram'
go
-- Attempt to add row "in between" other rows
INSERT INTO HH (hhno, hhname)
SELECT 102, 'chandra'
go
-- Get DBCC PAGE output to query window instead of system log
DBCC TRACEON(3604)
go
-- Find page in DB file where table data is stored
SELECT first FROM sysindexes WHERE id = OBJECT_ID('HH')
go
-- Check page (calculate page number from previous query output
DBCC PAGE(TestDB90, 1, 208, 2)
go
And here are some snippets from the output:
609EC060: 30000800 64000000 0200fc01 00150073 0...d..........s
609EC070: 75726573 68300008 00650000 000200fc uresh0...e......
609EC080: 01001300 62616261 30000800 67000000 ....baba0...g...
609EC090: 0200fc01 00120072 616d3000 08006600 .......ram0...f.
609EC0A0: 00000200 fc010016 00636861 6e647261 .........chandra
609EC0B0: 00000000 00000000 00000000 00000000 ................
(...)
609EDFF0: 01000000 381d0000 88009a00 75006000 ....8.......u.`.
OFFSET TABLE:
Row - Offset
3 (0x3) - 136 (0x88)
2 (0x2) - 154 (0x9a)
1 (0x1) - 117 (0x75)
0 (0x0) - 96 (0x60)
As you can see, the physical order matches (this case, coincidentally)
the order of insertion. The "logical" ordering according to the
clustered index is enforced in the offset table (which is encoded in the
last few bytes of the pages - you'll recognise the binary values of the
offsets in the last line of the dump).
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|