Patatas y bueyes

Algo que parece tan trivial como renombrar una base de datos, puede volverse una tarea ardua y complicada en SQL Server.

La primera opción que se suele elegir es ir directamente con el Management Studio (o el Administrador Corporativo) y elegir la opción Renombrar sobre la base de datos. Esta operación dispara una excepción que viene a decir que la base de datos podría no estar bloqueada de forma exclusiva para ejecutar dicha operación.

Otra opción podría ser usar el procedimiento almacenado sp_rename_db:

sp_rename_db nombre_anterior, nombre_nuevo

Dicho procedimiento almacenado se presenta como obsoleto para SQL Server 2005 o superiores, recomendando utilizar alter database de esta forma:

alter database nombre_anterior modify name = nombre_nuevo

Pero nos lanza una excepción similar al paso anterior. La respuesta a este problema consiste en echar a todos los usuarios que puedan estar accediendo a la BD y mantenerles desconectados lo suficiente como para hacer el cambio. Esta operación puede ser muy farragosa, ya que habría que recorrer los pids de sistema y matarlos uno a uno. En un sistema que está muy cargado, simplemente basta con desconectar el cable de red. SQL  nos ofrece una solución que consiste en añadir la opción ROLLBACK IMMEDIATE a la sentencia alter, pero tampoco funciona en la operación de renombrado.

Finalmente, lo que hemos de hacer es una operación consistente en varios pasos:

alter database nombre_anterior set single_user with rollback immediate

alter database nombre_anterior modify name=nuevo_nombre

alter database nuevo_nombre set multi_user

* Traducción libre de un artículo publicado en SQLServerCentral por Andy Warren:

http://www.sqlservercentral.com/articles/Administration/63891/

Vamos a presentar dos procedimientos almacenados muy útiles que se alojan en la base de datos master pero que no son mencionados en los libros en pantalla. Estos SPs son ideales para obtener el espacio utilizado, el número de filas, los índices en las tablas de usuario y demás.

El primer procedimiento, sp_MSForEachDB ejecuta tres comandos contra cada base de datos en el servidor.

|> @command1: primer comando a ejecutar

|> @replacechar: sustituye otro marcador de posición por la interrogación ?

|> @command2: segundo comando a ejecutar

|> @command3: tercer comando a ejecutar

|> @precommand: ejecuta el comando una vez antes de entrar en el bucle

|> @postcommand: ejecuta el comando una vez después de salir del bucle

Cada conjunto de comandos (incluso si sólo consiste en un único comando) se ejecuta como un batch contra las bases de datos, de forma que es más útil capturar el texto de salida que el típico grid de conjunto de resultados. Elegimos entonces que los resultados de la consulta se muestren como texto (pulsando Ctrl + T)

El siguiente código devuelve el espacio utilizado en cada base de datos del servidor:

exec sp_MSForEachDB

@command1 = “use ? exec sp_SpaceUsed”

La salida sería similar a esto:

database_name database_size unallocated space

——————————————————————————————————————————– —————— ——————

master 6.50 MB 1.35 MB

reserved data index_size unused

—————— —————— —————— ——————

2712 KB 1152 KB 1032 KB 528 KB

database_name database_size unallocated space

——————————————————————————————————————————– —————— ——————

tempdb 2.69 MB 1.03 MB

El segundo procedimiento, sp_MSForEachTable acepta siete parámetros:

|> @command1: primer comando a ejecutar

|> @replacechar: sustituye otro marcador de posición por la interrogación ?

|> @command2: segundo comando a ejecutar

|> @command3: tercer comando a ejecutar

|> @wherend: condición where (o cláusula order by)

|> @precommand: ejecuta el comando una vez antes de entrar en el bucle

|> @postcommand: ejecuta el comando una vez después de salir del bucle

Asignando valores a los parámetros que queremos pasar, no tenemos necesidad de pasar valores nulos. El parámetro @replacechar es útil cuando los comandos que deseamos ejecutar contienen una interrogación. El parámetro @whereand permite establecer la salida en base a un filtro.

Podemos incluir una cláusula ORDER BY. El siguiente ejemplo devuelve el número de filas en cada tabla de la base de datos AdventureWorks y los ordena por nombre de tabla:

exec sp_MSForEachTable

@command1 = “Print ‘?’”

, @command2 = “select count(*) from ?”

, @whereand = “ORDER BY 1”

Nos quedaría una salida similar a esta:

[HumanResources].[Department]

———–
16

[HumanResources].[Employee]

———–
290

[HumanResources].[EmployeeAddress]

———–
290

[HumanResources].[EmployeeDepartmentHistory]

———–
296

* Traducción del artículo “Iterating through tables and databases in SQL Server” de Arthur Fuller publicado en www.sqlservercentral.com “

Con la siguiente instrucción SQL podemos obtener todos los procedimientos almacenados que existen en nuestra base de datos:

USE MIBD;

GO

SELECT * FROM sys.all_objects

WHERE ([type] = ‘P’ OR [type] = ‘X’ OR [type] = ‘PC’)

ORDER BY [name];

GO

Esta codificación está basada en el filtro type. Mediante la siguiente tabla podemos determinar el tipo de objetos que deseamos que devuelva y de esta forma ajusta la cláusula WHERE para que devuelva sólo aquellos tipos que necesitamos. En ella únicamente podemos ver vistas, funciones y procedimientos, aunque disponemos de otra serie de tipos de objeto por los que filtrar:

Iniciales de tipo	Tipo de objeto devuelto
FN	Función SQL Escalar
IF	Función inline que devuelve una tabla
P	Procedimiento almacenado SQL
PC	Procedimiento almacenado CLR
TF	Función que devuelve una tabla SQL
V	Vista
X	Procedimiento almacenado Extendido

En un momento dado nos puede interesar conocer que SPs son los que han sido originalmente creados por Microsoft. Eso se define por el campo is_ms_shipedd. Cuando este campo toma como valor 1, el SP ha sido creado por MS. Con este filtrado podemos entonces conocer cuales han sido los SPs creados por usuario:

USE MIBD;

GO

SELECT * FROM sys.all_objects

WHERE ([type] = ‘P’ OR [type] = ‘X’ OR [type] = ‘PC’)

AND [is_ms_shipped] = 0

ORDER BY [name];

GO

* Traducción libre del artículo “Finding and Listing ALL Procedures, Functions, and Views in SQL Server 2005” de James Rea publicado en www.sqlservercentral.com

Oracle puede manejar objetos con características similares a las de cualquier lenguaje orientado a objetos. Dichos objetos pueden encontrarse en diferentes lugares en una aplicación Oracle: pueden estar almacenados en una tabla, declararse localmente en un bloque PL/SQL o mantenerse en una caché del lado del cliente. Dependiendo de donde esté localizado, un objeto tiene diferentes propiedades y se puede realizar con diferentes operaciones. Normalmente, una aplicación desplazará los objetos entre esas ubicaciones como parte de su ejecución.

Objetos transitorios	Objetos persistentes	Objetos cliente
Contienen instancias que son locales al bloque o paquete PL/SQL	Contienen instancias que se almacenan en una tabla de la BD	Contienen instancias que se almacenan en la caché cliente de un programa OCI o Pro*C
Se manipulan usando instrucciones PL/SQL	Se manipulan usando instrucciones SQL	Se manipulan usando instrucciones OCI o Pro*C
No pueden ser referenciados	Pueden ser referenciados (sólo objetos de fila)	Pueden ser referenciados
Existen mientras que la variable PL/SQL que les hace referencia sea visible	Existen hasta que se borran explícitamente de la base de datos	Existen hasta que son borrados de la caché

Objeto transitorio

Un objeto transitorio es local de un bloque PL/SQL y se deasigna cuando la variable donde se almacena deja de ser visible. Los objetos transitorios no se almacenan en la BD y su existencia no puede ser más larga que la de una sesión de base de datos. La siguiente sesión PL/SQL ilustra varios objetos transitorios:

CREATE OR REPLACE TYPE Punto AS OBJECT(

— Un punto se representa mediante su localización en

— una cuadrícula cartesiana X-Y

x NUMBER,

Y NUMBER,

— Devuelve una cadena ‘(x,y)’

MEMBER FUNCTION toString RETURN VARCHAR2,

PRAGMA RESTRICT_REFERENCES(toString, RNDS, WNDS, RNPS, WNPS)

);

/

CREATE OR REPLACE TYPE BODY Punto AS

— Devuelve una cadena ‘(x,y)’

MEMBER FUNCTION toString RETURN VARCHAR2 IS

v_Result VARCHAR2(20);

v_xString VARCHAR2(8) := SUBSTR(TO_CHAR(x),1,8);

v_yString VARCHAR2(8) := SUBSTR(TO_CHAR(y),1,8);

BEGIN

v_Result:='(‘ || v_xString || ‘, ‘;

v_Result:=v_Result || v_yString || ‘)’;

RETURN v_Result;

END toString;

END;

/

DECLARE v_Punto1 Punto := Punto(0,0);

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Punto 1 es ‘ || v_Punto1.toString);

— v_Punto1 ya no será visible después de este bloque y,

— por tanto se deasignará.

END;

Punto 1 es (0, 0)

Sentencia procesada.

CREATE OR REPLACE PACKAGE PkgPunto AS

v_Punto2 Punto := Punto(-7,-8);

END PkgPunto;

/

— Dado que v_Punto2 está en una cabecera de paquete, se conservará

— hasta el final de la sesión. Por ejemplo, podemos hacer referencia

— a esta variable en otro bloque anónimo:

BEGIN

DBMS_OUTPUT.PUT_LINE(

‘Punto 2 es’ || PkgPunto.v_Punto2.toString);

END;

Punto 2 es(-7, -8)

Sentencia procesada.

Los objetos transitorios se manipulan usando instrucciones PL/SQL. Las instrucciones SQL se usan para almacenar objetos transitorios en la base de datos que así se convierten en persistentes.

Objetos persistentes

Es un objeto que se almacena en la BD y está disponible hasta que se borra. Estos objetos se almacenan al igual que los tipos escalares predefinidos. Hay dos formas distintas de almacenar un objeto en una tabla: como objeto de fila o de columna.

Un objeto de columna se almacena como una columna de BD, al igual que los tipos escalares. Para crear una tabla con un objeto de columna, simplemente se usa el tipo del objeto para el tipo de columna en la instrucción de creación de la tabla. Una tabla puede tener una combinación de tipos escalares y de objetos de columna. Por ejemplo, podemos crear una tabla que almacene puntos de la siguiente forma:

CREATE TABLE puntos(

clave NUMBER PRIMARY KEY,

punto Punto);

Ahora veamos como podemos insertar puntos en esta tabla:

INSERT INTO puntos VALUES(1,Punto(1,1));

INSERT INTO puntos VALUES(2,Punto(1,-1));

INSERT INTO puntos VALUES(3,Punto(-1,-1));

INSERT INTO puntos VALUES(4,Punto(-1,1));

Observar que se usa el constructor para insertar el tipo de objeto. Las tablas con objetos de columna no pueden visualizarse en el modelo relacional. Aun así, SQL*Plus dará formatos en las consultas a los objetos de columna de forma que sean legibles:

SELECT * FROM PUNTOS WHERE CLAVE=1;

CLAVE PUNTO(X, Y)

————- ———-

1 PUNTO(1, 1)

Objetos de fila

Por otra parte, un objeto de fila ocupa una fila completa de la tabla. Dicha fila contiene únicamente el objeto y no tiene ninguna otra columna. Una tabla definida de este modo se conoce como tabla de objetos y se crea usando la sintaxis siguiente:

CREATE TABLE nombre_tabla OF tipo_objeto;

Por ejemplo, una tabla de filas de objeto de tipo punto:

CREATE TABLE tabladepuntos OF Punto;

Cada fila de una tabla de objetos contiene una instancia de su tipo de objeto, por lo que sólo se pueden insertar objetos de dicho tipo en la tabla. El siguiente ejemplo muestra una instrucción INSERT de ejemplo para la tabla. Observar el uso del constructor para el tipo de objeto:

INSERT INTO tabladepuntos VALUES(Punto(1,1));

Una tabla de objetos es muy similar a una tabla relacional estándar. De hecho, todas las operaciones relacionales funcionarán en ella. Por ejemplo, también podemos realizar inserciones en la tabla de la siguiente forma:

INSERT INTO tabladepuntos(x,y) VALUES(-1,1);

La lista de columnas es opcional, de igual modo que en una operación de inserción en una tabla relacional.

* Transcrito del libro Programación Avanzada con PL/SQL de Scott Urman

Un programa o proceso contiene al menos un subproceso y puede contener más. En ese caso puede ejecutar varias tareas al mismo tiempo. Estos subprocesos podemos crearlos, ejecutarlos, bloquearlos, suspenderlos, reanudarlos y terminarlos. Si suspendemos un subproceso podremos reanudarlo. Tenemos dos tipos de subprocesos: en primer plano (por defecto) o en segundo plano. Estos últimos terminan automáticamente cuando acaban los procesos en primer plano.

Para esto, .NET incluye el espacio de nombres System.Threading donde se encuentra la clase Thread que contiene lo necesario para poder crear y administrar nuestros propios hilos de ejecución.

Si queremos crear un subproceso debemos usar el constructor proporcionado por la clase Thread(ThreadStart comienzo). ThreadStart es un delegado de C# que contiene el nombre del método que hay que ejecutar para comenzar el subproceso. El tipo devuelto debe ser void y no debe tener ningún argumento. Una vez creado el subproceso lo ejecutamos con el método Start y terminará cuando finalice el método especificado en comienzo.

Con la propiedad IsAlive podemos saber si un proceso aun está en ejecución. Con el método Join() de la clase Thread unimos los subprocesos para que esperen a su finalización y así se finalice el hilo principal (nuestro programa). Veamos el siguiente listado:

using System;

using System.Threading;

class Hilos

{

public int contador;

public Thread hilo;

public Hilos(string nombre)

{

contador = 0;

hilo = new Thread(new ThreadStart(this.ejecucion));

hilo.Name = nombre;

hilo.Start();

}

void ejecucion()

{

Console.WriteLine(«Iniciando subproceso {0}», hilo.Name);

do

{

Thread.Sleep(300);

Console.WriteLine(«Subproceso: {0}. Contador = {1}.», hilo.Name, contador);

contador++;

} while (contador <= 3);

Console.WriteLine(«Terminando subproceso {0}», hilo.Name);

}

}

class Multiproceso1

{

public static void Main()

{

Console.WriteLine(«INICIANDO PROGRAMA PRINCIPAL»);

Hilos h1 = new Hilos(«Primero»);

Hilos h2 = new Hilos(«Segundo»);

Hilos h3 = new Hilos(«Tercero»);

do

{

Console.WriteLine(«*»);

Thread.Sleep(1000);

} while ((h1.hilo.IsAlive) || (h2.hilo.IsAlive) || (h3.hilo.IsAlive));

Console.WriteLine(«FINALIZANDO PROGRAMA PRINCIPAL»);

Console.ReadLine();

}

}

La clase Hilos contiene un contador y un objeto Thread, además del constructor y el método ejecución(). En el constructor inicializamos el contador que se va a usar para finalizar los subprocesos al llegar a un valor y creamos un hilo usando para ello la sintaxis antes explicada.

Como se puede observar, usamos el constructor del thread y el delegado con el nombre del método a ejecutar por el subproceso, en este caso, el método ejecución. Thread.Sleep() duerme la ejecución durante el tiempo expresado entre paréntesis. En el programa principal, creamos los tres subprocesos y esperamos a que finalicen mediante la comprobación realizada mediante IsAlive. De esta forma es sencillo comprobar y sincronizar los subprocesos para esperar a que todos terminen. En este caso usamos un simple OR cortocircuitado de forma que mientras haya alguno de los subprocesos activos no finalice el programa.

Antes hemos comentado la posibilidad de usar Join(). Podemos eliminar el bucle en nuestro programa e incluir estas sentencias:

h1.hilo.Join();

h2.hilo.Join();

h3.hilo.Join();

El método Join() puede aceptar como parámetro el tiempo máximo que debe esperar hasta que termine el subproceso «unido».

Otra propiedad importante es IsBackground. Si queremos especificar o convertir un proceso a segundo plano, no tenemos más que asignar un valor true a la propiedad IsBackground. El hecho de que un proceso esté en primer o segundo plano no tiene por qué afectar a su prioridad salvo que se la asignemos nosotros desde el sistema operativo. Sin embargo, disponemos de una propiedad para controlar las prioridades de los subprocesos (priority). Si tenemos un proceso con una prioridad muy alta pero que se detiene porque espera algún recurso que no está disponible (entrada de datos desde teclado, impresora no preparada, etc.), éste recibirá poco tiempo de cpu. Por eso es importante asignar correctamente las prioridades a los procesos sabiendo qué van a necesitar y que disponibilidad tenemos de los recursos necesarios. Esta propiedad se establece mediante la enumeración ThreadPriority, la cual puede tomar como valores Highest, AboveNormal, Normal, BelowNormal, Lowest.

Cuando creamos un subproceso su prioridad asignada es Normal y la podemos cambiar modificando el valor de la propiedad.

* Basado en un artículo de Jorge Navarrete publicado en la revista Arroba

Las definiciones de tablas, vistas, índices, permisos, procedimientos almacenados, etc. son metadatos que también reciben el nombre de esquema o estructura de la base de datos.

Con el API JDBC podemos acceder y manipular los metadatos de la BD así como los datos que almacena. Un empleo muy común es la consulta de las definiciones de las columnas de una tabla.

El método getMetaData() de un objeto ResultSet devuelve una referencia a un objeto de tipo ResultSetMetaData() que encapsula los metadatos del ResultSet. El interfaz ResultSetMetaData declara métodos que nos van a permitir obtener elementos de los metadatos.

El método getColumnCount() devuelve el número de columas del resultado como un valor int. Al llamar a los métodos getColumnName() y getColumnType() para cada columna se obtienen los nombres y tipos de cada una. En ambos casos se debe especificar la columna mediante un índice. El nombre de la columna se obtiene como un objeto de tipo String y el tipo de columna como un valor int correspondiente al tipo SQL de la columna. La clase Types de java.sql define los campos públicos de tipo int de los distintos tipos SQL, que además tienen los mismos nombres que en SQL (CHAR, DOUBLE, VARCHAR, INT, TIME, etc.). El siguiente fragmento de código lista los nombres de las columnas de un resultset que sean de tipo DATE:

ResultSetMetaData metadatos = Empleados.getMetaData();

int columnas=metadatos.getColumnCount();

for (int i=1; i<=columnas; i++)

{

if (metadatos.getColumnType(i)==Types.DATE)

System.out.println(metadatos.getColumnName(i));

}

Donde Empleados es el resultset del cual queremos obtener los metadatos.

También podemos mostrar el valor de los datos contenidos en cada fila de un objeto ResultSet que en nuestro caso, sean del tipo DATE:

ResultSetMetaData metadatos = Empleados.getMetaData();

int columnas=metadatos.getColumnCount();

int fila=0;

while (Empleados.next()){

System.out.print(«\nFila » + (++fila) + «:»);

for (int i=1; i<=columnas; i++)

{

if (metadatos.getColumnType(i)==Types.DATE)

System.out.print(» » + Empleados.getDate(i));

}

}

También podemos obtener el nombre del tipo de una columna con el método getColumnTypeName() que requiere como argumento el número de columna. Otro método muy útil es getColumnDisplaySize() que devuelve el número máximo de caracteres que necesitaré para mostrar los datos de la columna cuyo índice pasemos como argumento.

* Transcripción del libro «Fundamentos de bases de datos con Java» publicado por Wrox/Anaya

Para indicar al compilador que puede aparecer un número variable de parámetros, el prototipo de la función finaliza con puntos suspensivos. Por ejemplo: int suma(int a, int b, …).

Esto le indica al compilador que la función suma tiene dos parámetros de tipo int que deben aparecer como mínimo, pero que puede recibir más de cualquier tipo.

Para acceder a esos parámetros no definidos en la cabecera, el estándar ANSI C define tres funciones y un tipo incluidos en el archivo de cabecera stdarg.h. El tipo que define es un array que contiene información necesaria para las funciones. Dichas funciones son:

void va_start(va_list ap, lastfix): inicializa el array ap. Esta función debe ser llamada antes de acceder a los parámetros variables. El parámetro lastfix ha de ser el nombre del último parámetro fijo que recibe la función.

tipo va_arg(va_list ap, tipo): devuelve el siguiente parámetro de tipo tipo, de la lista de parámetros. Asimismo incrementa la posición de ap de forma que apunte al siguiente parámetro al que devuelve.

void va_end(va_list ap): elimina la información en el array ap. Debe ser llamada cuando ya no se va a acceder más veces a los parámetros, pero antes de salir de la función.

Vamos a verlo con un ejemplo:

/* Ejemplo de función con numero variable de parámetros */

#include <stdio.h>

#include <stdarg.h>

/* Declaración Prototipo de la función PedirNum */

int PedirNum(void);

/* Declaración Prototipo de la función suma */

long suma(unsigned char Numero, long Num1, long Num2, …);

int main()

{

/* Definimos tres enteros */

long Num1,Num2,Num3;

/* Pedimos valores para esas variables */

Num1=PedirNum();

Num2=PedirNum();

Num3=PedirNum();

/* Realizamos la operacion */

printf(«%li+%li=%li\n»,Num1,Num2,suma(2,Num1,Num2));

printf(«%li+%li=%li\n»,Num1,Num3,suma(2,Num1,Num3));

printf(«%li+%li=%li\n»,Num2,Num3,suma(2,Num2,Num3));

printf(«%li+%li+%li=%li\n»,Num1,Num2,Num3,suma(3,Num1,Num2,Num3));

return(0);

}

/* Definicion de PedirNum */

int PedirNum(void)

{

int Num;

printf(«Introduzca un numero: «);

scanf(«%i»,&Num);

return(Num);

}

/* Definición de Suma */

long suma(unsigned char Numero,long Num1,long Num2,…)

{

long Res;

va_list Param;

Res=Num1+Num2;

Numero-=2;

va_start(Param,Num2);

while (Numero>0)

{

Res+=va_arg(Param,long);

Numero++;

}

va_end(Param);

return(Res);

}

Este ejemplo solo nos vale si el número de argumentos es igual o inferior al primer parámetro, porque de no ser así, la función no tiene forma alguna de determinar el número de argumentos recibidos.

Las versiones Express de SQL Server carecen del servicio SQL Agent que nos permite programar una serie de tareas. En este apartado, veremos como se puede automatizar una tarea de backup de una base de datos SQL Express.

Primeramente se ha de definir un Dispositivo de Copia de Seguridad. Esta operación puede realizarse en el árbol del servidor, en el apartado «Objetos de servidor». El proceso es sencillo, definimos un dispositivo de copia de seguridad proporcionando la ruta y nombre del archivo donde vamos a almacenar las copias y le damos un nombre significativo que vamos a usar a posteriori. Para este ejemplo, supongamos que el nombre es BackupBD.

El proceso de realizar una copia de seguridad se realiza con el comando BACKUP DATABASE. Tendríamos entonces algo como esto:

BACKUP DATABASE miBD TO BackupBD

donde miBD es la base de datos a guardar y BackupBD el nombre del dispositivo de copia. Debemos tener en cuenta que la copia que se va a realizar es total (es la opción por defecto). Para consultar otras opciones, ver Libros en Pantalla, entrada Backup.

Bien, si dispusiéramos del servicio Agente de SQL Server, sería tan sencillo como definir un trabajo y darle este Backup, pero ese privilegio está reservado a las versiones comerciales de SQL Server. Dada la situación, podemos recurrir entonces a las Tareas Programadas que nos proporciona Windows.

Podemos ejecutar consultas SQL, procedimientos almacenados, etc. con el comando de sistema osql. Ésta va a ser la puerta que nos abra a poder automatizar esta tarea. Si nosotros lanzamos en una ventana de comando algo como esto:

osql –S SERVIDOR\SQLEXPRESS -U sa -P password -Q \” BACKUP DATABASE miBD TO BackupBD”

podremos ver que realiza perfectamente el backup.

Muy bien, pues la solución es tan sencilla como crear un archivo bat que ejecute este comando, o crear un ejecutable en cualquier lenguaje que haga el mismo trabajo (más que nada para que no pueda cualquiera con un simple editor de texto ver la contraseña de administrador).

Una vez hecho esto, creamos una nueva tarea en el sistema operativo que llame a este .bat o .exe con la frecuencia que decidamos (diariamente, solo laborales, semanalmente, etc) y tendremos de esta forma un backup automático de nuestra BD con esa frecuencia.

Básicamente, una vista indexada (o indizada) es una vista que ha «materializado» un conjunto de valores únicos en forma de índice agrupado. Otro nombre que toman estas vistas viene de ahí precisamente: vistas materializadas, el cual es el nombre que toman en SGBDs como Oracle. Su ventaja es que proporcionan una búsqueda rápida de obtener información desde una vista. Tras el primer índice, el cual ha de ser agrupado de un conjunto único de valores, podemos crear índices adicionales sobre la vista usando la clave agrupada del primer índice como punto de referencia. De todas formas, este tipo de vistas tiene una serie de restricciones sobre cuándo podemos y cuando no crear índices sobre las vistas. Se muestran a continuación:

– La vista ha de usar la opción SCHEMABINDING

– Si hace referencia a cualquier función definida por el usuario, entonces debe tener un esquema enlazado

– La vista no puede hacer referencia a ninguna otra vista, sólo a tablas y a funciones definidas por el usuario (UDF)

– Todas las tablas y UDF referenciadas en la vista deben utilizar un convenio de denominación de nombres compuestos de varias partes (dbo.Customers, usuario.MiUDF) y tener el mismo propietario que la vista.

– Debe estar en la misma base de datos que los objetos a los que referencia

– Las opciones ANSI_NULLS y QUOTED_IDENTIFIER deben estar activadas (por ejemplo, SET ANSI_NULLS ON) en el momento en que se cree la vista y todas las tablas subyacentes.

– Cualquier función a la que haga referencia la vista ha de ser determinista

– No podemos usar OUTER JOINS, únicamente INNER JOINS

Veamos un ejemplo:

CREATE VIEW VISTA_PEDIDOSCLIENTES

WITH SCHEMABINDING

AS

SELECT cu.CompanyName,

o.OrderId,

o.OrderDate,

od.ProductID,

p.ProductName,

od.Quantity,

od.UnitPrice

FROM dbo.Customers as cu

INNER JOIN dbo.Orders AS o ON cu.CustomerID=o.CustomerID

INNER JOIN dbo.[Order Details] AS od ON o.OrderID=od.OrderID

INNER JOIN dbo.Products AS P on od.Producto=p.ProductID

Con esto aun no tenemos una vista indexada, sino una vista que se puede indexar. Cuando creamos el índice, el primero ha de ser agrupado y único:

CREATE UNIQUE CLUSTERED INDEX IDX_CLIENTES_PEDIDOS

ON VISTA_CLIENTES_PEDIDOS(CompanyName, OrderID, ProductID)

Una vez ejecutado este comando, ya tendremos una vista agrupada. Esta vista se ha hecho sobre la base de datos Northwind (en diferentes versiones de SQL Server no aparece, pero puede importarse de otras versiones). Si ahora hacemos un select de todos los campos sobre esta vista y echamos un ojo al plan de ejecución, veremos que no ha utilizado para nada el índice que le acabamos de proporcionar.

El problema viene del tamaño de las tablas, ya que la base de datos no tiene suficientes datos. El optimizador hace un balance entre lo que puede tardar en ejecutarse el primer plan que encuentra y la cantidad de trabajo requerido para seguir buscando un plan mejor. Por ejemplo ¿tiene sentido tardar más de dos segundos en pensar un plan cuando el plan conocido se puede realizar en menos de un segundo?

En este caso, SQL Server observa la tabla subyacente, comprueba que no existen muchos datos y decide que el plan que tiene es «suficientemente bueno» antes de que el optimizador pueda comprobar que el índice de la vista podría ser más rápido. Este problema de «cuantos datos hay» frente a «lo que cuesta seguir buscando un plan mejor» lo debemos tener en cuenta al crear cualquier índice, no sólo para los de vistas. En los conjuntos de datos pequeños, existe una gran probabilidad de que SQL Server ignore totalmente nuestro índice a favor del primer plan que encuentre. En este caso, pagamos el costo de mantener el índice (ejecuciones más lentas de INSERT, UPDATE Y DELETE) sin obtener ningún beneficio en la instrucción SELECT.

Una de las ventajas de las vistas indexadas radica en que mejoran el rendimiento de operaciones de combinación y agregación. Si realizamos frecuentemente este tipo de operaciones, la vista indexada por su naturaleza nos permite tener el resultado obtenido de forma directa sin ningún cálculo, en contraposición a una consulta que tuviera que realizar todas esas operaciones en el momento.

Volvamos al tema del rendimiento, para poder ver la diferencia entre una cantidad pequeña de registros y una grande, podemos descargar y ejecutar una secuencia de comandos denominada CreateAndLoadNorthwindBulk.sql la cual se puede encontrar en:

http://media.wiley.com/product_ancillary/92/07645437/DOWNLOAD/4486.zip

Este script nos crea una réplica mucho más grande de Northwind. Ahora podemos volver a crear nuestra vista e índice para esa base de datos. Si comprobamos esta vez el nuevo plan de ejecución veremos que esta vez sí acepta la vista indexada que existe en nuestra tabla. El rendimiento total de esta vista es ahora mucho más rápido fila por fila de lo que hubiera sido el modelo anterior.

 

* Transcripción del libro Fundamentos Programación con SQL Server 2005 de Robert Vieira

En este apartado vamos a ver una de las novedades más significativas de SQL Server a partir de 2005 que es la integración con el Common Language Runtime (CLR) de .NET.

Como ejemplo, vamos a crear un procedimiento almacenado muy sencillo y de paso a configurar nuestro SGBD para ello.

Primeramente creamos un Proyecto de Base de Datos SQL Server. Eso lo hacemos partiendo del lenguaje con el que queramos programar (por ejemplo, C#) y de ahí Database (Base de datos), Proyecto de SQL Server. A partir de este momento nos va a aparecer el cuadro de diálogo típico de conexión a un servidor SQL Server donde además elegiremos la base de datos a la que conectarnos. Normalmente nos aparecerá un aviso preguntándonos si deseamos habilitar la depuración SQL/CLR, le vamos a decir que sí.

Una vez creada la solución, vamos a añadir un Procedimiento Almacenado (en Agregar Nuevo Elemento). Le vamos a llamar (¡como no!) HolaMundo.cs. Nos aparecerá una plantilla de código similar a esta:

[Microsoft.SqlServer.Server.SqlProcedure]

public static void HolaMundo()

{

// Put your code here

}

Como podemos observar, tenemos un atributo llamado SqlProcedure al cual le vamos a añadir un atributo llamado Name con el nombre de nuestro procedimiento almacenado (HolaMundo). Este nombre será el que realmente usemos desde T-SQL y puede ser diferente a la función. Nos quedará algo como esto:

[Microsoft.SqlServer.Server.SqlProcedure(Name=“HolaMundo”)]

En el cuerpo de la función vamos a introducir la siguiente línea de código:

SqlContext.Pipe.Send(“Hola Mundo”);

SqlContext nos permite tener comunicación con SQL Server, en este caso, mediante un pipe. Esta línea nos permite enviar entre otras cosas, un mensaje al servidor, pero podemos enviar entre otras cosas, un DataReader.

A continuación compilamos con Generar Solución y después hacemos lo que sería la implementación en SQL Server (Implementar Solución). Una vez hecho esto, en nuestro IDE en el explorador de servidores en el item Ensamblados, podemos ver el generado por nuestra solución. Por otra parte, si abrimos Management Studio de SQL Server en el mismo apartado Ensamblados dentro de Programación, aparece también, y en el de Procedimientos Almacenados se muestra con el nombre que previamente le dimos en el atributo Name.

Si ahora intentamos ejecutar este procedimiento con EXEC HolaMundo, el Management Studio nos va a lanzar el error siguiente:

La ejecución de código de usuario en .NET Framework está deshabilitada. Habilite la opción de configuración “clr enabled”.

Esto se debe a que la integración CLR no está habilitada por defecto en SQL Sercer (por razones de seguridad). Para habilitarla no tenemos más que entrar en la aplicación de Configuración de Superficie de SQL Server y en Configuración de superficie para características. Dentro de este apartado en el árbol Motor de base de datos-> Integración CLR marcamos Habilitar integración CLR. Si ahora ejecutamos el procedimiento almacenado, funcionará perfectamente, aunque inicialmente tardará un poco debido a que se está compilando.

La verdadera novedad a nivel funcional de todo esto es que podemos llamar a clases externas .NET o webservices por ejemplo desde este tipo de procedimientos.

Ahora vamos a probar creando un archivo, para lo cual procedemos a importar el namespace System.IO. A partir de aquí, nuestro procedimiento ahora va a necesitar un argumento de entrada que consiste en el texto que vamos a grabar dentro del fichero. Veamos el código del ejemplo:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure(Name=“HolaMundo”)]

public static void HolaMundo(SqlString valor)

{

using (StreamWriter escritor = File.CreateText(@”c:\test\holamundo.txt”))

{

escritor.Write(valor.ToString());

}

SqlContext.Pipe.Send(valor.ToString());

}

};

Como puede comprobarse, ahora definimos el procedimiento de forma que espere un SqlString valor y con un StreamWriter lo metemos en el archivo test en el directorio C:\

Volvemos a compilar e implementar y a ejecutar desde SQL Server 2005. Ahora, como podemos comprobar, nos vuelve a dar un error:

Mens 6522, Nivel 16, Estado 1, Procedimiento HolaMundo, Línea 0

Error de .NET Framework durante la ejecución de la rutina o agregado definido por el usuario “HolaMundo”:

System.Security.SecurityException: Request for the permission of type ‘System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.CodeAccessPermission.Demand()

at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

at System.IO.StreamWriter.CreateFile(String path, Boolean append)

at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)

at System.IO.StreamWriter..ctor(String path, Boolean append)

at System.IO.File.CreateText(String path)

at StoredProcedures.HolaMundo(SqlString valor)

La explicación es que esta rutina intenta acceder a procedimientos externos a SQL Server y no tiene permiso para ello, a diferencia del procedimiento anterior que únicamente mostraba un texto sin necesidad de recurrir a, como en este caso, operaciones de escritura sobre un fichero.

Para solucionar esto, lo primero que hemos de hacer en nuestras propiedades de proyecto es, en el apartado Base de datos, ajustar el nivel de permisos a External (por defecto está en Safe), aunque con esto sólo no basta. También hemos de ajustar un parámetro de configuración de nuestra base de datos:

alter database MiBD

set trustworthy on

Volvemos a implementar nuestra solución y ejecutamos-. Ahora si vamos a la carpeta test en C:\ veremos como se ha creado el archivo con el texto que le dimos. De esta forma hemos accedido a un namespace de .NET desde SQL Server lo que demuestra que podemos interactuar de forma fácil con el CLR .NET.

* Transcrito de un screencast creado por Misael Monterroca.