About Tables
You can view details for a table by selecting it in the Object Explorer. The following describes the details associated with each table:
| Use this tab... | To display... |
|---|---|
|
Columns |
Columns included in this table. |
|
Data |
Data associated with the included columns. See Data for more information. |
| Statistics | Includes statistics info for the table. See Create and Alter Table Statistics for more information. |
|
Indexes |
Indexes, primary keys, and unique constraints assigned to this table. |
|
Constraints |
Includes constraints used to enforce integrity for table columns, foreign and primary keys. |
|
Triggers |
Triggers that run automatically when you execute an Insert, Update, or Delete statement on the table. |
| Partitions | Displays partitions of the current table. |
|
Permissions |
Permissions granted to users for this object. |
|
Dependencies |
Dependent table objects and the objects that contain reference to the table. |
|
Extended Properties |
Extended properties you define to store additional metadata for this object. |
|
Script |
SQL statements for the selected object. |
Tips:
-
Right-click the object to see additional actions and tasks available for it.
-
You can change how objects display in the Object Explorer. For example, you can create custom categories or move objects out of existing categories (such as Programmability and Security) in Tools | Options | Explorer | Objects. See Object Options for more information.
-
You can hide system tables from the list of displayed tables in Tools | Options | Database | SQL Server. See SQL Server Options for more information.
- FileTables are marked with
icon in the Object Explorer.
Create and Alter Tables
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.To create or alter a table
- Select the Tables node in the Object Explorer.
- Select one of the following:
- Click
to create a table.
- Select a table and click
to create a table like the selected one.
- Select a table and click
to alter it.
- Click
-
Complete the Columns page. Review the following for additional information:
Note: (SQL Server 2012 and above) Specify the FileTable from the list of table types to create a table, that stores FILESTREAM data and data related to the directories and the files. Such data can be accessed from the other Windows applications. FileTables are marked with
FileTable Description FileTable directory
Specify the root directory for all directories and files to be stored in FileTable. Directory name should be unique among all FileTable directory names in the database.
You can also specify directory at the database level. See Create and Alter Databases for more information.
General Description Collation
Enter the collating sequence to use when sorting rows from a query using values in this column.
Scale
(For decimal data type only) Enter the maximum number of values that can display to the right of the decimal point. Default value or binding
Enter a default value for this column or enter the name of an existing default you want to bind to this column.
(SQL Server 2012 and above) You can enter "next value for <sequence_name>".
Persisted
Select True to store both the formula and the results of the formula. If you select False, only the formula is stored.
Identity
(For bigint, decimal, int, and smallint data types) Select True to specify that this column uses unique values.
Force for replication
Select True to preserve identity values during replication.
Identity increment
(For bigint, decimal, int, and smallint data types where Identity is set to True) Enter a value to increment each new row. For example, if you enter 2 in this field and the initial identity seed is 1, the next row value would be 3.
Identity seed
(For bigint, decimal, int, and smallint data types where Identity is set to True) Enter the initial value to use for the first row.
-
Complete the Storage page. Review the following for additional information:
Storage Description Partitioned
Indicates whether the table is partitioned. To partition a table, right-click the table in the Object Explorer and select Storage | Create Partition. Filegroup
Select a filegroup where the table is stored.
Text filegroup
Select a filegroup where text, ntext, and image columns are stored.
-
Complete the Options page. Review the following for additional information:
Options Description ANSI nulls
Select True to enforce ISO compliance behavior for Equals (=) and Not Equal To (<>) comparison operators for null values.
Quoted identifier
Select True to delimit identifiers using double quotation marks. If selected, you must delimit literals using single quotation marks.
Lock escalation
Select one of the following:
- Table—Locks are escalated on table level regardless if the table is partitioned or not.
- Auto—The locks are escalated automatically based on information that the table is partitioned or not.
- Disable—Locks are not escalated in most cases.
-
Complete the Constraints page. Review the following for additional information:
General
Description
Apply for replication
(For Foreign or Check constraints) Select False to disable the CHECK constraint during replication if the constraint is used by the source database and may prohibit data from being added to the target database.
Check clause
(For a Check constraint) Enter a value for the CHECK constraint. This field displays for Check constraints only. Constraint columns list
(For a Primary or Foreign Key constraint) Click
to select the columns to use in your constraint.
On delete
(For a Foreign Key constraint) Select an action to take when deleting the key that this foreign key references.
On update
(For a Foreign Key constraint) Select an action to take when updating the key that this foreign key references.
Trusted
(For Foreign or Check constraints) Select False to disable rows check before adding or enabling a constraint. When set to True a constraint is regarded as trusted and checking existing rows is performed. Clustered
(For a Primary or Unique Key constraint) Select True to create a clustered index.
Filegroup
(For a Primary or Unique Key constraint) Select the primary filegroup.
Index Options
Description
(For Primary Key and Unique constraints)
Pad index
Select True to apply the fill factor percentage to any newly created index pages.
Fill factor
Specify the percentage of index leaf nodes to fill when creating the index.
Ignore dup key
Select True to rollback the data if a duplicate key exists.
Statistics norecompute
Select True to avoid updating statistics automatically.
Allow row locks
(For Primary and Unique constraints) Select True to allow row locks when accessing the index.
Working with Tables
Check Tables
You can check a single table or multiple tables for any errors using this option. Results for the checked table display the operation performed, message type, and the status of the tables.
To check a table
- Select Tables under the Databases node in the Object Explorer.
-
Right-click one or more tables, and select Tasks | Check Tables.