Data Type means the type of data which
users provided to a specific column
In SQL Server 2 types of data types
available which includes
1. System Defined Data Types
2. User Defined Data Types.
System
Defined Data Types:
SQL Server already contains some data
types called System Defined data types or Predefined Data types or Built-in
Data types. System Defined Data Types again categorized into 4 types
a. Numeric Data types
b. String Data types
c. Date Time Data types
d. Special Data types
Numeric Data types:
These
Data types are used to provide numeric information for the columns, these
includes
Data Type Size
BIT 0 or 1
TINYINT 1 BYTE
SMALLINT 2 BYTES
* INT 4BYTES
BIGINT 8BYTES
REAL 4BYTES
FLOAT 8BYTES
DECIMAL
(P, S) 5-17 BYTES
TINYINT, SMALLINT, INT, BIGINT are called Exact Numerics where as REAL, FLOAT are
called Approximate Numerics.
String Data types:
These
Data types are used to provide character information for the columns, these
includes
Data
Type Size
CHAR [(N)] 1BYTE
* VARCHAR [(N)] 1BYTE
TEXT 16BYTES
VARCHAR(MAX) 16 GB
CHAR [(N)]: It is a fixed length data type, which occupies by default 1
byte memory. When we N value then it occupies N bytes of memory where N is an
integer. It follows Static memory allocation process.
VARCHAR [(N)]: It is a variable length data
type, which occupies by default 1 byte memory. When we N value then it occupies
N bytes of memory where N is an integer. It follows dynamic memory allocation
process.
Note : The Maximum limit for N is 8000,if
it is more than 8000 characters we will use TEXT or VARCHAR(MAX)
Date Time Data Type:
These
data types are used to provide date oriented information to the columns, these
includes
Data Type Size Range
SMALLDATETIME 2 BYTES 1900-01-01 TO 2079-06-06
* DATETIME 4BYTES 1753-01-01 TO 9999-12-31
Special Data types:
These data types are used to provide miscellaneous
information to the columns, these includes
Data Type Size
SMALLMONEY 4 BYTE
* MONEY 8
BYTES
IMAGE 16
BYTES
VARBINARY
(MAX) Unlimited
1.
SQL_VARIANT
- Binary Data types: These stores binary values of a given string in ordered to hide the original string values.
Data
Type Size
BINARY [(N)] 1BYTE
* VARBINARY [(N)] 1BYTE
- Unicode Data types: These Data types are used to store Unicode information, these includes
Data
Type Size
NCHAR [(N)] 2BYTE
* NVARCHAR [(N)] 2BYTE
NTEXT 16BYTES
II.
User Defined Data Types:
When user create a data type then that data
type is called user defined data type
Syntax:
CREATE TYPE
USER_DEFINED_DATATYPE FROM SYS_DEFINED_DATATYPE
Ex:
CREATE TYPE MYINT FROM INT
CREATE TYPE MYFLOAT FROM
FLOAT
CREATE TYPE CASH FROM MONEY
