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.

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.

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.

Different data types in Sql

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]
) 

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

• Strings Data Types
• Numeric Data Types
• Date and Time Data Types
• Binary Data Types
• Misc Data Types

All different type string data type that sql server supports.

• 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)

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

• 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

Learn more about sql datetime functions and data type

• 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)

• uniqueidentifier
  Stores a GUID ( globally unique identifier )
• xml
  Stores XML data.
• timestamp
  unique number that gets updated every time a row gets updated