2010-05-05 13:35:52.06 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
2010-05-05 13:35:52.06 Server (c) 2005 Microsoft Corporation.
2010-05-05 13:35:52.06 Server All rights reserved.
2010-05-05 13:35:52.06 Server Server process ID is 2848.
2010-05-05 13:35:52.06 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2010-05-05 13:35:52.06 Server This instance of SQL Server last reported using a process ID of 3348 at 2010-5-5 13:35:11 (local) 2010-5-5 5:35:11 (UTC). This is an informational message only; no user action is required.
2010-05-05 13:35:52.06 Server Registry startup parameters:
2010-05-05 13:35:52.06 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2010-05-05 13:35:52.06 Server -e C:\P ......

如何判断字段是否存在
 if col_length('表名','字段1') is null  ALTER TABLE 表名 ADD 字段1 Nvarchar(50)  if col_length('表名','字段2') is null  ALTER TABLE 表名 ADD 字段2 Nvarchar(50) ");
删除字段
if col_length('表名','字段1,') is not null  ALTER TABLE 表名 drop  column 字段1, column 字段2,column 字段3
创建字段
 ALTER TABLE 表名 ADD 字段1 Nvarchar(50) , 字段2  INT NULL ......

if not object_id('Tempdb..#T') is null
    drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go
--I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID<a.ID)
方法2:
delete a  from #T a left join (select min(ID)ID,Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null
方法3:
delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)
方法4(注:ID为唯一时可用):
delete a from #T a where ID not in(select min(ID)from #T group by Name)
方法5:
delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0
方法6:
delete a from #T a where ID<>(select top 1 ID from #T where Name=a.name order by ID)
方法7:
delete a fr ......

分页查询的原理：
 这个只能用再Sql2005及以上的版本
DECLARE @pagenum AS INT, @pagesize AS INT  
SET @pagenum = 2   
SET @pagesize = 3   
SELECT *   
from (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum,    
        speaker, track, score   
      from SpeakerStats) AS D   
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize  
这个适用支持top的sql
SELECT TOP (10) * from hy_hotel_info   
 WHERE hi_id >(SELECT MAX (hi_id)    
from (select top （2*10） hi_id    
from hy_hotel_info order by hi_id)&nbs ......

 
--合并行，并返回合并的值
Create proc [dbo].[proUniteRow]
@tab varchar(30),           --表名
@col varchar(30),           --合并的列名
@where varchar(2000),      --合并的条件
@firstSplit varchar(100), --连接字符前缀
@lastSplit varchar(100),  --连接字符后缀
@order varchar(100),       --排序
@val nvarchar(2000)='' output –返回值
as
begin
 declare @sql nvarchar(2000)
 set @sql='
 declare @retStr varchar(2000)
 set @retStr=''''
 declare cur cursor
 read_only
 for select
 '+@col+' from '+@tab
 if len(@where)>0
 set @sql=@sql+' where '+@where
 
 if len(@order)>0
 set @sql=@sql+' order by '+@order
 
 set @sql=@sql+'
 declare @str varchar(2000)
 open cur
 fetch next from cur into @str
 whil ......

找出TABLE1中
Column1 满足value1
或者
Column2 满足value2
或者
Column3 满足value3
的纪录
SELECT top 10 * from TABLE1
WHERE
CASE WHEN COLUMN1='value1' THEN 1
ELSE
(CASE WHEN COLUMN2='value2' THEN 1
ELSE
(CASE WHEN COLUMN3=value3 THEN 1 ELSE 0 END)
END)
END =1 ......