I'm creating a view that uses a statement with a WHERE clause similar to this:

WHERE
    (
        col1 IS NOT NULL
        OR
        col2 IS NOT NULL
    )
    AND
    NOT EXISTS (SELECT ...)

which takes an average of 10 seconds to run. However, when I try to save this query as a View, SQL Server (or the MS SQL Server Management Studio Client) "optimizes" the query to use this structure, instead:

WHERE
    (col1 IS NOT NULL AND NOT EXISTS (SELECT ...))
    OR
    (col2 IS NOT NULL AND NOT EXISTS (SELECT ...))

Slowing down the query to 6+ minutes. Is there any way to disable this behavior, so that the view uses exactly the SQL Query I give it?

up vote 14 down vote accepted

Don't use the SSMS wizards.

just type your CREATE VIEW dbo.Foo AS SELECT ... statement into a new query window and execute it.

This will stop it rearranging the query syntax to semantically equivalent but worse performing alternatives.

  • 4
    And all views, stored procs, UDFs, table, etc all objects created or changed should be in script anyway so they can be put into source control like all other code. – HLGEM Oct 31 '12 at 18:22
  • 1
    There are a few graphical SQL tools out there that are decent and won't do stupid stuff like this – Earlz Oct 31 '12 at 19:24
  • 2
    @Earlz true, but I think we all know we're talking about SSMS specifically, not all graphical tools in general. – Aaron Bertrand Oct 31 '12 at 19:47

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.