Microsoft SQL database table size

Question

I have come across a sizing issue with my database tables becoming too large. Some digging has shown that it is how MS SQL stores the information.

I created two test tables and filled them with data:

CREATE TABLE INVOICEDOCUMENTSTEST1 (
  INVOICEINDEXNUMBER numeric(10, 0) NOT NULL DEFAULT (0),
  CONSTRAINT PK_INVOICEDOCUMENTSTEST1 PRIMARY KEY CLUSTERED (INVOICEINDEXNUMBER))

DECLARE @i INT;
SELECT @i = 1;
SET NOCOUNT ON
WHILE @i <= 235605
BEGIN
  INSERT INTO INVOICEDOCUMENTSTEST1(INVOICEINDEXNUMBER) VALUES (@i);
  SELECT @i = @i + 1;
END;

CREATE TABLE INVOICEDOCUMENTSTEST2 (
  INVOICEINDEXNUMBER numeric(10, 0) NOT NULL DEFAULT (0),
  [INVOICETAXTYPE] [varchar](15) NULL,
  [INVOICETYPE] [varchar](20) NULL,
  [INVOICESTATUS] [varchar](25) NULL DEFAULT ('Entering'),
  [INVOICEDATE] [datetime] NULL,
  [INVOICETIME] [datetime] NULL,
  [CREATEDATE] [datetime] NULL,
  [CREATETIME] [datetime] NULL,
  [CREATEBY] [varchar](50) NULL,
  [POSTDATE] [datetime] NULL,
  [POSTTIME] [datetime] NULL,
  [POSTEDBY] [varchar](50) NULL,
  [JOBCODE] [varchar](30) NULL,
  [LINKCODEFORWARD] [varchar](30) NULL,
  [CUSTACCOUNTCODE] [varchar](50) NULL,
  [CUSTCOMPANYNAME] [varchar](50) NULL,
  [CUSTDELIVERYADDRESS1] [varchar](50) NULL,
  [CUSTDELIVERYADDRESS2] [varchar](50) NULL,
  [CUSTDELIVERYSUBURB] [varchar](50) NULL,
  [CUSTDELIVERYSTATE] [varchar](50) NULL,
  [CUSTDELIVERYPOSTCODE] [varchar](10) NULL,
  [CUSTDELIVERYCOUNTRY] [varchar](50) NULL,
  [CUSTORDERNUMBER] [varchar](20) NULL,
  [CUSTCONTACT] [varchar](20) NULL,
  [CUSTDEFAULTPRICECODE] [varchar](10) NULL DEFAULT ('Price01'),
  [CUSTCASHSALEACCOUNTONLY] [varchar](5) NULL,
  [CUSTANALYSISCODE] [varchar](50) NULL,
  [SALESPERSON] [varchar](50) NULL,
  [SALESPERSONANALYSISCODE] [varchar](50) NULL,
  [EXTAXINVOICE] [varchar](5) NULL DEFAULT ('FALSE'),
  [EXTAXNUMBER] [varchar](20) NULL,
  [COMMENTS1] [varchar](100) NULL,
  [COMMENTS2] [varchar](100) NULL,
  [COMMENTS3] [varchar](100) NULL,
  [COMMENTS4] [varchar](100) NULL,
  [COMMENTS5] [varchar](100) NULL,
  [CUSTINVOICEADDRESS1] [varchar](50) NULL,
  [CUSTINVOICEADDRESS2] [varchar](50) NULL,
  [CUSTINVOICESUBURB] [varchar](50) NULL,
  [CUSTINVOICESTATE] [varchar](50) NULL,
  [CUSTINVOICEPOSTCODE] [varchar](10) NULL,
  [CUSTINVOICECOUNTRY] [varchar](50) NULL,
  [COURIERCOMPANY] [varchar](50) NULL,
  [COURIERCONSIGNMENTNOTE] [varchar](50) NULL,
  [FREIGHTCOMPANY] [varchar](50) NULL,
  [PAYMENTTERMS] [varchar](50) NULL,
  [PAYMENTREQUIREDBY] [datetime] NULL,
  [REQUIREDBY] [datetime] NULL,
  [REQUIREDAFTER] [datetime] NULL,
  [SHIPPINGDESCRIPTION1] [varchar](50) NULL,
  [SHIPPINGDESCRIPTION2] [varchar](50) NULL,
  [SHIPPINGCARTONQUANTITY] [numeric](10, 0) NULL DEFAULT (0),
  [SHIPPINGDATE] [datetime] NULL,
  [SHIPPINGDGDECLARATION] [varchar](10) NULL DEFAULT ('Does not'),
  [SHIPPINGTIME] [datetime] NULL,
  [SHIPPINGUNITS] [varchar](20) NULL DEFAULT ('Cartons'),
  [NOTES] [ntext] NULL,
  [CUSTCUSTOMERSLEDGERCODE] [varchar](50) NULL,
  [CUSTACCRUALSLEDGERCODE] [varchar](50) NULL,
  [CUSTGLOBALDISCOUNT] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSUBTOTAL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSUBTOTALCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSUBTOTALCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALDISCOUNT] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALDISCOUNTCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALDISCOUNTCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALFREIGHT] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTAX] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTAXCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTAXCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALROUNDINGAMOUNT] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTOTAL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTOTALCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALTOTALCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALPAID] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALCHANGE] [numeric](14, 4) NULL DEFAULT (0),
  [SUPPLYLOCATION] [varchar](10) NULL,
  [HASBEENPRINTED] [varchar](5) NULL DEFAULT ('FALSE'),
  [PRINTTHISTIME] [varchar](5) NULL DEFAULT ('FALSE'),
  [TRANSACTIONMODE] [varchar](10) NULL DEFAULT ('Online'),
  [PREVTOTALFREIGHT] [numeric](14, 4) NULL DEFAULT (0),
  [LINEROUND] [varchar](5) NULL DEFAULT ('TRUE'),
  [SOURCEREFERENCE] [varchar](30) NULL,
  [LINECOUNT] [numeric](10, 0) NULL DEFAULT (0),
  [SHIPPINGINSTRUCTIONS] [varchar](50) NULL,
  [SHIPPINGPACKEDBY] [varchar](50) NULL,
  [INVENTORYFREIGHTANALCODE] [varchar](50) NULL,
  [LINKCODEBACKWARD] [varchar](30) NULL,
  [PREVTOTALCHANGE] [numeric](14, 4) NULL DEFAULT (0),
  [USEGST] [varchar](5) NULL DEFAULT ('FALSE'),
  [TOTALFREIGHTGST] [numeric](14, 4) NULL DEFAULT (0),
  [PREVTOTALFREIGHTGST] [numeric](14, 4) NULL DEFAULT (0),
  [GSTINVOICETYPE] [varchar](20) NULL DEFAULT ('Tax Invoice'),
  [CUSTPHONENUMBER] [varchar](25) NULL,
  [CUSTFAXNUMBER] [varchar](25) NULL,
  [LASTEDITDATE] [datetime] NULL,
  [LASTEDITTIME] [datetime] NULL,
  [LASTEDITBY] [varchar](50) NULL,
  [CUSTGSTSALESTYPE] [varchar](10) NULL DEFAULT ('Including'),
  [GSTPRICING] [varchar](10) NULL,
  [GSTTRANSFER] [numeric](10, 0) NULL DEFAULT (0),
  [ORIGINALINVOICENUMBER] [varchar](20) NULL,
  [ORIGINALDOCUMENTTYPE] [varchar](30) NULL,
  [CUSTEMAILADDRESS] [varchar](200) NULL,
  [CUSTMOBILENUMBER] [varchar](25) NULL,
  [TOTALSURCHARGE] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGECALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGECALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [CUSTFREIGHTCHARGE] [numeric](14, 4) NULL DEFAULT (0),
  [CUSTFREIGHTFREETHRESHOLD] [numeric](14, 4) NULL DEFAULT (0),
  [CUSTSURCHARGECHARGE] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGEGST] [numeric](14, 4) NULL DEFAULT (0),
  [PREVTOTALSURCHARGE] [numeric](14, 4) NULL DEFAULT (0),
  [PREVTOTALSURCHARGEGST] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGEGSTCALCORD] [numeric](14, 4) NULL DEFAULT (0),
  [TOTALSURCHARGEGSTCALCDEL] [numeric](14, 4) NULL DEFAULT (0),
  [INVENTORYSURCHARGEANALCODE] [varchar](50) NULL,
  [INVOICEPRIORITY] [varchar](10) NULL DEFAULT ('Normal'),
  [SHIPPINGPACKERHOLDINGLOC] [varchar](250) NULL,
  [CUSTOMERID] [numeric](10, 0) NOT NULL DEFAULT ((0)),
  [DELIVERYINSTRUCTIONS] [ntext] NULL,
  [PAYMENTNOTES] [ntext] NULL,
  [SHIPPINGNOTES] [ntext] NULL,
  [FREIGHTANDSURCHARGEOLDPOST] [varchar](5) NULL DEFAULT ('FALSE'),
  [TOTALFREIGHTTHRESHOLDREMOVED] [numeric](14, 4) NULL DEFAULT ((0)),
  [CUSTGSTLEDGERCODE] [varchar](50) NULL,
  [SALESPERSONNAME] [varchar](200) NULL,
  [SHIPPINGHASBEENPICKED] [varchar](5) NULL DEFAULT ('FALSE'),
  [SHIPPINGHASBEENPACKED] [varchar](5) NULL DEFAULT ('FALSE'),
  [SHIPPINGHASBEENDELIVERED] [varchar](5) NULL DEFAULT ('FALSE'),
  [SHIPPINGPICKEDBY] [varchar](250) NULL,
  [SHIPPINGPICKDATE] [datetime] NULL,
  [SHIPPINGPICKTIME] [datetime] NULL,
  [SHIPPINGPACKDATE] [datetime] NULL,
  [SHIPPINGPACKTIME] [datetime] NULL,
  [SHIPPINGDELIVEREDBY] [varchar](250) NULL,
  [SHIPPINGDELIVERYDATE] [datetime] NULL,
  [SHIPPINGDELIVERYTIME] [datetime] NULL,
  [INVOICEPRIORITYVALUE] [numeric](10, 0) NULL DEFAULT ((3)),
  [SHIPPINGPACKER] [varchar](250) NULL DEFAULT ('ANYONE'),
  [SHIPPINGPICKER] [varchar](250) NULL DEFAULT ('ANYONE'),
  [SHIPPINGDELIVERYRUN] [varchar](250) NULL DEFAULT ('ANYONE'),
  [CUSTALLOWBACKORDERS] [varchar](5) NULL DEFAULT ('TRUE'),
  [QUOTECOMMENT] [varchar](100) NULL,
  [QUOTESTATUS] [varchar](20) NULL DEFAULT ('Open'),
  [QUOTESTATUSCOMMENT] [varchar](100) NULL,
  [CUSTALLOWSHORTSUPPLY] [varchar](10) NULL DEFAULT ('ALL'),
  [PICKINGINPROGRESSBY] [varchar](100) NULL,
  [PACKINGINPROGRESSBY] [varchar](100) NULL,
  [MANFIESTED] [varchar](5) NULL DEFAULT ('FALSE'),
  CONSTRAINT PK_INVOICEDOCUMENTSTEST2 PRIMARY KEY CLUSTERED (INVOICEINDEXNUMBER))

DECLARE @i INT;
SELECT @i = 1;
SET NOCOUNT ON
WHILE @i <= 235605
BEGIN
  INSERT INTO INVOICEDOCUMENTSTEST2(INVOICEINDEXNUMBER) VALUES (@i);
  SELECT @i = @i + 1;
END;

The first table is a mini version of the second, with only the primary index created.

I have inserted 235605 rows as that's what I have in the live database.

I have a query to get the table and index size:

SELECT
  T.NAME AS TABLENAME,
  '' AS INDEXNAME,
  P.ROWS AS ROWCOUNTS,
  SUM(A.TOTAL_PAGES) * 8 AS TOTALSPACEKB,
  SUM(A.USED_PAGES) * 8 AS USEDSPACEKB,
  (SUM(A.TOTAL_PAGES) - SUM(A.USED_PAGES)) * 8 AS UNUSEDSPACEKB
FROM
  SYS.TABLES T
INNER JOIN
  SYS.INDEXES I ON T.OBJECT_ID = I.OBJECT_ID
INNER JOIN
  SYS.PARTITIONS P ON I.OBJECT_ID = P.OBJECT_ID AND I.INDEX_ID = P.INDEX_ID
INNER JOIN
  SYS.ALLOCATION_UNITS A ON P.PARTITION_ID = A.CONTAINER_ID
LEFT OUTER JOIN
  SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
WHERE
  T.NAME NOT LIKE 'dt%'
  AND T.IS_MS_SHIPPED = 0
  AND I.OBJECT_ID > 255
GROUP BY
  T.NAME, S.NAME, P.ROWS

UNION ALL

SELECT
  OBJECT_NAME(I.OBJECT_ID) AS TABLENAME,
  I.NAME AS INDEXNAME,
  0 AS ROWCOUNTS,
  8 * SUM(A.USED_PAGES) AS TOTALSPACEKB,
  8 * SUM(A.USED_PAGES) AS USEDSPACEKB,
  0 AS UNUSEDSPACEKB
FROM
  SYS.INDEXES AS I JOIN
  SYS.PARTITIONS AS P ON P.OBJECT_ID = I.OBJECT_ID AND P.INDEX_ID = I.INDEX_ID JOIN
  SYS.ALLOCATION_UNITS AS A ON A.CONTAINER_ID = P.PARTITION_ID
WHERE
  OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'sys%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'dt%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'filestream%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'filetable%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'plan_%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'queue_%%'
  AND OBJECT_NAME(I.OBJECT_ID) NOT LIKE 'sqlagent_%%'
GROUP BY
  I.OBJECT_ID,
  I.INDEX_ID,
  I.NAME

ORDER BY 1, 2

When I run the size query across these two tables, I get the following:

TableName             indexname                 RowCounts   TotalSpaceKB
INVOICEDOCUMENTSTEST1                             235605         7560
INVOICEDOCUMENTSTEST1 PK_INVOICEDOCUMENTSTEST1         0         7504
INVOICEDOCUMENTSTEST2                             235605       210584
INVOICEDOCUMENTSTEST2 PK_INVOICEDOCUMENTSTEST2         0       210496

This shows that the row data itself only takes up a fraction of the data. The index on only that field takes approx 7.5mb. However with those extra fields on the table, it expands the primary key out to 210mb, even though all those fields are blank and the same field is the only one in the primary key.

This appears to be an impact of how MS SQL want to access the data. If the primary key on Table 2 is created as 'nonclustered' then the index is smaller but there is a 'hidden' index created that is the same size as a clustered index anyway.

I have found that with the size of some of my tables, that even though the primary key is on a BCD file, the indexes are increased the database to 2-3x the size of the data.

In one case it's pushing the database over the 10gb Express limit so it's causing a financial problem as well.

Is there any other way to structure the table so that you don't give up so much space to the index?

Answer 1

SQL is pre-allocating the space for the fixed-length datatypes in your table (datetime 8b per field per row, numeric(10/14, x) 9b per field per row), and possibly 'stubs' for the variable length datatypes. It doesn't matter if they're null or not. This is by design so that the rows are inserted and updated efficiently. The varchar fields with defaults will also have space allocated to them appropriate to the size of the default. It also means your table is going to get a LOT bigger once you start populating the currently-null varchar fields.

A clustered index and the 'hidden' index you refer to (also known as a heap) aren't actually indexes as such; they're actually the table itself, and so will take up the space required to store the table data.

To try and compact your data, you can try to use some smaller datatypes in places. For example, date and time in place of datetime where appropriate, smaller precision numeric fields (numeric(9, x) and lower is 5 bytes), using integer types tinyint/smallint/int instead of numeric(10,0) for whole numbers, and using a bit field to represent true/false instead of varchar(5) (I count 11 true/false fields, which can be covered by 2b worth of bit fields instead of the 66-77b they currently occupy).

Beyond that, there appears to be a lot of normalisation that can be done on the table which could reduce the table size; e.g. can you combine invoice date & invoice time into a single datetime or datetime2 field? Address, staff/user details, comments and many others can be pushed out into lookup tables and a foreign key added to this table. There also seems to be a lot of calculated values which may be better created as calculated columns (or the logic moved into the app if there is one) rather than stored as an actual field.

Finally, you can look into sparse columns, where null values do not take up any space. However looking at the table I don't think they'll help much, as it looks like only a low number of columns will actually contain null data in normal use.

Answer 2

The index isn't the problem. You can see this yourself by creating the data as a heap (no clustered index) and comparing the space used by both tables.

sp_spaceused 'INVOICEDOCUMENTSTEST2_HEAP';

GO

sp_spaceused 'INVOICEDOCUMENTSTEST2';

GO

╔════════════════════════════╦════════╦═══════════╦═══════════╦════════════╦════════╗
║            name            ║  rows  ║ reserved  ║   data    ║ index_size ║ unused ║
╠════════════════════════════╬════════╬═══════════╬═══════════╬════════════╬════════╣
║ INVOICEDOCUMENTSTEST2_HEAP ║ 235605 ║ 235720 KB ║ 235664 KB ║ 8 KB       ║ 48 KB  ║
║ INVOICEDOCUMENTSTEST2      ║ 235605 ║ 210632 KB ║ 209432 KB ║ 1048 KB    ║ 152 KB ║
╚════════════════════════════╩════════╩═══════════╩═══════════╩════════════╩════════╝

For your example data, putting a clustered index on the table actually reduced the overall space needed for the table.

Storing less redundant data as Gareth Lyons suggested is a good idea. Another idea is to use page compression. Page compression is available in SQL Server Express starting with the 2016 SP1 edition. Page compression implements row compression which offers a number of benefits for your table structure:

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.

It uses variable-length storage format for numeric types (for example integer, decimal, and float) and the types that are based on numeric (for example datetime and money).

NULL and 0 values across all data types are optimized and take no bytes.

Prefix compression and dictionary compression may also save you space. I won't reproduce the details for those algorithms in this answer.

Applying compression is usually not a neutral change from a CPU point of view. CPU utilization can increase or decrease after applying compression. All you can do is test. For your sample data, which probably isn't realistic, the table with page compression is 10X smaller than the original table. As explained above, compression can offer big space savings when a lot of columns are NULL.

╔══════════════════════════════════╦════════╦══════════╦══════════╦════════════╦════════╗
║               name               ║  rows  ║ reserved ║   data   ║ index_size ║ unused ║
╠══════════════════════════════════╬════════╬══════════╬══════════╬════════════╬════════╣
║ INVOICEDOCUMENTSTEST2_COMPRESSED ║ 235605 ║ 21064 KB ║ 20960 KB ║ 96 KB      ║ 8 KB   ║
╚══════════════════════════════════╩════════╩══════════╩══════════╩════════════╩════════╝

For your real data I expect the compression ratio to not be as high. You also may need to move the ntext columns to another table to get the best compression ratio. By the way, ntext is deprecated. Switch to NVARCHAR(MAX).