SQL 判断字段类型语句

–Title:Generating test data –Author:wufeng4552 –Date :2009-09-25
09:56:07 if object_id(‘tb’)is not null drop table tb go create table
tb(ID int,name text) insert tb select 1,’test’ go –方法1 复制代码 代码如下: select
sql_variant_property(ID,’BaseType’) from tb –方法2 复制代码 代码如下: select object_name(ID)表名,
c.name 字段名, t.name 数据类型, c.prec 长度 from syscolumns c inner join
systypes t on c.xusertype=t.xusertype where
objectproperty(id,’IsUserTable’)=1 and id=object_id(‘tb’)

所有人都知道select top 的用法,但很多人还不知道update top 和 delete top
怎么用。以往的做法是set
rowcount来指定,其实SQL2005中对于Top语句的增强除了参数化之外还包括对update和delete的支持,但可惜的是还不支持自定义的order
by列。如果要自定义派序列可以借助CTE.对于CTE的任何更改都会影响到原始表。我们看下面的测试代码。复制代码 代码如下:set nocount on use tempdb go
if (object_id (‘tb’ ) is not null ) drop table tb go create table tb
(id int identity (1 , 1 ), name varchar (10 ), tag int default 0 )insert
into tb (name ) select ‘a’ insert into tb (name ) select ‘b’ insert into
tb (name ) select ‘c’ insert into tb (name ) select ‘d’ insert into tb
(name ) select ‘e’ /*–更新前两行 id name tag ———– ———-
———– 1 a 1 2 b 1 3 c 0 4 d 0 5 e 0 */ update top (2 ) tb set tag
= 1 select * from tb /*–更新后两行 id name tag ———– ———-
———– 1 a 1 2 b 1 3 c 0 4 d 1 5 e 1 */ ;with t as ( select top (2
) * from tb order by id desc )update t set tag = 1 select * from tb
/*–删除前两行 id name tag ———– ———- ———– 3 c 0 4 d
1 5 e 1 */ delete top (2 ) from tb select * from tb /*–删除后两行 id
name tag ———– ———- ———– 3 c 0 */ ;with t as (
select top (2 ) * from tb order by id desc )delete from t select *
from tb set nocount off
我会在下一篇文章中介绍一个应用,就是很多人关心的如何独占查询。如果你感兴趣的话可以自己先思考一下,我给一个提示:
SQLServer2005有一个关键字Output,它可以将更改和插入的数据输出,我们配合update
top就可以模拟出来一个相对高效的独占查询的事物。此功能适合用在并行的任务处理或者消费中。

–> Title  : MSSQL求連續ID內數量合計-常見解決方案

–> Author : wufeng4552

–> Date   : 2009-12-04

if object_id(‘tb’) is not null drop table tb

go

create table tb(ID varchar(10),Num decimal(10,2))

insert tb

select ‘001’,200.00 union all

select ‘002’,200.00 union all

select ‘003’,300.00 union all

select ‘007’,500.00 union all

select ‘008’,800.00 union all

select ‘009’,200.00 union all

select ‘012’,100.00 union all

select ‘013’,100.00 union all

select ‘014’,200.00 union all

select ‘017’,100.00 union all

select ‘018’,400.00 union all

select ‘019’,300.00

–>方法 1 臨時表

if object_id(‘tempdb..#t1’)is not null

drop table #澳门金沙vip,t1

if object_id(‘tempdb..#t2’)is not null

drop table #t2

go

–取出起號

select cnt=identity(int,1,1),* into #t1 from tb t where not
exists(select 1 from tb where id=t.id-1)order by t.id

–取出止號