1 实现对字符串的Spilt功能。 比如查出“I have a dream!”总共有几个单词，需要以' '分割，然后再求出总数。
ALTER function [dbo].[fc_SpiltString]
(
@str varchar(1024), --要分割的字符串
@split varchar(10) --分隔符号
)
returns int
as
begin
declare @location int
declare @start int
declare @length int set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
GO
2 表连接进行增删改
--INSERT
INSERT INTO [TABLE1]([ID],[NAME],[ClassName])
SELECT S.ID,S.NAME,C.[ClassName] FROM
[STUDENT] AS S INNER JOIN [CLASS] AS C
ON S.CID=C.ID --UPDATE
UPDATE S SET S.NAME='XXX' FROM [STUDENT] AS S
INNER JOIN [CLASS] AS C
ON S.CID=C.ID WHERE .. --DELETE
DELETE S FROM [STUDENT] AS S
INNER JOIN [CLASS] AS C
ON S.CID=C.ID WHERE ..
3 对临时表的基本操作（insert into #temp和select * into #temp ）具体实现语句不写，只写怎么判断。
IF (object_id('tempdb..#Temp') is null )
BEGIN
CREATE TABLE #Temp(
Name nvarchar(50) not null,
)
END
ELSE
BEGIN
TRUNCATE TABLE #Temp
END
4 开启sql执行统计功能
set statistics io on
set statistics time on
set statistics profile on
5 except 代替not in
select distinct [name] from [T] except select [name] from [T2]
select [name] [name]from [T] where TgtCulture not in (select [name] from [T2])
性能对比
T2:Scan count 1, logical reads 3009
T:Scan count 1, logical reads 2968, SQL Server Execution Times:
CPU time = 124 ms, elapsed time = 127 ms. T2:Scan count 7, logical reads 152215
T:Scan count 3, logical reads 3256
SQL Server Execution Times:
CPU time = 311 ms, elapsed time = 183 ms.
关于排序聚合函数：
row_number() 无重复不可并列的排序；
Rank() 相等的数据并列排序；
over (partition by xx) 按照XX 分组 效果等同于 Group by ;
下面的句子是 按照工资从大到小并且可以并列排名 给每个班级进行排名
select saray, name,groupid,rank() over(partition by groupid order by saray desc) rankId from [UserSarary]
表变量（把表当作参数执行方法或者存储过程）
--建立自定义表类型
create type MyTableV as table (ID int null)
--建立存储过程
create PROCEDURE [dbo].[temptest](
@Source as MyTableV readonly,
@table as MyTableV readonly
)
as
begin
select * from @source;
select * from @table
end
--声明表变量 @source 第一个参数
DECLARE @source TABLE(ID INT)
declare @source MyTableV
insert into @source values(3)
--声明表变量 @table 第二个参数
DECLARE @table TABLE(ID INT)
declare @table MyTableV
insert into @table values(31)
--执行存储过程
exec [temptest] @source,@table