[SQL] SQL Server Web Note

SQL Server 相关语法备忘


◇ G-Productions SQL Server Procedure

※ 有的没的
※ 字符串处理
※ 日期
※ CTE 语法
※ Pivot 语法
※ 分页语法
※ 排序
※ 索引及性能
※ 全文检索
※ 识别值
※ XML
※ 数据维护
※ SQL Injection

※ 网网相连
※ SQL Azure
※ SqlLocalDB
※ SQL Server 2012
※ MSSQLTips

http://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/


※ 有的没的

insert into abc(f1, f2)  Output inserted.id values ('a', 'b');

※ 字符串处理

// 特殊符: % * [ ] '

// charindex, pantindex

select charindex('SQL', 'Microsoft SQL Server') ---> 11
select charindex('7.0', 'Microsoft SQL Server 2000') ---> 0

patindex( '%pattern%' , expression ) 
patindex('%[A,Z,0-9]%[A,Z,0-9]%[A,Z,0-9]%','XYZABC123') 
   patindex('%[b,B]read%',description) > 0    
   and patindex('_[^e]%',description) = 1 

// lastIndexOf

REVERSE(SUBSTRING(REVERSE([字段]),0,CHARINDEX('[反向符]',REVERSE([字段]))))


※ 日期

◎ 追梦青年-专注C#与.NET -- SQL Server日期时间格式转换字符串 2014-3-18


※ CTE 语法

;With CTEXX as (
    select * from XXX
) update XXX as C1 
set xx1=xx 
 from CTEXX as C1
    inner join CTEXX as C2 on C2.rn1=C1.rn1

-- Recursive query
WITH Managers AS (
  SELECT EmployeeID, LastName, ReportsTo  --initialization
  FROM Employees
  WHERE ReportsTo IS NULL
UNION ALL
  SELECT e.employeeID,e.LastName, e.ReportsTo --recursive execution
  FROM Employees e INNER JOIN Managers
  ON e.ReportsTo = m.employeeID
)
SELECT * FROM Managers 

◎ Robert Sheldon SQL Server CTE Basics 2010-4-29

[WITH [,...]]
::=
cte_name [(column_name [,...])]
AS (cte_query)


※ Pivot 语法

select id,name, [1] as '1th Season', [2] as '2th Season', ...
from test
pivot (
  sum(profile) for quarter in ([1],[2],[3],[4])
) as pvt

select id,name,quarters,profiles
from test2
unpivot (
profiles for quarters in ([Q1],[Q2],[Q3],[Q4])
) as unpvt

◎ Aaron Bertrand -- Script to create dynamic PIVOT queries in SQL Server 2012-10-16

◎ Testing Mo -- Dynamic SQL - povit table with row total and column total 2012-5-7


※ 分页语法

◎ Gea-Suan Lin's -- 避免用 SQL 的 OFFSET 实做 Pager  2014-2-17

◎ 张小呆的碎碎念 -- SQL - SQL SERVER 2012 Paging 2012-10-2


※ 排序

ROW_NUMBER() OVER (order by AAA)

RANK() OVER (PARTITION BY LocationID ORDER BY Quantity DESC) AS Rank ' 依分组给序号

DENSE_RANK() OVER (order by 价格)' 依价格分组

◎ 施威铭 -- 排序函数:ROW_NUMBER()、RANK() 与 DENSE_RANK() 2005


※ 索引及性能

CREATE UNIQUE NONCLUSTERED INDEX (indexname) ON TableName(columns to include)

CREATE NONCLUSTERED INDEX Idx_TableName_SomeID ON TableName (Column1, Column2 ASC) WITH (DROP_EXISTING = ON);

◎ sqlchen -- SQL Server 性能调优3 之索引(Index)的维护 2014-7-3 ★★★★★

◎ CaryHsu - 如何管理遗失索引与维护索引的最佳数量  2012-12-18

◎ 猫肥熊老爸的窝 -- SQL Server Index架构读后心得 2010-4-2


※ 全文检索

SQL Server 2008 所有数据库都会启用全文检索。

-- 允许全文检索
exec sp_fulltext_database 'enable'

-- 全文检索名称
exec sp_fulltext_catalog 'Q_ID_Desc', 'create'

-- 建立 Q_ID_Desc 的全文检索目录 (默认位置)
exec sp_fulltext_table 'Question_Total', 'create', 'Q_ID_Desc', 'PK_Question_Total'

-- 指定主键
exec sp_fulltext_column 'Question_Total', 'Title', 'add'
exec sp_fulltext_column 'Question_Total', 'Content', 'add'

-- 定时为全文检索添加记录
exec sp_fulltext_table 'Question_Total', 'start_full'

-- 查询
SELECT * FROM FREETEXTTABLE(Question_Total, title, '测试')
SELECT * FROM FREETEXTTABLE(Answer_Total, Answer_Content, '测试')

SELECT * FROM FREETEXTTABLE(Question_Total, title, '测试 ')

◎ 互联网 -- 结合SQL Server全文检索对Word内容进行检索的三个方案 2011-4-23

◎ 积沙成塔 -- 浅谈 SQL Server 2012 全文检索的新功能 - Property Search 2012-7-16
   搜寻文件属性, 如 Office 文档

-- 查询主键
select name from sys.objects where type='PK'
and parent_object_id=(select object_id from sys.objects where type = 'U' and name = 'MyFileTable')

select * from MyFileTable where contains(property(file_stream, 'Author'), 'xxx') -- 作者含 xxx


※ 识别值

-- 取得 GUID 值, 只能产生单机唯一识别码
select NEWID();

-- 字段默认 GUID
xxxColumn UNIQUEIDENTIFIER default NEWID()

-- 使用识别值 (流水编号)
xxxColumn int IDENTITY(1,1)

-- 取得最近一笔相同范围识别值, SP, Triggger, FN, Batch
select SCOPE_IDENTITY();

-- 任何工作阶段指定 Table 最近产生的识别值
select IDENT_CURRENT('Table_Name');

@@IDENTITY -- 最后一个插入识别值, 可能误取其他 Table 产生之识别值

-- 插入识别值数据
set identity_insert TABLE_NAME on ;
insert TABLE_NAME (编号, 姓名) values (999, '王三') ;
set identity_insert TABLE_NAME off ;

-- 检查识别值
dbcc checkident('TABLE_NAME', RESEED);

-- 自订种子
dbcc checkident('TABLE_NAME', RESEED, 999);


※ XML

...

◎ 追梦青年-专注C#与.NET -- 多行合并问题 2013-2-25 ★★★★★


※ 数据维护

SET STATISTICS TIME ON
SET STATISTICS IO ON
Table 'XXXXX'. Scan count 3, logical reads 9223, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times: CPU time = 31 ms, elapsed time = 55 ms. 

◎ CodeProject Dave Elliott -- Query Results Column Information 2014-7-3 

    -- 表格
    select name from sysobjects where type='U';
    select name from sysobjects where xtype='u' and status>=0;
    select name as [进程] from sysobjects where xtype='P';

    -- 字段
    select name from syscolumns where id=object_id('TableName')

    -- 所有数据库
    select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01;

    -- 加密
    select encrypt('原始密码');
    select pwdencrypt('原始密码');
    select pwdcompare('原始密码', '加密后密码') = 1  --相同;否则不相同 encrypt('原始密码');
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码', '加密后密码') = 1  --相同;否则不相同;

    -- 硬盘分区
    EXEC master..xp_fixeddrives

-- SQL 执行次数
select text, execution_count
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st

◎ 我是我,叶子 -- 在SQL Server 2014中设定维护计划进行DB的完整与差异备份 2014-6-16

♫♫ 保哥 -- 如何还原数据库到 SQL Server 2012 LocalDB 执行个体 2012-11-8

※ SQL Server 错误记录

◆ SQL Server -- 检视 SQL Server 错误记录 (SQL2008)
  € C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG
◎ 陈昶达 -- SQL Server发生事务记录已满之处理 2013-1-30
◎ Dorlis -- [概念] 默认的追踪(Default Trace)与 SQL Server 记录档 2011-1-28
◎ 德瑞克 -- 使用 xp_readerrorlog 或 sp_readerrorlog 来读取 SQL Server “错误记录档” 2011-1-6
  EXEC master.dbo.xp_readerrorlog
♫♫ 保哥 -- 如何清除 SQL Server 的 Database Mail 纪录与发送失败邮件 2010-12-17 ★★★★★
◎ 德瑞克 -- 设定SQL Server“错误记录档(Error log file)”的保存份数 2010-4-3


※ SQL Injection

◎ CodeProject Mayur V Lohite -- Preventing SQL Injection Attack ASP.NET - Part I 2014-9-1


※ 网网相连

◎ 我是我,叶子 -- 建立数据库快照以读取镜像数据库的DB数据 2014-7-15
   CREATE DATABASE db1_mirror ON (NAME=db1, FILENAME='C:db1_mirror.ss' ) AS SNAPSHOT OF db1;

◎ CodeProject madhusudhanReddy -- Running the SQL Script File By Using The System.Diagnostics.Process Class 2014-7-2 ★★★★

◎ CodeProject Christian Graus -- Thinking in SQL - Distribution Functions in SS 2012 2014-3-24

◎ Ivan Krivyakov -- How to Send More than 4000 Characters from CLR Stored Proc 2013-6-8

乱马客 -- [SQL]对称金钥 (SYMMETRIC KEY) 要如何变更密码 2013-1-28

◎ 乱马客 -- [.NET]为数据做加解密处理-进化篇 2012-10-13 ★★★★

◎ RiCo技术农场 -- [SQL SERVER][Memo]透过事务纪录档找出谁删除数据表 2012-10-4 ★★★★★

◎ 林大猫 -- [SQL] 使用DDL triger 2012-9-7

◎ 林大猫 -- [SQL]使用OPENXML 2012-9-6

◎ 打杂也是种乐趣 -- 数据库备份及 Log 瘦身随手记 2012-8-18

◎ jerker --  将结果累积串联的最简便方法(FOR XML PATH('')) 2012-7-20

SELECT LEFT(RESULT,LEN(RESULT)-1) AS RESULT_VALUE
FROM (
   SELECT Y+','
   FROM (VALUES('01'),('02'),('03'),('04'),('05'),('06'),('07'),('08'),('09'),('10'),('11'),('12')) AS X(Y)
   FOR XML PATH('')
) AS A(RESULT);

◎ RiCo技术农场 -- [SQL SERVER][TS] 找出 blocking 源头 SQL 2012-7-10

◎ 普乌IT伊甸园 -- 使用 ROLLUP 来摘要数据 2012-7-10

◎ wonsoft的专栏 -- SQL Server Management Studio中访问EXCEL 2007(XLSX)文档的方法 2012-7-9

◎ 五饼二鱼工作室 --  [SQL] 诡异的数据列溢位处理 2012-5-10

◎ 积沙成塔 -- 完成备份数据库后如何寄发通知至多个信箱 2012-5-9

◎ 积沙成塔 -- 使用 FOR XML PATH 产生 XML 格式时,遇到 NULL 该如何处理? 2012-5-9

◎ qanholas -- 阻塞  2012-5-4 sp_lock

◎ 五饼二鱼工作室 -- [SQL] CTE 语法 2012-4-28

insert into SalesTable
with OrdersTable( OrderID, CustomerID, EmployeeID ) as (
   select OrderID, CustomerID, EmployeeID
   from dbo.Orders
) select * from OrdersTable;

◎ 五饼二鱼工作室 -- [SQL]数据库还原的经验分享 2012-4-17

◎ Mo菲思 -- SQL中断所有连线 2012-3-26

方法一:
USE master
GO
ALTER DATABASE [Northwind]
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

--复原为一般多人连线
USE master
GO
ALTER DATABASE [Northwind]
SET MULTI_USER
WITH ROLLBACK IMMEDIATE;
GO

方法二:
进入数据库 [属性] [选项] 修改 限制存取为 SINGLE_USER, 即可清除所有连线。

◎ JERRY 技术分享区 -- Transact-SQL 数字连乘  2012-4-5
    -- 乘方和 LOG 是可以相互抵消的
    select c1, power(10.0, sum(log10(C2))) Result
    from @Test group by c1

◎ IT狂想曲~ --  取得数据库SP, View, Function  2012-4-5

select
[id] , [sp_name] , [u_name] , [xtype] , [type_desc] ,
'GRANT ' + [GCMD] + ' ON ['+ [u_name] +'].[' + sp_name + '] TO [SomeUser]' [GRANT_CMD] ,
'REVOKE ALL ON ['+ [u_name] +'].[' + sp_name + '] TO [SomeUser]' [REVOKE_CMD]
from (
   select
 o.id , o.name [sp_name] , u.name [u_name] , o.xtype ,
 case
  when o.xtype in ('P','FN', 'IF', 'TF') then 'EXECUTE'
  when o.xtype in ('V') then 'SELECT'
 end [GCMD] ,
 case
  when o.xtype = 'P' then 'Stored Procedure'
  when o.xtype in ('FN', 'IF', 'TF') then 'Function'
  when o.xtype in ('V') then 'View'
 end [type_desc]
   from sysobjects o
   join sysusers u on o.uid = u.uid   
   where o.xtype in ('P', 'FN', 'IF', 'TF' , 'V' ) and o.category = 0
) t

◎ Testing Mo -- SQL 合并数行数据为一行 Combine Row data to a column  2012-3-28

◎ 普洛格莫XDB交响曲 -- 使用 OPENDATASOURCE、OPENROWSET 数据表函数或连结服务器来连接 Oracle 数据库     2012-3-19
    -- 查询系统日期
    SELECT *
    FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd', 'SELECT SYSDATE FROM dual')

    -- 查询版本资讯
    SELECT *
    FROM OPENROWSET ('OraOLEDB.Oracle', 'test01'; 'SYSTEM'; 'Pa$$w0rd', 'SELECT * FROM v$version')

   -- 查询数据表
   SELECT * FROM OPENDATASOURCE('OraOLEDB.Oracle','Data Source=test01; User ID=SYSTEM; Password=Pa$$w0rd')
                        ..HR.DEPARTMENTS

    -- 建立连结服务器
         sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]
          [ , [ @provider= ] 'provider_name' ]
          [ , [ @datasrc= ] 'data_source' ]
          [ , [ @location= ] 'location' ]
          [ , [ @provstr= ] 'provider_string' ]
          [ , [ @catalog= ] 'catalog' ] 

-- SQL登入与远端服务器对应:
        sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
          [ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']
          [ , [ @locallogin = ] 'locallogin' ]
          [ , [ @rmtuser = ] 'rmtuser' ]
          [ , [ @rmtpassword = ] 'rmtpassword' ]

◎ Aaron Storing E-mail addresses more efficiently in SQL Server  2012-3-13

;WITH x(n,i,rn) AS (
    SELECT TOP (10000) LOWER(s1.name), ABS(s2.[object_id] % 10),
        ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_columns AS s1
    CROSS JOIN sys.all_objects AS s2
),
y(i, localpart, domain) AS (
    SELECT i, n+RTRIM(rn),
        CASE WHEN i < =2 THEN 'gmail.com'
             WHEN i <= 4 THEN 'hotmail.com'
             WHEN i = 5 THEN 'aol.com'
             WHEN i = 6 THEN 'compuserve.net'
             WHEN i = 7 THEN 'mci.com'
             WHEN i = 8 THEN 'hotmail.co.uk'
             ELSE RTRIM(rn) + '.com'
        END
    FROM x
)
SELECT domain, c = COUNT(*)
FROM y GROUP BY domain ORDER BY c DESC;

◎ 暗大 -- 群组比率 2012-2-13
     SELECT ItemId, GroupId, CostAmt, ratio_to_report(CostAmt) over (PARTITION BY GroupId) as Ratio
     FROM Cost

     SELECT ItemId, X.GroupId, CostAmt, CostAmt/Y.CostSum AS Ratio
     FROM Cost X JOIN (
          SELECT GroupId, SUM(CostAmt) CostSum FROM Cost
          GROUP BY GroupId) Y ON X.GroupId = Y.GroupId
     ) Y ON X.GroupId = Y.GroupId

◎ CaryHsu -- 灾难救援演练 - 如何修复一个 Kill Session 后,发生问题的数据库 2012-1-18 ★★★★★
    select * from sys.dm_exec_sessions where session_id > 50;
    DBCC CHECKDB('database name');

◎ 小朱 -- [SQL Server] 游标使用的艺术 2011-4-15 ★★★★★

   1. Forward-Only Cursor (adOpenForwardOnly = 0)
      速度最快
   2. Static Cursor (adOpenStaic = 3)
      数据集会复制到 tempdb 中, 并设为只读
   3. Dynamic Cursor (adOpenDyanmic = 2)
      记录游标位置, 相对位置, 也会即时侦测游标所在数据列是否被变更, 最耗系统资源
   4. Keyset Cursor (adOpenKeyset = 1)
      类似 Static Cursor, 但会额外保存一个索引键字段, 消耗资源第二高

◎ SQL Server 2008中SQL应用系列及BI学习笔记系列--目录索引    2011-03-10

◎ 普洛格莫XDB交响曲 -- 利用一只单纯的SQL命令来呈现出公司组织图的关系    2010-12-5

◎ 乱马客 -- [SQL]SQL差集的使用(EXCEPT) 2010-12-6
   Select ... Union ... Except ...

◎ 梦泣的专栏 -- OpenDataSource、OPENQUERY、OPENROWSET用法 2010-10-4

◎ W大 -- 取得 SQL Server 数据库正在执行的 T-SQL 命令与详细资讯 2010-10-13
   SELECT      r.scheduler_id as 排程器识别码,  
               status         as 要求的状态,  
               r.session_id   as SPID,  
               r.blocking_session_id as BlkBy,  
               substring(  
                   ltrim(q.text),  
                   r.statement_start_offset/2+1,  
                   (CASE 
                    WHEN r.statement_end_offset = -1  
                    THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2  
                    ELSE r.statement_end_offset  
                    END - r.statement_start_offset)/2)  
                    AS [正在执行的 T-SQL 命令],  
               r.cpu_time      as [CPU Time(ms)],  
               r.start_time    as [开始时间],  
               r.total_elapsed_time as [执行总时间],  
               r.reads              as [读取数],  
               r.writes             as [写入数],  
               r.logical_reads      as [逻辑读取数],  
               -- q.text, /* 完整的 T-SQL 命令码 */  
               d.name               as [数据库名称]  
   FROM        sys.dm_exec_requests r   
               CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q  
               LEFT JOIN sys.databases d ON (r.database_id=d.database_id)  
   WHERE       r.session_id > 50 AND r.session_id <> @@SPID  
   ORDER BY    r.total_elapsed_time desc

♫♫ 保哥 -- 如何删除 SQL Server 数据库中重复的数据 (两种不同解法) 2010-8-12 ★★★★

♫♫ 保哥 -- 观念厘清:SQL Server 完整备份、差异备份、事务记录备份  2010-4-21

◎ DianPing IT -- FOR XML PATH 语句的应用  2009-4-3
    select UserID,UserName from @TempTable FOR XML PATH
    select UserID,UserName from @TempTable FOR XML PATH('lzy')
    select UserID,UserName from @TempTable FOR XML PATH('')

◎ 瓶水相逢 -- 艾小克 将多笔相同键值的字段内容合并 2008-3-19

◎ 小saim的讨论区 转:SQL Server 性能调校 2004-12-7
   Index View 也要建索引
   CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

◎ Scott Allen -- Table Variables In T-SQL 2005-3-21 ★★★★★


※ SQL Azure

♫♫ 保哥 -- 从本地 SQL 数据库迁移到云端 SQL Database 的注意事项  2014-3-7


※ SqlLocalDB

♫♫ 保哥 -- SQL Server 2012 Express LocalDB (SqlLocalDB) 深入剖析 2012-9-3


※ SQL Server 2012

◎ MSSQLTips -- Comparing SQL Server Datatypes, Size and Performance for Storing Numbers 2014-9-4

◎ Rajendra -- SQL Server 2012 Functions - Lead and Lag 2012-4-16
    LAG|LEAD (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )

    Lead(Code, 2, 0) OVER (ORDER BY Code ) LeadValue,
    Lag(Code, 3, 0) OVER (ORDER BY Code ) LagValue

◎ Rajendra -- New columnstore index feature in SQL Server 2012  2012/4/2
   CREATE NONCLUSTERED COLUMNSTORE INDEX ON Table_Name (Column1,Column2,… Column N)


※ MSSQLTips

◎ Ben Snaidero -- Using Powershell to Monitor the SQL Server system_health Extended Event Session  2014-4-1

◎ Aaron Bertrand -- Use SQL Server's UNPIVOT operator to help normalize output 2013-7-12

◎ Douglas -- Rolling up multiple rows into a single row and column for SQL Server data 2013-4-5 ★★★★

[TODO]

◎ Seth Delconte -- Examples of using XQuery to update XML Data in SQL Server 2012-8-2 ★★★★

◎ Ashish -- New FileTable Feature in SQL Server 2012  2012-4-3

◎ Burt King -- Verify SQL Server Stored Procedures are the same on multiple servers 2012-3-20
    SELECT OBJECT_DEFINITION (OBJECT_ID('sprocname_here' ))

◎ Aaron Bertrand -- Determine SQL Server memory use by database and object 2011-5-19

◎ Tim Ford -- Identify last statement run for a specific SQL Server session 2009-7-22

◎ Jeremy -- Insert, Update or Delete Data in SQL Server from Excel 2008-7-15

◎ Tim Ford -- How to Find Keywords in SQL Server Stored Procs and Functions 2008-1-25

◎ Greg -- How to get index usage information in SQL Server 2007-5-8


~ THE END ~