Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm not sure what to ask...

    huh?

    Got any code you can post?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    sorry, i was afraid that would be confusing...
    what i am trying to get at is what values do i use as my index id for each table? does it matter what i use. for example, if i have a table:

    Create Table Table1
    (
    TableID
    Row1
    Row2
    Row3
    Row4
    ForeignKeyID
    )

    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.

    thanks again.

  4. #4
    Join Date
    Sep 2003
    Location
    Los Angeles
    Posts
    103
    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).

  5. #5
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Not clear on what you are asking but maybe these help:

    http://www.sql-server-performance.co...structures.asp

    http://msdn.microsoft.com/library/de...es_05_5h6b.asp

    http://www.sqlservercentral.com/colu...rthatindex.asp

    if you read these and still have questions, come on back!!
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  6. #6
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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
    2000
    2050
    2100
    2150
    2200

    Create table test
    (
    col1 int Identity(2000,50) not null
    )
    Hope thi helps.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I usually use the largest possible negative number for the seed, with an increment of one. I don't consider the possible overlap of ID values to mean diddly squat.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •