SQL Server Instrucciones DDL CREATE VIEW y CREATE TRIGGER

Introducción

Como se ha indicado en entrega anterior sobre las instrucciones relacionadas con Data Definition Language (DDL) relacionadas con CREATE. En esta ocasion hablaremos de las instrucciones CREATE VIEW y CREATE TRIGGER.

CREATE VIEW

El uso de esta instrucción creará una tabla virtual cuyo contenido (columnas y filas) se define por una consulta. Por lo tanto, permite crear una vista de los datos en una o más tablas de la base de datos.

La sintaxis básica es la siguiente:

CREATE VIEW [ schema_name . ] view_name 
AS select_statement [;]

Donde:

Schema_name – Indica el nombre del esquema al que pertenecerá la vista.

View-name – Indica el nombre de la vista y deben seguir las reglas para los identificadores. 

Select_statement - Es la declaración de la instrucción SELECT que define la vista. La declaración puede utilizar más de una tabla y otras vistas.

Es importante indicar que las columnas de la vista adquieren los mismos nombres que las columnas identificadas de la instrucción SELECT.

Es posible crear vistas que permitan la actualización de datos y la utilización de índices, llevando a cabo las opciones especificas al momento de crear la vista. Estas opciones de definición de la vista usando la sintaxis adecuada debe revisarse en la documentación correspondiente.

Ejemplo 1:

CREATE VIEW Employee_BirthDate

AS  

SELECT FirstName, LastName, BirthDate   

FROM Employee;

En este ejemplo se define una vista de nombre Employee_BirthDate con tres columnas denomindas FirstName, LastName y BirthDate de la tabla Employee.

Ejemplo 2:

Este ejemplo muestra la forma de llevar a cabo la definición de una vista que obtiene unformación de dos tablas.

CREATE VIEW HR.VwHireDate  

AS   

SELECT p.FirstName, p.LastName, e.EntityID, e.HireDate  

FROM HR.Employee e   

JOIN Employee.Person AS p ON e.EntityID = p.EntityID ;  

GO

Se puede observar que se define una vista dentro del esquema HR denominada VwHireDate conteniendo 4 columnas denominadas FirstName, LastName, EntityId y HireDate, seleccionadas de las tablas siguientes; del esquema HR la tabla Employee y des esquema Employee la tabla Person, uniendo sobre la columna EntityID encontrada en ambas tablas. Recuerde que esta definición solo genera el objeto vista, no se ejecuta la consulta. El resultado de la consulta se ejecuta cuando se hace referencia a la vista. 

Como se ha indicado, una vista es una consulta almacenada, de tal forma que para usar la vista, ésta se colocara en una consulta. como se indica a continuación:

SELECT *

FROM HR.VwHireDate;

CREATE TRIGGER

Debe recordarse que un desencadenador es un tipo especial de procedimiento almacenado que se ejecuta automáticamente cuando ocurre un evento en la base de datos o en el servidor. Aquí es importante indicar que, principalmente se conocen los activadores DML que se ejecutan cuando un usuario intenta modificar datos a través de un evento generado por una instrucción del lenguaje de manipulación de datos (DML). Los eventos DML son declaraciones INSERT, UPDATE o DELETE en una tabla o vista. Estos activadores se activan cuando se activa cualquier evento válido, ya sea que las filas de la tabla se vean afectadas o no.

Por otro lado, se tienen los desencadenadores DDL que se ejecutan en respuesta a una variedad de eventos del lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a declaraciones CREATE, ALTER y DROP de Transact-SQL y a ciertos procedimientos almacenados del sistema que realizan operaciones similares a DDL.

Desencadenadores DML

Para los desencadenadores DML se tiene la siguiente sintaxis básica:

CREATE TRIGGER [ schema_name . ]trigger_name   
ON { table_name }   
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement } [ ; ]

Donde:

schema_name - El nombre del esquema al que pertenecerá el desencadenador DML. Estos tienen como ámbito el esquema de la tabla o vista en la que se crean. 

trigger_name - El nombre del desencadenante. Debe seguir las reglas para identificadores.

Table_name - La tabla en la que se ejecuta el desencadenador DML. Esta muchas veces se denomina tabla desencadenante. Especificar el nombre completo de la tabla es opcional. Es importante indicar que no puede definirse activadores DML en tablas temporales locales o globales.

FOR | AFTER - FOR o AFTER se utiliza para especificar que el activador DML se activará solo cuando todas las operaciones especificadas en la instrucción SQL aplicada en la tabla activadora se han iniciado correctamente. Todas las acciones en cascada referenciales y comprobaciones de restricciones también deben tener éxito antes de que se active este activador. 

INSTEAD OF - Como máximo, puede definir un activador INSTEAD OF por instrucción INSERT, UPDATE o DELETE en una tabla o vista.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } – Permite especificar las instrucciones de modificación de datos que activan el activador DML cuando se intenta en la tabla. Se requiere que se especifique al menos una opción. Tome en cuenta que puede utilizar cualquier combinación de estas opciones en cualquier orden para la definición del activador.

sql_statement – contiene las condiciones y acciones desencadenantes. Las condiciones de activación especifican criterios adicionales que determinan si los eventos DML probados provocan que se ejecuten las acciones de activación. Las acciones desencadenantes especificadas en las instrucciones Transact-SQL entran en vigor cuando se intenta la operación. Los desencadenadores pueden incluir cualquier número y tipo de instrucciones Transact-SQL, con excepciones. Recuérdese que un disparador se diseña para verificar o cambiar datos basándose en una modificación de datos de una tabla o una declaración de definición de objeto; no debería devolver datos al usuario. Las instrucciones Transact-SQL en un desencadenador frecuentemente incluyen lenguaje de control de flujo.

Finalmente, los activadores DML utilizan las tablas lógicas (conceptuales) que se identifican como deleted y inserted. Estas son estructuralmente similares a la tabla desencadenante, es decir, la tabla en la que se intenta la acción del usuario. Las tablas deleted y inserted contienen los valores antiguos o los nuevos valores de las filas que pueden manipularse o cambiarse mediante la acción del usuario.

Ejemplo 1:

CREATE TRIGGER NotifyAction  

ON Sales.Customer  

AFTER INSERT, UPDATE   

AS 

RAISERROR ('Notify Customer Relations', 16, 10);  

GO

En este ejemplo, se define un disparador de nombre NotifyAction para la tabla del esquema Sales de nombre Customer despues de una instrucción DML INSERT o UPDATE, al ser ejecutado se enviara un mensaje indicando el mensaje 'Notify Customer Relations'.

Ejemplo 2:

El siguiente disparador evita que se inserte una fila en la tabla Purchasing.PurchaseOrderHeader cuando la calificación crediticia del proveedor especificado se establece en 5 (por debajo del promedio). Este ejemplo esta obtenido de documentación de Microsoft SQL Server.

 

CREATE TRIGGER Purchasing.LowCredit

ON Purchasing.PurchaseOrderHeader  

AFTER INSERT  

AS  

IF (ROWCOUNT_BIG() = 0)

RETURN;

IF EXISTS (SELECT 1  

           FROM inserted AS i   

           JOIN Purchasing.Vendor AS v   

           ON v.BusinessEntityID = i.VendorID  

           WHERE v.CreditRating = 5  

          )  

BEGIN  

RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);  

ROLLBACK TRANSACTION;  

RETURN   

END;  

Se puede observar que se define un disparador dentro del equema Purchasing de nombre LowCredit, para la tabla del mismo esquema de nombre PurchaseOrderHeader que se ejecutara despues de llevar a cabo una instruccion DML INSERT. Hay que hacer notar que se hace uso de la tabla conceptual inserted, esta tabla conceptual contiene los valores insertados en la tabla. Asimismo, se observa que si se cumple la condición indicada se enviara un mensaje y la operación DML INSERT no se confirmará y se deshara.

Es importante mencionar que los activadores DML se utilizan frecuentemente para hacer cumplir las reglas comerciales y la integridad de los datos. 

Desencadenadores DDL

Microsoft SQL Server permite proporcionar la integridad referencial declarativa (DRI) a través de las declaraciones ALTER TABLE y CREATE TABLE. Sin embargo, la DRI no proporciona integridad referencial entre las distintas bases de datos. La integridad referencial se refiere a las reglas sobre las relaciones entre las claves primaria y foránea de las tablas.

Generalmente, un disparador se crea en la actual base de datos donde se encuentre trabajando; sin embargo, el desencadenador puede hacer referencia a objetos fuera de la base de datos actual, indicando el nombre de la base de datos en la referencia.

Es importante indicar que, si se especifica el nombre del esquema del activador para calificar el activador, califique el nombre de la tabla de la misma manera.

Para los disparadores DDL se utiliza la siguiente sintaxis básica:

CREATE] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement } [ ; ]

Donde:

Trigger_name: El nombre del desencadenante. Debe seguir las reglas para identificadores.

DATABASE – Especifica que el alcance del desencadenador DDL aplica a la base de datos actual. Si se especifica, el disparador se activa cada vez que ocurre un event_type o event_group en la base de datos actual.

ALL SERVER – Especifica que el alcance de un desencadenador DDL en el servidor actual. Si se especifica, el disparador se activa cada vez que event_type o event_group ocurre en cualquier lugar del servidor actual.

event_type - El nombre de un evento de lenguaje Transact-SQL que, después del lanzamiento, provoca que se active un desencadenador DDL. Los eventos válidos para desencadenadores DDL se enumeran en Eventos DDL, dentro de la documentación correspondiente.

event_group - El nombre de una agrupación predefinida de eventos del lenguaje Transact-SQL. El desencadenador DDL se activa después del lanzamiento de cualquier evento de lenguaje Transact-SQL que pertenezca a event_group. Los grupos de eventos válidos para desencadenadores DDL se enumeran en Grupos de eventos DDL, en la documentación correspondiente. Una vez que CREATE TRIGGER haya terminado de ejecutarse, event_group también actúa como una macro al agregar los tipos de eventos que cubre a la vista del catálogo sys.trigger_events.

sql_statement – contiene las condiciones y acciones desencadenantes. Los desencadenadores DDL capturan información sobre el evento desencadenante mediante la función EVENTDATA (Transact-SQL).

Los desencadenadores DDL, al igual que los desencadenadores estándar, inician procedimientos almacenados en respuesta a un evento. Se ejecutan principalmente en respuesta a declaraciones del lenguaje de definición de datos (DDL). Los tipos de declaraciones incluyen CREATE, ALTER, DROP, GRANT, DENY, REVOKE y UPDATE STATISTICS. Ciertos procedimientos almacenados del sistema que llevan a cabo operaciones similares a DDL también pueden activar desencadenadores DDL. Los desencadenadores DDL no se activan en respuesta a eventos que afectan a tablas temporales y procedimientos almacenados locales o globales.

Ejemplo:

CREATE TRIGGER safety   

ON DATABASE   

FOR DROP_VIEW  

AS   

IF (@@ROWCOUNT = 0)

RETURN;

RAISERROR ('You must disable Trigger "safety" to remove views!', 10, 1)  

ROLLBACK  

GO  

 En este ejemplo, se aprecia que se define un disparaor a nivel base de datos, de nombre safety para cuando se ejecute una instrucción DDL DROP VIEW, si se cumple la condición no se envia mensaje, en caso contrario, se envia el mensaje indicado. Este disparador impide que se eliminen las vistas de la base de datos, a menos que se elimine el disparador.

Conclusión

La creacion de vistas y disparadores en una base de datos a través de las instrucciones previamente indicadas es una de las actividades más importantes que se realiza cuando se crea o mantiene una base de datos. Recuerdese que los disparadores aplican principalmente en las tablas para ayudar en la consistencia de informción.

Es importante consultar la documentación relacionada con la sintaxis para la creación de las vistas, asi como la correspondiente a la creación de disparadores, para un mejor conocimiento de este tipo de objetos.

SQL Server Instrucciones DDL CREATE TABLE y CREATE INDEX

Introducción

Ya se ha indicado en la entrega anterior sobre las instrucciones relacionadas con Data Definition Language (DDL) relacionadas con CREATE. En esta ocasion hablaremos de las instrucciones CREATE TABLE y CREATE INDEX.

CREATE TABLE

El uso de esta instrucción crea una nueva tabla en Microsoft SQL Server y en Azure SQL Database. La sintaxis de la instrucción es básicamente, la misma. Sin embargo, cuando se crea una tabla, debe indicarse el conjunto de columnas y el tipo de dato que contendrá. 

La sintaxis básica en la siguiente:

CREATE TABLE
{ database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( { <column_definition> } [ ,... n ] )[ ; ]

Donde:

database_name - El nombre de la base de datos en la que se creara la tabla. Si se indica el nombre de base de datos debe especificar una base de datos existente. Si no se especifica, el nombre de base de datos toma por defecto la base de datos actual.

schema_name – Indica el nombre del esquema al que pertenecerá la nueva tabla.

table_name – Indica el nombre de la nueva tabla y deben seguir las reglas para los identificadores.

<column_definition> - column_name <data_type>

column_name – Indica el nombre de la columna que se creará.

<data_type> - Especifica el tipo de datos de la columna, el cual debe ser un tipo valido y especificar las propiedades de longitud y precisión en caso necesario.

La sintaxis incluye el nombre de la tabla, es posible indicar dentro del nombre esta la posibilidad de incluir el nombre de la base de datos y el nombre del esquema, separados por un punto. Asimismo, se puede observar que se debe incluir un par de paréntesis y dentro del par, se debe indicar la definición de las columnas, donde al menos una debe ser definida.

Es importante mencionar que la definición de la llave primaria de una tabla puede ser indicada cuando se lleva a cabo la creación de la tabla, en caso contrario, debe ser definida utilizando la correspondiente instrucción de modificación.

Existen otras opciones dentro de la definición de las tablas, como la especificación de identidad en una columna, o bien la definición de una restricción, un valor por omisión, la asignación de particionamiento en la tabla y otras opciones, para ello será necesario revisar la documentación correspondiente.

Tablas temporales

En Microsoft SQL Server es posible crear tablas de uso temporal que son definidas como locales y como globales. Las tablas temporales locales son visibles solo en la sesión de trabajo actual y las tablas temporales globales son visibles para todas las sesiones de trabajo. 

Para definir este tipo de tablas, se debe antepoer a los nombres de las tablas temporales locales un signo de número único (#table_name) y en los nombres de las tablas temporales globales anteponga con un signo de número doble (##table_name).

Ejemplo 1:

CREATE TABLE test (
    a INT,
    b INT
);

GO 

 

En el ejemplo, se puede observar que se creará la tabla denominda test bajo el esquema dbo, que es el de omisión, en la base de datos actual. Dentro de la tabla se tendrán dos columnas, denominadas a y b, ambas de tipo INT (entero).

Ejemplo 2:

CREATE TABLE HR.Employees

(

    EmployeeID INT NOT NULL,

    Salary Money NOT NULL,

    ValidFrom DATETIME2 NOT NULL,

    ValidTo DATETIME2 NOT NULL

);

GO 

En este ejemplo, se creará una tabla denominada Employees en el esquema denominado HR en la base de datos actual. Contendrá cuatro columnas, denominadas EmployeeID de tipo INT, Salary de tipo Money, ValidFrom y ValidTo ambas de tipo DATETIME2. Asimismo, se puede observar que se ha indicado en todas la columnas se han indicado que tienen que validarse que siempre contengan informacion, no puede haber valores nulos con la clausula NOT NULL en cada definición de columna.

Ejemplo 3:

CREATE TABLE dbo.Sample

(

    col1 INT,

    col2 INT,

    PRIMARY KEY CLUSTERED (col1)

);

GO

En este ejemplo, se creará una tabla denominda Sample en el esquema dbo de la base de datos actual. Contendrá dos columnas identificadas como col1 y col2 ambas de tipo INT, ademas se ha indicado la creación de la llave primaria de tipo agrupado que incluye a la columna col1.

Ejemplo 4:

CREATE TABLE #MyTempTable (

    col1 INT PRIMARY KEY

);

GO

Este ejemplo, creara una tabla temporal denominada #MyTempTable, esta tabla se creará en la base de datos temp. Contendrá una columna denominada col1 de tipo INT, especificando que será asignada como llave primaria.

CREATE INDEX

Se sabe que el índice que se define en una base de datos es una estructura de datos que se utiliza para mejorar la velocidad de las operaciones de consulta, de esta forma, por medio de un identificador único de cada fila de una tabla, permite un rápido acceso a los registros de una tabla dentro de una base de datos. El uso de esta instrucción crea un índice relacional en una tabla o vista, previamente definida.

La sintaxis básica es la siguiente:

CREATE INDEX {index_name] ON {schema_name.table_name} ( {column_name} [,…n] )[;]

Donde:

Index_name - El nombre del índice, que debe ser único dentro de una tabla o vista, pero no necesariamente tienen que ser únicos dentro de una base de datos, y deben seguir las reglas para los identificadores.

schema_name.table_name – El nombre de la tabla al que se generará el indice indicando el esquema donde se encuentra definida.

column_name – nombre de columna o columnas en las que se basa el índice. Debe especificarse dos o más nombres de columnas para crear un índice compuesto sobre los valores combinados en las columnas especificadas.

Es necesario indicar que la sintaxis anterior, creará un índice de tipo no-agrupado, que es el tipo asignado por omisión. Debe recordarse que los índices de tipo agrupado solo pueden definirse uno por tabla, de tal forma debe indicarse que se desea definir uno de este tipo. Asimismo, es posible indicar que el índice sea único, indicando que no se permita que dos filas tengan el mismo valor de clave de índice. De igual forma, recuérdese que un índice agrupado en una vista debe ser único. Ya se ha mencionado que existen diversos tipos de indices, revise la entrada SQL Server Conociendo sobre Indices.

Existen otras opciones para la definición de los índices, usando la sintaxis adecuada, por lo que debe revisarse la documentación correspondiente.

Ejemplo 1:

CREATE INDEX IX_VendorID ON ProductVendor (VendorID);

GO

En este ejemplo, se creará un índice no agrupado denominado IX_VendorID en la tabla ProductVendor del esquema dbo, sobre la columna VendorID.

Ejemplo 2:

CREATE CLUSTERED INDEX PK_Directorio_id ON Directorio (Id);

GO

En el ejemplo se establece la creación de un indice agrupado denominado PK_Directorio_id en la tabla Directorio del esquema dbo, sobre la columna id.

Conclusión

La creacion de tablas e indices en una base de datos a través de las instrucciones previamente indicadas es una de las actividades más importantes que se realiza cuando se crea o mantiene una base de datos. Recuerdese que los índices aplican principalmente en las tablas para ayudar en la consulta de información almacenada en ellas.

Es importante consultar la documentación relacionada con la sintaxis para la creación de los diversos tipos de tablas que se pueden manejar, asi como la correspondiente a la creación de índices.

SQL Server Instrucciones DDL CREATE

Introducción

Ya se ha hablado que las instrucciones relacionadas con Data Definition Language (DDL) que son parte de SQL. El día de hoy hablaremos de las instrucciones CREATE.

La instrucción CREATE se utiliza para llevar a cabo la creación de los objetos que pueden ser manejados por un sistema de gestion de base de datos relacional (RDBMS), como Microsoft SQL Server. En particular dentro de Microsoft SQL Server se pueden definir 68 tipos de objetos, Sin embargo, en esta ocasión, solo indicaremos los objetos de mayor uso.

Estas instrucciones nos ayudan a crear, entre otros, los siguientes objetos:

DATABASE 

Permite llevar a cabo la creación de la base de datos dentro del sistema de gestión.

SCHEMA

Permite llevar a cabo la creación de un esquema dentro de la base de datos.

TABLE

Permite llevar a cabo la creación de una tabla dentro de un esquema de una base de datos.

INDEX

Permite llevar a cabo la creación de un índice usado dentro de una tabla en la base de datos.

VIEW

Permite llevar a cabo la creación de una vista, o tabla virtual resultado de una consulta.

TRIGGER

Permite llevar a cabo la creación de un disparador que puede ser usado por una tabla.

FUNCTION

Puede llevar a cabo la creación de una función que puede ser usada dentro de una base de datos.

PROCEDURE

Puede llevar a cabo la creación de un procedimiento almacenado, que puede ser ejecutado dentro de una base de datos.

LOGIN

Puede llevar a cabo la creación de un inicio de sesión para acceder a los servicios de administración de base de datos.

ROLE

Puede llevar a cabo la creación de un papel para un usuario de una base de datos.

USER

Puede llevar a cabo la creación de un usuario para acceder a una base de datos.

En esta entrega presentare la primera instrucción, ya que es la primera y más importante instrucción, porque crea la base de datos, este objeto es el primero que debe crearse para poder generar y configurar la base de datos que se utilizara por las aplicaciones.

CREATE DATABASE

El primer y más importante objeto que debe crearse dentro de un sistema de gestion de bases de datos (DBMS) es la base de datos, de tal forma que esta instrucción es y debe ser la primera instrucción de un script de base de datos.

El uso de esta instrucción permite que, cuando se usa en Microsoft SQL Server, crear una nueva base de datos, los archivos utilizados y sus grupos de archivos. Sin embargo, cuando se usa en Azure SQL Database, para crear una base de datos única o una base de datos en un grupo elástico, la sintaxis de la instrucción es su forma más básica es la siguiente:

CREATE DATABASE { database_name }[;]

Donde:

database_name – Indicara el nombre de la nueva base de datos, el cual debe ser único y deben seguir las reglas para los identificadores.

Tratándose de una instalación en instalaciones y/o en un ambiente de infraestructura como servicio (IaaS), es posible indicar la localización de los archivos de datos y de registro de transacciones. Esta opción no es válida tratándose de base de datos como servicio (DBaaS)

Asimismo, es posible que se indiquen algunas opciones de configuración de la base de datos, dependiendo de las características requeridas y del ambiente de la base de datos. Se recomienda verificar las opciones en la documentación correspondiente.

Es importante indicar que las opciones de configuración y la localización de los archivos se tomaran por omisión que se hayan definido dentro del sistema de gestión, cuando no se proporcionan dentro del comando al momento de crear la base de datos.

Ejemplo 1:

CREATE DATABASE mytest;
GO

El ejemplo anterior crea la base de datos mytest y crea un archivo de registro primario de datos y de transacciones correspondiente. Debido a que la declaración no tiene elementos de especificación de archivos, el archivo de base de datos principal tiene el tamaño del archivo principal de la base de datos denominada model. El registro de transacciones se establece en el mayor de estos valores: 512 KB o 25 % del tamaño del archivo de datos principal. 

Ejemplo 2:

CREATE DATABASE Ventas
ON
( NAME = Ventas_dat,
    FILENAME = 'D:\DATA\ventadat.mdf’ )
LOG ON
( NAME = Ventas_log,
    FILENAME = 'E:\LOGS\ventalog.ldf' ) ;
GO

En el ejemplo anterior se crea la base de datos Ventas y se creara el archivo primario de registro de datos denominado Ventas_dat en la ubicación y nombre físico 'D:\DATA\ventadat.mdf’ y el registro de transacciones con el nombre Ventas_log en la ubicación y nombre físico 'E:\LOGS\ventalog.ldf', en este caso, aunque se ha definido la especificación de archivo, el archivo de datos tendrá el tamaño del archivo principal de la base de datos model. El registro de transacciones se establece en el mayor de estos valores: 512 KB o 25 % del tamaño del archivo de datos principal. Esta instrucción se puede usar en instalaciones locales o en ambiente de Infraestructura como servicio, donde se puede especificar la ubicación de los archivos, dado que en el caso de Azure SQL Database, la ubicación de los archivos no está permitido.

En el caso de la creación de una base de datos en Microsoft SQL Server y Azure SQL Database, existen otras opciones, que incluyen la indicación del esquema de ordenamiento, el tamaño de los archivos y tipo de crecimiento entre otras opciones, las cuales serán diferentes a las que se tiene como omisión en la base de datos model.

CREATE SCHEMA

El esquema es la estructura de una base de datos para especificar, basándose en el conocimiento del administrador de la base de datos sobre posibles aplicaciones, los hechos que pueden ingresar a la base de datos o aquellos de interés para los posibles usuarios finales.

Crea un esquema en la base de datos. Esta instrucción puede ser usada en Microsoft SQL Server y Azure SQL Database.

La sintaxis básica es la siguiente:

CREATE SCHEMA schema_name  [;]

Donde:

schema_name – Indica el nombre del nuevo esquema y deben seguir las reglas para los identificadores.

Debe indicarse que el nombre de esquema debe ser único dentro de la base de datos. Si no se crean esquemas en la base de datos, se asignará el esquema dbo por omisión.

Ejemplo:

CREATE SCHEMA Ventas;  
GO

El ejemplo anterior crea el esquema Ventas en la base de datos actual. Este nombre de esquema puede ser utilizado en la creación de las tablas, vistas y otros objetos que puedan encerrar los elementos de interés de las aplicaciones.

Conclusion

Las instrucciones de Data Definition Language (DDL) CREATE, que se han indicado se veran en diferentes entregas. En esta entrega se ha visto la creacion de una base de datos y la creacion de un esquema que se utilizara en la base de datos.
Una consideracion adicional que debe tomarse en cuenta es que debe utilizarse la instruccion USE {databasename} para asegurarse de que la instruccion DDL, con excepcion de CREATE DATABASE, que crean los objetos requeridos sean asociados a la base de datos de interes.

Las instrucciones CREATE DATABASE y CREATE SCHEMA principalmente son utilizadas al inicio de un script de generacion de base de datos.

Hablando de tipos de instrucciones de SQL

Introducción

Se sabe que SQL son las siglas de Structured Query Language (Lenguaje estructurado de consultas) y se reconoce como un conjunto de instrucciones que permiten la eficiente administración de las bases de datos de tipo relacional. En 1970's se conocian las bases de datos de modelo jerarquico y de red ademas de las bases de datos de modelo relacional, dichos modelos son enfoques alternos para ver y manipular los datos. Es importante mencionar que sin imporar el modelo de la base de datos, se require de las instrucciones que permitan llevar a cabo la descripción de las estructuras de datos, conocidas como Data Description Language (Lenguaje de Descripción de Datos).

Asimismo, para acceder a los datos en la base de datos, se requiere de las instrucciones conocidas como Data Manipulation Language (Lenguaje de manipulacion de datos). Así, para los modelos jerarquicos y de red, muchas veces se utilizaban instrucciones dentro de los programas desarrollados con los lenguajes de programacón COBOL y/o PL/1. es por ello que SQL ha tenido mucha aceptacion.

Tipos de Instrucciones

Generalmente se conoce el lenguaje de instrucciones dentro de SQL, en función de las funcionalidades que realizan las mismos, las cuales se pueden clasificar en:

• Lenguaje de definición de datos (Data Definition Language o DDL): para generar/realizar cambios en la estructura de una base de datos. Estas instrucciones, cuando se ejecutan, son de naturaleza de confirmación automática y todos los cambios en la base de datos o en los objetos del contenedor se reflejan y guardan inmediatamente. Incluye las siguientes instruccioones:

CREATE

Crea un nuevo objeto (base de datos, tabla, vista, etc.).

ALTER

Modifica la estructura o las propiedades de un objeto (base de datos, tabla, vista, etc.).

DROP

Remueve un objeto existente (base de datos, tabla, vista, etc).

TRUNCATE

Elimina todas las filas contenidas en una tabla. Esta instrucción es usada unicamente para el caso de tabla, require que la tabla existe previamente. 

• Lenguaje de manipulación de datos (Data Manipulation Language o DML): una vez que se crean la base de datos y las tablas mediante las intrucciones DDL, la manipulación de datos se realiza mediante estas instrucciones. La ventaja de usarlas es que, en caso de que se realicen cambios o valores incorrectos, se pueden cambiar y revertir fácilmente. Incluye las siguientes instrucciones:

INSERT

Se utiliza para insertar un conjunto de valores en una tabla.

DELETE

Se utiliza para eliminar un registro o conjunto de registros existente en una tabla, podría basarse en una condición, en caso de no existir una condicion, se borraran todos los registros de la tabla.

UPDATE

Se utiliza para actualizar valores existentes de registros en una tabla, podría basarse en una condición, en caso de no indicar alguna condición, se actualizaran todos los registros de la tabla.

SELECT

Se utiliza para seleccionar un conjunto de registros de una tabla o tablas, podría basarse en una condición, nuevamente, en caso de no haber condiciones se mostraran todos los registros contenidos en la tabla o tablas indicadas.

Es importante mencionar que en muchas ocasiones se indica que la instrucción SELECT  la colocan en una subcategoria denominada Data Query Language DQL, Sin embargo, desde mi particular punto de vista, la manipulacion de datos incluye la consulta y operaciones dentro de la misma, por ello se incluye en esta sección.

• Lenguaje de control de datos (Data Control Language o DCL): gestiona los asuntos y cuestiones relacionados con el control de los datos en cualquier base de datos. Estas instrucciones proporcionan principalmente acceso con privilegios especiales a los usuarios y también se utilizan para especificar las funciones de los usuarios en consecuencia. Incluye las siguientes instrucciones:

GRANT

Se utiliza para proporcionar acceso o privilegios a los usuarios, permitiéndoles realizar una tarea específica.

REVOKE

Se utiliza para recuperar los privilegios de acceso de los usuarios, esto significa cancelar los permisos previamente otorgados o denegados.

• Lenguaje de control de transacciones (Transaction Control Languages o TCL): gestiona los problemas y asuntos relacionados con las transacciones en la base de datos. Se utilizan para inciar y revertir o confirmar los cambios solicitados en la base de datos. Incluye las siguientes instrucciones:

BEGIN [TRANSACTION]

Marca el punto de partida de una transacción, representa un punto en el que los datos a los que hace referencia una conexión son consistentes.

COMMIT [TRANSACTION]

Se utiliza para implementar o aplicar los cambios en una tabla o base de datos.

ROLLBACK [TRANSACTION]

Se utiliza para cancelar o deshacer los cambios realizados en la tabla o base de datos.

SAVE

Guarda temporalmente una transacción, que puede retroceder a ese punto.

Es importante indicar que en Microsoft SQL Server las transacciones se confirman de forma implicta, sin embargo es posible usar estas instrucciones para que las transacciones se confirmen o reviertan de forma explicita, lo cual da un mayor control.

Conclusión

Como se ha podido observar, existen 4 grupos de instrucciones dentro de SQL con un total de 14 instreucciones indicadas. La sintaxis y opciones de cada una de las instrucciones puede variar de acuerdo con el grupo de instrucciones. Por ejemplo, para el caso de las instrucciones CREATE se tienen diversas opciones dependiendo del objeto a crear, para crear una base de datos se toma en cuenta una sintaxis y opciones especificas para la creacion, mientras que para la creacion de una tabla, la sintaxis y opciones de configuración son diferentes a la base de datos.

En otras ocasiones es necesario llevar a cabo una combinacion de las instrucciones relacionadas con la manipulacion de datos y las relacionadas con el control de las transacciones, para obtenre el control de lo que se maneja en la base de datos.

Las instrucciones más utilizadas son las que se indican en la manipulación de datos, generalmente la instrucción que mas se usa es SELECT, que permite obtener los datos de la forma requerida para generar los reportes necesarios. Sin embargo, es necesario conocer las demas instrucciones para incrementar los datos, actualizarlos o eliminar los que ya no son requeridos.

SQL Server Conociendo sobre CHECKPOINT

Introducción

Mucha gente tiende a preguntarse, ¿cómo es que la base de datos se mantiene actualizada, aún después de tener un evento de falla de energía, sin que diera tiempo de guardar los cambios? Es bien sabido que un manejador de base de datos relacional maneja mucha información en memoria, por lo que cuando se lleva a cabo una consulta de información de manera frecuente, la respuesta es casi inmediata. Ahora bien, es posible que la información sea actualizada, ya sea por la modificación de la información ya previamente registrada o el ingreso de nueva información, por lo que se requiere mantener un mecanismo que apoye que la información que se encuentre en memoria, esta sea escrita en los discos, de tal forma que la información este sincronizada. Por ello se utiliza un checkpoint.

¿Cómo funciona el Checkpoint?

Un Checkpoint (punto de control) se crea como un punto bien conocido desde el cual el Motor de base de datos de Microsoft SQL Server puede comenzar aplicando los cambios contenidos en el registro durante la recuperación después de un apagado del servidor o un bloqueo inesperado. Por lo tanto, un punto de control escribe las páginas modificadas que están actualmente en la memoria (conocidas como páginas desactualizadas) y la información del registro de transacciones de la memoria al disco y registra la información en el registro de transacciones. Es por motivos de rendimiento que Motor de base de datos realiza modificaciones en las páginas de la base de datos en la memoria caché del búfer y no escribe estas páginas en el disco después de cada cambio.

Representacion del concepto de Checkpoint 

Tipos de Checkpoint

Es posible indicar que el proceso de punto de control se puede categorizar en los siguientes tipos:

• Automático – Es un proceso en segundo plano realizado por defecto, este tipo está asociado a la opción de configuración del servidor “intervalo de recuperación”. El valor predeterminado se establece en cero, lo que significa un objetivo de un minuto. Esta es una opción de configuración avanzada. Esta opción solo está disponible para entornos locales e IaaS. Para modificar el valor de “intervalo de recuperación” ejecute los siguientes comandos:

sp_configure 'show advanced options', 1 

RECONFIGURE with override 

go 

sp_configure 'recovery interval', '<seconds>' 

go
sp_configure 'show advanced options', 0 
RECONFIGURE with override  

• Interno - esto ocurre en algunos eventos clave en Microsoft SQL Server, como durante un proceso de copia de seguridad de la base de datos o cuando se crea una instantánea de la base de datos, incluido el proceso creado por un comando DBCC CHECKDB. Otro proceso que puede generar un punto de control interno es el cierre de los servicios de Microsoft SQL Server. Es importante indicar que estos procesos internos de puntos de control no pueden ser controlados por un DBA.

• Manual - esto se hace ejecutando el comando T-SQL CHECKPOINT, el comando se ejecuta en la base de datos seleccionada. Cuando ejecuta el comando Transact-SQL CHECKPOINT, el punto de control manual se produce en la conexión de base de datos actual. De forma predeterminada, los puntos de control manuales se ejecutan hasta su finalización. Opcionalmente, el parámetro de duración del punto de control se puede indicar para especificar una cantidad de tiempo solicitada, en segundos, para que se complete el punto de control. El comando para hacer el punto de control manual es:

 

USE [master]; 

GO 

CHECKPOINT <checkpoint_duration_in_Seconds> 

GO; 

 

• Indirecto - a partir de Microsoft SQL Server 2012, esta función se agregó a las propiedades de la base de datos, de esta manera, es posible indicar el tiempo de recuperación objetivo para una base de datos, independientemente de la recuperación objetivo indicada para el servidor. Esta opción solo se puede usar en entornos locales e IaaS. Para especificar el tiempo de recuperación objetivo, use el comando ALTER DATABASE de la siguiente manera:

ALTER DATABASE [Database name]  

        SET TARGET_RECOVERY_TIME = <Duration_Seconds> SECONDS; 

Hay que tener cuidado al usar esta sentencia, el valor predeterminado es cero, lo que significa que el checkpoint ocurre cada minuto, igual a la opción de valor de tiempo de recuperación. Sin embargo, si el tiempo de recuperación objetivo se establece en cero y el valor del intervalo de recuperación se establece mayor a cero, el punto de control automático se toma de la opción de intervalo de recuperación. Finalmente, si el tiempo de recuperación objetivo es mayor que cero, el punto de control indirecto entra en vigor y los valores del intervalo de recuperación no se aplican.

Conclusión

Como resumen, un proceso de Checkpoint escribe todas las páginas sucias, disponibles en el caché del búfer, y los registros de transacciones en el disco, en un archivo de datos. El proceso también registra la información del checkpoint en el registro de transacciones.