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
 General SQL Server Forums
 Database Design and Application Architecture
 Store Integer and GUID in a same table column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ketanmaheta
Starting Member

1 Posts

Posted - 12/17/2012 :  10:26:39  Show Profile  Reply with Quote
Hi All,
I have a requirement like I want to store GUID and int value in a same column since I am getting this ids from two different systems. Is type varchar(36) fine for the same?

Is there any issue in doing so?

Please guide..

Thanks,
Ketan

sanjnep
Posting Yak Master

USA
191 Posts

Posted - 12/17/2012 :  10:57:07  Show Profile  Visit sanjnep's Homepage  Reply with Quote
I think you can use sql_variant data type to store GUID and int
Thanks,
Sanjeev
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/17/2012 :  10:57:45  Show Profile  Reply with Quote
If at all possible store them in two different columns with appropriate data types. Storing them in the same column has all kinds of disadvantages - for example, someone could store a string such as "Joe's Bar" in there. If you have them in two columns, you can always coalesce the two columns when querying.

If you must store it in a single column, 36 should be sufficient.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/17/2012 :  13:59:24  Show Profile  Reply with Quote
Can there be different ids and datatype coming in later for different system? It is better to store them in one column which will have sourcevalue and sourcetype to identify where it is coming. It is better to put varchar(36) as you don't have to convert it while joining with uniqueidentifier.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 12/17/2012 :  19:53:17  Show Profile  Reply with Quote
I'm not clear if you are saying you have to store both at the same time or either one or another.
If the former, then just use 2 not null columns of uniqueidentifier and INT. This will ensure only correct data can get in as Sunita says.
If the latter, then the same only nullable but with a check constraint that disallows them both to be null.
Depending on your future needs, consider ignoring the values and do the rest of your joins via a surrogate PK (it's rare I say that!).
Stuffing 2 fields together is probably the worst way if you ever need to get it back out but it will depend on what you need them for.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/17/2012 :  21:14:47  Show Profile  Reply with Quote
quote:
Originally posted by LoztInSpace

I'm not clear if you are saying you have to store both at the same time or either one or another.
If the former, then just use 2 not null columns of uniqueidentifier and INT. This will ensure only correct data can get in as Sunita says.If data is coming from 10 different system then will you have 10 columns to store it. that is bad design
If the latter, then the same only nullable but with a check constraint that disallows them both to be null.
Depending on your future needs, consider ignoring the values and do the rest of your joins via a surrogate PK (it's rare I say that!).
Stuffing 2 fields together is probably the worst way if you ever need to get it back out but it will depend on what you need them for.

Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 12/18/2012 :  19:22:11  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

quote:
Originally posted by LoztInSpace

I'm not clear if you are saying you have to store both at the same time or either one or another.
If the former, then just use 2 not null columns of uniqueidentifier and INT. This will ensure only correct data can get in as Sunita says.If data is coming from 10 different system then will you have 10 columns to store it. that is bad design
If the latter, then the same only nullable but with a check constraint that disallows them both to be null.
Depending on your future needs, consider ignoring the values and do the rest of your joins via a surrogate PK (it's rare I say that!).
Stuffing 2 fields together is probably the worst way if you ever need to get it back out but it will depend on what you need them for.




Data is coming from 2 columns, not 10. Where did you get 10 from?
In any case, if you are storing 10 different pieces of information, then 10 columns is the way to do it. That is generally true for 2, 10, 100. Are you seriously suggesting that's not correct?
If however, the requirement is to store one of 10 then maybe one column per data type (to ensure integrity) and a source type would be in order.
But, let me reiterate, the (ambiguous) question was about storing 2 things, not 10.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/18/2012 :  21:16:10  Show Profile  Reply with Quote
quote:
Originally posted by LoztInSpace

quote:
Originally posted by sodeep

quote:
Originally posted by LoztInSpace

I'm not clear if you are saying you have to store both at the same time or either one or another.
If the former, then just use 2 not null columns of uniqueidentifier and INT. This will ensure only correct data can get in as Sunita says.If data is coming from 10 different system then will you have 10 columns to store it. that is bad design
If the latter, then the same only nullable but with a check constraint that disallows them both to be null.
Depending on your future needs, consider ignoring the values and do the rest of your joins via a surrogate PK (it's rare I say that!).
Stuffing 2 fields together is probably the worst way if you ever need to get it back out but it will depend on what you need them for.




Data is coming from 2 columns, not 10. Where did you get 10 from?
In any case, if you are storing 10 different pieces of information, then 10 columns is the way to do it. That is generally true for 2, 10, 100. Are you seriously suggesting that's not correct?
If however, the requirement is to store one of 10 then maybe one column per data type (to ensure integrity) and a source type would be in order.
But, let me reiterate, the (ambiguous) question was about storing 2 things, not 10.




10 was just an example i am giving here.it can be any number of systems.if you read his question properly, he is asking a way to store 2 diff system with different datatype so that he doesn't have to create number of columns based on system. Thats where you would avoid redundant data.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 12/18/2012 :  23:03:10  Show Profile  Reply with Quote
quote:
Originally posted by sodeep
10 was just an example i am giving here.it can be any number of systems.

You seem to be flicking between 2 and 10 systems as it suits your argument to stuff everything together. Nobody but you brought up the requirement for anything other than 2 systems though.
quote:
Originally posted by sodeep
if you read his question properly, he is asking a way to store 2 diff system with different datatype so that he doesn't have to create number of columns based on system.

I did read the question and because it was such a strange one, I asked for clarification: Is it either one of INT or GUID or both at the same time. My answer does change depending on that, but in both cases, stuffing the other system ID(s) into a single, column of neither type is unlikely to be "best" by most measures of good design.
If you have X things to store for a given PK, generally it's best to have X columns, each of the appropriate type. I cannot understand why you consider this bad practice. If I had name, address, phone number etc you would not advocate stuffing this into one large string would you? So why do that for external system identifiers?
quote:
Originally posted by sodeep
Thats where you would avoid redundant data.


I have no idea what this comment refers to.
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.09 seconds. Powered By: Snitz Forums 2000