DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

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;

SQL ISNULL example before

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;

SQL ISNULL example after

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;

SQL ISNULL implicit conversion example

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....

 

 

 

 

Filed Under: All Articles, SQL Tips and Tricks Tagged With: sql server, t-sql

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”

SQL COALESCE example

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:

SQL COALESCE result

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! 🙂

Filed Under: All Articles, SQL Tips and Tricks Tagged With: mysql, sql, sql server, t-sql

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 INSERT INTO

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.

Filed Under: All Articles, MySQL Administration Tagged With: backup and restore, mysql

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.

New Feature of SQL Server 2016 configure multiple tempdb files

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

Filed Under: All Articles, SQL Server Administration Tagged With: sql server, sql server 2016, tempdb

  • « Previous Page
  • 1
  • 2
  • 3
  • 4
  • …
  • 22
  • Next Page »

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close