Wikibooks

Microsoft SQL Server/Variables

< Microsoft SQL Server

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
  1. ↑ https://msdn.microsoft.com/en-US/library/ms187752.aspx
  2. ↑ https://msdn.microsoft.com/en-US/library/ms176089.aspx
  3. ↑ https://msdn.microsoft.com/en-us/library/ms178550.aspx
Retrieved from "https://en.wikibooks.org/w/index.php?title=Microsoft_SQL_Server/Variables&oldid=3678079"