| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-10-20 : 13:07:46
|
| I need to uniquely group a set of records that will be inserted into a table. I need a unique identifier, and might just go with GETDATE(). That's pretty unique.But then, there's NEWID() - but NEWID is an unnecessarily large datatype. All I really need is a unique integer value that does not exist in the column, then I'll insert the group of records ala:INSERT INTO MyTable (UniqueID, MyData)SELECT @MyUniqueValue, Mydata FROM dbo.MyOtherTable ...So... all I need is a mechanism to initialize @MyUniqueValue with an integer that IS NOT in the set:SELECT DISTINCT UniqueID FROM dbo.MyTableSam |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-20 : 13:15:01
|
| Can't you just get MAX(UniqueID) and add 1 each time?Tara |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-10-20 : 13:38:50
|
Just go with the GUID column, Sam, its hardly worth the trouble  . I banged my head against the wall trying to think up a solution for a similar situation, and finally settled on NEWID()...it works just fine.Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-10-20 : 14:17:31
|
| Owais - You're probably right, and there may be some elegance to using NEWID, but I'm left feeling NEWID is overkill. Sort of like cracking a nut with a sledgehammer. It may be the best solution. How many bytes does NEWID take?I'm leaning to Tara's solution, which would've bitten me before I saw it. The only caution with this approach is ensuring two processes don't come up with the same MAX(UniqueID) at the same moment before following with a subsequent insertion of MAX+1. It would result in two insertions with the same UniqueID. Is there a straightfoward fix to resolve this race condition?Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-10-20 : 14:17:36
|
| duplicate entry. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-20 : 14:20:31
|
quote:
Originally posted by SamC
Is there a straightfoward fix to resolve this race condition?Sam
Yes, you'll need to provide locking hints. HOLDLOCK is the hint that you'd need. Or SET TRANSACTION ISOLATION LEVEL SERIALIZABLETara |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-10-20 : 14:35:48
|
| SELECT @MyUniqueValue = IsNull(MAX(UniqueID), 0) + 1 FROM dbo.MyTable WITH (HOLDLOCK)It's not too complex, and keeps the UniqueID as a simple integer.Thanks Owais and Tara.Sam |
|
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2003-10-20 : 14:42:54
|
You could always use a "TakeANumber" tableCREATE TABLE [tblTakeANumber] ( [TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [NextID] [int] NOT NULL CONSTRAINT [DF_tblTakeANumber_NextID] DEFAULT (1), CONSTRAINT [PK_tblTakeANumber] PRIMARY KEY CLUSTERED ( [TableName] ) WITH FILLFACTOR = 90 ON [PRIMARY] ) Whenever you need a unique ID, you can just select from this tiny table, and update the NextID incrementing it by 1. This will eliminate looking for the Max across a whole table.Steve |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-20 : 14:48:29
|
quote:
Originally posted by Blastrix
Whenever you need a unique ID, you can just select from this tiny table, and update the NextID incrementing it by 1. This will eliminate looking for the Max across a whole table.
I hate that method. We've got a couple of legacy apps that do this. The reason why they do this though is because they used to use a FoxPro back end which didn't have the IDENTITY option. Now they are on SQL Server, but we don't have the time to modify the apps for IDENTITY or for a better primary key if possible.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-20 : 15:16:29
|
| Does this "group" have a relationship to a parental table?Sounds like a good canidate for one,Just use the paental row's (IDENTITY) colum as the key.It'll allow you to tack things about the group (like inserted date, ect) and not need to keep them on every row in the group....Brett8-) |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-10-20 : 15:39:43
|
| The Group of records represents a 'group' of emails sent by a course administrator to a group of students. Having a unique ID allows me to pull up the entire recordset given any single email in the group.There's no parental relationship anywhere really. The datetime is good enough really, and NEWID would work too. Honestly, I was just stumped at why I couldn't come up with a simple integer until Tara came up with a technique.Sam |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-20 : 16:20:44
|
quote:
Originally posted by SamC
The Group of records represents a 'group' of emails sent by a course administrator to a group of students.
But won't you need something to be used to look why a group of email's where sent, or by whom, or on what day, or for what department?Brett8-) |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-10-20 : 16:31:00
|
| Yes. We've got the AdminID, Department and all that. But these emails get repetitive. Some other UniqueID was needed, like the DateTime or a unique Integer.The grouping is for historic retrieval purposes only.I'm going to have a martini now.Sam |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-20 : 17:03:20
|
quote:
Originally posted by SamC
I'm going to have a martini now.
I don't mean for a key...are you going to store data redundantly?You could have a parent table, store that info, use identity to log the fact that your doing a mailing then use that for the grouping...all nicely tied up in to one relational package.Make mine a doubleBrett8-) |
|
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2003-10-20 : 17:25:55
|
quote:
Originally posted by tduggan
I hate that method. We've got a couple of legacy apps that do this. The reason why they do this though is because they used to use a FoxPro back end which didn't have the IDENTITY option. Now they are on SQL Server, but we don't have the time to modify the apps for IDENTITY or for a better primary key if possible.Tara
I'm aware of why it was done in the past, but doesn't it seem like this would fit Sam's needs? It seems to me like it is not possible for him to use the IDENTITY option. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-10-20 : 17:45:00
|
| Yes it would work for Sam, but he has other options.Tara |
|
|
|
|
Unique Integer