Search code examples
sql-serversql-server-2008primary-keyclustered-index

Sql Server table structure

I have the following data i want to upload:

(Pair)    (Date)    (Time)       (Bid)   (Ask)
EurUsd  01/01/2012  18:04:01    1.55037 1.55137
EurUsd  01/01/2012  18:04:20    1.55018 1.55118
EurUsd  01/01/2012  18:04:22    1.55031 1.55131
EurUsd  01/01/2012  18:04:36    1.55045 1.55145
EurUsd  01/01/2012  18:04:40    1.55058 1.55158
EurUsd  01/01/2012  18:04:40    1.5508  1.5518

GbpUsd  01/01/2012  18:06:11    1.55097 1.55197
GbpUsd  01/01/2012  18:06:22    1.55117 1.55217
GbpUsd  01/01/2012  18:06:24    1.55128 1.55228
GbpUsd  01/01/2012  18:06:38    1.55098 1.55198

The data types are as follows: Nchar(6), Date, Time, Decimal, Decimal.

Unfortunately i cant guarantee that the rows will be unique on a minute basis. I will also be adding data to this table on a Daily basis & other Pairs.

Question 1). What is the best way to set up a PK for the above data. Im assuming i should just set up a new column called "Id", of type BigInt, and set its "Is Identity" to Yes? Then set the PK to be "Id"?

Question 2). Clustered Index, based on "Pair","Date", "Time"?

Question 3). Im assuming that if i set up an "Id" column in the table, i would need to have ColumnMapping on any sqlbulkcopy operation i was using to upload the data? Or is it not possible to load data into the column once its "Is Identity" property is set?

Thanks for your time.

Solution

  • 1.To setup a PK I suggest you can use the GUID(uniqueidentifier) and use NEWSEQUENTIALID() to populate the data automatically in your table.(This will become clustered index key)

    2.I guess there is no need of clustered index on the set of ("Pair","Date", "Time") columns.If you use any of these columns frequently in your where clause then I prefer non-clustered index on those columns.

    3.When it comes to sqlbulkcopy - you can map the only columns which you want to insert data in the table.