SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
micalo
micalo
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 40
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
Lempster
Lempster
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3185 Visits: 1657
How do you know when a CrewID value has been 'used'?

Regards
Lempster
micalo
micalo
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 40
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
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16049 Visits: 19528
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
Exploring Recursive CTEs by Example Dwain Camps
thava
thava
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 557
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
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7784 Visits: 7140
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
micalo
micalo
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 40
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
micalo
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 40
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
thava
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 557
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16049 Visits: 19528
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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search