Unidad 8. El DDL, Lenguaje de Definición de Datos (V)

Para finalizar tenemos el apartado <opciones> que nos permite definir y/o cambiar muchas opciones de la base de datos. La lista de opciones es muy larga, como podemos observar a continuación, y no entraremos en detalles.

    <opciones>::=

      SET 

      {

          { <optionspec> [ ,...n ] [ WITH  <termination> ] }

          | ALLOW_SNAPSHOT_ISOLATION {ON | OFF }

          | READ_COMMITTED_SNAPSHOT {ON | OFF } [ WITH  <termination> ]

      }

      <optionspec>::= 

      {

          <db_state_option>

        | <db_user_access_option> 

        | <db_update_option>   |  <external_access_option>

        | <cursor_option> 

        | <auto_option> 

        | <sql_option> 

        | <recovery_option> 

        | <database_mirroring_option>

        | <supplemental_logging_option>

        | <service_broker_option>

        | <date_correlation_optimization_option>

        |  <parameterization_option>

      }

      <db_state_option> ::=

          { ONLINE | OFFLINE | EMERGENCY }

    <db_user_access_option> ::=

          { SINGLE_USER | RESTRICTED_USER |  MULTI_USER }

    <db_update_option> ::=

          { READ_ONLY | READ_WRITE }

    <external_access_option> ::=

          DB_CHAINING { ON | OFF }

        | TRUSTWORTHY { ON | OFF }

    }

      <cursor_option> ::= 

      {   CURSOR_CLOSE_ON_COMMIT { ON | OFF } 

        | CURSOR_DEFAULT { LOCAL | GLOBAL } 

      }

    <auto_option> ::= 

      {

          AUTO_CLOSE { ON | OFF } 

        | AUTO_CREATE_STATISTICS { ON | OFF } 

        | AUTO_SHRINK { ON | OFF } 

        | AUTO_UPDATE_STATISTICS { ON | OFF } 

        | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

      }

      <sql_option> ::= 

      {

          ANSI_NULL_DEFAULT { ON | OFF } 

        | ANSI_NULLS { ON | OFF } 

        | ANSI_PADDING { ON | OFF } 

        | ANSI_WARNINGS { ON | OFF } 

        | ARITHABORT { ON | OFF } 

        | CONCAT_NULL_YIELDS_NULL { ON | OFF } 

        | NUMERIC_ROUNDABORT { ON | OFF } 

        | QUOTED_IDENTIFIER { ON | OFF } 

        | RECURSIVE_TRIGGERS { ON | OFF } 

      }

    <recovery_option> ::= 

      {

          RECOVERY { FULL | BULK_LOGGED | SIMPLE } 

        | TORN_PAGE_DETECTION { ON | OFF }

        | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }

      }

    < database_mirroring_option> ::= 

      { <partner_option> | <witness_option> }

          <partner_option> ::=

          PARTNER { = 'partner_server' 

                  |  FAILOVER 

                  |  FORCE_SERVICE_ALLOW_DATA_LOSS

                  |  OFF

                  |  RESUME 

                  |  SAFETY { FULL | OFF }

                  |  SUSPEND

                  |  REDO_QUEUE ( integer { KB | MB | GB } | UNLIMITED )

                  |  TIMEOUT integer

                  }

      <witness_option> ::=

      WITNESS { = 'witness_server' 

                  |  OFF 

                  }

      <supplemental_logging_option> ::=

          SUPPLEMENTAL_LOGGING { ON | OFF }

    <service_broker_option> ::=

      {

          ENABLE_BROKER

        | DISABLE_BROKER

        | NEW_BROKER

        | ERROR_BROKER_CONVERSATIONS

      }

    <date_correlation_optimization_option> ::=

      {

          DATE_CORRELATION_OPTIMIZATION { ON | OFF }

      }

    <parameterization_option> ::=

      {

          PARAMETERIZATION { SIMPLE | FORCED }

      }

    <termination> ::= 

      {

          ROLLBACK AFTER integer [ SECONDS ] 

        | ROLLBACK IMMEDIATE 

        | NO_WAIT

      }  

Se pueden ver más detalles de estas opciones en la ayuda de SQL SERVER buscando ALTER DABATABASE.

Vamos a practicar cambiando la intercalación de Gestión. Realiza el siguiente Ejercicio ALTER DATABASE.

 Cursos Informática Gratuitos