Chapter 20. Why every SQL developer needs a tools database
SQL isn’t an object-oriented language. There’s no notion of inheritance. The closest thing that SQL has to objects are views, user-defined functions, and stored procedures. Picture a developer at a software shop; this developer has written a distance calculation algorithm in SQL. Other developers copied this same code for use in their projects. After some time, the original developer finds a small defect in the code he wrote. He updates his code and contacts the other developers so that they can make the modification in their code.
This approach has a few problems; here are three of them:
- The original developer could forget to contact another developer to make the code change.
- A lot more people have to make changes now; this will increase the chance of mistakes.
- All the other developers have to update and test their code to make sure it works as expected.
As you can imagine, it’s much easier to change and test the code in one place. This is the primary reason you need a tools database. Ideally, the tools database should have loosely coupled code and data; it shouldn’t have data and code that depend on another user-created database. To give you an example, the tools database shouldn’t format dates based on a calendar table from the human resources database; the calendar table should be stored in the tools database itself.
What belongs in the tools database?
The following are examples of what should go in a tools database:
- ZIP code and address tables
- Auxiliary table of numbers
- Maintenance procedures for the database server
- Reporting procedures showing connected users, free space, or file sizes
- ISO country and currency code tables
- Region- and country-specific calendar tables
Creating the tools database
Begin by creating the following database on your server:
CREATE DATABASE Tools
GO
USE Tools
GO
Using an auxiliary table of numbers
A numbers table can be useful for a variety of reasons. You can create result sets on the fly without having to store the data itself. Using a numbers table, you can also do set-based operations; this will speed up some operations dramatically because you aren’t looping anymore. To find gaps in identity values, all you need to do is left-join your table with a numbers table and select the rows that have no value. Splitting off strings can also be accomplished fairly easy with a numbers table, as you’ll see later on.
How big should a numbers table be? To store a million numbers in a numbers table, you’ll need about 13 megabytes. If you’re just doing date ranges and splitting off strings, then 10,000 rows might be enough; if you need to find identity gaps, then you need more rows than the maximum value in your identity column.
Let’s start by creating our numbers table. We do this by creating an empty table, into which we insert 251 rows with values between 0 and 250 with the SQL script in listing 1.
Listing 1. Script to create a numbers table
CREATE TABLE Numbers(number int primary key not null)
GO
DECLARE @Loop int
SET @Loop = 0
SET NOCOUNT ON
WHILE @Loop <=250
BEGIN
INSERT Numbers(number) VALUES (@Loop)
SET @Loop = @Loop + 1
END
GO
Generating a calendar on the fly
Let’s look at how you can use a numbers table to create a result set of dates. When working with dates and number tables, you’ll use the DATEADD function. The syntax for DATEADD function looks like this: DATEADD ( datepart , number, date ).
The first argument is datepart; if we use mm for datepart, we’re telling the function to use months. The second argument is number; this tells the function what to add to the date. You can also pass in negative values for subtraction.
The third argument is date; this is a valid date or something that can be converted to a date. To see how this works, run the following piece of code:
SELECT DATEADD(mm,1,'20090501')
(Result set)
2009-06-01 00:00:00.000
As you can see after passing a value of 1 for the number argument, the date was incremented by a month. If we pass in a negative number, we’ll get a date that’s a month earlier than we passed in, as the following code demonstrates:
SELECT DATEADD(mm,-1,'20090501')
(Result set)
2009-04-01 00:00:00.000
Now it’s time to use our numbers table to create some dates. The query in listing 2 will add one month multiplied by the number in the numbers table to today’s date and return the next 100 months, beginning with today’s date.
Listing 2. Query to create dates from the numbers table
SELECT DATEADD(mm,number,CONVERT(varchar(8),GETDATE(),112))
FROM dbo.Numbers
WHERE number < 100
ORDER BY number
The query creates the dates in the result set in listing 3
Listing 3. Abridged result set of dates created from the numbers table.
2008-10-29 00:00:00.000
2008-11-29 00:00:00.000
2008-12-29 00:00:00.000
2009-01-29 00:00:00.000
....
....
2017-01-29 00:00:00.000
If you add a minus sign in front of number, it’ll go back in time. As shown in listing 4, we can use the minus sign before a number to go back in time.
Listing 4. Query to create dates in the past from the numbers table
SELECT DATEADD(mm,-number,CONVERT(varchar(8),GETDATE(),112))
FROM dbo.Numbers
WHERE number < 100
ORDER BY number
The query creates the dates in the result set in listing 5.
Listing 5. Abridged result set of dates created in the past from the numbers table
2008-10-29 00:00:00.000
2008-09-29 00:00:00.000
2008-08-29 00:00:00.000
....
....
2000-07-29 00:00:00.000
The query in listing 6 will return the first and last day of every quarter from January, 2000, until December, 2024.
Listing 6. Query to return the first and last day of every quarter from 2000 to 2024
DECLARE @Date datetime
SELECT @Date = '2000-01-01 00:00:00.000'
SELECT DATEADD(qq,number,@Date),DATEADD(qq,number+1,@Date)-1
FROM dbo.Numbers
WHERE number < 100
The result set of the query is listed in listing 7.
Listing 7. Abridged result set of query in listing 6
2000-01-01 00:00:00.000 2000-03-31 00:00:00.000
2000-04-01 00:00:00.000 2000-06-30 00:00:00.000
2000-07-01 00:00:00.000 2000-09-30 00:00:00.000
....
....
2024-10-01 00:00:00.000 2024-12-31 00:00:00.000
Splitting strings with a numbers table
Numbers tables are also handy if you need to split delimited strings. Take, for example, the following string: 'Z,X,A,B,D,F,Z,Z,Z,Z,A,V,S,Q,L,B,B,B,B,B'. You want to get all the characters in that string without duplicates. This is easily accomplished with a numbers table. First, create the stored procedure in listing 8.
Listing 8. Stored procedure to split delimited strings with the numbers table
CREATE PROCEDURE SplitString
@StringToSplit varchar(1000),
@Delimiter varchar(10)
AS
SELECT DISTINCT SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, number + 1,
CHARINDEX(@Delimiter, @Delimiter + @StringToSplit + @Delimiter, number + 1) - number -1) As StringItem
FROM Numbers
WHERE number <= LEN(@Delimiter + @StringToSplit + @Delimiter) - 1
AND SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, number, 1) = @Delimiter
ORDER BY StringItem
GO
Here’s how you’d call that stored procedure with a string delimited with commas:
EXEC SplitString 'Z,X,A,B,D,F,Z,Z,Z,Z,A,V,S,Q,L,B,B,B,B,B',','
The result set of unique characters in a string is shown in listing 9.
Listing 9. Result set of unique characters in a string
A
B
D
F
L
Q
S
V
X
Z
Here’s an example with a pipe symbol as delimiter:
EXEC SplitString 'Z|X|A', '|'
(Result set)
A
X
Z
The same code from the stored procedure, but with comments explaining how it works, is shown in listing 10.
Listing 10. Stored procedure with comments
-- This will hold the delimited string
DECLARE @StringToSplit varchar(1000)
SELECT @StringToSplit ='Z|X|A'
-- This is the delimiter
DECLARE @Delimiter varchar(10)
SELECT @Delimiter= '|'
-- Return unique values
SELECT DISTINCT
-- Add the delimiters to the string and add 1 to the start position
SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, number + 1,
-- Until you find the next delimiter
CHARINDEX(@Delimiter, @Delimiter + @StringToSplit + @Delimiter, number + 1) - number -1) As StringItem
-- Use the numbers table to loop
FROM Numbers
-- Keep going until you arrive at the end of the string
WHERE number <= LEN(@Delimiter + @StringToSplit + @Delimiter) - 1
-- Return only positions between delimiters
AND SUBSTRING(@Delimiter + @StringToSplit + @Delimiter, number, 1) = @Delimiter
ORDER BY StringItem
As you can see, I created a stored procedure and made it accept different delimiters; this provides flexibility and also one codebase. I don’t need to have a stored procedure for every delimiter that can possible be used.
Placing common code in the tools database
Common code is code that typically can be written and consumed only one way. Converting from Celsius to Fahrenheit, converting from miles to kilometers, and calculating sales tax are some examples.
Let’s look at a sales tax calculation example; each state will have a current tax rate and a previous tax rate in a table. The row where the EndDate is NULL is the current tax rate; the row where the EndDate isn’t NULL is a previous tax rate. When we add a new tax rate, we simply update the row where the EndDate column has a NULL value with the current date and insert a new row with a NULL value for the EndDate.
Create the StateTaxRates table in listing 11 and insert four rows for state tax rates.
Listing 11. Table for state tax rates
CREATE TABLE StateTaxRates(StateCode char(2) NOT NULL,
StartDate datetime NOT NULL,
EndDate datetime,
TaxRate decimal(4,4) NOT NULL)
GO
INSERT StateTaxRates VALUES('NJ','20010101','20070101',.07)
INSERT StateTaxRates VALUES('NJ','20070102',NULL,.08)
INSERT StateTaxRates VALUES('CA','20010101','20080101',.0825)
INSERT StateTaxRates VALUES('CA','20080102',NULL,0.09)
The user-defined function that calculates the tax is shown in listing 12.
Listing 12. User-defined function to calculate tax
CREATE FUNCTION CalculateStateTax(@Value decimal(20,8),@StateCode char(2),@Date datetime)
RETURNS decimal(20,4)
AS
BEGIN
DECLARE @TaxRate decimal(4,4)
--Grab latest tax rate
IF @Date IS NULL
BEGIN
SELECT @TaxRate = TaxRate
FROM StateTaxRates
WHERE StateCode = @StateCode
AND EndDate IS NULL
END
ELSE
--Grab tax rate for a specific day
BEGIN
SELECT @TaxRate = TaxRate
FROM StateTaxRates
WHERE StateCode = @StateCode
AND @Date >= StartDate
AND @Date < EndDate
END
--Do the calculation by multiplying the tax with the amount
RETURN @Value * @TaxRate
END
GO
Four example calls and their results are shown in listing 13.
Listing 13. Four example calls and their results
SELECT dbo.CalculateStateTax(100,'NJ',null)
(Result set)
8.0000
SELECT dbo.CalculateStateTax(100,'NJ','20020101')
(Result set)
7.0000
SELECT dbo.CalculateStateTax(10000,'CA',null)
(Result set)
900.0000
SELECT dbo.CalculateStateTax(100000,'CA','20020101')
(Result set)
8250.0000
Formatting
In general, formatting should be done in the presentation layer, but sometimes you need to generate a file and the recipient needs certain values to be in a specific format. Take a date for example; what if you want a date in the format YYYY-MM-DD? SQL Server has a bunch of formats built in, but it doesn’t have that one. Create the function in listing 14.
Listing 14. Function to format a date
CREATE FUNCTION FormatDateDash(@Date datetime)
RETURNS varchar(10)
AS
BEGIN
RETURN CONVERT(varchar(10),@Date,120)
END
GO
Now call it like this:
SELECT dbo.FormatDateDash(getdate()),dbo.FormatDateDash('20010101')
(Result set)
2008-10-29 2001-01-01
Calling code from a different database
In order to call code that resides in a different database, you’ll need to use three-part notation. Three-part notation looks like this:
DatabaseName.Schema.Object
A stored procedure named SplitString exists in the tools database within the dbo schema. In order to execute this stored procedure from the temp database, your code would look like this:
USE tempdb
GO
EXEC Tools.dbo.SplitString 'Z|X|A', '|'
GO
You need to have appropriate permissions in order to call objects in a database from another database!
Summary
Hopefully this chapter has given you an idea of how a tools database can help you save time and headaches. Next time when you have to make code changes in several places to fix a bug or make a change request, consider a tools database: it’ll make your life easier.
About the author
Denis Gobo resides in Princeton, New Jersey, with his wife and three kids. For the last four years, Denis has been working for Dow Jones, where his task is to optimize the storage and retrieval of a good amount of data; most of this data is stored in SQL Server. Denis is a cofounder of http://lessthandot.com, a community site for tech professionals, where he also blogs and answers questions in the forums. In his free time, Denis likes to read, watch horror movies, and spend time with his family.