SQL tips #1

Jump to Comments

definitely purely for newbies, helpful tips my colleague in Houston passed on to me, so thanks to Alex, SQL guru.

Playing it safe
This is very useful if you are running SQL that will change data or insert data. So this is how I recently wrote a buncha of SQL codes that would modify menus, set up data for report collections etc.

BEGIN TRANSACTION
…
(SQL code)

COMMIT
ROLLBACK

Firstly run the code together with BEGIN TRANSACTION. After this, check the affected tables, views etc, or how it has changed your web app if applicable and if all is in working order, just select COMMIT and run this and the changes you have made would become done. If its not what you desired, just highlight ROLLBACK, and the code you have run would not be applied. Do note that if you neither COMMIT or ROLLBACK, the SQL code would not have been, well, commited.

sp_help and sp_helptext (MS SQL Server Management Studio)
run
sp_help table/view
sp_helptext view/stored procedure
in MS SQL Server Management Studio

and sp_help displays all kinds of useful info about the table/view, like columns, keys, constraints. sp_helptext displays the sql definition of the view/stored procedure. This quick method has worked out to be very useful for me.

shortcuts (MS SQL Server Management Studio)
hold down SHIFT + F1 when selecting any SQL syntax, meaning SQL code like GROUP BY, DELETE, ALTER, IN, UPDATE… (you get the picture), for me basically any code that’s in blue
and this opens a help window, showing the definition of the syntax and how it is used.

There are many other useful shortcuts which I will probably discover in time to come, but these are the few that I have been using quite a bit of lately. I will definitely be posting up any other SQL tips I learn as I go along for sure.

1 Comment

Filed under SQL

Tags: MS SQL Server Management Studio, SQL