This question already has an answer here:
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.
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?
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
- 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_spaceusedand failed to add the output:
name rows reserved data index_size unused Table 11407899 160531008 KB 159465328 KB 525200 KB 540480 KB