Book Image

SQL Server 2014 Development Essentials

By : Basit A. Masood-Al-Farooq
Book Image

SQL Server 2014 Development Essentials

By: Basit A. Masood-Al-Farooq

Overview of this book

Table of Contents (14 chapters)
SQL Server 2014 Development Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Index

The importance of choosing the appropriate data type


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 2014 system data types

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:

  • Exact numeric data types include bigint, int, smallint, tinyint, bit, numeric, money, and smallmoney

  • Approximate numeric data types include float and real

  • Character string data types include char, varchar, and text

  • Unicode character string data types include nchar, nvarchar, and ntext

  • Date and time data types include date, time, smalldatetime, datetime, datetime2, and datetimeoffset

  • Binary string data types include: binary, varbinary, and image

  • Other data types include 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.

Note

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.

Alias data types

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.

Creating and dropping alias data types with SSMS 2014

Perform the following steps to create alias data types:

  1. Launch SQL Server 2014 Management Studio.

  2. In Object Explorer, expand the Databases folder, then the database for which you want to see user-defined types, then Programmability, and then Types.

  3. Right-click on User-Defined Data Types and choose New User-Defined Data Type.

  4. Enter the information about the data type you want to create.

To drop the alias data type, right-click on the data type and choose Delete.

Creating and dropping alias data types using the Transact-SQL DDL statement

In this section, we will use the CREATE TYPE and DROP TYPE Transact-SQL DDL statements to create and drop alias data types.

Creating an alias data type using CREATE TYPE

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;
Dropping an alias data type using DROP TYPE

The following is the basic syntax for the DROP TYPE Transact-SQL DDL statement:

DROP TYPE [schema.]name [;]

The following example T-SQL code drops the alias data type called account_type:

DROP TYPE dbo.account_type

CLR user-defined types

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.