Posted by Hugo Kornelis on 11/02/05 21:34

(Removed most groups from way too long crosspost list)

On Tue, 1 Nov 2005 08:06:22 +0000 (UTC), Erland Sommarskog wrote:

>What is different are the rules of what is being converted. In SQL 2000
>there is a strict data-type precendence, so a type with low precendence is
>always converted to a higher. Thus, a varchar value is converted to
>nvarchar, since nvarchar is higher up. I don't know the rules for SQL 7,
>as I never worked much with this version, but I know they are different,
>and it might be that values are converted rather than columns.

Hi Erland,

What I understood is that the difference is related to expressions of
the type
column_name = constant (value or expression)

In SQL Server 7.0, the constant would (sometimes? always?) be converted
to match the column's datatype, regardless of datatype precedence rules.
In SQL Server 2000, datatype precedence determines if the column or the
constant has to be converted.

The nice thing about the "old" method was that the implicit conversion
of the constant enabled the optimizer to use an index that was defined
on the column; in SQL Server 2000, the implicit conversion of the column
would preclude the use of that index.
Of course, the price one paid for the index use in the old version was
that the database didn't always do what you'd expect after perusing the
precedence rules.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]