[sql-server] SQL Server Management Studio – tips for improving the TSQL coding process

I used to work in a place where a common practice was to use Pair Programming. I remember how many small things we could learn from each other when working together on the code. Picking up new shortcuts, code snippets etc. with time significantly improved our efficiency of writing code.

Since I started working with SQL Server I have been left on my own. The best habits I would normally pick from working together with other people which I cannot do now.

So here is the question:

• What are you tips on efficiently writing TSQL code using SQL Server Management Studio?
• Please keep the tips to 2 – 3 things/shortcuts that you think improve you speed of coding
• Please stay within the scope of TSQL and SQL Server Management Studio 2005/2008 If the feature is specific to the version of Management Studio please indicate: e.g. “Works with SQL Server 2008 only"

EDIT:

I am afraid that I could have been misunderstood by some of you. I am not looking for tips for writing efficient TSQL code but rather for advice on how to efficiently use Management Studio to speed up the coding process itself.

The type of answers that I am looking for are:

• use of templates,
• keyboard-shortcuts,
• use of IntelliSense plugins etc.

Basically those little things that make the coding experience a bit more efficient and pleasant.

CTRL + I for incremental search. Hit F3 or CTRL + I to cycle through the results.

If you need to write a lot of sprocs for an API of some sort. You may like this tools I wrote when I was a programmer. Say you have a 200 columns table that need to have a sproc written to insert/update and another one to delete. Because you don't want your application to directly access the tables. Just the declaration part will be a tedious task but not if a part of the code is written for you. Here's an example...

CREATE PROC upsert_Table1(@col1 int, @col2 varchar(200), @col3 float, etc.)
AS
BEGIN
  UPDATE table1 SET col1 = @col1, col2 = @col2, col3 = @col3, etc.
  IF @@error <> 0
    INSERT Table1 (col1, col2, col3, etc.)
    VALUES(@col1, @col2, @col3, etc.)
END
GO
CREATE PROC delete_Table1(@col1)
AS DELETE FROM Table1 WHERE col1 = @col1

http://snipplr.com/view/13451/spcoldefinition-or-writing-upsert-sp-in-a-snap/

Note : You can also get to the original code and article written in 2002 (I feel old now!)
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=549&lngWId=5

Keyboard accelerators. Once you figure out what sorts of queries you write a lot, write utility stored procedures to automate the tasks, and map them to keyboard shortcuts. For example, this article talks about how to avoid typing "select top 10 * from SomeBigTable" every time you want to just get a quick look at sample data from that table. I've got a vastly expanded version of this procedure, mapped to CTRL + 5.
A few more I've got:

1. CTRL + 0: Quickly script a table's data, or a proc, UDF, or view's definition
2. CTRL + 9: find any object whose name contains a given string (for when you know you there's a procedure with "Option" in the name, but you don't know what its name starts with)
3. CTRL + 7: find any proc, UDF, or view that includes a given string in its code
4. CTRL + 4: find all tables that have a column with the given name

... and a few more that don't come to mind right now. Some of these things can be done through existing interfaces in SSMS, but SSMS's windows and widgets can be a bit slow loading up, especially when you're querying against a server across the internet, and I prefer not having to pick my hands up off the keyboard anyway.

Make use of the TRY/CATCH functionality for error catching.

Adam Machanic's Expert SQL Server 2005 Programming is a great resource for solid techniques and practices.

Use ownership chaining for stored procs.

Make use of schemas to enforce data security and roles.

If you drag from Object Explorer Columns node for a table it puts a CSV list of columns in the Query Window for you

Devart' SQL Complete express edition is an SSMS addon and is a free and useful addon. It provides much needed code formatting and intellisense features.

I also use SSMSToolsPack addon and it is very good. I Love;

1. It's SQL snippets where you can create short keys for code snippets and it appends them automatically when you type these keys and press enter.
2. Search through history to retrieve your queries which you ran months ago and forgot, saved a lot of my time.
3. Restore last session. Now I never save my queries if I have to just restart my windows. I just click on restore last session and my last session gets and restored and connection created automatically.
4. Create insert statements from query results (very useful). Just love this addon.

A small catch recently introduced. SSMSToolsPack is not free anymore for SSMS 2012. It's still free for SSMS 2005 and SSMS 2008, till yet. Use it only if you want to buy it when you migrate to SSMS 2012. Otherwise may be it's a good idea to wean away from it.

For Sub Queries

object explorer > right-click a table > Script table as > SELECT to > Clipboard

Then you can just paste in the section where you want that as a sub query.

Templates / Snippets

Create you own templates with only a code snippet. Then instead opening the template as a new document just drag it to you current query to insert the snippet.

A snippet can simply be a set of header with comments or just some simple piece of code.

Implicit transactions

If you wont remember to start a transaction before your delete statemens you can go to options and set implicit transactions by default in all your queries. They require always an explicit commit / rollback.

Isolation level

Go to options and set isolation level to READ_UNCOMMITED by default. This way you dont need to type a NOLOCK in all your ad hoc queries. Just dont forget to place the table hint when writing a new view or stored procedure.

Default database

Your login has a default database set by the DBA (To me is usually the undesired one almost every time).

If you want it to be a different one because of the project you are currently working on.

In 'Registered Servers pane' > Right click > Properties > Connection properties tab > connect to database.

Multiple logins

(These you might already have done though)

Register the server multiple times, each with a different login. You can then have the same server in the object browser open multiple times (each with a different login).

To execute the same query you already wrote with a different login, instead of copying the query just do a right click over the query pane > Connection > Change connection.

I suggest that you create standards for your SQL scripting and stick to them. Also use templates to quickly create different types of stored procedures and functions. Here is a question about templates in SQL Server 2005 Management Studio

How do you create SQL Server 2005 stored procedure templates in SQL Server 2005 Management Studio?

F5 to run the current query is an easy win, after that, the generic MS editor commands of CTRL + K + C to comment out the selected text and then CTRL + K + U to uncomment.

Using TAB on highlighted text will indent it. Nice for easily arranging your code in a readable format. Also, SHIFT + TAB will unindent.

Just a tiny one - rectangular selections ALT + DRAG come in really handy for copying + pasting vertically aligned column lists (e.g. when manually writing a massive UPDATE). Writing TSQL is about the only time I ever use it!

Display the Query Designer with CTRL + SHIFT + Q

I am developer of SSMSBoost add-in that was recently released for SSMS2008/R2, the intention was to add add features that speed up daily routine tasks:

Shorcuts: F2 - (in SQL Editor): script object located unted cursor

CTRL + F2 - (in SQL Editor): find object located under cursor in object explorer and focus it +It includes Shortcut editor, that is missing in SSMS2008 (is coming in SSMS2012)

also SSMSBoost adds toolbar with buttons:

• Syncronize SQL Editor connection to Object Explorer (focuses current database in Object Explorer)
• Manage your own preferred connections and switch between them through combo-box (including jumps between servers)
• Auto-replacements: typing "sel" will replace it by select * from and you can also add your own token-replacement pairs
• and some more useful features

My favorite quick tip is that when you expand a table name in the object explorer, just dragging the word colums to the query screen will put a list of all the columns in the table into the query. Much easier to just delete the ones you don't want than to type the ones you do want and it is so easy, it prevents people from using the truly awful select * syntax. And it prevents typos. Of course you can individually drag columns as well.

Use Object Explorer Details instead of object explorer for viewing your tables, this way you can press a letter and have it go to the first table with that letter prefix.

I like to setup the keyboard shortcut of CTRL + F1 as sp_helptext, as this allows you to highlight a stored procedure and quickly look at it's code. I find it is a nice complement to the default ALT + F1 sp_help shortcut.

If you work with developers, often get a sliver of code that is formatted as one long line of code, then sql pretty printer add-on for SQL Server management Studio may helps a lot with more than 60+ formatter options. http://www.dpriver.com/sqlpp/ssmsaddin.html

Use a SELECT INTO query to quickly/easily make backup tables to work and experiment with.

Try to use always the smallest datatype that you can and index all the fields most used in queries.

Try to avoid server side cursors as much as possible. Always stick to a 'set-based approach' instead of a 'procedural approach' for accessing and manipulating data. Cursors can often be avoided by using SELECT statements instead.

Always use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your queries do an "Index seek" instead of an "Index scan" or a "Table scan." A table scan or an index scan is a very bad thing and should be avoided where possible. Choose the right indexes on the right columns. Use the more readable ANSI-Standard Join clauses instead of the old style joins. With ANSI joins, the WHERE clause is used only for filtering data. Where as with older style joins, the WHERE clause handles both the join condition and filtering data.

Do not let your front-end applications query/manipulate the data directly using SELECT or INSERT/UPDATE/DELETE statements. Instead, create stored procedures, and let your applications access these stored procedures. This keeps the data access clean and consistent across all the modules of your application, and at the same time centralizing the business logic within the database.

Speaking about Stored procedures, do not prefix your stored procedure names with "sp_". The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries dbo as the owner. So you can really save time in locating the stored procedure by avoiding the "sp_" prefix.

Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime.

When is possible, try to use integrated authentication. It means, forget about the sa and others SQL users, use the microsoft user provisioning infra-structure and keep always your SQL server, up-to-date with all required patches. Microsoft do a good job developing, testing and releasing patches but it's your job to apply it.

Search at amazon.com books with good reviews about it and buy it!

+1 for SQL Prompt.

Something real simple that I guess I had never seen - which will work with just about ANY SQL environment (and other languages even):

After 12 years of SQL coding, I've recently become a convert to the comma-prefix style after seeing it in some SSMS generated code, I have found it very efficient. I was very surprised that I had never seen this style before, especially since it has boosted my productivity immensely.

SELECT
t.a
,t.b
,t.c
,t.d
FROM t

It makes it really easy to edit select lists, parameter lists, order by lists, group by lists, etc. I'm finding that I'm spending a lot less time fooling with adding and removing commas from the end of lists after cut-and-paste operations - I guess it works out easier because you almost always add things at the end, and with postfix commas, that requires you to move the cursor more.

Try it, you'll be surprised - I know I was.

Another thing that helps improve the accuracy of what I do isn't really a management studio tip but one using t-sql itself.

Whenever I write an update or delete statement for the first time, I incorporate a select into it so that I can see what records will be affected.

Examples:

select t1.field1,t2.field2
--update t
--set field1 = t2.field2 
from  mytable t1
join myothertable t2 on t1.idfield =t2.idfield
where t2.field1 >10

select t1.* 
--delete t1
from mytable t1
join myothertable t2 on t1.idfield =t2.idfield
where t2.field1 = 'test'

(note I used select * here just for illustration, I would normally only select the few fields I need to see that the query is correct. Sometimes I might need to see fields from the other tables inthe join as well as the records I plan to delete to make sure the join worked the way I thought it would)

When you run this code, you run the select first to ensure it is correct, then comment the select line(s) out and uncomment the delete or update parts. By doing it this way, you don't accidentally run the delete or update before you have checked it. Also you avoid the problem of forgetting to comment out the select causing the update to update all records in the database table that can occur if you use this syntax and uncomment the select to run it:

select t1.field1,t2.field2
update t
set field1 = t2.field2 
--select t1.field1,t2.field2
from  mytable t1
join myothertable t2 on t1.idfield =t2.idfield
where t2.field1 >10

As you can see from the example above, if you uncomment the select and forget to re-comment it out, oops you just updated the whole table and then ran a select when you thought to just run the update. Someone just did that in my office this week making it so only one person of all out clients could log into the client websites. So avoid doing this.

Take a look at Red Gate's SQL Prompt - it's a great product (as are most of Red Gate's contributions)

SQL Inform is also a great free (online) tool for formatting long procedures that can sometimes get out of hand.

Apart from that, I've learned from painful experience it's a good thing to precede any DELETE statement with a BEGIN TRANSACTION. Once you're sure your statement is deleting only what it should, you can then COMMIT.

Saved me on a number of occasions ;-)

I warmly recommend Red Gate's SQL Prompt. Auto-discovery (intellisense on tables, stored procedures, functions and native functions) is nothing short of awesome! :)

It comes with a price though. There is no free-ware version of the thing.

• ALT+SHIFT + Selection

This is a great one I discovered recently - it lets you select a rectangular section of text regardless of line breaks. Very handy for clipping out a subquery or list quickly.

Being aware of the two(?) different types of windows available in SQL Server Management Studio.

If you right-click a table and select Open it will use an editable grid that you can modify the cells in. If you right-click the database and select New Query it will create a slightly different type of window that you can't modify the grid in but it gives you a few other nice features, such as allowing different code snippets and letting you execute them separately by selection.

Highlighting an entity in a query and pressing ALT + F1 will run sp_help for it, giving you a breakdown of any columns, indexes, parameters etc.

Using bookmarks is great way to keep your sanity if you're working with or troubleshooting a really long procedure. Let's say you're working with a derived field in an outer query and it's definition is another 200 lines down inside the inner query. You can bookmark both locations and then quickly go back and forth between the two.

I have a scheduled task that each night writes each object (table, sproc, etc.) to a file. I have full-text search indexing set on the output directory, so when I'm looking for a certain string (e.g., a constant) that is buried somewhere in the DB I can very quickly find it.

Within Management Studio you can use the Tasks > Generate Scrips... command to see how to perform this.

Use the Filter button in the Object Explorer to quickly find a particular object (table, stored procedure, etc.) from partial text in the name or find objects that belong to a particular schema.