Improving productivity with SQL snippets

SQL snippets can be a big boost to productivity when writing T-SQL. First, to use a SQL snippet in a script, there is no need to know the syntax, only the purpose of that SQL snippet (e.g. delete an object, create a table, etc.). Using snippets also reduces of the lines of code that have to be typed, and thus decreases the potential for errors that could occur from typing.

Snippets are a feature that has existed in SQL Server Management Studio since version 2012. Here is an article that provides an overview of SSMS snippets.

ApexSQL Complete is add-in for SSMS and VS that helps to speed up the process of coding using suggestions from the hit-list or using features such as: auto-replacement, SQL snippets, auto-insert statements and more. It offers SQL snippets as well.

Creating and using SQL snippets from ApexSQL Complete

ApexSQL Complete’s snippets are placed in the list under the Snippets tab in the Options window. There two ways to initiate the Snippets tab:

1. From ApexSQL main menu, choose ApexSQL Complete menu, click the Options command, and from the Options window select the Snippets tab:

   

2. Using a combination of key Ctrl+Shift+Alt+R from the keyboard

Once in the Snippets tab, a list of a built-in SQL snippets will be shown:

Creating a user defined SQL snippet

There are three ways to create SQL snippets:

1. From the Snippets tab, select the New button:

   

2. Right-click in the query window and from the context menu, choose the New snippet command:

   

3. Using a combination of key shortcuts Ctrl+Shift+Alt+S

Any of these three methods will open the Create snippet window:

If nothing is selected, all code from the query window will be inserted in the code section of the Create snippet window:

Fill out some basic info and click the OK button to create a SQL snippet:

Macros

The snippet managing window contains a list of macro variables like Date and Machine for dynamic code generation:

A macro allows add/replace values to be automatically added to the snippet code that represent more detailed values.

So the Database macro would be placed in the variable %DB%, which, when the snippet is entered during the course of coding, would render to the name of the currently logged into the database:

Here is the code that is created in a snippet managing window with macros:

USE % DB %
	/*
Time Created: %TIME&hh:mm:ss%
Date Created: %DATE&MM/dd/yyyy%
User: %USER%

*/
GO

CREATE TABLE < NAME > ()
GO

After inserting the snippet in the query window, the macros will automatically render appropriate values. The Database macro will insert the name of the connected database. The Date and Time macros will insert current date and time and the User macro will insert the name of the connected user:

USE test
	/*
Time Created: 11:28:53
Date Created: 04/29/2016
User: Mare

*/
GO

CREATE TABLE < NAME > ()
GO

By default, the Date macro inserts the US date format %DATE&MM/dd/yyyy%, but in the Date macro can be set to any SQL Server date format. After %DATE%; type the desired format, for example yyMMdd which would render to:

%DATE&yy-MM-dd%

A list of all available SQL Server date formats can be found on this link.

The Caret and Selection macro variable

The Caret macro will place the cursor at the location of the %CARET% variable, after a SQL snippet is inserted in the query window.

Example:

Start typing the name of the SQL snippet and from the hint-list, choose the BeginTry snippet:

The cursor will be placed in the place of the %CARET% variable:

The Selection macro variable will replace the %SELECTION% variable with the selected code from the query window.

In the below example is a SQL snippet for creating a view:

Select the code in the query window, right click and choose the Insert snippet command:

From the prompted drop-down list, select the CV SQL snippet:

The %SELECTION% variable between Begin/End statement, will be replaced with selected code from the query window and the cursor will be inserted instead the %CARET% variable after View command:

For more information about using macros, please visit How to create and use T-SQL code snippets page.

Once created, user defined SQL snippet will be placed in the list among with built-in SQL snippets under the Snippets tab. The Snippets tab includes a code preview for a selected SQL snippet:

Editing a SQL snippet

To edit and make additional changes in a built-in or user defined SQL snippet, from the list under the Snippets tab, select snippet and press the Edit button, which will open the Edit snippet window:

Inserting SQL snippets in the query window

In order to use SQL snippets, position the cursor in the query window in the desired location, right-click and from the popup menu, choose the Insert snippet option or use combination of key shortcuts Ctrl+Shift+Alt+N:

The hint-list will be shown with all available SQL snippets:

Double-clicking on the desired SQL snippet or select a SQL snippet and press the Enter key, will insert the code of a SQL snippet in the query window:

Another way to insert a SQL snippet is to start typing the name of the SQL snippet in the query window and from the hint-list, choose a SQL snippet by double-clicking on it, or first select a SQL snippet from the list and press the Enter key:

By default, SQL snippets appear in the auto-complete hint-list. To remove them go to the ApexSQL Complete menu, choose the Options command, and under the Hints tab uncheck the Snippets check box:

All snippets are saved as xml file. Here is an example of a SQL snippet:

<?xml version=”1.0″ encoding=”utf-8″?>
<Snippet xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<Name>CT</Name>
<Description>Create a Table</Description>
<Text>
USE %DB%
/*
Time Created:%TIME&hh:mm:ss%
Date Created:%DATE&yy-MM-dd%
User:%USER%
*/
GO
CREATE TABLE <name>(

)
GO
</Text>
</Snippet>

By default, built-in and user defined SQL snippets are placed under the following path:

C:\Users\<current_user>\AppData\Local\ApexSQL\ApexSQLComplete\Snippets

Copying SQL snippets

SQL snippets can be copied from one to another machine where is already installed ApexSQL Complete by using the Export/Import options from the Snippets tab:

When using the Export option, by default, all (built-in and user defined) SQL snippets will be compressed as Snippets.zip file

To import SQL snippets in the Snippets tab, click the Import button, from the Open window, navigate to the Snippets.zip file and click the Open button:

This will import SQL snippets in the list under the Snippets tab. The SQL snippets that are already been in the list will stay intact.

Quick tip: After uninstalling ApexSQL Complete, SQL snippets will remain undeleted in the default location

See also:

• How to create and use T-SQL code snippets
• SQL snippets
• SQL snippets in SQL Server Management Studio