SQL Server的隐藏功能

30 回答

• 23

  The spatial results tab can be used to create art .

  enter link description here http://michaeljswart.com/wp-content/uploads/2010/02/venus.png

  回复于 2024-04-25T10:04:55+08:00
• 7

  TableDiff.exe

  • 表差异工具允许您发现和协调源表和目标表或视图之间的差异 . Tablediff实用程序可以报告架构和数据的差异 . tablediff最受欢迎的功能是它可以生成一个脚本，您可以在目标上运行该脚本来协调表之间的差异 .

  Link

  回复于 2024-04-25T10:04:55+08:00
• 22

  有时候没有合适的列可以排序，或者您只想在表上使用默认排序顺序，并且想要枚举每一行 . 为了做到这一点，你可以在“order by”子句中加上“（select 1）”，你就可以得到你想要的东西 . 整洁，嗯？

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

  回复于 2024-04-25T10:04:55+08:00
• 6

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

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

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

  回复于 2024-04-25T10:04:55+08:00
• 10

  如果您想要存储过程的代码，您可以：

  sp_helptext 'ProcedureName'
  

  （不确定它是否是隐藏功能，但我一直使用它）

  回复于 2024-04-25T10:04:55+08:00
• 52

  以下是我觉得有用的一些功能，但很多人似乎并不了解：

  sp_tables
  

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

  Link

  sp_stored_procedures
  

  返回当前环境中的存储过程列表 .

  Link

  回复于 2024-04-25T10:04:55+08:00
• 31

  EXCEPT and INTERSECT

  在比较两个查询结果时，这两个关键字不是编写复杂的连接和子查询，而是更加优雅的简写和可读方式来表达查询的意图 . 从SQL Server 2005开始，它们强有力地补充了多年来已经存在于TSQL语言中的UNION .

  EXCEPT，INTERSECT和UNION的概念是集合论的基础，它是所有现代RDBMS使用的关系建模的基础和基础 . 现在，使用TSQL可以更直观，更容易地生成维恩图类型结果 .

  回复于 2024-04-25T10:04:55+08:00
• 51

  查找日期在当前周内某处的记录 .

  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 )
  

  回复于 2024-04-25T10:04:55+08:00
• 9

  在为测试目的或其他任何方面恢复数据库时非常有用 . 重新映射登录ID正确：

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

  回复于 2024-04-25T10:04:55+08:00
• 7

  我知道它并没有完全隐藏，但没有太多人知道PIVOT命令 . 我能够更改使用游标的存储过程，并花了2分钟时间遇到快速的6秒代码，这个代码只有行数的十分之一！

  回复于 2024-04-25T10:04:55+08:00
• 16

  Row Constructors

  您可以使用单个insert语句插入多行数据 .

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

  回复于 2024-04-25T10:04:55+08:00
• 15

  许多SQL Server开发人员似乎仍然不了解DELETE，INSERT和UPDATE语句中的 OUTPUT clause （SQL Server 2005及更新版本） .

  知道哪些行已被INSERTed，UPDATEd或DELETEd非常有用，并且OUTPUT子句允许非常容易地执行此操作 - 它允许访问名为 inserted 和 deleted 的"virtual"表（如触发器中）：

  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 表示新值（在UPDATE之后），而 deleted 指的是UPDATE之前的旧值：

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

  如果返回大量信息，OUTPUT的输出也可以重定向到临时表或表变量（ OUTPUT INTO @myInfoTable ） .

  非常有用 - 而且知之甚少！

  渣

  回复于 2024-04-25T10:04:55+08:00
• 15

  sp_msforeachtable ：运行一个命令，其中'?'替换为每个表名 . 例如

  exec sp_msforeachtable "dbcc dbreindex('?')"
  

  您可以为每个表发出最多3个命令

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

  另外， sp_MSforeachdb

  回复于 2024-04-25T10:04:55+08:00
• 33

  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 Server - What is the date/time of the last inserted row of a table?

  在SQL 2005及更高版本中可用

  回复于 2024-04-25T10:04:55+08:00
• 14

  用于解析存储过程参数：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
  

  回复于 2024-04-25T10:04:55+08:00
• 16

  在Tools \ Options \ Keyboard下的Management Studio中设置密钥映射并不是一个隐藏的功能：Alt F1默认为sp_help“选定文本”但是如果没有为sp_helptext添加Ctrl F1“选择的文本”我就无法生存

  回复于 2024-04-25T10:04:55+08:00
• 70

  Figuring out the most popular queries

  • 使用sys.dm_exec_query_stats，您可以通过单个查询找出许多查询分析的组合 .

  Link与commnad

  select * from sys.dm_exec_query_stats 
  order by execution_count desc
  

  回复于 2024-04-25T10:04:55+08:00
• 7

  删除与数据库的所有连接：

  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
  

  回复于 2024-04-25T10:04:55+08:00
• 13

  我不确定这是否是一个隐藏的功能，但我偶然发现了这一点，并发现它在很多场合都很有用 . 您可以在单个select语句中对一组字段进行concatonate，而不是使用游标并循环遍历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,
  

  回复于 2024-04-25T10:04:55+08:00
• 22

  连接字符串附加功能：

  MultipleActiveResultSets=true;

  这使得ADO.Net 2.0及更高版本在单个数据库连接上读取多个只进，只读的结果集，如果您正在进行大量读取，则可以提高性能 . 即使您正在混合使用各种查询类型，也可以将其打开 .

  Application Name=MyProgramName

  现在，当您想通过查询sysprocesses表查看活动连接列表时，程序的名称将显示在program_name列而不是“.Net SqlClient Data Provider”中

  回复于 2024-04-25T10:04:55+08:00
• 91

  存储过程技巧是您可以从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
  

  回复于 2024-04-25T10:04:55+08:00
• 7

  在Management Studio中，您可以在GO批次结束标记之后放置一个数字，以使批次重复多次：

  PRINT 'X'
  GO 10
  

  将'X'打印10次 . 这样做可以避免在做重复的事情时从繁琐的复制/粘贴中解脱出来 .

  回复于 2024-04-25T10:04:55+08:00
• 20

  如果您想了解表结构，索引和约束：

  sp_help 'TableName'
  

  回复于 2024-04-25T10:04:55+08:00
• 16

  表格校验和

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

  行校验和

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

  回复于 2024-04-25T10:04:55+08:00
• 30

  在Management Studio中，您可以通过以下方式快速获取表的列的逗号分隔列表：

  • 在对象资源管理器中，展开给定表下的节点（这样您将看到列，键，约束，触发器等的文件夹）

  • 指向Columns文件夹并拖入查询 .

  当您不想使用通过右键单击表并选择Script Table As ...返回时发出的令人发指的格式时，这很方便，然后插入到...这个技巧可以与其他文件夹一起使用，因为它会给你以逗号分隔的文件夹中包含的名称列表 .

  回复于 2024-04-25T10:04:55+08:00
• 9

  HashBytes()返回其输入的MD2，MD4，MD5，SHA或SHA1哈希值 .

  回复于 2024-04-25T10:04:55+08:00
• 7

  使用EncryptByKey进行简单加密

  回复于 2024-04-25T10:04:55+08:00
• 12

  一种鲜为人知的TSQL技术，用于以随机顺序返回行：

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

  回复于 2024-04-25T10:04:55+08:00
• 17

  仅限退货日期

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

  要么

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

  回复于 2024-04-25T10:04:55+08:00
• 15

  Persisted-computed-columns

  • 计算列可以帮助您将运行时计算成本转移到数据修改阶段 . 该计算列与行的其余部分一起存储，并在计算列和查询的表达式匹配时透明使用 . 您还可以在PCC上构建索引，以加快表达式上的过滤和范围扫描 .

  Link

  回复于 2024-04-25T10:04:55+08:00