SQL Tips & Tricks #3

I have a lot to learn.  I know that this is particularly true when it comes to SQL, so I try to note down neat things I learn about SQL and add them to one blog post when I have enough.  If you missed the previous ones, you can view them here and here.

SET NOCOUNT ON

SET NOCOUNT ON prevents the message that shows the number of rows affected by a statement (or stored procedure) from being returned in the Results window.  The number of rows affected is still returned to the client, but the count is marked as not to be used.

Why?

If you have a stored procedure that contains a lot of loops, turning this ON may provide a performance boost (and it could be significant).  Microsoft encourages SQL developers to use SET NOCOUNT ON in stored procedures.

How?

This one is pretty easy:

TRUNCATE

The TRUNCATE TABLE command removes all of the rows from the specified table.

Why?

One case scenario for this might be if you have a table in database A that will be used for an EDI process in database B (or in application B).  You might import your data into a table that then gets some procedures called on it to pull all of the data into its respective tables and rows.  Once the process is done, you can TRUNCATE the original table so that it will be ready for the next batch of data.

How?

CASE

Case statements are common in many programming languages (it is worth nothing that Python lacks Case statement functionality).  A case statement is very similar to an if statement.  Case statements can be nested inside select statements, which make them really powerful!

Why/How?

There are many reasons you might use a case statement.  Say you have a table with the columns PaidStatus (bit) and Amount (float).  If you want to get the total paid and the total unpaid, it might be pretty tricky to do.  However, CASE statements make this task pretty easy:

Another reason you might want to use a Case statement is to modify the output of your select statement to be more ‘human readable’.  For instance, you could have a CASE statement like this:

This SQL statement would allow you to select mileages as human readable amounts such as ‘over 100,000’.  This could be helpful for building reports or sometimes even just understanding the data for yourself.  Also note that in this case statement, we added an ELSE.  If the mileage (which is a column in your table) does not fall into any of the other case statements, it will default to the ELSE statement.

 

Have questions or suggestions?  Please feel free to comment below or contact me.

Leave a Reply

Your email address will not be published. Required fields are marked *