My favorite SQL tip

Get SQL Server to do the hard work for you, and learn a lot of useful T-SQL while you’re at it. Nearly every operation you can perform with the SQL Server Management Studio GUI can be converted into a T-SQL script. This has a lot of advantages:

  • You can save it and/or share it
  • You can put it into Source Control
  • You can execute the same predicable script across multiple environments (such as dev, test, prod)
  • You can use the generated code as the basis for a more complicated script. I often end up copying & pasting the script and using search + replace to execute a similar operation on multiple objects. Sometimes I feel guilty and remember my CS106 section leader yelling at me that if I’m copying & pasting code, I should be looking to decompose a function and/or set up a loop. Thx Akash.
  • You can batch up a lot of long running statements and let them run while you take a walk, get a coffee, nap in a corner, etc.
  • You often get a lot more feedback in to what’s going by watching the Message tab than you get from watching the spinning green wheel, and you can copy/paste any error messages a whole lot easier from the query results window than from a dialog box. This could save you a lot of filtering in a SQL Profile session.
  • You can prepare a statement in advance, and wow your coworkers with your script-fu and knowledge of arcane DDL commands.


Technorati Tags: Tips and Tricks,SQL Server,T-SQL,Scripting