Características ocultas de SQL Server

¿Cuáles son algunas características ocultas de SQL Server ?

Por ejemplo, procedimientos almacenados en el sistema no documentado, trucos para hacer cosas que son muy útiles pero no están suficientemente documentadas.


Respuestas

Gracias a todos por todas las excelentes respuestas!

Procedimientos almacenados

  • sp_msforeachtable: ejecuta un comando con ‘?’ reemplazado con cada nombre de tabla (v6.5 y superior)
  • sp_msforeachdb: ejecuta un comando con ‘?’ reemplazado con cada nombre de base de datos (v7 y superior)
  • sp_who2: al igual que sp_who, pero con mucha más información para solucionar problemas de bloques (v7 y superior)
  • sp_helptext: si desea el código de un procedimiento almacenado, vea & UDF
  • sp_tables: devuelve una lista de todas las tablas y vistas de la base de datos en el scope.
  • sp_stored_procedures: devuelve una lista de todos los procedimientos almacenados
  • xp_sscanf: Lee los datos de la cadena en las ubicaciones de los argumentos especificados por cada argumento de formato.
  • xp_fixeddrives: Encuentra la unidad fija con mayor espacio libre
  • sp_help: si quieres saber la estructura de la tabla, los índices y las restricciones de una tabla. También vistas y UDF. El atajo es Alt + F1

Fragmentos

  • Devolviendo filas en orden aleatorio
  • Todos los objetos de usuario de la base de datos por fecha de última modificación
  • Solo fecha de regreso
  • Encontrar registros que fecha cae en algún lugar dentro de la semana actual.
  • Encontrar registros que fecha ocurrió la semana pasada.
  • Devuelve la fecha del comienzo de la semana actual.
  • Devuelve la fecha del comienzo de la semana pasada.
  • Ver el texto de un procedimiento que se ha implementado en un servidor
  • Eliminar todas las conexiones a la base de datos
  • Tabla de sum de comprobación
  • Checksum de fila
  • Suelta todos los procedimientos en una base de datos
  • Vuelva a asignar los Id. De inicio de sesión correctamente después de restaurar
  • Procedimientos almacenados de llamada desde una instrucción INSERT
  • Buscar procedimientos por palabra clave
  • Suelta todos los procedimientos en una base de datos
  • Consulta el registro de transacciones de una base de datos mediante progtwigción.

Funciones

  • HashBytes ()
  • EncryptByKey
  • Comando PIVOT

Misc

  • Cadena de conexión extras
  • TableDiff.exe
  • Desencadenadores para eventos de inicio de sesión (nuevo en Service Pack 2)
  • Potenciar el rendimiento con persisted-calculated-columns (pcc).
  • Configuración DEFAULT_SCHEMA en sys.database_principles
  • Parametrización Forzada
  • Formato de almacenamiento vardecimal
  • Averiguar las consultas más populares en segundos
  • Bases de datos compartidas escalables
  • Función de tabla / filtro de procedimiento almacenado en SQL Management Studio
  • Banderas de rastreo
  • El número después de un GO repite el lote
  • Seguridad usando esquemas
  • Cifrado usando funciones de cifrado integradas, vistas y tablas base con desencadenantes

En Management Studio, puede colocar un número después de un marcador GO end-of-batch para hacer que el lote se repita la cantidad de veces:

 PRINT 'X' GO 10 

Se imprimirá ‘X’ 10 veces. Esto puede evitarle copiar / pegar tediosamente cuando hace cosas repetitivas.

Muchos desarrolladores de SQL Server todavía no parecen saber acerca de la cláusula OUTPUT (SQL Server 2005 y posteriores) en las instrucciones DELETE, INSERT y UPDATE.

Puede ser extremadamente útil saber qué filas se han INSERTADO, ACTUALIZADO o ELIMINADO, y la cláusula OUTPUT permite hacer esto muy fácilmente: permite el acceso a las tablas “virtuales” llamadas inserted y deleted (como en desencadenantes):

 DELETE FROM (table) OUTPUT deleted.ID, deleted.Description WHERE (condition) 

Si está insertando valores en una tabla que tiene un campo de clave principal INT IDENTITY, con la cláusula OUTPUT, puede obtener la nueva ID insertada de inmediato:

 INSERT INTO MyTable(Field1, Field2) OUTPUT inserted.ID VALUES (Value1, Value2) 

Y si está actualizando, puede ser extremadamente útil saber qué cambió; en este caso, inserted los valores nuevos (después de la ACTUALIZACIÓN), mientras que los deleted refieren a los valores anteriores antes de la ACTUALIZACIÓN:

 UPDATE (table) SET field1 = value1, field2 = value2 OUTPUT inserted.ID, deleted.field1, inserted.field1 WHERE (condition) 

Si se devolverá mucha información, la salida de OUTPUT también se puede redirigir a una tabla temporal o una variable de tabla ( OUTPUT INTO @myInfoTable ).

Extremadamente útil, ¡y muy poco conocido!

Bagazo

sp_msforeachtable : ejecuta un comando con ‘?’ reemplazado con cada nombre de tabla. p.ej

 exec sp_msforeachtable "dbcc dbreindex('?')" 

Puede emitir hasta 3 comandos para cada tabla

 exec sp_msforeachtable @Command1 = 'print ''reindexing table ?''', @Command2 = 'dbcc dbreindex(''?'')', @Command3 = 'select count (*) [?] from ?' 

Además, sp_MSforeachdb

Extras de la cadena de conexión:

MultipleActiveResultSets = true;

Esto hace que ADO.Net 2.0 y posteriores lean conjuntos de resultados de solo lectura, solo de reenvío, en una sola conexión de base de datos, lo que puede mejorar el rendimiento si lees mucho. Puede activarlo incluso si está haciendo una combinación de tipos de consultas.

Nombre de la aplicación = MyProgramName

Ahora, cuando desee ver una lista de conexiones activas consultando la tabla sysprocesses, el nombre de su progtwig aparecerá en la columna program_name en lugar de “.Net SqlClient Data Provider”.

TableDiff.exe

  • La herramienta Diferencia de tabla le permite descubrir y reconciliar las diferencias entre una tabla de origen y de destino o una vista. La utilidad Tablediff puede informar las diferencias en el esquema y los datos. La característica más popular de tablediff es el hecho de que puede generar un script que puede ejecutarse en el destino que reconciliará las diferencias entre las tablas.

Enlazar

Una técnica de TSQL menos conocida para devolver filas en orden aleatorio:

 -- Return rows in a random order SELECT SomeColumn FROM SomeTable ORDER BY CHECKSUM(NEWID()) 

En Management Studio, puede obtener rápidamente una lista de columnas delimitada por comas para una tabla de la siguiente manera:

  1. En el Explorador de objetos, expanda los nodos debajo de una tabla determinada (para que pueda ver las carpetas de Columnas, Llaves, Restricciones, Disparadores, etc.)
  2. Apunte a la carpeta Columnas y arrastre a una consulta.

Esto es útil cuando no quiere usar un formato atroz devuelto al hacer clic con el botón derecho en la tabla y seleccionar Tabla de script como …, luego Insertar en … Este truco sí funciona con las otras carpetas, ya que le dará una lista de nombres delimitada por comas contenida en la carpeta.

Constructores de filas

Puede insertar múltiples filas de datos con una sola instrucción de inserción.

 INSERT INTO Colors (id, Color) VALUES (1, 'Red'), (2, 'Blue'), (3, 'Green'), (4, 'Yellow') 

Si desea conocer la estructura de la tabla, los índices y las restricciones:

 sp_help 'TableName' 

HashBytes () para devolver el hash MD2, MD4, MD5, SHA o SHA1 de su entrada.

Averiguar las consultas más populares

  • Con sys.dm_exec_query_stats, puede descubrir muchas combinaciones de análisis de consultas mediante una sola consulta.

Enlace con el commnad

 select * from sys.dm_exec_query_stats order by execution_count desc 

La pestaña de resultados espaciales se puede usar para crear arte .

ingrese la descripción del enlace aquí http://sofes.miximages.com/sql-server/venus.png

EXCEPTO e INTERSECTA

En lugar de escribir combinaciones elaboradas y subconsultas, estas dos palabras clave son una manera mucho más elegante de taquigrafía y legible de express el bash de su consulta al comparar dos resultados de consulta. Nuevo a partir de SQL Server 2005, complementan fuertemente a UNION que ya existe en el lenguaje TSQL desde hace años.

Los conceptos de EXCEPT, INTERSECT y UNION son fundamentales en la teoría de conjuntos, que sirve como base y fundamento del modelado relacional utilizado por todos los RDBMS modernos. Ahora, los resultados del tipo de diagtwig de Venn se pueden generar de forma más intuitiva y fácil usando TSQL.

Sé que no está exactamente oculto, pero no mucha gente sabe sobre el comando PIVOT . Pude cambiar un procedimiento almacenado que usaba cursores y tardé 2 minutos en encontrar un código rápido de 6 segundos que era una décima parte del número de líneas.

útil al restaurar una base de datos para fines de prueba o lo que sea. Replanta la ID de inicio de sesión correctamente:

 EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-36' 

Suelta todas las conexiones a la base de datos:

 Use Master Go Declare @dbname sysname Set @dbname = 'name of database you want to drop connections from' Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) While @spid Is Not Null Begin Execute ('Kill ' + @spid) Select @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) and spid > @spid End 

Tabla de sum de comprobación

 Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK) 

Checksum de fila

 Select CheckSum_Agg(Binary_CheckSum(*)) From Table With (NOLOCK) Where Column = Value 

No estoy seguro de si esta es una función oculta o no, pero me encontré con esto, y me pareció útil en muchas ocasiones. Puede concatenar un conjunto de un campo en una sola instrucción de selección, en lugar de usar un cursor y recorrer la instrucción de selección.

Ejemplo:

 DECLARE @nvcConcatonated nvarchar(max) SET @nvcConcatonated = '' SELECT @nvcConcatonated = @nvcConcatonated + C.CompanyName + ', ' FROM tblCompany C WHERE C.CompanyID IN (1,2,3) SELECT @nvcConcatonated 

Resultados:

 Acme, Microsoft, Apple, 

Si desea el código de un procedimiento almacenado, puede:

 sp_helptext 'ProcedureName' 

(no estoy seguro si es una característica oculta, pero la uso todo el tiempo)

Un truco de procedimiento almacenado es que puede llamarlos desde una instrucción INSERT. Encontré esto muy útil cuando estaba trabajando en una base de datos de SQL Server.

 CREATE TABLE #toto (v1 int, v2 int, v3 char(4), status char(6)) INSERT #toto (v1, v2, v3, status) EXEC dbo.sp_fulubulu(sp_param1) SELECT * FROM #toto DROP TABLE #toto 

En SQL Server 2005/2008 para mostrar los números de fila en un resultado de consulta SELECT:

 SELECT ( ROW_NUMBER() OVER (ORDER BY OrderId) ) AS RowNumber, GrandTotal, CustomerId, PurchaseDate FROM Orders 

ORDER BY es una cláusula obligatoria. La cláusula OVER () le dice a SQL Engine que ordene los datos en la columna especificada (en este caso OrderId) y asigne números según los resultados de clasificación.

Útil para analizar argumentos de procedimientos almacenados: xp_sscanf

Lee los datos de la cadena en las ubicaciones de los argumentos especificados por cada argumento de formato.

El siguiente ejemplo usa xp_sscanf para extraer dos valores de una cadena fuente en función de sus posiciones en el formato de la cadena fuente.

 DECLARE @filename varchar (20), @message varchar (20) EXEC xp_sscanf 'sync -b -fproducts10.tmp -rrandom', 'sync -b -f%s -r%s', @filename OUTPUT, @message OUTPUT SELECT @filename, @message 

Aquí está el conjunto de resultados.

 -------------------- -------------------- products10.tmp random 

Solo fecha de regreso

 Select Cast(Floor(Cast(Getdate() As Float))As Datetime) 

o

 Select DateAdd(Day, 0, DateDiff(Day, 0, Getdate())) 

dm_db_index_usage_stats

Esto le permite saber si los datos en una tabla se han actualizado recientemente, incluso si no tiene una columna DateUpdated en la tabla.

 SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,* FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'MyDatabase') AND OBJECT_ID=OBJECT_ID('MyTable') 

Código de: http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/

Información a la que se hace referencia desde: SQL Server: ¿Cuál es la fecha / hora de la última fila insertada de una tabla?

Disponible en SQL 2005 y posterior

Aquí hay algunas características que considero útiles pero que mucha gente no parece saber sobre:

 sp_tables 

Devuelve una lista de objetos que se pueden consultar en el entorno actual. Esto significa cualquier objeto que pueda aparecer en una cláusula FROM, excepto los objetos de sinónimos.

Enlazar

 sp_stored_procedures 

Devuelve una lista de procedimientos almacenados en el entorno actual.

Enlazar

Encontrar registros que fecha cae en algún lugar dentro de la semana actual.

 where dateadd( week, datediff( week, 0, TransDate ), 0 ) = dateadd( week, datediff( week, 0, getdate() ), 0 ) 

Encontrar registros que fecha ocurrió la semana pasada.

 where dateadd( week, datediff( week, 0, TransDate ), 0 ) = dateadd( week, datediff( week, 0, getdate() ) - 1, 0 ) 

Devuelve la fecha del comienzo de la semana actual.

 select dateadd( week, datediff( week, 0, getdate() ), 0 ) 

Devuelve la fecha del comienzo de la semana pasada.

 select dateadd( week, datediff( week, 0, getdate() ) - 1, 0 ) 

No es tanto una función oculta como la configuración de las asignaciones de teclas en Management Studio en Herramientas \ Opciones \ Teclado: Alt + F1 está predeterminado para sp_help “texto seleccionado” pero no puedo vivir sin agregar Ctrl + F1 para sp_helptext “texto seleccionado”

Persisted-calculated-columns

  • Las columnas calculadas pueden ayudarlo a cambiar el costo de cálculo del tiempo de ejecución a la fase de modificación de datos. La columna calculada se almacena con el rest de la fila y se utiliza de forma transparente cuando coincide la expresión en las columnas calculadas y la consulta. También puede crear índices en los PCC para acelerar las filtraciones y escaneos de rango en la expresión.

Enlazar

Hay momentos en los que no hay una columna adecuada para ordenar, o simplemente desea el orden de clasificación predeterminado en una tabla y desea enumerar cada fila. Para hacerlo, puede poner “(seleccionar 1)” en la cláusula “ordenar por” y obtendrá lo que desea. Neat, ¿eh?

 select row_number() over (order by (select 1)), * from dbo.Table as t 

Encriptación simple con EncryptByKey