viernes, 27 de febrero de 2015

COMMON TABLE EXPRESSION (CTE)


   Amigos,

     Desde el año 2005 Microsoft Sql Server ha puesto en nuestras manos una herramienta muy valiosa cuando de simplificar la lectura y escritura de código hablamos.
      Se trata de los famosos CTE - "Common Table Expression"

     Qué son los CTE ?

     En la práctica los CTE son recordset de datos que cuentan con la ventaja de poder ser referenciados dentro de una sentencia select - update - insert - delete tantas veces como sea necesario
      Básicamente actúan como vistas  y nos permiten prescindir del uso de tablas temporales, cursores, vistas y complejas lógicas (tales como subqueries "poco leíbles")
      Los CTE permiten definir uno o varias subqueries con un Alias (el nombre del CTE), y luego utilizar esas subqueries referenciando diréctamente al Alias.

      
    Para qué se usan?


  1.    Como ya se ha mencionado, para agilizar la lectura y escritura de queries complejas que requieren de subqueries, tablas temporales etc.
  2.    En aquellas ocasiones en la que es necesario referenciar el resultado de una tabla en múltiples ocasiones.
  3.    Para poder agrupar por una columna derivada de subqueries.
  4.    Para poder realizar consultas recursivas

    Cuál es su syntaxis?

    -          La sintaxis comienza con un WITH seguida del NombredelCTE

-         A continuación pueden detallarse los nombres de las columnas del CTE (opcional)

-         Luego un AS seguido de la SubConsulta entre paréntesis, esto último todo obligatorio 

WITH NombredelCTE (columna1, columna2)
AS
(Subconsulta)

Ejemplo 1.  Reemplazando una querie tradicional por otra utilizando CTE

Supongamos que tenemos:

-  Una tabla llamada Users_Subscriptions con el Teléfono(Ani)y la Fecha de Subscripción de un usuario a X servicio (Date_Begin)

-  Una segunda tabla llamada Cdr_Subscriptions con el detalle de las cobranzas hechas a cada ANI de la tabla User_Subscriptions - un registro en esta tabla es una cobranza realizada-

Nuestro jefe nos encarga la tarea de "Hacer un informe que conste de ANI, FechaSubscripción, Cobranzas, para el mes de Febrero de 2015"

Es aquí que podemos resolver nuestra solicitud del modo tradicional (utilizando subqueries) o del modo que les propongo hoy, mucho más sencillo, entendible y escalable en grado de complejidad (utilizando CTE)


A) Resolviendo la requisitoria de la forma tradicional


select

a.ani,
b.date_begin,
a.cobros
from
 --
(select ani, count(*) as cobros from
 cdr_subscriptions with (nolock)
 where smsdatetime between '2015-02-01 00:00:00' and '2015-02-28 23:59:59'
group by ani) as a
 --
INNER JOIN
 users_subscriptions as b with (nolock)
 on a.ani = b.ani
 --
 where
 date_begin between  '2015-02-01 00:00:00' and '2015-02-28 23:59:59'



B) Resolviendo la requisitoria usando CTE  (entendible y escalable, sumando tantas  CTEs como hagan falta y referenciando las mismas en la query final)

-- Querie con CTE --

-- Defino al Cte --
WITH A   (ANI, COBROS)
AS
(select ani, count(*) from
 cdr_subscriptions with (nolock)
 where smsdatetime between '2015-02-01 00:00:00' and '2015-02-28 23:59:59' 
 group by ani)

 -- Hago query referenciando al CTE
 select A.ani, A.cobros, b.date_begin
 from users_subscriptions AS B
 INNER join A on
 a.ANI = b.ANI
 where
 date_begin between  '2015-02-01 00:00:00' and '2015-02-28 23:59:59' 



No he querido hacer de este ejemplo un ejemplo complejo, puesto que quiero que se familiaricen y entiendan el funcionamiento de las CTE. 
La idéa es que "pierdan el miedo"a utilizar las CTE.
Hace ya 10 años que están disponibles y creanmé aún hay gente que se complica la vida escribiendo y/o interpretando complejísimas queries cuya escencia podría ser alivianada sustantivamente si se utilizacen las CTE.
El objetivo es que, aunque sea, tengan presente la posibilidad de utilizarlas en reemplazo de tablas temporales, vistas, subqueries etc, cuando tengan que encarar trabajos en los que la complejidad de las queries hagan que bien valga la pena el intento de uso de las CTE.



Ejemplo 2.  Utilizando Una CTE Recursiva


Una de las grandes utilidades de las CTE está relacionada con la necesidad de utilizar queries recursivas.

Puede ser necesario utilizar una query recursiva cuando queremos mostrar los empleados en un organigrama o una lista de materiales en donde un producto primario tiene uno o varios componentes que, a su vez, tienen subcomponentes o son componentes de otros elementos primarios.

-  Un CTE es recursivo cuando se ejecuta una x cantidad de veces devolviendo subconjunto de datos, hasta obtener el conjunto de datos completo.

- Una CTE Recursiva se compone de una o varias CTE combinadas mediante operadores UNION ALL, UNION, EXCEPT

- La CTE Recursiva finaliza de modo implícito cuando no se devuelven más filas desde su llamado.

Una vez más voy a utilizar un ejemplo muy sencillo para que se familiaricen con el uso de las CTE, en este caso las recursivas. La idéa es no asustarlos sino invitarlos a que las usen

-----------QUERY RECURSIVA ----------

WITH MuestraMensaje(Mensaje, Largo)
AS
(
SELECT
Mensaje = CONVERT(varchar(300), 'Amo '),
Largo =  LEN('Amo ')
UNION ALL
SELECT
CONVERT (VARCHAR(300), Mensaje + 'Sql Server! '),
LEN(mensaje)
FROM MuestraMensaje
WHERE Largo < 300
)
-- Llamo la cte recursiva
SELECT mensaje, largo FROM MuestraMensaje





Amigos, espero que haya sido de utilidad este post y como siempre espero sus contactos para resolver dudas. Los saludo cordialmente

         

miércoles, 25 de febrero de 2015

Mover Bases de Datos Model/Msdb/Master/Tempdb y los Archivos de Log del Sistema ErrorLog/SQLDump/AgentLog

    Amigos,  por motivos varios podemos encontrarnos frente a la tarea de tener que mover nuestras Bases de Datos y Archivos de Log del Sistema hacia otra unidad de disco. 

    No es una tarea difícil mas si requiere de un orden. Veamos pues el paso a paso:
  
     

    1) Base de Datos "Master"

      Esta base contiene buena parte de la configuración de la instancia de SQL Srv (logins,
      Bd, Errores etc).  También los Sp extendidos y muchos otros Sp del sistema.

1)   Crear el directorio donde vamos a mover los archivos master.mdf y el master.ldf

2)  Cambiar el path donde el Sql Servr “busca” los archivos , para ello:

Desde dentro del servidor… Start --> All Programs -->Microsoft SQL Server xxxx --> Configuration Tools--> Sql Server Configuration Manager --> SQL Server Services --> SQL Server(MSSQLSERVER) -->Botón derecho --> Properties --> Solapa Advanced --> Startup Parameters  y modificamos los path para el master.mdf y el master.ldf --> Luego “ok”


3)       Detener el servicio del SQL Server (MSSQLSERVER)
4)       Mover los archivos hacia la nueva ubicación (master.mdf y el master.ldf)
5)       Reiniciar el servicio..


2)  Archivo de Log de Errores "ErrorLog"

Sql server almacena errores del sistema e información adicional de distintas instancias del mismo en archivos ubicados en la carpeta ERRORLOG.
      
1)   Crear el directorio donde vamos a mover los archivos contenidos en la carpeta ERRORLOG

2)  Cambiar el path donde el Sql Servr loguea los archivos , para ello:

Desde dentro del servidor… Start --> All Programs -->Microsoft SQL Server xxxx --> Configuration Tools--> Sql Server Configuration Manager --> SQL Server Services --> SQL Server(MSSQLSERVER) -->Botón derecho --> Properties --> Solapa Advanced --> Startup Parameters  y modificamos los path para ERRORLOG--> Luego “ok”


3)       Detener el servicio del SQL Server (MSSQLSERVER)
4)       Mover los archivos hacia de log de Error a la nueva ubicación
5)       Reiniciar el servicio..


3)  Archivo de Log de Errores "SQLDump"

Sql server almacena crea un archivo de Dump cuando se produce un ‘Crash’ del Sql Server u otro error no documentado. También cuando el comando DBCC CHECKDB encuentra ‘corrupciones’ dentro de la Base de Datos. Por default el SQL Server almacena este tipo de archivos en la carpeta ERROLOG…

1)   Crear el directorio donde vamos a mover los archivos contenidos en la carpeta ERRORLOG

2)  Cambiar el path donde el Sql Servr loguea los archivos , para ello:

       Desde dentro del servidor… Start --> All Programs -->Microsoft SQL Server xxxx --> Configuration Tools--> Sql Server Configuration Manager --> SQL Server Services --> SQL Server(MSSQLSERVER) -->Botón derecho --> Properties --> Solapa Advanced --> Dump Directory y modificamos los path --> Luego “ok”


3)       Detener el servicio del SQL Server (MSSQLSERVER)
4)       Mover los archivos Dump hacia  la nueva ubicación
5)       Startear el servicio..



4)  Base de Datos "TempDB"

Esta base contiene todos los objetos temporales creados explícitamente por el usuario (tablas, sp, variables, etc) y otros objetos internos creados por el motor de la base de datos (por ejemplo tablas de trabajo para almacenar resultados intermedios).

1)       Corroborar la ubicación actual de los archivos correspondientes a la base TempDB

                SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
                   WHERE database_id = DB_ID(N'tempdb');

2)       Crear el directorio donde van a ubicarse los archivos tempdb.mdf y el templog.ldf

3)       Correr un Alter Database y setear las nuevas ubicaciones para los archivos temporales
                      
                 ALTER DATABASE TempDb
                      MODIFY FILE ( NAME = ' tempdev', FILENAME = 'E:\Data\Microsoft SQL
                     Server\MSSQL\Tempdb\tempdb.mdf' )  
                      go
                     ALTER DATABASE TempDb
     MODIFY FILE ( NAME = ' templog', FILENAME = 'E:\Data\Microsoft SQL               Server\MSSQL\Tempdb\templog.ldf' )

4)       Reiniciar el servicio de Sql Server

5)       Borrar el directorio con los archivos de la TempDB “viejos” (que ya se han regenerado)


5)  Base de Datos "MODEL"

Esta base contiene la plantilla de todas las bases de datos del SQL Server. Siempre debe estar presente puesto que la base de datos TempDb se regenera ante cada reinicio del motor de BD tomando como plantilla a la Model

1)       Corroborar la ubicación actual de los archivos correspondientes a la base Model

                SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
                   WHERE database_id = DB_ID(N'model');

2)       Crear el directorio donde vamos a mover los archivos model.mdf y el modellog.ldf

3)       Correr un Alter Database y setear las nuevas ubicaciones para los archivos 
                      
                 ALTER DATABASE Model
                      MODIFY FILE ( NAME = ' modeldev', FILENAME = 'E:\Data\Microsoft SQL
                     Server\MSSQL\Model\Model.mdf' )  
                      go
                     ALTER DATABASE Model
     MODIFY FILE ( NAME = ' modelog', FILENAME = 'E:\Data\Microsoft  SQL             Server\MSSQL\Model\modellog.ldf' )

4)       Stoppear  el servicio de Sql Server

5)        Mover a la nueva carpeta creada en el punto 2) los archivos model.mdf y el modellog.ldf

             6)       Startear el servicio de Sql Server


  6)  Base de Datos "MSDB"

Es la base de datos utilizada por el Sql Server Agent para programar alertas y trabajos.

1)       Corroborar la ubicación actual de los archivos correspondientes a la base MsDb

                SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
                   WHERE database_id = DB_ID(N'msdbl');

2)       Crear el directorio donde vamos a mover los archivos MSDBData.mdf y el MsdbLog.ldf

3)       Correr un Alter Database y setear las nuevas ubicaciones para los archivos 
                      
                 ALTER DATABASE MsDb
                      MODIFY FILE ( NAME = ' MSDBData', FILENAME = 'E:\Data\Microsoft SQL
                     Server\MSSQL\Model\MSDBDatal.mdf' )  
                      go
                     ALTER DATABASE MsDb
     MODIFY FILE ( NAME = ' MSDBlog', FILENAME = 'E:\Data\Microsoft  SQL             Server\MSSQL\Model\MSDBLog.ldf' )

4)       Stoppear  el servicio de Sql Server

5)        Mover a la nueva carpeta creada en el punto 2) los archivos MSDBData.mdf y el MSDBLog.ldf

             6)       Startear el servicio de Sql Server


  7) SQL Server Agent Log

      Es Sql Server Agent mantiene un set de archivos de log con warnings o errores encontrados durante la ejecución de un job.
.

1)   Crear el directorio donde vamos a mover los archivos master.mdf y el master.ldf

2)  Cambiar el path donde el Sql Servr “busca” los archivos , para ello:

Desde dentro del servidor… Start --> All Programs -->Microsoft SQL Server xxxx --> Configuration Tools--> Sql Server Configuration Manager --> SQL Server Agent(MSSQLSERVER)  --> SQL Server(MSSQLSERVER) -->Botón derecho --> Properties --> Solapa Advanced --> Dump Directory  y modificamos los path  --> Luego “ok”


3)       Detener el servicio del SQL Server (MSSQLSERVER)
4)       Mover los archivos de Dump hacia la nueva ubicación
5)       Startear el servicio..