I have wished for powerful and instant autocomplete in SQL editors for a long time. It never came - autocomplete in all database managers is pretty basic.

Let's see what we have prepared in SQL Studio.

We are offering table autocomplete in the FROM clause - standard. However, we made some innovations to increase productivity.

As soon as you type SELECT, we provide a list of tables right away and then expand to SELECT * FROM table.

When you are writing joins in the FROM clause, we know table relationships and are offering ON expressions based on actual foreign keys in joined tables.

WITH clause common-table expressions are fully supported. Not only is autocomplete working in the SELECT statements of the WITH clause items, we are providing CTEs where needed in all statements supporting WITH clause.

Expanding columns offers

  • star (*)
  • each column
  • all columns (comma delimited)

If your tables are aliased, we use those aliases to qualify the columns.

Our autocomplete engine actually relies on our static analysis engine. We track scoping and declarations in all parts of the complex statements and offer only items valid in that specific scope.

Plans for the future

Long ago, in a SSMS extension called SQL Format I built an autocomplete engine that offered checkboxes next to each item. We'll introduce that in SQL Studio as well. You will be able to select items with a space. Then, pressing ENTER will expand everything at once. For columns, you will get a comma-separated list. For tables, you will get INNER JOINs with proper ON expressions.

Performance

For autocomplete to be perceived as real-time, it needs to respond to your typing in less than one-tenth of a second. Processing that under a 100ms on a desktop machine isn't that hard. Doing that in a cloud when typing in a browser and returning result to the browser is no small feat. How we do that exactly is a topic for another post.