When SQL Server 2000 became SQL Server 2005, a huge number of things changed. DTS became SQL Server Integration Services, and as part of the package we saw the likes of Analysis Services and Reporting Services. All these additions to the SQL Server family necesitated a change in the suite of management tools – and that meant the death of Enterprise Manager and SQL Query Analyzer, tools loved by everyone, everywhere. They were combined and rebranded as a single tool: SQL Server Management Studio. DBAs and SQL developers cried out in horror – the new tool was, well, new – it was very different and required a change in working practice. What’s more, there was no choice – if you wanted SQL Server you had to use SSMS – there’s no using Enterprise Manager (although you can use SSMS to manage SQL Server 2000.)
The thing is, SSMS actually provides a lot of productivity tools to make your life easier, but they often go overlooked as people are used to working in Enterprise Manager, even now, some 6 years after it was released with two further releases since (SQL Server 2008, SQL Server 2008 R2). So here’s a quick look at some of the things you can do to speed up your development work, without even touching on the core features of SQL Server.
1) Explore the context menus
All items in SSMS have a context menu from the right click, and most, if not all, database objects provide a wealth of ability to quickly script tasks to either a query, a file or the clipboard. For instance, right click a table, click SELECT TO > Query Window and you’ll be provided with a query window with a full list of fields you can select. In SSMS for SQL 2005, the SELECT TOP ROWS option was removed. In SSMS 2008, it’s back in. You can rapidly build store procedures and other scripts using this functionality.
2) Empower the editor – search and replace with regex
Imagine you’ve used tip 1 to output 100 lines of SELECT SQL to a new query window. But you want to refactor it a bit. You could either edit each line by hand (yawn) or use some Regex. CTRL + H opens up the find/replace window.
Example 1
– Find e.g., ,<CaseKey, varchar(50),>)
,\<{[a-zA-Z ]+}, [a-z]+[\(\)0-9]+,\>
– Replace with e.g., ,CaseKey = ”
,\1 = ”
Example 2
Text:
” AS [Some Alias Name],
Regex:
{”}:b+AS:b{\[[a-zA-Z ]+\]},
Square brackets optional
{”}:b+AS:b{[\[a-zA-Z \]]+},
Replacement:
,\2 = ”
Produces:
,[Some Alias Name] = ”
3) Visual Studio style development . The editor inherits some of the functionality that you can get in Visual Studio. Explore the keyboard shortcuts, e.g., CTRL+K, CTRL + C to quickly comment out a section*.
We also get simple debugging
And in SSMS 2008, the piece of resistance everyone wanted, Intellisense:
4) Zero in on errors
If the VS style debugger isn’t your thing, you easily debug and check the syntax and run any piece of your script at any time, simply by selecting it and CTRL + F5 to check syntax, or just F5 to run it. So if you’re testing a long sproc, you can quickly check individual blocks by selecting each one in turn and hitting F5.
5) Deployment made easy
When deploying a database, questions are always asked… should I backup and restore to the new site, or recreate it by hand, or? Well that question just got answered: use the built in Generate Scripts functionality to quickly create all the SQL required to build the database. Useful for deployment or DR scenarios:
Hopefully some of the tips will make your SQL development more enjoyable.
* Request to the SSMS developers: can we please get more of these quick formats? CTRL + K, CTRL + D would be the obvious one to quickly format an area of code, especially the code created by the visual editor, which is ugly! For now, an online tool will have to suffice.
for