Tablas temporales en el sql server

Las Tablas versionadas o temporales se introdujeron en el estándar ANSI SQL 2011 y en SQL Server 2016 y es un tema a evaluar en la certificación de Microsoft para Consultas con SQL Server.

Una tabla versionada del sistema le permite consultar los datos actualizados y eliminados, mientras que una tabla normal sólo puede devolver los datos actuales. Por ejemplo, si se actualiza un valor de columna del 5 al 10, sólo se puede recuperar el valor 10 en una table normal. Una tabla versionada también le permite recuperar el valor anterior 5. Esto se logra
manteniendo una tabla de historial. Esta tabla almacena los datos de la historia antigua, junto con un conjunto de datos de inicio y fin para indicar cuando el registro fue activo.

Estos son los casos de uso más comunes
para las tablas temporales:

• Auditoría. Con tablas temporales puede averiguar qué valores una entidad específica ha tenido en toda su vida.
• Cambio de dimensiones lentas en un Data Warehouse. Una tabla versionada del sistema se comporta exactamente como una dimensión de tipo 2 cambiando comportamientos de sus propias tablas En este caso, la tabla de dimensión incluye los campos de fecha inicio de vigencia y fecha fin de vigencia. Estas fechas nos permiten determinar en qué estado estaba la dimensión en cualquier fecha del calendario.
• Reparación de corrupciones a nivel de registro. Que sería como una especie de mecanismo de copia de seguridad en una sola tabla.
• Eliminación accidental de un registro. Recuperar el archivo de la tabla historial e insertarla de nuevo en la tabla principal.

Creación de una tabla versionada del sistema

Cuando se desea crear una nueva tabla
temporal, un par de pre–requisitos se deben cumplir:

• Se debe definir una clave principal
• Dos columnas deben ser definidos para registrar la fecha de inicio y final con un tipo de datos de datetime2. Si es necesario, estas columnas se pueden ocultar mediante el indicador oculto.
  Estas columnas se llaman las columnas de tiempo SYSTEM_TIME.
• Los triggers INSTEAD OF no están permitidos. Los triggers AFTER sólo están permitidos en la tabla actual.
• Dentro de la memoria OLTP no se puede utilizar

También hay algunas limitaciones:

• tabla temporal y la historia no puede ser FileTable
• La tabla de la historia no puede tener ninguna restricción
• INSERT y UPDATE no pueden hacer referencia a las columnas de época SYSTEM_TIME
• Los datos de la tabla de historia no pueden ser modificados

La siguiente secuencia de comandos crea
una sencilla tabla versionada sistema:

CREATE TABLE dbo.TestTemporal
(ID int primary key, Multiplicando int, Multiplicador int
,Resultado AS A * B
,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON);

Si no se especifica un nombre para la tabla de historial, SQL Server
generará automáticamente una de la siguiente estructura: dbo.MSSQL_TemporalHistoryFor_,
donde <XXX> es el identificador de objeto de la tabla principal.

El resultado es el siguiente:

La tabla de historial tiene un conjunto idéntico de columnas, pero sin restricciones. También tiene su propio conjunto de índices y estadísticas.
Creación de sus propios índices como el índice de almacén de columnas agrupado en la tabla de historial puede mejorar considerablemente el rendimiento. Vamos a probar la funcionalidad de la tabla temporal mediante la inserción de datos en la tabla y luego modificarlo.

-- Carga Inicial
INSERT INTO dbo.TestTemporal (ID, A, B)  VALORES (1,2,3) ,   (2,4,5)  , (3,0,1);
GO
SELECT * FROM dbo.TestTemporal;

Ahora vamos a eliminar una fila y actualizar otro.

--Modificar datos
DELETE FROM dbo.TestTemporal  WHERE id = 2;
UPDATE dbo.TestTemporal  SET A = 5  WHERE id = 3;
GO

La tabla principal muestra el estado actual de los datos:

Tenga en cuenta que la columna de la SysEndTime no es necesario, ya que sólo muestra el valor máximo datetime2.

La historia muestra las versiones antiguas de las diferentes filas y están fechados correctamente.

Modificar el Esquema de una
tabla versionada

Cuando el control de versiones está
habilitado en una tabla, las modificaciones sobre la tabla son muy limitadas,
solo se permiten:

• ALTER TABLE …
  REBUILD
• CREATE INDEX
• CREATE STATISTICS

Todas las demás modificaciones del
esquema no están permitidas. Por ejemplo, no es posible eliminar una tabla temporal.

Tampoco estaría permitido agregar una nueva columna, con el fin de alterar
el esquema de una tabla temporal, el control de versiones Se debe desactivar
primero:

ALTER TABLE dbo.TestTemporal SET
(SYSTEM_VERSIONING = OFF);

Este comando eliminará el system_versioning y convertirá la
tabla principal y la tabla de la historia en dos tablas regulares.

Con esto se puede hacer cualquier
modificación que desee en ambas tablas. Asegúrese que los datos permanezcan
sincronizados y la historia siga siendo consistente. Después de las modificaciones, puede
activar el sistema de control de versiones de nuevo.

 ALTER TABLE dbo.TestTemporal SET (SYSTEM_VERSIONING = ON
 (HISTORY_TABLE = dbo.TestTemporal_History, DATA_CONSISTENCY_CHECK = [ON /     OFF]) 
 ); 


Consultas
a Tablas Temporales

Una vez que hemos creado una tabla temporal y hemos estado modificando los
datos durante algún tiempo, por supuesto que queremos ver todos los cambios
históricos de los datos, por lo que podemos realizar consultas a la tabla.

La siguiente secuencia de comandos creará dos tablas e insertar datos en
ella.

-- Crear tabla de historia

CREAR TABLA dbo.PriceHistory
(ID INT NOT NULL
, Producto VARCHAR (50) NOT NULL
, Precio DECIMAL(10,2) NOT NULL
, StartDate DATETIME2 NOT NULL
, EndDate DATETIME2 NOT NULL
);
GO

— los valores de inserción para la historia

INSERT INTO dbo.PriceHistory (ID, Producto, Precio, StartDate, EndDate) VALUES
(1, ‘Myproduct’,  1.15, ’01/07/2015 00:00:00′, ’07/01/2019 11:58:00′)
, (1, ‘Myproduct’, 1.16, ’07/01/2015 11:58:00′, ’03/07/2019 12:00:00)
, (1, ‘Myproduct’, 1.18, ’07/03/2015 12:00:00′, ’05/07/2019 18:05:00)
, (1, ‘Myproduct’, 1.21, ’05/07/2015 18:05:00′, ’07/07/2019 08:33:00′);

-- Crear tabla actual para almacenar los precios

CREAR TABLA dbo.Price
(ID INT NOT NULL
, Producto VARCHAR (50) NOT NULL
, Precio DECIMAL (10,2) NOT NULL
, StartDate DATETIME2 NOT NULL
, EndDate DATETIME2 NOT NULL
, CONSTRAINT PK_Price PRIMARY KEY CLUSTERED ( ID ASC)
);
GO

— Insertar el precio actual ¡asegúrese de que la fecha de inicio no está en el futuro!

INSERT INTO dbo.Price (ID, Producto, Precio, StartDate, EndDate)
VALUES (1, ‘MyProduct’, 1,20, ’07/07/2015 08:33:00′, ‘9999–12–31 23: 59: 59.9999999’);

La secuencia de comandos sólo crea filas para un producto, con el fin de
mantener el ejemplo sencillo y fácil de entender. Este producto tendrá 4
versiones históricas y 1 versión actual.

Esto se traducirá directamente en 4 filas en la tabla de historial y 1 fila
de la tabla principal. Ahora vamos a convertir esas dos tablas en una tabla
temporal.

-- Permitir a las columnas de tiempo sistema

ALTER TABLE dbo.Price
ADD PERIOD FOR SYSTEM_TIME(StartDate, EndDate);

-- Activar el sistema de control de versiones

ALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE = dbo.PriceHistory, DATA_CONSISTENCY_CHECK = ON)
);

SQL Server 2016 consulta Tablas temporales

La cláusula SELECT … FROM tiene una nueva cláusula en SQL Server 2016 que
es FOR SYSTEM_TIME,  esta nueva cláusula
también cuenta con 5 nuevos sub-cláusulas temporales específicas. En las
siguientes secciones vamos a discutir cada uno con un ejemplo.

Para llevar a cabo cualquier tipo de análisis basado en tiempo, use la
nueva cláusula FOR SYSTEM_TIME con cinco sub-cláusulas temporales específicas
para consultar datos en las tablas actuales y de historial.

• AS OF
• FROM
  TO
• BETWEEN
  AND
• CONTAINED IN
  ( , )
• ALL

AS OF

Utilizando el AS OF de la Sub-cláusula, recupera la versión para cada fila
que era válida en ese punto específico en el tiempo. Básicamente se le permite
viajar en el tiempo a un punto determinado en el pasado para ver en qué estado
está la tabla estaba en ese punto.

Por ejemplo:

SELECT * FROM dbo.Price  FOR AS SYSTEM_TIME AS OF ‘7/4/2015’;

Esta consulta será recuperar la versión que estaba en vigor el 4 de julio
(medianoche).

La tercera versión será devuelta, que tiene el precio de 1,18.

Tenga en cuenta que no es necesario conocer el nombre de la tabla de
historia o cómo se construye la tabla temporal. El uso de la tabla temporal es
transparente: sólo tiene que consultar la tabla mediante la cláusula FOR
SYSTEM_TIME y SQL Server controla todo el resto. Al mirar el plan de ejecución
real, se puede ver uniones de SQL Server de la tabla principal y la tabla de
historia junta y filtros para las filas solicitadas.

¿Qué pasa con el caso en el que el borde AL punto en el tiempo cae en un
límite? En otras palabras, el punto en el tiempo es igual a la fecha de
finalización de una versión y la fecha de inicio de la versión que viene justo
después. Vamos a probarlo.

SELECT * FROM dbo.Price
FOR SYSTEM_TIME AS OF ’07/03/2015 12:00:00′;

Esto devolverá la versión más reciente:

Consulta de A a B de la tabla temporal de datos de SQL
Server

Esta cláusula es funcionalmente equivalente a la construcción siguiente:

StartDate A

se devolverán todas las filas históricas y actuales que estaban en algún
momento activo en el período de tiempo entre A y B. toda la vida de un registro
de este tipo puede ser más grande que el período de tiempo entre A y B.

Por ejemplo:

SELECT * FROM dbo.Price
FOR SYSTEM_TIME FROM ‘7/2/2015’ A ‘7/6/2015’;

Esta consulta devolverá todas las versiones que estaban en algún momento
activo en el período de tiempo entre el 2 de julio y el 6 de julio.

Versión 2, 3 y 4 serán devueltos.

Lo que si A
y B son los mismos puntos en el tiempo?

SELECT * FROM dbo.Price
FOR SYSTEM_TIME FROM ‘7/6/2015’ A ‘7/6/2015’;

Este será el mismo que se utiliza AS DE. se devolverán las versiones
válidas en ese punto específico en el tiempo:

Sin embargo, el comportamiento difiere de AL cuando el punto en el tiempo
es en un límite de dos versiones.

SELECT * FROM dbo.Price
FORM SYSTEM_TIME FROM ’07/03/2015 12:00:00′ A ’03/07/2015 12:00:00′;

En este caso, no hay registros no se devuelven!

Entre A y B lógica para las tablas de SQL Server
temporales

Esta sub-cláusula es funcionalmente equivalente a la construcción
siguiente:

StartDate ≤ B AND  EndDate > A

En la mayoría de los casos esto también es equivalente a la de A a B, excepto cuando la fecha de finalización del período de tiempo (B) cae en un límite. Con entre A y B, se incluía esta frontera. Vamos a echar un vistazo al mismo ejemplo que en la sección anterior, pero con el rango ampliado hasta el punto de partida de la versión actual. En el ejemplo he comparado el medio y la DE A subcláusula.

La diferencia entre las dos cláusulas es ahora muy claro: entre A y B incluye el límite superior del marco de tiempo, lo que se traduce en una versión extra que se devuelve. Cuando A y B son los mismos, entre A y B es el equivalente de AL.

Aunque es similar al operador BETWEEN utilizado en la cláusula WHERE, hay una diferencia sutil ya que el operador BETWEEN incluye ambos límites, mientras que el temporal entre la sub–cláusula sólo se incluye el límite inferior.

CONTENIDO EN lógica (A, B) para las tablas de SQL
Server temporal

El último sub-cláusula tiene la siguiente equivalente funcional:

Un StartDate ≤ Y ≤ B EndDate

Sólo se volverá versiones de los cuales el intervalo de tiempo válido es totalmente dentro del rango de tiempo especificado, límites incluidos. Una vez que una versión cruza el límite del intervalo de tiempo, se incluye. Utilizamos el mismo ejemplo que en las secciones anteriores.

SELECT * FROM dbo.Price
FOR SYSTEM_TIME CONTAINED IN ( ‘2/7/2015′, ’07/06/2015’);

La consulta ahora sólo volverá versión 3, ya que es la única versión
completamente dentro del punto medio del rango de inicio y fin. La versión 2 y
la versión 4 cruzan las fronteras, por lo que se excluyen.

Tener A y B de la misma sería bastante inútil, ya que una consulta de este

tipo sólo volvería versiones que tenían una vida de exactamente un punto en el
tiempo.

Test Credit Card Account Numbers

Numeros de tarjeta de crédito para hacer pruebas de desarrollo, fuente paypal: https://www.paypalobjects.com/en_GB/vhelp/paypalmanager_help/credit_card_numbers.htm

Base de Datos en estado Suspect

Recientemente he tenido que reparar para una empresa multinacional una base de datos que se encontraba marcada como sospechosa para lo cual utilice el script que muestro abajo y que origina este artículo, pero como la intención no es ejecutar cualquier código que encontramos solo porque sí, principalmente en bases de datos grandes y de producción, comprometiendo los datos de la empresa, me di a la tarea de explicar por qué el uso de cada instrucción, que resulto en este texto.

En las bases de datos se pueden presentar problemas por falla de hardware como por ejemplo mal funcionamiento en los controladores de discos o los discos duros en sí, también las interrupciones en el suministro de energía eléctrica ocasionan problemas ya que cuando detenemos los servicios del SQL Server de manera correcta se realiza un proceso interno para dar de baja las bases de datos, identificando las transacciones completas e incompletas para dejarlas en un estado consistente,  este proceso generalmente no pasa cuando el servidor se apaga inesperadamente lo que puede corromper las páginas de datos mediante escrituras incompletas que derivan en páginas de datos corruptas.

Relacionado con los antivirus, otra causa común que provoca corrupción en las páginas de datos es cuando SQL Server deja de tener acceso exclusivo a los archivos de datos y otros procesos los utilizan, por ejemplo cuando no se configuran las excepciones en los antivirus para excluir los archivos de las bases de datos, los proceso de verificación de virus cuando revisan los archivos de las bases de datos  eventualmente los corrompen.

Cuando se alcanza el umbral de 1000 páginas corruptas la base de datos se coloca en modo SUSPECT como mecanismo de autoprotección.

Para resolver este problema podemos ejecutar las siguientes sentencias:

EXEC SP_RESETSTATUS  [Base de Datos];ALTER DATABASE [Base de Datos] SET EMERGENCYDBCC CHECKDB ([Base de Datos])ALTER DATABASE [Base de Datos] SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC CHECKDB ([Base de Datos], REPAIR_ALLOW_DATA_LOSS)ALTER DATABASE [Base de Datos] SET MULTI_USER

Ahora cual es la explicación de cada instrucción que usamos en el código anterior.

EXEC SP_RESETSTATUS  [Base de Datos];

Desactiva la marca de sospechoso de una base de datos. Este procedimiento actualiza las columnas de modo y estado de la base de datos con nombre en sys.databases. Se debe consultar el registro de errores de SQL Server y resolver todos los problemas antes de ejecutar este procedimiento. Después de ejecutar sp_resetstatus, detenga y reinicie la instancia de SQL Server

ALTER DATABASE [Base de Datos] SET EMERGENCY

Durante un proceso de recuperación de desastres, el estado de emergencia proporciona flexibilidad para realizar varias operaciones en una base de datos corrupta / sospechosa. Cuando se coloca una base de datos en el estado de emergencia, realiza tres cambios importantes en la configuración de la base de datos:

Set Emergency marca la base de datos como READ_ONLY, deshabilita el registro y el acceso está limitado a miembros del rol fijo de servidor sysadmin. Solamente los miembros del rol fijo de servidor sysadmin pueden establecer una base de datos en el estado EMERGENCY.

Aunque un estado sospechoso no es un requisito previo para poner una base de datos en un estado de emergencia, este es probablemente el momento más útil en el que se desea utilizar el estado de emergencia. Cuando una base de datos está en el estado de emergencia, puede acceder a sus datos, por lo que puede exportarla a otra base de datos e incluso puede ejecutar un DBCC CHECKDB en la base de datos para corregir la corrupción.

DBCC CHECKDB ([Base de Datos])

Comprueba la integridad física y lógica de todos los objetos de la base de datos especificada mediante la realización de las siguientes operaciones:

• Se ejecuta DBCC CHECKALLOC en la base de datos.
• Se ejecuta DBCC CHECKTABLE en cada tabla y vista en la base de datos.
• Se ejecuta DBCC CHECKCATALOG en la base de datos.
• Valida el contenido de cada vista indizada de la base de datos.
• Valida la coherencia de nivel de vínculo entre los archivos y directorios del sistema de archivos y metadatos de tabla al almacenar varbinary (max) datos del sistema de archivos con FILESTREAM.
• Valida los datos de Service Broker en la base de datos.

Esto significa que los comandos DBCC CHECKALLOC, DBCC CHECKTABLE o DBCC CHECKCATALOG no tienen que ejecutarse por separado de DBCC CHECKDB.

ALTER DATABASE [Base de Datos] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

El modo de usuario único se suele utilizar para operaciones de mantenimiento y especifica que solo un usuario puede tener acceso a la base de datos cada vez. La opción de terminación WITH ROLLBACK IMMEDIATE se especifica en la primera instrucción ALTER DATABASE . Esto hará que todas las transacciones incompletas se reviertan y que el resto de las conexiones a la base de datos se desconecten de inmediato.

DBCC CHECKDB ([Base de Datos], REPAIR_ALLOW_DATA_LOSS)

Especifica que DBCC CHECKDB repare los errores que encuentre, estas reparaciones pueden ocasionar alguna pérdida de datos. Utilice las opciones REPAIR solo como último recurso y tenga en cuenta que la base de datos especificada debe estar en modo de usuario único.

ALTER DATABASE [Base de Datos] SET MULTI_USER

Devuelve el acceso a la base de datos para todos los usuarios.

Este y otros temas de administración siguelos en el curso https://www.udemy.com/administracion-de-base-de-datos-con-sql-server/

PHP No Se Reconoce Como Comando Interno o Externo

Por cuestiones de trabajo me ha tocado trabajar con Magento un e-comerce durante el último año y recientemente tuve que formatear mi equipo y volver a configurar Apache usando Xampp en Windows 10.  Al trabajar con Magento para limpiar la cache o reindexar se tiene que ejecutar el comando "php bin/magento" acompañado de otras instrucciones pero al ejecutarlo recibí el mensaje de "php no se reconoce como comando interno o externo, programa o archivo por lotes ejecutable"
para resolver este error hicimos lo siguiente.

1, Abrimos la ventana de propiedades de Windows.

2. Luego hacemos clic en las opciones de la derecha en "Configuración Avanzada del Sistema"

3. En la ventana de Propiedades del Sistema, hacemos clic en la pestaña de Opciones Avanzadas y luego presionamos el botón de Variables de Entorno.

4. En el cuadro de abajo que es la sección de Variables del Sistema buscamos la opción de Path y hacemos doble clic, para abrir la ventana de variables de entorno.

5. En la ventana de variables de entorno, vamos a hacer clic en Nuevo y vamos a registrar el folder con la ubicación de los ejecutables de php, ubicados en la carpeta de Xampp > php, y listo.

6. Reiniciamos nuestra consola de comandos, y deberíamos poder utilizar el comando php.

Error al Iniciar Magento 2 Data Migration Version 1.25

Dese hace un año he estado involucrado en un proyecto con Magento y hemos tenido que migrar varias veces los datos de Magento 1.6 a Magento 2.2.4, pero en esta oportunidad la herramienta de Migración nos dio un error, así que lo documentamos para facilitar el trabajo en futuras oportunidades.

1. Descargamos del sitio la herramienta de migración del sitio: https://github.com/ubertheme/magento2_data_migration/releases

2.  Al descargar la carpeta en nuestro sitio y ejecutarlo nos devolvió el "Error 500" con los mensajes de php:

Notice: unserialize(): Unexpected end of serialized data in C:\xampp\htdocs\migracion\yii\caching\CCache.php on line 108

Notice: unserialize(): Error at offset 0 of 1 bytes in C:\xampp\htdocs\migracion\yii\caching\CCache.php on line 108

3. Para corregir este problema fuimos a abrir el archivo CCache.php ubicado en el folder yii>caching

4. Modificamos la línea 108 con la sintaxis: $value=unserialize($value); por el valor  $value=unserialize(base64_decode($value));

5. Y listo el problema se resolvió.

CHARLA SOBRE BACKUPS

Grabamos una sesión donde hablamos sobre backups, explicando primero las diferentes estrategias a partir de definir cada tipo de backup, luego en el segundo video ponemos manos a la obra y creamos los backups y restauramos para probar su funcioamiento, esto con el SQL Server de Microsoft