的SQL Server的隐藏的功能(Hidden Features of SQL Server)

什么是一些隐藏的功能的SQL Server ？

例如，无证系统存储过程，技巧，做的事情，这是非常有用的，但没有记录就够了吗？

---

答案

感谢大家为所有伟大的答案！

存储过程

• sp_msforeachtable：运行带有命令“？” 与每个表名替换（V6.5和向上）
• sp_msforeachdb：运行带有命令“？” 与每个数据库名称所取代（V7及以上）
• sp_who2：就像sp_who的，但与故障排除块多了很多信息（V7及以上）
• sp_helptext的：如果你想有一个存储过程的代码，视图和UDF
• sp_tables的：返回所有表和范围的意见数据库的列表。
• sp_stored_procedures：返回的所有存储过程的列表
• xp_sscanf：从字符串转换成由每个格式参数指定的参数的位置读取数据。
• xp_fixeddrives：查找与最大的自由空间的固定驱动器
• 则sp_help：如果你想知道一个表的表结构，索引和约束。 此外意见和UDF。 快捷键是Alt + F1

片段

• 按随机顺序返回行
• 通过最后修改日期所有数据库用户对象
• 返回日期只有
• 查找该日期落在本周内某处记录。
• 查找上周发生的日期记录。
• 返回当前周的开始日期。
• 返回上周开始的日期。
• 见已部署到服务器的过程的文本
• 拖放到数据库的所有连接
• 表校验
• 行校验
• 删除数据库中的所有程序
• 重新映射登录ID正确还原后
• 从INSERT语句调用存储过程
• 查找程序按关键字
• 删除数据库中的所有程序
• 查询事务日志程序的数据库。

功能

• HASHBYTES（）
• EncryptByKey的
• 转动命令

杂项

• 连接字符串临时演员
• TableDiff.exe
• 触发登录事件（在新的Service Pack 2）
• 与持续计算的柱（PCC）提高性能。
• 在sys.database_principles DEFAULT_SCHEMA设置
• 强制参数
• vardecimal存储格式
• 搞清楚在几秒钟内最热门查询
• 可伸缩共享数据库
• 在SQL Management Studio中表/存储过程筛选功能
• 跟踪标志
• 后一个数GO重复批次
• 使用安全模式
• 加密使用内置的加密功能，视图与触发器的基表

Answer 1:

在Management Studio中，你可以把一个号码GO结束批量标记后导致批处理重复指定的次数：

PRINT 'X'
GO 10

将打印“X”的10倍。 这可以从繁琐的复制/粘贴做重复的东西，当救你。

Answer 2:

很多SQL Server开发人员似乎仍然没有了解的OUTPUT子句的DELETE，INSERT和UPDATE语句（SQL Server 2005和更新版本）。

它可以知道哪些行已插入，更新或删除非常有用，并且输出子句允许这样做很容易-它允许访问称为“虚拟”的表inserted和deleted （在触发器等）：

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

如果你将值插入到它有一个INT IDENTITY主键字段，用OUTPUT子句的表，你可以插入新的ID马上：

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

如果你正在更新，它可以知道是什么改变了非常有用的-在这种情况下， inserted代表新值（更新后），同时deleted指的是更新前的旧值：

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

如果大量的信息将被退回，输出的输出也可以重定向到一个临时表或表变量（ OUTPUT INTO @myInfoTable ）。

非常有用 - 并且很少有人知道！

渣

Answer 3:

sp_msforeachtable ：运行带有命令“？” 每个表的名称取代。 例如

exec sp_msforeachtable "dbcc dbreindex('?')"

您可发行最多为每个表3级的命令

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

此外， sp_MSforeachdb

Answer 4:

连接字符串演员：

MultipleActiveResultSets = TRUE;

这使得ADO.Net 2.0及以上的读取多个，仅向前，只读产生一个单一的数据库连接，如果你正在做大量的阅读，可以提高性能上套。 你可以打开它，即使你正在做的查询类型的混合。

应用程序名称= MyProgramName

现在，当你想通过查询sysprocesses表看到活动连接的列表，你的程序的名字会出现在program_name列，而不是“的.Net SqlClient数据提供程序”

Answer 5:

TableDiff.exe

• 表差异工具允许你去发现和调和源和目标表或视图之间的差异。 Tablediff实用程序可以报告的架构和数据的差异。 tablediff最流行的功能是它可以生成你可以将调和表之间的差异目的地运行脚本的事实。

链接

Answer 6:

用于返回以随机顺序排A少已知TSQL技术：

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

Answer 7:

在Management Studio中，您可以快速得到一个逗号分隔的列清单的表是：

1. 在对象资源管理器中，展开节点给定的桌子下（所以你会看到文件夹列，键，约束，触发器等）
2. 指向列文件夹并拖动到一个查询。

这是非常方便，当你不希望使用右键单击该表并选择脚本表为...返回令人发指的格式，然后插入到...这招确实与其他文件夹的工作，它会给你以逗号分隔的此文件夹中的名称列表。

Answer 8:

行构造

你可以用一个INSERT语句插入多行数据。

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

Answer 9:

如果你想知道的表结构，索引和约束：

sp_help 'TableName'

Answer 10:

HASHBYTES（）返回MD2，MD4，MD5，SHA，或它的输入的SHA1哈希。

Answer 11:

找出最热门查询

• 随着sys.dm_exec_query_stats动态管理，你可以找出很多组合的查询通过一个单一的查询分析。

链接与commnad

select * from sys.dm_exec_query_stats 
order by execution_count desc

Answer 12:

空间结果标签可用于创建领域 。

这里http://michaeljswart.com/wp-content/uploads/2010/02/venus.png输入链接的描述

Answer 13:

EXCEPT和INTERSECT

而不是写精心联接和子查询，这两个关键字是一个更优雅的速记和比较两个查询结果时表达你的查询意图的易读的方式。 作为新的SQL Server 2005，他们互补性强，其在TSQL语言已经存在多年UNION。

除外，相交，和联盟概念在集合论其作为所有现代RDBMS使用关系建模的基础和依据的基础。 现在，维恩图型的结果可以更直观地和相当容易地使用TSQL生成。

Answer 14:

我知道它不完全隐藏，但没有太多人了解PIVOT命令。 我能够改变所使用游标和耗时2分钟来运行成迅速6秒一段代码，为十分之一的行数的存储过程！

Answer 15:

有用的恢复用于测试目的或任何一个数据库时。 重新映射的登录ID的正确：

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

Answer 16:

拖放到数据库的所有连接：

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 17:

表校验

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

行校验

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

Answer 18:

我不知道这是否是一个隐藏的功能或没有，但我偶然发现了这一点，并发现它是许多occassions有用。 您可以concatonate一组字段的一个SELECT语句，而不是使用游标，并通过select语句循环。

例：

DECLARE @nvcConcatonated nvarchar(max)
SET @nvcConcatonated = ''

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

SELECT @nvcConcatonated

结果：

Acme, Microsoft, Apple,

Answer 19:

如果你想有一个存储过程，你可以的代码：

sp_helptext 'ProcedureName'

（不知道是否是隐藏的功能，我却用它所有的时间）

Answer 20:

存储过程技巧是，你可以从一个INSERT语句调用它们。 我发现这非常有用，当我工作的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:

在SQL Server 2005/2008，显示在SELECT查询结果的行号：

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

ORDER BY是一个强制性条款。 该OVER（）子句告诉SQL引擎指定的列上的数据进行排序（在这种情况下的OrderId）和编号分配按照排序结果。

Answer 22:

有用的解析存储过程的参数： xp_sscanf

从字符串到由每一种格式参数指定的参数的位置读取数据。

下面的示例使用xp_sscanf从基于它们在源串的格式位置的源字符串中提取两个值。

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

下面是结果集。

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

Answer 23:

返回日期只有

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

要么

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

Answer 24:

dm_db_index_usage_stats

这可以让你知道，如果表中的数据，即使你没有在表中的列DateUpdated最近已更新。

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

代码： http://blog.sqlauthority.com/2009/05/09/sql-server-find-last-date-time-updated-for-any-table/

信息从引用： SQL服务器-什么是表的最后插入的行的日期/时间？

可在SQL 2005和更高

Answer 25:

这里有一些功能我觉得有用的，但似乎很多人并不知道：

sp_tables

返回可在当前环境中查询的对象的列表。 这意味着，可以在出现FROM子句，除非同义词对象的任何对象。

链接

sp_stored_procedures

返回在当前环境下存储过程的列表。

链接

Answer 26:

查找该日期落在本周内某处记录。

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

查找上周发生的日期记录。

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

返回当前周的开始日期。

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

返回上周开始的日期。

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

Answer 27:

与其说是一个隐藏的功能，但在工具\选项\键盘设置在Management Studio键映射键：Alt + F1默认为SP_HELP“选定文本”，但我不能没有加按Ctrl + F1活sp_helptext的“选定文本”

Answer 28:

持续计算的柱

• 计算列可以帮助你转移的运行成本的计算数据修改阶段。 计算列存储与该行的其余部分并且在计算列的表达和与查询匹配时被透明利用。 您也可以建立在PCC的加快上表达的过滤和范围扫描索引。

链接

Answer 29:

有些时候，有没有合适的列进行排序的，或者你只是想在一个表中的默认排序顺序，并要枚举每一行。 为了做到这一点，你可以“按订单”的条款把“（选1）”中，你会得到你想要的东西。 整洁的，是吗？

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

Answer 30:

简单的加密与EncryptByKey的

文章来源: Hidden Features of SQL Server