MSSQL Development – SQL Elemental

Como insertar filas en una tabla con identity?

A veces se ocupa poder insertar filas que se traen de una estructura vieja y hemos definido con anterioridad una columna con un identity. En estas ocasiones se hace necesario deshabilitar el comportamiento del identity.

Para lograrlo tenemos que hacer lo siguiente.

Ejecutar el comando [SET IDENTITY_INSERT dbo.MiTabla ON;]
Ejecutar un insert into, en este caso hago enfasis en que es necesario enumerar el nombre de la columna tanto en el insert como en la seccion del select.

SET IDENTITY_INSERT dbo.MiTabla ON;
GO
Insert into MiTabla (ID, Col1, Col2)
select ID, Col1, Col2
from MiTabla_old
where id> 1000
GO
SET IDENTITY_INSERT dbo.MiTabla OFF;

Es importante rescatar que cuando inserto estas nuevas filas el valor del identity se modificara a valor mas alto insertado.

Para validar el valor actual de la columna identity solamente necesitamos ejecutar el comando [DBCC CHECKIDENT], como se aprecia en el próximo ejemplo.

DBCC CHECKIDENT('MiTabla', NORESEED)

Cuando fue la ultima que se uso una tabla?

Hace rato que no escribia, he andado como siempre en mucha cosa. Estoy jugando un poco con PostGreSQL y Azure, seguramente empezare a incluir esa informacion en futuras entradas a este blog.

La respuesta a la pregunta del titulo me ha servido muchas veces como administrador de una base de datos a lo largo de los años.

Esta consulta esta basada en una vista de systema que analiza el uso de los indices de nuestras tablas.

sys.dm_db_index_usage_stats


SELECT  groupname = @groupname, casinoname = @casinoname, DB_NAME(a.database_id) Database_Name ,
        b.name Table_Name ,
        MAX(ISNULL(last_user_update,'2001-01-01')) last_user_update ,
        MAX(ISNULL(last_user_seek,'2001-01-01')) last_user_seek ,
        MAX(ISNULL(last_user_scan,'2001-01-01')) last_user_scan ,
        MAX(ISNULL(last_user_lookup,'2001-01-01')) last_user_lookup
FROM    sys.dm_db_index_usage_stats a
        INNER JOIN sys.tables b ON b.object_id = a.object_id
		INNER JOIN sys.indexes c ON c.object_id = a.object_id
                                    AND c.index_id = a.index_id
WHERE b.name = 'playerbalances'
GROUP BY a.database_id ,
        b.name
ORDER BY a.database_id ,
        b.name ;<span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			></span>

Utilizando este mismo codigo podemos determinar cuando fue la ultima vez que se utilizo uno de nuestros indices

SELECT  DB_NAME(a.database_id) Database_Name ,
        b.name Table_Name ,
        c.name Index_Name ,
        MAX(ISNULL(last_user_update,'2001-01-01')) last_user_update ,
        MAX(ISNULL(last_user_seek,'2001-01-01')) last_user_seek ,
        MAX(ISNULL(last_user_scan,'2001-01-01')) last_user_scan ,
        MAX(ISNULL(last_user_lookup,'2001-01-01')) last_user_lookup
FROM    sys.dm_db_index_usage_stats a
        INNER JOIN sys.tables b ON b.object_id = a.object_id
        INNER JOIN sys.indexes c ON c.object_id = a.object_id
                                    AND c.index_id = a.index_id
WHERE   a.database_id = DB_ID()
GROUP BY a.database_id ,
        b.name ,
        c.name
ORDER BY a.database_id ,
        b.name ,
        c.name;

Toda pieza de informacion es valiosa, en este caso puede ayudarme a ver si mi politica de creacion de indices esta sirviendo o no, puede indicarme cuales tablas tienen mas trafico y ayudarme a tomar decisiones para mejorar un problema de contencion.

Saludos

Referencia:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

Leer, buscar y filtrar el contenido del log de errores

Algunas veces se hace complicado encontrar lo que buscamos en el Log de SQL Server, debido a los muchos registros de información que se llevan a cabo por parte del sistema (auditorias de seguridad, ejecución de respaldos, etc)

--SIN FILTROS
EXEC xp_readerrorlog

Ejecutar la instruccion sin ningun filtro nos va a generar la misma cantidad de informacion tan abrumadora de la interfaz grafica. Dichosamente el procedimiento tiene parametros para filtrar la informacion:

Parametro #1 –
- Se usa para identificar el archivo a consultar.
- MSSQL guarda una cantidad configurable de archivos de registro de eventos, y se crea uno nuevo con cada registro Sin no se envia ningun valor en este parametro siempre se revisara el actual cuyo valor es de 0
Parametro #2 –
- Es un filtro para incluir eventos del motor o del Agente
  - 1 = SQL Server error log (default)
  - 2 = SQL Agent log
Parametro #3 –
- Es un filtro de busqueda del tipo NChar
Parametro #4 –
- Es un filtro adicional de busqueda del tipo NChar
Parametro #5 –
- Es un filtro de busqueda por fecha, en el que se incluyen los errores anteriores a la fecha definida
Parametro #6 –
- Es un filtro de busqueda por fecha, en el que se incluyen los errores posteriores a la fecha definida
Parametro #7 –
- Es un parametro para definir el ordenamiento de la informacion
  - – N’ASC’
  - – N’DESC’

--Con Filtros
EXEC xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'ASC'

La imagen que esta justo debajo de este texto es un ejemplo de la instancia que tengo instalada en mi maquina personal, y se muestran miles de miles de filas de informacion en una maquina de escritorio; que puede depararnos un server productivo con cientos, miles o millones de transacciones diarias.

Ref:

https://blogs.msdn.microsoft.com/askjay/2010/09/19/the-parameters-of-xp_readerrorlog/

Encontrar las dependencias en mi base de datos

Escribe esta consulta temprano para buscar las dependencias de un procedimiento que estaba modificando, pude haber utilizado el sp_depends para que me liste todas las dependencias pero no me hubiese listado los totales que es lo que realmente estaba buscando.

SELECT DISTINCT
OBJECT_NAME(o.object_id) ,
v.name ,
COUNT(1)
FROM
sys.objects o
INNER JOIN master.dbo.spt_values v ON o.type = SUBSTRING(v.name, 1, 2) COLLATE catalog_default
AND v.type = 'O9T'
INNER JOIN sys.sql_dependencies d ON o.object_id = d.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE
o.object_id = OBJECT_ID('<MIPROCEDIMIENTO>')
AND d.class < 2
GROUP BY
OBJECT_NAME(o.object_id) ,
v.name
ORDER BY
3 DESC;
GO


sp_depends '<MIPROCEDIMIENTO>';</pre>
<pre>

sp_executesql vrs sp_sqlexec

Estos procedimientos son utilizados comúnmente para ejecutar codigo Transact de manera dinamica; es codigo que armamos a partir de hileras para producir un producto que de otra forma seria un poco complicado.

sp_sqlexec esta en desuso, es un procedimiento que MSSQL ya no soporta y por tanto se deberia de reescribir cualquier codigo que lo este utilizando en este momento.

Cuando ejecutamos codigo dinamico podemos usar sp_executesql o bien exec. Con la diferencia de que el sp_executesql probablemente creara un plan de ejecucion que se pueda reutilizar para ejecuciones posteriores.

Sintaxis


USE master
GO

DECLARE @vsql NVARCHAR(max)

SET @vsql = 'select top 210 * from sys.tables'

EXEC(@vsql)

EXEC sys.sp_sqlexec @vsql

EXEC sys.sp_executesql @vsql