Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Understanding Column Properties for a SQL Server Table

MSSQLTips author Brady Upton By:   |   Read Comments (11)   |   Related Tips: More > SQL Server Management Studio
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.

create table using ssms

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:

creating my first table in SQL Server

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 this article. 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.

Default Value or Binding

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:

This also creates a default constraint automatically

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:

This property displays the collating sequence that SQL Server applies to the column

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.

Computed Column Specification

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:

the formula will multiply these values and display the result in Column3

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.

Condensed Data Type

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:

Statistical Semantic Search

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.

Identity Specification displays whether or not the column is an Identity

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)

run the following INSERT statement

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.

Not for Replication

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.

RowGuid is a property used for a unique identity value

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:

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:

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.

Next Steps


Last Update: 10/10/2012


About the author
MSSQLTips author Brady Upton
Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Wednesday, October 10, 2012 - 9:57:06 AM - Sandeep Kataria Read The Tip

Great article Brady. I have never seen such a description in one single place. Thank you taking the time to do this

 

SK


Wednesday, October 10, 2012 - 10:07:45 AM - Ajay Read The Tip

Nice article


Thursday, October 11, 2012 - 3:32:16 AM - Gopalakrishnan Arthanarisamy Read The Tip

Superb Article Brady.


Monday, October 15, 2012 - 6:26:45 AM - JagaaRj Read The Tip

Thank you for such a useful article.


Friday, October 26, 2012 - 11:02:49 AM - Garry M Read The Tip

In the section "Default Value or Binding",  you describe default value,  but not binding.  What is the binding property?


Saturday, November 17, 2012 - 2:05:18 AM - islam Read The Tip

thank alot for this article and keep going


Saturday, November 17, 2012 - 3:00:29 AM - islam Read The Tip

problem

1-i have table include cloumn name 'day', and the cloumn is sort by day every day i insert date by day for some how after period  this cloumn in not sort , every data i put it not after the last day so i had qreat another table with the same cloumn and and put the data

2-could you tell me that how can i copy or move a data from table to another

 

 


Saturday, November 17, 2012 - 4:17:00 AM - islam Read The Tip

can i copy sql query result to sql table


Thursday, December 20, 2012 - 2:28:40 AM - samath seng Read The Tip

in sql server 2008 

example: 2.59 result =3 and 2.49 result 2

which data type that we use?

how I use?

please help me (interface creat table)?


Wednesday, March 27, 2013 - 7:33:09 PM - kerany Read The Tip

hi

to Islame,

 

the query which would to load a query result into a table :

 

  insert into table1(column1,column2)

       select name,occupation from table 2

Best Regards

 


Friday, May 03, 2013 - 11:11:07 AM - Robert J. Good Read The Tip

Thanks for this article! It has been years since I revisited the column properties and was pleasantly suprised to see new properties since SQL Server 2005.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.