Efficient way to get the lowest unused value of a nvarchar field being used as a primary key
-
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
-
How do you know when a CrewID value has been 'used'?
Regards
Lempster
-
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
-
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 ShawFor 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 -
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] -
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
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
-
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
-
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] -
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 ShawFor 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 -
Hi Chris,
lol it was a typo, it should have said identity. sorry for the confusion
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply