SQL Prompt Prevents Stupidity

I upgraded my SQL Prompt recently, mostly as a habit. The team led by David and Aaron are always adding cool new features, and have almost never broken my flow, so I usually take the changes they’ve made whenever they appear. In this case, I got an unexpected surprise.

While demoing some tSQLt stuff, I wanted to show how to install the framework. I loaded the tsqlt.class.sql file and clicked Execute. What I saw was this:

2016-06-01 19_09_49-06_testingtsql_install_tSQLt.class.sql - JOLLYGREENGIANT_SQL2014.SimpleTalkDev_S

In the middle of my SSMS window was a warning. I’ve got multiple items without WHERE clauses in the script. There are deletes, and in this case they don’t matter. However I got a warning. I could stop execution or execute.

This made me pause in front of the audience for 10 seconds while I read it, but I clicked “Execute anyway”, things worked, and I went on.

However, that was cool.

I tried this in other ways. Suppose I had an UPDATE without a WHERE.

2016-06-01 19_11_06-SQLQuery2.sql - JOLLYGREENGIANT_SQL2014.TestingTSQL (JOLLYGREENGIANT_sjones (63)

Same warning. That’s a good one. What if I highlighted just part of a script?

2016-06-01 19_13_21-SQLQuery2.sql - JOLLYGREENGIANT_SQL2014.TestingTSQL (JOLLYGREENGIANT_sjones (63)

I can’t tell you how many times I’ve done this in a presentation. Or in production, where it’s happened a few times. I could turn off the warning, but I love it. This is exactly what I need to prevent me from doing something stupid that I didn’t mean to do.

If I want to clear a table, and sometimes I do in demos, I click “Execute”. However, if I’ve made a mistake, I just hit enter, take the default, and then fix things.

I love SQL Prompt, and it’s one of my favorite tools from Redgate. I also love the development process, with the team working against submissions and requests from users, and responding with small releases hundreds of times a year.

If you’ve got SQL Prompt, be sure you update to v7.2. If not, then download it and give it a try. I think if you spend a good two weeks working with it, customizing snippets, and practicing some of they keystrokes, you’ll love it as well.

Custom Placeholders in SQL Prompt 7

I love my snippets in SQL Prompt. Adding some snippets can make work go so much quicker.  I add new ones all the time, based on the tasks I’m doing and I find that code can almost write itself.

SQL Prompt 7 was just released, and it added a neat feature to the suggestions that I really appreciated. You can now add your own placeholders for code.  How does this work? Let me show you.

Let’s imagine that I want to quickly view a table and update a column. I build a snippet like this:

2015-09-09 15_01_15-SQL Prompt - Edit Snippet

Notice that I’ve added “tblnm” as a placeholder inside of two dollar signs. This is my custom value. It’s not a parameter, but rather a placeholder.

I can set a default value if I’d like.

2015-09-09 15_01_10-SQL Prompt - Edit Snippet

Now when I start typing, I see my snippet appear.

2015-09-09 15_01_24-ObjectDefinitionBox

I hit tab and then I get my snippet. The cursor is where I specified with the $CURSOR$ placeholder that was built in. However my custom placeholder has a list of the objects available that fit here.

2015-09-09 15_01_32-SQLQuery1.sql - aristotle.sandbox (ARISTOTLE_Steve (67))_ - Microsoft SQL Server

If I select one, I get my code. Note that the default value was inserted above.

2015-09-09 15_01_45-SQLQuery1.sql - aristotle.sandbox (ARISTOTLE_Steve (67))_ - Microsoft SQL Server

Very cool.

Now I can adjust my snippets with my own placeholder that makes sense to me, and have intellisense pop up right away.

Another keystroke or two saved.

SQL Prompt – Killing the Detail Box

I love SQL Prompt and use it constantly. When I don’t have it, my code writing process slows to spurts and stutters, and it’s painful.

However one of the defaults for SQL Prompt is to pop up details about objects when I’m typing, as shown below:

prompt2

I usually want the object name to appear, but I hate seeing the definitions. That’s rarely useful to me.

I can turn that off with a quick setting change. If I choose SQL Prompt from the menu and select Options, I’ll get a dialog with a number of sections on the left. The one that pops up first should be "Behavior" and there’s a checkbox in this for "Display Object definitions (see the image below).

prompt3

If I uncheck this, then I still get the list of objects, but no definition. The view in this image is a much easier way for me to write T-SQL code.

prompt4

You can see this noted in the SQL Prompt docs.

A New Use Case for SQL Prompt – Shrinking Code

I thought this would work, but I wasn’t sure. I saw some code the other day like this:

DECLARE @char AS CHAR(1); SET @char = NULL; SELECT ISNULL(@char, 0); SELECT COALESCE(@char, 0); SET @char = 'E'; SELECT ISNULL(@char, 0); SELECT COALESCE(@char, 0);

I dropped it into SSMS, and of course, I couldn’t read much of it. I did a quick CTRL+K, CTRL+Y and it looked like this:

DECLARE @char AS CHAR(1); SET @char = NULL; SELECT ISNULL(@char, 0); SELECT COALESCE(@char, 0); SET @char = 'E'; SELECT ISNULL(@char, 0); SELECT COALESCE(@char, 0);

Yeah, SQL Prompt. In addition to reformatting as I prefer, it removed the extra whitespace.

Execute the Current Statement–SQL Prompt

This is a great little feature in SQL Prompt that I wrote about at SQLServerCentral. It’s Current statement Execution, which comes about from pressing Shift+F5. Whatever statement the cursor is on, whether it’s a single line or multi-line, the statement will execute.

It can be on the first line:

currentstatemnt_e

the last line

currentstatemnt_f

the end of the line

currentstatemnt_g

It doesn’t matter. When I press Shift+F5 in all of these cases, I get this:

currentstatemnt_c

Only the second statement executed (the SELECT), the first statement is ignored (and all other statements) and the code executed is highlighted.

As mentioned at SQLServerCentral, there’s a caveat. This isn’t really highlighting, it’s just coloring. Read the article to understand the difference.

I also have a video version of my tip.

Quick Tips–SQL Prompt Custom Aliases

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

Custom Aliases

SQL Prompt can automatically create aliases for tables. However, as I’ve worked on different systems, I’ve often found that development teams like to use specific aliases consistently to ensure that everyone can easily read the code and understand which tables are being queried.

Suppose I decide that I have these tables:

  • Product
  • Product Details
  • Orders
  • OrderDetails

I often use these tables in queries in my system, and I want to have consistent aliases. Right now, I could have these two queries with the default SQL Prompt alias settings:

aliases20

Note that the Orders table has “O” as an alias in the first query, but “O2” as an alias in the second query. This isn’t an issue when I’m writing a query, but when I revisit this code in a month or two and add an enhancement, it can be tricky.

What I’d like to do is ensure I had consistent aliases for my tables, so that every developer always knows that “o” is Orders and “od” is OrderDetails. I want these aliases

  • Product – P
  • Product Details – PD
  • Orders – O
  • OrderDetails – OD

I can do that in SQL Prompt with custom aliases. Let me go back to the Options dialog and select Aliases.

aliases_21

I’ve already added an item in the Custom aliases section for the product table. However I can click New (highlighted above) and I’ll get a little dialog.

aliases22

I repeat this process for each table, and soon I have all four entered.

aliases_23

Now I can rewrite my query. I start typing each table, and once it’s highlighted, click “tab”. When I get done, I have:

aliases_24

A quick way to ensure that all of your tables are consistently aliased, no matter in which order you type things.

aliases_25

Quick Tips–SQL Prompt Aliases for Every Table

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

Aliases

Aliases are used to make code more readable, and shorten the amount of code that one needs to write. Typically we use these to give a short name to a table. Instead of:

aliases_12

We could use an alias. Note the “p” after the table below and the change in the column list.

aliases_13

Automated Aliases

SQL Prompt can automate aliases for me. Under the Options dialog, there is an Aliases selection (shown below).

aliases_1

Note that I’ve checked the “Assign Aliases” box. This is not checked by default, but once I check it, I get aliases. Let me write a query.

aliases_3

I’m about to select my Orders table from the Prompt drop down. Once I click Tab, I’ll get this:

aliases_34png

SQL Prompt has added the alias for me. It’s a lower case “o”. If I add another table:

aliases_5

I hit tab at this point and I get:

aliases_6

I have a new alias of “o2”. Not terribly creative, but it works.

I have some options for changing these around. Suppose I want to make these upper case to stand out. I can change this in options:

aliases_2

Now I add a third table:

aliases_7

I hit tab:

aliases_8

My new alias is an upper case “P” for the Product table. That gives me a bit of differentiation for my tables.

I, however, do not like the “AS” keyword. I typically just space my alias after the table. I can change that in options:

aliases_9

I’ve unchecked the box and now I add a new table.

aliases_10

When I hit Tab, I’ll get a new alias, upper case, but no AS.

aliases_11

These are not terribly intuitive aliases, but this does at least clean up your code a bit, so when you see all the column names they aren’t spread way to the right with table names like “ProductDescriptions”.

aliases_14

Quick Tips–SQL Prompt Qualifying Columns

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

Qualifying Columns

One of the things that’s a good programming practice for T-SQL is to qualify your columns. Imagine that I have this query:

qualify_a

Note that my column names are listed with just the column name and don’t include the table from which they come. Not a big deal here, but as I enhance this code over time, I may add another table to a join, perhaps one that includes BusinessEntityID in it. In that case, I’ll get an ambiguous column error, and a squiggly in SSMS (shown below).

qualify_e

SQL Prompt tries to make writing code quicker and easier, and if I look back to my first query, Prompt can qualify those columns for me.

If I press CTRL+B, CTRL+Q, I’ll get this (from the first query).

qualify_b

Note that every column now includes the table names.

It also works for aliases. If I have this (note I’ve added an alias)

qualify_c

CTRL+B, CTRL+Q gives me this:

qualify_d

As I add tables and modify this code, anytime I find columns unqualified, I can use this quick shortcut to fix my code.

Note: If you have ambiguous columns, Prompt can’t fix them (yet).

Quick Tips – SQL Prompt Stop the Yelling

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

Lower Case Keywords

One of the things that many developers like is lower case keywords. If you examine some C# code, you’ll often find that they have keywords in lower case. For example, here’s a sample from MSDN.

// versioning.cs
// CS0114 expected
public class MyBase 
{
   public virtual string Meth1() 
   {
      return "MyBase-Meth1";
   }
   public virtual string Meth2() 
   {
      return "MyBase-Meth2";
   }
   public virtual string Meth3() 
   {
      return "MyBase-Meth3";
   }
}

When I’ve gotten projects from Red Gate, I see something similar.

However, if I enter some code in Management Studio, by default, SQL Prompt will format it like this:

prompt_aa

Notice the upper case keywords. Personally I like these, but many developers may not. Fortunately there’s an easy fix. Access the SQL Prompt menu in Management Studio and select options (circled below).

prompt_ab

This will bring up the options dialog. Select the CASE item as shown below.

prompt_ac

On the upper right side, you’ll notice that you have drop downs for keywords, built-in functions and data types. By default these are all set to uppercase. However you have a number of choices.

prompt_ad

I’ll change mine to lowercase, click OK, and then reformat my code. I now see this.

prompt_ae

Quick and easy, and I can now read code that looks more like what developers are used to. Maybe I’ll leave things set like this…

Quick Tips–SQL Prompt Formatting

I love SQL Prompt, and think it’s a great productivity tool. Even before I worked at Red Gate, I love the tool and had a copy before Red Gate bought the technology from the original developer. Recently I’ve run into a few people that weren’t aware of some of the ways in which it can help you. This is a quick look at one of the ways I use SQL Prompt.

Ugly Code

It’s often that I’ll get code submitted at SQLServerCentral, or it will come in a demo from Red Gate, and it will look something like this:

prompt_af

This is actually code from Microsoft in the AdventureWorks 2008 sample database. It’s ugly and hard to read. In fact, I struggle trying to interpret code when I see it like this.

Reformatting

One of the things I probably do most often, after SSF, is reformat code so that I can read it. I have a few settings I prefer, but to apply them, I’ll click CTRL+K, then CTRL+Y and my code will get fixed.

prompt_ag

If I have invalid SQL, Prompt will throw an error and there will be a squiggle line under the offending sections. If I only want to reformat a portion of code, I can highlight it before hitting the shortcut.