June 14, 2009 at 6:34 pm
Identity and uniqueIdentifier and customid
Is there any CustomID method where I can create my own ID for keys in tables.
Sequential ID from identity is ok
The UniqueIdentifier works but is ugly..and not user friendly.
Is there any CustomID process out there ???:-)
UPDATE1: I was thinking something like this...
UserID= 105
Date = 2009/01/22
Time = 06:35:45
ID = 10520090122063545
Save as a bigint
Would this be fast for searching as a primary key (indexed) ??
Or would it be better to save it as a varchar(50) ?
June 14, 2009 at 8:18 pm
I'm not too sure but bigint will require 8 bytes for storage per record where as varchar(50) will take up 50 bytes of storage, in this case storage requirements of varchar will be 6 times higher than that of bigint. Also, maximum size of bigint is 9,223,372,036,854,775,807 which has a length of 19. If you were thinking of bigint as datatype, i suppose 14 places are consumed with date and time, and u use remaining 9 places to store identity generated ids. Even in this case if you use varchar, you'll require minimum of 19 characters (varchar(19)).
In my opinion, bigint will be faster as it is smaller in size.
I'm waiting for others to comment on this.
June 14, 2009 at 9:35 pm
This is the scalar bigint function that I will use to get my unique ID
NOTE: I do not need to insert 100's of records in a row, so I can allow a second delay btw each insert.
CREATE FUNCTION [dbo].[fnDateBigInt] (
@Date DATETIME)
RETURNS BIGINT
AS
BEGIN
DECLARE @display BIGINT
DECLARE @year as VARCHAR
SET @year = CAST(DATENAME(yy,getdate()) - 2000 AS VARCHAR)
SET @display=CAST(@year +
DATENAME(dy,getdate())+
DATENAME(hh,getdate())+
DATENAME(mi,getdate())+
DATENAME(ss,getdate())+
DATENAME(ms,getdate()) AS BIGINT)
RETURN @display
END
RETURNS : 916615341483
[SELECT dbo.fnDateBigInt(getdate())]
So with UserID = 105 then unique id will be
105916615341483
Save as a bigint the primary key field...
So would this be fast ???
UPDATE1:
When I run this...
SELECT dbo.fnDateBigInt(getdate())
go
SELECT dbo.fnDateBigInt(getdate())
go
SELECT dbo.fnDateBigInt(getdate())
go
SELECT dbo.fnDateBigInt(getdate())
go
SELECT dbo.fnDateBigInt(getdate())
go
SELECT dbo.fnDateBigInt(getdate())
go
SELECT dbo.fnDateBigInt(getdate())
go
I get a unique number every time...real nice..but would the bigint key be fast as primary key in 1000s of records ?
June 15, 2009 at 1:16 pm
i believe bigint will take much lesser space than varchar and when you have huge number of records in the table, there would be a significant performance improvement especially since you're going to have a clusterd index(primary key?); since the space occupied is lesser by bigint, a range search would be faster as there would be less IO activity that will be performed as compared to varchar. I dont think there will be any improvement if you search for specific values because any which ways sql server reads the entire page into the memory containing matching record, even if it has one record or 100 records.
June 15, 2009 at 1:19 pm
Is there a reason you need to consolidate it all into one column and make it into a custom code?
Why not have an int UserID column, a smalldate DateAndTime column, and an int ID column? Then have a calculated column that consolidates them all, but doesn't store it on the disk.
If you want to save some space in that, make the user ID smallint, if you'll never have more than about 32,000 users. (65,000 if you use negative as well as positive numbers.)
From your definition, if the UserID part of the code is limited to 3 digits, then smallint is definitely enough. (If it'll go over 1000 users ever, even possibly, then your proposed solution will have problems when it gets an extra digit.)
I've never yet found a need for a customized ID like what you're proposing. Honestly, int identity has always been adequate and users don't have a problem with it. GUIDs (unique identifier) are good for replication, but are rough on clustered indexes (even newsequentialid has some issues with performance). Either of those is almost certainly going to be adequate to your needs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 15, 2009 at 1:33 pm
Thanks, good response.
The table will suffer deleted records.
I like the idea
One field for UserID (no users 100 to 10000)
another for datetimecalc
Yes maybe I making too complicated, even tho deleted records makes the identity field like swiss cheese, it still works
Question, If I have two fields like UserID and UserGroupID in the groups table, should I join these two together as one primary key ( ie hold the shift key down over 2 fields, and select them as primary key) , would that support a fast search ?
June 15, 2009 at 1:56 pm
Digs (6/14/2009)
Identity and uniqueIdentifier and customidIs there any CustomID method where I can create my own ID for keys in tables.
Sequential ID from identity is ok
The UniqueIdentifier works but is ugly..and not user friendly.
Is there any CustomID process out there ???:-)
UPDATE1: I was thinking something like this...
UserID= 105
Date = 2009/01/22
Time = 06:35:45
ID = 10520090122063545
Save as a bigint
Would this be fast for searching as a primary key (indexed) ??
Or would it be better to save it as a varchar(50) ?
I would never base such a thing on date and time of day. Too big a chance for dupes. Try this and see...
SELECT TOP 1000 GETDATE()
FROM Master.dbo.SysColumns
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2009 at 2:13 pm
You missed my question in my last posting...
June 15, 2009 at 2:24 pm
It doesn't need to be the primary key. Doesn't even need to be the clustered index. Can be an index. It all depends... what do most of the queries need? Only testing will tell.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 15, 2009 at 2:39 pm
Digs (6/15/2009)
Thanks, good response.The table will suffer deleted records.
I like the idea
One field for UserID (no users 100 to 10000)
another for datetimecalc
Yes maybe I making too complicated, even tho deleted records makes the identity field like swiss cheese, it still works
Question, If I have two fields like UserID and UserGroupID in the groups table, should I join these two together as one primary key ( ie hold the shift key down over 2 fields, and select them as primary key) , would that support a fast search ?
I'm not sure how you make a compound key in the table designer. Haven't used the thing in about 8 years. I build tables by typing in the script for it. In that, you just define the PK with two columns (or more), with commas in between.
Don't worry about deleted records in the ID. Doesn't matter. Database doesn't care, you shouldn't care, and if users really care, have the front-end put sequential numbers on the left edge of the list and you'll be just fine.
ID is a good candidate for the leading edge of the primary key and clustered index, if you have a lot of inserts. Date/time data is almost as good, so long as you store milliseconds. Not quite as clean, but almost. If you don't have a lot of sequential inserts, ID might not be as good for the PK or clustered index.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 15, 2009 at 3:02 pm
[font="Verdana"]Okay, I understand that the table will "suffer a lot of deletes". I'm not sure why that requires such a bizarre key format.
Provided you are still getting sequential or unordered inserts, I'd still go with a simple primary key on an identity field. It's simple, it works well. If there's a performance issue in practice, then you can look at tuning the physical design.
No matter what key format you use, you are still going to get allocation spaces from deletes. If it's an issue, use periodic clustered index rebuilds. Don't pervert a design for something that in practice may not even be an issue.
[/font]
June 15, 2009 at 3:26 pm
I'd agree that you shouldn't mess with the design, and I'd also be careful about basing things on date. Dups will occur, and then you'll be in the case where you don't have a custom ID.
What's the point of this if you don't need uniqueness?
The identity and NEWID() are optimized to work quickly and handle loads.
Why would you worry about spaces because of deletes? It seems this question regularly comes up with people, but without a good reason as to why this matters.
June 15, 2009 at 3:39 pm
Date time with milliesecound, that create a great chance of uniqueness, would it not !
condition: You dont have 1000s inserts in a split second.
June 15, 2009 at 3:45 pm
There are absolutely systems with multiple inserts in a split second, and it's not 1000 needed, it's 2 that can cause issues.
I still don't see a reason why identity won't work for you?
June 15, 2009 at 4:01 pm
So why construct the ID in the first place instead of just using the user id and datetime entered as the columns of a primary key ( or just an index)? Are you having some sort of performance problem that the bigint column will help solve? If so, you might want to discribe the problem and let the folks here have a go at providing a solution to it that might not involve building an intelligence-laden key. It's been my experience that keys built this way end up being more trouble than they're worth.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply