SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 What's the smart way to use GUIDs?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/28/2013 :  07:54:40  Show Profile  Reply with Quote
I have users (Salesmen) in ActiveDirectory and I think the only way I can identify them is using ObjectGUIDs. Now when I have the query to the linked server and receive all the info from there along with the GUIDs, I obviously need to make queries against that table. So I can have a query like
SELECT Orders FROM OrderTable WHERE Salesman =

what? The GUID? Am I supposed to type the whole 34-character string there everytime I set up a query? Or should I make a table that has the GUIDs and some autonumber ID to make kind of an alias for the GUID? Or how should I treat these?

James K
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 03/28/2013 :  08:29:10  Show Profile  Reply with Quote
If the uniqueness of the Salesman column is determined by a GUID, you would have to use the GUID in the where clause. Anything else (such as lastname + firstname) might give you incorrect results. There could be two Jane Doe's for example.

You could add an autoincrementing column (identity column) to the table where the Salesman informaton is kept and then use the value in that column in the where clause, but I can't say whether that is a good thing to do or not without knowing a lot more about your tables and queries.
Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/28/2013 :  08:38:16  Show Profile  Reply with Quote
quote:
Originally posted by James K

If the uniqueness of the Salesman column is determined by a GUID, you would have to use the GUID in the where clause. Anything else (such as lastname + firstname) might give you incorrect results. There could be two Jane Doe's for example.

You could add an autoincrementing column (identity column) to the table where the Salesman informaton is kept and then use the value in that column in the where clause, but I can't say whether that is a good thing to do or not without knowing a lot more about your tables and queries.



The salesman information is kept in a view - or more exactly - in ActiveDirectory, which is connected using a linked server and then queried to parse the view. I don't know if I know make a reliable autoincrementing column in a view and that's why I thought of using an extra table instead.

But yes, this chance of "double names" is exactly the problem here - and as we both know, a schoolbook example of what not to use as ID.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
346 Posts

Posted - 03/28/2013 :  10:37:31  Show Profile  Reply with Quote
Typically companies assign salesmen unique numbers, which may be the equivalent of identity-based or may be manually assigned.

I would assign each unique salesman a unique number and use that.

We all know guids are a pain to work with; that's why most people avoid them whenever possible .
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/28/2013 :  10:51:12  Show Profile  Reply with Quote
One way is to use custom generated sequence based on identity field


see example here

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

KilpAr
Yak Posting Veteran

80 Posts

Posted - 03/29/2013 :  11:37:50  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

One way is to use custom generated sequence based on identity field


see example here

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





This was interesting to read and seems like I wasn't way off with my initial suggestion of an extra table.

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/29/2013 :  14:03:35  Show Profile  Reply with Quote
quote:
Originally posted by KilpAr

quote:
Originally posted by visakh16

One way is to use custom generated sequence based on identity field


see example here

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/





This was interesting to read and seems like I wasn't way off with my initial suggestion of an extra table.

Thanks!


It doesnt need an extra table but just an extra column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000