Understanding Column Properties for a SQL Server Table

Problem

I’m creating a table in SQL Server using SSMS and I’m a little overwhelmed with all of the column properties. Can you please explain to me what each property is meant for and the options I should take.

Solution

Designing a table can be a little complicated if you don’t have the correct knowledge of data types, relationships, and even column properties. In this tip, I’ll go over the column properties and provide examples.

To create a new table using SSMS, expand the tree for a database and right click on Tables and select "New Table..." as shown below.

A new window will open and once you enter a Column Name and a Data Type you will see the appropriate Column Properties for that data type as shown below:

Note: Some properties only appear for certain data types

OK, let’s go over each property.

---

(Name)

Name, simply, is the name of the column. You can change the name of the column in the table design view or in the column properties.

---

Allow Nulls

Allow Nulls indicates whether or not the column will allow null values. If the column does not allow null values then some sort of data must be put into this record. You can change this value in the table design view by checking/unchecking the Allow Nulls box or from the column properties.

---

Data Type

Data type, like its name implies, is the type of data stored for the column. You can learn more about data types in thisarticle. You can change the data type in the table design view or the column properties.

---

Default Value or Binding

The Default Value option will allow you to enter a default value in case a value is not specified in an insert statement. For example, let’s say we have three columns in a table named Demo (Column1, Column2, and Column3) and we put a value of 50 in the Default Value or Binding for Column2.

In the query below we are inserting data to Column1 and Column3, but nothing for Column2 so this will get the default value of 50.

INSERT INTO DEMO (Column1, Column3)
VALUES (1, ‘Brady Upton’)

Our result set should be:

By creating a default value, this also creates a default constraint automatically as well as shown below:

---

Length

Length displays the number of characters for character-based data types. For example, nvarchar(50) has a length of 50. You can change the length in table design view or column properties.

---

Collation

Collation can be specified at the instance level, database level, and even down to the column level. This property displays the collating sequence that SQL Server applies to the column. To change the collation using column properties, click the ellipsis and choose the collation:

---

Computed Column Specification

Computed Column Specification displays information about a computed column. A computed column is a logical column that is not physically stored in the table unless the column is marked as Persisted (see Is Persisted below)

• Formula: This field is where you can use formula’s. (See below for an example)
• Is Persisted: This field indicates whether the results of the formula are stored in the database or are calculated each time the column is referenced

Example:

Let’s say we have three columns in a table named Demo (Column1, Column2, and Column3) Column3 is a Computed Column with the formula of Column1 * Column2.

If we were to insert some values into Column1 and Column2, the formula will multiply these values and display the result in Column3.

INSERT INTO DEMO (Column1, Column2)
VALUES (50, 5)

Our result set should be:

---

Condensed Data Type

Condensed Data Type, is almost exactly like Data Type in that it displays information about the field’s data type, in the same format as the SQL CREATE TABLE statement. For example, a field containing a variable-length string with a maximum length of 20 characters would be represented as “varchar(20)”. To change this property, type the value directly.

---

Description

Description is a field that describes the column.

---

Deterministic

Deterministic shows whether the data type of the selected column can be determined with certainty.

---

DTS-published

DTS-published will show you if the column has been published in DTS (SQL Server 2005 only).

---

Full-text Specification

Full Text Specification will only be editable if the column has a full-text index defined.

• (Is Full-text Indexed): If the column has a full-text index this will display “Yes”. You can change the value to “No” if desired.
• Full-text Type Column: If there was a column defined when creating the full-text index it will display in this dropdown. Otherwise, this column will display “None”
• Language: Language displays the language of the word breakers used to index the column. This can be changed via the dropdown.
• Statistical Semantics: If Statistical Semantics was enabled when creating the full-text index it will display a “Yes”. Otherwise this column, will display “No”.

**Statistical Semantic Search

This provides deep insight into unstructured documents stored in SQL Server databases by extracting and indexing statistically relevant key phrases. –MSDN

Below is an example of a column with a full-text index defined:

---

Has Non-SQL Server Subscriber

If this column is being replicated to a non-SQL Server subscriber, such as Oracle or DB2, this will display a “Yes”. This field cannot be manually edited.

---

Identity Specification

Identity Specification displays whether or not the column is an Identity (see below)

• (Is Identity): Displays whether or not this column is an Identity. An Identity column is a unique column that can create a numeric sequence for you based on Identity Seed and Identity Increment.
• Identity Increment: Identity Increment indicates the increment in which the numeric values will use. See example below. The default value is 1.
• Identity Seed: Identity Seed is the value assigned to the first row. The default value is 1. See example below.

In this example, I have a table where I have set Column1 as an Identity column with an Increment of 5 and a Seed of 20.

I’ll run the following INSERT statement to populate the table with data:

INSERT INTO DEMO (Column2, Column3)
VALUES (‘Cheese’, ‘Pizza’)
INSERT INTO DEMO (Column2, Column3)
VALUES (‘Ham’, ‘Pizza’)
INSERT INTO DEMO (Column2, Column3)
VALUES (‘Pepperoni’, ‘Pizza’)
INSERT INTO DEMO (Column2, Column3)
VALUES (‘Sausage’, ‘Pizza’)
INSERT INTO DEMO (Column2, Column3)
VALUES (‘Mushroom’, ‘Pizza’)

Our result should be as shown below, where you can see the column started at 20 (Seed) and increased in increments of 5 (Increment)

---

Indexable

Indexable simply lets the developer know if an index can be applied to the particular column.

---

Is Sparse

Is Sparse was added in SQL Server 2008. Sparse columns are columns that do not take up storage space when a NULL value is used. This type of property would be useful in a situation where the column has more NULL values that non-NULL. See syntax below.

---

Is Columnset

Is columnset goes hand in hand with Sparse columns. When a non-NULL value is entered into a sparse column the columnset column stores this value in an XML format.

• There can only be one columnset column per table
• Columnset columns can’t have constraints or default values
• The XML data type has to be used for this column

CREATE TABLE DEMO
(
Column1 int primary key,
Column2 int sparse,
Column3 xml column_set for all_sparse_columns
)

---

Merge-Published

Shows whether the column is using merge replication. If the column is using merge replication the value will be “Yes”. This property cannot be edited within the column properties.

---

Not for Replication

Not for Replication displays if the original identity value is kept during replication. Replication must be used and the column must be an identity column for this to be “Yes”. This value can be changed if applicable.

---

Replicated

Shows whether or not the column is replicated (SQL Server 2005 only)

---

RowGuid

RowGuid is a property used for a unique identity value (uniqueidentifer data type) and will populate the column with unique Guids.

For example, I have a table with 4 columns with Column4 being a RowGuid.

I’ll run the following INSERT statement to populate the table with data:

INSERT INTO DEMO (Column2, Column3)
VALUES (‘Cheese’, ‘Pizza’)
INSERT INTO DEMO (Column2, Column3)
VALUES (‘Ham’, ‘Pizza’)

Our result set should show the following with Column4 being a unique GUID:

---

Size

Size displays how many bytes each record entered into the column will use.

For example, below is a chart of date and time data types and how much storage space they take use.

Data Type	Storage Space
smalldatetime	4 bytes
datetime	8 bytes
datetime2	6 to 8 bytes
datetimeoffset	8 to 10 bytes
date	3 bytes
time	3 to 5 bytes

If I had a column using the datetime data type, size would display 8:

---

Additional Properties

If you select a decimal data type, two new properties will show up under the (General) section, scale and precision. Scale is the amount of characters that can be displayed to the right of the decimal point and precision is the maximum number of digits for the value.

ref: http://www.mssqltips.com/sqlservertip/2784/understanding-column-properties-for-a-sql-server-table/