Efficient way to get the lowest unused value of a nvarchar field being used as a primary key

Question

Post reply

Efficient way to get the lowest unused value of a nvarchar field being used as a primary key

micalo

SSC Journeyman

Points: 88
More actions
March 21, 2014 at 12:57 am

#286897

Hi all,
Trying to get the lowest unused value of CrewID from the below table in a efficient manner but stumped and was wondering if any one could think of a good solution. I cant change the table structure as its from a 3rd party and it would break our support agreement.
CREATE TABLE [dbo].[Crews](
[CrewID] [nvarchar](10) NOT NULL,
[CrewName] [nvarchar](200) NOT NULL,
[CompanyID] [nvarchar](10) NOT NULL,
[StartTime] [nvarchar](50) NULL,
[BreakTime] [real] NULL,
[FinishTime] [nvarchar](20) NULL,
[Notes] [ntext] NULL,
[IsPartial] [smallint] NOT NULL,
[LastModified] [datetime] NOT NULL,
[LastModifiedBy] [nvarchar](50) NULL,
[GUID] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_Crews] PRIMARY KEY CLUSTERED
(
[CrewID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Crews] ADD CONSTRAINT [DF_Crews_BreakTime] DEFAULT (0) FOR [BreakTime]
GO
ALTER TABLE [dbo].[Crews] ADD CONSTRAINT [DF_Crews_IsPartial] DEFAULT (0) FOR [IsPartial]
GO
ALTER TABLE [dbo].[Crews] ADD CONSTRAINT [DF_Crews_LastModified] DEFAULT (getdate()) FOR [LastModified]
GO
ALTER TABLE [dbo].[Crews] ADD CONSTRAINT [DF_Crews_GUID] DEFAULT (newid()) FOR [GUID]
GO

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lempster SSCoach Points: 15651 More actions · Answer 1

How do you know when a CrewID value has been 'used'?

Regards

Lempster

micalo SSC Journeyman Points: 88 More actions · Answer 2

Hi Lempster,

People can put whatever they want in there unfortunately. There no really way without going through all the data that i can think of. I personally think its bad design but have to work with it unfortunately.

Regards,

Michael

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

micalo (3/21/2014)
Hi Lempster,
People can put whatever they want in there unfortunately. There no really way without going through all the data that i can think of. I personally think its bad design but have to work with it unfortunately.
Regards,
Michael

The lowest unused value of an nvarchar column...what characters would you like to take into account?

^{“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

thava SSCrazy Points: 2275 More actions · Answer 4

can you show Query, how did you try to pick the lowest value

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

ScottPletcher SSC Guru Points: 100942 More actions · Answer 5

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.

micalo SSC Journeyman Points: 88 More actions · Answer 6

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

micalo SSC Journeyman Points: 88 More actions · Answer 7

FYI,

before deciding its not worth it this is the sql I was playing with. It works but i really didn't want to show it to anyone a I think its a bad solution :ermm:

DECLARE @ID varchar(10)

WHILE @1<=126

BEGIN

WHILE @2<=126

BEGIN

WHILE @3<=126

BEGIN

WHILE @4<=126

BEGIN

WHILE @5<=126

BEGIN

SELECT @ID = 'CREW' + ltrim(char(@1)) + ltrim(CHAR(@2)) + ltrim(char(@3)) + ltrim(CHAR(@4)) + ltrim(char(@5))

IF (SELECT COUNT(CrewID) FROM Crews WHERE CrewID =@ID)=0

BEGIN

GOTO ADDME;

END

SELECT @5=@5+1

END

SELECT @5=32

SELECT @4=@4+1

END

SELECT @4=32

SELECT @3=@3+1

END

SELECT @3=32

SELECT @2=@2+1

END

SELECT @2=32

SELECT @1=@1+1

END

ADDME:

SELECT @ID

thava SSCrazy Points: 2275 More actions · Answer 8

Hi there,

Thanks, I think you try to pick the missing crewid well need some more data, minimum crewid maximum crewid and some sample crewid to know how the crewid is formed, because from your code the logic is missing length, of the column is 10, from your code it may reach 11, please post some data to identify the pattern of the crewid

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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

micalo SSC Journeyman Points: 88 More actions · Answer 10

Hi Chris,

lol it was a typo, it should have said identity. sorry for the confusion