SQL Server

Some tips for using data types in SQL Server 2016

Some tips for using data types in SQL Server 2016


Use Date/Time data types to store date/time information separately.

In SQL Server 2016 you can store the date and time information separately. For

example, if you need to work with date data only, use the Date data type instead

of datetime or smalldatetime to optimize storage space and simplify date

modifications without a time component.

Consider using datetimeoffset column, if you need to store a time of a day that

has time zone awareness.

Datetimeoffset data type provides more seconds precision and provides time zone

support for globally deployed applications.


Use smallmoney data type instead of money data type, if you need to store

monetary data values from – 214748.3648 through 214748.3647.

The columns with smallmoney data type use only four bytes to store their values,

in comparison with eight bytes used to store the columns with money data types.

For example, if you need to store the monthly employee payments, it might be

possible to use a column with the smallmoney data type instead of money data type.

Consider using the GEOMETRY data type to represent two-dimensional planar data.

SQL Server 2016 supports GEOMETRY data type, which can be used to store

two-dimensional planar data such as points on maps.

Use smalldatetime data type instead of datetime data type, if you need to store

the date and time data from January 1, 1900 through June 6, 2079, with accuracy

to the minute.

The columns with smalldatetime data type use only four bytes to store their values,

in comparison with eight bytes used to store the columns with datetime data types.

For example, if you need to store the employee’s hire date, you can use column

with the smalldatetime data type instead of datetime data type.


Avoid using timestamp column as a primary key.

Timestamp is a data type that exposes automatically generated, unique binary

numbers within a database. Every time that a row with a timestamp column is

modified or inserted, the incremented database rowversion value is inserted

in the timestamp column. This property makes a timestamp column a poor candidate

for keys, especially primary keys.

Use tinyint data type, if you need to store integer data from 0 through 255.

The columns with tinyint data type use only one byte to store their values,

in comparison with two bytes, four bytes and eight bytes used to store smallint,

int and bigint values accordingly. For example, if you design tables for a

small company with 5-7 departments, you can create the departments table with

the DepartmentID tinyint column to store the unique number of each department.


Use smallint data type, if you need to store integer data from -32768 through 32767.

The columns with smallint data type use only two bytes to store their values,

in comparison with four bytes and eight bytes used to store the columns with

int and bigint data types accordingly. For example, if you design tables for

a company with several hundred employees, you can create an employee table

with the EmployeeID smallint column to store the unique number of each employee.

Consider using datetime2 column, if you need to store the fractional seconds.

Datetime2 data type can be considered as an extension of the existing datetime

type that has a larger date range, a larger default fractional precision,

and optional user-specified precision.

Use uniqueidentifier columns if you setting up merge or transactional

replication with updating subscriptions.

Merge replication and transactional replication with updating subscriptions

use uniqueidentifier columns to guarantee that rows are uniquely identified

across multiple copies of the table.

Use int data type, if you need to store integer data from -2147483648

through 2147483647.

The columns with int data type use only four bytes to store their values,

in comparison with eight bytes used to store the columns with bigint data

types. For example, to design tables for a library with more than 32767

books, create a books table with a BookID int column to store the unique

number of each book.

Consider using the GEOGRAPHY data type to represent three-dimensional
geodetic data.

SQL Server 2016 supports GEOGRAPHY data type, which can be used to store
three-dimensional geodetic data such as GPS applications use.

Use varchar(max) data type instead of text data type when the sizes of the

column data entries vary considerably, and the size might exceed 8000 bytes.

SQL Server works with varchar(max) data much more efficient than with the

text data, so you should use varchar(max) data type instead of text data

type whenever possible. The large value data types can store the same size

values as text, ntext, and image values up to 2 GB, but are processed in the

same way as when they store shorter values.

Use the Hierarchyid data type to create tables with a hierarchical structure,

or to reference the hierarchical structure of data in another location.

The Hierarchyid data type was first introduced in SQL Server 2008 and was

optimized for representing trees to make it easier to store and query

hierarchical data.

Consider using the CLR user-defined data types.

By using the CLR user-defined data types, you can create new database data

types in any of the languages supported by the .NET CLR. CLR user-defined

data types can be used in all the contexts where the built-in types are used.

Keep in mind, that the ability to execute CLR code is set to OFF by default.

To execute CLR code you should enable CLR by using the sp_configure stored

procedure.

Try to design your tables in such a way as to maximize the number of rows

that can fit into one data page.

To maximize the number of rows that can fit into one data page, you should

specify the narrowest columns you can. The narrower the columns are, the

lesser the data that is stored, and the faster SQL Server is able to read

and write data.

Use nvarchar(max) data type instead of ntext data type when the sizes of the

column data entries vary considerably, and the size might exceed 4000 characters.

SQL Server works with nvarchar(max) data much more efficient than with the ntext

data, so you should use nvarchar(max) data type instead of ntext data type whenever

possible. The large value data types can store the same size values as text, ntext,

and image values up to 2 GB, but are processed in the same way as when they store

shorter values.

Use varbinary(max) data type instead of image when the sizes of the column

data entries vary considerably, and the size might exceed 8000 bytes.

SQL Server works with varbinary(max) data much more efficient than with

the image data, so you should use varbinary(max) data type instead of

image data type whenever possible.

Use varchar/nvarchar columns instead of text/ntext columns whenever possible.

In SQL Server 2016 text and ntext data stores in the Text/Image pages, while

char, varchar, nchar and nvarchar data stores in Data pages. SQL Server works

with the Data pages much more efficient than with the Text/Image pages. So,

you should use char, varchar, nchar and nvarchar data types instead of text,

ntext data types whenever possible.


Consider using the xml data type to store xml documents and fragments in

SQL Server 2016 database.

SQL Server 2016 supports an xml data type for storing xml documents in table

columns or Transact-SQL variables. You can also use the xml data type as the

stored procedure or function parameters. You can store in the xml data column

or variable the value that size can be up to 2 Gb.

Use binary/varbinary columns instead of image columns whenever possible.

In SQL Server 2016 image data stores in the Text/Image pages, while binary

and varbinary data stores in Data pages. SQL Server works with the Data pages

much more efficient than with the Text/Image pages. So, you should use binary

and varbinary data types instead of image data types whenever possible.

Use char/varchar columns instead of nchar/nvarchar, if you do not need to

store unicode data.

The char/varchar value uses only one byte to store one character, while the

nchar/nvarchar value uses two bytes to store one character. So the char/varchar

columns use two times less space to store data in comparison with nchar/nvarchar

columns.

Try to avoid using sql_variant data type.

The sql_variant data type allows a single column, parameter, or variable

to store data values of different data types, except text, ntext, image,

timestamp, and sql_variant. Because sql_variant data type has some restrictions

(for example, sql_variant columns do not support the IDENTITY property,

cannot be used in a computed column, and so on) and requires more overhead

to process, for best performance you should avoid using sql_variant data type.

Use Table data type instead of temporary tables whenever possible.

A Table data type is a special data type that used for temporary storage of

a set of rows. In comparison with the temporary tables, table variables have

some benefits. For example, table variables are cleaned up automatically at

the end of the stored procedure or batch in which they are defined and table

variables require less locking and logging resources in comparison with the

temporary tables.

Facebooktwitterredditpinterestlinkedinmail