自然数表1-1MCREATE TABLE Nums(n int NOT NULL PRIMARY KEY CLUSTERED)
书上介绍了很多种填充方法，以下是最高效的一种，需要SS2005的ROW_NUMBER()函数。WITH B1 AS(SELECT n=1 UNION ALL SELECT n=1), 2B2 AS(SELECT n=1 FROM B1 a CROSS JOIN B1 b), 4B3 AS(SELECT n=1 FROM B2 a CROSS JOIN B2 b), 16B4 AS(SELECT n=1 FROM B3 a CROSS JOIN B3 b), 256B5 AS(SELECT n=1 FROM B4 a CROSS JOIN B4 b), 65536CTE AS(SELECT r=ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM B5 a CROSS JOIN B3 b) 65536 * 16INSERT INTO Nums(n)
SELECT TOP(1000000) r FROM CTE ORDER BY r

CREATE TABLE Calendar(
    date datetime NOT NULL PRIMARY KEY CLUSTERED,
    weeknum int NOT NULL,
    weekday int NOT NULL,
    weekday_desc nchar(3) NOT NULL,
    is_workday bit NOT NULL,
    is_weekend bit NOT NULL
)
GOWITH CTE1 AS(
    SELECT
        date = DATEADD(day,n,''19991231'')
    FROM Nums
    WHERE n <= DATEDIFF(day,''19991231'',''20201231'')),
CTE2 AS(
    SELECT
        date,
        weeknum = DATEPART(week,date),
        weekday = (DATEPART(weekday,date) + @@DATEFIRST - 1) % 7,
        weekday_desc = DATENAME(weekday,date)
    FROM CTE1)
INSERT INTO CalendarSELECT
    date,
    weeknum,
    weekday,
    weekday_desc,
    is_workday = CASE WHEN weekday IN (0,6) THEN 0 ELSE 1 END,
    is_weekend = CASE WHEN weekday IN (0,6) THEN 1 ELSE 0 ENDFROM CTE2

将一组查询结果按指定分隔符拼接到一个变量中DECLARE @Datebases varchar(max)
SET @Datebases = STUFF((
        SELECT '',''+name
        FROM sys.databases
        ORDER BY name
        FOR XML PATH('''')),1,1,'''')
SELECT @Datebases将传入的一个参数按指定分隔符切分到一个表中DECLARE @SourceIDs varchar(max)
SET @SourceIDs = ''a,bcd,123,+-*/=,x&y,<key>''SELECT v = x.n.value(''.'',''varchar(10)'')
FROM (
    SELECT ValuesXML = CAST(''<root>'' +
        REPLACE((SELECT v = @SourceIDs FOR XML PATH('''')),'','',''</v><v>'') +
        ''</root>'' AS XML)
) t
CROSS APPLY t.ValuesXML.nodes(''/root/v'') x(n)

测试数据：CREATE TABLE #ToJoin(
    TableName varchar(20) NOT NULL,
    ColumnName varchar(20) NOT NULL,
    PRIMARY KEY CLUSTERED(TableName,ColumnName))
GOCREATE TABLE #ToSplit(
    TableName varchar(20) NOT NULL PRIMARY KEY CLUSTERED,
    ColumnNames varchar(max) NOT NULL)
GOINSERT INTO #ToJoin VALUES(''tblEmployee'',''EmployeeCode'')
INSERT INTO #ToJoin VALUES(''tblEmployee'',''EmployeeName'')
INSERT INTO #ToJoin VALUES(''tblEmployee'',''HireDate'')
INSERT INTO #ToJoin VALUES(''tblEmployee'',''JobCode'')
INSERT INTO #ToJoin VALUES(''tblEmployee'',''ReportToCode'')
INSERT INTO #ToJoin VALUES(''tblJob'',''JobCode'')
INSERT INTO #ToJoin VALUES(''tblJob'',''JobTitle'')
INSERT INTO #ToJoin VALUES(''tblJob'',''JobLevel'')
INSERT INTO #ToJoin VALUES(''tblJob'',''DepartmentCode'')
INSERT INTO #ToJoin VALUES(''tblDepartment'',''DepartmentCode'')
INSERT INTO #ToJoin VALUES(''tblDepartment'',''DepartmentName'')
GOINSERT INTO #ToSplit VALUES(''tblDepartment'',''DepartmentCode,DepartmentName'')
INSERT INTO #ToSplit VALUES(''tblEmployee'',''EmployeeCode,EmployeeName,HireDate,JobCode,ReportToCode'')
INSERT INTO #ToSplit VALUES(''tblJob'',''DepartmentCode,JobCode,JobLevel,JobTitle'')
GO拼接(Join)，SQL Server 2005的FOR XML扩展可以将一个列表转成一个字串：SELECT
    t.TableName,
    ColumnNames = STUFF(
        (SELECT '','' + c.ColumnName
        FROM #ToJoin c
        WHERE c.TableName = t.TableName
        FOR XML PATH('''')),
        1,1,'''')
FROM #ToJoin t
GROUP BY t.TableName

切分(Split)，使用SQL Server 2005对XQuery的支持：SELECT
    t.TableName,
    ColumnName = c.ColumnName.value(''.'',''varchar(20)'')
FROM (
    SELECT
        TableName,
        ColumnNamesXML = CAST(''<Root>'' + REPLACE((SELECT ColumnName = ColumnNames FOR XML PATH('''')),'','',''</ColumnName><ColumnName>'') + ''</Root>'' AS xml)
    FROM #ToSplit
) t
CROSS APPLY t.ColumnNamesXML.nodes(''/Root/ColumnName'') c(ColumnName)

测试数据CREATE TABLE #Employees(
    EmployeeCode varchar(20) NOT NULL PRIMARY KEY CLUSTERED,
    ReportToCode varchar(20) NULL)
GOINSERT INTO #Employees VALUES(''A'',NULL)
INSERT INTO #Employees VALUES(''B'',''A'')
INSERT INTO #Employees VALUES(''C'',''A'')
INSERT INTO #Employees VALUES(''D'',''A'')
INSERT INTO #Employees VALUES(''E'',''B'')
INSERT INTO #Employees VALUES(''F'',''B'')
INSERT INTO #Employees VALUES(''G'',''C'')
INSERT INTO #Employees VALUES(''H'',''D'')
INSERT INTO #Employees VALUES(''I'',''D'')
INSERT INTO #Employees VALUES(''J'',''D'')
INSERT INTO #Employees VALUES(''K'',''J'')
INSERT INTO #Employees VALUES(''L'',''J'')
INSERT INTO #Employees VALUES(''M'',''J'')
INSERT INTO #Employees VALUES(''N'',''K'')
GO/*
可能遇到的查询问题：
1. 员工''D''的所有直接下属
2. 员工''D''的所有2级以内的下属（包括直接下属和直接下属的下属）
3. 员工''N''的所有上级（按报告线顺序列出）
4. 员工@EmployeeCode的所有@LevelDown级以内的下属（@EmployeeCode和@LevelDown以变量传入）
DECLARE @EmployeeCode varchar(20), @LevelDown int;
SET @EmployeeCode = ''D'';
SET @LevelDown = 2;
5. 员工@EmployeeCode的所有@LevelUp级以内的上级（@EmployeeCode和@LevelUp以变量传入）
DECLARE @EmployeeCode varchar(20), @LevelUp int;
SET @EmployeeCode = ''N'';
SET @LevelUp = 2;
*/用递归CTE实现员工树形关系表WITH CTE AS(
    SELECT
        EmployeeCode,
        ReportToCode,
        ReportToDepth = 0,
        ReportToPath = CAST(''/'' + EmployeeCode + ''/'' AS varchar(200))
    FROM #Employees
    WHERE ReportToCode IS NULL
    UNION ALL
    SELECT
        e.EmployeeCode,
        e.ReportToCode,
        ReportToDepth = mgr.ReportToDepth + 1,
        ReportToPath = CAST(mgr.ReportToPath + e.EmployeeCode + ''/'' AS varchar(200))
    FROM #Employees e
    INNER JOIN CTE mgr
    ON e.ReportToCode = mgr.EmployeeCode
)
SELECT * FROM CTE ORDER BY ReportToPath

测试数据CREATE TABLE #IPs(
    strIP varchar(15) NULL,
    binIP binary(4) NULL)
GOINSERT INTO #IPs VALUES(''0.0.0.0'',NULL)
INSERT INTO #IPs VALUES(''255.255.255.255'',NULL)
INSERT INTO #IPs VALUES(''127.0.0.1'',NULL)
INSERT INTO #IPs VALUES(''192.168.43.192'',NULL)
INSERT INTO #IPs VALUES(''192.168.1.101'',NULL)
INSERT INTO #IPs VALUES(''65.54.239.80'',NULL)
INSERT INTO #IPs VALUES(NULL,0xB92AEAD3)
INSERT INTO #IPs VALUES(NULL,0x2D4B2E53)
INSERT INTO #IPs VALUES(NULL,0x31031B0B)
INSERT INTO #IPs VALUES(NULL,0x7C2D5F2F)
INSERT INTO #IPs VALUES(NULL,0x473E5D31)
INSERT INTO #IPs VALUES(NULL,0x90D7D66B)
GOSELECT
    strIP,binIP,
    strIP_new = CAST(CAST(SUBSTRING(binIP,1,1) AS int) AS varchar(3)) + ''.'' +
                CAST(CAST(SUBSTRING(binIP,2,1) AS int) AS varchar(3)) + ''.'' +
                CAST(CAST(SUBSTRING(binIP,3,1) AS int) AS varchar(3)) + ''.'' +
                CAST(CAST(SUBSTRING(binIP,4,1) AS int) AS varchar(3)),
    binIP_new = CAST(CAST(PARSENAME(strIP,4) AS int) AS binary(1)) +
                CAST(CAST(PARSENAME(strIP,3) AS int) AS binary(1)) +
                CAST(CAST(PARSENAME(strIP,2) AS int) AS binary(1)) +
                CAST(CAST(PARSENAME(strIP,1) AS int) AS binary(1)),
    intIP_new = CAST(PARSENAME(strIP,1) AS bigint) +
                CAST(PARSENAME(strIP,2) AS bigint) * 256 +
                CAST(PARSENAME(strIP,3) AS bigint) * 65536 +
                CAST(PARSENAME(strIP,4) AS bigint) * 16777216  int类型也可以，但浪费空间且不直观FROM #IPs

ASCII字符SELECT n,x=CAST(n AS binary(2)),u=NCHAR(n) FROM Nums WHERE n BETWEEN 32 AND 126UNICODE中文字符SELECT n,x=CAST(n AS binary(2)),u=NCHAR(n) FROM Nums WHERE n BETWEEN 19968 AND 4086919968    0x4E00    一
40869    0x9FA5    龥
以下两个条件用来判断字符串是否包含汉字LIKE N''%[吖-咗]%'' COLLATE Chinese_PRC_CI_AS
LIKE N''%[一-龥]%'' COLLATE Chinese_PRC_BIN
这是因为在以上两种不同的排序规则下，汉字的排列顺序是不同的。
中文全角标点符号SELECT n,x=CAST(n AS binary(2)),uq=NCHAR(n),ub=NCHAR(n-65248) FROM Nums WHERE n BETWEEN 65281 AND 65374SELECT NCHAR(12288),NCHAR(32)
65281    0xFF01    ！    !
65374    0xFF5E    ～    ~以下条件用来判断字符串是否包含全角标点LIKE N''%[！-～]%'' COLLATE Chinese_PRC_BIN

full2halfCREATE FUNCTION [dbo].[full2half](
@String nvarchar(max)
)
RETURNS nvarchar(max)
AS/*
全角(Fullwidth)转换为半角(Halfwidth)
*/BEGIN
    DECLARE @chr nchar(1)
    DECLARE @i int
    SET @String = REPLACE(@String,N''　'',N'' '')
    SET @i = PATINDEX(N''%[！-～]%'' COLLATE Latin1_General_BIN,@String)
    WHILE @i > 0
    BEGIN
        SET @chr = SUBSTRING(@String,@i,1)
        SET @String = REPLACE(@String,@chr,NCHAR(UNICODE(@chr)-65248))
        SET @i = PATINDEX(N''%[！-～]%'' COLLATE Latin1_General_BIN,@String)
    END
    RETURN @StringENDGOCREATE FUNCTION [dbo].[half2full](
@String nvarchar(max)
)
RETURNS nvarchar(max)
AS/*
半角(Halfwidth)转换为全角(Fullwidth)
*/BEGIN
    DECLARE @chr nchar(1)
    DECLARE @i int
    SET @String = REPLACE(@String,N'' '',N''　'')
    SET @i = PATINDEX(N''%[!-~]%'' COLLATE Latin1_General_BIN,@String)
    WHILE @i > 0
    BEGIN
        SET @chr = SUBSTRING(@String,@i,1)
        SET @String = REPLACE(@String,@chr,NCHAR(UNICODE(@chr)+65248))
        SET @i = PATINDEX(N''%[!-~]%'' COLLATE Latin1_General_BIN,@String)
    END
    RETURN @StringENDGO