Andy Hayes | DBA Diaries

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Using ISNULL in SQL Server to Replace NULL Values

Posted on May 11, 2017 Written by Andy Hayes Leave a Comment

There are different ways to handle NULL’s and I wrote about one such way in my last post. In this post, we look at SQL ISNULL() and specifically at the SQL Server version. MySQL does have this function but it behaves a little differently as it returns a boolean value.

Let’s look at the syntax

ISNULL(check_expression, replacement_value)

check_expression – this is the expression to be checked. This can be any data type.

replacement_value – this is the value to replace the NULL with. This can also be a field.

The data type returned depends on what check_expression data type is. Pass in a varchar field here and a varchar will be returned. A varchar will be returned by ISNULL() here even if replacement_value has an integer value passed in. However, there are exceptions as the replacement_value has to be of a data type that can be implicitly converted to the same data type as check_expression.

If check_expression is NULL then the data type returned will be whatever data type is supplied as replacement_value

Let’s take a look at an example.

SELECT HomePhone 
FROM Leads;

So this is the data with some NULL’s in rows 2 and 3. Now we will apply ISNULL() to return a hyphen in place of the NULL.

SELECT ISNULL(HomePhone, '-') AS Phone
FROM Leads;

If we now pass in an integer as replacement_value, the code will still work fine because the integer can be implicitly converted to a varchar.

SELECT ISNULL(HomePhone, 1) AS Phone
FROM Leads;

So this works fine. Personally if I were writing this code, I would implicitly convert it within the SQL. In my opinion, this reads better because it’s clear what the data type returned is.

SELECT ISNULL(HomePhone, CAST(1 AS VARCHAR)) AS Phone
FROM Leads;

I would write it in the same way if replacement_value was a field for the same reason.

SELECT ISNULL(HomePhone, CAST(ReplacementField AS VARCHAR)) AS Phone FROM....

Using SQL COALESCE to Find the First Non-NULL Value

Posted on May 10, 2017 Written by Andy Hayes Leave a Comment

You are looking to find a way to find the first non-null value from a list of fields. In this post, we look at SQL COALESCE – a wonderfully useful tool that helps to solve that problem.

To help demonstrate this, we will talk about a hypothetical scenario where a number of customer leads have been added to a table from different sources and the consistency of data found in some of the key fields is sparse.

Here is the data, the table is called “Leads”

For the purposes of this example, the task is to pull out a single column list of phone numbers for the leads in the table and that involves finding the first non-null value.

Here is what you do:

SELECT 
  COALESCE(HomePhone, BusinessPhone, MobilePhone) AS Phone 
FROM Leads;

This produces a list:

You can see that it has taken the first phone number (non-null value) found based on the order of columns passed into COALESCE.

It is possible to write this in another way using SELECT CASE

SELECT 
 CASE WHEN HomePhone IS NOT NULL THEN HomePhone
      WHEN BusinessPhone IS NOT NULL THEN BusinessPhone
      WHEN MobilePhone IS NOT NULL THEN MobilePhone
   END AS Phone
FROM Leads;

The result is the same but it takes longer to write the code so using COALESCE in your SQL statement is certainly more efficient in this case – less is more! 🙂

How to Take a Backup of a Table in MySQL

Posted on May 9, 2017 Written by Andy Hayes Leave a Comment

Sometimes, there is a requirement to make a backup of a table in MySQL. For instance, there could be a data update planned, schema change needed or that it is necessary to work with a copy of a table, perhaps for some report.

A possible approach to this is to run a full backup of the database to call upon if something goes wrong. This is feasible but sometimes not practical. The database dump could take some time to complete and if something goes wrong and you need to restore, restoring the dump could equally take some time to complete.

In this post, I will show a couple of ways to take backups of tables in MySQL.

Method 1 – Taking a Backup of a MySQL Table Using LIKE

CREATE TABLE table_backup; 
INSERT INTO table_backup SELECT * FROM table;

This method creates the structure of the table including indexes and then loading the data in via one statement.

The advantages of this method are that makes a backup of the table immediately available with the database. It is however to all extents and purposes just like any other SQL statement by way of consuming disk space on the data drive. So disk space should be a consideration when performing this method.

Method 2 – Taking a Backup of a MySQL Table Using mysqldump

mysqldump -u{backup_user} -p{backup_password} from_db_name table_to_backup > backup_file.sql

Instead of backing up the entire database, a backup of the table can be made using mysqldump.

This is my personal preference if the requirement is just that a backup is needed and that there is no requirement to have the table online like in the first example. The backup is taken using mysqldump and can be directed to a location of choice. Disk space is therefore not a consideration for the data drive, rather it is necessary just for the location being backed up to.

SQL Server 2016 New Features – Multiple TempDB Files Configured at Server Install

Posted on June 12, 2016 Written by Andy Hayes Leave a Comment

Configuring multiple tempdb files is not a new feature in SQL Server and has been a best practice for sometime to help ease and prevent tempdb contention which helps to increase the scalability of your SQL Server installation.

Before SQL Server 2016, this was a task which needed to be carried out after the installation but one of the new features of SQL Server 2016 is the ability to configure this as part of the install process.

As you step through the installation and get to the point of Database Engine Configuration, there is a tab for tempdb where the configuration can be done.

There is not a lot else to say about this really except that it’s another welcome new feature of SQL Server 2016! 🙂