dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Posted June 30, 2021

Become More Efficient at Writing TSQL by Creating Code Snippets

By Gregory A. Larsen

A TSQL code snippet is a set of code that performs a task that can easily be placed in your TSQL code using SQL Server Management Studio (SSMS). Snippets are a great way to jump start creating a fragment of TSQL code for common types tasks, like creating a table, a stored procedure, a trigger, an index, and more. In this tip I will show you how to create and register a new snippet with SSMS.

To create a new snippet, you first have to create a file that contains your snippet code. Code snippets are defined using XML, and are saved with a file extension of “snippet”. SQL Server comes with some pre-canned snippet of XML code that is stored in the following root folder structure on my PC in the following location: C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SQL\Snippets\1033\. Depending on your installation of SSMS it might be in the same or a slightly different location. Underneath this folder there are series of other folders, where each folder represents a different type of SQL Server object. For example the “Create Table” snippet is stored in the C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SQL\Snippets\1033\Table file folder.

To show you how to create a new snippet, I will be creating a new snippet named “Create Table In-Memory”. My new snippet can be used to quickly insert a CREATE TABLE statement into a TSQL script, that has the syntax necessary to create a table that resides in memory. Let’s get started in creating my new snippet.

The first step to creating this new template will be to create my snippet file, which will be called “Create Table In-Memory”. I can store my snippet anywhere, even in the root folder identified above. My snippet code looks like this:

<?xml version="1.0" encoding="utf-8" ?>

<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">

<_locDefinition xmlns="urn:locstudio">

    <_locDefault _loc="locNone" />

    <_locTag _loc="locData">Title</_locTag>

    <_locTag _loc="locData">Description</_locTag>

    <_locTag _loc="locData">Author</_locTag>

    <_locTag _loc="locData">ToolTip</_locTag>

</_locDefinition>

<CodeSnippet Format="1.0.0">

<Header>

<Title>Create Table In Memory</Title>

<Shortcut></Shortcut>

<Description>Creates a table In Memory.</Description>

<Author>Gregory A. Larsen</Author>

<SnippetTypes>

<SnippetType>Expansion</SnippetType>

</SnippetTypes>

</Header>

<Snippet>

<Declarations>

<Literal>

<ID>SchemaName</ID>

<ToolTip>Name of the schema</ToolTip>

<Default>dbo</Default>

</Literal>

<Literal>

<ID>Tablename</ID>

<ToolTip>Name of the table</ToolTip>

<Default>Sample_Table</Default>

</Literal>

<Literal>

<ID>column1</ID>

<ToolTip>Name of the column</ToolTip>

<Default>column_1</Default>

</Literal>

<Literal>

<ID>datatype1</ID>

<ToolTip>Data type of the column for Primary Key</ToolTip>

<Default>int NOT NULL PRIMARY KEY NONCLUSTERED</Default>

</Literal>

<Literal>

<ID>column2</ID>

<ToolTip>Name of the column</ToolTip>

<Default>column_2</Default>

</Literal>

<Literal>

<ID>datatype2</ID>

<ToolTip>Data type of the column</ToolTip>

<Default>int NULL</Default>

</Literal>

</Declarations>

<Code Language="SQL">

<![CDATA[CREATE TABLE $SchemaName$.$Tablename$

(

    $column1$ $datatype1$,

    $column2$ $datatype2$

)

WITH

   (MEMORY_OPTIMIZED = ON,

    DURABILITY = SCHEMA_AND_DATA);

$end$]]>

</Code>

</Snippet>

</CodeSnippet>

</CodeSnippets>

 

To add my new snippet, I use the “Code Snippets Manager” which can be started using the Tools option as shown below, or by using the Ctrl+K, Ctrl+B shortcut.

 

Writing code snippets starts with the code snippets manager window.

After launching the Code Snippet Manager tool, the following screen is displayed: 

Code Snippets manager window.

 
To add my new “Create Table In-Memory” schema I will click on the “Import” button.  Upon doing that SSMS allows me to browse to the folder that contains my new snippet and there I select my “Create Table In-Memory” file.   After doing that the following “Insert Code Snippet” window will be displayed:  

The final window needed for SQL snippets.

The final step to create my new “Create Table In-Memory” snippet is to click on the “Finish” button. It’s that easy. 

Once I have finished importing my new snippet, I can start using the Code Snippet Manager to insert my new create table in-memory snippet into a query window.  Having a snippet for those fragments of code you write frequently can improve the speed and accuracy in of writing TSQL code.  As you are building your TSQL code you might want to consider creating snippets for those code fragments you write over and over again.

 
 





Latest Forum Threads
DB2 Forum
Topic By Replies Updated
tables get lock during backup in db2 v9.5 Hamsoo 0 May 8th, 01:03 AM
Query to Pull Last 7 days records from a table vgoushik 0 March 20th, 06:05 PM
DB2 Visual studio addin: ANSI/Unicode problem 10Pints 0 October 5th, 02:08 AM
Things that are everbodys labor day ideas? Lipsett197 0 September 1st, 09:42 AM