locked
Not Equal to Operator in trigger RRS feed

  • Question

  • I have a following where condition in the trigger. If I use <> operator  in the where clause I get deadlocks. If I change to NOT IN  it works fine. This behavior happens only in SQL 2014. The usage of <> works fine in SQL2012. Is something changed in SQL2014?

    --Issue 

    Where i.Idnty <> t.dnty

    --no issue 

    where i.Idnty not in(select idnty from tabB)

    Monday, September 19, 2016 7:05 PM

Answers

  • Thanks for all the replays. I rewrote with NOT EXISTS and works fine. but my question is why it is different in SQL2014 with same code and same amount of data.

    Generally, performance in a relational database is anyhing but deterministic. The optimizer uses statistics which has been sampled from the data and from this it makes an estimate which is the best way to compute the query. For a query of any complexity, there is no chance for the optmizer to evaluate all possible plans - that would take too much time.

    Not surprisingly, there are changes to the optimizer to every release. If there was not, this would mean that the product stopped evovling. In the case of SQL 2014, Microsoft rewrote a component known as the cardinality estimator.

    While these improvements on the average are beneficial for the user community, there are always cases when they backfire. Queries that are poorly written or which does not have proper index support are more prone to this, in my experience.

    Tuesday, September 20, 2016 9:54 PM

All replies

  • Compare the execution plans of the query on both versions to see if they are the same.  Most likely, the plans are different and the SQL 2014 version is more prone to deadlocking because more data are touched.

    If the database is in SQL 2014 compatibility level, the new cardinality estimator is used by default.  This is generally beneficial but there are cases where regression occurs.  If the plans are different, try adding the QUERYTRACEON(4891) hint to the problem query.  Alternatively, change the database compatibility level to SQL 2012.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Monday, September 19, 2016 9:30 PM correct race flag number
    Monday, September 19, 2016 7:18 PM
  • Is this column nullable?

    Can you provide the whole trigger's code?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 19, 2016 8:06 PM
  • No this column is primary key. 
    Monday, September 19, 2016 8:33 PM
  • Can you show the code, please? Why would you use WHERE PK <> Another PK ? It doesn't look like correct query to me. I'd rather use NOT EXISTS (...) or something like this. May be it's a good thing that this query deadlocked, so you can write a better version.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, September 19, 2016 8:39 PM
  • What is the column type?

    Ricardo Lacerda

    Monday, September 19, 2016 8:45 PM
  • I have a following where condition in the trigger. If I use <> operator  in the where clause I get deadlocks. If I change to NOT IN  it works fine.

    <> and NOT IN is logically not really the same thing, so maybe the answer to why <> does not work is that the query is incorrect. Incorrect queries are more prone to cause deadlocks, because they read too much and are too wild.

    But without seeing the trigger code, all you will get is wild speculations.

    • Proposed as answer by Riaon Tuesday, September 20, 2016 4:42 AM
    Monday, September 19, 2016 10:15 PM
  • Thanks for all the replays. I rewrote with NOT EXISTS and works fine. but my question is why it is different in SQL2014 with same code and same amount of data.
    Tuesday, September 20, 2016 4:23 PM
  • Did you read Erland's reply? I think changes in SQL Server optimizer finally revealed problems with that query that have been there all that time.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, September 20, 2016 4:36 PM
  • Thanks for all the replays. I rewrote with NOT EXISTS and works fine. but my question is why it is different in SQL2014 with same code and same amount of data.

    Generally, performance in a relational database is anyhing but deterministic. The optimizer uses statistics which has been sampled from the data and from this it makes an estimate which is the best way to compute the query. For a query of any complexity, there is no chance for the optmizer to evaluate all possible plans - that would take too much time.

    Not surprisingly, there are changes to the optimizer to every release. If there was not, this would mean that the product stopped evovling. In the case of SQL 2014, Microsoft rewrote a component known as the cardinality estimator.

    While these improvements on the average are beneficial for the user community, there are always cases when they backfire. Queries that are poorly written or which does not have proper index support are more prone to this, in my experience.

    Tuesday, September 20, 2016 9:54 PM
  • Where is the code for this trigger? Where is the DDL the table. This fires on? Are you always rude to people you work with or just on forums?

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, September 21, 2016 2:58 AM