Home Forums SQL Server 2008 T-SQL (SS2K8) Efficient way to get the lowest unused value of a nvarchar field being used as a primary key RE: Efficient way to get the lowest unused value of a nvarchar field being used as a primary key

  • March 24, 2014 at 3:07 am

    #1699426

    micalo (3/23/2014)

    Hi Scott,

    Thanks for trying but didn't work unfortunately. I had another crack at it but in the end decided the that its really in the best interest of the company that developed the app to have a nvarchar as the PK. Will just have to go through the experience of explaining why but it think its worth it for them and for me. There'll be some benefits for them in performance as well where this is used in joins if the do that and if they have that in one table it might be worth taking a look at the whole db and offering some suggestions for everyone's sake. Wanted to give it a go as I like a challenge.

    Thanks,

    Michael

    "its really in the best interest of the company that developed the app to have a nvarchar as the PK"

    Can you explain why? If you're going to use a surrogate key, surely an identity column is better than an arbitrary nvarchar value containing tab, space etc characters and which you have to generate for every row?

    "There'll be some benefits for them in performance as well"

    Can you elaborate? Benefits compared to what?

    Why include "Crew" as a suffix in the column values? Call the column 'CrewID' and gain 4 character spaces - you could probably get away with a varchar column instead of nvarchar. Then you won't have to jump through hoops figuring out a slow and dodgy algorithm to find unused PK's.

    If you like a challenge, and offering a challenge to anyone else who's going to be working on this database, then you've chosen well.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden