2

I have a .txt file with storage size 1253 MB. After importing it to SQL Server 2016, the table takes 1680 MB on disk. Why does the storage space grow so much?

I didn't do any data transformation and use pure char format to store data. I read the data dictionary of the txt file and if the length of a variable is at most n, then I use varchar(3). If the file is fixed length n, then I used char(n).

About 4 to 5 columns are densely populated and all the other columns are very sparse. For the sparse columns, only 1% rows are not null.

Here is the code creating the table and importing data:

CREATE TABLE table1 (
var1 char(12),      var2 char(6),      var3 varchar(12),       var4 varchar(3),
var5 varchar(3),    var6 varchar(4),   var7 char(1),           var8 char(1),
var9 char(2),       var10 char(6),     var11     varchar(8),   var12 varchar(12),
var13 char(6),      var14 varchar(12), var15 varchar(14),var16 varchar(12),
var17 varchar(12),  var18 varchar(12), var19 varchar(12),var20 varchar(12),
var21 varchar(12),  var22 varchar(12));

How can I reduce the storage space?

Here is the storage information from command sp_spaced:

name       table1
rows       22260960
reserved   1721240 KB
data       1721128 KB
index_size 24 KB
unused     88 KB
3

There is a metadata storage overhead for columns in SQL Server that you do not have to pay for in a flat file. Using compression can reduce this overhead. From Row Compression Implementation:

It reduces the metadata overhead that is associated with the record. This metadata is information about columns, their lengths and offsets. In some cases, the metadata overhead might be larger than the old storage format.

Try compressing your table with row or page compression and see if that reduces the space required in SQL Server.

ALTER TABLE table1 REBUILD WITH (DATA_COMPRESSION = ROW);

In my experience, compression can both improve and degrade query performance. It depends on the data and the query that you're running. It's possible that you'll need to make a trade off between space and query performance.

1

About 4 to 5 columns are densely populated and all the other columns are very sparse.

Another option to explore is to use SPARSE columns. See Use Sparse Columns in the documentation. Note though that sparse columns are incompatible with data compression (and columnstore).

How can I reduce the storage space?

In addition to the row and page compression option mentioned in Joe Obbish's answer, in SQL Server 2016 you could instead use clustered columnstore storage. This will typically reduce storage even more than page compression, particularly if archive compression is used.

See Columnstore Indexes Guide and Using Columnstore and Columnstore Archive Compression in the documentation.

Columnstore tables and indexes are always stored with columnstore compression. You can further reduce the size of columnstore data by configuring an additional compression called archival compression. To perform archival compression, SQL Server runs the Microsoft XPRESS compression algorithm on the data.

0

How many records are imported? It should be relatively straightforward to determine how much storage is required. Every char(n) requires n bytes, and every varchar(n) requires (2+the actual length of the data entered) bytes.

See char and varchar (Transact-SQL) in the product documentation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.