| Author |
Topic |
|
ketanmaheta
Starting Member
1 Post |
 Posted - 2012-12-17 : 10:26:39
|
| 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
191 Posts |
Posted - 2012-12-17 : 10:57:07
|
| I think you can use sql_variant data type to store GUID and int Thanks,Sanjeev |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-17 : 10:57:45
|
| 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. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-17 : 13:59:24
|
| 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. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-12-17 : 19:53:17
|
| 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. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-17 : 21:14:47
|
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 designIf 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.
|
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-12-18 : 19:22:11
|
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 designIf 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. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-18 : 21:16:10
|
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 designIf 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. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-12-18 : 23:03:10
|
quote:
Originally posted by sodeep10 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 sodeepif 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. |
|
|
|
|
Store Integer and GUID in a same table column