Membership User ID is uniqueidentifier, could I use INT and map theto fields?

  • Thread starter Thread starter DotNetNewbie
  • Start date Start date
D

DotNetNewbie

Hi,

I am developing an application that has to scale and be very
efficient, and I am using asp.net membership in my application.

I set things up in my Users table (it has extra columns that I need
over and above what aspnet_users has):

UserID INT
Membershipuserid uniqueidentifier


Now I have tables that will hold 10K+ rows to even 1 million rows, and
these tables have to be INNER JOINED with the Users table.

Doing joins on a INT should be much faster than on a uniqueidentifier
data type right?

Is there a way I could use my own INT ID field in my users table
(while still storing the aspnet_users unique identifier in my table
also) and just map the 2 when needed?

***If I do this, will my application still be able to do all the
things asp.net membership is capable of, like mulitiple applications
sharing the users etc?
 
Technically, no, there isn't a way that you can do this. The number of
possible values for a GUID is 2^128, while the number of possible values for
a 32-bit integer is 2^32. You will run out of integers before you run out
of GUIDs.

Granted, you can say that you will never have more than 2^32 records in
the table. In that case, feel free to use a secondary id which is an
integer (it should have a unique index on it, of course).

The issue you have now is the mapping between the GUID and the integer.
Since GUIDs are not generated sequentially, you have to have some sort of
lookup which is going to be performed. Also, there is the issue of
generating the id. You have to generate the id at the end of any add
operation, and you will have to serialize access to the generation of the id
(or you will have gaps), and this can lead to locking issues.

Generally speaking, I don't know that going to an integer is the best
solution here. Have you done some performance testing to verify that the
difference between comparing GUIDs and ints is that much of a difference?
I'm just guessing here, but if you are running SQL Server on a 64 bit
machine (which anyone who is serious about running SQL server ^should^ be
doing) it might not be as drastic of a difference than compared to doing the
same comparison on a 32 bit machine.
 
    Technically, no, there isn't a way that you can do this.  The number of
possible values for a GUID is 2^128, while the number of possible values for
a 32-bit integer is 2^32.  You will run out of integers before you run out
of GUIDs.

    Granted, you can say that you will never have more than 2^32 records in
the table.  In that case, feel free to use a secondary id which is an
integer (it should have a unique index on it, of course).

    The issue you have now is the mapping between the GUID and the integer.
Since GUIDs are not generated sequentially, you have to have some sort of
lookup which is going to be performed.  Also, there is the issue of
generating the id.  You have to generate the id at the end of any add
operation, and you will have to serialize access to the generation of the id
(or you will have gaps), and this can lead to locking issues.

    Generally speaking, I don't know that going to an integer is the best
solution here.  Have you done some performance testing to verify that the
difference between comparing GUIDs and ints is that much of a difference?
I'm just guessing here, but if you are running SQL Server on a 64 bit
machine (which anyone who is serious about running SQL server ^should^ be
doing) it might not be as drastic of a difference than compared to doing the
same comparison on a 32 bit machine.

--
          - Nicholas Paldino [.NET/C# MVP]
          - (e-mail address removed)




Hi,
Click to expand...
I am developing an application that has to scale and be very
efficient, and I am using asp.net membership in my application.
Click to expand...
I set things up in my Users table (it has extra columns that I need
over and above what aspnet_users has):
Click to expand...
UserID    INT
Membershipuserid uniqueidentifier
Click to expand...
Now I have tables that will hold 10K+ rows to even 1 million rows, and
these tables have to be INNER JOINED with the Users table.
Click to expand...
Doing joins on a INT should be much faster than on a uniqueidentifier
data type right?
Click to expand...
Is there a way I could use my own INT ID field in my users table
(while still storing the aspnet_users unique identifier in my table
also) and just map the 2 when needed?
Click to expand...
***If I do this, will my application still be able to do all the
things asp.net membership is capable of, like mulitiple applications
sharing the users etc?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

This is what I planned on doing:

1. whenever someone registers to my application using asp.net
memership (writing to aspnet_users, aspnet_members), if the insert
passes, I will do another insert into my own Users table and pass
along the uniqueidentifier that asp.net-membership created.

This seems to work just fine, the only thing I'm worried about is, say
someone wants to integrate another web application with mine, and they
also are using asp.net membership. Will my 'hack' ruin the membership
sharing capability that asp.net membership has??
 
The default schema for the ASPNET_Users table maps the UserId
(uniqueidentifier) as the primary key for the table. If you start adding
additional columns such as your Int column, any joins you do are going to be
dependent on that primary key, which is nonclustered in the default scenario.
As long as you leave this arrangement alone, and add your int column at the
end of the table, you should be OK.

However the first thing I'd do is make some speed tests on joins first,
before adding the extra baggage. You might be surprised to find out that a
uniqueidentifier primary key that is nonclustered will join quite nicely.
-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com
 
I agree with Peter and think you're fearing the uniqueidentifier too much.

...........

Some of the articles on the web are a little dated. Testing is a good idea.

Here is a "trick" as well.

You can rewrite the uniqueidentifier's ... to be sequential, even if they
don't start out that way:

The example does not have a FK....(the below example).
I wrote one up for that scenario, but can't find it.

But basically, I dropped the constraint (FK)
Did the "magic" below.
Re-add the constraint (FK).




------------START TSQL

/*
The below example show how you might re-order data
using the new 2005 NEWSEQUENTIALID datatype.

Naturally, you can always use an 'Order By' clause in your sql statement,
but sometimes this may incurr to large a penalty.
(Think of a 9million row table, and you only want a TOP 100, if you add the
Order By clause, the rdbms needs to look at all 9million rows).

While it might be for a rare use, below is a slight trick for reordering the
data for a "good enough" scenario.

Note that the only true way to guarantee a sort by order is to specify it.
But this can work for a "good enough" scenario.



*/

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Patient]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DROP TABLE [dbo].[Patient]
END
GO


CREATE TABLE [dbo].[Patient] (
[PatientUUID] [uniqueidentifier] primary key not null default
NEWSEQUENTIALID() ,
PatientLastName varchar(64) not null ,
PatientFirstName varchar(64) not null ,
DateOfBirth smalldatetime not null ,
AdmittedDate smalldatetime not null ,
CustomerRating smallint not null default 0 CHECK(CustomerRating > 0 AND
CustomerRating < 6)
--CONSTRAINT Pat_PatName_UNIQUE UNIQUE ([PatientUUID] , PatientName)
)
GO






set nocount on
delete from dbo.Patient

INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )
values ( 'Einstein' , 'Roger' , '05/05/1975' , '02/02/1995' , 3 )
INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )
values ( 'Banana' , 'Ulysses' , '05/05/1972' , '02/02/1998' , 1 )
INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )
values ( 'Donner' , 'Samantha' , '05/05/1974' , '02/02/1996' , 2 )
INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )
values ( 'Cucumber' , 'Tommy' , '05/05/1973' , '02/02/1997' , 4 )
INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )
values ( 'Apple' , 'Vicky' , '05/05/1971' , '02/02/1999' , 5 )

print 'Should be How They Were Entered'
select * from dbo.Patient

declare @reorder1 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )
insert into @reorder1 ( OldPatientUUID )
Select PatientUUID from dbo.Patient ORDER BY CustomerRating
--select * from @reorder1
BEGIN TRAN
Update dbo.Patient
Set PatientUUID = ro.NewPatientUUID
From
dbo.Patient p , @reorder1 ro
Where
p.PatientUUID = OldPatientUUID
COMMIT TRAN
print 'Should be CustomerRating'
select CustomerRating , * from dbo.Patient
------------------

declare @reorder2 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )
insert into @reorder2 ( OldPatientUUID )
Select PatientUUID from dbo.Patient ORDER BY AdmittedDate
--select * from @reorder2
BEGIN TRAN
Update dbo.Patient
Set PatientUUID = ro.NewPatientUUID
From
dbo.Patient p , @reorder2 ro
Where
p.PatientUUID = OldPatientUUID
COMMIT TRAN
print 'Should be AdmittedDate'
select AdmittedDate , * from dbo.Patient

------------------

declare @reorder3 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )
insert into @reorder3 ( OldPatientUUID )
Select PatientUUID from dbo.Patient ORDER BY DateOfBirth
--select * from @reorder2
BEGIN TRAN
Update dbo.Patient
Set PatientUUID = ro.NewPatientUUID
From
dbo.Patient p , @reorder3 ro
Where
p.PatientUUID = OldPatientUUID
COMMIT TRAN
print 'Should be DateOfBirth'
select DateOfBirth , * from dbo.Patient

----------------------

declare @reorder4 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )
insert into @reorder4 ( OldPatientUUID )
Select PatientUUID from dbo.Patient ORDER BY CustomerRating
--select * from @reorder1
BEGIN TRAN
Update dbo.Patient
Set PatientUUID = ro.NewPatientUUID
From
dbo.Patient p , @reorder4 ro
Where
p.PatientUUID = OldPatientUUID
COMMIT TRAN
print 'Should be CustomerRating'
select CustomerRating , * from dbo.Patient
------------------

declare @reorder5 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )
insert into @reorder5 ( OldPatientUUID )
Select PatientUUID from dbo.Patient ORDER BY PatientLastName
--select * from @reorder1
BEGIN TRAN
Update dbo.Patient
Set PatientUUID = ro.NewPatientUUID
From
dbo.Patient p , @reorder5 ro
Where
p.PatientUUID = OldPatientUUID
COMMIT TRAN
print 'Should be PatientLastName'
select PatientLastName , * from dbo.Patient
------------------

declare @reorder6 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )
insert into @reorder6 ( OldPatientUUID )
Select PatientUUID from dbo.Patient ORDER BY PatientFirstName
--select * from @reorder1
BEGIN TRAN
Update dbo.Patient
Set PatientUUID = ro.NewPatientUUID
From
dbo.Patient p , @reorder6 ro
Where
p.PatientUUID = OldPatientUUID
COMMIT TRAN
print 'Should be PatientFirstName'
select PatientFirstName , * from dbo.Patient
------------------


--END TSQL
 
I found my "with FK" example:


This works as is.

You can also read all my inline notes about
ON UPDATE CASCADE
and you can optional use that method instead.



My point is that if you don't like the randomness of NEWID (or UserId in
Membership) there are some things you can...
that will still leave the Membership tables alone and AS IS.




if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[HospitalVisit]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)

BEGIN

DROP TABLE [dbo].[HospitalVisit]

END

GO





if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Patient]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

BEGIN

DROP TABLE [dbo].[Patient]

END

GO




CREATE TABLE [dbo].[Patient] (

[PatientUUID] [uniqueidentifier] primary key not null default NEWID()
,--<<Intentionally a NEWID to "random it up" some --NEWSEQUENTIALID() ,

PatientLastName varchar(32) not null ,

PatientFirstName varchar(32) not null ,

DateOfBirth smalldatetime not null ,

SignupDate smalldatetime not null default CURRENT_TIMESTAMP ,

AdmittedDate smalldatetime not null ,



CustomerRating smallint not null default 0 CHECK(CustomerRating > 0 AND
CustomerRating < 6)

--CONSTRAINT Pat_PatName_UNIQUE UNIQUE ([PatientUUID] , PatientName)

)

GO









CREATE TABLE [dbo].[HospitalVisit] (

[HospitalVisitUUID] [uniqueidentifier] primary key not null default
NEWSEQUENTIALID() ,

HospitalVisitKey smallint not null ,

[PatientUUID] [uniqueidentifier] not null , --Moved to be a ADD CONSTRAINT
so it could have a Hard Coded Name -- FOREIGN KEY (PatientUUID) REFERENCES
dbo.Patient(PatientUUID),

HospitalVisitName varchar(32) not null ,

HospitalVisitDescription varchar(128) not null ,



CONSTRAINT HospitalVisit_HospitalVisitName_UNIQUE UNIQUE (PatientUUID ,
HospitalVisitName) ,

CONSTRAINT HospitalVisit_HospitalVisitKey_UNIQUE UNIQUE (HospitalVisitKey)



)



ALTER TABLE dbo.[HospitalVisit]

ADD CONSTRAINT FK_HV_To_Patient_PatientUUID

FOREIGN KEY ( PatientUUID )

REFERENCES dbo.Patient ( PatientUUID )

--ON UPDATE CASCADE -- << Comment out this line to experiment



GO

/*

IF EXISTS (SELECT name FROM sysindexes WHERE name = 'PK_Patient')

DROP INDEX Patient.PK_Patient

CREATE UNIQUE CLUSTERED INDEX PK_Patient ON [dbo].Patient(PatientUUID)

G--O

*/







IF EXISTS (SELECT name FROM sysindexes WHERE name =
'IX_HospitalVisit_PatientUUID')

DROP INDEX HospitalVisit.IX_HospitalVisit_PatientUUID

CREATE INDEX IX_HospitalVisit_PatientUUID ON
[dbo].HospitalVisit([PatientUUID])

GO





GRANT SELECT , INSERT, UPDATE, DELETE ON dbo.Patient TO
hospitaluser--[[[[DBUSERNAME]]]]

GO



GRANT SELECT , INSERT, UPDATE, DELETE ON dbo.HospitalVisit TO
hospitaluser--[[[[DBUSERNAME]]]]

GO













set nocount on

delete from dbo.Patient



INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )

values ( 'Einstein' , 'Roger' , '05/05/1975' , '02/02/1995' , 3 )

INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )

values ( 'Banana' , 'Ulysses' , '05/05/1972' , '02/02/1998' , 1 )

INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )

values ( 'Donner' , 'Samantha' , '05/05/1974' , '02/02/1996' , 2 )

INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )

values ( 'Cucumber' , 'Tommy' , '05/05/1973' , '02/02/1997' , 4 )

INSERT INTO dbo.Patient ( PatientLastName , PatientFirstName , DateOfBirth ,
AdmittedDate , CustomerRating )

values ( 'Apple' , 'Vicky' , '05/05/1971' , '02/02/1999' , 5 )













INSERT INTO dbo.HospitalVisit

(HospitalVisitKey,PatientUUID,HospitalVisitName,HospitalVisitDescription)

Select DENSE_RANK() OVER (ORDER BY CustomerRating ASC) AS ROWID ,

PatientUUID , 'Visit For ' + PatientLastName + ', ' + PatientFirstName ,
PatientFirstName + ' enjoyed his/her time at the hospital'

from dbo.Patient p Group By PatientUUID , PatientLastName , PatientFirstName
, CustomerRating



Select * from dbo.HospitalVisit

print ''





print 'Before any reordering'

select p.PatientUUID , derived1.HospitalVisitName , * from dbo.Patient p
join

( Select * from dbo.HospitalVisit hv ) as derived1

on p.PatientUUID = derived1.PatientUUID

declare @reorder1 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )

insert into @reorder1 ( OldPatientUUID )

Select PatientUUID from dbo.Patient ORDER BY CustomerRating

--select * from @reorder1

BEGIN TRAN

--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this DROP
CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

DROP CONSTRAINT FK_HV_To_Patient_PatientUUID



Update dbo.Patient

Set PatientUUID = ro.NewPatientUUID

From

dbo.Patient p , @reorder1 ro

Where

p.PatientUUID = OldPatientUUID



Update dbo.HospitalVisit

Set PatientUUID = ro.NewPatientUUID

FROM

dbo.HospitalVisit hv , @reorder1 ro

Where

hv.PatientUUID = ro.OldPatientUUID



--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this (re)
ADD CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

ADD CONSTRAINT FK_HV_To_Patient_PatientUUID

FOREIGN KEY ( PatientUUID )

REFERENCES dbo.Patient ( PatientUUID )



COMMIT TRAN

print 'The PatientUUID PK should be in order based on the : CustomerRating'

select CustomerRating , derived1.HospitalVisitName , * from dbo.Patient p
join

( Select * from dbo.HospitalVisit hv ) as derived1

on p.PatientUUID = derived1.PatientUUID

Order by p.CustomerRating

------------------



declare @reorder2 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )

insert into @reorder2 ( OldPatientUUID )

Select PatientUUID from dbo.Patient ORDER BY AdmittedDate

--select * from @reorder2

BEGIN TRAN

--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this DROP
CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

DROP CONSTRAINT FK_HV_To_Patient_PatientUUID







Update dbo.Patient

Set PatientUUID = ro.NewPatientUUID

From

dbo.Patient p , @reorder2 ro

Where

p.PatientUUID = OldPatientUUID

Update dbo.HospitalVisit

Set PatientUUID = ro.NewPatientUUID

FROM

dbo.HospitalVisit hv , @reorder2 ro

Where

hv.PatientUUID = ro.OldPatientUUID



--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this (re)
ADD CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

ADD CONSTRAINT FK_HV_To_Patient_PatientUUID

FOREIGN KEY ( PatientUUID )

REFERENCES dbo.Patient ( PatientUUID )



COMMIT TRAN

print 'The PatientUUID PK should be in order based on the : AdmittedDate'

select AdmittedDate , derived1.HospitalVisitName , * from dbo.Patient p join

( Select * from dbo.HospitalVisit hv ) as derived1

on p.PatientUUID = derived1.PatientUUID

Order by p.AdmittedDate

------------------



declare @reorder3 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )

insert into @reorder3 ( OldPatientUUID )

Select PatientUUID from dbo.Patient ORDER BY DateOfBirth

--select * from @reorder2

BEGIN TRAN

--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this DROP
CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

DROP CONSTRAINT FK_HV_To_Patient_PatientUUID





Update dbo.Patient

Set PatientUUID = ro.NewPatientUUID

From

dbo.Patient p , @reorder3 ro

Where

p.PatientUUID = OldPatientUUID



Update dbo.HospitalVisit

Set PatientUUID = ro.NewPatientUUID

FROM

dbo.HospitalVisit hv , @reorder3 ro

Where

hv.PatientUUID = ro.OldPatientUUID



--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this (re)
ADD CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

ADD CONSTRAINT FK_HV_To_Patient_PatientUUID

FOREIGN KEY ( PatientUUID )

REFERENCES dbo.Patient ( PatientUUID )





COMMIT TRAN

print 'The PatientUUID PK should be in order based on the : DateOfBirth'

select DateOfBirth , derived1.HospitalVisitName , * from dbo.Patient p join

( Select * from dbo.HospitalVisit hv ) as derived1

on p.PatientUUID = derived1.PatientUUID

Order by p.DateOfBirth









----------------------



declare @reorder4 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )

insert into @reorder4 ( OldPatientUUID )

Select PatientUUID from dbo.Patient ORDER BY CustomerRating

--select * from @reorder1

BEGIN TRAN

--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this DROP
CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

DROP CONSTRAINT FK_HV_To_Patient_PatientUUID







Update dbo.Patient

Set PatientUUID = ro.NewPatientUUID

From

dbo.Patient p , @reorder4 ro

Where

p.PatientUUID = OldPatientUUID

Update dbo.HospitalVisit

Set PatientUUID = ro.NewPatientUUID

FROM

dbo.HospitalVisit hv , @reorder4 ro

Where

hv.PatientUUID = ro.OldPatientUUID

--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this (re)
ADD CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

ADD CONSTRAINT FK_HV_To_Patient_PatientUUID

FOREIGN KEY ( PatientUUID )

REFERENCES dbo.Patient ( PatientUUID )



COMMIT TRAN

print 'The PatientUUID PK should be in order based on the : CustomerRating'

select CustomerRating , derived1.HospitalVisitName , * from dbo.Patient p
join

( Select * from dbo.HospitalVisit hv ) as derived1

on p.PatientUUID = derived1.PatientUUID

Order by p.CustomerRating

------------------



declare @reorder5 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )

insert into @reorder5 ( OldPatientUUID )

Select PatientUUID from dbo.Patient ORDER BY PatientLastName

--select * from @reorder1

BEGIN TRAN



--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this DROP
CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

DROP CONSTRAINT FK_HV_To_Patient_PatientUUID







Update dbo.Patient

Set PatientUUID = ro.NewPatientUUID

From

dbo.Patient p , @reorder5 ro

Where

p.PatientUUID = OldPatientUUID



Update dbo.HospitalVisit

Set PatientUUID = ro.NewPatientUUID

FROM

dbo.HospitalVisit hv , @reorder5 ro

Where

hv.PatientUUID = ro.OldPatientUUID





--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this (re)
ADD CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

ADD CONSTRAINT FK_HV_To_Patient_PatientUUID

FOREIGN KEY ( PatientUUID )

REFERENCES dbo.Patient ( PatientUUID )

COMMIT TRAN

print 'The PatientUUID PK should be in order based on the : PatientLastName'

select PatientLastName , derived1.HospitalVisitName , * from dbo.Patient p
join

( Select * from dbo.HospitalVisit hv ) as derived1

on p.PatientUUID = derived1.PatientUUID

Order by p.PatientLastName

------------------



declare @reorder6 table ( OldPatientUUID [uniqueidentifier] , NewPatientUUID
[uniqueidentifier] not null default NEWSEQUENTIALID() )

insert into @reorder6 ( OldPatientUUID )

Select PatientUUID from dbo.Patient ORDER BY PatientFirstName

--select * from @reorder1

BEGIN TRAN

--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this DROP
CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

DROP CONSTRAINT FK_HV_To_Patient_PatientUUID





Update dbo.Patient

Set PatientUUID = ro.NewPatientUUID

From

dbo.Patient p , @reorder6 ro

Where

p.PatientUUID = OldPatientUUID



Update dbo.HospitalVisit

Set PatientUUID = ro.NewPatientUUID

FROM

dbo.HospitalVisit hv , @reorder6 ro

Where

hv.PatientUUID = ro.OldPatientUUID

--If you keep the ""ON UPDATE CASCADE"" from above...you can skip this (re)
ADD CONSTRAINT

ALTER TABLE dbo.[HospitalVisit]

ADD CONSTRAINT FK_HV_To_Patient_PatientUUID

FOREIGN KEY ( PatientUUID )

REFERENCES dbo.Patient ( PatientUUID )



COMMIT TRAN

print 'The PatientUUID PK should be in order based on the :
PatientFirstName'

select PatientFirstName , derived1.HospitalVisitName , * from dbo.Patient p
join

( Select * from dbo.HospitalVisit hv ) as derived1

on p.PatientUUID = derived1.PatientUUID

Order by p.PatientFirstName


------------------
 
The default schema for the ASPNET_Users table maps the UserId
(uniqueidentifier) as the primary key for the table. If you start adding
additional columns such as your Int column, any joins you do are going to be
dependent on that primary key, which is nonclustered in the default scenario.
As long as you leave this arrangement alone, and add your int column at the
end of the table, you should be OK.

However the first thing I'd do is make some speed tests on joins first,  
before adding the extra baggage. You might be surprised to find out that a
uniqueidentifier primary key that is nonclustered will join quite nicely.
-- Peter
Site:http://www.eggheadcafe.com
UnBlog:http://petesbloggerama.blogspot.com
MetaFinder:http://www.blogmetafinder.com












- Show quoted text -
Click to expand...

Peter, say I am designing an application like 'Google groups' that has
to inner join with the User's table to show the username, I think w/o
even testing it will def. be much slower than joining on a UserID. I
agree I should test things out but I have heard joining on guids are
slow in other applications that use them for ID's.

By the way the UserID INT will be a PK since I have another Users
table that has extra meta data on each user along with the
uniqueidentifier - I didn't want to use the aspnet_userprofile table
as its not normalized etc).
 
//
I think w/o
even testing it will def. be much slower than joining on a UserID. I
agree I should test things out but I have heard joining on guids are
slow in other applications that use them for ID's.
//


That's the issue on the table.
quote //"I think" //end quote
quote //"I have heard"// end quote

No one can do the work for you, you need to test it.

My suggestion was to not fear the uniqueidentifier, and give it a try.

But for 1,000,000 rows, I don't think there will be a big difference.

I use guid's as PK and FK's all the time.


Good luck.





The default schema for the ASPNET_Users table maps the UserId
(uniqueidentifier) as the primary key for the table. If you start adding
additional columns such as your Int column, any joins you do are going to
be
dependent on that primary key, which is nonclustered in the default
scenario.
As long as you leave this arrangement alone, and add your int column at
the
end of the table, you should be OK.

However the first thing I'd do is make some speed tests on joins first,
before adding the extra baggage. You might be surprised to find out that a
uniqueidentifier primary key that is nonclustered will join quite nicely.
-- Peter
Site:http://www.eggheadcafe.com
UnBlog:http://petesbloggerama.blogspot.com
MetaFinder:http://www.blogmetafinder.com












- Show quoted text -
Click to expand...

Peter, say I am designing an application like 'Google groups' that has
to inner join with the User's table to show the username, I think w/o
even testing it will def. be much slower than joining on a UserID. I
agree I should test things out but I have heard joining on guids are
slow in other applications that use them for ID's.

By the way the UserID INT will be a PK since I have another Users
table that has extra meta data on each user along with the
uniqueidentifier - I didn't want to use the aspnet_userprofile table
as its not normalized etc).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Advice needed (ASP.NET membership) 2
NHibernate QueryOver with Many-to-Many 0
Figuring out how to use a custom membership provider. 1
Advice needed (ASP.NET membership) 14
ASP.net 2.0 Users (aspnet_Users table) create constraint problem 1
autoincrement vs uniqueidentifier (easy) 17
ASPNET Membership 1
Help with user authentication 1

Back
Top