A data type determines the type of data that can be stored in a database table column. When you create a table, you must decide on the data type to be used for the column definitions. You can also use data types to define variables and store procedure input and output parameters. You must select a data type for each column or variable appropriate for the data stored in that column or variable. In addition, you must consider storage requirements and choose data types that allow for efficient storage. For example, you should always use tinyint
instead of smallint
, int
, or bigint
if you want to store whole positive integers between 0 and 255. This is because tinyint
is a fixed 1-byte field, whereas smallint
is 2 bytes, int
is 4 bytes, and bigint
is a fixed 8-byte field.
Choosing the right data types for your tables, stored procedures, and variables not only improves performance by ensuring a correct execution plan, but it also improves data integrity by ensuring that the correct data is stored within a database. For example, if you use a datetime
data type for a column of dates, then only valid dates will be stored in this column. However, if you use a character or numeric data type for the column, then eventually, someone will be able to store any type of character or numeric data value in the column that does not represent a date.
SQL Server 2014 supports three basic data types: system data types defined by SQL Server, alias data types based on system data types, and .NET Framework common language runtime (CLR) user-defined data types (UDT).
SQL Server defines a wide variety of system data types that are designed to meet most of your data storage requirements. The system data types are organized into the following categories:
bigint
, int
, smallint
, tinyint
, bit
, numeric
, money
, and smallmoney
float
and real
char
, varchar
, and text
nchar
, nvarchar
, and ntext
date
, time
, smalldatetime
, datetime
, datetime2
, and datetimeoffset
binary
, varbinary
, and image
cursor
, timestamp
, hierarchyid
, uniqueidentifier
, sql_variant
, xml
, table
, and spatial types (geometry
and geography
)Out of these data types, the following data types are not supported in memory-optimized tables and natively compiled stored procedures: datetimeoffset
, geography
, geometry
, hierarchyid
, rowversion
, sql_variant
, UDT
, xml
, varchar(max)
, nvarchar(max)
, image
, xml
, text
, and ntext
. This is because the size of the memory-optimized tables is limited to 8,060 bytes, and they do not support off-row or
large object (LOB) storage.
For more information on the data types supported in memory-optimized tables and natively compiled stored procedures, refer to the Supported Data Types article at http://msdn.microsoft.com/en-us/library/dn133179(v=sql.120).aspx.
In SQL Server, you can create alias data types, also known as user-defined data types. The purpose of the alias data types is to create a custom data type to help ensure data consistency. The alias data types are based on system data types. You can either use SQL Server 2014 Management Studio or the CREATE TYPE
and DROP TYPE
Transact-SQL DDL statements to create and drop alias data types.
Perform the following steps to create alias data types:
Databases
folder, then the database for which you want to see user-defined types, then Programmability
, and then Types
.To drop the alias data type, right-click on the data type and choose Delete.
In this section, we will use the CREATE TYPE
and DROP TYPE
Transact-SQL DDL statements to create and drop alias data types.
The following is the basic syntax for the CREATE TYPE
Transact-SQL DDL statement:
CREATE TYPE [schema.]name FROM base_type[(precision [, scale])] [NULL | NOT NULL] [;]
In the following example, T-SQL code creates the alias data type called account_type
to hold the six-character book type:
CREATE TYPE dbo.account_type FROM char(6) NOT NULL;
CLR user-defined types are data types based on CLR assemblies. A detailed discussion on CLR data types is outside the scope of this chapter. For help with this, refer to the CLR User-Defined Types article at http://msdn.microsoft.com/en-us/library/ms131120(v=sql.120).aspx.
3.138.117.75