hi

Variables And Data Types in SQL Server

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: textntextimagevarchar(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


Share this

Related Posts

Previous
Next Post »