Microsoft SQL Server/Variables

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Declaration and affectation[edit | edit source]

Every variable name begins with an at.

  • Integer operations:
declare @i int
set @i = 5

declare @j int
set @j = 6

print @i+@j -- displays 11
  • Character operations:
declare @k char
set @k = '5'

declare @l char
set @l = '6'

print @k+@l -- displays 56

Types[edit | edit source]

The possible variable types are similar to the table fields ones[1]:

Characters[edit | edit source]

Those beginning by "n" are in Unicode format.

char, nchar, nvarchar, ntext, text, varchar.

To save a few memory space, it's possible to set a characters number limit during the declaration:


The variable of characters maximum size is 2 GB[2] :


Numbers[edit | edit source]

decimal, int (tinyint, smallint, bigint), float, money, numeric, real, smallmoney.

Dates[edit | edit source]

date, datetime, datetime2, datetimeoffset, smalldatetime, time.

Personalized types[edit | edit source]

In addition to the native types, it's possible to create one's own data types with CREATE TYPE.

Type determination[edit | edit source]

The function SQL_VARIANT_PROPERTY returns a given field type[3]. Example:

FROM table1

References[edit | edit source]