Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Efficient way to get the lowest unused value of a nvarchar field being used as a primary key Expand / Collapse
Author
Message
Posted Friday, March 21, 2014 12:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:26 PM
Points: 6, Visits: 36

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
Post #1553331
Posted Friday, March 21, 2014 4:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:18 AM
Points: 2,048, Visits: 1,403
How do you know when a CrewID value has been 'used'?

Regards
Lempster
Post #1553389
Posted Friday, March 21, 2014 5:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:26 PM
Points: 6, Visits: 36
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
Post #1553437
Posted Friday, March 21, 2014 5:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 7,219, Visits: 13,684
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
Post #1553439
Posted Friday, March 21, 2014 5:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:25 AM
Points: 249, Visits: 529
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
Post #1553443
Posted Friday, March 21, 2014 1:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 2,037, Visits: 3,049
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1553652
Posted Sunday, March 23, 2014 4:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:26 PM
Points: 6, Visits: 36
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
Post #1553841
Posted Sunday, March 23, 2014 5:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 6:26 PM
Points: 6, Visits: 36
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

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
Post #1553842
Posted Sunday, March 23, 2014 7:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 6:25 AM
Points: 249, Visits: 529
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
Post #1553846
Posted Monday, March 24, 2014 3:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 7,219, Visits: 13,684
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
Post #1553917
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse