SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
MS SQL Server: no automatic aliases after a table name

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
MS SQL Server: no automatic aliases after a table name
Author Message
MarcinH



Joined: 18 May 2019
Posts: 7
Country: Poland

Post MS SQL Server: no automatic aliases after a table name Reply with quote
SQL Assitant v10.1.278
SSMS v17.9.1
MS SQL Server 2016 SP1, 2012

After FROM clause when I type few characters and choose a table name from the list, SQL Assistant often does not automatically add an alias to that name.
Sometimes it works, sometimes it doesn't.
I think it's a problem from SQL Assistant 10.1.x version.

Thanks
Marcin
Sat May 18, 2019 12:20 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
Please provide example of table for which is doesn't generate an alias automatically. Please also describe the context in which the SQL Assistant is used, the context is very important too.

If the statement is like UPDATE, ALTER, etc... (or somehow it "thinks" the context is not a SELECT query), it won't add an alias automatically.
Sat May 18, 2019 9:13 pm View user's profile Send private message
MarcinH



Joined: 18 May 2019
Posts: 7
Country: Poland

Post Reply with quote
It's just a simple select statement like: SELECT * FROM table.

SQL Assistant options:
Always Fully Qualify Object Name = With schema name
Name Matching Method = Name Contains Characters from Key String, Order by Best Match
Auto Add Aliases = Yes (without AS keyword)
Aliases Character Case = Lower case
Custom Aliases = Regular names

I think I know the pattern:

For the test let's assume the table is:
CREATE TABLE dbo.MyTable (col1 INT PRIMARY KEY, col2 VARCHAR(100));

1. I start typing: SELECT * FROM[space]
After the space after the FROM, the object list is automatically shown and typing just a few characters of MyTable (for example mt) I can choose the table name and the alias mt is automatically added:
SELECT * FROM dbo.MyTable mt.
Success!
2. I start typing: SELECT * FROM[space]
After the space after the FROM, the object list is automatically shown but I hit Escape. Then I'm typing just a few characters of MyTable (for example mt). I hit CTRL+SPACE to show the matching object list, then I choose MyTable. The table name is added but the alias is not added.
Failed!
3. The same scenario as 1. But when the alias mt is automatically added I delete the alias and the table name to the space (but not the space) after the FROM. I start typing, I hit CTRL+space. The table name is added but the alias is not added.
Failed!
4. I start typing: SELECT * FROM[space]
After the space after the FROM, the object list is automatically shown but I hit Escape. Then I'm typing exact schema name (dbo.), the object list is shown. Then I hit Escape. Typing a few characters of MyTable (for example mt). I hit CTRL+SPACE to show the matching object list, then I choose MyTable. The table name is added AND the alias mt is automatically added.
Success!

Conclusion: alias is only automatically added when the database object list is shown automatically or when I add the schema name.
Alias is not added when the table was chosen by CTRL+SPACE
Mon May 20, 2019 9:25 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7842

Post Reply with quote
Thank you for the provided examples. I discussed them internally and I believe both behaviors behaviors 2 and 3 are by design. The forced popup (Ctrl+Space) doesn't work as a continuation of the previous SQL statement when you type SELECT * FROM ... It's in place popup enabling you to paste object name rater then type it.

To see what I mean, start with an empty editor, press Ctrl+Space. similarly type
SELECT * FROM press Escape to dismiss the popup for SELECT * FROM ...
press Enter key couple of times
Enter /* some comment here */ then press Ctrl+Space and choose something. ..

In other words Ctrl+Space is not the same as an automatic sentence completing popup.

Just in case, there is another key Shift+Enter that can be used to select a name in an automatic popup and insert it as is without expanding columns and without adding an alias.
Tue May 21, 2019 8:55 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.