SQL Server的隐藏特性

SQL Server有哪些隐藏特性?

例如，没有文档的系统存储过程，做一些非常有用但没有足够文档的事情的技巧?

答案

感谢大家的精彩回答!

存储过程

sp_msforeachtable: Runs a command with '?' replaced with each table name (v6.5 and up) sp_msforeachdb: Runs a command with '?' replaced with each database name (v7 and up) sp_who2: just like sp_who, but with a lot more info for troubleshooting blocks (v7 and up) sp_helptext: If you want the code of a stored procedure, view & UDF sp_tables: return a list of all tables and views of database in scope. sp_stored_procedures: return a list of all stored procedures xp_sscanf: Reads data from the string into the argument locations specified by each format argument. xp_fixeddrives:: Find the fixed drive with largest free space sp_help: If you want to know the table structure, indexes and constraints of a table. Also views and UDFs. Shortcut is Alt+F1

片段

Returning rows in random order All database User Objects by Last Modified Date Return Date Only Find records which date falls somewhere inside the current week. Find records which date occurred last week. Returns the date for the beginning of the current week. Returns the date for the beginning of last week. See the text of a procedure that has been deployed to a server Drop all connections to the database Table Checksum Row Checksum Drop all the procedures in a database Re-map the login Ids correctly after restore Call Stored Procedures from an INSERT statement Find Procedures By Keyword Drop all the procedures in a database Query the transaction log for a database programmatically.

功能

哈希字节（） EncryptByKey 枢轴命令

Misc

Connection String extras TableDiff.exe Triggers for Logon Events (New in Service Pack 2) Boosting performance with persisted-computed-columns (pcc). DEFAULT_SCHEMA setting in sys.database_principles Forced Parameterization Vardecimal Storage Format Figuring out the most popular queries in seconds Scalable Shared Databases Table/Stored Procedure Filter feature in SQL Management Studio Trace flags Number after a GO repeats the batch Security using schemas Encryption using built in encryption functions, views and base tables with triggers

当前回答

有时没有合适的列来排序，或者您只是想要表上的默认排序顺序，并且希望枚举每一行。为了做到这一点，你可以把“(select 1)”放在“order by”子句中，你就会得到你想要的东西。整洁的,是吗?

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

2010-09-05 15:37:31

其他回答

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

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

ORDER BY是一个强制性条款。OVER()子句告诉SQL引擎对指定列(在本例中为OrderId)上的数据进行排序，并根据排序结果分配数字。

2009-02-10 15:22:30

跟踪旗帜!“1204”在SQL Server 2000上的死锁调试中是无价的(2005有更好的工具)。

2008-09-24 13:11:34

SQL Server 2005:

select * from sys.dm_os_performance_counters

select * from sys.dm_exec_requests

2008-09-23 16:05:02

下面是一些我喜欢的SQL Management Studio隐藏特性。

我喜欢的一点是，如果你在高亮显示信息时按住ALT键，你可以选择柱状信息，而不仅仅是整行。

在SQL Management Studio中，你有预定义的快捷键:

Ctrl+1运行sp_who Ctrl+2运行sp_lock Alt+F1运行sp_help Ctrl+F1运行sp_helptext

因此，如果您在编辑器中突出显示表名并按Alt+F1，它将显示表的结构。

2010-01-08 00:56:15

好吧，这是我剩下的一些，很遗憾我错过了开始，但继续吧，这里有一些顶级的东西!

查询分析器

Alt+F1对所选文本执行sp_help Alt-D -聚焦到数据库下拉菜单，这样你就可以使用字母光标键选择db。

t - sql

if (object_id("nameofobject") IS NOT NULL) begin <do something> end -最简单的存在性检查 Sp_locks——比sp_who2(这是第一个调用端口)更深入的锁定信息 DBCC inputbuffer(spid) -执行进程的顶部行列表(有点有用，但简单) DBCC outputbuffer(spid) -执行进程输出的顶行列表

通用T-sql技巧

对于大容量的数据，可以自由地使用子查询来处理集合中的数据

例如，获得已婚人士的名单 50岁以上的人可以选择一群结婚的人子查询并与一组同样是50岁以上的人加入结果-请原谅的例子

2009-06-30 20:28:57

SQL Server的隐藏特性

推荐文章

最新文章

标签