6

I have this table:

CREATE TABLE [dbo].[relatea] (
   [mid] [varchar](16) NOT NULL,
   [sid] [varchar](16) NOT NULL
)

It stores hash matches. Is there any benefit to having this as a heap? It has around 7 million rows, and the values are not unique in either column. I know heaps are generally never good for any large tables. This table currently has no indexes.

I was thinking of altering the table to:

CREATE TABLE [dbo].[relatea] (
   [mid] [varchar](16) NOT NULL,
   [sid] [varchar](16) NOT NULL,

   CONSTRAINT [pk_relatea] PRIMARY KEY CLUSTERED (
      [mid] ASC,
      [sid] ASC
   )
)

Table queries are generally but not always on sid, and the combination of mid and sid should always be unique.

Is it better to create a clustered index like this, as opposed to leaving the table as a heap?

5
  • The first thing that comes to mind is that you can write faster to a heap. The second thing that comes to mind is that a hash is a fixed-length bit field; so why are you using VARCHAR? You will pay a 6B row overhead for each row. Use CHAR(16) instead and be sure that your hash values are exactly 16B wide. This will save you the VARCHAR row overhead.
    – ooutwire
    Mar 17, 2012 at 0:48
  • "you can write faster to a heap" That's what I figured but at 7mil rows isn't reading against it like looking for a needle in a hay stack ??? Also its 3rd party app so I have no control over design, just trying to index it as it is very heavily read against. Mar 17, 2012 at 3:47
  • 2
    Just finished reading through Use The Index Luke and, unless I'm misunderstanding it, sid should be the first option to index.
    – swasheck
    Mar 19, 2012 at 19:29
  • How often do you query on both?
    – swasheck
    Mar 19, 2012 at 19:31
  • 1
    @ooutwire If the hashes are hexadecimal digits, then char(16) is 8 too many bytes, compared to binary(8).
    – ErikE
    Mar 19, 2012 at 21:50

1 Answer 1

8
+25

I would put a clustered index (not a primary key) on the sid column. If you do lookups against the mid column sometimes as well then add an index to the mid column with the sid column as an included index.

If you don't want to create a clustered index, then two non-clustered indexes one on each column with the other column as an included column should give you the same basic read performance.

2
  • 1
    You wouldn't need to add sid as an included field on the mid index since it's the clustered key and would be included at the leaf level anyways.
    – JNK
    Mar 21, 2012 at 20:56
  • 1
    True. There's no benefit or harm in adding it as when you don't added it's automatically added because it's the clustered index. If you add it you are only storing one copy of it, but now it's visible in the index definition so easier to deal with for long term maintenance.
    – mrdenny
    Mar 21, 2012 at 23:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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