I am a big fan of snippets in SQL Prompt, often using them in demos to quickly get code written. However, I’ve liked the idea of snippets and templates for a long time. These are great time savers, and they can dramatically improve productivity and code quality.
How? If you have certain constructs in your environment that developers struggle to remember or implement, make a snippet. This makes things very easy and consistent. It’s a great way to help younger developers learn as well.
Here’s an example.
Create Primary Keys
I have worked with no shortage of developers that build tables like this:
CREATE TABLE Shipper
( ShipperKey INT NOT NULL
, ShipperName VARCHAR(100)
, ShipperAddress VARCHAR(100)
, ShipperCity VARCHAR(100)
, ShipperRegion VARCHAR(20)
, CountryCode CHAR(3)
)
GO
This isn’t a great design, but more importantly, deploying this results in a heap. Perhaps another issue is that there are no indexes, which isn’t usually a good idea.
A better idea might be a table like this:
CREATE TABLE dbo.Shipper
( ShipperKey INT NOT NULL CONSTRAINT ShipperPK PRIMARY KEY
, ShipperName VARCHAR(100)
, ShipperAddress VARCHAR(100)
, ShipperCity VARCHAR(100)
, ShipperRegion VARCHAR(20)
, CountryCode CHAR(3)
)
GO
CREATE INDEX Shipper_Region ON dbo.Shipper (ShipperRegion)
Now we can’t template all of this, but we can do a few things. I’ll show you how Prompt facilitates this.
A Customized Snippet
Let’s start with the basic code. I know I need a table name, I’ll want a PK, and I want to help someone add at least one index. With that in mind, I’ll build this snippet code. Note that I’ve replaced the table name with a parameter. I did this with a search and replace in the script.
CREATE TABLE dbo.$TableName$
( $TableName$Key INT NOT NULL CONSTRAINT $TableName$PK PRIMARY KEY
$CURSOR$
)
GO
CREATE INDEX $TableName$_ ON dbo.$TableName$ ()
You can see this in the SQL Prompt Snippet Manager. Note that the parameter (or placeholder) has been inserted in the bottom by Prompt.
One other thing I might do is add a schema placeholder like this:
Note that I’ve added a default for schema, as this is usually dbo. I’ll also click the up arrow to the right to ensure schema is entered first.
Now, let’s use this. I’ll save this and close the options. Then in a new query window, I’ll type my snippet beginning as “crt”. I see this:
My snippet is listed. I can select it and I’ll then see this code. See how Prompt has inserted my snippet, but already highlighted the schemaname parameter and given me the intellisense of the schemas in my database.
I’ll type dbo and Enter. Prompt moves to the next placeholder parameter. Here I see TableName highlighted all over.
If I type “Shipper”, I see this.
Now I’ll hit Enter again. This time Prompt puts the cursor where I need it to start entering other columns.
I can easily enter my columns, and now my table has a PK. What’s more, if I enter a few columns and run this, I’ll see an error.
The table was created, but the index statement isn’t correct. While this doesn’t necessarily ensure developers follow naming standards or create an index, at least this will get them to think about it. They can correct the statement by adding a column to the index statement between parenthesis, and hopefully change the name.
If you haven’t seen how Prompt can really improve your coding, download an eval today and give it a try. If you have it, take advantage of snippets.
Voice of the DBA 