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

  • My best quick guess is that a StartTime of NULL indicates an unused entry. Even if not, the code below may give you some ideas for the correct column(s) on which unused status is based.

    --first create an index to speed lookup

    CREATE UNIQUE NONCLUSTERED INDEX IX_Crews_StartTime

    ON dbo.Crews ( StartTime, CrewID )

    --WHERE valid only on SQL 2008+

    WHERE StartTime IS NULL

    --if on Enterprise Edition, uncomment "ONLINE = ON,"

    WITH ( FILLFACTOR = 99, /*ONLINE = ON,*/ SORT_IN_TEMPDB = ON )

    ON [PRIMARY]

    --then do the actual lookup itself

    SELECT /*@CrewID = */ MIN(CrewID)

    FROM dbo.Crews

    WHERE StartTime IS NULL

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.