-4

Can the indexes on a SQL Server table consume more disk space than the table itself?

I have gone through several articles and studied this and a few expert recommended table partitioning, for example, this MSDN forums question

why can and index be of greater size than the actual data?

How can I reduce index size?

How can I identify unnecessary indexes in this context?

  • 4
    I've read this a few times now, and I'm still not sure what your question is. Where does partitioning come in? Who recommended it for what? Can you update it to be really clear what information you're looking for? – Erik Darling Aug 20 '18 at 18:00
  • 1
    @DBOn - "script" the table and it's indexes and add those definitions to your question. Then add details about the exact problem you are having. Referencing some question someone else has about a problem tells us nothing about what you need to fix. – Max Vernon Aug 20 '18 at 18:14
5

A completely valid table setup might be:

CREATE TABLE dbo.A
(
    a int NOT NULL
    , b varchar(30) NOT NULL
    , c varchar(30) NOT NULL
);

CREATE INDEX A_01 ON dbo.A (a, b);
CREATE INDEX A_02 ON dbo.A (a, c);
CREATE INDEX A_03 ON dbo.A (a, c, b);

Pretty clearly, the total space used by the indexes will be more than the space used by the table itself.

There are many reasons why the total amount of index space might be higher than that used by the table itself. Several that immediately come to mind:

  • Carelessly chosen index strategy where we "index everything".
  • Implementation of every single index recommended by the Database Engine Tuning Advisor
  • Favoring query speed for many combinations of queries on a primarily read-only workload, such as a data warehouse.

If you provide the table definition in your question along with all existing indexes, in T-SQL CREATE TABLE statements, we may be able to suggest improvements.

  • @DBOn, the only reason to create indexes at all is performance (barring constraint indexes and unique indexes serving as constraints. The particular indexing strategy in Max's answer is dubious as the last composite index eliminates the need for the first 2 indexes. It should also be the clustered index so that little additional space is needed. – Dan Guzman Aug 20 '18 at 22:40
2

A single non clustered index could theoretically be larger than your data size too, since the clustered key is included in the non clustered index as a pointer to the actual row.

So let's say you have a table like this

CREATE TABLE dbo.Table
(
    a char(30) NOT NULL
    , b char(30) NOT NULL
    , c char(30) NOT NULL
);

With a clustered index on a and b, and then create a nonclustered index on b and c the nonclustered index will contain the following column values

+------------------+---------------------+
|   index columns  |  bookmark           |
+------------------+---------------------+
|  b     |  c      |  a       |   b      |
+------------------+---------------------+

Read this post by Kalen for an introduction.

If your clustered key isn't unique SQL will even add a 4 bit uniquifier to the index.

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.