ApexSQL Complete vs. SSMS – Part 4 – SSMS IntelliSense add-in and native SSMS IntelliSense

In the previous articles ApexSQL Complete vs. SSMS – Part 1 – Object recognition and ApexSQL Complete vs. SSMS – Part 2 – Auto-complete DDL and DML statements, we have presented two features of ApexSQL Complete’s intellisense tool: context based recognition of typed terms and support for auto-complete DDL and DML statements. In the third ApexSQL Complete vs. SSMS – Part 3 – Creating and using T-SQL code we compared ApexSQL Complete’s and SQL Server Management Studio’s snippet features.

Now, we are going to compare ApexSQL Complete’s auto – complete feature and SQL Server Management Studio’s IntelliSense.

SSMS and ApexSQL Complete both use an automatically generated in-memory database with SQL database names and SQL object names to store the generated hint list. SSMS and ApexSQL Complete are also parsing the current script in the SQL query editor including the objects from the script into the in-memory database.

The SSMS IntelliSense hint list

The implementation of SSMS IntelliSense is based on detecting periods, or other predefined characters, and keystroke combinations (“Ctrl + J” and “Ctrl + Space”). When a user types any of these characters or the keystroke combination, SSMS IntelliSense offers suggestions in a hint-list:

The ApexSQL Complete auto – complete feature hint list

The ApexSQL Complete auto – complete feature is the context sensitive feature, meaning that ApexSQL Complete parses the code while user is typing and shows the hint list that correlates with the cursor position in the query. The hint list is shown automatically by pressing either the Tab or the Space key. ApexSQL Complete auto – complete feature is aware of the current script context because it uses algorithms that allow keywords separation into meaningful executable units:

Content of the SSMS IntelliSense hint list

By pressing the “Ctrl + J“ or “Ctrl + Space” key combinations after the SELECT statement, SSMS IntelliSense will offer only the schema list and list of tables under the dbo schema, but not the columns list:

To get to the table column in SSMS typing the schema and the table names is necessary. When a user types a schema/owner name in the SQL query editor and selects the schema from the hint list, they must type the “.” key and SSMS IntelliSense will search for objects under the e.g., Person schema and display them in the hint-list:

After selecting the table and pressing the “.” key user will be offered to select from the columns hint list

Content of the ApexSQL Complete auto-complete feature hint list

In ApexSQL Complete typing schemas or table names is not required. All available SQL objects and columns are listed after pressing the Tab or the Space key. ApexSQL Complete auto – complete feature is able to recognize that after the SELECT statement, the auto-complete list can contain only objects that can return some data (functions, views, tables etc.):

After choosing an object in the hint list, the SQL query will be auto-completed saving the time for typing the rest of the query:

Recognizing the objects from non-executed queries in SSMS IntelliSense

Within a Transact-SQL script or a batch file in the database engine query editor, SSMS IntelliSense supports objects that have been specified in a CREATE or ALTER statement and do not yet exist in the database because the statements have not been executed.

For example, the following code in the SQL query editor is still not executed:

USE AdventureWorks2014;
GO
CREATE TABLE TestTable( PrimaryKey int PRIMARY KEY ,
FirstName nvarchar( 50 ),
LastName nvarchar( 50 )
);
GO

After typing the SELECT statement SSMS IntelliSense will list PrimaryKey, FirstName, and LastName as possible elements in the select list, even if the script has not been executed and the table does not yet exist in AdventureWorks2014 database

Clicking the ENTER tab or selecting the table from the list and pressing the “.” key will give the list of the TestTable columns:

Recognizing the objects from non-executed SQL queries in ApexSQL Complete auto – complete feature

ApexSQL Complete auto – complete feature also supports objects specified in non-executed CREATE or ALTER statement that do not yet exist in the database. After typing the SELECT statement, ApexSQL Complete auto – complete feature will offer the sorted hint list. The table from the query window will be first on the list and highlighted. All table columns will be listed as well. This way, ApexSQL Complete allows user to pick the wanted table from the hint list quickly, opposite to the SSMS IntelliSense:

See also:

ApexSQL Complete vs. SSMS – Part 1 – Object recognition
ApexSQL Complete vs. SSMS – Part 2 – Auto-complete DDL and DML statements
ApexSQL Complete vs. SSMS – Part 3 – Creating and using T-SQL code
ApexSQL Complete vs. SSMS – Part 5 – Options not supported by SSMS intellisense