Best GUID Storage

Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?

Best GUID Storage

Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?If you are storing a GUID, then why not use Uniqueidentifier data type.
In SQL Server Books Online, read the page titled "Using uniqueidentifier
Data". This page discusses the advantages and disadvantages of this
datatype.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
Because of the problem getting IDENTITY primary key values back when
inserting batches of rows, I would like to experiment with using
GUIDs. Within an application, I would like to assign the primary keys
to the rows and then pass them into the INSERT statements. Then I
wouldn't have to worry about using triggers or Identity scope to
determine the new primary keys.
My question is basically, what's the best datatype to store the GUIDs
in the column? From what I've read so far, it looks like
UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
using UniqueIdentifier?|||TW,
I've used both, and it nearly always comes down to interoperability.
Some systems cannot deal with binary so you have to go varchar(36)/char(36).
Where did you get 40, incidentally?
James Hokes
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?|||Excellent choice to use Guids, imho.
Vyas has already pointed you to a good article on the topic, but here are a
couple of extra things not mentioned in that article:
(a) A benefit of using Guids instead of Identities is that if you ever need
to implement horizontal partitioning on the table, it will be substantially
easier with Guids. With Guids, the partitioning process is virtually
seemless to the application but partitioning tables with Identities nearly
always breaks the application.
(b) On the other hand, a problem with using Guids which is not mentioned in
that article is that T-SQL has no ISGUID() type function which causes minor
coding issues. Of course, it's possible to roll your own though.
Regards,
Greg Linwood
SQL Server MVP
"- TW" <Thumper@.kqrsrocks.com> wrote in message
news:151a6e6b.0402231323.29f24d45@.posting.google.com...
> Because of the problem getting IDENTITY primary key values back when
> inserting batches of rows, I would like to experiment with using
> GUIDs. Within an application, I would like to assign the primary keys
> to the rows and then pass them into the INSERT statements. Then I
> wouldn't have to worry about using triggers or Identity scope to
> determine the new primary keys.
> My question is basically, what's the best datatype to store the GUIDs
> in the column? From what I've read so far, it looks like
> UniqueIdentifier or CHAR(40) are my options. Is there any drawback to
> using UniqueIdentifier?

BEGINNER'S QUESTION: SORTING IN ALPHABETICAL ORDER

Hi

I am trying to convert an Access back end into a SQL Server back end and cannot work out how to convert a query that sorts addresses that start with numbers into alphabetical order.

In Access I defined a function that converted the full address to one without the initial numbers eg:
66 Acacia Gardens to Acacia Gardens

I then used this function as an expression in the field column of the query and set it to sort by this field. This put the rows into alphabetical order. (A second function was used to return just the number which was used as the second sort).

I am converting the Access query into a stored procedure. I have successfully converted the Access number removal function into a separate stored procedure and I was intending to execute it from within the main stored procedure but I cannot work out how to do this. I don't think you can insert an Execute statement into a field as you can with a function in Access.

Code for working number removal stored procedure:

ALTER PROCEDURE spRemoveInitialNumbers
@.JobName varchar(20), @.FirstNumbersRemoved varchar(20) OUTPUT
As
DECLARE @.Chopped varchar(20)
DECLARE @.CharacterCount tinyint
DECLARE @.CharacterCode tinyint
SET @.Chopped = @.JobName
IF Substring(@.Chopped,1,5) = 'Unit '
Begin
SET @.Chopped = Right(@.Chopped, Len(@.Chopped)-5)
End
ELSE
SET @.CharacterCount = 1
WHILE @.CharacterCount<=Len(@.Chopped)
BEGIN
SET @.CharacterCode=Ascii(Substring(@.Chopped,@.CharacterCount,1))
If @.CharacterCode>64 and @.CharacterCode<91
Begin
SET @.Chopped=Right(@.Chopped, Len(@.Chopped) - @.CharacterCount + 1)
Break
End
Else
Set @.CharacterCount = @.CharacterCount + 1
End
SET @.FirstNumbersRemoved=@.Chopped

Code for non-working main stored procedure:

SELECT dbo.tblJobs.JobID, dbo.tblJobs.JobNumber, dbo.tblJobs.Surveyor, dbo.tblClients.ClientName, dbo.tblJobs.JobName, dbo.tblJobs.Description,
dbo.tblJobs.Status, dbo.tblJobs.UrgencyCode, dbo.tblJobs.EstimatedFeesOutstanding, dbo.tblJobs.[Live/Dead],
[Intended to Execute SpRemoveInitialNumbers here] AS Expr1,
SUM(dbo.tblFeeAccounts.TotalLessVATandExpenses - dbo.tblFeeAccounts.SubContactors) AS TotalNet, dbo.tblJobs.InstructionDate
FROM dbo.tblJobs INNER JOIN
dbo.tblClients ON dbo.tblJobs.ClientID = dbo.tblClients.ClientID LEFT OUTER JOIN
dbo.tblFeeAccounts ON dbo.tblJobs.JobID = dbo.tblFeeAccounts.JobID
GROUP BY dbo.tblJobs.JobNumber, dbo.tblJobs.Surveyor, dbo.tblClients.ClientName, dbo.tblJobs.JobName, dbo.tblJobs.Description, dbo.tblJobs.Status,
dbo.tblJobs.UrgencyCode, dbo.tblJobs.EstimatedFeesOutstanding, dbo.tblJobs.[Live/Dead], dbo.tblJobs.InstructionDate, dbo.tblJobs.JobID
HAVING (dbo.tblJobs.[Live/Dead] = N'D')
ORDER BY dbo.tblClients.ClientName, dbo.tblJobs.JobName

I would like to undertake the sort on the server, not client side hence my attempt to use stored procedures. I'm not sure that I am going about this the right way. Can anyone help me?

Many thanks.

Whitebeam

Please put the logic of SpRemoveInitialNumbers SP into a TSQL scalar user-defined function. You can then call it from the SELECT list. Alternatively, you can also embed the required substring / charindex / patindex expression in the SELECT list itself for better performance. ( I am assuming you use such functions to remove the initial numbers).

BEGIN TRANSACTION COMMIT TRANSACTION help

I had thought that if any statement failed within a BEING TRANS .. COMMIT TRANS block, then all the statements would be rolled back. But I am seeing different behavior (SQL Server 2000 8.00.2039)

For instance, run these statements to set up a test:
--DROP TABLE testTable1
--DROP TABLE testTable2
CREATE TABLE testTable1 (f1 varchar(1))
CREATE TABLE testTable2 (f1 varchar(1))
CREATE UNIQUE INDEX idx_tmptmp ON testTable1 (f1)
insert into testTable1(f1) values ('a')

So table testTable1 has a unique index on it..

Now try to run these statements:

--DELETE FROM testTable2
BEGIN TRANSACTION
insert into testTable1(f1) values ('a')
insert into testTable2(f1) values ('a')
COMMIT TRANSACTION

SELECT * FROM testTable2

..the first insert fails on the unique index.. but the second insert succeeds. Shouldn't the second insert roll back? How can I make two operations atomic?in order to roll back a transaction, you have to specifically issue the ROLLBACK command. usually after checking the error status of each operation that matters.|||Huh. That sort of stinks. I had thought an error that occurred within an explicit transaction would automatically rollback the transaction. Guess not...

So, I have modified the test as follows and it works:

DECLARE @.errorHolder int
SELECT @.errorHolder = 0
--DELETE FROM testTable2
BEGIN TRANSACTION aaa
insert into testTable1(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
insert into testTable2(f1) values ('a')
SELECT @.errorHolder = @.errorHolder + @.@.ERROR
IF @.errorHolder > 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SELECT * FROM testTable2

Is there an easier way|||Use
SET XACT_ABORT ON
to automatically roll back a transaction if a run-time error is raised in the TSQL statements.|||thanks kaffenils, that's much easier.|||I prefer to try and catch my errors withg error handling and then I have control over my logic

BUT DDL?|||I prefer to try and catch my errors withg error handling and then I have control over my logic

I agree that TRY...CATCH is preferrable in SQL Server 2005 as it gives you more control, but craigmc is using SQL Server 2000. It is time consuming work to use @.@.ERROR if the only purpose is to rollback the transaction. XACT_ABORT could provide a qiuick and dirty solution if handling an error only means rolling back the transaction.|||Time consuming? That's your justification?!
Oh dear...|||Time consuming? That's your justification?!
Oh dear...

Did you even read what I wrote? Have I said not to use TRY...CATCH or @.@.ERROR if you need more control of exceptions?
"Oh Dear" :shocked:

All I said was that if you have multiple DML statements (and you're running on SQL Server 2000) and all you need to do if one of them fails is rollback, then it is easier to SET XACT_ABORT ON than to writing multiple
IF @.@.ERROR<>0 BEGIN ... END or [GOTO abc
On SQL Server 2005 I would always use TRY...CATCH, except for my own simple one-time ad-hoc DMLs. Then XACT_ABORT will do the job good enough.

Before Delete Trigger?

I have a database that will be used as the back end of a distributed application that holds information based on application users. The application needs to log on and see if there are updates for the user. My current thoughts on this are that the application will log in and check a Date column named [modified] in the users table (I am not worried about what individual changes have occured, but more if anything has changed). To implement this I have now put Insert, Update triggers that use the tables relationships to track which users need to be updated on the tables that need to be watched... they look something like this:

CREATE TRIGGER MOD_UP_INS_GROUPS
ON dbo.Groups
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE @.IDVar1 as int
SET @.IDVar1 = (SELECT GroupID FROM inserted)
UPDATE Users
SET Modified = GetDate()
WHERE (AccountName IN
(SELECT DISTINCT dbo.Users.AccountName
FROM dbo.Groups INNER JOIN
dbo.GroupUserDetail ON dbo.Groups.GroupID = dbo.GroupUserDetail.GroupID INNER JOIN
dbo.Users ON dbo.GroupUserDetail.AccountName = dbo.Users.AccountName
WHERE (dbo.Groups.GroupID = @.IDVar1)))

This appears to be working great... however the Delete Trigger is where my problems start... I can not use the above trigger (with deleted in place of inserted) because it appears the delete action takes place prior to the Delete Trigger and with the referential deletes, etc. The path to the user is lost before I can track it with the delete trigger. Is there a way to make a BEFORE DELETE TRIGGER... or any other thoughts would be helpfull.

Thank You,
KentDo Instead of Trigger.|||Not sure I'm following...but you can code an INSTEAD OF trigger...look it up in BOL...|||I tried a Instead of Trigger but I had the following error:

Cannot ALTER INSTEAD OF DELETE or UPDATE TRIGGER 'MY TRIGGER NAME' on table 'dbo.Group' because the table has a FOREIGN KEY with cascaded DELETE or UPDATE.

Doing a little reading I have found I can't define this trigger on tables with foreign key relationships with cascading deletes.

The other problem I thought of with this type of trigger... is how do you let an outside programs sql delete requests continue with this trigger?|||Well...can you simply explain what your goal is, in business terms...

I'm having a hard time seeing what you're trying to do..

Also, you got other problems

SET @.IDVar1 = (SELECT GroupID FROM inserted)

You do know that inserted may have many rows...so doing that will give you the last value in the result set...|||First of all thanks for your replies:

The Database is the backend of a Client-Server Application that assigns Startup Scripts for Company Programs to individual users within the company (Based on Windows Login Names that are stored in the users table). It does this in a method similar to the Windows Server Environment where you assign individual Company Programs to a Group then assign a group to a user or a user to a group.

The front end that the users see needs to be able to connect to the database and determine if anything has changed, and if so, update itself with the new settings whether it be the user has been added\deleted from a group or if an actual Company Program Startup Script has changed.

My attempted solution to this problem was to create a modified column in the users table. When a table is modified that effects a user or users the modified column for the user or users in question would be updated with the current date (getdate()). The front end then accessess the database and compares it's last updated date with the date in the users.modified column to determine if it needs to update. With the update, Insert Triggers I have been able to accomplish this very nice. However, the delete trigger causes problems because lets say a group is deleted... the group is deleted then referential updates delete the users who where assigned to that group in a groupdetails table then I am unable to track which users need to be modified...

The front end does not contain a database... but rather stores items in an ini file and the registry, therefore replication is not an option. The other thing is clients are not always connected to the network so the settings are stored on the local machine for the individual users.

I hope this is clearer... thanks again.