Repurposing VarChar columns
-
So I brought our entire system to it's knees yesterday. What did I do? I entered some text into a varchar column in one of our database tables. Why was that bad? When this column was created it originally held text, but then somebody decided that this text would be better served in a lookup table. Instead of creating a new column for the lookup key (an integer), they simply changed each value in column to its corresponding number in the lookup table. And then they created views that join the table and lookup table which our company's programs tightly couple to. Microsoft, in their ever unceasing efforts to be helpful says you can join a varchar column to an integer column - so long as EVERY item in the varchar column casts to an integer. But if there is even one row that doesn't cast ... Why they couldn't cast the integer column to a varchar for us, only Microsoft can answer that. So, to make a long story short, I added my text as a new lookup item and now there is a number where my text used to be and everything and everybody is happy again.
Brent
In fairness to Microsoft, they have introduced TRY_CAST() and TRY_CONVERT() in recent times which are very helpful when dealing with this kind thing.
98.4% of statistics are made up on the spot.
-
Jörgen Andersson wrote:
If you make a join between an int and a varchar it wouldn't make a narrowing conversion on the varchar to int. It would make a widening conversion of the int to a varchar.
Nope -
inthas a higher precedence thanvarchar. If you try to joininttovarchar, SQL will try to convert all values toint. Data Type Precedence (Transact-SQL) | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Fuck me, you're right. But why?
Wrong is evil and must be defeated. - Jeff Ello
-
So I brought our entire system to it's knees yesterday. What did I do? I entered some text into a varchar column in one of our database tables. Why was that bad? When this column was created it originally held text, but then somebody decided that this text would be better served in a lookup table. Instead of creating a new column for the lookup key (an integer), they simply changed each value in column to its corresponding number in the lookup table. And then they created views that join the table and lookup table which our company's programs tightly couple to. Microsoft, in their ever unceasing efforts to be helpful says you can join a varchar column to an integer column - so long as EVERY item in the varchar column casts to an integer. But if there is even one row that doesn't cast ... Why they couldn't cast the integer column to a varchar for us, only Microsoft can answer that. So, to make a long story short, I added my text as a new lookup item and now there is a number where my text used to be and everything and everybody is happy again.
Brent
How about creating a foreign key relationship between the tables? Would that not prevent this issue from happening again?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Fuck me, you're right. But why?
Wrong is evil and must be defeated. - Jeff Ello
-
Yes, but a widening conversion to varchar and comparison between two varchars are probably faster than a narrowing conversion to int with an error check and then comparing two ints.
Wrong is evil and must be defeated. - Jeff Ello
-
Yes, but a widening conversion to varchar and comparison between two varchars are probably faster than a narrowing conversion to int with an error check and then comparing two ints.
Wrong is evil and must be defeated. - Jeff Ello
Jörgen Andersson wrote:
with an error check and then comparing two ints.
It's probably more a cast than a conversion.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
-
Jörgen Andersson wrote:
with an error check and then comparing two ints.
It's probably more a cast than a conversion.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
Probably.
Wrong is evil and must be defeated. - Jeff Ello
-
How about creating a foreign key relationship between the tables? Would that not prevent this issue from happening again?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
How about creating a foreign key relationship between the tables?
I believe that would trade one problem for another; you would trade an invalid cast for a foreign key constraint exception, which might well roll back a whole transaction. Moreover, who knows what it would do to the view?
David A. Gray Delivering Solutions for the Ages, One Problem at a Time Interpreting the Fundamental Principle of Tabular Reporting
-
GuyThiebaut wrote:
How about creating a foreign key relationship between the tables?
I believe that would trade one problem for another; you would trade an invalid cast for a foreign key constraint exception, which might well roll back a whole transaction. Moreover, who knows what it would do to the view?
David A. Gray Delivering Solutions for the Ages, One Problem at a Time Interpreting the Fundamental Principle of Tabular Reporting
Exactly, which is why foreign keys can be useful as they will prevent the insertion of data into a database which does not meet certain criteria. A foreign key constraint should not affect a view - I see it more as a gatekeeper that prevents problematic data from being added to a database, whether that is by insertion, deletion or update. I am aware that many people do not use foreign keys, however I have seen them to help in making the developer's life easier by keeping problematic data out of a database. Without foreign keys you can find yourself in the position where the client is complaining about the system behaving strangely when the reason for that behaviour is, for want of a better word, unsanitised data.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
-
Exactly, which is why foreign keys can be useful as they will prevent the insertion of data into a database which does not meet certain criteria. A foreign key constraint should not affect a view - I see it more as a gatekeeper that prevents problematic data from being added to a database, whether that is by insertion, deletion or update. I am aware that many people do not use foreign keys, however I have seen them to help in making the developer's life easier by keeping problematic data out of a database. Without foreign keys you can find yourself in the position where the client is complaining about the system behaving strangely when the reason for that behaviour is, for want of a better word, unsanitised data.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
GuyThiebaut wrote:
have seen them to help in making the developer's life easier by keeping problematic data out of a database.
I agree with you 100%, and I rely on foreign keys for precisely that reason. Nevertheless, I think it's important to be aware that having one doesn't excuse the developer from handling exceptions. In the situation under discussion, you are trading one kind of exception for another, albeit, more useful, one. I don't think it would affect the view, either, but I thought it prudent to allow for some technical detail that I might have overlooked. I usually don't construct any views until I have my foreign keys defined, so that I can leverage them to help create the views.
David A. Gray Delivering Solutions for the Ages, One Problem at a Time Interpreting the Fundamental Principle of Tabular Reporting
