Adding intellisense and refactoring to the T-SQL editor

  • Article

One of the features I would like to see in our tools for SQL Server is a T-SQL language service. This would give customers features like intellisense, text completion, refactoring (e.g. renaming a table everywhere at once), error highlighting (as in Word’s red squiggly underlining), and host of other features that C# and Visual Basic have in Visual Studio 2005. Unfortunately, implementing a language service for T-SQL requires solving some difficult problems. Below I discuss the implementation problems and some ideas I have for fixing them. It would be great to hear your thoughts too.

T-SQL has a large and complex syntax with features that make parsing it with conventional parsers difficult. Language constructs like select … from … make intellisense hard to do since you need to have the from-clause written before you can offer intellisense on the select-clause yet T-SQL encourages people to write the select clause first. The only viable solution, I can think of, is when the user has typed select to automatically add the from-clause and move the cursor there. Perhaps in a future version of T-SQL we could support a from … select … syntax which would also be a more natural syntax. Moving users to the from-clause may be too intrusive, what do you think?

To allow intellisense and refactoring work well, an accurate semantic analysis of the T-SQL script needs to take place. However, T-SQL frequently refers to other databases and some parsing depends on server settings (e.g. ANSI nulls). To provide enough information for the language service to work correctly and efficiently, we will need to cache server and database related information locally in database models. Since we need to do this, it makes sense to build the language service on the modeling engine in Microsoft’s new graphical DSL framework. Some changes will be required but the modeling framework is close to a good AST framework and so it makes sense to reuse it.

Batch parsers are fast but for large files, they are not fast enough to support character at a time reparsing. There are ways to disguise some of this delay but ultimately intellisense becomes too slow to be usable and frustrates users. In addition, batch parsers require heavy modification to minimize the amount of change events from the object model they expose to tools. For example, imagine an explorer that lets you explore your T-SQL code like class view lets you explore your C# code. It would be unacceptable if at every key stroke all of the tables defined in the file disappeared and then reappeared in the explorer. Thus, a best practice for language services is to base them on a new incremental parser for the language. This involves a lot of work that we could use to improve other aspects of the T-SQL development experience. Is intellisense, refactoring, etc worth it?

A language service needs to handle errors robustly since users may be partly through typing a statement but intellisense etc should continue to work well. Batch parsers (i.e. compiler parsers) must handle large amounts of correct text quickly. Batch parsers are optimized for throughput not error recovery so adapting them for a language service is challenging. Adding to the complexity, in SQL Server 2005, T-SQL may contain imbedded XQuery expressions. For a language service parser, I would normally recommend either a recursive descent parser (for speed and great error recovery options) or an incremental GLR parser (for minimal reparsing costs, good error recovery and good ambiguity handling). However, the need to deal with multiple languages makes an incremental GLR parser the best choice. I particularly like the incremental GLR parser in Tim A. Wagner’s thesis when paired with the incremental semantic analysis in William Maddox’s thesis.

One of the nice things in Visual Studio 2005 is the managed language service (the wonderful new Visual Studio XML editor uses it). Provided you have a good incremental parser and an almost LR language then it is easy to build a great language service. It would be great to combine our graphical DSL framework, the managed language service and an incremental GLR parser framework to create a framework for building textual DSLs. This would allow people to create their own special purpose languages complete with intellisense, text completion, etc.

This posting is provided "AS IS" with no warranties, and confers no rights.

Comments

  • Anonymous
    September 26, 2005
    The comment has been removed
  • Anonymous
    September 26, 2005
  1. this just reinforces that SQL/T-SQL is a badly designed language

    2) http://www.upscene.com/ already supports some form of "intellisense" for MS SQL Server (among other dbmss). so does http://www.teratrax.com/tdm/help/intellisense.html

    3) while there is value in a clean and elegant solution, even a limited and crude (relatively) solution is sorely needed and way overdue for MS T-SQL tools. Even if you have to tie it to a specific T-SQL version and/or support only limited languange constructs it would still be very useful.
  • Anonymous
    September 26, 2005
    The comment has been removed
  • Anonymous
    September 26, 2005
    One argument is that if a compiler can understand all the constructs, aliases, schemas or not, prefixes or not.

    The solutions I have seen often suffer with the number of objects to hold information about. I would be interested to know how many objects the intellisense for say c# is designed to cope with (or tested against). Often the reason for the slowness is due to not understanding context properly, which is part of the problem with SQL with it being very flexible.

    Then there is the issue of dynamic SQL, how do you solve that one easily?

    You then have the issue of defferred object creation so objects might not exist at design time. I wish this was configurable (with out using schema binding)
  • Anonymous
    September 26, 2005
    There are several 3rd party editors our there that do a fairly good job at providing Intellisense (one happens to be mine ApexSQL Edit :) Providing Intellisense is difficult in a language like SQL because of the issues you mention and also because of the context since you can have aliases, or not, subqueries, or not, etc. One way we handle the select clause issue is to allow the user to predefine aliases for commonly used tables so that when a user types a specific alias in the select clause, the IDE looks up the alias and shows the appropriate columns.

    There are also other tools out there that do a decent job of Intellisense for SQL, although none of them are perfect and handle every case because of the difficulties mentioned.


    However, I don't think it's the problem with TSQL as much as SQL. TSQL is a very structured languages, it's SQL itself that allows for essentially free form flow. For example, you can have the following

    select 1 "Column", 1 as "Column", "Column"=1
    from Orders, Northwind.dbo.Orders, dbo.Orders, Orders ord

    All of which are the same in the select and from clauses, so trying to parse it becomes a complex task.
  • Anonymous
    September 26, 2005
    It looks to me like MS are going away from "most" developers having to hand-code TSQL anyway. They're introducing LINQ/DLINQ which gives you language constructs that ultimately map to SQL and, supposedly, optimised SQL much better than you or I would imagine.
    These do have intellisense, so TSQL intellisense suddenly becomes much less important in their eyes...... I wouldn't expect it in SQL-Server-Next either.
  • Anonymous
    September 27, 2005
    Hi,

    A workarroud for this can be SQLPrompt http://www.sqlprompt.com.

  • Anonymous
    September 27, 2005
    "Internally, several people have built T-SQL intellisense and people here seem happy enough to use these imperfect tools"

    Why not release it either unofficially, or as a hidden unsupported feature (registry setting dependent)? (e.g. MS SQL Server PowerToys). Or part of MS SQL Server Resource Kit. Or something...just get it out already. 5 years ago =)
  • Anonymous
    October 01, 2005
    Someone mentioned 'sqlprompt' -- its actually called PromptSQL and uses low-level system hooks to intercept keystrokes in Query Analyzer, VS.NET and SSMS, combined with the ANTLR parser to give SQL Intellisense: http://www.promptsql.com/
  • Anonymous
    October 03, 2005
    I am familiar with Ashton's work with ApexSQL Edit and it is quite elegant.

    I would like to add that some of the difficulties surrounding intellisense center on where the object caching occurs. If I am correct, SQL uses disk based caching of objects and other products are using memory. This impacts both the way in which intellisense is working and the user experience in using the intellisense (slow - fast, cumbersome - easy).

    I do think that Rommel is right in that our expectation of the great Wizard of MS would have been to have elegantly conquered SQL intellisense quite some time ago.

    Come on... Dorothy needs to get home... presumably to work on other higher level things than TSQL object sytanx...
  • Anonymous
    October 11, 2005
    Another one I've found and used a bit lately is SqlAssist (add-in to Visual Studio). Seems quite nice, as it supports some fancy features like templates, pretty formatting, running a script on several DBs at once, etc. Site says it is still in beta (alpha?), but seems pretty powerful for an early product to me. Anyway, thought some of

    http://www.roundpolygons.com

    ~Appdev
  • Anonymous
    February 05, 2006
    The comment has been removed
  • Anonymous
    February 08, 2006
    The comment has been removed
  • Anonymous
    February 08, 2006
    The comment has been removed
  • Anonymous
    May 22, 2006
    The comment has been removed
  • Anonymous
    August 03, 2006
    Not looking for Intellisense for the "select" command necessarily, but how about Intellisense for the functions that are available?  Anyone got something that works in TSQL???
  • Anonymous
    November 15, 2007
    Recently I ve been trying PragmaSQL it is like its name suggest a pragmatic approach to T-SQL development. You can check it out fromwww.pragmasql.com