SQL Feather and Quill

This weeks tip:

Again….What’s the fastest land animal?  The Cheetah.

What’s the fastest way to test a query?  Don’t use your mouse and learn lots of keyboard shortcuts!

Some of my favorites in the query editor of SQL Server Management Studio are:

• All the text selection short cuts – standard in most Windows applications
• F5 – Execute the highlighted query
• CTRL + T – Results to Text
• CTRL + D – Results to Grid
• CTRL + R – Show or hide the results pane
• CTRL + Shift + R – Refresh the Intellisense cache
• CTRL+K then CTRL+C – Comment out the highlighted text
• CTRL+K then CTRL+U – Uncomment the highlighted text

Pick one or two of these for a day and work on them.  In no time you will be writing, testing and understanding more code than ever before – and if your mouse runs out of batteries – it’s just not a problem.

Another good way to embed these in your mind is to hang a cheat sheet in your office for a quick and easy reference.

Here are some related posts from the intraweb:

• From MSDN:  SQL Server Management Studio Keyboard Shortcuts
• From Pinal Dave:  SQL Server Management Studio Keyboard Shortcuts (SSMS Shortcuts) Download

Previous Tips O’ the Week

• TSQL Tip O’ the Week – 6/28/11 – SP_HELPTEXT
• TSQL Tip O’ the Week – 6/21/11 – Copy a Table Structure
• TSQL Tip O’ the Week – 6/14/11 – The Empty GUID
• TSQL Tip O’ the Week – 6/6/11 – Declare and Assign Variables

This weeks tip:

Often I’ll need to copy a tables structure, i.e. all the columns and data types, to a new table.  Some examples of times when this is needed are when you are migrating data, staging data, etc.  A quick and easy way to do this is to select an empty record set into a new table.  For Example:

Select * into Table2 from Table1 where 1 = 2

Obviously one never equals two, so there will be no rows affected, but this will create Table2 with the same columns as Table1.

Note this method will include:

• All columns
• All data types and sizes
• Identity columns
• Nullability of all columns

Note this method will NOT include:

• Indexes
• Foreign keys
• Constraints
• Defaults
• Triggers

Here’s what it looks like in SSMS with the AdventureWorks database – I copied the Production.BillOfMaterials table to a new table called Production.BillOfMaterials2:

Here are some related posts from the intraweb:

• The INTO Clause page from Books Online for any other caveats – thanks for the suggestion Eric (blog/twitter)
• I found an article from Geeks with Blogs here: Copy a table into new table with/without data – SQL Server
• A good explanation from Caveman’s Blog here: Sql Server: How to copy a table?

Previous Tips O’ the Week

• TSQL Tip O’ the Week – 6/14/11 – The Empty GUID
• TSQL Tip O’ the Week – 6/6/11 – Declare and Assign Variables

I’m starting a new series of posts that will have a TSQL tip.  Some of them will be simple, some will be complicated, but hopefully they will all be helpful.

This weeks tip:

When using SQL 2008 use the feature that allows you to declare and set your variables in the same line.  Here’s an example of how to do it:

Declare @tester varchar(100) = ‘This saves time’

Note – This saves a ton of time for debugging stored procedures – any default value can be left as is.

Here are some related posts from the blog-o-sphere:

• SQL SERVER 2008 – 2011 – Declare and Assign Variable in Single Statement – March 18, 2011 by Pinal Dave
• SQL 2008 Inline Variable Assignment – Feb 3rd, 2009 by Colin Stasiuk.