Recursos ocultos do SQL Server

Quais são alguns dos resources ocultos do SQL Server ?

Por exemplo, stored procedures do sistema não documentado, truques para fazer coisas que são muito úteis mas não documentadas o suficiente?


Respostas

Obrigado a todos por todas as ótimas respostas!

Procedimentos armazenados

  • sp_msforeachtable: executa um comando com ‘?’ substituído por cada nome de tabela (v6.5 e superior)
  • sp_msforeachdb: Executa um comando com ‘?’ substituído por cada nome de database (v7 e acima)
  • sp_who2: apenas como sp_who, mas com muito mais informações para solucionar problemas de blocos (v7 e superior)
  • sp_helptext: Se você quiser o código de um procedimento armazenado, visualize & UDF
  • sp_tables: retorna uma lista de todas as tabelas e visualizações do database no escopo.
  • sp_stored_procedures: retorna uma lista de todos os stored procedures
  • xp_sscanf: Lê os dados da string para os locais de argumentos especificados por cada argumento de formato.
  • xp_fixeddrives:: Encontre a unidade fixa com o maior espaço livre
  • sp_help: Se você quiser conhecer a estrutura da tabela, índices e restrições de uma tabela. Também visualizações e UDFs. Atalho é Alt + F1

Trechos

  • Retornando linhas em ordem aleatória
  • Todos os objects de usuário do database pela data da última modificação
  • Apenas data de retorno
  • Encontre registros que data cai em algum lugar dentro da semana atual.
  • Encontre registros que ocorreram na semana passada.
  • Retorna a data para o início da semana atual.
  • Retorna a data para o início da semana passada.
  • Veja o texto de um procedimento que foi implantado em um servidor
  • Solte todas as conexões para o database
  • Tabela Checksum
  • Checksum de linha
  • Solte todos os procedimentos em um database
  • Mapeie novamente os IDs de login após a restauração
  • Chamar stored procedures de uma instrução INSERT
  • Encontre procedimentos por palavra-chave
  • Solte todos os procedimentos em um database
  • Consultar o log de transactions de um database programaticamente.

Funções

  • HashBytes ()
  • EncryptByKey
  • Comando PIVOT

Misc

  • Extras da String de Conexão
  • TableDiff.exe
  • Gatilhos para Eventos de Logon (Novo no Service Pack 2)
  • Aumentando o desempenho com colunas computadas persistentes (pcc).
  • Configuração DEFAULT_SCHEMA em sys.database_principles
  • Parametrização Forçada
  • Formato de armazenamento vardecimal
  • Descobrir as consultas mais populares em segundos
  • Bancos de dados compartilhados escalonáveis
  • Recurso Filtro de Tabela / Stored Procedure no SQL Management Studio
  • Sinalizadores de rastreamento
  • Número após um GO repete o lote
  • Segurança usando esquemas
  • Criptografia usando funções de criptografia incorporadas, visualizações e tabelas base com gatilhos

No Management Studio, você pode colocar um número após um marcador de fim de lote GO para fazer com que o lote seja repetido esse número de vezes:

 PRINT 'X' GO 10 

Vai imprimir ‘X’ 10 vezes. Isso pode salvar você de copiar / colar tedioso ao fazer coisas repetitivas.

Muitos desenvolvedores do SQL Server ainda não parecem saber sobre a cláusula OUTPUT (SQL Server 2005 e mais recente) na instrução DELETE, INSERT e UPDATE.

Pode ser extremamente útil saber quais linhas foram INSERTadas, ATUALIZADAS ou EXCLUIDAS, e a cláusula OUTPUT permite fazer isso com muita facilidade – permite access às tabelas “virtuais” chamadas inserted e deleted (como nos gatilhos):

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

Se você estiver inserindo valores em uma tabela que tenha um campo de chave primária IDENTITY INT, com a cláusula OUTPUT, você poderá obter o novo ID inserido imediatamente:

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

E se você está atualizando, pode ser extremamente útil saber o que mudou – neste caso, inserted representa os novos valores (após o UPDATE), enquanto que deleted se referem aos valores antigos antes do UPDATE:

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

Se muitas informações forem retornadas, a saída de OUTPUT também poderá ser redirecionada para uma tabela temporária ou uma variável de tabela ( OUTPUT INTO @myInfoTable ).

Extremamente útil – e muito pouco conhecido!

Marc

sp_msforeachtable : executa um comando com ‘?’ substituído por cada nome de tabela. por exemplo

 exec sp_msforeachtable "dbcc dbreindex('?')" 

Você pode emitir até 3 comandos para cada tabela

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

Além disso, sp_MSforeachdb

Extras da String de Conexão:

MultipleActiveResultSets = true;

Isso faz com que o ADO.Net 2.0 e posterior leiam vários conjuntos de resultados somente leitura e somente de encaminhamento em uma única conexão de database, o que pode melhorar o desempenho se você estiver lendo muito. Você pode ativá-lo mesmo se estiver fazendo uma mistura de tipos de consulta.

Nome da aplicação = MyProgramName

Agora, quando você quiser ver uma lista de conexões ativas consultando a tabela sysprocesses, o nome do seu programa aparecerá na coluna nome_do_programa em vez de “.Net SqlClient Data Provider”

TableDiff.exe

  • A ferramenta Table Difference permite descobrir e reconciliar as diferenças entre uma tabela de origem e de destino ou uma exibição. O Utilitário Tablediff pode relatar diferenças no esquema e nos dados. O recurso mais popular do tablediff é o fato de que ele pode gerar um script que você pode executar no destino que reconciliará as diferenças entre as tabelas.

Ligação

Uma técnica menos conhecida do TSQL para retornar linhas em ordem aleatória:

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

No Management Studio, você pode obter rapidamente uma lista de colunas delimitada por vírgulas para uma tabela por:

  1. No Pesquisador de Objetos, expanda os nós sob uma determinada tabela (assim você verá pastas para Colunas, Chaves, Restrições, Disparadores etc.)
  2. Aponte para a pasta Colunas e arraste para uma consulta.

Isso é útil quando você não deseja usar o formato hediondo retornado clicando com o botão direito do mouse na tabela e escolhendo a Tabela de Script como … e, em seguida, Inserir para … Esse truque funciona com as outras pastas que ele lhe dará uma lista delimitada por vírgulas de nomes contidos na pasta.

Construtores de linha

Você pode inserir várias linhas de dados com uma única instrução de inserção.

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

Se você quiser saber a estrutura da tabela, índices e restrições:

 sp_help 'TableName' 

HashBytes () para retornar o hash MD2, MD4, MD5, SHA ou SHA1 de sua input.

Descobrir as consultas mais populares

  • Com sys.dm_exec_query_stats, você pode descobrir muitas combinações de análises de consulta por uma única consulta.

Link com o commnad

 select * from sys.dm_exec_query_stats order by execution_count desc 

A guia de resultados espaciais pode ser usada para criar arte .

insira a descrição do link aqui http://sofpt.miximages.com/sql-server/venus.png

EXCETO E INTERSEÇÃO

Em vez de escrever junções e subconsultas elaboradas, essas duas palavras-chave são uma maneira abreviada muito mais elegante e legível de expressar a intenção da sua consulta ao comparar dois resultados da consulta. Novo a partir do SQL Server 2005, eles complementam fortemente o UNION, que já existe na linguagem TSQL há anos.

Os conceitos de EXCEPT, INTERSECT e UNION são fundamentais na teoria dos conjuntos, que serve como base e fundamento da modelagem relacional usada por todos os RDBMS modernos. Agora, os resultados do tipo de diagrama Venn podem ser mais intuitivamente e facilmente gerados usando o TSQL.

Eu sei que não é exatamente escondido, mas muitas pessoas não sabem sobre o comando Pivot . Consegui alterar um procedimento armazenado que usava cursores e levei 2 minutos para executar um código de 6 segundos que era um décimo do número de linhas!

útil ao restaurar um database para fins de teste ou qualquer outra coisa. Mapeia novamente o ID de login corretamente:

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

Solte todas as conexões para o database:

 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 

Tabela Checksum

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

Checksum de linha

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

Não tenho certeza se isso é um recurso oculto ou não, mas me deparei com isso e achei útil em muitas ocasiões. Você pode concatenar um conjunto de um campo em uma única instrução de seleção, em vez de usar um cursor e fazer um loop através da instrução select.

Exemplo:

 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, 

Se você quiser o código de um procedimento armazenado, você pode:

 sp_helptext 'ProcedureName' 

(não tenho certeza se é recurso oculto, mas eu uso o tempo todo)

Um truque de procedimento armazenado é que você pode chamá-los de uma instrução INSERT. Achei isso muito útil quando estava trabalhando em um database do 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 

No SQL Server 2005/2008 para mostrar números de linha em um resultado da consulta SELECT:

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

ORDER BY é uma cláusula obrigatória. A cláusula OVER () informa ao SQL Engine para classificar dados na coluna especificada (neste caso, OrderId) e atribuir números de acordo com os resultados de sorting.

Útil para analisar argumentos do procedimento armazenado: xp_sscanf

Lê os dados da string para os locais de argumentos especificados por cada argumento de formato.

O exemplo a seguir usa xp_sscanf para extrair dois valores de uma string de origem com base em suas posições no formato da string de origem.

 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 

Aqui está o conjunto de resultados.

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

Apenas data de retorno

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

ou

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

dm_db_index_usage_stats

Isso permite que você saiba se os dados em uma tabela foram atualizados recentemente, mesmo se você não tiver uma coluna DateUpdated na tabela.

 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/

Informações referenciadas de: SQL Server – Qual é a data / hora da última linha inserida de uma tabela?

Disponível no SQL 2005 e posterior

Aqui estão algumas características que acho úteis, mas muitas pessoas não parecem saber sobre:

 sp_tables 

Retorna uma lista de objects que podem ser consultados no ambiente atual. Isso significa qualquer object que possa aparecer em uma cláusula FROM, exceto objects de sinônimos.

Ligação

 sp_stored_procedures 

Retorna uma lista de stored procedures no ambiente atual.

Ligação

Encontre registros que data cai em algum lugar dentro da semana atual.

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

Encontre registros que ocorreram na semana passada.

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

Retorna a data para o início da semana atual.

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

Retorna a data para o início da semana passada.

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

Não é muito um recurso oculto, mas a configuração de mapeamentos de teclas no Management Studio em Ferramentas \ Opções \ Teclado: Alt + F1 tem como padrão sp_help “texto selecionado”, mas não posso viver sem adicionar Ctrl + F1 para sp_helptext “texto selecionado”

Colunas Computadas Persistentes

  • Colunas computadas podem ajudá-lo a mudar o custo de computação do tempo de execução para a fase de modificação de dados. A coluna computada é armazenada com o restante da linha e é utilizada de forma transparente quando a expressão nas colunas calculadas e a consulta correspondem. Você também pode criar índices nos PCCs para acelerar filtrações e varreduras de alcance na expressão.

Ligação

Há momentos em que não há coluna adequada para classificar ou apenas a ordem de sorting padrão em uma tabela e você deseja enumerar cada linha. Para fazer isso, você pode colocar “(selecione 1)” na cláusula “order by” e obter o que deseja. Puro, né?

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

Criptografia simples com EncryptByKey