SQL Server data types with code examples to create columns

29 November 2022

List of SQL Server data types for creating columns or variables in T-SQL.

Here is the complete list of SQL Server data types, with examples written in T-SQL (Transact-SQL) to declare and instantiate each data type. In fact, in Transact-SQL, there are several data types that can be used to store different types of values in a database.

The different categories of SQL Server data types

Here is a list of the most common data types, grouped by category.

Numeric data

  • INT: fixed-point integer, from -2147483648 to 2147483647
  • BIGINT: fixed-point integer, from -9223372036854775808 to 9223372036854775807
  • SMALLINT: fixed-point integer, from -32768 to 32767
  • TINYINT: fixed-point integer, from 0 to 255
  • DECIMAL: fixed-point decimal, with specified precision and scale
  • NUMERIC: fixed-point decimal, with specified precision and scale
  • FLOAT: floating-point, with specified precision
  • REAL: floating-point, with lower precision than FLOAT

Date and time data

  • DATE: date (year, month, day)
  • TIME: time (hours, minutes, seconds, fractions of seconds)
  • DATETIME: date and time (year, month, day, hours, minutes, seconds)
  • DATETIME2: date and time (year, month, day, hours, minutes, seconds, fractions of seconds) with a larger range than DATETIME
  • SMALLDATETIME: date and time (year, month, day, hours, minutes) with a smaller range than DATETIME
  • DATETIMEOFFSET: date and time with time zone offset (year, month, day, hours, minutes, seconds, fractions of seconds, time zone offset)

String data

  • CHAR: fixed-length string, with specified length
  • VARCHAR: variable-length string, with specified length
  • TEXT: variable-length string with a maximum length of 2147483647 characters
  • NCHAR: fixed-length Unicode string, with specified length
  • NVARCHAR: variable-length Unicode string, with specified length
  • NTEXT: variable-length Unicode string with a maximum length of 1073741823 characters

Binary Data

  • BINARY: fixed-length binary data, with specified length
  • VARBINARY: variable-length binary data, with specified length
  • IMAGE: variable-length binary data with a maximum length of 2147483647 bytes

There are also other less common data types, such as spatial data types and special data types.

Spatial data types

  • GEOMETRY: geometric data, used to represent geographical objects such as points, lines, polygons, etc.
  • GEOGRAPHY: geographical data, used to represent geographical objects on the Earth’s surface, taking into account the Earth’s curvature.

Special data types

  • XML: XML data (eXtensible Markup Language)
  • JSON: JSON (JavaScript Object Notation) data
  • UNIQUEIDENTIFIER: randomly generated unique identifier

It is important to choose the appropriate data type for each column in your SQL Server database, to ensure the efficiency and accuracy of the data stored.

1. The CHAR data type with SQL Server

First, the CHAR data type. It allows for storing fixed-length character strings. The length must be specified in number of characters when declaring the column or variable in a T-SQL script like a stored procedure.

For example, char(10) declares a char column capable of storing up to 10 characters. Here is an example of T-SQL code that creates a table with a char column and inserts a value into it.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  code CHAR(10) NOT NULL
);

INSERT INTO MyTable (id, code) VALUES (1, 'ABC123');

2. VARCHAR data type in T-SQL

VARCHAR data type in T-SQL allows users to store character strings with a variable length. The maximum length must be specified in number of characters when declaring the VARCHAR column. To work with strings, a common challenge in projects is to split a string based on a specific delimiter with T-SQL.

For example, VARCHAR(255) declares a varchar column that can store up to 255 characters. Here is an example of T-SQL code that creates a table with a varchar column and inserts a value.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  nom VARCHAR(255) NOT NULL
);

INSERT INTO MyTable (id, nom) VALUES (1, 'Dupont');

3. T-SQL NCHAR and NVARCHAR data types

NCHAR and NVARCHAR data types in T-SQL are Unicode versions of CHAR and VARCHAR, respectively. They allow for storing characters of different languages. The length must be specified in number of Unicode characters when declaring the column.

To illustrate this with an example, NCHAR(10) declares an nchar column that can store up to 10 Unicode characters. Here is an example of T-SQL code that creates a table with a nvarchar column and inserts a value.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  nom NVARCHAR(255) NOT NULL
);

INSERT INTO MyTable (id, nom)
   VALUES (1, N'Dupont');

4. INT data type in SQL Server

The INT data type is used to store integers. Here is an example of T-SQL code that creates a table with an integer column and inserts a value.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  age INT NOT NULL
);

INSERT INTO MyTable (id, age)
   VALUES (1, 35);

5. SQL Server DECIMAL and NUMERIC data types

SQL Server DECIMAL and NUMERIC data types are data types that are used to store decimal numbers with specified precision and scale. Precision and scale are specified in number of digits when declaring the column.

For example, DECIMAL(5,2) declares a decimal column that can store decimal numbers with 5 digits in total, with 2 after the decimal point. Here is an example of T-SQL code that creates a table with a decimal column and inserts a value: 9.99.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  prix DECIMAL(5,2) NOT NULL
);

INSERT INTO MyTable (id, prix)
VALUES (1, 9.99);

6. The FLOAT data type SQL Server

FLOAT data type is designed to store floating point numbers with approximate precision. Here is an example of T-SQL code to create a table with a FLOAT column and insert a value: 23.5.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  temperature FLOAT NOT NULL
);

INSERT INTO MyTable (id, temperature) VALUES (1, 23.5);

7. The REAL data type

The REAL data type is similar to the float type but with lower precision. Here is an example of T-SQL code that creates a table with a REAL column and inserts a value.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  temperature REAL NOT NULL
);

INSERT INTO MyTable (id, temperature)
   VALUES (1, 23.5);

8. The DATETIME data type in T-SQL

The DATETIME data type allows for storing dates and times, as well as minutes and seconds. Here is an example of T-SQL code that creates a table with a DATETIME column and inserts a value.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  date_naissance DATETIME NOT NULL
);

INSERT INTO MyTable (id, date_naissance)
   VALUES (1, '1999-01-01 00:00:00');

Here is a practical example using DATATIME data types, a tutorial on how to calculate the difference between two dates or timestamps.

Calculate the difference between two dates with SQL Server

9. The BIT data type

BIT, which means byte in English, allows for storing Boolean values (0 or 1). Here is an example of T-SQL code that creates a table with a BIT column and inserts a value.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  est_membre BIT NOT NULL
);

INSERT INTO MyTable (id, est_membre) VALUES (1, 1);

10. BINARY and VARBINARY data types in T-SQL

These are two data types that allow for storing fixed-length and variable-length binary data, respectively. The maximum length must be specified in number of bytes when declaring the column.

To take a practical example, the BINARY(10) statement declares a BINARY column that can store up to 10 bytes of binary data. Here is an example of T-SQL code that creates a table with a VARBINARY column and inserts a value.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  photo VARBINARY(MAX) NOT NULL
);

INSERT INTO MyTable (id, photo)
VALUES (1, 0x89504E470D0A1A0A0000000D49484452000000010000000108060000001FF3FF610000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA86400000A4F6943435050686F746F73686F70204943432070726F66696C65000048656C6C6F2C20776F726C64210A77);

To learn how to manipulate data with the T-SQL operators, here is how to use the SQL SELECT statement to select and filter data.

SQL Server SELECT queries to select and filter data

Here is the continuation of the list of data types in T-SQL with code examples for declaring and instantiating each presented data type.

11. The IMAGE data type in T-SQL

IMAGE is a special data type because it allows for storing images or other large binary data. That is, the IMAGE data type allows for storing a file directly in a column. This is how SharePoint files, for example, which is Microsoft’s document management solution, are stored.

Here is an example of T-SQL code that creates a table with an IMAGE column and inserts a value.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  image IMAGE NOT NULL
);

INSERT INTO MyTable (id, image)
VALUES (1, 0x89504E470D0A1A0A0000000D49484452000000010000000108060000001FF3FF610000000467414D410000B18F0BFC6105000000097048597300000EC300000EC301C76FA86400000A4F6943435050686F746F73686F70204943432070726F66696C65000048656C6C6F2C20776F726C64210A77);

12. The TEXT data type in MS SQL

SQL Server TEXT data type allows for storing variable-length text. Here is an example of T-SQL code that creates a table with a TEXT column and inserts a value.

CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  commentaires TEXT NOT NULL
);

INSERT INTO MyTable (id, commentaires) VALUES (1, 'This is a comment.');

13. The NTEXT data type

Finally, the NTEXT data type, which is a Unicode version of TEXT, allows for storing variable-length Unicode text. Here is an example of SQL Server code to create a table using the NTEXT data type in 2 steps, detailed here:

  1. Create a table with an NTEXT column
  2. Insert a value into the column.
CREATE TABLE MyTable (
  id INT PRIMARY KEY,
  commentaires NTEXT NOT NULL
);

INSERT INTO MyTable (id, commentaires)
   VALUES (1, 'Ceci est un commentaire.');

Best practices for using SQL Server data types

T-SQL (Transact-SQL) data types are categories of values that define the type of data that a table column or variable can contain. They help ensure that data stored in a database is structured consistently and correctly.

Chose the appropriate data type for consistency

It is important to choose the appropriate data type for each column or variable to ensure that data is stored efficiently and that queries and operations on the data are executed correctly.

Select the appropriate data types for efficient storage

For example, it is recommended to use a numeric data type for columns containing only numbers. And a date and time data type for columns containing temporal data, etc. In term of storage used, a numeric column takes much more storage than an alphanumeric one, i.e., a column containing text (numbers and letters, potentially other alphanumeric characters as well).

Define constraints on columns

It is also recommended to define constraints on columns, such as primary or foreign keys, null or unique value constraints, to ensure the quality and integrity of data in the database.

Conclusion on SQL Server data types

Finally, it is important to remember that T-SQL data types are only part of designing effective and high-quality databases. It is also important to properly design tables and relationships between them, choose appropriate indexes and constraints, and properly manage database storage space and performance.

Be the first to comment

Leave a Reply

Your email address will not be published.


*