After Hours

10+ common questions about SQL Server data types

SQL Server has a variety of data types, and as with most things, the more options you have, the more confusing a choice can be. Susan Harkins offers answers to the questions she hears most often about using SQL Server data types.

SQL Server has a variety of data types, and as with anything, the more options you have, the more confusing a choice can be. Most misunderstandings arise from data type limitations rather than functionality. Here are the most common questions I receive about using SQL Server data types.

Note: This information is also available as a PDF download.

#1: Which character data type should I use?

Use character data types to store values you don't evaluate in mathematical equations, even if the data consists of numeric characters. For instance, use a character data type to store names, addresses, ZIP codes, and phone numbers. SQL Server offers several character data types and deciding which to apply is confusing only if you don't know the differences between them. Table A gives a quick comparison of char, varchar, nchar, and nvarchar.

Table A: Character data types

Data Type Length Storage Size Max Characters Unicode
char Fixed Always n bytes 8,000 No; each character requires 1 byte
varchar Variable Actual length of entry in bytes 8,000 No; each character requires 1 byte
nchar Fixed Twice n bytes 4,000 Yes; each character requires 2 bytes
nvarchar Variable Twice actual length of entry in bytes 4,000 Yes; each character requires 2 bytes

Here are a few general rules that should help:

  • Don't use nchar or nvarchar unless you truly need it. (Unicode provides a unique number for up to 65,536 characters. ANSI, the one most of us are most familiar with, has only 256.) Unless you're working with an international application, you probably don't need a Unicode data type.
  • Use the smallest data type necessary, but make sure it can accommodate the largest possible value.
  • Use a fixed-length data type when the values are mostly about the same size.
  • Use a variable length when the values vary a lot in size.

#2: Which integer type should I use?

Use integer data types to store numeric data that the application evaluates as numbers. Table B compares the four integer data types.

Table B: Integer data types

Data type Minimum value Maximum value Storage size
tinyint 0 255 1 byte
smallint -32,768 32,767 2 bytes
int -2,147,483,648 2,147,483,674 4 bytes
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807 8 bytes

Assigning the appropriate integer data type isn't as confusing as choosing a character data type. Simply use the smallest integer data type that accommodates the largest possible value.

#3: What's the difference between numeric and decimal?

There's no difference between the numeric and decimal data types. Use them interchangeably or use one or the other to store integer and floating-point numbers scaled from 1 to 38 places, inclusive of both sides of a decimal. Use this data type when you need to control the accuracy of your calculations in terms of the number of decimal digits. The following table lists the exact storage size for this data type, depending on size, as listed in Table C.

Table C: Precision storage requirements

Total characters (precision) Storage size
1 - 9 5 bytes
10 - 19 9 bytes
20 - 28 13 bytes
29 - 38 17 bytes

#4: What's the difference between float and real?

The only differences between float and real are their minimum and maximum values and their required storage, as compared in Table D. Use float or real to store approximate values, where precision can't be represented (e.g., Pi).

Table D: Float and real data type restrictions

Data type n Minimum Value Maximum value Precision Storage size
float(n) 1 - 24 -1.79E + 308 1.79 + 308 7 digits 4 bytes
25 - 53 -1.79E + 308 1.79E + 308 15 digits 8 bytes
real n/a -3.40E + 38 3.40E + 38 7 digits 4 bytes

The real data type is the same as float(24) -- a floating data type with 24 digits to the right of the decimal point.

#5: What's the difference between smalldatetime and datetime?

Both smalldatetime and datetime store a combination date and time value, but the minimum and maximum values, accuracy, and storage size are different, as compared in Table E. Use datetime even when all dates fall into smalldatetime's range, if you require up-to-the-second accuracy.

Table E: Smalldatetime and datetime restrictions

Data type Minimum value Maximum value Accuracy Storage size
smalldatetime January 1, 1900 June 6, 2079 Up to a minute 4 bytes (the first 2 bytes store the date; the second 2 bytes store the time)
datetime January 1, 1753 December 31, 9999 One three-hundredth of a second 8 bytes (the first 4 bytes store the date; the second 4 bytes store the time)

#6: What's the difference between smallmoney and money?

Use both smallmoney and money to store currency values. However, the minimum and maximum values for both differ, as compared in Table F. Both data types are accurate up to ten-thousandths of a monetary unit.

Table F: Smallmoney and money restrictions

Data type Minimum value Maximum value Storage size
smallmoney -214,748.3648 214,748,3647 4 bytes
money -922,337,203,685,477.5808 922,337,203.685,477.5807 8 bytes

#7: Where's the Boolean data type?

SQL Server doesn't have a Boolean data type, at least not by that name. To store True/False, Yes/No, and On/Off values, use the bit data type. It accepts only three values: 0, 1, and NULL. (NULL is supported by SQL Server 7.0 and later.)

#8: What happened to text, ntext, and image?

SQL Server is phasing out text, ntext, and image. There's no way to know how long SQL Server will support the older data types. Upgrade legacy applications to varchar, nvarchar, and varbinary.

#9: How do I assign a cursor or table data type?

You don't, at least not in the traditional manner. You don't assign these data types to a column. You can use cursor and table only as variables:

  • The cursor data type allows you to return a cursor from a stored procedure or store a cursor as a variable.
  • The table data type returns a table from a stored procedure or stores a table as a variable for later processing.

#10: What is a user-defined data type?

SQL Server lets you create custom data types that are based on system data types. Create a user-defined data type when you specify the same limitations often. For instance, if many tables contain a state column, base a user-defined data type on SQL Server's nchar (see #1) with a length of 2 and name it State. Then, choose State as the column's data type, instead of specifying nchar(2). It requires about as much work, but it's self-documenting and easy to remember. This example is simple; usually a user-defined data type is a bit more complex.

#11: Does SQL Server 2008 have any new data types?

SQL Server 2008 has several new data types:

  • date stores only date values with a range of 0001-01-01 through 9999-12-31.
  • time stores only time values with a range of 00:00:00.0000000 through 23:59:59.9999999.
  • datetime2 has a larger year and second range.
  • datetimeoffset lets you consider times in different zones.
  • hierarchyid constructs relationships among data elements within a table, so you can represent a position in a hierarchy.
  • spatial identifies geographical locations and shapes -- landmarks, roads, and so on.


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
chris.hunt
chris.hunt

From number 8 in the article, SQL Server is phasing out the Text, Ntext and image data types. Does anyone have any thoughts on how we may store large documents or images within the database with Varchar and Nvarchar maxing out at 8000 and 4000 chars respectively? Would Microsoft be pushing the use of Sharepoint Server instead? I can think of may situations where this wouldn't be an ideal solution.

Tony Hopkinson
Tony Hopkinson

Can't think of a single reason to ue sharepoint to get round the limitation though. Coping with the linked file / resource not being there (hopefully gracefully), and above all back ups is an issue, but other than that blobs just make a complete arse out of a database. They are convenient for storage and retrieval, but analysis wise near useless without heavy effort. Far too often they are chosen out of erm laziness, even by me :(

Editor's Picks