SQL Server Performance

Heap tables

Discussion in 'ALL SQL SERVER QUESTIONS' started by Frank.svs, Dec 11, 2013.

  1. Frank.svs New Member

    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.

Share This Page