RFC: Trigger uodating records in INSERTED Table

1) We have a trigger which applies further changes to the inserted/updated
records. Is this fundamentally bad or an acceptable practice?
2) We suspect that one version of such a trigger is causing deadlocks.
Interestingly this does not seem to happen if we use a cursor. See two
versions below. Any insights why the behavior differs?
Looking forward to your comments,
Jonathan Orgel
-- Suspected of causing dead lock
CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
FOR INSERT, UPDATE
AS
BEGIN
UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID IN (SELECT DOCUMENTID FROM
INSERTED)
END
-- OK...
CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
FOR INSERT, UPDATE
AS
BEGIN
DECLARE IndexCursor CURSOR LOCAL STATIC FOR SELECT DOCUMENTID FROM
INSERTED
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @.DOCUMENTID
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID = @.DOCUMENTID
FETCH NEXT FROM IndexCursor INTO @.DOCUMENTID
END
CLOSE IndexCursor
DEALLOCATE IndexCursor
ENDRewrite your first trigger to be like the following
CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
FOR INSERT, UPDATE
AS
BEGIN
UPDATE
a
SET
X=Y
from
documents a inner join inserted b on
a.documentid=b.documentid
END
I think you are getting deadlocks because of "where documentid in ..."
syntax. Usually this causes SQL Server not to use index optimisation, and
would attempt to do a table scan. Since your code is also doing an update on
the same table, this would cause deadlocks. Also make sure you have an index
on documentid. By the look of things, documentid should be the primary key
and should obviously have been indexed to start with.
HTH
"Jonathan Orgel" <Jonathan@.srssoft.com> wrote in message
news:ek1TflmUGHA.1728@.TK2MSFTNGP11.phx.gbl...
> 1) We have a trigger which applies further changes to the inserted/updated
> records. Is this fundamentally bad or an acceptable practice?
> 2) We suspect that one version of such a trigger is causing deadlocks.
> Interestingly this does not seem to happen if we use a cursor. See two
> versions below. Any insights why the behavior differs?
> Looking forward to your comments,
> Jonathan Orgel
> -- Suspected of causing dead lock
> CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
> FOR INSERT, UPDATE
> AS
> BEGIN
> UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID IN (SELECT DOCUMENTID FROM
> INSERTED)
> END
> -- OK...
> CREATE TRIGGER IU_DOCUMENTS ON DOCUMENTS
> FOR INSERT, UPDATE
> AS
> BEGIN
> DECLARE IndexCursor CURSOR LOCAL STATIC FOR SELECT DOCUMENTID FROM
> INSERTED
> OPEN IndexCursor
> FETCH NEXT FROM IndexCursor INTO @.DOCUMENTID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE DOCUMENTS SET X=Y WHERE DOCUMENTID = @.DOCUMENTID
> FETCH NEXT FROM IndexCursor INTO @.DOCUMENTID
> END
> CLOSE IndexCursor
> DEALLOCATE IndexCursor
> END
>
>

RFC: EXISTS (SELECT ... FROM) optional?

Hi,
in SQL there is a term that's bothering me:
EXISTS (SELECT ... FROM
I guess generations of SQL programmers put some brains into thinking of some
expression to put after the "SELECT" term. I usually use a NULL, like
EXISTS (SELECT NULL FROM
I tend to believe this term is as redundant as DELETE * FROM. Wouldn't it be
good to have this part optional, like with the DELETE statement? Like:
EXISTS ([SELECT <column>[,<column>] FROM ] <tablename> ...)
so it would be a valid term to write:
EXISTS (MyTable WHERE MyIdColumn = 1234)
RFC,
Axel DahmenAxel Dahmen wrote:
> Hi,
> in SQL there is a term that's bothering me:
> EXISTS (SELECT ... FROM
> I guess generations of SQL programmers put some brains into thinking of so
me
> expression to put after the "SELECT" term. I usually use a NULL, like
> EXISTS (SELECT NULL FROM
> I tend to believe this term is as redundant as DELETE * FROM. Wouldn't it
be
> good to have this part optional, like with the DELETE statement? Like:
> EXISTS ([SELECT <column>[,<column>] FROM ] <tablename> ...)
> so it would be a valid term to write:
> EXISTS (MyTable WHERE MyIdColumn = 1234)
> RFC,
> Axel Dahmen
You are quite right that EXISTS is a pretty silly syntax for a semi
join in SQL. I believe that EXISTS pre-dates the ANSI outer join
syntax. That probably explains why EXISTS exists at all.
Another problem is that EXISTS only implements two-value logic
(TRUE/FALSE) even though the predicate that forms the join (the WHERE
clause in the subquery) may give an UNKNOWN result.
David Portas
SQL Server MVP
--|||On 15 Dec 2005 03:29:17 -0800, David Portas wrote:

>Axel Dahmen wrote:
>You are quite right that EXISTS is a pretty silly syntax for a semi
>join in SQL. I believe that EXISTS pre-dates the ANSI outer join
>syntax. That probably explains why EXISTS exists at all.
Hi David,
An existence check with an EXISTS clause is only equivalent to an
existence check with an outer join if the relationship is not one to
many. Otherwise, you'll either have to use a DISTINCT or GROUP BY to get
rid of the duplicates, or remove duplicates in a derived table before
doing the join.
In either case, an EXISTS is simpler and more elegant.

>Another problem is that EXISTS only implements two-value logic
>(TRUE/FALSE) even though the predicate that forms the join (the WHERE
>clause in the subquery) may give an UNKNOWN result.
Why is that a problem? Do you also have a problem with SIGN that can
only return -1, 0, or -1 even though the integer domain contains many
more values?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis wrote:
> On 15 Dec 2005 03:29:17 -0800, David Portas wrote:
>
> Hi David,
> An existence check with an EXISTS clause is only equivalent to an
> existence check with an outer join if the relationship is not one to
> many. Otherwise, you'll either have to use a DISTINCT or GROUP BY to get
> rid of the duplicates, or remove duplicates in a derived table before
> doing the join.
> In either case, an EXISTS is simpler and more elegant.
A proper semijoin would implicitly be DISTINCT and would therefore work
much the same as EXISTS.

>
> Why is that a problem? Do you also have a problem with SIGN that can
> only return -1, 0, or -1 even though the integer domain contains many
> more values?
>
The problem is that EXISTS doesn't propagate nulls. It converts unknown
results to FALSE ones. The end results can be odd when you try to make
sense of the logic. For example:
CREATE TABLE foo (x INTEGER NOT NULL PRIMARY KEY) ;
CREATE TABLE bar (x INTEGER NOT NULL PRIMARY KEY, y INTEGER NULL) ;
INSERT INTO foo (x) VALUES (1) ;
INSERT INTO bar (x,y) VALUES (1,NULL) ;
SELECT x
FROM foo
WHERE NOT EXISTS
(SELECT *
FROM bar
WHERE bar.x = foo.x
AND bar.y = 2) ;
Result:
x
--
1
(1 row(s) affected)
But this doesn't match the expected interpretation of y=NULL. If y is
unknown then we cannot definitively say that no row exists in Bar where
x=1 and y=2. We should surely expect EXISTS to return UNKINOWN and
therefore no row would be returned. The above example contradicts the
following apparently identical query using the same data:
SELECT x
FROM bar
WHERE y=2
AND EXISTS
(SELECT *
FROM foo
WHERE foo.x = bar.x) ;
x
--
(0 row(s) affected)
David Portas
SQL Server MVP
--|||David Portas wrote:
> therefore no row would be returned. The above example contradicts the
> following apparently identical query using the same data:
> SELECT x
> FROM bar
> WHERE y=2
> AND EXISTS
> (SELECT *
> FROM foo
> WHERE foo.x = bar.x) ;
>
Oops. That should have been:
SELECT x
FROM bar
WHERE y<>2
AND EXISTS
(SELECT *
FROM foo
WHERE foo.x = bar.x) ;
The result is the same as before.
David Portas
SQL Server MVP
--|||>> in SQL there is a term that's bothering me:
EXISTS (SELECT ... FROM
I guess generations of SQL programmers put some brains into thinking of
some expression to put after the "SELECT" term. <<
Now I get to tell ANSI X3H2 stories!
The predicates that use a subquery are "[NOT] EXISTS(<subquery> )",
"<expr> [SOME|ANY] <comp op><subquery>" and "<expr> ALL <comp
op><subquery>"; they required a single-column <subquery> in the
original SQL Standard.
When you used a "EXISTS (SELECT * FROM.." syntax, the fiction was that
the engine picked a single column from the list to use.
In the very first SQL engines, it made a difference if you had a
constant or a column name or a star. Constants ran faster in
Oracle because it had a crappy optimizer -- and still does-- so you
will see Oracle code with "SELECT 1" in their code.
The star was preferred because the better optimizers could look for an
indexed column and use the index for any outer references without
looking at base tables at all.
Today, it does not matter -- the SELECT list is ignored. But the
SELECT * is considered better style because it clearly shows that we
are working at the table level and the unit of work is a whole row.
There is no "DELETE * FROM" in SQL and never has been. That is Access,
a proprietary language that is nothing like SQL except for a few stolen
keywords.
Finally, We thought about it in X3H2 over 20 years ago and rejected
it because (1) It was a way to pass hints to the optimizers back
then (2) Think about nesting subqueries in an EXISTS()!!! Your
proposed syntax would require a major change in the BNF and syntax
rules for a subquery, for the predicates, etc.|||>> The problem is that EXISTS doesn't propagate NULLs. It converts UNKNOWN r
esults to FALSE ones. <<
No. No. No. The EXISTS() is not defined to have an UNKNOWN result at
all. The unit of work is a row, not a column so it cannot produce an
UNKNOWN.
This is ***important*** because an UNKNOWN "converts" to a FALSE in the
ON and WHERE clauses in the DML and to a TRUE in the CHECK() and other
predicates in the DDL.
Gee, Dave, I have not "Celko-ed" in a loooooong time!|||On 16 Dec 2005 03:36:05 -0800, David Portas wrote:
(snip)
>A proper semijoin would implicitly be DISTINCT and would therefore work
>much the same as EXISTS.
Hi David,
Agreed. But that would require a new operator, as the OUTER JOIN does
not (and should not) imply a DISTINCT.

>The problem is that EXISTS doesn't propagate nulls. It converts unknown
>results to FALSE ones. The end results can be odd when you try to make
>sense of the logic. For example:
>CREATE TABLE foo (x INTEGER NOT NULL PRIMARY KEY) ;
>CREATE TABLE bar (x INTEGER NOT NULL PRIMARY KEY, y INTEGER NULL) ;
>INSERT INTO foo (x) VALUES (1) ;
>INSERT INTO bar (x,y) VALUES (1,NULL) ;
>SELECT x
> FROM foo
> WHERE NOT EXISTS
> (SELECT *
> FROM bar
> WHERE bar.x = foo.x
> AND bar.y = 2) ;
>Result:
>x
>--
>1
>(1 row(s) affected)
>But this doesn't match the expected interpretation of y=NULL. If y is
>unknown then we cannot definitively say that no row exists in Bar where
>x=1 and y=2. We should surely expect EXISTS to return UNKINOWN and
>therefore no row would be returned.
Well, I would surely not expect EXISTS to return UNKNOWN, since I know
how the EXISTS operator is defined. :-)
But I see your point. The EXISTS operator should be renamed to something
like KNOWN_FOR_SURE_TO_EXIST to match it's real behaviour. And a new
operator, somewhat like EXISTS but able to return UNKNOWN as well might
have it's merit.

> The above example contradicts the
>following apparently identical query using the same data:
(snip)
(copy corrected version from other post)
>SELECT x
> FROM bar
> WHERE y<>2
> AND EXISTS
> (SELECT *
> FROM foo
> WHERE foo.x = bar.x) ;
>x
>--
>(0 row(s) affected)
It's probably just me being dense, but I fail to see why you feel that
this second query should be identical to the first.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||--CELKO-- wrote:
> No. No. No. The EXISTS() is not defined to have an UNKNOWN result at
> all. The unit of work is a row, not a column so it cannot produce an
> UNKNOWN.
> This is ***important*** because an UNKNOWN "converts" to a FALSE in the
> ON and WHERE clauses in the DML and to a TRUE in the CHECK() and other
> predicates in the DDL.
> Gee, Dave, I have not "Celko-ed" in a loooooong time!
That's what the SQL says but my contention is that this isn't a natural
interpretation given SQL's treatment of 3VL elsewhere. The definition
of EXISTS makes less sense for the user and is more likely to lead to
mistakes. Partly I suppose the choice of keyword is at fault. "EXISTS"
doesn't really check for existence of a real world entity since (in
SQL's model) its existence or non-existence may be an unknown.
"SUBQUERY RETURNS ROWS" might be a more accurate name for SQL's EXISTS
operator.
A SEMIJOIN operator would be a more useful variant of EXISTS because we
so commonly code that function using INNER joins. Unfortunately, given
that SQL supports tables without keys and can only support projection
through the DISTINCT keyword I can see how a SEMIJOIN operator might
seem tricky and counter-intuitive to some users also.
David Portas
SQL Server MVP
--

RFC on a different method for paging of large result set, please comment

Summary : I think of two methods for the same need. I expose them here and ask the forum for hints and comments.

Here is the context :

SQL Server 2005 database called from a C# program. Based on user interaction the program has to issue a query that returns a variable number of records. Sometimes the number is small, sometimes it may be bigger (thousands) and will eventually become even bigger (hundreds of thousands). The query may have several JOIN and ORDER BY (no GROUP BY). The columns to retrieve depend on user input. There may be a lot of columns to fetch. The queried tables are append-only and do not change often. The C# program probably runs on the same machine as the database but the user GUI is on a remote computer.

All the record data is never needed at once. Instead, only some slices (pages) of the whole result set are needed, on demand. They are displayed in a virtual grid in a Windows Forms app.

So far this looks relatively common. Searching on SQL Server docs, on the web and on the forum shows one recommended way, which is basically explained on http://technet.microsoft.com/fr-fr/library/ms186734(SQL.90).aspx : make a first SELECT that includes a computed column based on ROW_NUMBER() and a certain ORDER BY. That SELECT is wrapped in a second select which restricts the result set to a certain range of rows.

Although this method is better than some previous before ROW_NUMBER() was available, it looks like it makes the SQL Server perform the same query again and again for each slice/page needed.

I see another method and wonder if anyone has hints or advice about it.

The method is :

Perform the whole query once (no limit on returned rows) but only request the ID of the records, not any other column. Fetch all those IDs from the database and keep them at C# level. When any page is needed, the C# level only has to look in the fetched table to find the IDs actually needed. It request a set of objects by their IDs, like this :
SELECT some, columns IN my_table WHERE ID IN (someId1, someId2 etc...)
(a real example may still have some JOIN, the order is more or less irrelevant because the client know the order by IDs). So only one SELECT does all the hard work. All subsequent SELECTs (one per page) only fetch a set of records using simple queries (still with joins).

Let's call that second method the "list of IDs" method.

The supposed differences are :

on one hand, list of IDs seems better because the subsequent queries are simpler and only involve few objects, making them much lighter and faster. On the contrary, ROW_NUMBER() method reperforms the whole initial query each time a page is needed (only fetching a different limited range of rows). It seems that in that case the database engine has to basically redo the same tough work (considering the whole thing, perform the order, generate the row_number column), right ? on the other hand, list of IDs method fetches all the IDs even when very little of them are needed. ROW_NUMBER() method only sends from database to client the smallest needed data. Can this be an argument in favor of ROW_NUMBER() method ?

So I'm wondering why I could not find anything mentioning this "list of ids" method or anything similar neither in the documentation, nor on the web.

Thank you for your insightful comments.

The first questions I have are about returning "hundreds of thousands" of ID_LIST records:

What are the bandwidth standards for your network? What is the bandwidth that will be required for you to return "hundreds of thousands" of ID_LIST records? How frequently might the network need to service such requests? How sensitive do you need to be to changes in the list?|||

Here are the clarifications you asked for :

What is the bandwidth that will be required for you to return "hundreds of thousands" of ID_LIST records?

For a start we can assume that the database client lives on the same machine as the SQL Server.

The "hundreds of thousands" are only IDs, not any ID_LIST record. I mean : the ID column of the involved database table (which has other columns and joins to other tables).

So, that big one-column-of-IDs result set will only travel from the SQL Server 2005 process to the .NET process on the same machine.

For completeness, that C# program (the client of the SQL Server process) is in turn a remoting server to which a C# GUI client is connected, through a, say, 128kbps to 1Mbps link. As far as the latter user process is concerned, the situation is the same in the two methods because exactly the same data will flow through the slow link. The difference between the two methods only change the interaction between the SQL Server 2005 process and its direct C# client which we can assume to live on the same machine.

I guess this answers your questions about bandwidth. As for the frequency, well, several remote C# clients may connect simultaneously to the server machine, all to the same C# process. That only server C# process is the only client connected to the involved dabatase. Requests are triggered by human interaction so they are not numerous but they should quickly serve the first page and any other requested page (by scrolling the virtual grid). Often there won't be many pages requested, but if they are they have to be served quickly.

How sensitive do you need to be to changes in the list?

As written above "The queried tables are append-only and do not change often.". Not often means here a few times a day. Moreover I can be notified of a change by other means and invalidate the display so that it refreshes correctly.

Thank you for your attention.

|||I have used both methods you describe in different situations. There are pros and cons to each. The simple answer is whatever works for your situation.

Yes, the ROW_NUMBER method does the same query many times. However, in most situations, the data is cached, so the performance is very fast and it should be only searching the indexes. This method also gives instant results of changes. If someone adds a record on page 10, now your query shows it on page 10 and shifts everything down.

The other method, involves either running multiple individual queries, or parsing a string of 50 IDs to get at once or dynamic SQL. You have basically cached the query results. So you need a method to rerun the query and update the list, so when someone adds a record you get it. Also, you need to handle if someone deletes one of your keys in the database. This works best for short lists which do not change often.

Most applications do not want "cached" results. Which is why you don't see this often. You will get calls, "Joe just added a record and I don't see it".

Either method works fine.

|||

Thank you for your answer. So, there is apparently no hidden gotcha about the "list of ids" method. Fine !

In our application a notification mechanism already ensures that when Joe adds a record all clients immediately get an updated display, so both methods are acceptable in our case without further complication.

Those who will read that thread may find interesting that link that I found on another thread in those forums : Arrays and Lists in SQL Server 2005 . It enumerates various techniques on how to pass a list of values from client to SQL Server.