ntext column problem?

  • Hallo,

    Here is a SQL Server 2000 table:

    CREATE TABLE [dbo].[dt_Dossier] (

    [id] [int] IDENTITY (1, 1) NOT NULL ,

    [creatieDt] [datetime] NOT NULL ,

    [wijzigDt] [datetime] NOT NULL ,

    [leningNr] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

    [aanvraagId] [int] NULL ,

    [XML] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[dt_Dossier] ADD CONSTRAINT [PK_Dossier] PRIMARY KEY NONCLUSTERED ([id]) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[dt_Dossier] ADD CONSTRAINT [FK_Dossier_AanvraagId] FOREIGN KEY ([aanvraagId]) REFERENCES [dbo].[dt_Aanvraag] ([id])

    GO

    The table contains about 1 million rows.

    The XML column in all the rows is set to NULL. Committed, checked, etc.

    I expect the used size of the table to be 1000000 * (the sum of column lengths) = 1000000 * 90 Bytes = about 90 MB.

    However, the used size of the table (obtained by Enterprise Manager and scripts) is 40 GB, i.e. about 40 KB/row.

    What is my mistake?

  • I haven't used ntext in SQL 2000 in a while, but I think it has to allocate space for it even if it's null. I could easily be wrong about that.

    Have you tried creating a copy of the table without the ntext column and seeing what that has for the table size?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Yes, I have tried this.

    The copy size is about 80 MB, even with an (empty) ntext column. So, this is a work-around (copy, drop original, rename copy). Thank you!

    But how can I get the free space without this trick?

  • Youri S. Pikulin (7/28/2009)


    Yes, I have tried this.

    The copy size is about 80 MB, even with an (empty) ntext column. So, this is a work-around (copy, drop original, rename copy). Thank you!

    But how can I get the free space without this trick?

    Take a look at today's QotD (Question of the Day). Based on that, I don't think there is another way, but I may be wrong.

  • Thank you all.

    DBCC CLEANTABLE was the solution for me.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply