How to: Specify Table and Index Options

  • Article

You can specify table options and index options in the table and index definitions in your database project. For more information about the supported table options and their values, see sp_tableoption (Transact-SQL). For more information about supported index options and their values, see sp_indexoption (Transact-SQL).

Important

You cannot specify table or index options in a Data-tier Application (DAC) project because DAC projects do not support the EXEC StoredProcedure construct, except within the body of an object, such as a stored procedure or function. For more information, see the following page on the Microsoft Web site: Features Supported in Data-tier Applications.

To specify table options

  1. Open your database project in Visual Studio Premium.

  2. In Solution Explorer, double-click the table for which you want to specify a table option.

    The Transact-SQL editor appears and displays the definition for the table.

  3. After the table definition in the Transact-SQL script, add the following Transact-SQL statement:

    EXEC sp_tableoption 'TableName', 'OptionName', OptionValue
go

  4. On the File menu, click Save QualifiedTableName**.table.sql**.

    You should now be able to build and deploy the database project. If any tables in your database project use the vardecimal storage format, the database option for vardecimal storage will be enabled automatically. For more information, see sp_db_vardecimal_storage_format (Transact-SQL).

    Note

    Vardecimal storage format requires at least Microsoft SQL Server 2005 Service Pack 2 and is available only in SQL Server 2005 Enterprise, Developer, and Evaluation editions.

To specify index options

  1. Open your database project in Visual Studio Premium.

  2. In Solution Explorer, double-click the index for which you want to specify an index option.

    The Transact-SQL editor appears and displays the definition for the index.

  3. After the index definition in the Transact-SQL script, add the following Transact-SQL statement:

    EXEC sp_indexoption 'IndexName', 'OptionName', OptionValue
go

  4. On the File menu, click Save QualifiedIndexName**.table.sql**.

    You can now build and deploy the database project.

See Also

Tasks

How to: Create a Database Object

How to: Build a Database Project to Generate a Compiled Schema (.dbschema) File