Problem with HOST_NAME Function with Linked View

[Erland Sommarskog]

Re: Problem with HOST_NAME Function with Linked View

Neil (nospam@nospam. net) writes:[color=blue]
> After I got your note I did go ahead and make MachName nchar, as BOL
> says that HOST_NAME() returns that type, and the sample it showed of
> storing its return value in a table used an nchar(30) field.
>
> The InvtrySelection s table had the PK (ID/MachName) as the clustered
> index. I created a second index on MachName alone, and it made it the
> clustered index.
>
> With the above two changes, the results were the same. Still getting
> "#Deleted".[/color]

As I said, I did not really expect that. I more had performance in mind.

But now it occurred to me, that it's a bit of a non-issue for you. To
wit, the rules for implicit conversion are different in SQL 7 and
SQL 2000. The potential performance problem I saw, probably only exists
in SQL 2000. Then again, it's good to be prepared, in case you upgrade
to SQL 2005 one day. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Neil]

Re: Problem with HOST_NAME Function with Linked View

You're saying that SQL 2000 performs implicit conversions, but SQL 7
doesn't? That seems like a step backwards for MS. I wish Access didn't
perform implicit conversions.


"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns9701130 5379Yazorman@12 7.0.0.1...[color=blue]
> Neil (nospam@nospam. net) writes:[color=green]
>> After I got your note I did go ahead and make MachName nchar, as BOL
>> says that HOST_NAME() returns that type, and the sample it showed of
>> storing its return value in a table used an nchar(30) field.
>>
>> The InvtrySelection s table had the PK (ID/MachName) as the clustered
>> index. I created a second index on MachName alone, and it made it the
>> clustered index.
>>
>> With the above two changes, the results were the same. Still getting
>> "#Deleted".[/color]
>
> As I said, I did not really expect that. I more had performance in mind.
>
> But now it occurred to me, that it's a bit of a non-issue for you. To
> wit, the rules for implicit conversion are different in SQL 7 and
> SQL 2000. The potential performance problem I saw, probably only exists
> in SQL 2000. Then again, it's good to be prepared, in case you upgrade
> to SQL 2005 one day. :-)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>[/color]

[Erland Sommarskog]

Re: Problem with HOST_NAME Function with Linked View

Neil (nospam@nospam. net) writes:[color=blue]
> You're saying that SQL 2000 performs implicit conversions, but SQL 7
> doesn't? That seems like a step backwards for MS. I wish Access didn't
> perform implicit conversions.[/color]

That step backwards was taken with SQL 7 from SQL 6.5. A few more implicit
conversions were added in SQL 2005.

Of course, sometimes you do want implicit conversions. It's convenient
to have explicit conversions from character to datetime, or from integer
to bit. But from string to number and numbers to string? That's only bad.

This particular case is about an implicit conversion between varchar
and nvarchar. (or char/nchar), which can occur in both SQL 7 and SQL 2000.
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.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Problem with HOST_NAME Function with Linked View

(Removed most groups from way too long crosspost list)

On Tue, 1 Nov 2005 08:06:22 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
>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.[/color]

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)

[Erland Sommarskog]

Re: Problem with HOST_NAME Function with Linked View

Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=blue]
> 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.[/color]

But what happened if two columns of different data types met?
[color=blue]
> 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.[/color]

As I said, I basically slept over SQL 7, so missed the problem.

Of course, in many cases, these problems could be avoided by not
having implicit conversions at all.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Hugo Kornelis]

Re: Problem with HOST_NAME Function with Linked View

On Wed, 2 Nov 2005 22:47:00 +0000 (UTC), Erland Sommarskog wrote:
[color=blue]
>Hugo Kornelis (hugo@pe_NO_rFa ct.in_SPAM_fo) writes:[color=green]
>> 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.[/color]
>
>But what happened if two columns of different data types met?[/color]

Hi Erland,

I'm sorry, but I don't know that.

I've learned what I posted when figuring out why queries that ran
smoothly on SQL 7 were going at snail pace after upgrading. Turned out
that the converting a column instead of converting the constant meant
that a table scan was chosen instead of an index seek.

I've never witnessed similar problems for column to column comparisons,
so I don't know how they were executed.

[color=blue][color=green]
>> 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.[/color]
>
>As I said, I basically slept over SQL 7, so missed the problem.
>
>Of course, in many cases, these problems could be avoided by not
>having implicit conversions at all.[/color]

I couldn't agree more!

Best, Hugo
--

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