Teddy Caddy Teddy Caddy is a database reporting analyst and programmer at High Point University, maintaining SQL Server-based ERP systems. In this tutorial, he walks through some advanced techniques and issues that SQL Server administrators are likely to encounter.

Disclaimer: This is not a beginner’s tutorial. If you need a beginner’s tutorial, look here for a few examples.

1 Introduction

I work in a University IT environment on the team that manages the ERP system. The ERP database is a multi-value database (UniData) and data is exported to SQL Server for reporting and downstream purposes (this is called ODS or Operational Data Store). Many other vendor-provided solutions have SQL Server backends. Some of our in-house solutions have also used SQL Server backends. This tutorial will cover some of the more advanced issues I have come across.

1.1 Background

I started using SQL Server about 5 years ago, coming to it from an open source background. I didn’t like it at first. The drivers for PHP or Ruby, for example, were cumbersome and buggy relative to the drivers for other databases, such as MySQL. I found it more difficult to work with dates, times, and boolean data types. I didn’t like having to work from SQL Server Management Studio in a Windows environment.

Over time and after many projects, my familiarity and comfort with SQL Server has grown. At the same time, Microsoft’s approach to the open source community has become more helpful and friendly. The drivers have changed, with Microsoft itself providing drivers for PHP and Node.js (if you run on Windows), and the FreeTDS based drivers improving as well.

SQL Server has a pretty big footprint in the world’s data centers. There are plenty of IT shops where it is the only database in use. Businesses are comfortable in the “known” and “stable” hands of a Microsoft product versus being in the “unknown” and “unstable” world of open source products.

1.2 Getting started with virtual labs

You probably already have at least one SQL Server database that you work with if you are reading this, but Microsoft offers free access to Virtual Labs. For this tutorial, you can use the Your First Look at SQL Server 2008 R2 Sample Databases Virtual Lab. This lab already has SQL Server Management Studio installed with full admin access to create new databases.

2 Handling multiple rows for a “single record”

A common problem for the data I work with is what to do when you get duplicate rows when you were only expecting one. With data coming from a multi-value database, it is possible to have two or more rows for a given record id. My real-world example is cell phones; most people have one cell phone, but a few people have two or more. I wouldn’t want a report to have duplicate records simply because I added a cell phone column.

2.1 Example

Consider the following tables (created with this SQL):

SELECT * FROM people SELECT * FROM phones

If we write this query, we get 2 results for Ned Flanders. If this were a report, our users might complain that Ned Flanders is showing up twice after adding the cell phone column.

SELECT people.id, people.first_name, people.last_name, phones.phone_number as 'cell' FROM people LEFT OUTER JOIN phones ON phones.person_id=people.id AND phones.phone_number_type='cell' ORDER BY people.id

2.2 Identifying the problem

In our example, we can easily spot the duplicate record because there are so few records. But in a larger dataset, we won’t be able to quickly spot the duplicates. The easiest way to identify the presence of duplicate results is to compare a count of the record ids to a distinct count of the record ids:

SELECT COUNT(id) AS cnt, COUNT(DISTINCT(id)) AS distinct_cnt FROM ( -- previous query here, without ORDER BY clause SELECT people.id, people.first_name, people.last_name, phones.phone_number as 'cell' FROM people LEFT OUTER JOIN phones ON phones.person_id=people.id AND phones.phone_number_type='cell' ) results

In this example, the previous query is put into a subquery. This saves us from having to refactor the previous query too much in order to simply get a record count. The ORDER BY clause had to be removed because SQL Server does not allow it to be used in subqueries.

The next step is identifying the record ids that are causing the duplicate records:

SELECT * FROM ( SELECT id, COUNT(*) as cnt FROM ( -- previous query here, without ORDER BY clause SELECT people.id, people.first_name, people.last_name, phones.phone_number as 'cell' FROM people LEFT OUTER JOIN phones ON phones.person_id=people.id AND phones.phone_number_type='cell' ) results1 GROUP BY id ) results2 WHERE cnt>1 ORDER BY cnt DESC

In this example, we had to use two subqueries. The innermost subquery gives us a count of cell phone numbers for each person_id . The next subquery is necessary in order to have a WHERE clause for the cnt value. We are only interested in duplicate records, so that would be where the count is greater than 1.

2.3 Possible Solutions

First of all, we have to ignore one of the cell phone records belonging to Ned Flanders. In a real-world situation, hopefully we would have a preferred flag column or a date modified column to help us choose which record to keep and which ones to ignore. Also we will want to make sure the phone_number isn’t NULL or an empty string. In this sample case, let’s assume the highest id value is the newest and therefore most accurate record.

2.3.1 Use a SELECT within the SELECT statement

One way to fix this is to use a SELECT statement within the SELECT statement. Doing this will get only one cell phone for each person_id.

SELECT people.first_name, people.last_name, ( SELECT TOP 1 phone_number FROM phones WHERE phones.person_id=people.id AND phones.phone_number_type='cell' AND phone_number IS NOT NULL AND RTRIM(LTRIM(phone_number))!='' ORDER BY phones.id DESC ) AS 'cell_phone' FROM people ORDER BY people.id

In this example, we are selecting only one cell phone record per person. What determines the selected cell phone record is the ORDER BY clause; you would put your business logic here to reduce it to one result per person. It is important to have the extra checks in the WHERE clause, as we are omitting records in an attempt to select the "best" record. We wouldn’t want to select a NULL value, just because it was the most recent.

2.3.2 Write a view

A view is essentially a saved SELECT query that isn’t allowed to have an ORDER BY clause. When you create a view, it becomes a virtual table and the results of the view can be queried in the same way as you would query a table.

There are a lot of good reasons to go ahead and make a view to solve this problem:

Save work for ourselves next time. Less-technical report writers can query this easily. Drag-and-drop reporting software can use it.

2.3.2.1 Getting the Row Number

Let’s start with this query:

SELECT id, person_id, phone_number, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id DESC) AS row FROM phones WHERE phone_number_type='cell' AND phone_number IS NOT NULL AND LTRIM(RTRIM(phone_number)) != ''

The special part in this query is to use ROW_NUMBER() OVER (...) syntax to get a row number on a per-person basis. It is kind of weird syntax; it helps to think of PARTITION BY being like a GROUP BY clause. The idea is to get a new column called row with a prioritized order from 1...n for each person_id. Then all we have to do is have a WHERE clause to specify that row=1.

Again, you see the same restricting WHERE clause and the same ORDER BY clause as before.

2.3.2.2 Final Result

The final query looks like this:

SELECT id, person_id, phone_number FROM ( SELECT id, person_id, phone_number, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id DESC) AS row FROM phones WHERE phone_number_type='cell' AND phone_number IS NOT NULL AND LTRIM(RTRIM(phone_number)) != '' ) results WHERE row=1

We used a subquery and added the WHERE clause to restrict it to the first row-per-person. Also, the row number column is omitted from our final SELECT , as we don’t want it in the results.

2.3.2.3 Turning a SELECT query into a view

Creating a view basically involves putting a little bit of special syntax before and after your SELECT statement.

CREATE VIEW dbo.cell_phones AS SELECT id, person_id, phone_number FROM ( SELECT id, person_id, phone_number, ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY id DESC) AS row FROM phones WHERE phone_number_type='cell' AND phone_number IS NOT NULL AND LTRIM(RTRIM(phone_number)) != '' ) results WHERE row=1 GO

2.3.2.4 Testing it out

We can add new phone records to see our view update and we can test it out with “bad” cell phone entries to make sure our view omits them.

-- insert a few types of "bad" records INSERT INTO phones (person_id,phone_number,phone_number_type) VALUES (1,' ','cell'),(1,NULL,'cell'),(1,'','cell'), (2,' ','cell'),(2,NULL,'cell'),(2,'','cell'), (3,' ','cell'),(3,NULL,'cell'),(3,'','cell')

-- select our view, make sure it still looks good SELECT * FROM cell_phones

As you can see, the view works as expected, filtering out the unwanted records that are null or empty. With a view like this in place, we can offer our users reliable results even if we cannot necessarily stop the bad data from being in the database in the first place.

3 Consolidating multiple results

Let’s say we wanted to show every person’s phone numbers in one field, separated by commas. How would we do that?

SELECT people.*, SUBSTRING(phone_list,1,LEN(phone_list)-1) AS cell_phones FROM people CROSS APPLY ( SELECT phone_number+', ' FROM phones WHERE person_id=people.id AND phone_number_type = 'cell' AND phone_number IS NOT NULL AND LTRIM(RTRIM(phone_number)) != '' ORDER BY phones.id DESC FOR XML PATH('') ) results (phone_list)

This is a pretty strange query. As you can see, the results are what we expected, but how did we get here? If you look at line 1, we are selecting everything from the people table, along with a substring of phone_list . The phone_list column is a concatenation of phone_number , a comma, and a space. If we didn’t take a substring of the phone_list column, it would actually end with a comma and a space, which is not desired.

The CROSS APPLY part of the query is what returns the comma-joined phone numbers. It works in a similar manner to a join, but allows you to invoke a table-valued function for each row. The syntax on the closing end of the parentheses on line 12 is what allows us to work with the column with the name phone_list on line 1.

The FOR XML PATH('') part of the query on line 11 concatenates our rows together as one string. This is actually a hack of SQL Server’s ability to output XML.

The FOR XML PATH() function expects a tag name so it can make a XML node with a start and end tag; it also joins multiple rows together as one text string. We are using a blank string and therefore it doesn’t create a tag, but it does join together multiple rows as one text string. SQL Server also wants to add a start and end tag with the column_name by default, but we have appended a comma and a space to the the phone_number select, creating an unnamed selection and therefore avoided another XML tag that SQL Server would ordinarily insert.

4 Caching Your Views

Our sample view doesn’t present any performance issues, but in the real world, views can do a lot of work and sometimes they take too long to generate.

In an ODS environment, the data is stale after a refresh, so there is no technical need to query the view more than one time after a refresh. If you can cache the view after a data refresh, it will be identical.

In other environments, it is a case-by-case evaluation. Sometimes cached results are not an acceptable alternative to real-time results. Here are some reasons for using cached results:

Reduces load on transactional / production databases. Speeds up query times. Allows all stakeholders to view same snapshot of data.

4.1 Creating the cached table

To do this, simply create a table that matches the schema of your view, making sure to keep the same column order and column names. You might need to do a little research to figure out exactly how big you need to make each field. You should make each field large enough to hold the largest possible value that could ever come from the view.

CREATE TABLE cached_cell_phones ( id int NOT NULL, person_id int NULL, phone_number varchar(50) NULL, CONSTRAINT PK_cached_cell_phones PRIMARY KEY(id) ); GO

4.2 Updating the cached view

Because the schema matches the view, you can populate the cached table with this query:

DELETE FROM cached_cell_phones; INSERT INTO cached_cell_phones SELECT * FROM cell_phones

4.3 Updating the cached table from a stored procedure

We can create a simple stored procedure to update the cached view for us:

-- ============================================= -- Author: Caddy, Teddy -- Create date: 2014.10.05 -- Description: Caches cell_phones view -- ============================================= CREATE PROCEDURE usp_cache_cell_phones AS BEGIN SET NOCOUNT ON; DELETE FROM cached_cell_phones; INSERT INTO cached_cell_phones SELECT * FROM cell_phones END

4.3.1 Executing a stored procedure

Before executing the stored procedure, we should check the cached_cell_phones table to make sure it is empty.

SELECT COUNT(*) AS 'count' FROM cached_cell_phones

We can execute the stored procedure with this query:

EXEC usp_cache_cell_phones

After executing the stored procedure, we can check the cached_cell_phones table again to verify that it was populated.

SELECT COUNT(*) AS 'count' FROM cached_cell_phones

5 Linked Servers

It is pretty easy to query other databases on the same SQL Server host by prefixing references to the table name with [database_name].dbo.[table_name] , provided your credentials provide access to both databases. However, it is also possible to do this with instances of SQL Server hosted on a different server. These are called linked servers.

Once you have set up a linked server, you can query a database on the linked host with [hostname].[database_name].dbo.[table_name] syntax. You can find the names of the linked servers with this query:

EXEC sp_linkedservers

One thing to keep in mind with linked servers is that you can easily incur a performance hit. If you do a join to mix local and remote databases, it requires a lot of data to be sent over the network in order to make the join work and that creates a performance hit.

To work around this, use subqueries from within your WHERE clause, like this:

SELECT [local_table].* FROM [local_table] WHERE [local_id] IN ( SELECT DISTINCT(remote_id) FROM [remote_host].[remote_database].dbo.[remote_table] WHERE some_attribute=1 )

Linked servers are something you’d might have to ask your database admin to setup. Since it requires two distinct SQL Server hosts, we won’t be setting this up in the tutorial.

6 Collation incompatibilities

After you begin to work with linked servers, you are likely to run across collation issues. This is where the character set of one database doesn’t match the other.

You will get an error that looks like this:

Cannot resolve the collation conflict between "Latin1_General_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Although we won’t be creating an actual linked server connection in this tutorial, we can replicate a collation conflict by creating a column with a collation that isn’t the database default.

Let’s create a collation conflict by creating another table with a different character set and using a foreign key with alpha characters. We will create an addresses table with this SQL that looks like this:

SELECT * FROM addresses

(Any Simpsons fan will understand why the state is NULL, but in case you are not a fan, there is a running joke where they won’t tell the viewers what state Springfield is located in.)

Let’s update our people table to have a new column called alt_id . You can update it with this SQL. After the update, it should look like this:

SELECT * FROM people

Let’s verify that our collations do not match:

SELECT TABLE_NAME AS 'Table', COLUMN_NAME AS 'Column', COLLATION_NAME AS 'Collation' FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME='addresses' AND COLUMN_NAME='id') OR (TABLE_NAME='people' AND COLUMN_NAME='alt_id')

Let’s create a collation conflict error by attempting to make a join on these columns:

SELECT people.id, people.first_name, people.last_name, addresses.[address], addresses.city, addresses.[state] FROM people LEFT OUTER JOIN addresses ON addresses.id=people.alt_id

As you can see, we were able to create the collation conflict error.

Let’s fix the collation conflict error:

SELECT people.id, people.first_name, people.last_name, addresses.[address], addresses.city, addresses.[state] FROM people LEFT OUTER JOIN addresses ON addresses.id=people.alt_id COLLATE Latin1_General_BIN

7 Sending email

You can send email natively from within SQL Server using Database Mail. This is a great tool for notifying sysadmins about errors or other database events. You could also use it to send a report or an email message to an end user.

The basic syntax for this is:

EXEC msdb.dbo.sp_send_dbmail @recipients='user@yourdomain.com', @subject='Testing Email from SQL Server', @body='<p>It Worked!</p><p>Email sent successfully</p>', @body_format='HTML', @from_address='Sender Name <sender@yourdomain.com>', @reply_to='sender@yourdomain.com'

Before use, Database Mail must be enabled using the Database Mail Configuration Wizard, or sp_configure . A database or Exchange admin might need to help you configure this.

See http://msdn.microsoft.com/en-us/library/ms190307.aspx and http://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Server for more information.

8 Catching errors

Catching errors in SQL Server is similar to other programming languages. The basic syntax is:

BEGIN TRY -- Do something to throw an error END TRY BEGIN CATCH -- Do something to handle the error END CATCH

Consider this simple error:

SELECT 1/0

As you can see, you get a “Divide by zero error encountered” message. We can catch this error and get some details about it.

BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS 'ERROR_NUMBER', ERROR_MESSAGE() AS 'ERROR_MESSAGE', ERROR_LINE() AS 'ERROR_LINE', ERROR_PROCEDURE() AS 'ERROR_PROCEDURE', ERROR_SEVERITY() AS 'ERROR_SEVERITY', ERROR_STATE() AS 'ERROR_STATE' END CATCH

9 Base64 encoding and decoding

SQL Server has native Base64 encoding and decoding capabilities. Unfortunately there are no useful functions delivered for this. You have to use an XML hack to utilize the feature.

9.1 Base64 encoding

In order to encode Base64 data (convert data to Base64) you need to provide a VARCHAR or NVARCHAR value to the second argument in the value() function, as shown below.

9.1.1 Encoding an SQL variable

Let’s say you had some binary data stored in a variable called @binary_data_var . You could convert it to Base64 with this query:

SELECT CAST(N'' AS XML).value( 'xs:base64Binary(sql:variable(''@binary_data_var''))','NVARCHAR(MAX)' )

There are three things to pay attention to in this query:

The use of sql:variable() -- this means a variable will be encoded/decoded. The location of @binary_data_var -- this is where you place the variable name, notice the use of double single quotes around the variable name. The use of NVARCHAR(MAX) -- this is the second argument to the value() function and it tells SQL Server how to cast the output and therefore if it will encode to Base64 or decode from Base64.

9.1.2 Encoding a SQL column

Let’s say that you had data in a VARBINARY column called binary_data_col . You could convert it to Base64 with this query:

SELECT CAST(N'' AS XML).value( 'xs:base64Binary(sql:column(''binary_data_col''))','NVARCHAR(MAX)' )

This query is nearly identical to the last, with the following two exceptions:

The use of sql:column() -- this means a column will be selected for encoding/decoding. The use of the column name binary_data_col instead of the variable named @binary_data_var -- since we are using sql:column(), we need to provide a column name. Notice the same use of double single quotes around the column name.

9.2 Base64 decoding

In order to decode Base64 data you need to provide a VARBINARY value to the second argument in the value() function, as shown below.

9.2.1 Decoding a SQL Variable

Let’s say you had some Base64 data stored in a NVARCHAR variable called @base64_var . You could decode it with this query:

SELECT CAST(N'' AS XML).value( 'xs:base64Binary(sql:variable(''@base64_var''))','VARBINARY(MAX)' )

This query is like the one above that we used to convert @binary_data_var , except the second variable passed to value() is VARBINARY(MAX). This is what tells SQL Server to decode Base64 data.

9.2.2 Decoding a SQL column

Finally, let’s say you had some Base64 data stored in NVARCHAR column called base64_col . You could decode it with this query:

SELECT CAST(N'' AS XML).value( 'xs:base64Binary(sql:column(''base64_col''))','VARBINARY(MAX)' )

9.3 Base64 Encoding/Decoding Example

In this example, we will decode and re-encode a Base64-encoded version of a PNG image of a red dot:

DECLARE @base64 NVARCHAR(MAX) SET @base64='iVBORw0KGgoAAAANSUhEUgAAAAUAAAAFCAYAAACNbyblAAAAHElEQVQI'+ '12P4//8/w38GIAXDIBKE0DHxgljNBAAO9TXL0Y4OHwAAAABJRU5ErkJggg==' -- red dot, PNG DECLARE @bin VARBINARY(MAX) DECLARE @base64_2 NVARCHAR(MAX) SET @bin = (SELECT CAST(N'' AS XML).value('xs:base64Binary(sql:variable(''@base64''))','VARBINARY(MAX)')) SET @base64_2 = (SELECT CAST(N'' AS XML).value('xs:base64Binary(sql:variable(''@bin''))','NVARCHAR(MAX)')) SELECT @bin AS '@bin', @base64_2 AS '@base64_2', CASE WHEN @base64=@base64_2 THEN 1 ELSE 0 END AS 'match'

Here we set @bin to be the Base64-decoded version of @base64 , which is the PNG image data. We then set @base64_2 to the Base64-encoded version of @bin . In the results of the SELECT query, you can see the brief representation of the binary and Base64 data from our variables, as well as the result of our match whichs shows that @base64 and @base64_2 are identical.

10 Putting it all together

Let’s create a working example using the topics covered in this tutorial. We will create a view that tackles some of the problems discussed and a cached table to provide faster access to the results. Next, we’ll write a stored procedure to update the cached table, wrapping it in a try/catch statement so we can send an HTML email if there is an error.

10.1 Adding images

Let’s add some images to the database that we can use in our HTML email. We will use the Base64 conversion method to populate the VARBINARY column in the images table using this SQL. Our images table should look like this:

SELECT * FROM images

10.2 Query for our view

This is the query to create our view:

CREATE VIEW dbo.cell_phones_with_address AS SELECT people.id, people.first_name, people.last_name, ( SELECT TOP 1 phone_number FROM phones WHERE phones.person_id=people.id AND phones.phone_number_type='cell' AND phone_number IS NOT NULL AND RTRIM(LTRIM(phone_number))!='' ORDER BY phones.id DESC ) AS primary_cell_phone, SUBSTRING(phone_list,1,LEN(phone_list)-1) AS all_cell_phones, addresses.[address], addresses.city, addresses.[state] FROM people LEFT OUTER JOIN addresses ON addresses.id=people.alt_id COLLATE Latin1_General_BIN CROSS APPLY ( SELECT phone_number+', ' FROM phones WHERE person_id=people.id AND phone_number_type = 'cell' AND phone_number IS NOT NULL AND LTRIM(RTRIM(phone_number)) != '' ORDER BY phones.id DESC FOR XML PATH('') ) results (phone_list); GO

10.3 Cached copy of view

Let’s create a cached copy of the view:

CREATE TABLE cached_cell_phones_with_address ( id int NOT NULL, first_name varchar(50) NULL, last_name varchar(50) NULL, primary_cell_phone varchar(50) NULL, all_cell_phones varchar(50) NULL, [address] nvarchar(50) NULL, city nvarchar(50) NULL, [state] nvarchar(50) NULL CONSTRAINT PK_cached_cell_phones_with_address PRIMARY KEY(id) ); GO

Let’s create a stored procedure to update the contents of cached_cell_phones_with_address with the results of the cell_phones_with_address . We will catch any errors and send an email if there is an error:

-- ============================================= -- Author: Caddy, Teddy -- Create date: 2014.10.05 -- Description: Caches cell_phones_with_address view -- ============================================= CREATE PROCEDURE usp_cache_cell_phones_with_address AS BEGIN SET NOCOUNT ON; -- Do a try/catch block to catch errors BEGIN TRY DELETE FROM cached_cell_phones_with_address; INSERT INTO cached_cell_phones_with_address SELECT * FROM cell_phones_with_address END TRY BEGIN CATCH DECLARE @body varchar(max) = '' DECLARE @subject nvarchar(255) = 'Error executing stored procedure - '+(SELECT OBJECT_NAME(@@PROCID)) DECLARE @from_address varchar(max) = 'Sender Name <sender@yourdomain.com>' DECLARE @alert_64 varchar(max) = (SELECT CAST(N'' AS XML).value('xs:base64Binary(sql:column(''data''))','VARBINARY(MAX)') FROM images WHERE name='alert') DECLARE @alert_mime varchar(50) = (SELECT mime FROM images WHERE name='alert') SET @body += '<p>'+ '<img src="data:'+@alert_mime+';base64,'+@alert_64+'" alt="alert"/>'+ CAST(@subject as nvarchar(max))+ '</p>'+ '<p>An error occurred trying to execute a stored procedure.</p>'+ '<p>Action should be taken to fix the cause of the error and re-run the stored procedure.</p>'+ '<p>Details of the error:</p>'+ '<ul>'+ '<li><b>Error Message:</b> '+CAST(ERROR_MESSAGE() as varchar(max))+'</li>'+ '<li><b>Error Number:</b> '+CAST(ERROR_NUMBER() as varchar(max))+'</li>'+ '<li><b>Error Procedure:</b> '+CAST(ERROR_PROCEDURE() as varchar(max))+'</li>'+ '<li><b>Error Line:</b> '+CAST(ERROR_LINE() as varchar(max))+'</li>'+ '</ul>' EXEC msdb.dbo.sp_send_dbmail @recipients='recipient@yourdomain.com', @subject=@subject, @body=@body, @body_format='HTML', @from_address=@from_address, @reply_to=@from_address END CATCH END

We can execute our stored procedure to populate the cached_cell_phones_with_address table:

EXEC usp_cache_cell_phones_with_address

10.5 Creating an error

It might not be evident, but I left the cached_cell_phones_with_address table setup to create an error. The limit for the all_cell_phones column is only 50 characters. We could easily exceed this with the following query:

INSERT INTO phones (person_id,phone_number,phone_number_type) VALUES (2,'A really long entry that will cause an error','cell')

Let’s take a look at the view now:

SELECT * FROM cell_phones_with_address

The entry for the all_cell_phones column for Ned Flanders looks pretty long. We can see how long it really is with this query:

SELECT MAX(LEN(all_cell_phones)) FROM cell_phones_with_address

The column has a maximum size of 64 characters which is going to exceed the limit we set in the cached_cell_phones_with_address table. Let’s try to run the stored procedure, create the error, and check our email.

EXEC usp_cache_cell_phones_with_address

This should have generated an error and an email message to notify you about the error.

We could fix this error by expanding the size of the column in the cached database or by cleaning up the bad data in the phones table. In this case, let’s just remove the bad phone record.

DELETE FROM phones WHERE LEN(phone_number) = ( SELECT MAX(LEN(phone_number)) FROM phones )

After this, we can execute our stored procedure without generating an error email. Perhaps we should extend the size of the all_cell_phones column to a larger size so that the likelihood of this error is reduced.

11 Conclusion

SQL Server is a versatile database engine that can be used in a variety of ways to solve different problems. We saw that in the examples in this tutorial, which go beyond the basic SELECT, INSERT, and DELETE statements. You can achieve a lot of other tasks within the SQL Server database.

SQL Server is a widely adopted technology that you will find in the data centers of many organizations, from large to small. Many organizations make large investments and long term plans centered around SQL Server. Having additional SQL skills is always a good thing to have.

Maybe you have learned something that you did not know SQL Server could do. Whether it is fixing a join to remove duplicate results or notifying an admin of a database error, hopefully this tutorial has been helpful and educational.