Data Types in sqlserver

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data types in Transact-SQL or the Microsoft .NET Framework. Alias data types are based on the system-supplied data types. For more information about alias data types, see Working with Alias Data Types. User-defined types obtain their characteristics from the methods and operators of a class that you create by using one of the programming languages support by the .NET Framework. For more information, seeWorking with CLR User-defined Types.
When two expressions that have different data types, collations, precision, scale, or length are combined by an operator, the characteristics of result are determined by the following:

• The data type of the result is determined by applying the rules of data type precedence to the data types of the input expressions. For more information, see Data Type Precedence (Transact-SQL).
• The collation of the result is determined by the rules of collation precedence when the result data type ischar, varchar, text, nchar, nvarchar, or ntext. For more information, see Collation Precedence (Transact-SQL).
• The precision, scale, and length of the result depend on the precision, scale, and length of the input expressions. For more information, see Precision, Scale, and Length (Transact-SQL).

SQL Server provides data type synonyms for ISO compatibility. For more information, see Data Type Synonyms (Transact-SQL).

Data Type Categories

---

Data types in SQL Server are organized into the following categories:

Exact numerics	Unicode character strings
Approximate numerics	Binary strings
Date and time	Other data types
Character strings

In SQL Server, based on their storage characteristics, some data types are designated as belonging to the following groups:

• Large value data types: varchar(max), nvarchar(max), and varbinary(max)
• Large object data types: text, ntext, image, varchar(max), nvarchar(max), varbinary(max), and xml
  

  Note
  sp_help returns -1 as the length for the large-value and xml data types.

Exact Numerics

bigint	numeric
bit	smallint
decimal	smallmoney
int	tinyint
money

Approximate Numerics

float

real

Date and Time

date	datetimeoffset
datetime2	smalldatetime
datetime	time

Character Strings

char	varchar
text

Unicode Character Strings

nchar	nvarchar
ntext

Binary Strings

binary	varbinary
image

Other Data Types

cursor	timestamp
hierarchyid	uniqueidentifier
sql_variant	xml
table

example for table datatype

--code for userdefinefunction

-----------------------

create function div(@a decimal(14,2),@b int)

returns decimal(14,2)

as 

begin

declare @c decimal(14,2)

set @c=@a/@b

return @c

end

-------------------

--call the user define function

--------------------

select dbo.div(12.0,2)

---------------

--create a procedure name is sai which contain one parameter name is [@discount_product_code] which is varachar type and contains two userdefine table type[table datatype] variable [1-@sai and 2-@sai1]

--------------------

CREATE PROC sai(@discount_product_code varchar(30))

 AS

DECLARE @sai TABLE(recv_qty decimal(14,2),discountlist_quantity int)

INSERT INTO @sai

SELECT   sum(recv_qty)as recv_qty , Mas_Discount_Product_List.discountlist_quantity 

FROM         Mas_Stock INNER JOIN

                      Mas_Discount_Product_List ON Mas_Stock.product_code = Mas_Discount_Product_List.product_code 

where Mas_Discount_Product_List.discount_product_code like @discount_product_code+'%'

and Mas_Stock.branch_code='NEO2AND1' and Mas_Stock.warehouse_code='NEO6WHS1' and Mas_Stock.financial_year='2010-2011' group by Mas_Discount_Product_List.product_code,Mas_Discount_Product_List.discountlist_quantity

DECLARE @sai1 TABLE(recv_qty decimal(14,2),discountlist_quantity int,availableitem decimal(14,2))

INSERT INTO @sai1

SELECT recv_qty,discountlist_quantity,DBO.div(recv_qty,discountlist_quantity) 

FROM @sai  

select min(availableitem) from @sai1

----------------------

---call the procedure

---------------------

exec sai 'dp001'

-----------------

--drop the proc 

-------------

drop proc sai