Blog
Tools for Writing T-SQL Queries in the Microsoft World
By Don Kiely | December 07, 2012
Most modern code development environments have support for writing code. In the Microsoft world, one of the primary tools is IntelliSense, which presents a list of objects and keywords as you type code. In SQL Server's Management Studio, a related feature that isn't really IntelliSense but helps you write good code is syntax checking, which marks T-SQL syntax errors with squiggly underlines when it detects a problem.
The Figure below shows IntelliSense in action. In a Query Editor window, with the database context set to AdventureWorks2012, start typing a SELECT statement. IntelliSense can't help much with "SELECT * FROM", but once you type that, it is obvious that the next element is a database name, a schema name from the database context, or a table name.
So IntelliSense pops up a list of possibilities, and as you continue to type, it narrows the possibilities. You can continue typing at this point, or press the TAB key to select Sales. The list contains every object name that would work in a FROM clause within the current database context. In this case, it is a couple of system objects that happen to have "sa" somewhere in their name, as well as Sales, which is a schema name in the AdventureWorks2012 database.
Once you have the schema name, type the period. IntelliSense pops up a list of schema objects that you may want, as shown in the Figure below, and a ToolTip if you hover the mouse over any of the items. You can use the arrow keys to move up and down the list, or continue typing. This shows how IntelliSense functions as both a typing shortcut as well as an easy reference to available objects in the context of the code.
IntelliSense is by no means perfect, and sometimes it is of no help if it can't identify any context for the statement you are writing. But when it can do its job, it can be a big help writing code.
TIP: If IntelliSense drives you nuts, particularly if you just can't get used to the lists popping up all the time, you can shut it off or customize it in simple ways. Go to Tools|Options, and drill down to the Text Editor|Transact-SQL|IntelliSense node. You can turn off syntax checking separately from IntelliSense. But Microsoft has vastly improved IntelliSense for T-SQL in Management Studio, and it now an effective productivity tool.
IntelliSense also helps with syntax checking. Make a spelling mistake in a statement, and even before you finish, as you can see in the Figure below where we have misspelled "SLECT," IntelliSense shows a red squiggly underline to indicate a problem.
You have to be careful with this, because the actual problem may not be in the current statement; it could be in the previous statement, but at least it provides the context for the problem. And IntelliSense often doesn't recognize valid objects, or takes a minute or more to get caught up with everything when you change database context. So if it works for you, make good use of it. If it is just an annoyance, turn it off. On a reasonably fast machine, IntelliSense usually performs well.
This post is an excerpt from the online courseware for our SQL Server 2012: Configuring Management Studio course written by expert Don Kiely.
