1 year ago

#327932

test-img

MikeO

Index and primary key in large table that doesn't have an Id column

I'm looking for guidance on the best practice for adding indexes / primary key for the following table in SQL Server.

My goal is to maximize performance mostly on selecting data, but also in inserts.

IndicatorValue
(
   [IndicatorId] [uniqueidentifier] NOT NULL,  -- this is a foreign key
   [UnixTime] [bigint] NOT null,
   [Value] [decimal](15,4) NOT NULL,
   [Interval] [int] NOT NULL
)

The table will have over 10 million rows. Data is batch inserted between 5-10 thousand rows at a time.

I frequently query the data and retrieve the same 5-10 thousand rows at a time with SQL similar to

SELECT [UnixTime]
FROM [IndicatorValue]
WHERE [IndicatorId] = 'xxx GUID xxx'
  AND [Interval] = 2
ORDER BY [UnixTime]

or

SELECT [UnixTime], [Value]
FROM [IndicatorValue]
WHERE [IndicatorId] = 'xxx GUID xxx'
  AND [Interval] = 2
ORDER BY [UnixTime]

Based on my limited knowledge of SQL indexes, I think:

  1. I should have a clustered index on IndicatorId and Interval. Because of the ORDER BY, should it also include UnixTime?
  2. As I don't have an identity column (didn't create one because I wouldn't use it), I could have a non-clustered primary key on IndicatorId, UnixTime and Interval, because I read that it's always good to have PK on every table.

Also, the data is very rarely deleted, and there are not many updates, but when they happen it's only on 1 row.

Any insight on best practices would be much appreciated.

sql-server

database-indexes

0 Answers

Your Answer

Accepted video resources