Rounding Up in SQL

Working in the telecommunications industry, you run into situations not always found in the real world.  For example, when billing a call or applying taxes to an account, you always round up.  We can't charge a customer anything less than 1¢ (since they can't pay less than 1¢), so you have to round up.

SQL has a function that allows you to perform rounding on your values.  This function is called ROUND(), but only does true rounding (below 5 = round down, above 5 = round up).  So using this function, how do we always round up?

First, we have to figure out how many decimal places we will be billing our customer.  Let's say, for example, that our customer's calls are rounded to the nearest two decimals.  Once we know how many decimals we need to round, we can apply our logic.  The logic is then to add 49 one digit past the decimal place you are trying to round.  In this case, since we want to round to the nearest two decimal places, we will add $0.0049 to our value and then round.  If we wanted to go three decimal places, we would add $0.00049, $0.000049 to four decimal places, etc.

The following will round to $0.01:

select ROUND(0.013, 2)

But if we want to always round up, we need to add $0.0049:

select ROUND(0.013+0.0049, 2)

The above rounds to $0.02.

Now, you might be asking yourself: why do we add $0.0049 instead of $0.005?  Well, adding $0.005 might inadvertently makes us round higher than we want to go.  Look below for an example, which will round up to $0.03:

select ROUND(0.020+0.005, 2)

When we actually want it to stay at $0.02:

select ROUND(0.020+0.0049, 2)

I've found it is more productive to add $0.0049 to the number and then round, as opposed to trying to figure out a way for SQL to round up only those values that need it.

To read up more on rounding, go here: SQL ROUNDING

SQL CHOOSE

SQL Server has a list of logical functions that can be used on data.  The first of these logical functions is the CHOOSE() function.  This function will return the value identified at the specified index.  Here is what the syntax for the CHOOSE() function looks like:

CHOOSE(index value, value 1, value 2, value 3, etc.)

A pretty simple example of the CHOOSE() function looks like this:

SELECT CHOOSE(3, 'Executive', 'Senior Management', 'Vice President')

The above would return 'Vice President', since the index for lookup is 3 and the third value is 'Vice President'.

So how would a function like this be useful?  Well, we could use it to list out the months of the year for an employee's birthday, like so:

SELECT CHOOSE(Month(Birthday), 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')
FROM Employee

How does this differ from what you might see using the CASE() function?  There isn't a difference.  CHOOSE() is just a short hand version of CASE().  It definitely looks neater than CASE(), since you don't have to do so much coding.  Using the example above, CASE() would look something like this:

SELECT CASE Month(Birthday)
WHEN 1 THEN 'January'
WHEN 2 THEN 'February'
WHEN 3 THEN 'March'
WHEN 4 THEN 'April'
WHEN 5 THEN 'May'
WHEN 6 THEN 'June'
WHEN 7 THEN 'July'
WHEN 8 THEN 'August'
WHEN 9 THEN 'September'
WHEN 10 THEN 'October'
WHEN 11 THEN 'November'
WHEN 12 THEN 'December'
FROM Employee

As you can see, much mode coding needs to be done, while also adding the value you are looking for.  CHOOSE() already knows the value you are looking for and selects it based on the order in the provided value list.  Any value searched for that is not contained in the value list (such as 13 for our months) will return a NULL value.

How do you think you could use the CHOOSE() function at your organization?

To read up more on SQL CHOOSE(), go here: SQL CHOOSE

SQL SUBSTRING

There may be reasons why you may only need part of the text and not the whole thing.  For example, if you wanted to organize your customer list by first initial of their company name.  How would you do this?  SQL Server has a function called SUBSTRING().  This function allows for you to specify your starting position and the number of digits you want to take.  If you want the first digit of a customer's company name, you would write your query like this:

SELECT SUBSTRING(CompanyName, 1, 1), *
from CustomerList

You could also use it to find out the Area Code of your customers, by getting the first 3 digits of their phone number:


SELECT SUBSTRING(PhoneNumber, 1, 3), *
from CustomerList


Unlike other programming languages, SQL's SUBSTRING() function is 1-based, while other programming languages are 0-based.  This means that if you want the first digit in SQL, you use 1.  With C#, for example, if you wanted the first digit, you would use 0.

You can also run a query to see how many customers you have for each letter in the alphabet.  This could be done by combining the SUBSTRING() function with a group by, like so:


SELECT SUBSTRING(CompanyName, 1, 1), count(*)
from CustomerList
group by SUBSTRING(CompanyName, 1, 1)
order by SUBSTRING(CompanyName, 1, 1)


SQL SUBSTRING()

What data could you see using the SUBSTRING() function on?

Using EXCEPT in SQL

EXCEPT is an Operator in SQL that will return any distinct values found in the "left" table that is not found in the "right" table.  How can this be useful?  Let's say that one day you notice you have some data corruption in your database.  Your Orders table has some orders that are not found in the OrdersDetails table.  How do we easily figure out which orders are missing?  Our initial query could look something like this:

SELECT distinct OrderNumber
from Orders o
LEFT OUTER JOIN OrdersDetails d
on o.OrderNumber = d.OrderNumber
WHERE d.OrderNumber is null

This gets the job done, but doesn't look very organized.  We have to make sure we:

• Have the correct join (LEFT or RIGHT, depending on which table has the rows we want)
• Make sure the NULL is on the correct table (NULL would return where rows don't exist).

An easier and more efficient way to do this is to use the EXIST Operator in SQL:


SELECT OrderNumber
from Orders
EXISTS
select OrderNumber
from OrdersDetails

You will want to make sure that your data types for comparing are the same.  If not, it is possible you will not get expected results.

Where do you think you could use EXCEPT in your organization?

To read more on EXCEPT, go here: SQL EXCEPT

SQL Having Clause

Let's say you have a query that you want to run, where you summarize customer information (order totals, number of calls, etc.).  With your query, though, you only want to include customers that have over a certain value for what you are summarizing.  The SQL WHERE clause doesn't allow for aggregate functions, so what do you do?

To exclude customers under a certain value, you would have to use the SQL HAVING Clause.  There are a few things you need to keep in mind when using the HAVING Clause.

• The HAVING Clause occurs after your GROUP BY.
• To use the HAVING Clause, you must have a GROUP BY because you are aggregating data together
• You do not need a WHERE Clause

So, how does the HAVING Clause work?  Let's say that we want to see the total dollar value of a customer's order.  The statement would look something like this:

SELECT LastName, SUM(Total) Total

from OrderDetails

Group By LastName

Now, we want the query to return customers whose sum total is greater than $100.  That query would look like this:

SELECT LastName, SUM(Total) Total

from OrderDetails

Group By LastName

Having SUM(Total) > 100

We can also throw a WHERE Clause in there if we just want it returned for customers that have a certain last name:

SELECT LastName, SUM(Total) Total

from OrderDetails

WHERE LastName = 'Smith'

Group By LastName

Having SUM(Total) > 100

The HAVING Clause can also be used for non-aggregate functions, but at that point, it is better to use the WHERE Clause.  This is because the WHERE Clause removes the data before the result set is returned, while the HAVING Clause is applied to the rows after the result set has been returned.

Read more on SQL HAVING Clause here: SQL Having Clause

SQL Stored Procedures

Yesterday I talked about SQL Functions and their benefits.  Today I wanted to talk about SQL Stored Procedure.  Much like a SQL Function, a Stored Procedure takes in parameters and performs certain actions.  A Stored Procedure can do more, though, which would include the following:

• Update data within the database
• Return more than one value
• Call (run) other stored procedures
• Return status flag on whether or not the process completed successfully or failed

So why would someone have need for a SQL Stored Procedure?  Let's take the situation, for example, that you are an online company that takes sales orders, much like Amazon.com or eBay.  Every time someone completes an order, you have two choices.

1. Have the web interface (website) run certain insert statements into your database so the order is tracked.
2. Have the web interface (website) run one or multiple Stored Procedures to insert the order into your database

Both options are perfectly viable, but for option #1, anyone who understands HTML code can easily capture your statements and use it to hack into your database.

The second option, on the other hand, not only obfuscates (hides) data about your database, but you can also more easily control what happens if an error occurs at any moment in the insert.  This could include trying to run the stored procedure again if it fails, or show a pop-up window to a customer on failure.  These stored procedures can also be set up to fulfill your company's business rules.  And then, if for whatever reason your business rules change, you just have to update your stored procedure, instead of your website.

The syntax to create a stored procedure looks like the following:


CREATE PROCEDURE  
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> = , 
<@Param2, sysname, @p2> =
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END

If I wanted to create a stored procedure that returned my name, it would look like this:

CREATE PROCEDURE myName

AS

BEGIN

SELECT 'Bill Lantz'

END

Of course, stored procedure can be much more complicated than this.  For instance, if you wanted to allow someone to provide a customer first name, last name, and phone number and then insert it into your customer table, it would look something like this:


CREATE PROCEDURE insertCustomer
(
@LastName varchar(100),
@FistName varchar(100),
@PhoneNumber varchar(10)
)
AS
BEGIN

Insert into Customer
Values (@LastName, @FirstName, @PhoneNumber)

END

How do you think SQL Stored Procedures would work at your organization?

Non-SARGable Queries

As I've been doing some research online, I've come across a term that I am not familiar with.  This term is SARGable and is used in reference to the WHERE clause of select statements.  SARG actually stands for Search ARGument able.  Basically, if you have an index or multiple indexes on a table, a SARGable WHERE clause can take advantage of your indexes.  On the other hand, a non-SARGable WHERE clause cannot take full advantage of the indexes and will cause your query to take longer to run.

Keep in mind that this only applies to WHERE clauses.  If these statement are used in your SELECT statement, it will not affect performance.

So, what makes a query non-SARGable?  Here are some examples:

• Using LIKE with a leading %
• SELECT...... WHERE LastName like '%mith'
• Using a field within a function in the WHERE clause
• SELECT...... WHERE RIGHT(LastName, 5) like 'mith'

Each of these queries skips the index and uses a table scan, causing your query to run longer and resources to be tied up.  So what can we do instead?

• Use =, >, <, >=, <=, BETWEEN, LIKE without a leading %
• SELECT....... WHERE LastName like 'Smith%'
• Use IN, OR, NOT IN, NOT EXISTS, NOT LIKE
• SELECT....... WHERE LastName not in ('Smith')

Using these clues can increase your performance and should be used at all times when possible.

Wikipedia on "SARGable"

REPLACE Function

Let's say that you have a customer database and you need to update all of your customers' addresses.  You need to change every instance of "Street" to "St."  How would you go about easily doing this using a T-SQL query?

Microsoft SQL Server has a nifty function called REPLACE().  Following is the basic syntax for the REPLACE function:

REPLACE ( string_expression , string_pattern , string_replacement )

In our example above, we would put the field of our address in the "string_expression", the value "Street" in the "string_pattern" and "St." in the "string_replacement:.  You would have a query much like this:

update customer
set address1 = REPLACE(address1, 'Street', 'St.')
where address1 like '%Street%'

You can also use REPLACE() to reference a value in one field to update another field.  For example, instead of using strings in "string_pattern" and "string_replacement", you can use fields in your table.  If the customer table contains both a phone number and an extension for your customer, you could use this to update the customer's call records.  A query would look something like this:

update RatedCall
set CallingNumber = REPLACE(CallingNumber, ANI, Customer.Extension)
from RatedCall, Customer
where RatedCall.CustomerNumber = Customer.CustomerNumber

Where ANI is the customer's phone number and extension is their extension (CallingNumber is the ANI is the call record).  This would only update instances where CallingNumber is the customer's phone number.

In what cases at your organization could you see using the REPLACE() function?

To read up more on SQL REPLACE(), go here: SQL REPLACE()

SQL Indexes and Their Benefits

As the data in your database grows, you may notice that your querying becomes slower.  This occurs because SQL Server needs to search through each record until it finds all the records you are looking for.  So, when this happens to your database, how do we approach the problem?

The answer to this lies in something called indexes.  An index is described as a structure on a table or view that speeds up the retrieval of data.  In SQL Server, there are two major types of indexes:

1. Clustered Index
• Clustered indexes sort and store the data based on key values (specified by you).
• Each table can only contain one clustered index.
2. Nonclustered index
• Nonclustered indexes do not sort and store the data like clustered indexes do.  They basically have a pointer to the data based on the key values selected.
• The pointer is actually called a row locator.
• Depending on your version of SQL Server, you can have quite a few nonclustered indexes.

When performing a search on table, SQL Server will use the least labor intensive method.  Indexes will be the least labor intensive (finds storage location for the data) than a non-indexed table (table scan), which needs to search through each row to find a match and can be resource intensive.

Now, it is very important to put in time considering what should be included in your indexes.  For example, clustered indexes should be fields that have unique data (primary key).  Nonclustered indexes could be created on other columns that you search on frequently and multiple columns that are searched together could be included on the same nonclustered index.  Be careful, though, because adding too many indexes (one of every column) will cause too much overhead.

Don't be afraid to try out different indexes on your data.  Put an index in place, query the table, and see if your querying improves.  If not, try a different index and keep trying until you feel like you have a couple that fit your needs.

Clustered and Nonclustered Indexes Described

The Magic of Triggers

I was recently asked a question at work by one of our developers.  He needed some help on updating one of his stored procedures.  He was looking to see if there was some kind of variable that he could use to pass the current table he was querying.  This made me curious as to why he would pass such a variable.  After some discussion, I realized that he was looking to create audit trail for our internal website using that stored procedure.

My suggestion is quite uncommonly known for people new to Microsoft SQL Server.  I introduced him to the idea of triggers.

SQL Triggers

Now, what are triggers and why are they so useful?  Triggers are a special kind of stored procedure that occurs (is triggered) when a certain event happens on a given table.  Different events that can activate a Trigger include:

• Insert
• Update
• Delete

So, now onto why they are useful.  Let's say, for instance, that you have a Customer table and every time a new customer is added, a contact is created in your Contact table.  Now, you could write a stored procedure that you would call once you've added the customer account.  On the other hand, why not create a trigger that would insert the new contact into the Contact table automatically, without a need for calling anything.  The trigger would look something like this:

CREATE TRIGGER tContact
ON Customer
AFTER INSERT 
AS 

INSERT INTO Contact (CustomerId, FirstName, LastName, EMail, PhoneNumber)
SELECT CustomerId, FirstName, LastName, Email, PhoneNumber

from Inserted

GO

SQL Server also creates two temporary tables that can be used during the Trigger: Deleted and Inserted.  The Deleted table contains original data from the table during Update and Delete statements.  The Inserted table contains data that is effected during Insert and Delete statements.  These tables could be used to grab data for whatever the purpose of your trigger is.

Overall, Triggers can be very useful, depending on the purpose.  Don't create too many triggers, though, as this could create too much overhead on the system.  Also, triggers are extremely hard to debug, so for debugging purposes, stored procedures might be better.  For more on the disadvantages triggers, visit the following blog:

Disadvantages of Triggers