0

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?

laconicdev
  • 6,360
  • 11
  • 63
  • 89

1 Answers1

1

Create an index on the datetime_stamp column.

CREATE INDEX IX_Foo_DatetimeStamp ON Foo(datetime_stamp)
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • why not create a clustered non unique index on timestamp column? – Asdfg Apr 12 '12 at 19:39
  • @Asdfg Look again at the DDL. The `id` column is already a clustered primary key. – Joe Stefanelli Apr 12 '12 at 19:40
  • Joe, what performance will be achieved by creating an index on datetime_stamp vs not creating an index on it at all? Also, if the index can not be created. What is the best way? – laconicdev Apr 12 '12 at 19:55