Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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.

share|improve this answer
6  
+1 IMHO you could full stop after the first sentence. –  Aaron Bertrand Oct 31 '12 at 17:54
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 posting your answer, you agree to the privacy policy and terms of service.

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