Date, time and number data types in SQL Server

The main difference between these two data types is in the amount of space they occupy. datetime occupies 8 bytes and smalldatetime only 4 bytes. Other differences between the two types are the precision of the date stored and the range of dates that can be used. The precision of smalldatetime is one minute, and it covers dates from January 1, 1900, through June 6, 2079, which is usually more than enough. The precision of datetime is 3.33 ms, and it covers dates from January 1, 1753, to December 31, 9999.

Date and time constants are written in Transact-SQL with quote delimiters (as are character strings):

update Contacts_2
Set DateOfBirth = '2/21/1965 10:03 AM'
where ContactId = 'CO-92-81'

TIP
SQL Server supports many different date and time formats. The Convert() function accepts a parameter that controls the format of date and time functions (explained in detail in Chapter 4).

If time is not specified in a constant, SQL Server automatically assigns a default value—12:00 A.M. (midnight). You should keep in mind that SQL Server always records time as a part of these data types. Thus, if you want to select all contacts born on a particular day, you should not use something like this:

select *
from Contacts_2
where DateOfBirth = '2/21/1965'

select *
from Contacts_2
where DateOfBirth >= '2/21/1965' and DateOfBirth < '2/22/1965'

The great thing about the int data types is that they can store huge numbers in a small space. For this reason, they are often used for key values. If the data type of the primary key is int, the table can store up to four billion records, which is typically enough for any purpose. Therefore, if you are not sure about the size of your table, use int as its primary key.

TIP
However, we are starting to see computers with billions of records—both OLTP and data warehousing systems are getting bigger and bigger, and there are also some implementations of distributed databases that can use integers higher than two billion. In those cases you could use bigint for primary keys.

Integer constants do not need delimiters:

update Inventory
Set StatusId = 3,
    Operational = 0
Where InventoryId = 3432

Approximate Numbers
Decimal numbers are often stored in real and float data types, also known as single and double precision. Their advantage is that they do not occupy much space but they can hold large ranges of numbers. The only trouble is that they are not exact. They store a binary representation of the number that is often approximately, but not exactly, equal to the original decimal number.

Precision is the number of significant digits in the number, and scale is the number of digits to the right of the decimal point. For example, the number 123456.789 has a precision of 9 and a scale of 3. The precision of real numbers is up to 7 digits, and the precision of float numbers is up to 15 digits. For this reason, they are ideal for science and engineering (where, for example, you may not care about a couple of meters when you are measuring the distance between the Earth and the Moon), but they are not adequate for the financial industry (where a company budget has to be exact to the last cent). To record the number 234,000,000,000 in mathematics, you can use 234×109, and in Transact-SQL, you can use 234E9. This is known as scientific notation. The number after E is called the exponent, and the number before E is called the mantissa. This notation can be used to store small constants, too. In mathematics, 0.000000000234 can be written as 0.234×10–9, and in Transact-SQL, it can be written as 0.234E-9.

SQL Server uses the IEEE 754 standard to store these numbers. When a float or real variable or column is assigned a number, SQL Server first converts the decimal number to its binary representation. This conversion is the reason these values are approximately, but not exactly, equal to the decimal version. This is why they are referred to as approximate numbers. Therefore, you should not rely on the equivalence of two such numbers. You should limit their use in Where clauses to < and > operators and avoid the use of the = operator.

Exact Numbers
The decimal or numeric data type does not use approximations when storing numbers. Unfortunately, it requires much more space than the real and float data types. When a decimal column or a variable is defined, you have to specify its scale and precision.
SQL Server can store decimal numbers with a maximum precision of 38. Scale can be less than or equal to the precision.
In the next example, Weight and Height columns have precision 5 and scale 2—the columns can have up to two digits after the decimal point and up to three digits before.

Create table Patient (PatientId int,
                      FullName varchar(30),
                      Weight decimal(5,2),
                      Height decimal(5,2),
                      ADP smallint,
                      BDZ tinyint)
go

insert into Patient (PatientId, FullName, Weight, Height, ADP, BDZ)
values (834021, 'Tom Jones', 89.5, 188.5, 450, 11)

Monetary Data Types
The money and smallmoney data types are a compromise between the precision of decimal numbers and the small size of real numbers. smallmoney occupies 4 bytes and uses the same internal structure as int numbers. The data can have up to four digits after the decimal point. For this reason, you can store numbers ranging from –214,768.3648 to 214,768.3647 in the smallmoney data type. The money data type uses the same structure for storing information as the bigint data type. It occupies 8 bytes for storage, so its values must range from –922,337,203,685,477.5808 to +922,337,203,685,477.5807.
Monetary constants can be preceded by $ or one of 18 other currency symbols (listed in SQL Server Books OnLine):

update Inventory_2
Set Rent = $0,
LeaseCost = $119.95
Where InventoryId = 3432

Binary Data Types
Binary data types are used to store strings of bits. SQL Server supports four basic binary data types, the attributes of which are similar to character data types:

The binary and varbinary data types can store up to 8,000 bytes of information, and image and varbinary (max) can store up to 2GB of data. The following example creates a table that has two binary columns:

CREATE TABLE MyTable (
     Id int,
     BinData varbinary(8000),
     Diagram varbinary(max))
go

Update MyTable
Set BinData = 0x82A7210B
where Id = 121131

UPDATE dbo.MyTable
SET Diagram = (SELECT * 
               FROM OPENROWSET(BULK 'C:My Picturesdesktop.bmp', 
                               SINGLE_BLOB) AS a )
where Id = 121131

Basic Transact-SQL Programming Constructs

 Home: Introduction
 Tip 1: T-SQL identifiers
 Tip 2: Database object qualifiers
 Tip 3: Character string data types
 Tip4: Date, time and number data types
 Tip 5: Special data types-Part 1
 Tip 6: Special data types-Part 2
 Tip 7: Local variables
 Tip 8: Global variables
 Tip 9: Table variables
 Tip 10: Flow control statements
 Tip 11: Blocks, looping and conditional statements
 Tip 12: Unconditional and scheduled execution
 Tip 13: Transact SQL cursors
 Tip 14: Cursor related statements
 Tip 15: Problems and justified use of cursors

The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL/Transact SQL (T-SQL) SQL Server data conversions from date/time values to character types SQL and SQL Server Tutorial and Reference Guide How to use the SELECT statement in SQL Translating information requests into SQL SELECT statements SQL SELECT statement and SELECT query samples Using the ORDER BY clause of the SELECT query in SQL Using DISTINCT in SQL to eliminate duplicate rows Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005 How to configure Database Mail in SQL Server 2005 to send mail SQL Server stored procedures tutorial: Write, tune and get examples SQL/Transact SQL (T-SQL) Research SQL Server stored procedures SQL and SQL Server Tutorial and Reference Guide SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure Track changes to SQL Server 2000 and 2005 with one simple utility Troubleshoot SQL Server 2005 temporary table performance problems Use SQL Profiler to find long running stored procedures and commands Stored procedure to monitor long-running jobs in SQL Server 2000 XML in SQL Server Manage traces in SQL Server 2005 Analysis Services with XMLA commands Create a computed column in SQL Server using XML data Create DDL table in SQL Server 2005 to audit DDL trigger activity Retrieve XML data values with XQuery in SQL Server 2005 XML data type in SQL Server 2005 vs. VARCHAR (MAX) SQL Server Blog Watch T-SQL commands vs. XML AUTO in SQL Server Basic Transact-SQL programming constructs: 15 tips, 15 minutes T-SQL identifiers Special data types-Part 1

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) commit  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary