1

I have a some tables(Table A and Table B) with a BIGINT with IDENTITY specification as primary key. In those tables I have 2 VARBINARY(MAX) columns. Updates and deletes are very rare.

They have with almost the same row count, Table B a bit less but have significant more data in the VARBINARY(MAX) columns.

I was surprised to see that the storage used by PK in Table B was much higher than the storage used by PK in Table A. Doing some reading, correct me if I am wrong, on the subject clarified that is has some thing to do with the max row size around 8k. So the there is some paging going on with a byte reference which is then included in the index. Hence the larger storage used by PK in Table B. It is around 30 percent of the total size of the DB. I was of the assumption that only the BIGINT was part of the index.

My question is whether there is a workaround for that? Any designs, techniques or hacks that can prevent this?

Regards

Vilma

Community
  • 1
  • 1
Vilma
  • 11
  • 2

1 Answers1

0

A PK is a CLUSTERED index: the data is stored with the key. You can have only one clustered index per table, because the data can only be stored in one place. So any clustered index (such as a PK) will take up more space than a non-clustered index.

If there is more varbinary table in B, then I would expect the PK to take up more space.

However, since this varbinary is (MAX) then the initial thought is that only the data pointer should be stored with the key. However, if the row is small enough (i.e. < 8000 bytes) I imagine that SQL Server optimises the store/retrieve by keeping the data with the key, thus increasing the size of the index. I do not know that this happens, but was unable to find anything to say it doesn't; as an optimisation is seems reasonable.

Take that for what it's worth!

simon at rcl
  • 7,326
  • 1
  • 17
  • 24
  • I see. So the assumption is that VARBINARY(MAX) data in this case is embedded in the PK clustered index. Is it redundant then? Mening that it is stored in the column too? – Vilma Aug 14 '15 at 09:17
  • M y speculation was that in some circumstances (when the varbinary is short enough) it would be stored with the index, yes; when it gets long enough for that to impossible then it's shunted off to where big stuff is stored and replaced in the index with a pointer to the big-stuff storage. – simon at rcl Aug 14 '15 at 12:05