none
SSMS 2008 - Can you change the intellisense suggestions?

    Question

  • Hi All,

    Is there any way I can change the list of keywords that intellisense suggests in the T-SQL editor window?

    I am being driven to distraction, because when I write AS it automatically changes it to ASCII, leading to gibberish syntax.  Since our company coding convention is to always require AS before column aliases then I get a lot of this!

    I could just turn the feature off, but it is useful in lots of other places.

    It must be possible to remove ASCII (Which I have used once in 10 years) or to add AS above it, if only the list was accessible. And MS employees care to help out?

    Thanks in advance,

    Richard
    Tuesday, August 25, 2009 7:32 AM

Answers

  • Hi Papy,

    I can reproduce your problem - it doesn't display any data types, indicating that the Intellisense is not aware that you're wanting a data type.  While using AS for aliases seems to work for me (that is, it doesn't bring up Intellisense), it seems to get a little confused when used inside a CAST.  It's possible that this is just an oversight, and will be improved on in future versions.

    I find Intellisense works consistently if you stick to a set of rules.  Make sure the query above is perfectly formed, and completed (with a semicolon at the end, or a GO batch terminator), and give Intellisense as much information to go on.  For example, if you're selecting from a table, type SELECT  FROM TableName (Intellisense should work for the TableName), and then go back to add the column names.

    I don't have a problem with SET.  In your example above, I can type:
    DECLARE @BeginDate AS Date
    DECLARE @EndDate AS Date
    SET @B = C('01/01/2009' AS Date[ESC])
    SELECT @B, @E

    As you can see, Intellisense doesn't help with the DECLARE statements.  It can understand variable names and function names as part of the SET statement, but the internals of the CAST function confuse it, leading to you having to hit escape to cancel Intellisense, or Control-Z twice to undo Intellisense.  This is just annoying, as it's now slowing you down, but hopefully the speed improvements when using it for table names and column names makes up for this annoyance.  I note also that if you use CONVERT, you don't get any Intellisense at all, which might help prevent DATEADD from popping up.

    Richard, I still can't get Intellisense into a situation where AS will bring up Intellisense.  Could you walk us through what your query looks like when the problem occurs?

    Wednesday, August 26, 2009 11:43 PM
  • Hello Jim,

    I have done a little test and i have had the same problem in the query windows of my SSMSE

    use [ComsTel] 
    go
    DECLARE @BeginDate AS date
    declare @EndDate as date
    SET @BeginDate = CAST('01/01/2009' as DATE)
    set @EndDate = CAST('01/07/2009' as DATE)
    select @BeginDate,@EndDate
    go
    declare @firstdate as date
    set @firstdate = CAST('29/07/2009' as DATE)
    select @firstdate 
    go
    select [Communications].[Date Communication] as 'Date of the call'
    from [Communications] 
    where [CommunicationID] = 155;
    go
    Worst , date is not recognized as datatype ( it does not exist among the suggestions of IntelliSense , even in the displayed list ). and i type on "space" or on ")", i am obtaining DATEADD. So i have to go back to eliminate the the useless "ADDD"
    Maybe a problem of install ?
    It is annoying as i am working with a database with date or time fields ( i have the same problem with the datatype time : no suggestion of time even after i have typed ti )

    The IntelliSense is working for many things, not for AS,DECLARE,SET,date,time , but it is working for [Date ( suggested Date Communication )

    SQL Server Dev 2008 RTM.

    Or maybe a problem with RTM, corrected with SP1 ? ( SP1 not installed for the moment )

    If you have an idea, a suggestion ( an option not activated...) , i would be thankful

    Have a nice day 
    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Wednesday, August 26, 2009 4:50 PM
  • AFAIK, it's not possible. It's more than just SSMS; there're hooks in the engine to enable intellisense.


    joe.
    No great genius has ever existed without some touch of madness. - Aristotle
    Thursday, August 27, 2009 6:17 AM

All replies

  • Hi Richard,

    I'm having a little trouble reproducing your problem.  When I type "SELECT FiscalYear AS", it doesn't offer any suggestions, as it realises that I've finish writing the column name, and it's expecting either a comma, an alias, AS, or FROM.  If I type "SELECT FiscalYear, AS" (note the comma), then I get "ASCII" offered up.  

    Are you typing "AS Alias" and then going back to fill in the column name? I must confess, I frequently have Intellisense suggest the wrong thing, forcing me to undo its suggestion, or hit escape to cancel it.  It's annoying, but overall the time savings are good enough.
    Wednesday, August 26, 2009 6:16 AM
  • Hello Jim,

    I have done a little test and i have had the same problem in the query windows of my SSMSE

    use [ComsTel] 
    go
    DECLARE @BeginDate AS date
    declare @EndDate as date
    SET @BeginDate = CAST('01/01/2009' as DATE)
    set @EndDate = CAST('01/07/2009' as DATE)
    select @BeginDate,@EndDate
    go
    declare @firstdate as date
    set @firstdate = CAST('29/07/2009' as DATE)
    select @firstdate 
    go
    select [Communications].[Date Communication] as 'Date of the call'
    from [Communications] 
    where [CommunicationID] = 155;
    go
    Worst , date is not recognized as datatype ( it does not exist among the suggestions of IntelliSense , even in the displayed list ). and i type on "space" or on ")", i am obtaining DATEADD. So i have to go back to eliminate the the useless "ADDD"
    Maybe a problem of install ?
    It is annoying as i am working with a database with date or time fields ( i have the same problem with the datatype time : no suggestion of time even after i have typed ti )

    The IntelliSense is working for many things, not for AS,DECLARE,SET,date,time , but it is working for [Date ( suggested Date Communication )

    SQL Server Dev 2008 RTM.

    Or maybe a problem with RTM, corrected with SP1 ? ( SP1 not installed for the moment )

    If you have an idea, a suggestion ( an option not activated...) , i would be thankful

    Have a nice day 
    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Wednesday, August 26, 2009 4:50 PM
  • Hi Papy,

    I can reproduce your problem - it doesn't display any data types, indicating that the Intellisense is not aware that you're wanting a data type.  While using AS for aliases seems to work for me (that is, it doesn't bring up Intellisense), it seems to get a little confused when used inside a CAST.  It's possible that this is just an oversight, and will be improved on in future versions.

    I find Intellisense works consistently if you stick to a set of rules.  Make sure the query above is perfectly formed, and completed (with a semicolon at the end, or a GO batch terminator), and give Intellisense as much information to go on.  For example, if you're selecting from a table, type SELECT  FROM TableName (Intellisense should work for the TableName), and then go back to add the column names.

    I don't have a problem with SET.  In your example above, I can type:
    DECLARE @BeginDate AS Date
    DECLARE @EndDate AS Date
    SET @B = C('01/01/2009' AS Date[ESC])
    SELECT @B, @E

    As you can see, Intellisense doesn't help with the DECLARE statements.  It can understand variable names and function names as part of the SET statement, but the internals of the CAST function confuse it, leading to you having to hit escape to cancel Intellisense, or Control-Z twice to undo Intellisense.  This is just annoying, as it's now slowing you down, but hopefully the speed improvements when using it for table names and column names makes up for this annoyance.  I note also that if you use CONVERT, you don't get any Intellisense at all, which might help prevent DATEADD from popping up.

    Richard, I still can't get Intellisense into a situation where AS will bring up Intellisense.  Could you walk us through what your query looks like when the problem occurs?

    Wednesday, August 26, 2009 11:43 PM
  • AFAIK, it's not possible. It's more than just SSMS; there're hooks in the engine to enable intellisense.


    joe.
    No great genius has ever existed without some touch of madness. - Aristotle
    Thursday, August 27, 2009 6:17 AM
  • Hi Richard

    I can't reproduce the issue what you meet; so I suspect the issue might be related to the query you run or the version of the SSMS; So please post the problematice query and tell me the version of the SSMS.

    Regards
    Mark
    Thursday, August 27, 2009 6:17 AM
  • Hi Richard,

     

    Did you ever manage to find a way to change this? I have this exact same issue as often under certain circumstances it want to auto complete AS as ASCII. The other more annoying thing is for it to auto complete the COUNT function to the table name countries. This would be as much of an issue if SQL intellisense worked like other auto complete systems where it reduced the number of options based on what was typed in, but when it lists the entire DB objects it's a little time consuming to scroll all the way back up to the COUNT function.

     

    Cheers,

     

    Cameron

    Monday, June 20, 2011 4:15 AM
  • I had no issue with "AS".

    Tried the examples above and also:

    SELECT TotalDue AS Sales FROM AdventureWorks2008.Sales.SalesOrderHeader
    
    


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam
    Tuesday, June 28, 2011 11:57 AM
    Answerer