Understand MS SQL Server Data Types

What is a data type?

A database stores information in tables. A table has rows and columns. A column is like one labelled box on a form, such as "Age" or "Name".

A data type tells the database what kind of thing can go in that box. It is like a label that says "numbers only" or "text only". It keeps your data tidy and saves space.

MS SQL Server is a database system made by Microsoft. It uses a language called SQL to talk to the data.

Number types

• INT: whole numbers, such as 5 or 2000. This is the most common choice.
• SMALLINT and TINYINT: smaller whole numbers that use less space.
• BIGINT: very large whole numbers.
• DECIMAL(10,2): exact numbers with a decimal point. The first number is the total digits. The second is the digits after the point. It is good for money.
• FLOAT: approximate decimal numbers. Use it for science-style values, not money.
• BIT: only 0 or 1. It works like a yes or no switch.

Text types

• CHAR(10): text with a fixed length. Short values are padded with spaces.
• VARCHAR(100): text that can be up to 100 characters. It only uses the space it needs.
• NVARCHAR(100): like VARCHAR, but it can store characters from any language, such as Hindi or Chinese.
• VARCHAR(MAX): very long text, such as a blog post.

Date and time types

• DATE: only the date, like 2025-01-31.
• TIME: only the time of day.
• DATETIME2: date and time together, with good precision.

Other useful types

• UNIQUEIDENTIFIER: a long random ID that is very unlikely to repeat.
• VARBINARY(MAX): raw file data, such as a small image.
• XML: data written in XML format.

Example

This command makes a small table. Each column has a type.

CREATE TABLE Customers (
  Id INT,
  FullName NVARCHAR(100),
  Balance DECIMAL(10,2),
  IsActive BIT,
  JoinedOn DATE
);

It creates a table named Customers with five columns, each with its own type.

How to choose

1. Ask what the column will hold: a number, text, a date, or yes/no.
2. Pick the smallest type that fits. Smaller types make the database faster.
3. Use DECIMAL for money, never FLOAT.
4. Use NVARCHAR if people may type in different languages.

Quick recap

• A data type says what kind of data a column can hold.
• Use INT for whole numbers and DECIMAL for money.
• Use VARCHAR or NVARCHAR for text.
• Use DATE or DATETIME2 for dates and times.
• Pick the smallest type that fits your data.