MS -SQL behaviour of INSERTED / DELETED tables --> very strange

Hi All,
We have an odd thing while using the DELETED and INSERTED-
"tables"in triggers.
If my collegue executes and SP in the Query Analizer, a
query in a trigger takes a lot less when I execute the
same SP.
We think the difference is in the the usages of the
DELETED and INSERTED-tables. When my colleque runs it, the
DELETED and INSERTED-tables are being used, but when I run
the SP, a full tables scan is being performed (we
think!!!).
Has anyone seen this behaviour before or does anyone have
a solution for this?
Time difference between my collegue and I is 0.5 sec per
trigger (2 triggers in the SP are executed and lots of SP
executions).
Thanks in Advance,
Jeroen
PS. MS-SQL 2000 with SP3 on WIN 2000 serverJeroen
Can you show us your SP's code?
Have you ran SQL Server Profiler to identify what is going on when you
execute the SP?
"Jeroen Kraij" <jkrai@.kempen.nl> wrote in message
news:080e01c3bfc7$e7334580$a401280a@.phx.gbl...
> Hi All,
> We have an odd thing while using the DELETED and INSERTED-
> "tables"in triggers.
> If my collegue executes and SP in the Query Analizer, a
> query in a trigger takes a lot less when I execute the
> same SP.
> We think the difference is in the the usages of the
> DELETED and INSERTED-tables. When my colleque runs it, the
> DELETED and INSERTED-tables are being used, but when I run
> the SP, a full tables scan is being performed (we
> think!!!).
> Has anyone seen this behaviour before or does anyone have
> a solution for this?
> Time difference between my collegue and I is 0.5 sec per
> trigger (2 triggers in the SP are executed and lots of SP
> executions).
> Thanks in Advance,
> Jeroen
> PS. MS-SQL 2000 with SP3 on WIN 2000 server|||Hi,
The "thing" I would like to focus on is that we have a Pc
with an SQL-client.
My coleque logs in to this PC and connects to SQL-server
and executes the SP.
When I do exactly the same on this PC, The execution of
the SP takes a lot more time.
In the executionplan we can see that the processingtime of
the trigger(s) are the difference...
Is there any logical explanation for this? Or a solution?
Jeroen
>--Original Message--
>Jeroen
>Can you show us your SP's code?
>Have you ran SQL Server Profiler to identify what is
going on when you
>execute the SP?
>
>
>
>"Jeroen Kraij" <jkrai@.kempen.nl> wrote in message
>news:080e01c3bfc7$e7334580$a401280a@.phx.gbl...
>> Hi All,
>> We have an odd thing while using the DELETED and
INSERTED-
>> "tables"in triggers.
>> If my collegue executes and SP in the Query Analizer, a
>> query in a trigger takes a lot less when I execute the
>> same SP.
>> We think the difference is in the the usages of the
>> DELETED and INSERTED-tables. When my colleque runs it,
the
>> DELETED and INSERTED-tables are being used, but when I
run
>> the SP, a full tables scan is being performed (we
>> think!!!).
>> Has anyone seen this behaviour before or does anyone
have
>> a solution for this?
>> Time difference between my collegue and I is 0.5 sec per
>> trigger (2 triggers in the SP are executed and lots of
SP
>> executions).
>> Thanks in Advance,
>> Jeroen
>> PS. MS-SQL 2000 with SP3 on WIN 2000 server
>
>.
>

MS SQL 7 and 2000

Does anyone know of any differences between queries in SQL 7 and SQL
2000? I have a collegue that says there could be "subtle" differences
in the output, though I cannot find any documentation of this. Any
link to prove him wrong would be very helpful. Thanks.kingk436 (development@.circuitmap.com) writes:
> Does anyone know of any differences between queries in SQL 7 and SQL
> 2000? I have a collegue that says there could be "subtle" differences
> in the output, though I cannot find any documentation of this. Any
> link to prove him wrong would be very helpful. Thanks.

One "subtle" difference is the rule for auto-conversion of data types.
In SQL 2000, conversion is done from a type hierarchy, whereas SQL 7
tries to first convert a constant.

This can differences if you have things like:

SELECT * FROM tbl where varcharcol > 1

In SQL 7, the 1 will be converted to a string, where as in SQL 2000
varcharcol will be converted to an integer. (And thus the query will
fail if there are values in the column that cannot be converted to
integer.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9684F3D9BB08Yazorman@.127.0.0.1...
> kingk436 (development@.circuitmap.com) writes:
> > Does anyone know of any differences between queries in SQL 7 and SQL
> > 2000? I have a collegue that says there could be "subtle" differences
> > in the output, though I cannot find any documentation of this. Any
> > link to prove him wrong would be very helpful. Thanks.
> One "subtle" difference is the rule for auto-conversion of data types.
> In SQL 2000, conversion is done from a type hierarchy, whereas SQL 7
> tries to first convert a constant.
> This can differences if you have things like:
> SELECT * FROM tbl where varcharcol > 1
> In SQL 7, the 1 will be converted to a string, where as in SQL 2000
> varcharcol will be converted to an integer. (And thus the query will
> fail if there are values in the column that cannot be converted to
> integer.)

In addition I believe that without an order by, results returned will be
different. (Of course the order shouldn't be specified, but I believe both
SQL 7.0 and 2000 do default to some underlying physical order, but each
differently from the other.)

>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Greg D. Moore (Strider) (mooregr_deleteth1s@.greenms.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9684F3D9BB08Yazorman@.127.0.0.1...
>> In SQL 7, the 1 will be converted to a string, where as in SQL 2000
>> varcharcol will be converted to an integer. (And thus the query will
>> fail if there are values in the column that cannot be converted to
>> integer.)
> In addition I believe that without an order by, results returned will be
> different. (Of course the order shouldn't be specified, but I believe both
> SQL 7.0 and 2000 do default to some underlying physical order, but each
> differently from the other.)

Well, without any ORDER BY you could get different results from the
same server running SQL 7 or later depending on the phase of the moon,
availble CPUs if there are parallel streams, etc.

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

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