CODEWITHSUNDEEP
sqlclustered-index
laconicdev
laconicdev

Reputation: 6440

Where clause on non-clustered column that was part of a large insert

I have the following table schema:

CREATE TABLE [Foo](
[id]    [int]       NOT NULL, 
[name]  [varchar(250] NULL,
[datetime_stamp] [datetime]     NULL,
CONSTRAINT [pk_Positions] PRIMARY KEY CLUSTERED 
(   [id] ASC))

Assuming there are inserts that happen n times per day. Each insert consists of 26000 rows. For each insert, the id is a unique value. E.G: insert 1, the id 1, insert 2 the id is 2 for all the rows. The table has a few million rows. If I want to retrieve the rows for a given insert based on its time, would it make sense to use a datetime_stamp in the where clause? Although it's not clustered, it would still be located together in the table. Or is there a more efficient way to retrieve all the inserts for a give n date?

Upvotes: 0

Views: 53

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

Create an index on the datetime_stamp column.

CREATE INDEX IX_Foo_DatetimeStamp ON Foo(datetime_stamp)

Upvotes: 1

Related Questions