SQL Server Management Studio - Tips to Improve TSQL Coding

Question

I worked in a place where it was common practice to use Pair Programming. I remember how many small things we could learn from each other, working together on code. Selection of new shortcuts, code fragments, etc. Over time, it has significantly improved our coding efficiency.

Since I started working with SQL Server, I was left alone. The best habits that I usually chose, working with other people that I can’t do right now.

So here is the question:

• What can you say about efficient TSQL code writing using SQL Server Management Studio?
• Please keep your tips on 2 - 3 things / shortcuts that you think improve coding speed.
• Please stay in control of TSQL and SQL Server Studio 2005/2008 If this feature is version-specific Management Studio, please indicate: for example "Works with SQL Server 2008 only"

EDIT:

I am afraid that some of you might be misunderstood. I'm not looking for tips for writing effective TSQL code, but rather for tips on how to use Management Studio effectively to speed up the coding process.

Type of answers I'm looking for:

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

Basically, those little things that make coding more efficient and enjoyable.

Answer 1

Community wiki Answer - feel free to edit or add comments:

Keyboard shortcuts

• F5 , CTRL + E or ALT + X - execute the currently selected TSQL code
• CTRL + R - show / hide the results pane
• CTRL + N - Open a new query window
• CTRL + L - Show query execution plan

Editing Shortcuts

• CTRL + K + C and CTRL + K + U - comment / uncomment selected block of code (Unsliced ​​suggested)
• CTRL + SHIFT + U and CTRL + SHIFT + L - changes the selected text to upper / lower case
• SHIFT + ALT + Select text - select / cut / copy / paste a rectangular block of text

Addons

• Red SQL SQL Prompt - IntelliSense (proposed by Galwegen)
• SQLinForm - TSQL formatting (suggested by Galwegian)
• T-SQL Formatter Bad Man - An Open Source Formatting Add-In

Other tips

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

useful links

• SQL Server Management Studio keyboard shortcuts (full list)

Answer 2

Take a look at Red SQL SQL Prompt is a great product (like most Red Gate contributions)

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

In addition, I learned from painful experience that comes before any DELETE with BEGIN TRANSACTION . Once you are sure that your operator deletes only what it should, you can COMMIT .

He kept me on several occasions; -)

Answer 3

+1 for SQL query.

Something real simple that I think I have never seen - this will work with almost any SQL environment (and even in other languages):

After 12 years of SQL coding, I recently began to convert to the prefix style after the decimal point, seeing it in some SSMS code, I found it very effective. I was very surprised that I had not seen this style before, especially since it significantly increased my productivity.

 SELECT ta ,tb ,tc ,td FROM t 

This makes it easy to edit selection lists, parameter lists, organize lists, group by lists, etc. I find that I spend much less time cheating with adding and removing commas from the end of lists after cutting and pasting operations - I think this works easier because you almost always add things at the end and with postfix commas, which requires that You moved the cursor more.

Try it, you will be surprised - I know that I was.

Answer 4

My favorite quick tip is that when expanding the table name in the object explorer, simply dragging the word columns onto the query screen will put a list of all the columns in the table into the query. It’s much easier to just delete the ones you don’t need than to type the ones you need, and it’s so simple that it doesn’t allow people to use the really terrible select * syntax. And it prevents typos. Of course, you can individually drag and drop columns.

Answer 5

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

Answer 6

Try to always use the smallest data type and index all the fields most used in queries.

If possible, try to avoid server-side cursors. Always follow a “set-based approach” instead of a “procedural approach” for accessing and managing data. Cursors can often be avoided by using SELECT statements instead.

Always use the graphical execution plan in the Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your queries perform an “index scan” and not an “index scan” or “table scan”. Crawling a table or crawling an index is a very bad thing and should be avoided whenever possible. Select the correct indexes in the right column. Use more readable ANSI-Standard join conditions rather than old-style joins. When connecting ANSI, the WHERE clause is used only to filter data. Where, as in the case of the old style, the WHERE clause processes both the join condition and the filtering data.

Do not allow your external applications to directly query / manipulate data using SELECT or INSERT / UPDATE / DELETE statements. Instead, create stored procedures and let your applications access these stored procedures. This ensures the purity and consistency of data access in all modules of your application and at the same time centralizes the business logic in the database.

Speaking of stored procedures, do not name the stored procedures "sp_". The sp_ prefix is ​​reserved for the system stored procedure that ships with SQL Server. Whenever SQL Server comes across a procedure name starting with sp_, it first tries to find the procedure in the main database, then it searches for the provided qualifiers (database, owner), then tries dbo as the owner. This way you can save time when searching for a stored procedure by avoiding the "sp_" prefix.

Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL because SQL Server must generate an execution plan every time it runs.

Whenever possible, try using integrated authentication. This means that you forget about sa and other SQL users, use the infrastructure to provide microsoft users, and always keep your SQL server up to date with all the necessary fixes. Microsoft is doing a good job of developing, testing, and releasing patches, but it's your job of applying it.

Find good reviews about it at amazon.com and buy it!

Answer 7

CTRL + I for incremental search . Press F3 or CTRL + I to view the results.

Answer 8

Keyboard accelerators. After you find out what queries you write a lot, write service stored procedures to automate tasks and map them to shortcuts. For example, this article talks about how to avoid typing "select top 10 * from SomeBigTable" every time you just want to take a look at the sample data from this table. I have a very advanced version of this procedure displayed on CTRL + 5 .
Several Yet:

• CTRL + 0 : quick script table data or definition of proc, UDF or view
• CTRL + 9 : find any object whose name contains the given string (if you know that there is a procedure with "Option" in the name, but you do not know where its name begins)
• CTRL + 7 : find any proc, UDF, or view that contains the given string in 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 using existing interfaces in SSMS, but SSMS windows and widgets can slow down a bit, especially when you request a server over the Internet, and I prefer not to pick up the keyboard anyway.

Answer 9

Just a tiny rectangular selection ALT + DRAG comes in handy for copying + pasting vertically aligned column lists (for example, when manually writing a massive UPDATE). Writing TSQL is the only time I use it!

Answer 10

If you drag node for the table from the column of the object browser, it puts a list of CSV columns in the query window for you

Answer 11

Another thing that helps to improve the accuracy of what I'm doing is not a management studio tip, but one that uses t-sql itself.

Whenever I write an update or delete instruction for the first time, I include a selection in it so that I can see which entries 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 that I used select * here just for illustration, I would usually select only a few fields that I need to see that the query is correct. Sometimes I may need to see fields from other tables in join, as well as the entries I plan delete to make sure the connection works the way I thought it would)

When you run this code, you first run the selection to make sure it is correct, then comment out the selection line and uncomment the parts of the removal or update. By doing this in this way, you do not accidentally run the uninstall or update before you check it. Also, you avoid the problem of forgetting to comment on the choice, forcing the update to update all records in the database table that may occur if you use this syntax and uncomment the choice 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 above example, if you uncomment the selection and forget to rebuild it, just refresh the whole table and then run the selection when you decide to start the update. Someone just did it in my office this week, making it so that only one person out of all clients could log into client sites. Therefore, avoid this.

Answer 12

For auxiliary queries

object explorer> right click table table> Script as> SELECT in> Clipboard

Then you can simply paste into the section where you want it to be as an additional request.

Patterns / Fragments

Create your own templates with only a snippet of code. Then, opening the template as a new document, just drag it to the current request to insert a fragment.

A snippet can simply be a collection of captions with comments, or just some simple piece of code.

Implicit Transactions

If you remember to start a transaction before your deletion stats, you can go to the options and set implicit transactions by default in all your queries. They always require explicit commit / rollback.

Isolation level

Go to the settings and set the isolation level to READ_UNCOMMITED by default. This way you do not need to enter NOLOCK in all your special requests. Just remember to place the table tip when writing a new view or stored procedure.

Default database

Your login has a default database set by the database administrator (for me it is usually undesirable almost every time).

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

In the Registered Servers area> Right-click> Properties> the Connection Properties tab> connect to the database.

Multiple logins

(Maybe you already did it)

Register the server several times, each with a different login. Then you can open the same server in the object browser several times (each with a different login).

To execute the same request that you already wrote using another input, instead of copying the request, just right-click on the request area> Connection> Change connection.

Answer 13

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

Answer 14

I like to configure the CTRL + F1 key combination as sp_helptext , as this allows you to select a stored procedure and quickly view its code. I find this a nice addition to the default ALT + F1 sp_help shortcut .

Answer 15

I have a scheduled task: to write every file (table, sproc, etc.) to a file every night. I have full-text search indexing in the output directory, so when I search for a specific line (for example, a constant) that is buried somewhere in the database, I can find it very quickly.

In Management Studio, you can use the Tasks> Generate Scrips ... command to see how to accomplish this.

Answer 16

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

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

Answer 17

Display Query Designer with CTRL + SHIFT + Q

Answer 18

I’m the developer of the SSMSBoost add-on recently released for SSMS2008 / R2, it was supposed to add add functions to speed up everyday tasks:

Shorcuts: F2 - (in SQL Editor): script object that is a lax cursor

CTRL + F2 - (in the SQL editor): find the object located under the cursor in the object explorer and center it + It includes a shortcut editor that is not in SSMS2008 (included in SSMS2012)

SSMSBoost also adds a toolbar with buttons:

• Synchronize SQL Editor connection with the object browser (focuses the current database in the object browser)
• Manage your own preferred connections and switch between them through a combo box (including server transitions).
• AutoCorrect: typing " sel " will replace it with select * from , and you can also add your pair of token replacement pairs
• and some more useful features

Answer 19

Use the TRY / CATCH functions to detect errors.

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

Use the ownership chain for stored procedures.

Use schemas for security and data roles.

Answer 20

F5 to run the current request is an easy win, after that, the general commands of the MS editor CTRL + K + C to comment on the selected text, and then CTRL + K + U to terminate.

Answer 21

Use Object Explorer Details instead of the object explorer to view your tables, so you can click a letter and move it to the first table with the letter prefix.

Answer 22

If you work with developers, you often get a piece of code that is formatted as one long line of code, and then adds an SQL add-in add-in to manage SQL Server. Studio can help a lot with over 60+ formatting options. http://www.dpriver.com/sqlpp/ssmsaddin.html

Answer 23

Using TAB , the selected text will be indented. It's nice to easily organize the code in a readable format. In addition, SHIFT + TAB will be invalid.

Answer 24

Using bookmarks is a great way to keep your sanity if you work or eliminate a very long process. Say you are working with a derived field in an external query, and that is the definition of another 200 lines inside the internal query. You can tag both locations and then quickly go back and forth between them.

Answer 25

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

I also use the SSMSToolsPack addon, and this is very good. I like;

• These are SQL fragments in which you can create short keys for code fragments and automatically add them when you enter these keys and press the enter key.
• Searching the history to get your queries that you launched several months ago and forgot, saved a lot of time.
• Restore the last session. Now I never save my requests if I just need to restart my windows. I just click on restore the last session, and my last session is received and restored, and the connection is created automatically.
• Create insert statements from query results (very useful). Just love this addon.

Recently a small catch has appeared. SSMSToolsPack is no longer free for SSMS 2012. It is still free for SSMS 2005 and SSMS 2008, for now . Use it only if you want to buy it when upgrading to SSMS 2012. Otherwise, it may be a good idea to wean it.

Answer 26

• ALT+SHIFT + Select

This is a wonderful result that I recently discovered - it allows you to select a rectangular section of text regardless of line breaks. Very handy for quickly cutting a subquery or list.

Answer 27

If you need to write a lot of sprocs for some kind of API. You may like these tools that I wrote when I was a programmer. Let's say you have a table of 200 columns for which you need to write sproc to insert / update, and another to delete. Because you do not want your application to access tables directly. Just part of the declaration will be a tedious task, but if part of the code is not written for you. Here is 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 skip to the source code and article written in 2002 (now I feel old!)
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=549&lngWId=5

Answer 28

I highly recommend Red Gate SQL Prompt. Auto discovery (intellisense on tables, stored procedures, functions, and native functions) is nothing short of amazing! :)

It comes with a price. There is no free version of this thing.

Answer 29

Knowledge of two (?) Different window types available in SQL Server Management Studio.

If you right-click on the table and select Open , it will use an editable grid into which you can change the cells. If you right-click on the database and select New Query , it will create a slightly different type of window that you cannot change the grid, but it gives you several other nice features, for example, allows you to separate code fragments and allow you to execute them separately by choice.

Answer 30

Use the SELECT INTO query to quickly / easily create backup tables for work and experimentation.