designing new table for daily uploads - use unique constraint

Question

I am using SQL Server 2012 & am creating a table that will have 8 columns, types below

datetime
varchar(12)
varchar(6)
varchar(100)
float
float
int
datetime

Once a day (normally) there will be an upload of approx 10,000 rows of data. Going forward its possible it could be 100,000.

The rows will be unique if I group on the first three columns listed above. I have read I can use the unique constraint on multiple columns which will guarantee the rows are unique.

I think I'm correct in saying that the unique constraint by default sets up non-clustered index. Would a clustered index be better & assuming when the table starts to contain millions of rows this won't cause any issues?

My last question. By applying the unique constraint on my table I am right to say querying the data will be quicker than if the unique constraint wasn't applied (because of the non-clustering or clustering) & uploading the data will be slower (which is fine) with the constraint on the table?

Does your table have primary key? If yes you cannot have 2 clustered indexes. — Lukasz Szozda, Aug 21 '15 at 10:09
the queries will vary to be honest. I imagine most queries will make use of the first datetime column — mHelpMe, Aug 21 '15 at 10:13

Backs · Accepted Answer · 2015-08-21T10:26:42.730

Unique index can be non-clustered.
Primary key is unique and can be clustered
Clustered index is not unique by default
Unique clustered index is unique :)

Mor information you can get from this guide.

So, we should separate uniqueness and index keys. If you need to kepp data unique by some column - create uniqe contraint (unique index). You'll protect your data. Also, you can create primary key (PK) on your columns - they will be unique also. But, there is a difference: all other indexies will use PK for referencing, so PK must be as short as possible. So, my advice - create Identity column (int or bigint) and create PK on it. And, create unique index on your unique columns. Querying data may become faster, if you do queries on your unique columns, if you do query on other columns - you need to create other, specific indexies.

So, unique keys - for data consistency, indexies - for queries.

isn't it that the primary key can be non-clustered too? – Manish Rawat Aug 21 '15 at 10:25 — Manish Rawat, Aug 21 '15 at 10:25

score 1 · Answer 2 · edited May 23 '17 at 11:51

I think I'm correct in saying that the unique constraint by default sets up non-clustered index

TRUE

Would a clustered index be better & assuming when the table starts to contain millions of rows this won't cause any issues?

(1)if u need to make (datetime ,varchar(12), varchar(6)) Unique

(2)if you application or you will access rows using datetime or datetime ,varchar(12) or datetime ,varchar(12), varchar(6) in where condition ALL the time then have primary key on (datetime ,varchar(12), varchar(6))

by default it will put Uniqness and clustered index on all above three column.

but as you commented above:

the queries will vary to be honest. I imagine most queries will make use of the first datetime column

and you will deal with huge data and might join this table with other tables

then its better have a surrogate key( ever-increasing unique identifier ) in the table and to satisfy your Selects have Non-Clustered INDEXES

Surrogate Key vs Business Key

NON-CLUSTERED INDEX

designing new table for daily uploads - use unique constraint

2 Answers2

Linked