Microsoft SQL Server/Variables

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

Declaration and affectation[edit]

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]

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

Characters[edit]

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:

varchar(255)

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

varchar(MAX)

Numbers[edit]

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

Dates[edit]

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

Personalized types[edit]

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

Type determination[edit]

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

SELECT SQL_VARIANT_PROPERTY(Field1, 'BaseType')
FROM table1

References[edit]