How can we help you today? How can we help you today?

Brief "Help" for the revised "Alias Assignment" options, plz

Could you post a few lines explaining the changes to the Aliases tab of the Options dialog, specifically the "Alias Assignment" section, please... Wouldn't want to pick the wrong one(s) :)

Or let us know when we can pick up a revised Help file...

Thanks.
PDinCA
0

Comments

5 comments

Sort by
  • Bart Read
    Hi Stephen,


    Sorry, I'm a bit confused by this one. We haven't made any changes to the Aliases tab since before the beta that came out in mid-December. There will be minor revisions to the helpfile that was distributed with that beta but no major changes.

    However, a brief overview. If alias assignment is switched on:

    - Checking "Include AS in alias definition" will generate aliases like this: TableName AS tn. Unchecked you'll just get: TableName tn.
    - "Learn aliases as I type" basically does exactly what it says. Let's say you don't like an alias that SQL Prompt assigns and then decide to change it. Later on in your session you insert the same table and SQL Prompt instead of inserting the original generated alias will insert the alias you changed it to, or rather the last alias you've changed it to. If you use different aliases for the same table in different places in your script it will pick the last alias it spots.
    - "Learn aliases when I open files or paste text." If checked SQL Prompt will spend a maximum of 5 seconds scanning through your script for aliases when you open a file or paste in a block of SQL. The reason that it's time-limited is that it's quite time-consuming and unfortunately because neither SSMS or QA are thread-safe I have to do this in a foreground thread and locking the editor up is a bit nasty really. If you're bothered about performance and you're working with relatively large scripts I wouldn't check this option.

    Hope that helps.


    Thanks,
    Bart
    Bart Read
    0
  • PDinCA
    If you look at the preliminary help, under the "Configuring SQL Prompt" topic, then the "Managing Aliases" item, you'll see that the "Alias Assignment" section shows a pair of radio buttons for On/Off and a checkbox subordinate to the "On" button, labelled "Include AS in alias definition". I'd add a snapshot if I could :(

    Build 819's dialog has the same On/Off radio buttons but there are now THREE subordinate checkboxes, as you kindly detailed in your response. Hopefully the Help file will get your prose for Production Release.

    Thanks for explaining the new options. May I suggest that you add a FOURTH option, "Learn NEW alaises as I type". Your explanation includes the common scenario where a table is featured multiple times in the same script with different aliases (self-joins must do this); I wouldn't want my regular alias to be overridden in these cases, but, where I've added a table to my schema and not added it to my User-defined list, I'd like SQL Prompt's smarts to add the table and first alias I use for it to my list.
    PDinCA
    0
  • Tilman
    Hi,
    If you look at the preliminary help, under the "Configuring SQL Prompt" topic, then the "Managing Aliases" item, you'll see that the "Alias Assignment" section shows a pair of radio buttons for On/Off and a checkbox subordinate to the "On" button, labelled "Include AS in alias definition". I'd add a snapshot if I could Sad

    Build 819's dialog has the same On/Off radio buttons but there are now THREE subordinate checkboxes, as you kindly detailed in your response. Hopefully the Help file will get your prose for Production Release.
    Yes, sorry, we will update the help file for the final release.
    Thanks for explaining the new options. May I suggest that you add a FOURTH option, "Learn NEW alaises as I type". Your explanation includes the common scenario where a table is featured multiple times in the same script with different aliases (self-joins must do this); I wouldn't want my regular alias to be overridden in these cases, but, where I've added a table to my schema and not added it to my User-defined list, I'd like SQL Prompt's smarts to add the table and first alias I use for it to my list.
    I'm not totally sure what you mean here. Could you expand a bit, please.

    Regards,

    Tilman
    Tilman
    0
  • PDinCA
    The abbreviated explanations for the three options given, with my comments after each one, were:

    - Checking "Include AS in alias definition" will generate aliases like this: TableName AS tn. Unchecked you'll just get: TableName tn.

    Understood and OK.

    - "Learn aliases as I type" basically does exactly what it says. Let's say you don't like an alias that SQL Prompt assigns and then decide to change it. Later on in your session you insert the same table and SQL Prompt instead of inserting the original generated alias will insert the alias you changed it to, or rather the last alias you've changed it to. If you use different aliases for the same table in different places in your script it will pick the last alias it spots.

    OK in principle until you hit the self join, or multiple use scenarios, where SP will "learn" to use the latest alias, which is not very friendly when I have a set of User-defined aliases. If I have dbo.Country whose alias is "c", then a chunk of code to find Customers with differring residence and ship-to Countries, such as 
    SELECT ind.IndividualID
     , ab.City
     , c.[Name]      AS ContactCountry
     , ab2.City
     , c2.[Name]     AS ShippingCountry
  FROM dbo.Individual ind
       INNER JOIN dbo.AddressBook ab
               ON ab.AddressBookID = ind.DefaultPrimaryContactAddressID
                  INNER JOIN dbo.Country c
                          ON c.CountryID = ab.CountryID
       LEFT  JOIN dbo.AddressBook ab2
               ON ab2.AddressBookID = ind.DefaultShippingAddressID
                  LEFT  JOIN dbo.Country c2
                          ON c2.CountryID = ab2.CountryID
 WHERE ab2.CountryID IS NOT NULL
   AND ab.CountryID  <> ab2.CountryID
 ORDER BY 1
    has SP "learining" to use c2, in place of c. Hence I don't use the option.

    - "Learn aliases when I open files or paste text." If checked SQL Prompt will spend a maximum of 5 seconds scanning through your script for aliases when you open a file or paste in a block of SQL.[/code]

    This is a good idea, provided the aliases are "standard/preferred" the first time SP encounters a particular table. Sadly, alias use in the legacy code I'm updating uses a, b, c, d, etc... NOTHING meaningful... I don't use this option either...

    What I was suggesting is that when SP encounters a table for which it has neither "learned" an alias, nor has one in the list of pre-defined aliases, it will add the "learned" alias to my user-defined list, with the opportunity for me to change the alias if I need to on its way into my list. It's highly likely that if I'm creating a table, the first time I reference it I'll use a "good alias" that I would likely want to use in perpetuity. So, if I add, just for discussion purposes, a hastily cobbled together table that deserves critical bombardment :wink:
    CREATE TABLE dbo.CountryStyle
     ( CountryStyleID   int  IDENTITY(1,1)
     , StyleName        nvarchar(50)
     , Aspect           nvarchar(20)
     , Format           nvarchar(200)
     , CONSTRAINT pk_CountryStyle PRIMARY KEY CLUSTERED ( CountryStyleID )
     )

GO
EXEC dbo.sp_addextendedproperty @name=N'TableAlias', @value=N'cs' , @level0type=N'USER',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CountryStyle'

    Then reference the table with an alias: 
    SELECT * FROM dbo.CountryStyle cs
    I'd like SP to "learn" my "cs" alias and add it to my User-Defined Aliases, or, at the very least, notice that I'd like it to be remembered because it is a NEW-to-SQL Prompt table.

    Hope this makes sense... Hope it's also clear why I wouldn't use either of the 2nd two exisiting checkbox options under normal circumstances, hence the suggestion for a fourth, less automatic but more manageable way of learning and subsequently using aliases.

    P.S., it would be cool if SP would refresh the schema automatically when it notices a CREATE TABLE is executed :D
    PDinCA
    0
  • Bart Read
    Hi Stephen,


    I imagine we'll revisit this in future to actually provide you with one or more suggested aliases after the table name and/or AS when automatic aliasing is switched off.

    The one comment I would make on all of this is that in your last example SQL Prompt would automatically suggest that "cs" alias anyway so as long as you didn't change it that's what it would always use.

    I hear you about the legacy code though: PITA when the aliases mean nothing, and that being the case I certainly wouldn't suggest you turn this option on. We could consider adding an option not to learn any aliases shorter than n characters. Something else we could do in the future, which has already been suggested elsewhere, is add an item to the context menu when you select the "tablename AS alias" text something along the lines of "Add to user defined aliases", then it's learned forever.


    Thanks,
    Bart
    Bart Read
    0

Add comment

Please sign in to leave a comment.