临时表是利用了硬盘(tempdb数据库) ，表名变量是占用内存，因此小数据量当然是内存中的表变量更快。当大数据量时，就不能用表变量了，太耗内存了。大数据量时适合用临时表。
临时表：create table #tb(id nvarchar(10))
或者 SELECT id INTO #tb FROM mytable
表变量
declare @tb table( id nvarchar(10))
select * from @tb
或者 with cet as (select * from tableName)
表值函数
Create FUNCTION f_Name
(
@ID varchar(50)='0'
)
RETURNS @tb table(id varchar(50)
标量函数
Create FUNCTION f_Name
(
@ID varchar(50)
)
RETURNS float
AS
BEGIN
END
更新触发器
CREATE TRIGGER [dbo].[TR_Update_XXX]
ON [dbo].[XXX]
for update
AS
BEGIN
SET NOCOUNT ON;
if update(ColumnName)
begin
declare @before varchar(50)
declare @aftert varchar(50)
select @before=ColumnName from deleted
select @aftert=ColumnName from inserted
if(@before<>@aftert)
删除触发器
ALTER TRIGGER [dbo].[TR_DELETE_XXX]
ON [dbo].[XXX]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @typeName nvarchar(50);
select @typeName= XXXfrom deleted
游标：
declare c cursor for select * from XXX
open c
fetch next from c into @v1,@v2
while @@fetch_status=0
begin
fetch next from c into @v1,@v2
end
close c
DEALLOCATE c