”Don’t focus on having a great blog. Focus on
producing a blog that’s great for your readers.”- Brian Clark
 
 

MS SQL Server Data Types

11 November 2012

Introduction

Choosing the right data type is an important part of building a good database schema. To fully understand and correctly use SQL Server data type will improve query execution time, storage management on disk, and reduce backup time. The following tables provide quick reference to look up for limitations and ranges for each SQL Server data type.

Exact Numerics

Type From To
bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
int -2,147,483,648 2,147,483,647
smallint -32,768 32,767
tinyint 0 255
bit 0 1
decimal -10^38 +1 10^38 –1
numeric -10^38 +1 10^38 –1
money -922,337,203,685,477.5808 +922,337,203,685,477.5807
smallmoney -214,748.3648 +214,748.3647

numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate Numerics

Type From To
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

datetime and smalldatetime

Type From To
datetime (3.33 milliseconds accuracy) Jan 1, 1753 Dec 31, 9999
smalldatetime (1 minute accuracy) Jan 1, 1900 Jun 6, 2079
date (1 day accuracy. Introduced in SQL Server 2008) Jan 1, 0001 Dec 31, 9999
datetimeoffset (100 nanoseconds accuracy. Introduced in SQL Server 2008) Jan 1, 0001 Dec 31, 9999
datetime2 (100 nanoseconds accuracy. Introduced in SQL Server 2008) Jan 1, 0001 Dec 31, 9999
time (100 nanoseconds accuracy. Introduced in SQL Server 2008) 00:00:00.0000000 23:59:59.9999999

Character Strings

Here are a few general rules:

  • 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.
Type Description
char Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar Variable-length non-Unicode data with a maximum of 8,000 characters.
varchar(max) Variable-length non-Unicode data with a maximum length of 231characters (Introduced in SQL Server 2005).
text Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings

Type Description
nchar Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar Variable-length Unicode data with a maximum length of 4,000 characters.
nvarchar(max) Variable-length Unicode data with a maximum length of 230characters (Introduced in SQL Server 2005).
ntext Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

Binary Strings

Type Description
binary Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary Variable-length binary data with a maximum length of 8,000 bytes.
varbinary(max) Variable-length binary data with a maximum length of 231bytes (Introduced in SQL Server 2005).
image Variable-length binary data with a maximum length of 2,147,483,647 bytes.

Other Data Types

  • sql_variant: Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
  • timestamp: Stores a database-wide unique number that gets updated every time a row gets updated.
  • uniqueidentifier: Stores a globally unique identifier (GUID).
  • xml: Stores XML data. You can store xml instances in a column or a variable (Introduced in SQL Server 2005).
  • cursor: A reference to a cursor.
  • table: Stores a result set for later processing.
  • hierarchyid: A variable length, system data type used to represent position in a hierarchy (Introduced in SQL Server 2008).

About Site by Tommy

Freelance web design and developer in Jakarta and Microsoft Certified Professional with five years of experience in web design and development, application development, database system, Search Engine Optimization (SEO), graphic design, logo design, business cards, corporate letterhead, posters/banners, brochures and flyers, social media marketing, and email marketing/campaign.

 
Project Highlight
 
What Client Says
 
I’ve Got You Covered
  • Website Design
  • Application Development
  • Database System
  • Search Engine Optimization
  • Web Hosting
  • Graphic Design
  • Logo Design
  • Business Cards
  • Corporate Letterhead
  • Posters/Banners
  • Brochures and Flyers
  • Event Invitations
  • Promotional Products
  • Social Media Marketing
  • E-mail Marketing/Campaign
  • Event Marketing