December 11, 2013 at 6:12 am
Hi,
Can anyone explain the below behavior for HEAP tables?
I have created a small heap table with 3 rows which can easily fit in 1 data page. Not sure why SQL Server is allocating 2 data pages. When I create a clustered index , I see only 1 data page which is expected. Not sure why 2 pages for Heaps ?
-- Heap table behaviour
CREATE TABLE Test
( c1 int IDENTITY(1,1) NOT NULL,
c2 VARCHAR(2500)
)
GO
INSERT INTO Test( c2 ) SELECT REPLICATE('X', 2500)
GO 3
DBCC IND(0,N'Test',-1)
GO
-- Clusterred index behaviour
CREATE TABLE Test2
( c1 int IDENTITY(1,1) NOT NULL primary key,
c2 VARCHAR(2500)
)
GO
INSERT INTO Test2( c2 ) SELECT REPLICATE('X', 2500)
GO 3
DBCC IND(0,N'Test2',-1)
GO
Thanks in Advance.
December 11, 2013 at 7:16 am
It's largely because heap storage goes where ever. It's one of the fundamental issues with heaps and why it's worth pointing out that fundamental storage and access in SQL Server is designed around the clustered index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 11, 2013 at 7:31 am
Hi Grant,
Thanks for quick response.
I was little curious and I had my question to myself of why the storage engine behaves differently in allocating data pages. For instance, if anyone has a Heap table, then I might end up with more data pages !
Again, I completely agree its a good practice to go with clustered index to improve my SELECT performance.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply