SUMMARY:
SSMS Query Designer chokes on analytic functions. Is there any
workaround for this?


EXAMPLE:
This query runs fine:
SELECT
*
,ROW_NUMBER() OVER (PARTITION BY TABLE_SCHEMA, TABLE_TYPE ORDER BY
TABLE_SCHEMA, TABLE_TYPE, TABLE_NAME DESC) AS row_selector_nr
FROM
INFORMATION_SCHEMA.TABLES

But highlight the query, right click and choose "Design Query in
Editor . . "

You will get an error dialog box that says:
"The text that you have selected cannot be represented graphically
using the Query Designer. Please select just the text necessary to
design a SELECT, DELETE, INSERT or UPDATE statement."

The query designer is choking on the ROW_NUMBER() function.


DETAIL:
I generally prefer to simply write my SQL queries, but a lot of
clients like to use the Query Designer. And for just dashing off a
quick join, re-aliasing, etc, the designer is quite nice. I would
like to switch between the two. Edit the code to fix the designer's
mistakes, but then paste the code into the designer to take advantages
of its strengths.

When my needs involve an analytic function, this issue keeps me from
using the designer. Does anyone know if there is a way to make the
designer accept this function?

bill (billmaclean1 (AT) gmail (DOT) com) writes:
Yes, don't use the Query Designer. It's a piece of crap. For reasons
you just have discovered. There are plenty of constructs it does not
support. It tends to put in TOP(100) PERCENT ORDER BY in view definitions
which is useless. It also has quite funky formatting of the queries.

Yes, it may seem comfortable for the novice, but that is just like
the bikes we had when we were kids. They had three wheels, or if they
were two-wheeled, they had two small support wheels. But there was a
point when mom and dad took those away, and told us that it was time
that we learn to master the bicycle as it is meant to be used.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

I was afraid this was the answer. There are a lot of shortcomings,
but that fact is that clients like the Designer. Redgate solves the
Designer's annoying habit of absolutely destroying the format of the
code, which makes the idea of flipping between the designer and code
tweaking very attractive. I think the biggest shortcomings of the
designer are:

1. Incredibly horrible formatting of the code, including crazy over
use of parenthesis in the WHERE clause.
2. Designer often puts filter in the wrong place. On OUTER joins, at
least one of the filters often belong in the join, not the WHERE.
ASFAIK, the Desginer will always put it in the WHERE (not talking
about cases where it puts the filter in a HAVING with GROUP BY).
3.Weak support for CTE's
4. No support for analytic functions.
5. ANY use of the TOP operator. I am not sure that operator should
even exist at all, and the tool should certainly not add it to
queries.

Has anyone heard if the Designer will be improved in Denali?


Thanks,

Bill

bill (billmaclean1 (AT) gmail (DOT) com) writes:
I doubt.

One problem with a thing like the Designer is that there is only so much
you can do with a graphical interface. How would you visualise a row number?
A running sum (improvement to the OVER() clause added in Denali.)

Of course it doesn't help if what you have is rotten from the bottom up.

Personally, I think the best improvement that Microsoft could do is to
pull it. Alas, it is still there.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Jul 1, 5:17*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
While I don't have much trouble writing my own SQL I am spelling
impaired. Or, rather, I have trouble remembering how a field name was
abbreviated or misspelled.

I find it easier to control the assistive logic in the designer that I
do in the editor, so I use it for my fist cut at the query. or clause
making sure I pull in all the tables and field names I need.

After that, I agree with you. Forget the designer.

Fred.

I agree that graphics are inherantly limited. Trying to graphically
build an analytic function would be difficult. I would just like the
designer not to choke on a query (that may or may not be inside of a
CTE) that is created with valid SQL syntax.

The idea would be this: Someone comfortable with advanced SQL could
write whatever they need for a query / view. But if, say, a column
from an already joined table needs to be added later, a less
experienced person ought to be able to open the query in Designer
without trashing it.

The designer currently represents CTE's (that don't use analytic
functions) as just another box on the painter surface, which is fine.
They need to make it so that designer will recognize the code for
analytic functions and put the resulting column in the appropriate
box. I don't think that is all that different than SELECT A + B AS C
from <table>

THanks,

Bill

On Jul 2, 1:30*am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote: