Is there a way to turn off implicit type conversion in SQL Server?

Question

As the title indicates: Is there a way to turn off implicit type conversion in SQL Server?

I want the following simple SQL to generate an error

SELECT 'a' WHERE 1='1'

Doubleteam

Good question, except: why does it matter? After an extensive ten seconds thinking about it I can't think of an example where I would accidentally compare two different types, but if I'm deliberately comparing two different types then I want conversion... – From

I think it matters for a lot of reasons. Among others the one you name: accidental comparison. I think it would be nice to have a user defined type in a table on a given field that might be a key and have a foreign key in another table. I think that the default should be the when comparing two values - If type types are different: fail. – Doubleteam

It's a feature of Standard SQL which Chris Date attributes to his friend E. F. Codd, the idea being "sometimes the user will know more than the DBMS does". But Date considers this is have been a misjudgement: google the exact phrase `"Codd-style domain checks" for more details. – Smitherman

Implicit type conversion causes some bizarre-looking errors. Suppose table foo has column c which is of char type. Then select * from foo where c = 123 will cause the strange error Conversion failed when converting the varchar value 'ABC' to data type int. Nowhere in my query did I ask to convert 'ABC' to anything; instead, this value is in column c somewhere. Much more helpful if MSSQL would warn me that I have specified an int when the column type is char. – Danielledaniels

Answer 1

There is no way to disable it.

It has been requested though: see the proposed SET OPTION STRICT ON MS Connect request which comes from Erland Sommarskog

However, it is utterly predictable according to datatype precedence rules

Your example of a foreign key is interesting because an actual FOREIGN KEY constraint requires the same datatype, length and collation.

Rheum

Problem is stuff like datetimeoffsets and datetime comparisons. The datetimes get implicitly cast WITH +00:00 offset instead of say local offset. Allowing implicit casts here 99% of the time isn't doing what you actually want to do. – Faucal

Answer 2

There is no way to disable the implicit conversions in SQL server, though some searching will show that it is something a number of people have asked for.

Nariko