Primary or Unique Key Properties

There are two sets of Primary or Unique Key properties: you can select the object in server explorer to see an abbreviated set or select the object from the dropdown list at the top of the Properties window to see a fuller set.

Properties for object selected in Server Explorer

• Identity Category
  Expands to show the Name property.

• Name
  Shows the name of the selected key.

• Misc Category
  Expands to show the remaining properties.

• Disabled
  Indicates whether the index to which this key belongs is disabled. Disabled indexes do not support searches.

• Is XML Index
  Indicates whether the key is of the XML data type.

• Unique
  Indicates whether the key must have unique values.

Properties for object selected from the Properties window dropdown

• General Category
  Expanded, shows the properties Columns, Is Unique, and Type.

• Columns
  Lists chosen sort orders for the columns in the key or index, and provides access to a dialog box where the sort orders can be defined. To bring the dialog box up, click Columns and then click the ellipsis (…) that appears to the right of the property field.

• Is Unique
  Indicates whether data entered into this index or key must be unique. This is unavailable for XML Indexes.

• Type
  Provides a drop-down list where you can specify whether the item selected in the Selected Primary/Unique Key or Index list is a unique key, a primary key, or an index. For primary keys this field is read-only.

• Identity Category
  Expanded, it shows the property fields for Name and Description.

• Name
  Shows the name of the key or index. When a new one is created, it is given a default name based on the table in the active window in Table Designer. You can change the name at any time.

• Description
  Provides a place to describe the key or index. To write a more detailed description, click Description and then click the ellipsis (…) that appears to the right of the property field. This provides a larger area in which to write text.

• Table Designer Category
  Expanded, shows information for Columns and Create as Clustered.

• Create as Clustered
  Provides an option to make the key or index clustered. Clustered indexes make queries run faster than non-clustered indexes, but creating them in the first place takes longer. For this reason, non-clustered indexes are better for columns that undergo frequent changes.

• Data Space Specification
  Expanded, shows information for (Data Space Type), Filegroup or Partition Scheme Name, and Partition Column List.

• (Data Space Type)
  Indicates whether this index or key belongs to a file group or partition scheme.

• Filegroup or Partition Scheme Name
  Shows the name of the file group or partition scheme on which it is stored.

• Partition Column List
  Displays a comma-separated list of columns that participate in the partition function. Unavailable if Filegroup is selected in the (Data Space Type) field.

• Fill Specification
  Expanded, shows information for Fill Factor and Pad Index.

• Fill Factor
  Specifies what percentage of the index's leaf-level pages the system can fill. Once a page is full the system must split the pages if new data is added, impairing performance.

  • A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table.

  • A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space.

• Pad Index
  Indicates whether intermediate pages in this index are provided the same percentage of empty space (padding) specified in Fill Factor when they grow.

• Ignore Duplicate Keys
  Controls what happens when a row whose key value equals an existing key value is inserted during a bulk insert operation. If you choose:

  • Yes    Query and View Designer will issue a warning, ignore the offending incoming row, and try to insert the remaining rows.

  • No    Query and View Designer will issue an error message and roll back the entire bulk insert operation.

• Included Columns
  Displays a comma-separated list of the names of all the columns that constitute the index key. Sub-key columns can only be specified for nonclustered indexes. This property is hidden for XML indexes.

• Is Disabled
  Indicates whether this index is disabled. This is a read-only property. This property will only be set to Yes if the index has been disabled in an outside tool, such as SQL Workbench.

• Is Full-Text Key
  Indicates whether this index is a full-text key. For more information on full-text keys, see SQL Server Books Online. This property is hidden for XML indexes.

• Page Locks Allowed
  Indicates whether page-level locking is allowed on this index. Allowing or disallowing page-level locking affects database performance.

• Re-compute Statistics
  Indicates whether the underlying database engine computes new statistics when the index is created. Re-computing statistics slows the building of indexes but will very likely improve query performance.

• Row Locks Allowed
  Indicates whether row-level locking is allowed on this index. Allowing or disallowing row-level locking affects database performance.

See Also

Reference

Indexes/Keys Dialog Box

Other Resources

Properties in Visual Database Tools

Working with Keys