HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

How can I prevent SSMS from re-writing my code when saving a view?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
preventcanssmswritingviewhowcodefromsavingwhen

Problem

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?

Solution

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.

Context

StackExchange Database Administrators Q#27970, answer score: 14

Revisions (0)

No revisions yet.