Free Web Development Online Tutorials, Learn to Code
Learn SQL Database Development, SQL Data Types with Example, define or alter data type in SQL table

SQL Data Types with Example

Here you learn SQL Data Types with Example.

What is Data Type in SQL

Data Types in SQL is an attribute, which tells the type of data of any column, variable and expression has a related data type in SQL.

sql data types

As you can see in above picture diagram, you can define data type while creating a table, also can alter the data type for any particular column, however if there is data of different type which are not convertible, that that may cause an error.

Here is the SQL code of defining data type at the time of creating or altering a Table

Create TABLE [dbo].[tbWeek](
[WeekId] [int] IDENTITY(1,1) NOT NULL,
[WeekName] [varchar](50) NOT NULL,
[UpdateDate] [datetime] NOT NULL,
[CreateDate] [date] NULL,
[NoOfHolidays] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[WeekId] ASC
)
 ON [PRIMARY]
) 

Alter Column Data Type in SQL Table

Here is an example of how you can alter column data type in SQL.

Notice, when i created the table with above script, the column "WeekName" was defined as Varchar data type, now i change the data type to nvarchar data type.

ALTER TABLE tbWeek 
ALTER COLUMN [WeekName] nvarchar(50);
GO

Here are the different data type examples in SQL Database

Strings Data Types in SQL Database

Data Type Example
char Max length of 8,000 characters (non-Unicode characters, fixed length)
varchar Max of 8,000 characters.(non-Unicode data, variable-length )
text Max length of 2,147,483,647 characters (variable length, non-Unicode data)
nchar Max length of 4,000 characters (unicode characters, fixed length)
nvarchar Max length of 4,000 characters (unicode characters, variable length)
ntext Max length of 1,073,741,823 characters. ( unicode, variable length)

Numeric Data Types in SQL Database

Data Type Example
bigint -+9,223,372,036,854,775,807
int -+2,147,483,648
numeric 10^38 -1
decimal 100000.000001
bit 0 or 1 (boolean)
money +- 922,337,203,685,477.5808
smallmoney +- 214,748.3648

SQL Date Data Types

Data Type Example
datetime
smalldatetime
date
time

SQL Binary Data Types

Data Type Example
binary Max length of 8,000 bytes(binary data, fixed length )
varbinary Max length of 8,000 bytes.(binary data, variable length)
image Max length of 2,147,483,647 bytes (binary data, variable length)

Misc Data Types in SQL Database

Data Type Example
uniqueidentifier Stores a GUID ( globally unique identifier )
xml Stores XML data.
timestamp unique number that gets updated every time a row gets updated
Comment
Name
Email
Website
Subscribe
 
Data Types in SQL

SQL job Interview Questions Answers

Course in Demand
MS SQL Examples | Join MS SQL Course