lfree (a@a.a) writes:
> My develop programer encrypt many stored Procedure, I find
> one stored procedure exist bad sql command. I don't look
> executing sql command at SQL profiler, I review source code
> at stored procedure,I find those bad sql command isn't exist!
>
> After I run `alter procedure ...` , I find bad sql command
> is disappear. I download a decrypt program and run at test machine,
> compare source code and decryption code to find them is sameness.
>
> When I run `alter procedure ... with encryption` again ,bad sql command is
> also disappear .why?

I'm not sure that I understand. Is that bad SQL the "756 x 208" thing?
I have no idea what that is, but it does not seem to come from Profiler.
It seems that this is some other application, although I don't know
what it could be.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

> I'm not sure that I understand. Is that bad SQL the "756 x 208" thing?
> I have no idea what that is, but it does not seem to come from Profiler.
> It seems that this is some other application, although I don't know
> what it could be.

I am sorry . I use HySnapDX to capture picture ,"756x208" is the picture
size.
Btw: those "bad" sql command is 'update...' command. read=4132,4130,4130.

lfree (a@a.a) writes:
> Btw: those "bad" sql command is 'update...' command. read=4132,4130,4130.

The execution plan can change if you reload the procedure, because the
plan in the cache was a result from atypical parameters on first invocation.
There are also a few more possible reasons.

With the miniscule amount of information that I have, it is difficult to
say more.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

lfree (a@a.a) writes:
> I find the problem,in stored procedure, parameter variable define
> numeric(8).
> but in table define int.
>
> I find in execute plan ,encrtpyion procedure (as figure1) and discrypt
> procedure (as figure2)
> is diff, My develop company Install Service Pack 3a (SP3a) .
>
> I check all stored procedure that having bad sql command are define
> numeric(8).
> I guess that this is a bug .

No, that is not a bug in SQL Server.

SQL Server has something called data-type precedence. Whenever two
different datatypes encounter each other in an expression, the one
with lower precedence is converted to the type of higher precendence.
(Provided that there is an implicit conversion at all, that is!)

In this case, numeric has higher precedence than int. Therefor the table
column is converted. Now, whenever a column that is part of index is
converted, the index for that column can no longer be used to search
values. In most cases, this leads to the index not being used at all.

> before I only know if table field char or varchar, if where condition is
> foo_char = 111
> sql-server execute is scan entire index.

Yes, this is the same issue. Char has lower precendence than int. In
this case it is very clear why the index cannot be used. The index would
be sorted as:

1 11 111 12 112 13 ...

but you cannot find the number 111 in this except by scanning the index.


You find the complete data-type precedence in Books Online, in the T-SQL
References under Data Types.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp