SQL 小技巧

[]的使用

　　当我们所要查的表是系统关键字或者表名中含有空格时，需要用[]括起来，例如新建了两个表，分别为user,user info，那么select * from user和select * from user info就要报错，需要写成：select * from [user] 和 select * from [user info]，不过千万不要因为有[]的帮助，就随意起名了，那是自找麻烦，不过我确实看到有人把用户表起名为user的。

 

NULLIF函数

　　NULLIF(Expression1,Expression2):给定两个参数Expression1和Expression2，如果两个参数相等，则返回NULL；否则就返回第一个参数。

　　等价于：Case WHEN Expression1=Expression2 Then NULL ELSE Expression1。

　　例如Select NULLIF(1,1)返回NULL，Select NULLIF(1,2)返回1。

　　有一个实际的应用，例如防止除0操作的放生，可以使用a/NULLIF(b,0)，这样就不怕b是0了，当然除0操作也可以通过别的方式判断。

 

NULL

　　NULL是个神奇的东西，表示空值，未知值，任何数与它加减乘除都返回NULL。

 

ISNULL函数

　　ISNULL(Expression1,Expression2):给定两个参数Expression1和Expression2，如果Expression1是NULL，那么返回Expression2，否则返回Expression1。

　　等价于：Case WHEN Expression1 is NULL Then Expression2 ELSE Expression1。

　　例如Select ISNULL(NULL,1)返回1，Select ISNULL(1,2)返回1。

　　有一个实际的应用，可以对空值进行默认值替代，例如SELECT ISNULL(email,'没有填写email') from table1,所有email为null的，用'没有填写email'来替代。

 

COALESCE函数

　　COALESCE(Expression1,Expression2,Expression3,......)：接受一系列的表达式或列，返回第一个非空的值。

      例如SELECT COALESCE(NULL,NULL,4,NULL,NULL,5)，那么返回4，如果里面的参数都为NULL，那么会报错。

 

WITH TIES

　　与top()和order by 一起用，可以返回多于top的行。防止丢失想要的信息。

　　例如：有个表table1

select * from table1 order by name desc

 

select top(3) * from table1 order by name desc

 

select top(3) with ties * from table1 order by name desc

 

 

TOP 增强

可以指定一个数字表达式，以返回要通过查询影响的行数或百分比，还可以根据情况使用变量或子查询。

可以在DELETE、UPDATE和INSERT查询中使用TOP选项。

 

 

ORDER BY NEWID()

返回随机排序结果。

 

BETWEEN a AND b

返回大于等于a，小于等于b的结果。如果a>b，那么返回NULL。

 

不要在where条件中使用函数，会强制每一行都计算该函数，无法使用索引查找。

　　例如:select * from table1 where id+3>5和select * from table1 where id>5-3，后者效率比前者高。

 

可以将列名以_desc结尾为更友好的表示一个列的含义。 

 

推荐一个小插件，SQL Prompt，配合Microsoft SQL Server Management Studio，使用起来非常方便，同时再加上以下几个快捷键：

　　(1)ctrl+5或F5，运行代码，如果想运行特定的语句，那么只是选中该语句，然后F5或ctrl+E即可。

　　(2)ctrl+L：显示执行计划。

　　(3)ctrl+R：显示隐藏下面的结果窗口，增大自己书写sql的空间。

　　(4)ctrl+K,然后按Y，格式化SQL代码。

 

cross apply 和 cross join

cross join 是用于交叉联接的。实际上增加 cross apply 和 outer apply 是用于交叉联接表值函数（返回表结果集的函数）的，更重要的是这个函数的参数是另一个表中的字段。

cross apply select * from TABLE_1 T1 cross apply FN_TableValue(T1.column_a)

cross join 联接两个表(或表和表值函数，表值函数的参数是个“常量”)

select * from TABLE_1 as T1 cross join TABLE_2 as T2

 

丰富的数据类型 Richer Data Types

varchar(max)、nvarchar(max)和varbinary(max)数据类型最多可以保存2GB的数据，可以取代text、ntext或image数据类型。

 

XML数据类型

XML数据类型允许用户在SQL Server数据库中保存XML片段或文档。

 

错误处理 Error Handling

  BEGIN TRY

    BEGIN TRAN

...

    COMMIT TRAN

  END TRY

  BEGIN CATCH

    ROLLBACK

...

   END CATCH

 

快照隔离 Snapshot Isolation

1、写入程序不会阻碍读取程序

2、Snapshot isolation must be enabled for DB

ALTER DATABASE 数据库 SET allow_snapshot_isolation ON

3、Snapshot isolation must be enabled for connection

Set transaction isolation level snapshot

4、UPDATE transactions keep old versions of data in a linked list

5、新的隔离级别提供了以下优点：

1) 提高了只读应用程序的数据可用性

2) 允许在OLTP环境中执行非阻止读取操作

3) 可对写入事务进行自动的强制冲突检测

 

OUTPUT

可以使您可以从修改语句(INSERT、UPDATE、DELETE)中将数据返回到表变量中。

OUTPUT子局的语法为：

OUTPUT <dml_select_list> INTO @table_variable

可以通过引用插入的表或删除的表来访问被修改的行的旧/新影象，其方式与访问触发器类似。在INSERT语句中，只能访问插入的表。在DELETE语句中，只能访问删除的表。在UPDATE语句中，可以访问插入的表和删除的表。

 

排序函数 Ranking Functions

1、SQL Server引入几个新的排序函数：如ROW_NUMBER、RANK、DENSE_RANK等。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。

2、排序函数都遵循类似的语法模式：

()OVER

([PARTITION BY]

ORDER BY)

该函数只能在查询的两个子句中指定 - 在SELECT子句或ORDER BY子句中。以下详细讨论不同的函数。 

3、ROW_NUMBER

ROW_NUMBER是结果集的顺序, 而不是数据库中纪录存放的原始顺序

 

通用表表达式 Common Table Expressions 

通用表表达式（CTE）是一个可以由定义语句引用的临时表命名的结果集。在他们的简单形式中，您可以将CTE视为类似于视图和派生表混合功能的改进版本。在查询的FROM子句中引用CTE的方式类似于引用派生表和视图的方式。只须定义CTE一次，即可在查询中多次引用它。在CTE的定义中，可以引用在同一批处理中定义的变量。但是CTE的真正威力在于它们的递归功能，即CTE可以包含对它们自身的引用。

视图、派生表和CTE内部的查询的一般形式

1、视图

CREATE VIEW <view_name>(<column_aliases>) AS <view_query>

 

2、派生表

SELECT * FROM (<derived_table)query>) AS <dericed_table_alias>(<column_aliases>)

 

3、CTE

WITH <cte_alias>(<column_aliases>)

AS

{

<cte_query>

)

SELECT * FROM <cte_alias]>

在关键字WITH之后，为CTE提供一个别名，并且为它的结果列提供一个可选的别名列表；编写CTE的主体；然后从外部查询中引用它。

 

Recursive CTEs 递归的通用表表达式

递归的CTE是根据至少两个查询（或者称为两个成员）构建的，一个是非递归查询，也成为固定成员，只能调用一次，另外一个是递归查询，也成为递归成员（RM），可以反复调用，直到查询不再返回行。查询由UNION ALL运算符连接为一个单独的CTE。

 

新的关系运算符 PIVOT/UNPIVOT/APPLY

1、PIVOT

PIVOT运算符将行旋转为列，并且可能同时执行聚合。使用PIVOT运算符时要注意的重要一点是，需要为它提供一个查询表达式，表达式使用视图、派生表或者是CTE只返回所关注的列。

 

2、UNPIVOT

UNPIVOT运算符执行与PIVOT运算符相反的操作；他将列旋转为行了。

 

3、APPLY

APPLY关系运算符允许您对外部表的每个行调用指定的表值函数一次。您可以在查询的FROM子句中指定APPLY，其方式与使用JOIN关系运算符类似。APPLY具有两种形式：CROSS APPLY和OUTER APPLY。

 

SqlServer连接多服务器的方式有3种：

openrowset最好，使用简单而且支持在连接时制定查询语句使用很灵活

openquery也不错查询时也可以指定查询语句使用也很灵活，不过查询前要先用exec sp_addlinkedserver和exec sp_addlinkedsrvlogin建立服务器和服务器连接稍显麻烦

opendatasource稍显欠佳，他无法在连接时指定查询使用起来稍显笨拙

在使用openrowset/opendatasource前搜先要启用Ad Hoc Distributed Queries服务，因为这个服务不安全所以SqlServer默认是关闭的。系统管理员可以通过使用 sp_configure  启用 'Ad Hoc Distributed Queries'。