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.