SQL --Uso de SET

Uso de SET

Transact-SQL permite agrupar más de una opción en el comando set, si todas las opciones van a tomar el mismo valor. Esto es muy útil en este caso:

set showplan, noexec on
...
set showplan, noexec off

De hecho, el agrupamiento es la única razón que se me ocurre para justificar que noexec sea una opción "negativa".

SQL --Uso de HAVING en self-joins

Uso de HAVING en self-joins

Esto está explicado en el librito Optimizing Transact-SQL, pero vale la pena comentarlo:

Supongamos que tengo una tabla con una columna numérica, y quiero el máximo que es menor a un número K dado: la solución obvia es:

select max(foo) from bar where foo < K

pero no sirve si quiero traer otras columnas.

Para fijar ideas, supongamos que tengo la tabla Comisiones con campos Nivel y Porcentaje, y la consulta típica es: seleccionar el Porcentaje de Comisiones para el máximo Nivel (notar cómo la condicion establece un piso) menor a un K dado. La segunda solución obvia es:

select Porcentaje from Comisiones
where Nivel = (select max(Nivel) from Comisiones where Nivel < K)

El truco para eliminar el select anidado es este: el where filtra filas, y el having filtra grupos. Pero no hace falta tener grupos; la tabla completa es, conceptualmente, un grupo. Entonces, esta consulta hace lo mismo:

select Porcentaje from Comisiones
where Nivel < K
having Nivel = max(Nivel)

Cuidado, porque si tengo más condiciones, tengo que pensar qué condiciones corresponden a la fila y qué condiciones corresponden al grupo. Por ejemplo, supongamos que la tabla además tiene una columna Plan, que puede ser 1, 2 ó 3. Si quiero seleccionar el Porcentaje de Comisiones para el Plan 3 con Nivel menor a K, la condición Plan < 3 va en el having, y no en el where:

select Porcentaje from Comisiones
where Nivel < K
having Plan = 3
and Nivel = max(Nivel)

Alternativamente, puedo agrupar explícitamente:

select Porcentaje from Comisiones
where Plan = 3
and Nivel < K
group by Plan
having Nivel = max(Nivel)

Buscar techos es el problema inverso: es el mínimo Nivel mayor a uno dado.

SQL --Eliminación de consultas anidadas

Eliminación de consultas anidadas

Las consultas sobre una tabla que tienen una condición sobre una columna de otra tabla relacionada tienen siempre la misma forma: por ejemplo, buscar todas las operaciones cuyos movimientos fueron devengados antes de una fecha dada. En vez de escribir:

select * from A where (select s from B where B.a = A.a and max(B.s) < N)

dar vuelta el join y escribir:

select A.a from A, B
where A.a = B.a
group by B.a
having A.a = B.a and max(B.s) < N

La idea es fijarse en los grupos en B de acuerdo a la clave foránea, y seleccionar o no un grupo completo de acuerdo a si el máximo valor en s es menor al dado. Notar cómo la condición del join se repite en el having.

Esto es más rápido porque, como mínimo, no crea una tabla de trabajo para guardar el select anidado (que de todas maneras es un join).

SQL --Poner los registros identity a un determinado numero

Para poner los identity a cero o a un determinado número debemos ejecutar la siguiente instrucción:

DBCC CHECKIDENT (Tabla, RESEED, 15)

Donde Tabla es el nombre de la tabla que queremos reinicializar y 15 el numero al que queremos el identity.
Con esto lo que hacemos es reiniciar el campo identity a un determinado numero, si en lugar de 15 ponemos 0 lo reiniciamos a cero, asi evitamos que si borramos muchos campos de la tabla, los indices no nos queden correlativos.

SQL --Paginación de registros desde Stored Procedures

A veces es necesario por determinadas circunstancias que los registros que devuelve un stored procedure al recordset llegue con paginación incorporada y esta paginación no tengamos que hacerla en el cliente, asp, vb, etc.
El método que propongo no es único ni más bueno o más malo, es un método más que se puede utilizar. La idea es crear una tabla temporal donde grabaremos los datos de la consulta que queremos paginar y a través de un índice de página ir devolviendo los registros de n en n.

Pero lo mejor para una buena explicación es trabajar con un ejemplo.
En el ejemplo extraemos un listado de la base de datos Northwind de productos ordenados por categorías, extraemos los campos ID del producto, nombre del producto y nombre de la categoría. El Stored Procedure recibe dos parámetros:

- Index, el número de página que queremos mostrar
- NumRegs, el número máximo de registros a mostrar por página.

Create Procedure Paginacion_Registros

--Número de página a mostrar

@index integer
-- Total de resgistros por página

@NumRegs integer
As

-- Declaramos dos variables que serán los índices mayor
-- y menor del filtro de paginación

Declare @Maximo numeric
Declare @Minimo numeric

-- Y asignamos los valores que le corresponden a cada página
-- la asignación de los límites se realiza de la siguiente forma:
-- si index = 1 y NumRegs = 10, entonces los registros a mostrar
-- son del 1 al 10, si index = 2, los registros a mostrar son del 11 al 20
-- y asi sucesivamente.

Select @Maximo = (@index * @NumRegs)
Select @Minimo = @Maximo - (@NumRegs - 1)

-- Creamos la tabla temporal para la paginación,
-- con un campo auto numérico que será el que nos
-- sirva de índice a la hora de extraer los registros.

Create Table #tmpListado (
nOrden INT IDENTITY(1,1),
IDProducto numeric,
NombreProducto varchar(40),
NombreCategoria varchar(15)
)

-- Insertamos en la tabla temporal directamente del select

Insert #tmpListado Select Products.ProductID, Products.ProductName,
Categories.CategoryName
From Products, Categories
Where Products.CategoryID = Categories.CategoryID
order by Categories.CategoryName

-- Una vez tenemos los datos en la tabla temporal los extraemos
-- con un select filtrados por los valores de paginación
-- @Maximo y @Minimo

Select IDProducto, NombreProducto, NombreCategoria
FROM #tmpListado
WHERE (nOrden BETWEEN @Minimo AND @Maximo)

Si pruebas el ejemplo y vas cambiando el valor de index veras como va paginando el Stored Procedure.

SQL --Reducir el tamaño de los ficheros de una base de datos

Este proceso es muy sencillo, a través de una utilidad del Enterprise Manager del SQL Server y de unas instrucciones de transact-SQL reducir el tamaño de la base de datos y del log.

Ha veces que el tamaño físico de nuestra base de datos es superior al tamaño real de datos, es decir, en disco el fichero físico de la base de datos puede ocupar un 1GB y realmente estar ocupando 100MB, es decir solo tener 100MB de información, esto es debido a las modificaciones que realizamos durante la vida de la base de datos. Al borrar registro u otros objetos de la base de datos éstos no liberan el espacio físico y por eso aunque nosotros reduzcamos los datos no pasa lo mismo con el tamaño físico.


Para reducir el tamaño del fichero físico debemos utilizar una opción del menú del Enterprise Manager, colocándonos sobre la base de datos pulsamos con el botón derecho del raton y seleccionamos “all tasks” (todas las tareas) -> Shrink Database (imagen 2) y aparece un cuadro de dialogo como en la imagen 3, aquí se ha de seleccionar la opción ““Move pages to beginning of file befote shrinking” pulsamos OK y esto reducirá el tamaño del fichero fisico.


Una vez hecho este proceso recomiendo utilizar las siguientes intrucciones para borrar el log y reducir su tamaño.
“
BACKUP LOG base_de_datos WITH TRUNCATE_ONLY

DBCC SHRINKDATABASE ( base_de_datos , TRUNCATEONLY )

Es probable, bueno, seguro que si borrarais muchos datos el fichero de log os crezca desmesuradamente por lo que es conveniente tener suficiente espacio en disco e ir reduciendo el fichero de log.

SQL --Realizar copias de seguridad

Realizar copias de seguridad desde el administrador corporativo es una tarea sencilla que solo requiere de unos pasos para completarla.

Para realizar esta acción nos pondremos sobre la base de datos que queremos copiar y pulsando con el botón derecho seleccionamos la opción Todas las tareas -> copias de seguridad de la base de datos y nos aparece la imagen 1. Aquí definimos:

- La base de datos que queremos copiar, en nuestro ejemplo la pubs.
- El nombre de la copia
- Descripción si queremos darle.
- Tipo de copia, si es completa o diferencial, si la copia es diferencial registra sólo los cambios de la información de la base de datos realizados después de la última copia de seguridad.
- Destino. Aquí debemos poner un nombre de fichero y un path, para esto tenemos un asistente, pulsando sobre el botón agregar aparecerá la imagen 2 en la cual especificaremos el path y nombre de la copia.

- Sobrescribir o anexar al medio, si sobrescribimos la copia, el fichero de copia solo contendrá la última copia de seguridad realizada, si anexamos al medio, el fichero será incremental y contendrá todas las copias que realicemos.
- Programar, si queremos programar la copia como un trabajo para que se ejecute en una hora y fecha determinada.

Por último solo nos queda seleccionar una opción más, antes de realizar la copia. Pulsamos el tab opciones (imagen 3) y seleccionamos el check “comprobar la copia de seguridad al concluir” para que verifique que la copia de seguridad se ha realizado correctamente.

SQL --Leer el transaction Log

Leer el transaction Log

Es algo tan sencillo que no va a requerir una explicación, con una simple línea de código ejecutada en el Query Analizer podremos leer el fichero de transacciones.

Con esta instrucción puedes leer el registro de transacciones:

DBCC LOG (Base_de_datos, 2)

SQL --Ejecutar comandos del sistema desde Transact-sql

Ejecutar comandos del sistema desde Transact-sql
Una de las múltiples opciones que tiene SQL Server es ejecutar comandos del sistema operativo desde Transact-SQL. Probablemente no es una de las opciones más utilizadas pero a veces es necesario recurrir a ella.

Para realizar esta acción utilizaremos un Extended Stored Procedure que incorpora SQL Server en la base de datos master, xp_cmdshell:

xp_cmdshell {'command_string'} [, no_output]

'command_string', es la sentencia del sistema que ejecutara
no_output, Es un parámetro opcional para que no devuelva nada.

Ejemplo:
xp_cmdshell 'dir.exe c:\*.*'

Mas Informacion: