Creating Your First Table in Microsoft SQL Server

Understanding column properties

You’re almost ready to create your first table. Before doing so, however, you must understand that a table contains one or more columns, which make up the rows of a table. Each column stores very specific information. You can configure certain properties for a given column based on the selected data type, which is a property itself.

The most common property is Allow Nulls. This simply means that you can insert a row into the table without supplying a value. For example, say you have a table that contains FirstName, MiddleName, and LastName. Every person does not have a middle name; therefore, that value should be optional. When designing your table, consider the business logic behind the value when deciding nullability.

The second most common property is Is Identity. It is second because it is only available for most numeric data types. When you set this value for a column, SQL Server automatically generates a number as each row is inserted. You can customize or configure the starting point and how the number will increment using the properties that are available. You will learn how to configure the identity value later in this chapter.

SQL Server 2012 introduces a new autonumber-generating mechanism called Sequence, which is a schema-bound object that generates a sequence of numeric values based on certain options specified during its creation. Chapter 12, “Modifying data,” discusses this topic at length.