About SQL server datatype and variables | SQL Server

Veerababu Narla #1

Member

30 Points

Posted on 31 Jan 2014 04:37 PM IST

Variables

A variable is nothing but a name given to a storage location. We can place data into them and retrieve. Each variable in Sql Server has a specific type, which determines the size and layout of the variable's memory; the range of values that can be stored within that memory; and the set of operations that can be applied to the variable.

Variable Declaration

Variables can be declared by using DECLARE statement in Sql server as shown below.

DEMO 1: Declaring an Integer Variable, assigning value using SET statement and displaying it's value using PRINT statement

DECLARE @i INT

SET @i = 10

PRINT @i

RESULT:

DEMO 2: Multiple variables can be declared using single declare statement as shown below

DECLARE @i INT, @j TINYINT

DEMO 3: Variables can be assigned value while declaring as shown below in Sql Server 2008 and above.

DECLARE @i INT = 10

DEMO 4: Variables can be assigned value by using SET/SELECT statement as shown below.

SET statement can assign only one variable at a time.

DECLARE @i INT, @j INT

SET @i = 10

SET @j = 20

SELECT statement can assign values to more than one variable at a time

DECLARE @i INT, @j INT

SELECT @i = 10, @j = 20

Data Types

Following are the Main categories of Data Types in Sql Server

Numeric Data Types
- Exact Numeric Data Type
- Approximate Numeric Data Types

Date And Time Data Types

Character String Data Types
- Non-Unicode Character String Data Types
- Unicode Character String Data Types

Binary Data Types

Other Data Types

Let us go-through each of these categories of datatypes.

1. Numeric Data Types

Exact Numeric Data Type

Data Type	Explanation and Size (in Bytes)	Range
BIT	If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.	It can take a value of 1, 0, or NULL.
TINYINT	1 Byte	0 to 255
SMALLINT	2 Byte	-2^15 (-32,768) to 2^15-1 (32,767)
INT	4 Byte	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
BIGINT	8 Byte	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
DECIMAL[(p[,s])] or NUMERIC[(p[,s])]	precision p, scale s. Example: decimal(5,2) is a number that has 3 (p-s) digits before the decimal and 2 digits after the decimal. Precision: 1 - 9 Size: 5 Bytes Precision: 10 - 19 Size: 9 Bytes Precision: 20 - 28 Size: 13 Bytes Precision: 29 - 38 Size: 17 Bytes	- 10^38 +1 through 10^38 - 1
SMALLMONEY	4 Bytes	-214,748.3648 to +214,748.3647
MONEY	8 Bytes	-922,337,203,685,477.5808 to +922,337,203,685,477.5807

Approximate Numeric Data Types

Data Type	Explanation and Size (in Bytes)	Range
FLOAT[(n)]	Floating point Numeric data.n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If nis specified, it must be a value between 1 and 53. The default value of n is 53. If n valueIf 1<=n<=24, n is treated as 24, precision will be 7 digits and storage size will be 4 bytes.If 25<=n<=53, n is treated as 53, precision will be 15 digits and storage size will be 8 bytes.	- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
REAL	REAL is FLOAT(24)4 Bytes	- 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38

Working with BIT Data Type
Demo 1: For BIT data type we can take a value of 1, 0, or NULL.

DECLARE @i BIT

SET @i = 1

PRINT @i

RESULT: 1

DECLARE @i BIT

SET @i = 0

SELECT @i

RESULT: 0

DECLARE @i BIT

SELECT @i

RESULT: NULL

DECLARE @i BIT

SET @i = NULL

SELECT @i

RESULT: NULL

DEMO 2: Try to assign value other than 0, 1 and NULL

 DECLARE @i BIT

 SET @i = 500

 PRINT @i

RESULT: 1

DECLARE @i BIT

 SET @i = -500.9

 PRINT @i

RESULT: 1

DECLARE @i BIT

 SET @i = 'TRUE'

 PRINT @i

RESULT: 1

DECLARE @i BIT

 SET @i = 'False'

 PRINT @i

RESULT: 0

DECLARE @i BIT

 SET @i = 'TEST'

 PRINT @i

RESULT:
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'TEST' to data type bit.

Working with TINYINT DataType

DEMO 1: Valid Range 0 to 255

DECLARE @i TINYINT

SET @i = 150

PRINT @i

RESULT: 150
DEMO 2: Try to assign out of range value for TinyInt

DECLARE @i TINYINT

SET @i = 260

PRINT @i

RESULT
Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = 260.

DECLARE @i TINYINT

SET @i = -150

PRINT @i

RESULT:
Msg 220, Level 16, State 2, Line 2
Arithmetic overflow error for data type tinyint, value = -150.

Working with SMALLINT DataType
DEMO 1: Valid Values

DECLARE @i SMALLINT

SET @i = 4000

PRINT @i

RESULT: 4000

DECLARE @j SMALLINT

SET @j = -4000

PRINT @j

RESULT: -4000

DEMO 2: InValid Values

DECLARE @k SMALLINT

SET @k = 40000

PRINT @k

RESULT:
Msg 220, Level 16, State 1, Line 2
Arithmetic overflow error for data type smallint, value = 40000.

Working with INT DataType

DEMO 1: Valid Values

DECLARE @i INT

SET @i = 4000

PRINT @i

RESULT: 4000

DECLARE @j INT

SET @j = -4000

PRINT @j

RESULT: -4000

DECLARE @k INT

SET @k = 40000

PRINT @k

RESULT: 40000

Working with DECIMAL data type

DECLARE @a DECIMAL(3,3)

SET @a = 3.10

PRINT @a

RESULT:
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.

DECLARE @a DECIMAL(4,3)

SET @a = 3.10

PRINT @a

RESULT: 3.100

DECLARE @a DECIMAL(3,3)

SET @a = 0.310

PRINT @a

RESULT: 0.310

DECLARE @a DECIMAL(4,3)

SET @a = 3.10

PRINT @a

RESULT:3.100

DECLARE @a DECIMAL(6,3)

SET @a = 0.36

PRINT @a

RESULT: 0.360

DECLARE @a DECIMAL(6,3)

SET @a = 20.36

PRINT @a

RESULT: 20.360

DECLARE @a DECIMAL(6,3)

SET @a = 999.36

PRINT @a

RESULT: 999.360

DECLARE @a DECIMAL(6,3)

SET @a = 1999.3

PRINT @a

RESULT:
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.

DECLARE @a DECIMAL(5,3)

SET @a = 33.1116666

PRINT @a

RESULT: 33.112

DECLARE @a DECIMAL(5,0)

SET @a = 133.111

PRINT @a

RESULT: 133

2. Date And Time Data Types

Below were the only two Date and Time data types that were present in Sql Server prior to Sql Server 2008.

Data Type	Explanation and Size (in Bytes)	Range
SMALLDATETIME	Storage: 4 Bytes Default Format: YYYY-MM-DD hh:mm:ss Accuracy: 1 Minute. Seconds value will always be 0. Seconds values that are 29.998 seconds or less are rounded down to the nearest minute, Values of 29.999 seconds or more are rounded up to the nearest minute.	1900-01-01 00:00:00 to 2079-06-06 23:59:00
DATETIME	Storage: 8 Bytes Default Format: YYYY-MM-DD hh:mm:ss.nnn Accuracy: Rounded to increments of .000, .003, or .007 seconds	1753-01-01 00:00:00 to 9999-12-31 23:59:59.997

Below are the list of additional Date and Time Datatypes which were introduced in Sql Server 2008:

Data Type	Explanation and Size (in Bytes)	Range
TIME[(n)]	Storage: 3 to 5 Bytes based on the value of n (i.e. Fractional seconds precision). If 0<=n<=2, Size: 3 Bytes If 3<=n<=4, Size: 4 Bytes If 5<=n<=7, Size: 5 Bytes As n is optional if it is not specified, then it is considered as 7 Default Format: hh:mm:ss[.nnnnnnn] Accuracy: 100 nanoseconds	00:00:00 to 23:59:59.9999999
DATE	Storage: 3 Bytes Default Format: YYYY-MM-DD Accuracy: 1 Day	0001-01-01 to 9999-12-31
DATETIME2[(n)]	Storage: 6 to 8 Bytes based on the value of n (i.e. Fractional seconds precision). If 0<=n<=2, Size: 6 Bytes If 3<=n<=4, Size: 7 Bytes If 5<=n<=7, Size: 8 Bytes As n is optional if it is not specified, then it is considered as 7 Default Format: hh:mm:ss[.nnnnnnn] Accuracy: 100 nanoseconds	0001-01-01 00:00:00 to 9999-12-31 23:59:59.9999999
DATETIMEOFFSET[(n)]	Storage: 8 to 10 Bytes based on the value of n (i.e. Fractional seconds precision). If 0<=n<=2, Size: 8 Bytes If 3<=n<=4, Size: 9 Bytes If 5<=n<=7, Size: 10 Bytes As n is optional if it is not specified, then it is considered as 7. Default Format: YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+\|-}hh:mm] Accuracy: 100 nanosecondsNote: DateTimeOffset defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.	0001-01-01 00:00:00 to 9999-12-31 23:59:59.9999999 (in UTC)

You may like to read Difference between DateTime and DateTime2 DataType.

Working with DATETIME data type

DECLARE @DateEx DATETIME

SET @DateEx = GETDATE()

PRINT @DateEx

RESULT: Jan 26 2014 6:25AM

DECLARE @DateEx DATETIME

SET @DateEx = '12/15/1800'

PRINT @DateEx

RESULT: Dec 15 1800 12:00AM

DECLARE @DateEx DATETIME

SET @DateEx = '12/15/1400'

PRINT @DateEx

RESULT:
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Working with SMALLDATETIME data type

DECLARE @DateEx SMALLDATETIME

SET @DateEx = Getdate()

PRINT @DateEx

RESULT: Jan 26 2014 6:27AM

DECLARE @DateEx SMALLDATETIME

SET @DateEx = '12/15/1800'

PRINT @DateEx

RESULT:
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

DECLARE @DateEx SMALLDATETIME

SET @DateEx = '12/15/2078'

PRINT @DateEx

RESULT:Dec 15 2078 12:00AM

DECLARE @DateEx SMALLDATETIME

SET @DateEx = '12/15/2080'

PRINT @DateEx

RESULT:
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

3. Character String Data Types

Non-Unicode Character String Data Types

Data Type	Explanation and Size (in Bytes)
CHAR[(n)]	Fixed Length Non-Unicode Character Data Type. For example if you declare a variable of type CHAR (10), then it will always take 10 Bytes + 2 Bytes extra for defining offset) irrespective of whether you are storing 1 character or 10 character in this variable or column. 1<=n<=8000. Can store Maximum of 8000 Characters. Default Value of n: 1
VARCHAR[(n)]	Variable Length Non-Unicode Character Data Type. 1<=n<=8000. Default Value of n: 1 It can store maximum 8000 Non-Unicode characters (i.e. maximum storage capacity is 8000 bytes of storage). The storage size is the actual length of the data entered + 2 bytes.
VARCHAR(Max)	Variable Length Non-Unicode Character Data Type. It can store maximum of 2 147 483 647 Non-Unicode characters (i.e. maximum storage capacity is: 2GB). The storage size is the actual length of the data entered + 2 bytes.

Unicode ( i.e. Japanese, Korean etc double byte) Character String Data Types

Data Type	Explanation and Size (in Bytes)
NCHAR[(n)]	Fixed Length UNicode Character Data Type. For example if you declare a variable of type NChar(10), then it will always take 20 Bytes + 2 Bytes extra for defining offset irrespective of whether you are storing 1 character or 10 character in this variable or column. 1<=n<=4000. Can store Maximum of 4000 Characters. Default Value of n: 1
NVARCHAR[(n)]	UNicode Variable Length Character Data Type. 1<=n<=4000. Default Value of n: 1 It can store maximum 4000 characters (i.e. maximum storage capacity is 8000 bytes of storage). The storage size is two times the actual length of the data entered + 2 bytes.
NVARCHAR(Max)	UNicode Variable Length Character Data Type. Maximum storage capacity is: 2GB. The storage size is two times the actual length of the data entered + 2 bytes.

Here Text and NText Character strings data type's are not mentioned as they are not recommended instead their respective alternatives Varchar(MAX) and NVarchar(MAX) which were introduced in Sql Server 2005 can be used.

Below articles gives detailed insights into the Character String Data Types.
Difference between Sql Server CHAR and VARCHAR Data Type
Difference Between Sql Server VARCHAR and NVARCHAR Data Type
Difference Between Sql Server VARCHAR and VARCHAR(MAX) Data Type

CHAR DEMO

DECLARE @name CHAR(4)

SET @name = 'AB'

PRINT @name

--DATALENGTH function can be used to identify storage size

PRINT DATALENGTH(@name)

RESULT:
AB
4

DECLARE @name CHAR(4)

SET @name = 'ABCD'

SELECT @name, DATALENGTH(@name)

RESULT: ABCD 4

--Fifth character will be truncated

DECLARE @name CHAR(4)

SET @name = 'ABCDE'

SELECT @name, DATALENGTH(@name)

RESULT: ABCD 4

VARCHAR DEMO

DECLARE @name VARCHAR(4)

SET @name = 'AB'

SELECT @name, DATALENGTH(@name)

RESULT: AB 2

DECLARE @name VARCHAR(4)

SET @name = 'ABCD'

SELECT @name, DATALENGTH(@name)

RESULT: ABCD 4

--Fifth character will be truncated

DECLARE @name VARCHAR(4)

SET @name = 'ABCDE'

SELECT @name, DATALENGTH(@name)

RESULT: ABCD 4

VARCHAR(max) DEMO

DECLARE @name VARCHAR(max)

SET @name = 'AB'

SELECT @name, DATALENGTH(@name)

RESULT: AB 2

NCHAR DEMO

DECLARE @name NCHAR(4)

SET @name = 'AB'

SELECT @name, DATALENGTH(@name)

RESULT: AB 8

DECLARE @name NCHAR(4)

SET @name = 'ABCD'

SELECT @name, DATALENGTH(@name)

RESULT: ABCD 8

--Fifth character will be truncated

DECLARE @name NCHAR(4)

SET @name = 'ABCDE'

SELECT @name, DATALENGTH(@name)

RESULT: ABCD 8

NVARCHAR DEMO

DECLARE @name NVARCHAR(4)

SET @name = 'AB'

SELECT @name, DATALENGTH(@name)

RESULT: AB 8

DECLARE @name NVARCHAR(4)

SET @name = 'ABCD'

SELECT @name, DATALENGTH(@name)

RESULT: ABCD 8

--Fifth character will be truncated

DECLARE @name NVARCHAR(4)

SET @name = 'ABCDE'

SELECT @name, DATALENGTH(@name)

RESULT: ABCD 8

NVARCHAR(max) DEMO

DECLARE @name NVARCHAR(max)

SET @name = 'AB'

SELECT @name, DATALENGTH(@name)

RESULT: AB 4

4. Binary Data Types

Data Type	Explanation and Size (in Bytes)
BINARY[(n)]	Fixed-length Binary Data Type. 1<=n<=8000. Can store Maximum of 8000 bytes of data. Default Value of n: 1 The storage size is n bytes.
VARBINARY[(n)]	Variable Length Binary Data Type. 1<=n<=8000. Can store Maximum of 8000 bytes of data. Default Value of n: 1 The storage size is the actual length of the data entered + 2 bytes.
VARBINARY(Max)	Variable Length Binary Data Type. It can store Maximium 2GB of data. The storage size is the actual length of the data entered + 2 bytes.

Here Image data type is skipped as it is not recommended instead it's alternative VarBinary(MAX) which was introduced in Sql Server 2005 can be used.

5. Other Data Types

Data Type	Details
SQL_VARIANT	A data type that stores values of various SQL Server-supported data types, except the values of the datatypes varchar(max), varbinary(max), nvarchar(max), xml, text, ntext, image, timestamp, sql_variant, geography, hierarchyid, geometry, User-defined types, datetimeoffset. For example, a column defined as sql_variant can store int, binary, and char values. sql_variant can have a maximum length of 8016 bytes. This includes both the base type information and the base type value. The maximum length of the actual base type value is 8,000 bytes.
UNIQUEIDENTIFIER	Stores a globally unique identifier (GUID) It can be initialized by using NEWID() function or by converting from a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid UniqueIdentifier value.
XML	Stores XML data. Maximum storage size is 2 GB
TABLE	Stores query result set for later processing

Working with UNIQUEIDENTIFIER

DECLARE @a UNIQUEIDENTIFIER

SET @a = NEWID()

PRINT @a

RESULT: 0A1A803E-D5CA-4AB7-8357-26DCA166BB99

DECLARE @a UNIQUEIDENTIFIER

SET @a = 'Kalpana'

PRINT @a

RESULT:
Msg 8169, Level 16, State 2, Line 2
Conversion failed when converting from a character string to uniqueidentifier.

DECLARE @a UNIQUEIDENTIFIER

SET @a = 'C50F5FB8-8D4D-4581-BEE4-46C5B24FB5C0'

PRINT @a

RESULT: C50F5FB8-8D4D-4581-BEE4-46C5B24FB5C0

SQL Server 1114 views Reply to this topic

No replies to this topic

Variables

Data Types

1. Numeric Data Types

2. Date And Time Data Types

3. Character String Data Types

4. Binary Data Types

5. Other Data Types

Tags

Top Contributors