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

Question

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

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

Keyboard Shortcuts

• F5, CTRL + E or ALT + X - execute currently selected 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
• SHIFT + ALT + Selecting text - select/cut/copy/paste a rectangular block of text

Addons

• Red Gate's SQL Prompt - IntelliSense (suggested by Galwegian)
• SQLinForm - formatting of TSQL (suggested by Galwegian)
• Poor Man's T-SQL Formatter - open-source formatting add-in

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)

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

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.

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.

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!