0

I need to create a table with a single column: email.

Can I create a table with a single column and add a clustered index on email? Or should I create and identity column and do a non-clustered on email?

The table will hold around a million email addresses.

Developers will use this table, and I imagine they will just do where xxxx in (select email from table); the way I see it, there is no other way of using this table.

I will run a merge once a week that will insert new emails. Not sure if I should do a merge, if it is uniquely clustered on email. I can just insert and hopefully if a record is duplicated it would not insert it and continue with the rest, right?

Tanner
  • 22,205
  • 9
  • 65
  • 83
Chicago1988
  • 970
  • 3
  • 14
  • 35
  • A single column table doesn't need to be indexed. How will the index help ? – sagi Dec 20 '16 at 14:34
  • @sagi, maybe to know if exists? – Juan Carlos Oropeza Dec 20 '16 at 14:36
  • 2
    To answer your question... Yes you can. Create the column as PK, Clustered. If you try to insert a duplicate, it will throw an error, so you'll need to handle that. – SS_DBA Dec 20 '16 at 14:36
  • Mmm yea, but on 1MIL record table, I doubt there will be any differences in terms of performances.. Maybe only with hurt them on inserts @JuanCarlosOropeza – sagi Dec 20 '16 at 14:42
  • 1
    It depends how this table is related with the other entities (read: tables), i won't let each developer to code their own relations...so i would find a table with just one field useless. – deblocker Dec 20 '16 at 14:46
  • I think you need to read this question: http://stackoverflow.com/questions/3804108/use-email-address-as-primary-key/3804174#3804174 – HLGEM Dec 20 '16 at 14:53

1 Answers1

0

This is mostly a personal choice decision. There's some performance improvements to having the identity column as your clustered index key when your code is inserting/updating/deleting.

I would create the identity column as the clustered index and make the email a separate column. It's ideal to have the clustered index key as an ever-increasing value.

What happens if you enter the same email twice? Should that be two separate rows or should that cause an error? These are things to think about when you design this table.

Arthur D
  • 592
  • 4
  • 10