January 22, 2010 at 4:23 am
Hello,
In our app, we expect to have a table with approx 300 Million rows.
We gave two BigInt columns (PathKey, FileKey) which determine the uniqueness of a record.
I want to know which way will be good to index this table.
1. Create one composite primary key on both the columns.
2. create a surrogate key which is int. (we don't expect more than 2 billion rows in this table). Then create one composite non-clustered index on the two bigint columns
3. create a surrogate key which is int. (we don't expect more than 2 billion rows in this table). Then create two non-clustered indexes, one on PathKey and the other on FileKey.
This table is never queried directly... it is always joined with other tables.... which get the queries.
January 22, 2010 at 4:47 am
Two options.
1) Create a primary key on those columns.
2) Create a surrogate pk (int identity perhaps) and then put a unique constraint on those two columns.
There's lots of debate which is better, you'll have people on both sides of the fence. I prefer the surrogate pk, because it means that foreign keys are smaller, but doing it the other way is not wrong.
Both of those ensure data integrity, by making sure that the unique columns are really unique. As for general indexing advice, cannot give any advice here as you haven't said anything about how the table is queried.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 22, 2010 at 5:08 am
The table is never queried directly. It is joined with other tables, and it is those tables which are queried.
the rows of this table is fetched, purely from the join.
January 22, 2010 at 5:50 am
And it's only ever joined on the two columns? No filtered are ever done against other columns in this table?
If that's the case then you probably want to put the clustered index on those two fields. If you make them the composite pk, that will happen by default. If you add a surrogate pk, just ensure that it's nonclustered and make the unique constraint clustered.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply