In SQL server
Variables is a name which is given to a storage area, we can assign value to
that and we can retrieve that value. Every variable have their data type, which
specify the size for that variable.
Assign value for
that variable is stored within that memory, if you will try to assign value
more that memory it will give “arithmetic overflow error”.
Variable
Declaration
Variable
declaration in SQL Server is like following
|
DECLARE @i
INT
SELECT
@i
= 20
PRINT @i
DECLARE : – Variables are declared
using the “DECLARED” keyword
INT :-
DATATYPE of the variable
@ :- This is name of the variable here @ is
define Local variable
SELECT/SET : – Assign the value to the
variable we can use SELECT/SET
Print : – Print the Value we use PRINT
Keyword
|
Example
for variable Declaration
DECLARE
@i INT
SELECT
@i
= 100
PRINT
@i
Output
If we assign value
to variable more than memory storage it gives arithmetic overflow error.
DECLARE
@i INT
SELECT
@i
= 10000000000
PRINT
@i
Output
DEMO
2: Multiple variables declaration
|
DECLARE @i INT , @j DECIMAL
|
DEMO
3: Value can be assign to a variable in
Sql Server 2008 and above.
|
DECLARE @i INT = 10
|
DEMO
4: Assign the variable
|
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
|
There are two types
of variable scope:
·
Local variables – variables declared in an inner
block and not accessible to outer blocks.
·
Global variables – variables declared in the outermost
block or a package.
Data Types
DataType is an
property to assign the type of the variable ,there are many type of data
types in sql server ex int,float,bool,decimal etc
Following are the
Main categories of Data Types in Sql Server
1. Numeric Data Types
·
Exact Numeric Data Type
·
Approximate Numeric Data Types
1. Date And Time Data Types
2. Character String Data Types
·
Non-Unicode Character String Data Types
·
Unicode Character String Data Types
1. Binary Data Types
2. Other Data Types
·
Large value data types: varchar(max), nvarchar(max),
and varbinary(max)
|
DATA TYPE
|
FROM
|
TO
|
|
bigint
|
-9,223,372,036,854,775,808
|
9,223,372,036,854,775,807
|
|
int
|
-2,147,483,648
|
2,147,483,647
|
|
smallint
|
-32,768
|
32,767
|
|
tinyint
|
0
|
255
|
|
bit
|
0
|
1
|
|
decimal
|
-10^38 +1
|
10^38 -1
|
|
numeric
|
-10^38 +1
|
10^38 -1
|
|
money
|
-922,337,203,685,477.5808
|
+922,337,203,685,477.5807
|
|
smallmoney
|
-214,748.3648
|
+214,748.3647
|
·
Large object data types: text, ntext, image, varchar(max), nvarchar(max),varbinary(max),
and xml
Exact Numeric DataType
Approximate
Numeric Data Types
|
DATA TYPE
|
FROM
|
TO
|
|
float
|
-1.79E + 308
|
1.79E + 308
|
|
real
|
-3.40E + 38
|
3.40E + 38
|
2.
Date And Time
Data Types
|
DATA TYPE
|
FROM
|
TO
|
|
datetime
|
Jan 1, 1753
|
Dec 31, 9999
|
|
smalldatetime
|
Jan 1, 1900
|
Jun 6, 2079
|
|
date
|
Stores a date like June 30, 1991
|
|
|
time
|
Stores a time of day like 12:30
P.M.
|
|
|
DATA
TYPE
|
Description
|
|
char
|
Maximum
length of 8,000 characters.( Fixed length non-Unicode characters)
|
|
varchar
|
Maximum
of 8,000 characters.(Variable-length non-Unicode data).
|
|
varchar(max)
|
Maximum
length of 231characters, Variable-length non-Unicode data (SQL Server 2005
only).
|
|
text
|
Variable-length
non-Unicode data with a maximum length of 2,147,483,647 characters.
|
3.
Character
String Data Types
|
DATA TYPE
|
Description
|
|
nchar
|
Maximum length of 4,000 characters.( Fixed length Unicode)
|
|
nvarchar
|
Maximum length of 4,000 characters.(Variable length Unicode)
|
|
nvarchar(max)
|
Maximum length of 231characters (SQL Server 2005 only).(
Variable length Unicode)
|
|
ntext
|
Maximum length of 1,073,741,823 characters. ( Variable length
Unicode )
|
Non-Unicode
Character String Data Types
Binary Data Types
|
DATA TYPE
|
Description
|
|
binary
|
Maximum length of 8,000
bytes(Fixed-length binary data )
|
|
varbinary
|
Maximum length of 8,000
bytes.(Variable length binary data)
|
|
varbinary(max)
|
Maximum length of 231 bytes (SQL
Server 2005 only). ( Variable length Binary data)
|
|
image
|
Maximum length of 2,147,483,647
bytes. ( Variable length Binary Data)
|
Misc Data Types
|
DATA TYPE
|
Description
|
|
sql_variant
|
Stores values of various SQL
Server-supported data types, except text, ntext, and timestamp.
|
|
timestamp
|
Stores a database-wide unique number
that gets updated every time a row gets updated
|
|
uniqueidentifier
|
Stores a globally unique identifier
(GUID)
|
|
xml
|
Stores XML data. You can store xml
instances in a column or a variable (SQL Server 2005 only).
|
|
cursor
|
Reference to a cursor object
|
|
table
|
Stores a result set for later
processing
|
EmoticonEmoticon