SQL Server Management Studio – tips for improving the TSQL coding process

Question

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"

Thanks

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.

Thanks again

Answer 1

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 ;-)

Answer 2

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?

Answer 3

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!

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

community owned wiki Answer - feel free to edit or add comments:

Keyboard Shortcuts

• F5 or Ctrl + E or Alt + x - execute TSQL code
• Ctrl + R – show/hide Results Pane
• Ctrl + N – Open New Query Window
• Ctrl + L – Display query execution plan

Editing Shortcuts

• Ctrl + K,C and Ctrl + K,U - comment/uncomment selected block of code (suggested by Unsliced)
• Ctrl + Shift + U and Ctrl + Shift + L - changes selected text to UPPER/lower case

Addons

• Red Gate's SQL Prompt - IntelliSense (suggested by Galwegian)
• SQLinForm - formatting of TSQL (suggested by Galwegian)

Other Tips

• Using comma prefix style (suggested by Cade Roux)
• Using keyboard accelerators (suggested by kcrumley)

Useful Links

• SQL Server Management Studio Keyboard Shortcuts (full list)

Answer 8

+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.

Answer 9

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.

Answer 10

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.

Answer 11

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.

Answer 12

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.

Answer 13

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.

Answer 14

This is a great add-on for SSMS as well

http://www.ssmstoolspack.com/Main.aspx

It has KEYWORD UPPERCASE, standard template, query history, etc... Generate CRUD SQL code, INSERT statements

It helps at times, and has some functions that Red Gate SQL Prompt doesn't have

Answer 15

Awesome tips!

My little one:
Display the Query Designer with CTRL+SHIFT+Q

Answer 16

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.

Answer 17

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!

Answer 18

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.

Answer 19

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

Answer 20

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.

Answer 21

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

Answer 22

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.

Answer 23

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

Answer 24

CTRL + i => for incremental search.