5

I have a table in SQL Server 2012 Enterprise Edition that has ~100GB of data. Most of this data is attributed to a single NVARCHAR(MAX) column which has an average length of 4500 characters. It stores data compressed by an application that has no need for Unicode characters.

Since NVARCHAR takes two bytes per character and VARCHAR takes one, I surmised I could cut down on some storage by switching the table over. However, once I altered the column to be VARCHAR(MAX), the table ballooned to 155GB.

I am aware SQL may use Unicode Compression but the documentation states this does not apply to NVARCHAR(MAX) so it couldn't have been in use.

So why has my table grown so much in size?

Table:

CREATE TABLE [dbo].[Table](
    [RecordLocator] [char](6) NOT NULL,
    [CreateDate] [date] NOT NULL,
    [SystemId] [varchar](40) NOT NULL,
    [EventXML] [nvarchar](max) NULL, --Not XML, compressed XML as characters
    [UpdateUTCTimeStamp] [datetime2](7) NOT NULL,
    [DeleteDate] [date] NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED 
(
    [RecordLocator] ASC,
    [CreateDate] ASC,
    [SystemId] ASC,
    [DeleteDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
GO

Additional notes:

  • Table design is not ideal, I am also working on getting that changed with the third party developers.
  • Table is partitioned on DeleteDate - also not ideal but required as of now to efficiently swap out old data
  • Table does not have row or page compression enabled
  • Table row growth before and after table alter is negligible
  • D'oh, I did run sp_spaceused and failed to add the output:

     name   rows        reserved        data            index_size  unused
     Table  11407899    160531008 KB    159465328 KB    525200 KB   540480 KB
    

marked as duplicate by Paul White Oct 4 '17 at 15:10

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.