SQL Server configuration parameters

Configuration parameters identify a database object to be configured. Their corresponding values identify how the object will be stored in the database. The parameters and their configuration strings are grouped together by configuration keywords.

In geodatabases stored in Microsoft SQL Server, configuration parameters and their corresponding configuration strings are used by ArcGIS to define how data is stored. The following lists storage settings available with different parameters:

  • Define the data type for spatial columns.
  • Define how character data is stored.
  • Make keywords available for users in the ArcGIS interface.
  • Provide comments that describe the configuration keyword.

Legacy:

Configuration keywords and parameters are largely legacy functionality. They were implemented when databases required far more intervention and tinkering on the part of the database administrator to get them to perform well. For the most part, you will not need to alter the configuration parameters in your geodatabase.

When you create an enterprise geodatabase, it is populated with default configuration keywords and parameters. In the majority of cases, the default parameter values are sufficient. Possible exceptions include the following:

  • You might want to change the spatial type used for features. If you want the majority of feature classes to use a spatial type different than the default value, change the GEOMETRY_STORAGE parameter for the DEFAULTS keyword. If you want only some feature classes to use a different spatial type, create a custom keyword and include the GEOMETRY_STORAGE parameter set to the spatial type you want, and include the UI_TEXT parameter to make your custom keyword available to users.
  • If you will never need to store multibyte characters in your attribute fields, you could set the UNICODE_STRING parameter for the DEFAULTS keyword to FALSE. New text fields will use the varchar data type instead of nvarchar, which could save a tiny amount of storage space in your database. If data creators in your organization need to create a few tables or feature classes that store large amounts of single-byte text inside a single field in each table, you can create a custom keyword and include the UNICODE_STRING parameter set to FALSE, and include the UI_TEXT parameter to make your custom keyword available to users.
  • If you want to group your data into different file groups so you can create backups of individual file groups, you can create custom keywords for different types of data and set parameters in each keyword that will store all tables and indexes for that type of data in a specific file group. For example, you might want all actively edited data to go to one file group so you can create more frequent backups, so you would create an EDITED_LAYERS keyword that contains business, adds, and deletes table parameters that store all tables and indexes for these layers in the same file group.

Additional parameters exist to control the following, but you are far less likely to use these parameters. They remain for backward compatibility and, therefore, remain in this page to help you understand their intended use when they were added:

  • Whether to cluster an index.
  • How much to fill each index page (FILLFACTOR).
  • How much binary data should be stored in-line to a data page (OUT_OF_ROW).
  • Define how XML documents are stored. Since ArcGIS does not use XML documents directly, it is unlikely you'll need to set these parameters.

The following table is an alphabetic list of all the possible configuration parameters that can be used in a geodatabase in SQL Server. Default values are listed first where applicable. Following the table is a more in-depth explanation of the parameters roughly grouped by related functionality.

Parameter nameDescriptionValues

A_CLUSTER_RASTER

Index type for raster column in Adds table

0 or 1; 1 = clustered 0 = nonclustered

A_CLUSTER_ROWID

Index type for rowid column on Adds table

0 or 1; 1 = clustered 0 = nonclustered

A_CLUSTER_SHAPE

Index type for Adds table shape column

1 or 0; 1 = clustered 0 = nonclustered

A_CLUSTER_STATEID

Index type for Adds table stated column

0 or 1; 1 = clustered 0 = nonclustered

A_CLUSTER_USER

Index type for any user-defined indexes on Adds table

0 or 1; 1 = clustered 0 = nonclustered

A_CLUSTER_XML

Index type for xml doc type column of Adds table

0 or 1; 1 = clustered 0 = nonclustered

A_INDEX_RASTER

Index type for raster column in Adds table

0 or 1; 1 = clustered 0 = nonclustered

A_INDEX_ROWID

Adds table object ID column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

A_INDEX_SHAPE

Adds table spatial column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

A_INDEX_STATEID

Adds table sde_state_id column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

A_INDEX_USER

Adds table index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

A_INDEX_XML

Adds table XML column index table storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

A_OUT_OF_ROW

Determines whether or not data will be stored in row or out of row for varbinary(max) columns in the adds table

If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row.

0 or 1

A_STORAGE

Adds table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

AUX_CLUSTER_COMPOSITE

Index type for primary key

1 or 0; 1 = clustered 0 = nonclustered

AUX_INDEX_COMPOSITE

Raster AUX table composite column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

AUX_STORAGE

Raster AUX table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

B_CLUSTER_RASTER

Index type for raster column in business table

0 or 1; 1 = clustered 0 = nonclustered

B_CLUSTER_ROWID

Index type for rowid column on business table

0 or 1; 1 = clustered 0 = nonclustered

B_CLUSTER_SHAPE

Index type for business table shape column

1 or 0; 1 = clustered 0 = nonclustered

B_CLUSTER_TO_DATE

Specifies the index type on the to_date,archive_rowid column in the history table of a table that is enabled for archiving.

0 or 1; 1 = clustered 0 = nonclustered

B_CLUSTER_USER

Index type for any user-defined indexes on business table

0 or 1; 1 = clustered 0 = nonclustered

B_CLUSTER_XML

Index type for xml doc type column of business table

0 or 1; 1 = clustered 0 = nonclustered

B_INDEX_RASTER

Business table raster column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

B_INDEX_ROWID

Business table object ID column index raster rowid index R<N>_SDE_ROWID_UK storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

B_INDEX_SHAPE

Business table spatial column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

B_INDEX_TO_DATE

Storage parameter info for creating the index R<registration_id>_sde_todate, which is used when updating the history table during an archive operation

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

B_INDEX_USER

Business table user index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

B_INDEX_XML

Business table XML column index table storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

B_OUT_OF_ROW

Determines whether or not data will be stored in row or out of row for varbinary(max) columns in a business table

If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row.

0 or 1

B_STORAGE

Business table and raster attribute table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

BLK_CLUSTER_COMPOSITE

Index type for primary key

1 or 0; 1 = clustered 0 = nonclustered

BLK_INDEX_COMPOSITE

Raster BLK table composite column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

BLK_OUT_OF_ROW

Determines whether or not data will be stored in row or out of row for varbinary(max) columns in the raster blocks table

If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row.

0 or 1

BLK_STORAGE

Raster BLK table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

BND_CLUSTER_COMPOSITE

Index type for primary key

0 or 1; 1 = clustered 0 = nonclustered

BND_CLUSTER_ID

Index type for RASTER_ID, SEQUENCE_NBR columns

0 or 1; 1 = clustered 0 = nonclustered

BND_INDEX_COMPOSITE

Raster BND table composite column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

BND_INDEX_ID

Raster BND table RID column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

BND_STORAGE

Raster BND table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

COLLATION_NAME

Collation of user-defined text columns

Uses the database collation by default, unless other collation is specified

COMMENT

Line used for comments

Description up to 2,048 characters

D_CLUSTER_ALL

Index type for SDE_STATES_ID, SDE_DELETES_ROW_ID, and DELETED_AT columns

0 or 1; 1 = clustered 0 = nonclustered

D_CLUSTER_DELETED_AT

Index type for DELETED_AT column

1 or 0; 1 = clustered 0 = nonclustered

D_INDEX_ALL

FILLFACTOR and location (file group) for composite index on SDE_STATES_ID, SDE_DELETES_ROW_ID, and DELETED_AT columns

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

D_INDEX_DELETED_AT

Deletes table DELETED_AT column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

D_STORAGE

Deletes table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

F_CLUSTER_FID

Index type for FID column

1 or 0; 1 = clustered 0 = nonclustered

F_INDEX_AREA

Feature table area column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

Not used in ArcGIS Pro

F_INDEX_FID

Feature table FID column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

Not used in ArcGIS Pro

F_INDEX_LEN

Feature table length column index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

Not used in ArcGIS Pro

F_OUT_OF_ROW

Determines whether or not data will be stored in row or out of row for varbinary(max) columns in a feature (f) table

If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row.

0 or 1

F_STORAGE

Feature table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

Not used in ArcGIS Pro

GEOMETRY_STORAGE

Indicates storage type for spatial column

GEOMETRY or GEOGRAPHY

GEOM_SRID_CHECK

Adds a check constraint on the geometry column for an SRID value

TRUE or FALSE

I_STORAGE

Defines storage for the i tables, which are used to generate IDs.

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

MVTABLES_MODIFIED_INDEX

Mvtables_modified index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

MVTABLES_MODIFIED_TABLE

Mvtables_modified table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

NUM_DEFAULT_CURSORS

Controls the SQL Server cursor threshold

-1 = All keysets are generated synchronously

0 = All cursor keysets are generated asynchronously

For all other values, the SQL Server Query Optimizer compares the number of expected rows in the cursor set to the number set in the cursor threshold, then builds the keyset asynchronously if it exceeds the cursor threshold number.

PERMISSION_CACHE_THRESHOLD

Controls amount of time (in milliseconds) the database can take to query the sysprotects table

If the PERMISSION_CACHE_THRESHOLD value is exceeded, a temporary table (cache) is created to store a user's permission and is used from then on. As long as PERMISSION_CACHE_THRESHOLD > 0, the permission will be cached. If PERMISSION_CACHE_THRESHOLD = 0, it will not.

0–1,000

250is the default value

RAS_CLUSTER_ID

Index type for primary key of RAS table

1 or 0; 1 = clustered 0 = nonclustered

RAS_INDEX_ID

Raster RAS table RID index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

RAS_STORAGE

Raster RAS table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

RASTER_STORAGE

Defines the raster data storage type

rasterblob or binary

S_CLUSTER_ALL

Index type for primary key (all columns of table)

1 or 0; 1 = clustered 0 = nonclustered

S_CLUSTER_SP_FID

Fill factor and location (file group) for sp_fid column index

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

S_INDEX_ALL

Spatial index table first index storage definition when using binary geometry storage

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

S_INDEX_SP_FID

Spatial index table second index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

S_STORAGE

Represents the spatial index table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

STATES_INDEX

States table storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

STATES_LINEAGES_INDEX

Controls the storage of the index on the SDE_state_lineages table's primary key

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

STATES_LINEAGES_TABLE

State_lineages table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

STATES_TABLE

States table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

UI_NETWORK_TEXT

User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of network configuration

Description up to 2,048 characters

Not used in ArcGIS Pro

UI_TERRAIN_TEXT

User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of terrain configuration

Description up to 2,048 characters

UI_TEXT

User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of associated noncomposite configuration keyword

Description up to 2,048 characters

UI_TOPOLOGY_TEXT

User interface parameter, which indicates associated configuration keyword will appear in the ArcGIS user interface; contains description of topology configuration

Description up to 2,048 characters

UNICODE_STRING

Determines whether Unicode text types will be used or not

If set to TRUE, character fields will be stored in UNICODE compliant data types. For example, if the UNICODE_STRING parameter is set to FALSE, a string data type would be VARCHAR. If UNICODE_STRING is set to TRUE, the data type of the field would be NVARCHAR.

TRUE or FALSE

VERSIONS_INDEX

Version index storage definition

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

VERSIONS_TABLE

Versions table storage definition

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

XML_COLUMN_PATH_IDX

Determines if an XML path index is created

1 or 0

0 = No path index created, 1 = Path index is created.

XML_COLUMN_PRIMARY_IDX

Determines if XML primary index is created

1 or 0

0 = No primary index created, 1 = Primary index created

XML_COLUMN_PROPERTY_IDX

Determines if XML property index is created

0 or 1

0 = No property index created, 1 = Property index created

XML_COLUMN_SCHEMA

Specifies the XML schema to be used for validation

XML schema collection name, up to 128 characters

XML_COLUMN_TYPE

Specifies the XML document type that a column can store

CONTENT or DOCUMENT

XML_COLUMN_VALUE_IDX

Determines if XML value index is created

1 or 0

0 = No value index created, 1 = Value index is created.

XML_DOC_INDEX

Storage clause for xmldoc<n>_pk and xml_doc<n>_ix indexes on the sde_xml_doc<n> table

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

XML_DOC_MODE

Storage type for XML documents

COMPRESSED or UNCOMPRESSED

XML_DOC_OUT_OF_ROW

Determines whether or not XML document BLOB data will be stored in row or out of row; only used for varbinary(max) columns

If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row.

0 or 1

XML_DOC_STORAGE

Storage clause for sde_xml_doc<n> table

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

XML_DOC_UNCOMPRESSED_TYPE

When the XML_DOC_MODE parameter is set to UNCOMPRESSED, the XML_DOC_UNCOMPRESSED_TYPE parameter determines the storage format for XML documents

Since XML_DOC_MODE is set to COMPRESSED by default, the XML_DOC_UNCOMPRESSED_TYPE parameter is not present by default. Possible values: BINARY, TEXT, or UNICODE

XML_IDX_CLUSTER_DOUBLE

Storage clause indicating if the xmlix<n>_db index on the double_tag column of the sde_xml_idx<n> table is clustered

0 or 1; 1 = clustered 0 = nonclustered

XML_IDX_CLUSTER_ID

Storage clause indicating if the xmlix<n>_id index on the id column of the sde_xml_idx<n> table is clustered

0 or 1; 1 = clustered 0 = nonclustered

XML_IDX_CLUSTER_PK

Storage clause indicating if the xmlix<n>_pk index on the xml_key_column identity column of the sde_xml_idx<n> table is clustered

1 or 0; 1 = clustered 0 = nonclustered

XML_IDX_CLUSTER_STRING

Storage clause indicating if the xmlix<n>_st index on the string_tag column of the sde_xml_idx<n> table is clustered

0 or 1; 1 = clustered 0 = nonclustered

XML_IDX_CLUSTER_TAG

Storage clause indicating if the xmlix<n>_tg index on the tag_id column of the sde_xml_idx<n> table is clustered

0 or 1; 1 = clustered 0 = nonclustered

XML_IDX_FULLTEXT_CAT

Name of the full-text catalog in which the contents of the xml_doc_val column in the sde_xml_doc<n> table and the text_tag column of the sde_xml_idx<n> table are indexed

The name you gave the full-text catalog when it was created; the default is SDE_DEFAULT_CAT. If you gave the catalog any other name, you must change the value of this parameter to match it.

XML_IDX_FULLTEXT_LANGUAGE

The language used for linguistic analysis when building the text index on the contents of the xml_doc_val column in the sde_xml_doc<n> table and the text_tag column of the sde_xml_idx<n> table

There is no default value set for this parameter. Consult your DBMS documentation for valid language settings.

XML_IDX_FULLTEXT_TIMESTAMP

Determines whether or not a time stamp column will be added to the sde_xml_idx<n> table

1 or 0

1 = timestamp column will be added, 0 = time stamp column will not be added.

XML_IDX_FULLTEXT_UPDATE_METHOD

Dictates how changes made to the xml_doc_val column in the sde_xml_doc<n> table (the XML document table) and the text_tag column of the sde_xml_idx<n> table (the index table of an XML column) are propagated to the full-text index

CHANGE_TRACKING BACKGROUND or CHANGE_TRACKING MANUAL

XML_IDX_INDEX_DOUBLE

Storage clause for the xmlix<n>_db index on the double_tag column of the sde_xml_idx<n> table

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

XML_IDX_INDEX_ID

Storage clause for the xmlix<n>_id index on the ID column of the xml_idx<n> table

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

XML_IDX_INDEX_PK

Storage clause for xmlix<n>_pk index on the xml_key_column identity column of the sde_xml_idx<n> table

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

XML_IDX_INDEX_STRING

Storage clause for xmlix<n>_st index on the string_tag column of the sde_xml_idx<n> table

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

XML_IDX_INDEX_TAG

Storage clause for the xmlix<n>_tg index on the tag_id column of the sde_xml_idx<n> table

See the Microsoft SQL Server documentation for CREATE INDEX parameters.

XML_IDX_OUT_OF_ROW

Determines whether or not the contents of the text_tag column in the sde_xml_idx<n> table (the index table of an XML column) can be stored in row or out of row; only used for varbinary(max) columns

If set to 0, up to 8,000 bytes can be stored directly in the data page of the table. If set to 1, data will always be stored out of row.

0 or 1

XML_IDX_STORAGE

Storage clause for sde_xml_idx<n> table (the index table of an XML column)

See the Microsoft SQL Server documentation for CREATE TABLE parameters.

Configuration parameter summary

For the XML parameters, <n> refers to the xml_column_id associated with a specific XML column.

There are a number of ways to categorize configuration parameters. Often, a parameter fits into more than one category. For example, the B_CLUSTER_RASTER parameter can be categorized as a business table parameter, a raster parameter, or a clustered index parameter. The following sections describe these categories.

Parameters specific to feature class and raster storage

Business table parameters

The business table is the attribute table of a feature class or nonspatial table. Business table parameters begin with B and define storage for the business table and its indexes. The parameters are as follows:

ParameterDescription

B_CLUSTER_ROWID

Index type for row ID (object ID) column on the business table; 0 = nonclustered index, 1 = clustered index

B_CLUSTER_SHAPE

Index type for shape column of a business table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

B_CLUSTER_USER

Index type for any user-defined indexes on a business table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

B_CLUSTER_XML

Index type for the XML type column of a business table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

B_CLUSTER_RASTER

Index type for the raster column in a business table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

B_INDEX_ROWID

Defines the fillfactor and location (file group) for the row ID column index of a business table

To specify a file group, use the SQL ON statement, for example:

B_INDEX_ROWID "with fillfactor=99 
ON IDXfg"

B_INDEX_SHAPE

Defines the fillfactor and location (file group) for the shape column index of a business table To specify a file group, use the SQL ON statement, for example:

B_INDEX_SHAPE "with fillfactor=99 
ON SHAPEfg"

B_INDEX_USER

Defines the fillfactor and location (file group) for any user defined indexes on a business table

To specify a file group, use the SQL ON statement, for example:

B_INDEX_USER "with fillfactor=99 
ON IDXfg"

B_INDEX_XML

Defines the fillfactor and location (file group) for XML index on a business table

To specify a file group, use the SQL ON statement, for example:

B_INDEX_XML "with fillfactor=99 
ON XMLfg"

B_INDEX_RASTER

Defines the fillfactor and location (file group) for the raster column index on a business table

To specify a file group, use the SQL ON statement, for example:

B_INDEX_RASTER "with fillfactor=99 
ON RASfg"

B_INDEX_TO_DATE

Storage information for creating the index R<registration_id>_sde_todate, which is used when updating the history table during an archive operation

Note:

This parameter does not have a corresponding adds table parameter.

B_OUT_OF_ROW

A value of either 0 or 1

If set to 0, up to 8,000 bytes are stored directly in the data page of the table.

If set to 1, data is always stored out of row.

B_STORAGE

File group location for a business table

Use ON to control location, for example:

B_STORAGE "ON ADDS_FG"

For a nonspatial business table, do one of the following:

  • Change the B_CLUSTER_ROWID parameter's config_string to 1 and the B_CLUSTER_SHAPE parameter's config_string to 0. This will create a clustered index on the object ID field. Any subsequent user-defined indexes you create will be nonclustered.
  • Change the B_CLUSTER_USER parameter's config_string to 1. The first user-defined index created by ArcGIS will be clustered. Change B_CLUSTER_SHAPE to 0.
  • Create the data and change whatever index (or composite indexes) you would like to be clustered.

Adds table parameters

An adds table is a table that stores insert and update edits made against a feature class in a geodatabase that uses traditional versioning. It is almost identical in structure to the business table but has additional columns to track state IDs. Adds table parameters begin with A. The adds table parameters are as follows:

ParameterDescription

A_CLUSTER_ROWID

Index type for row ID column on an adds table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

A_CLUSTER_SHAPE

Index type for shape column of an adds table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

A_CLUSTER_STATEID

Index type for the stated column of an adds table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

Note:

This parameter does not have a corresponding business table parameter.

A_CLUSTER_USER

Index type for any user-defined indexes on an adds table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

A_CLUSTER_XML

Index type for the XML type column of an adds table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

A_CLUSTER_RASTER

Index type for a raster column in an adds table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

A_INDEX_ROWID

Specifies the fillfactor and location (file group) for the row ID column index of an adds table

To specify a file group, use the SQL ON statement, for example:

A_INDEX_ROWID "with fillfactor=99 
ON IDXfg"

A_INDEX_SHAPE

Specifies the fillfactor and location (file group) for the shape column index of an adds table

To specify a file group, use the SQL ON statement, for example:

A_INDEX_SHAPE "with fillfactor=99 
ON SHAPEfg"

A_INDEX_STATEID

Specifies the fillfactor and location (file group) for the state ID column index of an adds table

To specify a file group, use the SQL ON statement, for example:

A_INDEX_STATEID "with fillfactor=99 
ON STATEIDXfg"

Note:

This parameter does not have a corresponding business table parameter.

A_INDEX_USER

Specifies the fillfactor and location (file group) for any user-defined indexes on an adds table

To specify a file group, use the SQL ON statement, for example:

A_INDEX_USER "with fillfactor=99 
ON IDXfg"

A_INDEX_XML

Specifies the fillfactor and location (file group) for an XML index on an adds table

To specify a file group, use the SQL ON statement, for example:

A_INDEX_XML "with fillfactor=99 
ON XMLfg"

A_INDEX_RASTER

Specifies the fillfactor and location (file group) for a raster column index on an adds table

To specify a file group, use the SQL ON statement, for example:

A_INDEX_RASTER "with fillfactor=99 
ON RASfg"

A_OUT_OF_ROW

A value of either 0 or 1

If set to 0, up to 8,000 bytes are stored directly in the data page of the table.

If set to 1, data is always stored out of row.

A_STORAGE

Specifies in which file group adds tables will be created when datasets are registered as versioned

Use ON to control location, for example:

A_STORAGE "ON ADDS_FG"

Nonspatial tables have no shape column, so cluster one of the other indexes.

Deletes table parameters

The deletes table is used to track updates and deletes made to tables that use traditional versioning. The deletes table parameters work the same way as adds table parameters. All deletes table parameters begin with D. They are as follows:

ParameterDescription

D_CLUSTER_ALL

Index type for the index created on the SDE_STATES_ID, SDE_DELETES_ROW_ID, and DELETED_AT columns

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

D_CLUSTER_DELETED_AT

Index type for the index on the DELETED_AT column

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

D_INDEX_ALL

Specifies the fillfactor and location (file group) for a composite index on the SDE_STATE_ID, SDE_DELETES_ROW_ID, and DELETED_AT columns, for example:

D_INDEX_ALL "with fillfactor=99 
ON Deletes_fg"

D_INDEX_DELETED_AT

Specifies the fillfactor and location (file group) for the index on the deleted_at column, for example:

D_INDEX_DELETED_AT "with fillfactor=80
 ON Deletes_fg"

D_STORAGE

Specifies in which file group deletes tables will be created when datasets are registered as versioned

Use ON to control location, for example:

D_STORAGE "ON Deletes_fg"

Raster table parameters

Binary and rasterblob rasters (both are BLOB storage types) in ArcGIS are stored as five separate tables: a band table (SDE_bnd_#), a block table (SDE_blk_#), a raster table (SDE_ras_#), an auxiliary table (SDE_aux_#), and a business table.

Rasters can be stored as embedded catalogs or columns in feature classes or can be stand-alone datasets.

Raster table parameters begin with AUX, BLK, BND, and RAS, which correspond to the raster tables. The parameters that define storage for the business table of the raster are defined by business table parameters.

Of all the raster tables, only the block table will get large.

If using binary rasters, make certain the BND_CLUSTER_COMPOSITE config_string is set to 1 to ensure that a clustered index is generated for the band table.

The raster table parameters are as follows:

ParameterDescription

AUX_CLUSTER_COMPOSITE

Index type for the primary key of the auxiliary table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

AUX_INDEX_COMPOSITE

Specifies the fillfactor and file group location for the primary key index of the auxiliary table, for example:

AUX_INDEX_COMPOSITE	"WITH FILLFACTOR= 90 
ON AUX_FG"

AUX_STORAGE

Specifies the file group location for the auxiliary table

Use ON to specify location, for example:

AUX_STORAGE	"ON AUX_FG"

BLK_CLUSTER_COMPOSITE

Index type for the primary key of the block table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

BLK_INDEX_COMPOSITE

Specifies the file group location for the composite index on the block table

Use ON to specify location, for example:

BLK_INDEX_COMPOSITE	"WITH FILLFACTOR = 95 
ON BLK_FG"

BLK_STORAGE

Specifies the file group location for the block table

Use ON to specify location, for example:

BLK_STORAGE	"ON BLK_FG"

BND_CLUSTER_COMPOSITE

Index type for the primary key index of the band table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

BND_CLUSTER_ID

Index type for the raster_id and sequence_nbr columns of the band table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

BND_INDEX_COMPOSITE

Specifies the fillfactor and file group location for the primary key index of the band table, for example:

BND_INDEX_COMPOSITE	"WITH FILLFACTOR =90 
ON BND_FG"

BND_INDEX_ID

Specifies the fillfactor and file group location for the raster_id and sequence_nbr column index of the band table, for example:

BND_INDEX_ID	"WITH FILLFACTOR = 90 
ON BND_FG"

BND_STORAGE

The file group location for the band table

Use ON to control location, for example:

BND_STORAGE " ON BND_FG"

RAS_CLUSTER_ID

Index type for the primary key of the raster table

If set to 0, a nonclustered index is created. If set to 1, a clustered index is created.

RAS_INDEX_ID

Fillfactor and location (file group) for the primary key index of the raster table

Use ON to control location, for example:

RAS_INDEX_ID	"WITH FILLFACTOR = 85 ON RAS_FG"

RAS_STORAGE

File group location for the raster table

Use ON to control location, for example:

RAS_STORAGE	" ON RAS_FG"

RASTER_STORAGE

Defines which raster data storage type to use:

  • rasterblob (optimized BLOB storage)*
  • binary (BLOB storage)

*ArcGIS 10.4.1 and older clients and ArcGIS Pro 1.3 and older clients cannot create or access raster datasets or mosaic datasets that use this storage type.

The B_STORAGE parameter defines the storage location for raster attribute tables. This table (or tables; there can be multiple such tables) stores attribute values based on cell values in the raster. If you want to store these tables in a different location than feature class business tables, create a raster keyword that specifies different storage information for the raster attribute tables. Instruct data creators to use this keyword when they create raster datasets.

Parameters specific to indexes

Fill factor parameters

These parameters are structured as *_INDEX_*. They allow you to specify the FILLFACTOR argument for that index. The FILLFACTOR argument specifies how full each page in the leaf level of an index should be. SQL Server uses a default value of 0, which means that the leaf pages of an index are almost full, but the nonleaf pages have room for at least two more rows. User-defined fill factors can be between 1 and 100. If the fill factor is 100, all pages are completely full. With a fill factor of 75, each clustered index page starts 75 percent full. Subsequent inserts and updates to that data add to the index page. When the page hits 100 percent capacity, it is full. Any subsequent insert or update to data in that page will split the page. Use FILLFACTOR to balance full index pages and page splits. When a page is split, SQL Server moves approximately 50 percent of the data in the split page to a new page, most likely allocated from a different extent. Page splits will fragment your tables and compromise performance. Setting FILLFACTOR too low creates too many data pages and extents to traverse in a query, thus negatively impacting performance.The following are decision criteria for choosing a fill factor:

  • Is your data read-only? Will it never be edited? If yes, set all fill factors on your data to 100.
  • Will your data be updated frequently? Use the defaults.
  • Will your data be updated occasionally? Pick a range between 75 and 95 percent based on how often you want to defragment your tables.

Monitor fragmented tables and page splits with sys.dm_db_index_physical_stats.

Clustered index parameters

Note:

You cannot separate a clustered index from its table; therefore, for the table parameters described in previous sections, make sure you specify a storage location that is the same for the table's associated clustered indexes.

These parameters are structured as *_CLUSTER_*, and they indicate whether or not a particular index should be clustered (1 = cluster; 0 = nonclustered). Clustered indexes store tabular data at their leaf nodes. The data pages at the clustered index leaf level derive their order from the clustered index key value. This has one important consequence with regard to the configuration parameters: you cannot separate a table from its clustered index. For example, you specify that a feature table's feature ID (FID) index be created on the FeatIdx file group while the feature table should be stored on the Feat file group. The FID index is created as clustered. The configuration settings might look like this:

keywordparameter_nameconfig_string

DEFAULTS

F_INDEX_FID

WITH FILLFACTOR=90 ON FEATIDX

DEFAULTS

F_STORAGE

ON FEAT

In the preceding example, both the feature table and feature table's index will reside on the FeatIdx file group. The feature table is created first, then a primary key constraint is applied to the FID column. The constraint creates a clustered index on the FID column and references the FEATIDX file group in this statement:

ALTER TABLE features.dbo.f4 
 ADD CONSTRAINT f4_pk PRIMARY KEY CLUSTERED (fid) 
 WITH FILLFACTOR=75  
 ON FEATIDX

Therefore, the ON FEAT configuration string is redundant, as the index is created after the table, and the F_INDEX_FID configuration string will overwrite that of F_STORAGE.The next example specifies a file group for the feature table but not for the feature table's index.

keywordparameter_nameconfig_string

DEFAULTS

F_INDEX_FID

WITH FILLFACTOR=90

DEFAULTS

F_STORAGE

ON FEAT

In this case, both the feature table and clustered index on the FID column will reside on the Feat file group. This occurs because the table is created first, and when the ALTER TABLE statement is applied, no ON statement is appended because no such string is listed in the preceding config_string column.

ALTER TABLE features.dbo.f5 
ADD CONSTRAINT f5_pk PRIMARY KEY CLUSTERED (fid) 
WITH FILLFACTOR=75

Parameters for storing text

Text column storage parameters

There are two parameters that affect how text data is stored in the database: UNICODE_STRING and COLLATION_NAME. The UNICODE_STRING parameter determines whether or not text columns use Unicode encoding. By default, this parameter is set to TRUE, meaning all text data created using the DEFAULTS configuration keyword will be stored in Unicode format (UTF-8). If this parameter is set to FALSE, text is stored using the encoding set for the database. If UNICODE_STRING is set to TRUE, the COLLATION_NAME parameter can be used to specify a collation for user-defined text columns that differs from the collation of the database. By default, all character data columns use the default database collation. When a new dataset (such as a table or feature class) is created, the collation specified in COLLATION_NAME is applied to every character data column. If COLLATION_NAME is blank, the database collation is used. If the UNICODE_STRING parameter is set to FALSE, the COLLATION_NAME parameter is ignored.

For the value of the COLLATION_NAME parameter, choose the case-sensitive version of your database collation. This usually means altering the CI in the collation name to CS. If you are unsure, check the SQL Server documentation or execute the following query for a list of collation names:

SELECT * FROM ::fn_helpcollations()
The COLLATION_NAME parameter is especially important for Turkish collation users. To avoid uppercasing and lowercasing issues with the letter i in the Turkish alphabet, Turkish language users should set the COLLATION_NAME parameter to indicate they are using a Turkish collation.

Parameters that affect spatial storage

Spatial index parameters were discussed under the section Parameters specific to indexes. Other parameters that affect spatial data storage are GEOMETRY_STORAGE and GEOM_SRID_CHECK.

GEOMETRY_STORAGE

Geodatabases in SQL Server can use the spatial data storage formats described below. The GEOMETRY_STORAGE parameter indicates which geometry storage method to use for new feature classes.

  • Microsoft SQL Server Geometry type—The Microsoft spatial type for managing spatial data defined by coordinates on an arbitrary plane and for which the curvature of the Earth is not a consideration. This is the default spatial storage method of geodatabases in SQL Server. Keep the GEOMETRY_STORAGE parameter set to GEOMETRY if you want to store your spatial data in this format. If the GEOMETRY_STORAGE parameter is not set, the GEOMETRY type is assumed.
  • Microsoft SQL Server Geography type—The Microsoft spatial type for managing spatial data defined by lat/long coordinates. Use this type when your features span large areas and need to take into consideration the curvature of the Earth. If you want to make this format the default storage type for your geodatabase, set the GEOMETRY_STORAGE parameter to GEOGRAPHY under the DEFAULTS configuration keyword parameter list. If you want to use it for only some datasets, create a custom keyword that contains the GEOMETRY_STORAGE parameter set to GEOGRAPHY and include the UI_TEXT parameter so the keyword will be available to data creators in ArcGIS client apps.

If all the feature classes in your database use the same geometry storage method, set the GEOMETRY_STORAGE parameter once in the DEFAULTS configuration keyword.

GEOM_SRID_CHECK

If you create feature classes in ArcGIS that use the SQL Server geometry type, or you create a spatial table with a SQL Server geometry column using SQL and register the table with the geodatabase, all records in the table must use the same spatial reference ID (SRID).

The database management system does not enforce having a single SRID for all records in a table. Therefore, if you plan to use SQL to edit tables that have a SQL Server geometry column but are registered with the geodatabase, you may want to set the GEOM_SRID_CHECK parameter to TRUE. When this parameter is set to TRUE, ArcGIS adds a check constraint on the geometry column for a SRID value. This ensures that users editing outside of ArcGIS do not add multiple SRIDs to the same table.

Caution:

Setting this parameter to TRUE can impact performance. For that reason, do not set this parameter to TRUE if none of your users are using SQL (or third-party software) to edit spatial tables registered with the geodatabase.

Parameters for XML document storage

Note:

If you do not use XML columns and XML documents in your geodatabase, you do not need to configure these parameters. As there are no longer ArcGIS clients that work directly with XML documents, it is unlikely you need to alter any of these parameters.

If your tables use native SQL Server XML columns (DB_XML), you can set the following parameters:

XML_COLUMN_SCHEMA
XML_COLUMN_TYPE
XML_COLUMN_PRIMARY_IDX
XML_COLUMN_PATH_IDX 
XML_COLUMN_PROPERTY_IDX
XML_COLUMN_VALUE_IDX

XML_COLUMN_SCHEMA specifies a schema collection to be used when adding or altering XML data. XML schema collections enforce schema constraints on XML data.

XML data that is associated with an XML schema collection is referred to as typed XML. The XML_COLUMN_TYPE parameter specifies what type of XML document the column stores; either CONTENT or DOCUMENT. CONTENT is the default value. DOCUMENT should only be used if the XML data has only one top-level element.

XML_COLUMN_PRIMARY_IDX, XML_COLUMN_PATH_IDX, XML_COLUMN_PROPERTY_IDX, and XML_COLUMN_VALUE_IDX determine whether or not primary, path, property, or value indexes will be created on the XML column.

If searches typically examine the entire content of XML documents to see if they contain specific words, or if you don't search XML documents at all, the XML document table will be more heavily used. XML document tables will have three parameters:

XML_DOC_INDEX
XML_DOC_STORAGE
XML_DOC_OUT_OF_ROW

XML_DOC_STORAGE provides the storage string for the table's creation statement. XML_DOC_INDEX has the index fill factor and storage parameters, while XML_DOC_OUT_OF_ROW pertains to storing BLOB data out of line. See the section in this topic, "Text in row parameters", for more information.

If individual elements are frequently searched, the XML document index table will be the most heavily accessed of the XML tables. It has more configuration parameters; they all begin with XML_IDX_.

XML_IDX_CLUSTER_DOUBLE
XML_IDX_CLUSTER_ID
XML_IDX_CLUSTER_PK
XML_IDX_CLUSTER_TAG
XML_IDX_INDEX_DOUBLE
XML_IDX_INDEX_ID
XML_IDX_INDEX_PK
XML_IDX_INDEX_TAG
XML_IDX_STORAGE
XML_IDX_OUT_OF_ROW

The XML_IDX_CLUSTER_* parameters dictate which index of the XML document index table should be clustered. By default, the primary key's index (on the xml_key_column) is clustered.

The following parameters affect both the XML document table and the XML document index table for an XML column. They control how and when the document content is indexed.

XML_IDX_FULLTEXT_CAT
XML_IDX_FULLTEXT_LANGUAGE
XML_IDX_FULLTEXT_TIMESTAMP
XML_IDX_FULLTEXT_UPDATE_METHOD

XML_IDX_FULLTEXT_CAT contains the name of the full-text catalog you created. The default is SDE_DEFAULT_CAT. If you name your full-text catalog something other than SDE_DEFAULT_CAT, you must update the config_string for this parameter.

XML_IDX_FULLTEXT_LANGUAGE represents the language to be used for linguistic analysis when building the text indexes on the XML document's content. A default value is not provided; therefore, the language defined for the SQL Server default full-text language setting is used. If a value is provided, this language will be used for linguistic analysis instead.

XML_IDX_FULLTEXT_TIMESTAMP and XML_IDX_FULLTEXT_UPDATE_METHOD control full-text index maintenance. The update_method parameter dictates how changes made to the document table are propagated to the full-text index. The time stamp parameter, by default (1), will add a time stamp column to the SDE_xml_idx<xml_column_id> table. If set to 0, no such column is added.

If update_method is set to 0 and time stamp is set to 0, no index maintenance is performed, and whenever ArcGIS is instructed to update the full-text index (through SE_xmlindex_update_text_index), the index will be fully populated.

If update_method is set to 0 and time stamp is set to 1, no index maintenance is performed, and ArcGIS will perform an incremental index population of whatever has changed since the last incremental update.

If update_method is set to CHANGE_TRACKING MANUAL, the database maintains a list of changed rows but does not update the index.

If update_method is set to CHANGE_TRACKING BACKGROUND, the database tracks changes and automatically updates the index.

It is recommended that you use the default settings provided with the geodatabase. If your server is unable to service its workload and your only recourse is to change indexing behavior, set change tracking to manual (CHANGE_TRACKING MANUAL).

The next parameters, XML_IDX_INDEX_*, control index fill factor and storage on the SDE_xml_idx<xml_column_id> table. The XML_IDX_TEXT_IN_ROW controls how much of the XML document BLOB can be in-line. As with most text in row settings, it is recommended that you do not change the defaults.

Parameters that affect log file tables and indexes

Log file tables are used by ArcGIS to maintain temporary and persistent sets of selected records.

Session log file tables are always created in tempdb in geodatabases in SQL Server. Although log file table parameters are present in these geodatabases, they are ignored.

Additional configuration parameters

Some parameters do not fit well in a particular category. These are described in this section.

NUM_DEFAULT_CURSORS parameter

The NUM_DEFAULT_CURSORS parameter controls the cursor threshold. It specifies the number of rows in a cursor set for which cursor keysets will be generated asynchronously. The default value of -1 means all keysets are generated synchronously, which is better for smaller cursor sets. If you set it to 0, all cursor keysets are generated asynchronously. If you use a value other than 0 or -1, the no matter log file parameters are set to Query Optimizer compares the number of expected rows in the cursor set to the number set in cursor threshold, then builds the keyset asynchronously if it exceeds the cursor threshold number. Asynchronous population means that you can access rows already in the cursor while the cursor is being populated. With synchronous population, all rows are put in the cursor before any are accessible.

Caution:

It is best not to alter the default value; changes made to this value affect the entire server. It is very difficult to determine how big your average cursor keyset is going to be. Unless you know for certain that changing this value is going to help performance, it is best not to change it.

PERMISSION_CACHE_THRESHOLD

Although still present in the geodatabase, this parameter no longer has much affect on connection performance.

When this parameter was added, the method for determining object privileges in the database required querying a database system table and building a list of the datasets to which the connecting user had access. Connection performance was sometimes slow if the database contained a large number of objects. SQL Server subsequently improved how this information is accessed, thereby rendering this parameter obsolete.

The PERMISSION_CACHE_THRESHOLD builds a temporary table if the initial query of object permission information exceeds the threshold, in milliseconds, that you set for the parameter. The default threshold is 250 milliseconds, the maximum setting is 1,000 milliseconds.

PERMISSION_CACHE_THRESHOLD can be used only in the DEFAULTS parameter group. The temporary table persists for the duration of the connection, so if the connecting user's permissions change during the session, changes won't be seen until the user disconnects and reconnects to the database.

User interface parameters

User interface parameters begin with UI and indicate whether their associated configuration keyword will be available through the ArcGIS user interface and ArcObjects. UI_TEXT is used for noncomposite configuration keywords. UI_TOPOLOGY_TEXT is used for topology keywords. UI_NETWORK_TEXT is used for network keywords. The default configuration keywords that need UI parameters already have them. You would only add one of these parameters if you created your own custom keywords.