How can i reduce the size of a table stores long text in Sql Server?-CodePudding

I have a table with some nvarchar(MAX) columns in sqlserver.

Now, most of these columns are storing a few hundred lines of text, which make the table taking up a lot of disk space.

How can i reduce the size of the table, may be change the type of the columns, without losing data?

CodePudding user response：

If your data doesn't have any unicode characters, you could use varchar(max), that uses half the space of nvarchar(max). But if you have unicode characters, they will be replaced by '?', so you would lose data.

The other option you have is to activate data compression on the table. You can do this on the Data Compression Wizard of SQL Server Management Studio

CodePudding user response：

It is worth considering the possibility of using a clustered columnstore index on a table since it provides a high data compression ratio.

For example:

Create Table [dbo].[Tbl] (ID Int Identity(1,1), Txt nVarChar(max))

Declare @Cnt Int = 0
Declare @LowerNum Int = 500
Declare @UpperNum Int = 8000
Declare @Length Int

-- filling in the table with data

While @Cnt < 20000
    Begin 
        Select @Length = Round(((@UpperNum - @LowerNum) * Rand())   @LowerNum, 0)

        Insert Into [dbo].[Tbl] (Txt)
        Select Txt
        From OpenJson((Select Txt From (Select crypt_gen_random(@Length) As Txt) As R For Json Auto)) With (Txt nVarChar(max))

        Set @Cnt = @Cnt   1
    End

Сreating a clustered primary key on the [ID] column.

ALTER TABLE [dbo].[Tbl] ADD CONSTRAINT [PK_Tbl] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
        IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, 
        FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
ON [PRIMARY]

The size of the table in this case is about 250MB.

Let's create a clustered columnstore index instead of a clustered primary key.

CREATE CLUSTERED COLUMNSTORE INDEX [PK_Tbl] ON [dbo].[Tbl] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]

Now the size of the table is about 135MB.