The SQL that is Inserted

• 
  I'd like to have control over the formatting of the alias. Our coding standard, (like it or not), is to not use the AS when creating the alias, so having the ability to not generate the AS would be a feature for our team.
  
  About how intellisense works...
  It would be better usability if given I have the DOT option checked in the ]SQL Prompt 3 options that when I type the following:

  select *
  from Authors a
  where a.
  

  
  The dot on the last line should produce intellisense of columns for that aliased table. It is not doing that in 3.0.0.300

  HotAir September 01, 2006 17:40

  - 0 +
• 
  Automatic aliasing is fine by me. It is really effective, albeit that I would like the '*' to be prefixed as well. HotAir's remark about 'a.' is definitely my point as well.
  
  When joining, I would like to have an opportunity to choose between left and inner join, imho the mostly used joins. In our company we made an agreement we never right join, because always inner joining or left joining provides the most clear sql scripts (especially as we sometimes join more than 20 tables).
  
  I would like working Intellisense in dynamic sql scripts, but I don't know how hard that one is for you. It should not be that hard to combine a string and use your already built intellisense system on that. But who am I ...

  bert September 02, 2006 10:47

  - 0 +
• 
  * What do you think of the automatic aliasing of tables?
  Its ok with me. I prefer to have the aliasing work. It is very inconsistent and at times wrong with the candidate listings.
  
  * Do you like the auto insertion of table names when you select a column?
  ok.
  
  * If you insert columns from many tables should the tables be automatically inner joined of should there be some way of choosing left outer / right outer join?
  I think a method of picking should be presented,
  
  * Are there more features in the snippets that you would like?
  I would like to be able to specify a snippet and provide it one or more table names. From this, a list of columns should be displayed for me to choose from, defaulting to all, once this is done. A complete, "SELECT" for example, SQL statement should be created. That would be cool!

  brian26ny September 04, 2006 03:10

  - 0 +
• 

  HotAir wrote:

  I'd like to have control over the formatting of the alias. Our coding standard, (like it or not), is to not use the AS when creating the alias, so having the ability to not generate the AS would be a feature for our team.

  
  No worries, we'll add something to the Options dialog for that.
  

  About how intellisense works...
  It would be better usability if given I have the DOT option checked in the ]SQL Prompt 3 options that when I type the following:

  select *
  from Authors a
  where a.
  

  
  The dot on the last line should produce intellisense of columns for that aliased table. It is not doing that in 3.0.0.300

  
  Absolutely, that's a known issue and will be fixed for the final release.
  
  
  Thanks,
  Bart

  Bart Read September 04, 2006 11:14

  - 0 +
• 

  bert wrote:

  Automatic aliasing is fine by me. It is really effective, albeit that I would like the '*' to be prefixed as well. HotAir's remark about 'a.' is definitely my point as well.

  
  Good point. We'll sort this out.
  

  When joining, I would like to have an opportunity to choose between left and inner join, imho the mostly used joins. In our company we made an agreement we never right join, because always inner joining or left joining provides the most clear sql scripts (especially as we sometimes join more than 20 tables).

  
  We have a plan for this involving the use of modifier keys, e.g. to select a table/view:
  
  User hits ENTER -> INNER JOIN added to FROM clause
  User hits any other completion key (DOT, SPACE etc) -> table simply added to FROM clause (=cross join)
  User hits CTRL + ENTER => LEFT OUTER JOIN
  User hits SHIFT + ENTER => RIGHT OUTER JOIN
  User hits CTRL + SHIFT + ENTER => FULL JOIN
  
  How does that sound?
  

  I would like working Intellisense in dynamic sql scripts, but I don't know how hard that one is for you. It should not be that hard to combine a string and use your already built intellisense system on that. But who am I ...

  
  This *is* hard since you need to build up type information, get scoping right etc. However, we will be supporting it in the final release.
  
  
  Thanks,
  Bart

  Bart Read September 04, 2006 11:18

  - 0 +
• 
  Hi Brian,
  
  

  * If you insert columns from many tables should the tables be automatically inner joined of should there be some way of choosing left outer / right outer join?
  I think a method of picking should be presented,

  
  See my post above where I address this issue. If you can think of a better way then please let us know. What we're after is something that won't interrupt your typing rhythm, which is why so far we've tryed to avoid sublists.
  

  I would like to be able to specify a snippet and provide it one or more table names. From this, a list of columns should be displayed for me to choose from, defaulting to all, once this is done. A complete, "SELECT" for example, SQL statement should be created. That would be cool!

  
  Nice idea. Sounds dangerously like a Live Templates for SQL type of functionality. I can't guarantee this will get in for SQL Prompt 3, in fact unfortunately I can pretty much say it won't simply due to the amount of work we already have to do, however it's certainly something we're considering for future releases. I can't tell you definitively if/when there will be a SQL Prompt 4, however if SQL Prompt 3 does well it could be in the not too distant future, and if it does happen then this is exactly the kind of functionality I'd expect to see going in to it. Of course, I never said that and will officially deny ever saying it if questioned about it in a public forum.
  
  
  Cheers,
  Bart

  Bart Read September 04, 2006 11:23

  - 0 +
• 

  Bart Read wrote:

  * What do you think of the automatic aliasing of tables?

  Like others, I would like to control how the alias' are used. For example, we use a 3 character alias in our coding standard (refers to the way we name tables) and I'd like to be able to automatically use this 3 char standard. (I know that would be hard to do, but one can wish, right?)
  

  * Do you like the auto insertion of table names when you select a column?

  
  I think this is a nice feature. Kind of hard to keep track of sometimes though. I guess once I get used to the feel, it will work better for me.
  

  * If you insert columns from many tables should the tables be automatically inner joined of should there be some way of choosing left outer / right outer join?

  We tend to use outer joins on a regular basis, so I'd like to see some functionality there. Maybe a feature that can be toggled?

  What's good? What's bad? What's downright ugly?

  
  I really like the integrated functionality. I am using SQL Workbench, and I especially like the way SQLPrompt just "fits in". It took a little getting used to the schema box, but overall I am very impressed. One little nit though, when going back over text and editing, I find that unless I delete the inserted table alias, I end up getting duplicate "alias." entries, which invalidates the code. (For example, say I have SELECT o.customerID FROM dbo.orders as the code, and then I go back to "customerID" and change it to "orderID", what I then end up with is o.o.customerID....)
  
  First impression says this is going to be very useful in our environment. Will add more feedback as I use it more.
  
  Thanks..
  
  Ted

  tmalone September 04, 2006 15:40

  - 0 +
• 

  Bart Read wrote:

  User hits ENTER -> INNER JOIN added to FROM clause
  User hits any other completion key (DOT, SPACE etc) -> table simply added to FROM clause (=cross join)
  User hits CTRL + ENTER => LEFT OUTER JOIN
  User hits SHIFT + ENTER => RIGHT OUTER JOIN
  User hits CTRL + SHIFT + ENTER => FULL JOIN
  
  How does that sound?

  
  That sounds better. Enter and Ctrl+Enter are the most instinctive buttons since the combination is often used in other software. To couple these commands on INNER and LEFT (OUTER) join is imo the best way. The other two commands are also good, perhaps the FULL JOIN will be a bit difficult to remember, but who cares when it is relatively rarely used ?
  
  Conclusion: I definitely think you're on the right way to perfection!

  bert September 05, 2006 02:47

  - 0 +
• 
  Thanks guys, that's good to know. The thing that worries me about toggling the JOIN type between different settings is that it may be the wrong setting for the current context most of the time. On the other hand using modifier keys can be the start of a slippery slope: I don't want us to end up with a situation where using QA or SSMS is like using Emacs because that would be completely intolerable (apologies to any closet Emacs fans ).
  
  And sorry about the double alias insertion Ted: we'll be fixing that. I wanted to try to sort that out for the beta, but it's actually slightly tricky to pull off (isn't everything with this tool?) so I ran out of time. Like I say, it will be sorted in the final release though.
  
  
  Thanks,
  Bart

  Bart Read September 05, 2006 07:41

  - 0 +
• 
  With all those promises, you get yourself a huge responsibility :lol: ! Good luck fixing and building!

  bert September 05, 2006 08:07

  - 0 +
• 
  If you were to add hot-keys like that, i'd like to see it configurable on the options screen.
  
  Just makes it that bit easier.[/i]

  lysp September 05, 2006 08:40

  - 0 +
• 
  OK thanks, we'll give it some consideration.

  Bart Read September 05, 2006 09:36

  - 0 +
• 
  I do not like the auto insertion of the "AS" clause for table names. I would like to see it available as an option when I type a space after the word "AS", but not added automatically. Having to erase something you do not want seems more anoying than selecting something from the pop-up list.
  
  bill

  burrowsuw September 07, 2006 19:28

  - 0 +
• 

  burrowsuw wrote:

  I do not like the auto insertion of the "AS" clause for table names. I would like to see it available as an option when I type a space after the word "AS", but not added automatically. Having to erase something you do not want seems more anoying than selecting something from the pop-up list.
  
  bill

  
  Hi there,
  
  
  Thanks for your comments. We've added this as an option and it will be available in the final release.
  
  
  Cheers,
  Bart

  Bart Read September 08, 2006 07:47

  - 0 +

Add comment

Please sign in to leave a comment.