SQlpro

My feedback

  1. 201 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  9 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    SQlpro supported this idea  · 
  2. 377 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  15 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    SQlpro supported this idea  · 
  3. 10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    SQlpro supported this idea  · 
  4. 5 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    SQlpro commented  · 

    This is the most stupid idea ever. RDBMS vendors have all came to triggers against the CREATE ASSERTION statement for performances reasons...

    Let me tell you why with an example.

    You have two tables, one with customers wich are enterprise that have emitted an order and the other with prospectives that are also enterprise but had never ordered
    On common attribute is the legal state tax id.
    The businees rule il that you must never have the same legal state tax id in both two table.
    That is a quite simple ASSERTION, wich can be write in pure ISO normative SQL language, as :
    CREATE ASSERTION unique_state_tax_id
    AS
    CHECK NOT EXISTS(SELECT *
    FROM customers AS c
    JOIN prospectives AS p
    ON c.state_tax_id = p.state_tax_id)
    Assuming both table have only 1000 rows and no indexes are on this join, the treatment must read 1 million rows evrey time a INSERT, DELETE or UPDATE occur (even when it is embedded in a MERGE statement).
    With a couple of triggers, you can choose the events that are really needed. Validating this assertion in a case of DELETE is particlarely stupid.
    In a trigger you can also reduce the amount of rows to check by using the pseudo tables INSERTED or DELETED.

    For one row INSERTED, the treatment is now reduced to 1000 rows to verify this assertion (that's only 1000 times better... !).
    An finally the UPDATE() function into the scope of the trigger can decide if the check must be done or not. For instance, if an UPDATE occur and modify the name of a company, there is no need to check the assertion.

    For all theeses reasons, ASSERTION has not be implemented in the most common databases to preserve good performances

    Whith such a request, you try to kill the performances...

  5. 63 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  7 comments  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
    SQlpro supported this idea  · 
  6. 16 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    SQlpro shared this idea  · 
1 3 Next →

Feedback and Knowledge Base