A Questionable Trigger
-
I did ctl+m and still got no error messages. Everything worked fine.
-
Hugo Kornelis (4/2/2014)
I understand that, after running into this, Andy wanted to submit this as a QotD. But he somehow forgot to check exectly how unrealistic the assumptions are that someone seeing only this question would have to make.I think thats a pretty fair description, and also the rest of what Hugo wrote. I'll add two comments to what Hugo said: (i) Andy, your story requires us to assume that we will switch viewing actual execution plan on when not only do we have no indexes or keys involved but also we have, and know we have, only two rows, because, you say, we are doing performance testing. That's just unbelievable nonsense. I do no performace testing on two rows. I might start from 2000, more likely from 2000000. I might start without an index, but I don't like heaps, and in SQL Server primary keys require indexes - heaps are forbidden in relational databases, and it's a pity that SQL server is sufficiently unrelational to permit them. (ii) It is generally agreed that QotD is based on a default installation of a currently fully supported version of SQL Server. Your question is based on a non-default install. Hugo's point that the non-default value for that option deprives us of useful capabilities is bad enough already, but the additional point that you are violating a well understood convention that most people believe applies in the QotD context is even worse in the QotD context. If I were to ignore that convention I would assert that the trigger here certainly has a logic error, it will fail every time it runs, because I've never built a system that didn't permit recursive triggers and therefor (for me, anyway) it's a serious logic error to have a trigger that entails infinite recursion - I agree with Hugo that it's a serious error to have a trigger that doesn't contain SET NOCOUNT ON as its first statement, and although I wouldn't describe it as a logic error (and don't think he would either) I think it's the only sensible answer to fixing the query.
And as for a "total" of 3 rows - well, depending on all sorts of things one might get 6 or 4 or 3 or 2; your assumptions seem to rule out 4 and 2, so maybe we should be able to deduce from that something about what assumptions you have made which you didn't mention, but it certainly isn't enough to work out just what you are at in this utterly awful question.
Tom
-
Sean Lange (4/3/2014)
auke.teeninga (4/3/2014)
I think the trigger still contains a logic error...Currently it updates the datechanged even if the values remain the same. Maybe if the field was called DateUpdated a change wouldn't be required 😉
ALTER TRIGGER updateQuestions ON dbo.Questions
FOR UPDATE
AS
SET NOCOUNT ON
UPDATE Q
SET Q.datechanged = GETUTCDATE()
FROM inserted i
INNER JOIN dbo.Questions Q
ON I.QuestionID = q.QuestionID
where
IsNull(Q.QuestionTitle,'') <> IsNull(i.QuestionTitle,'') or
IsNull(Q.IsApproved, -1) <> IsNull(i.IsApproved, -1)
While that does meet some esoteric requirements this will perform very poorly because this is nonSARGable.
The bigger is that you have introduced a logic error by wrapping a bit column with IsNull. This changes NULL to 1. Don't believe on that one? Try casting -1 as bit and see what you get.
select CAST(-1 as bit)
Since there are no indexes on the table, absolutely nothing is SARGable - you can't make something SARGable if no index refers to it. There's an extra test, but that adds something that might be valid functionality and given the DDL provided in the question it makes SRFA difference to performance.
Tom
-
when ever I see a question from Andy, i think "what's the obvious catch"
which got me thinking, why is aliasing in trigger update not consistent? was the a red herring - as it will clearly give an error on a case sensitive collation 🙂 - obviously this was a red herring :w00t:
obviously I was way off track - missed the query plan.
But as somebody said 4/5 ain't bad!
-
auke.teeninga (4/3/2014)
Sean Lange (4/3/2014)
While that does meet some esoteric requirements this will perform very poorly because this is nonSARGable.
Good call on the bit field. I try to avoid them myself, so I had not encountered that behavior before.
I think even with the nonSARGablility of the where clause I think it might outperform the original tigger, due to the fact that no update (cq no lock) is needed when there's no change.
You see an index seek on a heap? Good heavens, has the worl changed totally while i wasn't looking?
Tom
-
TomThomson (4/3/2014)
You see an index seek on a heap? Good heavens, has the worl changed totally while i wasn't looking?
This has been possible since at least SQL 7.0, probably even before that.
Repro:
CREATE TABLE dbo.DemoTable
(KeyCol int NOT NULL PRIMARY KEY NONCLUSTERED,
DataCol int NOT NULL);
go
SELECT * FROM dbo.DemoTable WHERE KeyCol = 2;
go
The table has no clustered index, so it's a heap. The PRIMARY KEY is implemented with a (nonclustered) index. The query will probably (*) generate an execution plan with an index seek and an RID lookup.
(*) I didn't test it. If you get a table scan, add a bunch of rows to the table, then try again.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (4/3/2014)
TomThomson (4/3/2014)
You see an index seek on a heap? Good heavens, has the worl changed totally while i wasn't looking?
This has been possible since at least SQL 7.0, probably even before that.
Repro:
CREATE TABLE dbo.DemoTable
(KeyCol int NOT NULL PRIMARY KEY NONCLUSTERED,
DataCol int NOT NULL);
go
SELECT * FROM dbo.DemoTable WHERE KeyCol = 2;
go
The table has no clustered index, so it's a heap. The PRIMARY KEY is implemented with a (nonclustered) index. The query will probably (*) generate an execution plan with an index seek and an RID lookup.
(*) I didn't test it. If you get a table scan, add a bunch of rows to the table, then try again.
Quite right, I was misusing teh term "heap" to mean a table with no logical structure at all - no keys (not even a primary key), and no indexes. That is what the question was about and what I think Sean and Auke were talking about. I think maybe I'm reverting to terminology that has changed since I learnt it, I have a feeling that when I first saw the term "heap" it meant "unindexed set of rows" as opposed to "relation" which meant "set of rows indexed by a key", but could be so long ago that it's totally possible that it's just imagination on my part, and anyway I know that that's not what it's meant for the last 20 years or so.
Tom
-
TomThomson (4/3/2014)
what I think Sean and Auke were talking about.
Sean and myself were talking about a situation where the DB structure had already been fixed, so indexes and keys had been added.
-
Thanks for the Q
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
TomThomson (4/3/2014)
I think maybe I'm reverting to terminology that has changed since I learnt it, I have a feeling that when I first saw the term "heap" it meant "unindexed set of rows" as opposed to "relation" which meant "set of rows indexed by a key", but could be so long ago that it's totally possible that it's just imagination on my part, and anyway I know that that's not what it's meant for the last 20 years or so.The only thing I know is that within the context of SQL Server, a heap always refers to a table without clustered index. It may or may not have nonclustered indexes, and they may or may not be unique indexes.
As soon as you add a clustered index (either unique or nonunique), the heap is rebuilt into a table with a clustered index (sometimes refered to as a clustered table, but I don't think that this is official terminology).
Now, outside of the SQL Server context ... no clue! 😉
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Hugo Kornelis (4/4/2014)
TomThomson (4/3/2014)
I think maybe I'm reverting to terminology that has changed since I learnt it, I have a feeling that when I first saw the term "heap" it meant "unindexed set of rows" as opposed to "relation" which meant "set of rows indexed by a key", but could be so long ago that it's totally possible that it's just imagination on my part, and anyway I know that that's not what it's meant for the last 20 years or so.The only thing I know is that within the context of SQL Server, a heap always refers to a table without clustered index. It may or may not have nonclustered indexes, and they may or may not be unique indexes.
As soon as you add a clustered index (either unique or nonunique), the heap is rebuilt into a table with a clustered index (sometimes refered to as a clustered table, but I don't think that this is official terminology).
Now, outside of the SQL Server context ... no clue! 😉
Well, I believe that since the first MS SQL Server, or even earlier in Sybase SQL Server (or whatever they called it when MS shipped the Sybase product) your definition is the correct one. That's why I said it was 20 years or so ago for what I previosly suggeted it meant when you pointed out my error, which was of course a silly aberration caused maybe by senility of maybe just by alcohol or even perhaps by posting while 90% asleep.
Tom
-
Carlo Romagnano (4/2/2014)
I do not like question too long. I have just few minute to answer.A lot of useless words and none of the main one.
+1 agree!
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
Uwe Ricken (4/2/2014)
With respect - I know that preparation of a QoTD can be quite tricky and time consuming but this QoTD has the same problem as the last one; it's dealing with settings which are "not normal"The default for that setting is false, but it in this case it was enabled
This information hits the nail - but it was in the answer not in the question 🙁
4 of 5 correct; last wrong because of missing information!
+1 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
I got 4/5 right.Bur for getting the 5th one needs to change the default settings which could have mentioned in the question.
Steve - I want +1 for it 😉
as it is not my fault and i lost one point 🙁
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply