Table Properties - SSMS
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
This topic describes the table properties that are displayed in the Table Properties dialog box in SQL Server Management Studio. For more information about how to display these properties, see View the Table Definition.
In This Topic
The name of the database containing this table.
The name of the current server instance.
The name of the user of this connection.
The date and time that the table was created.
The name of the table.
The schema that owns the table.
Indicates this table is a system table, used by SQL Server to contain internal information. Users should not directly change or reference system tables.
Indicates if the object was created with the ANSI NULLs option set to ON. For more information, see SET ANSI_NULLS (Transact-SQL)
Indicates if the object was created with the quoted identifier option set to ON. For more information, see SET QUOTED_IDENTIFIER (Transact-SQL)
Indicates the lock escalation granularity of the table. For more information about locking in the Database Engine, see SQL Server Transaction Locking and Row Versioning Guide. Possible values are:
This option allows the SQL Server Database Engine to select the lock escalation granularity that is appropriate for the table schema.
- If the table is partitioned, lock escalation will be allowed to the heap or B-tree (HoBT) granularity. In other words, escalation will be allowed to the partition level. After the lock is escalated to the HoBT level, the lock will not be escalated later to TABLE granularity.
- If the table isn't partitioned, the lock escalation is done to the TABLE granularity.
Lock escalation will be done at table-level granularity regardless of whether the table is partitioned or not partitioned. TABLE is the default value.
Prevents lock escalation in most cases. Table-level locks are not completely disallowed. For example, when you are scanning a table that has no clustered index under the serializable isolation level, the Database Engine must take a table lock to protect data integrity.
Table is replicated
Indicates when the table is replicated to another database using SQL Server replication. Possible values are True or False.
Change Tracking Page
Indicates whether change tracking is enabled for the table. The default value is False.
This option is available only when change tracking is enabled for the database.
To enable change tracking, the table must have a primary key, and you must have permission to modify the table. You can configure change tracking by using ALTER TABLE.
Track Columns Updated
Indicates whether the SQL Server Database Engine tracks which columns were updated.
For more information about Change Tracking, see About Change Tracking (SQL Server).
Displays properties of the table related to FileTables. For more information, see FileTables (SQL Server).
FileTable name column collation
The collation that is applied to the Name column in a FileTable. The Name column contains file and directory names.
FileTable directory name
The root folder for the FileTable.
FileTable namespace enabled
When True, this value indicates that the table is a FileTable. If you change this value to False, you are changing the FileTable to an ordinary user table. If you later want to change the table back to a FileTable, the table will have to pass a FileTable consistency check before the conversion succeeds.
Displays the storage related properties of the selected table.
The compression type of the table. This property is only available for tables that are not partitioned. For more information, see Data Compression.
Partitions using page compression
The partition numbers that are using page compression. This property is only available for partitioned tables.
Partitions not compressed
The partition numbers that are not compressed. This property is only available for partitioned tables.
Partitions using row compression
The partition numbers that are using row compression. This property is only available for partitioned tables.
The name of the filegroup that contains the text data for the table.
The name of the filegroup that contains the table.
Table is partitioned
Possible values are True and False.
Specify the name of the FILESTREAM data filegroup if the table has a varbinary(max) column that has the FILESTREAM attribute. The default value is the default FILESTREAM data filegroup.
If the table does not contain FILESTREAM data, the field is blank.
Vardecimal storage format is enabled
When True, this read-only value indicates that decimal and numeric data types are stored by using the vardecimal storage format. To change this option, use the vardecimal storage format option of sp_tableoption. Vardecimal storage format is deprecated. Use ROW compression instead.
The amount of space in megabytes that the indexes occupy in the table. This value does not include XML index space usage for the table. If XML indexes belong to the table, use sp_spaceused instead.
The number of rows in the table.
The amount of space in megabytes that the data occupies in the table.
This section is only available if the table is partitioned. For more information, see Partitioned Tables and Indexes.
The name of the column on which the table is partitioned.
Name of the partition scheme if the table is partitioned. If the table is not partitioned, the field is blank.
Number of partitions
The number of partitions in the table.
FILESTREAM partition scheme
The name of the FILESTREAM partition scheme if the table is partitioned. If the table is not partitioned, the field is blank.
The FILESTREAM partition scheme must be symmetric to the scheme that is specified in the Partition scheme option.
Submit and view feedback for