5 Underused SQL Functions, Developer-style

11_1

The frontend and backend should work in perfect harmony. That’s the idea anyways. Whether you’re thinking about front and backend developers, or a database server and a website server, sometimes it’s just not the case, but there’s hope at least in the SQL realm.

SQL has several functions you can use to calculate formatted output, which makes the frontend jobs way easier. Here’s how it works. Back-end developers perform calculations or formatted output when data is sent back to the web server. This puts more load on the web server, or front-end developers. For this reason, you should perform your formatting and calculations on the database server.

Here are five underused SQL functions that can speed up your website, and keep your front-end teammates happier.

  1.  The SUM Function
    Most SQL coders know of this function, but it’s underused. In many cases, the developer will retrieve a list of values and add them up in a loop structure in the backend web code. The number of values could be 10 or 1000. Looping through 1000 values on the web server takes much more resources than just using the SQL SUM function. If you need to add a list of values, use the SUM function and return the total to your pages. The following is a simple example of the SUM function:

SELECT SUM(total) FROM Order WHERE CustomerId=111

In the example above, the sum of all orders for customer number 111 is calculated.

  1. The AVG Function
    Similar to the SUM function, the AVG function is underused especially by front end developers. Usually, developers retrieve a list of results and then loop through them to perform the calculation. The values are added and then an average is calculated.

With the AVG function, you don’t need to do any of these calculations. The database does it for you. It gets the sum of the values and takes an average based on the record set count. The following is a simple example of the AVG function:

SELECT AVG(total) FROM Order WHERE CustomerId=111

The code above provides you with an average value for the customer’s orders.

  1. The SUBSTRING Function
    In many situations, you need to get a part of a string. Again, you could do this in your web server code or you can use the SUBSTRING function in your SQL stored procedures. For instance, you might want to get the first initial of a user’s first name. You can do this with the SUBSTRING function. The following example gets the first initial of a user’s first name:

SELECT SUBSTRING(firstname, 1, 1) FROM Order WHERE CustomerId=111

The function takes three parameters. The first one is the column name (firstname in this case), the second is the start of where you want to retrieve characters, and the third parameter is the number of characters you want to retrieve.

  1. The ROUND Function
    Sometimes you want to show whole numbers, and other times you want to round to the nearest decimal point. For instance, it’s common for a database to store more than two decimal points for a total value, but you only want to show two decimal points on the frontend web page. You can do this by using the SQL database server’s ROUND function. The following is an example of the ROUND function:

SELECT ROUND(total, 2) FROM Order WHERE CustomerId=111

In the above statement, the total for customer 111 is rounded to two decimal points.

  1. The COUNT Function
    The COUNT function adds up the number of records returned. Developers typically use a temporary variable to count the number of records as the code loops through each one. You can simply add the number of records using the COUNT function. The following is an example of the COUNT function:

SELECT COUNT(*) FROM Order WHERE CustomerId=111

In the above statement, the code counts the number of orders for customer 111.

Final Thoughts
If your frontend application is running slowly, you can put some of the load on the database server and speed it up. These SQL functions also reduce the code you need to calculate on the frontend, and you can save many of the formatting and looping structures once the data is returned.

New to SQL? CBT Nuggets has got you covered!

Not a CBT Nuggets subscriber? Start your free week today.