tinyint

• Domain: Non-negative whole numbers from 0 through 255.
• Storage: 1 byte.
• Discussion:
• tinyints are used to store small non-negative integer values. When using a single byte for storage, if the values you’ll be dealing with are guaranteed always to be in this range, a tinyint is perfect. A great use for this is for the primary key of a domain table that can be guaranteed to have only a couple values. The tinyint datatype is especially useful in a data warehouse to keep the surrogate keys small. However, you have to make sure that there will never be more than 256 values, so unless the need for performance is incredibly great (such as if the key will migrate to tables with billions and billions of rows), it’s best to use a larger datatype.
• Row Compression Effect:
• No effect, because 1 byte is the minimum for the integer types other than bit.

smallint

• Domain: Whole numbers from -32,768 through 32,767 (or -2 ¹⁵ through 2 ¹⁵ – 1).
• Storage: 2 bytes.
• Discussion:
• If you can be guaranteed to need values only in this range, the smallint can be a useful type. It requires 2 bytes of storage.
• One use of a smallint that crops up from time to time is as a Boolean. This is because, in earlier versions of Visual Basic, 0 equaled False and -1 equaled True (technically, VB would treat any nonzero value as True, but it used -1 for its representation of False). Storing data in this manner is not only a tremendous waste of space—2 bytes versus potentially 1/8th of a byte for a bit, or even a single byte for a char(1)—'Y' or 'N'. It’s also confusing to all the other SQL Server programmers. ODBC and OLE DB drivers do this translation for you, but even if they didn’t, it’s worth the time to write a method or a function in VB to translate True to a value of 1.
• Row Compression Effect:
• The value will be stored in the smallest number of bytes required to represent the value. For example, if the value is 10, it would fit in a single byte; then it would use 1 byte, and so forth, up to 2 bytes.

int

• Domain: Whole numbers from -2,147,483,648 to 2,147,483,647 (that is, –2 ³¹ to 2 ³¹ – 1).
• Storage: 4 bytes.
• Discussion:
• The integer datatype is frequently employed as a primary key for tables, because it’s small (it requires 4 bytes of storage) and efficient to store and retrieve.
• One downfall of the int datatype is that it doesn’t include an unsigned version, which for a 32-bit version could store non-negative values from 0 to 4,294,967,296 (2 ³²). Because most ­primary key values start out at 1, this would give you more than 2 billion extra values for a primary key value without having to involve negative numbers that can be confusing to the user. This might seem unnecessary, but systems that have billions of rows are becoming more and more common.
• An application where the storage of the int column plays an important part is the storage of IP addresses as integers. An IP address is simply a 32-bit integer broken down into four octets. For example, if you had an IP address of 234.23.45.123, you would take (234 * 2 ³) + (23 * 2²) + (45 * 2¹) + (123 * 2⁰). This value fits nicely into an unsigned 32-bit integer, but not into a signed one. However, the 64-bit integer (bigint, which is covered next) in SQL Server covers the current IP address standard nicely but requires twice as much storage. Of course, bigint will fall down in the same manner when we get to IPv6 (the forthcoming Internet addressing protocol), because it uses a full 64-bit unsigned integer.
• Row Compression Effect:
• The value will be stored in the smallest number of bytes required to represent the value. For example, if the value is 10, it would fit in a single byte; then it would use 1 byte, and so forth, up to 4 bytes.

bigint

• Domain: Whole numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (that is, –2 ⁶³ to 2 ⁶³ – 1).
• Storage: 8 bytes.
• Discussion:
• One of the common reasons to use the 64-bit datatype is as a primary key for tables where you’ll have more than 2 billion rows, or if the situation directly dictates it, such as the IP address situation I previously discussed. Of course, there are some companies where a billion isn’t really a very large number of things to store or count, so using a bigint will be commonplace to them. As usual, the important thing is to size your utilization of any type to the situation, not using too small or even too large of a type than is necessary.
• Row Compression Effect:
• The value will be stored in the smallest number of bytes required to represent the value. For example, if the value is 10, it would fit in a single byte; then it would use 1 byte, and so forth, up to 8 bytes.

decimal (alias: numeric)

• Domain: All numeric data (including fractional parts) between –10 ³⁸ + 1 through 10 ³⁸ – 1.
• Storage: Based on precision (the number of significant digits): 1–9 digits, 5 bytes; 10–19 digits, 9 bytes; 20–28 digits, 13 bytes; and 29–38 digits, 17 bytes.
• Discussion:
• The decimal datatype is a precise datatype because it’s stored in a manner that’s like character data (as if the data had only 12 characters, 0 to 9 and the minus and decimal point symbols). The way it’s stored prevents the kind of imprecision you’ll see with the float and real datatypes a bit later. However, decimal does incur an additional cost in getting and doing math on the values, because there’s no hardware to do the mathematics.
• To specify a decimal number, you need to define the precision and the scale:

• Precision is the total number of significant digits in the number. For example, 10 would need a precision of 2, and 43.00000004 would need a precision of 10. The precision may be as small as 1 or as large as 38.
• Scale is the possible number of significant digits to the right of the decimal point. Reusing the previous example, 10 would require a scale of 0, and 43.00000004 would need 8.

• Numeric datatypes are bound by this precision and scale to define how large the data is. For example, take the following declaration of a numeric variable:

   DECLARE @testvar decimal(3,1)

• This allows you to enter any numeric values greater than -99.94 and less than 99.94. Entering 99.949999 works, but entering 99.95 doesn’t, because it’s rounded up to 100.0, which can’t be displayed by decimal(3,1). Take the following, for example:

SELECT @testvar = -10.155555555;

SELECT @testvar;

• This returns the following result:

• This rounding behavior is both a blessing and a curse. You must be careful when butting up to the edge of the datatype’s allowable values. There is a setting—SET NUMERIC_ROUNDABORT ON— that causes an error to be generated when a loss of precision would occur from an implicit data conversion. That’s kind of like what happens when you try to put too many characters into a character value.
• Take the following code:

   SET NUMERIC_ROUNDABORT ON;

   DECLARE @testvar decimal(3,1);

   SELECT @testvar = -10.155555555;

   SET NUMERIC_ROUNDABORT OFF;--this setting persists for a connection

• This causes the following error:

• SET NUMERIC_ROUNDABORT can be quite dangerous to use and might throw off applications using SQL Server if set to ON. However, if you need guaranteed prevention of implicit round-off due to system constraints, it’s there.

As far as usage is concerned, you should generally use the decimal datatype as sparingly as possible, and I don’t mean this negatively. There’s nothing wrong with the type at all, but it does take that little bit more processing than integers or real data, and hence there’s a performance hit. You should use it when you have specific values that you want to store where you can’t accept any loss of precision. Again, I’ll deal with the topic of loss of precision in more detail in the section “Approximate Numeric Data.” The decimal type is commonly used as a replacement for the money type, because it has certain round-off issues that decimal does not.

• Row Compression Effect:
• The value will be stored in the smallest number of bytes that are necessary to provide the precision necessary, plus 2 bytes overhead per row. For example, if you are storing the value of 2 in a numeric(28,2) column, it needn’t use all the possible space; it can use the space of a numeric(3,2), plus the 2 bytes overhead.

Money Types

There are two intrinsic datatypes that are for storing monetary values. Both are based on integer types, with a fixed four decimal places. These types are as follows:

• money

• Domain: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
• Storage: 8 bytes

• smallmoney

• Domain: -214,748.3648 to 214,748.3647
• Storage: 4 bytes

The money datatypes are generally considered a poor choice of datatype, even for storing monetary values, because they have a few inconsistencies that can cause a good deal of confusion. First, you can specify units, such as $ or £, but the units are of no real value. For example:

CREATE TABLE dbo.testMoney

(

 moneyValue money

);

go

INSERT INTO dbo.testMoney

VALUES ($100);

INSERT INTO dbo.testMoney

VALUES (100);

INSERT INTO dbo.testMoney

VALUES (£100);

GO

SELECT * FROM dbo.testMoney;

The query at the end of this code example returns the following results (each having the exact same value.):

The second problem is that the money datatypes have well-known rounding issues with math. I mentioned that these types are based on integers (the range for smallmoney is -214,748.3648 to 214,748.3647, and the range for an integer is 2,147,483,648 to 2,147,483,647). Unfortunately, as I will demonstrate, intermediate results are stored in the same types, causing unexpected rounding errors. For example:

DECLARE @money1 money = 1.00,

        @money2 money = 800.00;

SELECT CAST(@money1/@money2 as money);

This returns the following result:

However, try the following code:

DECLARE @decimal1 decimal(19,4) = 1.00,

      , @decimal2 decimal(19,4) = 800.00;

SELECT CAST(@decimal1/@decimal2 as decimal(19,4));

It returns the following result:

Why? Because money uses only four decimal places for intermediate results, where decimal uses a much larger precision:

SELECT @money1/@money2;

SELECT @decimal1/@decimal2;

This code returns the following results:

That’s why there are round-off issues. And if you turned SET NUMERIC_ROUNDABORT ON, the decimal example would fail, telling you that you were losing precision, whereas there is no way to stop the roundoff from occurring with the money types. The common consensus among database architects is to avoid the money datatype and use a numeric type instead, because of the following reasons:

• Numeric types give the answers to math problems in the natural manner that’s expected.
• Numeric types have no built-in units to confuse matters.

Even in the previous version of SQL Server, the following statement was included in the monetary data section: “If a greater number of decimal places are required, use the decimal datatype instead.” Using a decimal type instead gives you the precision needed. To replicate the range for money, use DECIMAL(19,4), or for smallmoney, use DECIMAL(10,4). However, you needn’t use such large values if you don’t need them. If you happen to be calculating the national debt or my yearly gadget allowance, you might need to use a larger value.

• Row Compression Effect:
• The money types are simply integer types with their decimal places shifted. As such, they are compressed in the same manner that integer types would be. However, since the values would be larger than they appear (because of the value 10 being stored as 10.000, or 10000 in the physical storage), the compression would be less than for an integer of the same magnitude.

Date Functions

With the creation of new date and time (and datetime) types back in SQL Server 2008, there needed to be more functions to work with. Microsoft has added functions that return and modify dates and time with more precision than GETDATE or GETUTCDATE:

• SYSDATETIME: Returns system time to the nearest fraction of a second, with seven places of scale in the return value
• SYSDATETIMEOFFSET: Same as SYSDATETIME but returns the offset of the server in the return value
• SYSUTCDATETIME: Same as SYSDATETIME but returns the UTC date time rather than local time
• SWITCHOFFSET: Changes the offset for a datetimeoffset value
• TODATETIMEOFFSET: Converts a local date and time value to a given time zone

There have been a few changes to the basic date functions as well:

• DATENAME: Includes values for microsecond, nanosecond, and TZoffset. These will work differently for the different types. For example, TZoffset will work with datetime2 and datetimeoffset, but not the other types.
• DATEPART: Includes microsecond, nanosecond, TZoffset, and ISO_WEEK. ISO_WEEK is a feature that has been long desired by programmers who need to know the week of the year, rather than the nonstandard week value that SQL Server has previously provided.
• DATEADD: Supports micro and nanoseconds.

With all the date functions, you really have to be careful that you are cognizant of what you are requesting. For an example, how old is this person? Say you know that a person was born on December 31, 2008, and on January 3, 2009, you want to know their age. Common sense says to look for a function to take the difference between two dates. You are in luck—there is a DATEDIFF function. Executing the following:

DECLARE @time1 date = '20111231',

        @time2 date = '20120102';

SELECT DATEDIFF(yy,@time1,@time2);

You see that the person is 0 years old right? Wrong! It returns 1.Ok, so if that is true, then the following should probably return 2, right?

DECLARE @time1 date = '20110101',

        @time2 date = '20121231';

SELECT DATEDIFF(yy,@time1,@time2);

That also returns 1. So, no matter whether the date values are 1 day apart or 730, you get the same result? Yes, because the DATEDIFF function is fairly dumb in that it is taking the difference of the year value, not the difference in years. Then, to find out the age, you will need to use several functions.

So, what is the answer? We could do something fancier, likely by coming up with an algorithm based on the number of days, or months, or shifting dates to some common value, but that is way more like work than the really straightforward answer. Build a table of dates, commonly called a calendar (see Chapter 12).

Date Ranges

This topic will probably seem really elementary, but the fact is that one of the largest blunders in the database implementation world is working with ranges of dates. The problem is that when you want to do inclusive ranges, you have always needed to consider the time in the equation. For example, the following criteria:

WHERE pointInTimeValue BETWEEN '2012-01-01' AND '2012-12-31'

means something different based on whether the values stored in pointInTimeValue have, or do not have, a time part stored. With the introduction of the date type, the need to worry about this issue of date ranges may eventually become a thing of the past, but it is still an issue that you’ll always need to worry about whether you need to worry about it.

The problem is that any value with the same date as the end value plus a time (such as '2012-12-31 12:00:00') does not fit within the preceding selecting criteria. Because you will miss all the activity that occurred on December 31 that wasn’t at midnight.

There are two ways to deal with this. Either code your WHERE clause like this:

WHERE pointInTimeValue > = '2012-01-01' and pointInTimeValue < '2013-01-01'

or use a calculated column to translate point-in-time values in your tables to date-only values (like dateValue as cast(pointInTimeValue as date)). Many times the date value will come in handy for grouping activity by day as well. Having done that, a value such as '2012-12-31 12:00:00' will be truncated to '2012-12-31 00:00:00', and a row containing that value will be picked up by selection criteria such as this:

WHERE pointInTimeValue BETWEEN '2012-01-01' AND '2012-12-31'

A common solution that I don’t generally suggest is to use a between range like this:

WHERE pointInTimeValue BETWEEN '2012-01-01' AND '2012-12-31 23:59:59.9999999'

The idea is that if the second value is less than the next day, values for the next day won’t be returned. The major problem with this solution has to do with the conversion of 23:59:59.9999999 to one of the various date datatypes. Each of the types will round up, so you must match the number of fractional parts to the precision of the type. For datetime2(3), you would need 23:59:59.999. If the pointInTimeValue column was of type: datetime, you would need use this:

WHERE pointInTimeValue BETWEEN '2009-01-01' AND '2009-12-31 23:59:59.997'

However, for a smalldatetime value, it would need to be this:

WHERE pointInTimeValue BETWEEN '2009-01-01' AND '2009-12-31 23:59'

and so on, for all of the different date types, which gets complicated by the new types where you can specify precision. I strongly suggest you avoid trying to use a maximum date value like this unless you are tremendously careful with the types of data and how their values round off.

For more information about how date and time data work with one another and converting from one type to another, read the topic “Using Date and Time Data” in Books Online.

Representing Dates in Text Formats

When working with date values in text, using a standard format is always best. There are many different formats used around the world for dates, most confusingly MMDDYYYY and DDMMYYYY (is 01022004 or 02012004 the same day, or a different day?). Although SQL Server uses the locale information on your server to decide how to interpret your date input, using one of the following formats ensures that SQL Server doesn’t mistake the input regardless of where the value is entered.

Generally speaking, it is best to stick with one of the standard date formats that are recognized regardless of where the user is. This prevents any issues when sharing data with international clients, or even with sharing it with others on the Web when looking for help.

There are several standards formats that will work:

• ANSI SQL Standard

• No time zone offset: 'YYYY-MM-DD HH:MM:SS'
• With time zone: 'YYYY-MM-DD HH:MM:SS –OH:OM' (Z, for Zulu, can be used to indicate the time zone is the base time of 00:00 offset, otherwise known as GMT [Greenwich Mean Time] or the most standard/modern name is UTC [Coordinated Universal Time].)

• ISO 8601

• Unseparated: 'YYYYMMDD'
• Numeric: 'YYYY-MM-DD'
• Time: 'HH:MM:SS.sssssss' (SS and .sssssss are optional)
• Date and time: 'YYYY-MM-DDTHH:MM:SS.sssssss'
• Date and time with offset: 'YYYY-MM-DDTHH:MM:SS.sssssss -OH:OM'

• ODBC

• Date: {d 'YYYY-MM-DD'}
• Time: {t 'HH:MM:SS'}
• Date and time: {ts 'YYYY-MM-DD HH:MM:SS'}

Using the ANSI SQL Standard or the ISO 8601 formats is generally considered the best practice for specifying date values. It will definitely feel odd when you first begin typing '2008-08-09' for a date value, but once you get used to it, it will feel natural.

The following are some examples using the ANSI and ISO formats:

SELECT CAST('2013-01-01' as date) AS dateOnly;

SELECT CAST('2013-01-01 14:23:00.003' AS datetime) as withTime;

You might also see values that are close to this format, such as the following:

SELECT CAST ('20130101' as date) AS dateOnly;

SELECT CAST('2013-01-01 T14:23:00.120' AS datetime) AS withTime;

For more information, check SQL Server 2012 Books Online under “Date and Time Format” Related to dates, a new function FORMAT has been added to SQL Server 2012 that will help you output dates in any format you need to. As a very brief example, consider the following code snippet:

DECLARE @DateValue datetime2(3) = '2012-05-21 15:45:01.456'

SELECT @DateValue as Unformatted,

       FORMAT(@DateValue,'yyyyMMdd') as IsoUnseperated,

       FORMAT(@DateValue,'yyyy-MM-ddThh:mm:ss') as IsoDateTime,

       FORMAT(@DateValue,'D','en-US' ) as USRegional,

       FORMAT(@DateValue,'D','en-GB' ) as GBRegional,

       FORMAT(@DateValue,'D','fr-fr' ) as FRRegional;