Results 1 to 8 of 8

Thread: Index Key

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Index Key

    Has anyone tried to program their own Index key into a sql database?? Lets say you want a primary key that starts like TCC001 - TCC900, How would you program sql to populate your index in that range for every time you add a record??? Is this possible???

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Index pages are automagically maintained by the engine for every action query (INSERT,UPDATE,DELETE). Bulk load operations through statistics information off which requires additional manual maintenance on them...or, if you know exactly what statistics are present you can force their re-population by a SELECT statement with proper clause.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can simulate/duplicate SQL Server's Identity functionality by creating a table to store the next index value to be used. You then write an INSERT trigger on your data table that copies this value for the record's key and increments the stored value for the next call.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Coach me on this one... I thought it was questionable the first time we discussed it. I'm not completely sure it is a bad idea, but I'm not very comfortable with it either.

    Can you explain in a bit more detail what you want, and why you think this format beats an identity or a guid? I'm not saying that it isn't better, I just can't see what it does better than they do.

    -PatP

  5. #5
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    I don't think it's better personally Pat and blindman. I've used the exact same technique he's talking about though. We had a system where we couldn't change the stupid key they had. It was an account number for customers and the business had a convulsion at the idea of changing it. We used a table with an identity column and a couple calculated fields everytime we inserted a new customer. We then copied the combined columns to the customer table.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    All this is good, but why notuse the techniques that are already available in out-of-the-box SQL2K installation, - computed columns? Pat's reference to the previous discussion clearly indicates that the poster is still pondering on having her own key. So while "888" portion can be taken from the identity field, "TL" or whatever else can be either derived from a case stament or straight from another table and finally form what the poster asks for, - TL888. And if the column is defined correctly, - you can have a unique constraint placed on it which will allow you to use it as a PK.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    I love it

    Fascinating Answers guys...I really appreciate it. I was just pondering it, I wanted to get the GURUS opinion on the matter. The reason I ask is because the the exsisting licensed vendors get a vendor Number thats how they are identified..but the Government vendors dont so theres no way to identify them (unique). Perhaps I should just seperate them out of the Table and put them in thier own called GovVendors_Tbl and then just let the IDENTITY handle the primary key role. Its just that in order for the users to do a search by the Vendor Number which they know they would then have to learn which number they are when the identity is applied that was the main concern. Does that make sense?

    Thanks again for the response I learn something new everytime you guys respond

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would use an integer identity as the actual PK for the table, then use the "id" value that the vendor assigns as a sort of "short name" column. This lets you off the hook if some bozo vendor decides to re-use an id of their own, or better still of another vendor!

    Then you could have the stored procedure that inserts new items assign an id if the value is blank (NULL, spaces, whatever works for your app). If you don't have a stored procedure doing the insert (naughty, naughty!), you could use a trigger to get the same effect.

    -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
  •