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,

      [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?

  • 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 '12 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. – bumble_bee_tuna Mar 17 '12 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 '12 at 19:29
  • How often do you query on both? – swasheck Mar 19 '12 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 '12 at 21:50

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.

| improve this answer | |
  • 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 '12 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 '12 at 23:04

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.