Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
SQL Server Developer Center
Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Database Engine
Technical Reference

  Switch on low bandwidth view
Community Content
In this section
Statistics Annotations (1)
Other versions are also available for the following:
SQL Server 2008 Books Online (June 2009)
Data Types (Transact-SQL)

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.

SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types are based on the system-supplied data types. For more information about alias data types, see Working with Alias Data Types. User-defined types obtain their characteristics from the methods and operators of a class that you create by using one of the programming languages support by the .NET Framework. For more information, see Working with CLR User-defined Types.

When two expressions that have different data types, collations, precision, scale, or length are combined by an operator, the characteristics of result are determined by the following:

  • The data type of the result is determined by applying the rules of data type precedence to the data types of the input expressions. For more information, see Data Type Precedence (Transact-SQL).
  • The collation of the result is determined by the rules of collation precedence when the result data type is char, varchar, text, nchar, nvarchar, or ntext. For more information, see Collation Precedence (Transact-SQL).
  • The precision, scale, and length of the result depend on the precision, scale, and length of the input expressions. For more information, see Precision, Scale, and Length (Transact-SQL).

SQL Server provides data type synonyms for ISO compatibility. For more information, see Data Type Synonyms (Transact-SQL).

Data types in SQL Server are organized into the following categories:

Exact numerics

Unicode character strings

Approximate numerics

Binary strings

Date and time

Other data types

Character strings

 

In SQL Server, based on their storage characteristics, some data types are designated as belonging to the following groups:

  • Large value data types: varchar(max), nvarchar(max), and varbinary(max)
  • Large object data types: text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml
    ms187752.note(en-us,SQL.100).gifNote:
    sp_help returns -1 as the length for the large-value and xml data types.

Exact Numerics

bigint

numeric

bit

smallint

decimal

smallmoney

int

tinyint

money

 

Approximate Numerics

float

real

Date and Time

date

datetimeoffset

datetime2

smalldatetime

datetime

time

Character Strings

char

varchar

text

 

Unicode Character Strings

nchar

nvarchar

ntext

 

Binary Strings

binary

varbinary

image

 

Other Data Types

cursor

timestamp

hierarchyid

uniqueidentifier

sql_variant

xml

table

Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
SQL Data Type Question      mmagnes ... Tai Yee - MSFT   |   Edit   |   Show History
in access i can create a table field with the data type of Auto number to have the next unique number automatically generated in the field. How/Can this be done in SQL?

[Tai Yee - MSFT] Hi, the best way to get questions answered is to post in the Microsoft Community Forum. The SQL Server Forums are located here: http://social.msdn.microsoft.com/Forums/en-US/category/sqlserver/. You may want to post this to the Transact-SQL forum.
You can generate autonumbers in SQL as follow      Generate Auto number in SQL   |   Edit   |   Show History
Hi,

use this to generate auto numbers in SQL.first of all these auto-generated numbers mostly applies to Primary keys or columns with unique identifier.

Create table Test
( TestID int unique identity (0001, 1)
)

here i have created a table called Test, with a column testid -- int as the data type, identity to auto generate the id with a seed of 0001 and an increment of 1.

hope that help,i have not tried in my query window but i hope it works for you otherwise let me know and i modify if there is any mistake.

let me know
Tags What's this?: Add a tag
Flag as ContentBug
decimal part of money field is cut in a select with inner join      PericlesP ... Tai Yee - MSFT   |   Edit   |   Show History
Hi,
executing a simple query on a table A having a field of type money and content eg 28.5 it correctly returns the value 28.5.
When executing a query with INNER JOIN with another table B, in the results it returns the field value with a value of 28 not 28.5.
Note that this Amount field in table A does not exiat as a field in table B

Is Inner JOIN doing any conversion on the data? I read from the documentation that the field created has same type and length atributes. But why I receive the integral part only?
thanks

[Tai Yee - MSFT] Hi, the best way to get questions answered is to post in the Microsoft Community Forum. The SQL Server Forums are located here: http://social.msdn.microsoft.com/Forums/en-US/category/sqlserver/. You will likely want to post this question to the Transact-SQL forum. 7/8/2009
© 2009 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker