Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

up vote 8 down vote accepted
+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.

share|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
    
+1 - I would do exactly what mrdenny suggested. –  JHFB Mar 22 '12 at 16:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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