April 25, 2007 at 6:52 am
I have stopped using the Query Designer for a number of reasons**.
But when I create a query directly in the Management Studio .sql screen, I encounter (much too frequently) spurious errors (from the blue check on the right of the Execute button). In many cases, these can be cleared simply by deleting leading speces from the offending line and replacing them with one or more tabs.
I also often see these problems after using the Comment/Uncomment buttons.
Has anyone else see this? Is it something I am doing wrong?
** Problems with the Query Designer:
Discards query formatting, and makes no effort to format it's output.
Cannot drag and drop from the Object Explorer (particularly other tables in different databases on the same server.)
Seems not to have kept pace with the SQL engine -- e.g. does not allow pivot table specification in view editor, but the same query works fine just using CREATE/MODIFY View direct from SQL.
Adds the silly "TOP 100 %" to a view with an order clause; while this seems to work when you run the query while editing it, the order may not be preserved when later opening the view directly.
Again, how many of the Query Designer are caused by my own stupidity?
April 25, 2007 at 8:05 am
Adds the silly "TOP 100 %" to a view with an order clause; while this seems to work when you run the query while editing it, the order may not be preserved when later opening the view directly.
-- Although SSMS adds the TOP 100 PERCENT when you add an ORDER BY to a view specification (using an ORDER BY in a view is only permitted when the TOP clause is used) the fact that the ORDER BY is not honored by SQL when top 100 Percent is used is that the view is a dynamic table and SQL does not guarantee the order of the rows of a table unless you use an order by in the query (select * from MyView order by mycolumn).
As for the other problems you report, I haven't experienced them, probably becuase I don't use the Query Designer to build my queries or views. I prefer to write them myself. Not sure if you can build cte's using Query Designer, and I have written several views and procedures using cte's.
April 25, 2007 at 8:20 am
I've stopped using Query Designer myself; the first problems I noted were creating/and checking my own SQL statements. If you have not seen the spurious whitespace errors, it must be something I'm doing.
The bottom problems, including the "TOP" thing, were the reasons I stopped using Query Designer.
I think the CTE restriction is also an example of Query Designer not keeping pace with SQL. I use CTEs frequently. (They are my attempt to build queries (the Access terminology for SQL code -- which is why Access does not need Views) which reference other queries.
April 29, 2007 at 11:31 pm
about views with ORDER BY clauses in them: don't do it. it's a bug if the query designer is adding that. The order by belongs in the query that targets the view, not in the view definition itself.
The optimizer reserves the right to ignore the order by, which it sounds like you are running across.
See: http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx
---------------------------------------
elsasoft.org
April 30, 2007 at 2:53 pm
SQL only ignores the ORDER BY if you specify TOP 100 PERCENT. If your query specified TOP 10 PERCENT (or any value less the 100), the ORDER BY is honored.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply