Unanswered: indexing question (getting started with index selection)
hi. this may sound super obvious but i am curious about the thought process that goes into index selection for a table. I have designed a new database and have read up on indexes. What i am unclear about is what value to originally assign the index? Say I have 5 tables. Should OneID = 1, TwoID = 2, ThreeID = 3 and so on? what happens when the index of table one and the index of table two are the same values, does that matter? I have run the index selection wizard and used the profiler to create a trace table but, when i am inserting data into my tables, i am asked to insert an index value... i am not sure where i should start/what numbers i should use (without simply putting down something random).
some "getting started" thoughts on indexing would be appreciated.
thanks in advance.
and then i do an first insert:
Insert into table1 (tableid, row1, row2, row3, row4, foreignkeyid)
values (?, value, value, value, value, ?)
what should i be inserting for my Indexes? Right now i am simly assigning random values... 1000 for TableID and 2000 for the foreign keyid, and so on. Will this cause complications as the table grows and numbers overlap, etc?
If this question still sounds awkward, maybe somebody could point me to a good resouce on INdex Selection for new tables? I haven't found anything good online.
I would make [TableID] an Identity field that would insure a unique value.
And you need to have the value of the foreign key before you insert a new record. Whether it's unique or not it depends on your appliciation (1 to 1 or 1 to many).
it appears that you are asking for a unique key value for each primary key column..
if this is correct you can use the identity function on the column to generate a auto incrementing monotonic key.
identity has 2 arguments the seed and the increment.
seed is the inital start value when the first row is inserted into the table
increment is the step between the previous number and the next number
so an seed of 2000 and an increment of 50 would be
Create table test
col1 int Identity(2000,50) not null
Hope thi helps.