March 30, 2012 at 17:44Developer Toolkit: SQL Assistant
I work as a database developer (MS SQL) and most of my work is writing T-SQL code. Working in Query Analyzer, and then in Management Studio (2005, 2008, 2008R2), I really lacked editor functions and additional development environment functions aimed specifically at the process of writing code, building queries, etc. ... Compared to MS Visual Studio or Embarcadero RAD Studio, what could the Query Analyzer offer? Change the indentation of the code block, change the case of the selected text, comment out and cancel commenting on the code block, drag the name of the table or field (s) into the editor window, script the selected object. But it lacked a full IntelliSense. All these convenient auto-completion functions, interactive hints for the dialed code, a choice from the list of objects, etc.
With the release of MS Management Studio 2005, the situation has not changed. And only in MS Management 2008 appeared Transact-SQL IntelliSense. Yes, there was an autocompletion of the typed word, a list of database objects and table fields, syntax highlighting (begin ... end, ()). But before that, I began to use a third-party utility SQL Assistant from SoftTree Technologies. Amid the opportunities that SQL Assistant provides, the native IntelliSense is just oak. But is it possible to get something more, because it is SQL, and not an object-oriented language? Can! In this article, I would like to talk about the SQL Assistant utility, and more specifically about the function and techniques that I use when writing SQL code.
SQL Assistant is a toolkit for developers and database administrators. It can be used when working on projects of any degree of complexity, from small databases to complex corporate systems. The software is ready for use immediately after installation and does not involve the suspension of current work processes. The toolkit improves the productivity of SQL developers and guarantees high quality code. The package also includes excellent tools for checking the correctness of SQL syntax, mechanisms for completing syntactic constructions, fast navigation tools through program code, and much more. Users will also have an integrated spellchecker at their disposal.
SQL Assistant is integrated into Management Studio and all its commands are available through the main menu item or through the context menu of the code editor.
Here are the main features:
Basic information can be found on the official website and in the documentation (links at the end of the article). And here I want to talk about the most important function of this utility, the assistant itself - a context-sensitive pop-up window that appears in the code editor.
The assistant provides assistance depending on the place where he was called, starting from tips for automatically completing the typed words and ending with building links between the tables. Writing a query with a bunch of several tables takes much less time. You will no longer be mistaken in the name of the tables and fields, there is no need to remember the primary key fields and foreign key fields to build relationships. You only need to specify the names of the tables from the list, and SQL Assitant itself will finish the join construct based on the selected foreign key.
I start writing a request from the from section. The name of the first table is indicated, I type inner join and the assistant in the window shows a list of tables to which foreign keys from the Product table exist. Fields highlighted in red will be used to join two tables. I select a connection to the ProductInventory table and get the code:
Next, I continue to build the query, type inner join and the assistant shows a list of tables to which foreign keys already exist from the ProductInventory table, but connections from the Product table are also available.
Link building works in the from section, i.e. and for select, insert, delete, update. If it is necessary to build a connection between tables that are not connected by a foreign key, then you need to specify these tables and SQL Assistant can offer to build a relationship by fields with the same names.
Received request code:
Bring in the desired form using the formatting function. Code formatting rules are customizable.
By moving the cursor to the select section, the assistant will show a hint for selecting the fields that will be included in the selection from the tables listed in the from section. The same principle in any section of the request (where, group by, order by).
In addition to information about the type of field, for the fields that make up the primary key, an icon is displayed in the form of a yellow key, a green key - fields of a foreign key, lightning - an index is built on the field.
Other objects from the current database of other server databases are also available in the list. If you select a field from a table that is not in the from section, then it will be automatically added to the from section. It remains only to add the necessary conditions for communication.
In general, the following objects with their components can be displayed in the list:
It all depends on the context of the assistant call. So, if you write the insert Production.Product construct, then when you call the helper, you can select the required fields of the table for which the insert statement is written.
But it’s more convenient to use an assistant at the time of writing the operator itself. After I wrote the insert statement, the assistant prompts me to select a database object.
After I select the necessary table, I will automatically receive the following code:
It is similarly obtained with the update operator. Code is automatically generated with a similar input sequence:
You can even declare variables based on table structure. I type declare and select the Location table, as a result, the code is generated:
Build a procedure or function call.
Even those objects that are created or declared in the code become available in the assistant window. It takes into account the creation of temporary tables, the declaration of variables, including table ones.
If I meet a variable by code, then holding down the Ctrl key and clicking on this variable, I will move to the place where this variable was declared. But it’s easier to move the cursor to this variable to get a tooltip that will indicate the type of the variable. For the table, its structure and links will be displayed for a script to create, quick view of the contents and more information. System
For the procedure - a list of parameters, etc. for other objects.
For built-in functions, a tooltip with a brief description of the function. And the assistant will help with entering the parameters.
Here are the main features that I use daily while working in Management Studio. These are far from all the features of SQL Assistant. There are many more interesting functions, features, pleasant things. But, I think that building links, a context list for selecting tables and table fields can already ease the work of a programmer by an order of magnitude. By the way, SQL Assistant keeps statistics on how many characters the user entered and how many characters the assistant added, and then the percentage of productivity growth is calculated from this data.
Official site of SQL Assistant Program
Documentation (PDF, 7.2Mb)
With the release of MS Management Studio 2005, the situation has not changed. And only in MS Management 2008 appeared Transact-SQL IntelliSense. Yes, there was an autocompletion of the typed word, a list of database objects and table fields, syntax highlighting (begin ... end, ()). But before that, I began to use a third-party utility SQL Assistant from SoftTree Technologies. Amid the opportunities that SQL Assistant provides, the native IntelliSense is just oak. But is it possible to get something more, because it is SQL, and not an object-oriented language? Can! In this article, I would like to talk about the SQL Assistant utility, and more specifically about the function and techniques that I use when writing SQL code.
Theory
SQL Assistant is a toolkit for developers and database administrators. It can be used when working on projects of any degree of complexity, from small databases to complex corporate systems. The software is ready for use immediately after installation and does not involve the suspension of current work processes. The toolkit improves the productivity of SQL developers and guarantees high quality code. The package also includes excellent tools for checking the correctness of SQL syntax, mechanisms for completing syntactic constructions, fast navigation tools through program code, and much more. Users will also have an integrated spellchecker at their disposal.
SQL Assistant is integrated into Management Studio and all its commands are available through the main menu item or through the context menu of the code editor.
Here are the main features:
- Code entry assistant
- Code navigation (code structure, document map)
- Code formatting
- Patterns and Code Generators
- Refactoring Functions
- Interactive help system for entering SQL constructs
- Obtaining an object script (script for creating a table, procedure, function, etc.)
- Quick view table
- Scripting, importing, exporting data
- Separate SQL Execution
- SQL Execution Scheduler (using Windows Scheduler)
- Test data generator
- Unit Testing Framework
- Background syntax checking
- Query Performance Analyzer
- Integration with version control system
- Spell check
- Code comparison
- Bookmarks
Basic information can be found on the official website and in the documentation (links at the end of the article). And here I want to talk about the most important function of this utility, the assistant itself - a context-sensitive pop-up window that appears in the code editor.
Practice
The assistant provides assistance depending on the place where he was called, starting from tips for automatically completing the typed words and ending with building links between the tables. Writing a query with a bunch of several tables takes much less time. You will no longer be mistaken in the name of the tables and fields, there is no need to remember the primary key fields and foreign key fields to build relationships. You only need to specify the names of the tables from the list, and SQL Assitant itself will finish the join construct based on the selected foreign key.
I start writing a request from the from section. The name of the first table is indicated, I type inner join and the assistant in the window shows a list of tables to which foreign keys from the Product table exist. Fields highlighted in red will be used to join two tables. I select a connection to the ProductInventory table and get the code:
SELECT
FROM
Production.Product p
INNER join Production.ProductInventory pi1 ON pi1.ProductID = p.ProductID
Next, I continue to build the query, type inner join and the assistant shows a list of tables to which foreign keys already exist from the ProductInventory table, but connections from the Product table are also available.
Link building works in the from section, i.e. and for select, insert, delete, update. If it is necessary to build a connection between tables that are not connected by a foreign key, then you need to specify these tables and SQL Assistant can offer to build a relationship by fields with the same names.
Received request code:
SELECT
FROM
Production.Product p
INNER join Production.ProductInventory pi1 ON pi1.ProductID = p.ProductID
INNER join Production.Location l ON l.LocationID = pi1.LocationID
INNER join Production.ProductReview pr ON pr.ProductID = p.ProductID
Bring in the desired form using the formatting function. Code formatting rules are customizable.
SELECT
FROM
Production.Product p
INNER join Production.ProductInventory pi1
ON
pi1.ProductID = p.ProductID
INNER join Production.Location l
ON
l.LocationID = pi1.LocationID
INNER join Production.ProductReview pr
ON
pr.ProductID = p.ProductID
By moving the cursor to the select section, the assistant will show a hint for selecting the fields that will be included in the selection from the tables listed in the from section. The same principle in any section of the request (where, group by, order by).
In addition to information about the type of field, for the fields that make up the primary key, an icon is displayed in the form of a yellow key, a green key - fields of a foreign key, lightning - an index is built on the field.
Other objects from the current database of other server databases are also available in the list. If you select a field from a table that is not in the from section, then it will be automatically added to the from section. It remains only to add the necessary conditions for communication.
In general, the following objects with their components can be displayed in the list:
- local variables
- temporary tables
- persistent tables
- representation;
- procedures;
- functions
- synonyms;
- schemes;
- Database;
- built-in functions.
It all depends on the context of the assistant call. So, if you write the insert Production.Product construct, then when you call the helper, you can select the required fields of the table for which the insert statement is written.
But it’s more convenient to use an assistant at the time of writing the operator itself. After I wrote the insert statement, the assistant prompts me to select a database object.
After I select the necessary table, I will automatically receive the following code:
INSERT INTO Production.Location
(
- LocationID - this column value is auto-generated
Name,
CostRate,
Availability,
ModifiedDate
)
VALUES
(
/ * {Name} * /,
/ * {CostRate} * /,
/ * {Availability} * /,
/ * {ModifiedDate} * /
)
It is similarly obtained with the update operator. Code is automatically generated with a similar input sequence:
UPDATE Production.Location
SET
- LocationID =? - this column value is auto-generated
Name =?,
CostRate =?,
Availability =?,
ModifiedDate =?
You can even declare variables based on table structure. I type declare and select the Location table, as a result, the code is generated:
DECLARE
@LocationID SMALLINT,
@Name name,
@CostRate SMALLMONEY,
@Availability DECIMAL (8, 2),
@ModifiedDate DATETIME
Build a procedure or function call.
Even those objects that are created or declared in the code become available in the assistant window. It takes into account the creation of temporary tables, the declaration of variables, including table ones.
If I meet a variable by code, then holding down the Ctrl key and clicking on this variable, I will move to the place where this variable was declared. But it’s easier to move the cursor to this variable to get a tooltip that will indicate the type of the variable. For the table, its structure and links will be displayed for a script to create, quick view of the contents and more information. System
For the procedure - a list of parameters, etc. for other objects.
For built-in functions, a tooltip with a brief description of the function. And the assistant will help with entering the parameters.
Conclusion
Here are the main features that I use daily while working in Management Studio. These are far from all the features of SQL Assistant. There are many more interesting functions, features, pleasant things. But, I think that building links, a context list for selecting tables and table fields can already ease the work of a programmer by an order of magnitude. By the way, SQL Assistant keeps statistics on how many characters the user entered and how many characters the assistant added, and then the percentage of productivity growth is calculated from this data.
References
Official site of SQL Assistant Program
Documentation (PDF, 7.2Mb)