Sql data types with example, define or alter data type in sql table, learn sql database development

Sql data types example

In any programming language, there are some standard defined data types, even in SQL (Structured Query Language) which is used for database programming, has some certain data type, here you learn all 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 sql 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 table.

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

String data types in sql database

All different type string data type that sql server supports.

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

Here are different type numeric data type that sql server supports.

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

Learn more about sql datetime functions and data type

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
MS SQL Examples | Join MS SQL Course