Post reply

Index larger than table

  • January 7, 2020 at 8:19 pm

    Go to Answer

    #3713383

    I am looking at the large amount of space taken up by indexes and have a few questions; I have been searching online for a while and can't find seem to find a direct answer.  For context I have more of an Oracle background.

    1. On a table with a clustered primary key, is the row locater in a non-clustered index a small row id or the primary key itself?
    2. Does a table with a clustered primary key even have a row id?
    3. If there is no row id, why does a clustered primary key take up index space?

    I'm trying to figure out why a non-clustered index with only one column takes up 20% more space than the table itself.  Fill factor is 99.8%.  However, 7 of the tables 9 columns are in the primary key.  Also wondering why primary keys take up index space.

    Thanks very much.

  • January 7, 2020 at 8:21 pm

    Answer

    #3713384

    .1. The PK itself.

    .2. No.

    .3. The clustering key column(s) are stored in higher-level index entries.  The deeper the index depth, the more space the upper levels of the index take.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 7, 2020 at 8:29 pm

    #3713389

    Thank you.

  • January 8, 2020 at 2:08 pm

    #3713593

    Also, in SQL Server, when you're looking at a clustered index, remember, that's the table. The data is stored with the clustered index. There isn't any kind of separation between the two. We have four kinds of table storage: clustered index, heap, clustered columnstore, memory-optimized.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 8, 2020 at 3:34 pm

    #3713650

    Grant Fritchey wrote:

    Also, in SQL Server, when you're looking at a clustered index, remember, that's the table. The data is stored with the clustered index. There isn't any kind of separation between the two. We have four kinds of table storage: clustered index, heap, clustered columnstore, memory-optimized.

    Thank you.  That's exactly why I was wondering what was stored in the index space for a clustered index, I thought it was all in the data space since it's the table itself.  It's typically less than 1% of the size of the table, so not significant though.

  • January 8, 2020 at 5:07 pm

    #3713700

    The slight extra space is to store the upper level(s) (root level and intermediate level(s)) of the index, which are the b-tree structure that allows quick searches to specific row keys.  The leaf pages are the actual data pages.  Here's the basic structure of a clustered index.

    sql_clustered_index_structure

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • January 8, 2020 at 5:23 pm

    #3713710

    ScottPletcher wrote:

    The slight extra space is to store the upper level(s) (root level and intermediate level(s)) of the index, which are the b-tree structure that allows quick searches to specific row keys.  The leaf pages are the actual data pages.  Here's the basic structure of a clustered index.

    Thanks, that's what I thought based on your original reply, but good to see the details.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply