studio - Características ocultas de SQL Server

transact sql mysql

Question

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

Por ejemplo, procedimientos almacenados del sistema no documentados, trucos para hacer cosas que son muy útiles pero no están lo suficientemente documentados.

Respuestas

Gracias a todos por todas las grandes 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: igual que sp_who, pero con mucha más información para solucionar problemas de bloques (v7 y superiores)
• 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 alcance.
• sp_stored_procedures: devuelve una lista de todos los procedimientos almacenados
• xp_sscanf: lee datos de la cadena en las ubicaciones de argumentos especificadas por cada argumento de formato.
• xp_fixeddrives:: encuentra la unidad fija con el mayor espacio libre
• sp_help: si desea conocer la estructura de la tabla, los índices y las restricciones de una tabla. También vistas y UDFs. El atajo es Alt + F1

Fragmentos

• Volviendo filas en orden aleatorio
• Todos los objetos de usuario de la base de datos por última fecha de modificación
• Solo fecha de regreso
• Encuentra los registros que la fecha cae en algún lugar dentro de la semana actual.
• Encuentra los registros en que fecha ocurrió la semana pasada.
• Devuelve la fecha para el comienzo de la semana actual.
• Devuelve la fecha del comienzo de la semana pasada.
• Consulte el texto de un procedimiento que se ha implementado en un servidor.
• Soltar todas las conexiones a la base de datos
• Suma de control de mesa
• Suma de comprobación de la fila
• Soltar todos los procedimientos en una base de datos.
• Vuelva a asignar los identificadores de inicio de sesión correctamente después de la restauración
• Llame a procedimientos almacenados desde una declaración INSERT
• Buscar procedimientos por palabra clave
• Soltar todos los procedimientos en una base de datos.
• Consultar el registro de transacciones para una base de datos mediante programación.

Funciones

• HashBytes ()
• EncryptByKey
• Comando PIVOT

Misceláneo

• Cadena de conexión extras
• TableDiff.exe
• Desencadenadores para eventos de inicio de sesión (Nuevo en Service Pack 2)
• Aumentar el rendimiento con columnas computadas persistentes (pcc).
• Configuración de DEFAULT_SCHEMA en sys.database_principles
• Parametrización forzada
• Formato de almacenamiento vardecimal
• Descubrir las consultas más populares en segundos.
• Bases de datos compartidas escalables
• Función de filtro de tabla / procedimiento almacenado en SQL Management Studio
• Banderas de rastreo
• Número después de que un GO repite el lote
• Seguridad utilizando esquemas.
• Cifrado mediante funciones de cifrado integradas, vistas y tablas base con activadores

Answer 1

Útil para analizar argumentos de procedimientos almacenados: xp_sscanf

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

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

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

Answer 2

Aquí hay algunas características que me parecen útiles, pero muchas personas no parecen 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ónimo.

Link

sp_stored_procedures

Devuelve una lista de procedimientos almacenados en el entorno actual.

Link

Answer 3

Cadena de conexión extras:

MultipleActiveResultSets = true;

Esto hace que ADO.Net 2.0 y versiones posteriores lean varios conjuntos de resultados de solo lectura hacia adelante y solo en una sola base de datos, lo que puede mejorar el rendimiento si está leyendo mucho. Puede activarlo incluso si está realizando una combinación de tipos de consulta.

Nombre de la aplicación = MyProgramName

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

Answer 4

Cifrado simple con EncryptByKey

Answer 5

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 dada (para que vea las carpetas de Columnas, Claves, Restricciones, Desencadenadores, etc.)
2. Apunte a la carpeta Columnas y arrástrelo a una consulta.

Esto es útil cuando no desea usar el formato atroz que se devuelve al hacer clic con el botón derecho en la tabla y al seleccionar Tabla de secuencias de comandos como ..., luego Insertar en ... Este truco funciona con las otras carpetas que le brindarán una lista delimitada por comas de nombres contenidos dentro de la carpeta.

Answer 6

En Management Studio, puede poner un número después de un marcador GO de fin de lote para hacer que el lote se repita esa cantidad de veces:

PRINT ''X''
GO 10

Imprimirá ''X'' 10 veces. Esto puede evitarle la tediosa copia / pegado al hacer cosas repetitivas.

Answer 7

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 al motor SQL que ordene los datos en la columna especificada (en este caso, OrderId) y asigne números según los resultados de la clasificación.

Answer 8

Encuentra los registros que la 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 )

Encuentra los registros en 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 para el 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 )

Answer 9

Es útil cuando se restaura una base de datos para propósitos de prueba o lo que sea. Re-mapea la ID de inicio de sesión correctamente:

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

Answer 10

Hay ocasiones en que no hay una columna adecuada para ordenar, o simplemente desea el orden predeterminado en una tabla y desea enumerar cada fila. Para hacer eso, puede poner "(seleccione 1)" en la cláusula "ordenar por" y obtendrá lo que desea. Limpio, ¿eh?

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

Answer 11

No es tanto una característica oculta, pero la configuración de asignaciones de teclas en Management Studio en Herramientas / Opciones / Teclado: Alt + F1 está predeterminada como sp_help "texto seleccionado" pero no puedo vivir sin agregar Ctrl + F1 para sp_helptext "texto seleccionado"

Answer 12

No estoy seguro de si esta es una característica oculta o no, pero me topé con esto, y he encontrado que es útil en muchas ocasiones. Puede concatinar un conjunto de un campo en una sola instrucción de selección, en lugar de usar un cursor y recorrer en bucle 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,

Answer 13

Parece que muchos desarrolladores de SQL Server aún no conocen la cláusula OUTPUT (SQL Server 2005 y posteriores) en la declaración DELETE, INSERT y UPDATE.

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

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

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

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

Y si está actualizando, puede ser extremadamente útil saber qué ha cambiado; en este caso, inserted representa los nuevos valores (después de ACTUALIZAR), mientras que los deleted refieren a los valores anteriores antes de ACTUALIZAR:

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

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

Extremadamente útil - y muy poco conocido!

Bagazo

Answer 14

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

Answer 15

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

sp_help ''TableName''

Answer 16

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

sp_helptext ''ProcedureName''

(No estoy seguro si es una función oculta, pero la uso todo el tiempo)

Answer 17

Solo fecha de regreso

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

o

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

Answer 18

Suelte 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

Answer 19

Suma de control de mesa

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

Suma de comprobación de la fila

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

Answer 20

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

Answer 21

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

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

Answer 22

dm_db_index_usage_stats

Esto le permite saber si los datos de 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 versiones posteriores.

Answer 23

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

ingrese la descripción del enlace aquí http://michaeljswart.com/wp-content/uploads/2010/02/venus.png

Answer 24

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

Answer 25

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 ?''

También, sp_MSforeachdb

Answer 26

EXCEPTO e INTERSECTAR

En lugar de escribir combinaciones y subconsultas elaboradas, estas dos palabras clave son una forma abreviada y mucho más elegante de expresar la intención de su consulta cuando se comparan los resultados de dos consultas. Como novedad de SQL Server 2005, complementan fuertemente a UNION, que ya existe desde hace años en el lenguaje TSQL.

Los conceptos de EXCEPTO, INTERSECTO y UNIÓN 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 diagrama de Venn se pueden generar de forma más intuitiva y sencilla utilizando TSQL.

Answer 27

Averiguar las consultas más populares.

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

Link con el commnad

select * from sys.dm_exec_query_stats 
order by execution_count desc

Answer 28

Columnas computadas persistentes

• 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 resto de la fila y se utiliza de forma transparente cuando la expresión en las columnas calculadas y la consulta coinciden. También puede crear índices en los PCC para acelerar las filtraciones y exploraciones de rango en la expresión.

Link

Answer 29

Constructores de hileras

Puede insertar varias 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'')

Answer 30

TableDiff.exe

• La herramienta Diferencia de tabla le permite descubrir y reconciliar las diferencias entre una tabla de origen y destino o una vista. La utilidad Tablediff puede reportar 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 ejecutar en el destino que reconciliará las diferencias entre las tablas.

Link