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

User · Answer

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

User · Answer

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

User · Answer

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    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  Search through history to retrieve your queries which you ran months ago and forgot  saved a lot of my time  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  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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt  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 amp lngWId 5

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Display the Query Designer with CTRL   SHIFT   Q

User · Answer

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  -

User · Answer

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

User · Answer

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

User · Answer

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  gt 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  gt 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    CTRL   0  Quickly script a table s data  or a proc  UDF  or view s definition 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  CTRL   7  find any proc  UDF  or view that includes a given string in its code 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql-server

Examples related to tsql

Examples related to optimization

Examples related to keyboard-shortcuts