Creating Indexes in SQL Server?

[Access9001]

I have a really, really massive table (over 160 million rows) -- but I want to make some indexes to optimize this thing.

I have three columns, User ID (number/int), Badge ID (number/int), and WinDate (Date/Time)

There may be duplicate values in each column, but each row is unique in itself. For instance:

1, 1, 5/24/2011
1, 4, 6/7/2011
1, 2, 4/4/2010
2, 2, 5/25/2011
2, 3, 6/2/2011
2, 4, 8/8/2011

I basically want to be able to statistically process this table so I'll be joining on User ID's in some queries, Badge ID's in others, and grouping things into date groups for others, etc.

What kind of indexes do I need to make and how? (clustered, nonclustered, xml, spatial, etc)

 

[bparkinson]

Looks like a card reader app with lots of swipes. A couple of things spring to mind.

SQL Server is fine with millions of rows, but you might want to consider splitting those data up into archival tables and a current table - if most of your querying will be current year or some subset like that.

Put non-clustered indexes on BadgeID and WinDate. Maybe a clustered on UserID, but I think non-clustered is best actually.

Make sure there are clustered indexes on Badge.ID and User.ID and that they are primary key for the User and Badge tables.

This table is probably being inserted into on every badge swipe, so test for performance degradation after adding the indexes.