SQL Prompt 6.3 beta (Now closed)

Provides intelligent code completion for SQL Server editors.

Moderators: Anu Deshpande, Aaron Law, David Priddle

Postby PDinCA » Thu Mar 06, 2014 7:30 am

Attributes used to come first for WHERE clauses - now they are last and that's highly counterproductive. I get other table then function names before columns. Is it just me? Using the 188 build, win8.1 x64, SQL2012EE
PDinCA
 
Posts: 517
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby Aaron Law » Thu Mar 06, 2014 12:11 pm

Hi PDinCA,
I'm having difficulty recreating your issue - I always get columns suggested first on a where clause. Would it be possible to get a script or a screenshot of where this is happening for you?

Mr.DiGi, Unfortunately I don't think there's enough logging in the current build to help figure out where qualify object names is going wrong. Does it hang every time for you? Even on older, non-beta builds?
Aaron Law
 
Posts: 262
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby PDinCA » Thu Mar 06, 2014 11:17 pm

Yesterday's script:
Code: Select all
INSERT INTO dbo.t_company_custom_def
     ( custom_id
     , custom_company_id
     , custom_xml
     , update_date
     , update_by
     )
SELECT @NewID
     , 30
     ,[custom_xml]
     , '20140305 15:45'
     , 'me'
  FROM [xcp_ref].[dbo].[t_company_custom_def] ccd
 WHERE co

First off, having completed the WHERE and a space, it gave me ccd, the alias, as expected, and correct. I typed c and the ccd alias reamined at the top, followed by columns, so I typed an o, to get to "WHERE co" as in the code above. That's when the whole list changed to a list of f(x) functions, headed by COALESCE, then CONTAINS, then a bunch of tables (I have them listed by schema), then a slew of sys.functions, and I don't see the "custom_COmpany_id" (in the enlarged pop-up) that I expected my CO typing to bring to the top of the list. SQL Prompt at this point becomes useless for rapid code completion.

I would really like to send you a SnagIt or Snip, but Windows8.1 has completely destroyed my ability to use them. I hope you can create a simple table like the above, and reproduce the problem...

Failing that, a remote diagnostic session would be fine by me.
PDinCA
 
Posts: 517
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby Aaron Law » Fri Mar 07, 2014 12:25 pm

Hi PDinCA,
I just gave your script a try and I think this behavior might be by design. Prompt will display any objects matched by the prefix first (COALESCE, CONTAINS etc all start with CO) followed by any partial matches.

I get the column listed much further down as it's not considered that "good" a match yet:
Image

To get a better match against the columns you should be able to type the first letter or two of each word (eg. Typing "cu_co", "cco" or even "_co" will give a better match against custom_company_id)

Edit: as of 6.3.0.240 the columns should be at the top of the partial matches list, objects matched by a prefix are still first however.
Aaron Law
 
Posts: 262
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby PDinCA » Tue Mar 11, 2014 6:20 pm

I must admit to being surprised by the volume of what I will call "noise" listed above potential columns that are partially matched.

Thinking about the context of the SQL Prompt suggestions, when one is writing a SQL WHERE clause, how frequently is a column in a FROM clause the actual subject of the WHERE versus some obscure system function, another table name (huh?), or a function? I'd suggest that in excess of 95% of the time the COLUMN is what is wanted... It is acknowledged BAD PRACTICE to use functions in WHERE clauses anyway, or so I believe.

I'm out of votes on SQL Prompt (two logins' worth), so may I here suggest:

Provide an option to "always list columns in suggestion lists, even partial matches, above any functions of any kind".

In over 25 years of coding SQL, I can safely say the number of times I want a built-in or sys. function as the FIRST artifact after my WHERE is 0.05% of the time, or less!

Doesn't "columns first" simply just make sense? Or maybe I really am missing something...
PDinCA
 
Posts: 517
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Postby Aaron Law » Tue Mar 18, 2014 3:18 pm

Hi PDinCA,
Could you give 6.3.0.252 a try and see if that's closer to what you'd prefer? (We normally sort by prefix/partial match and then the object type, build 252 should reverse this so columns and aliases are kept at the top for the WHERE clause)
Aaron Law
 
Posts: 262
Joined: Fri Jun 28, 2013 10:56 am
Location: Red Gate Software

Postby PDinCA » Wed Mar 19, 2014 12:13 am

Thanks Aaron, installed and makes a lot more sense. Tried several partial matches on wide tables and everything looks good. Appreciate the swift turnaround.
PDinCA
 
Posts: 517
Joined: Mon Jul 25, 2005 11:42 pm
Location: Costa Mesa, CA, USA

Previous

Return to SQL Prompt 6

Who is online

Users browsing this forum: No registered users and 0 guests