Sql相关

临时表是利用了硬盘(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