行列互转
insert into test values ( 1 , ' a ' , 1 , 1000 )
insert into test values ( 1 , ' a ' , 2 , 2000 )
insert into test values ( 1 , ' a ' , 3 , 4000 )
insert into test values ( 1 , ' a ' , 4 , 5000 )
insert into test values ( 2 , ' b ' , 1 , 3000 )
insert into test values ( 2 , ' b ' , 2 , 3500 )
insert into test values ( 2 , ' b ' , 3 , 4200 )
insert into test values ( 2 , ' b ' , 4 , 5500 )
select * from test
-- 行转列
select id,name,
[ 1 ] as "一季度",
[ 2 ] as "二季度",
[ 3 ] as "三季度",
[ 4 ] as "四季度",
[ 5 ] as " 5 "
from
test
pivot
(
sum (profile)
for quarter in
( [ 1 ] , [ 2 ] , [ 3 ] , [ 4 ] , [ 5 ] )
)
as pvt
create table test2(id int ,name varchar ( 20 ), Q1 int , Q2 int , Q3 int , Q4 int )
insert into test2 values ( 1 , ' a ' , 1000 , 2000 , 4000 , 5000 )
insert into test2 values ( 2 , ' b ' , 3000 , 3500 , 4200 , 5500 )
select * from test2
-- 列转行
select id,name,quarter,profile
from
test2
unpivot
(
profile
for quarter in
( [ Q1 ] , [ Q2 ] , [ Q3 ] , [ Q4 ] )
)
as unpvt
sql替换字符串 substring replace
update tbPersonalInfo set TrueName = replace (TrueName, substring (TrueName, 2 , 4 ), ' ** ' ) where ID = 1
-- 例子2:
update tbPersonalInfo set Mobile = replace (Mobile, substring (Mobile, 4 , 11 ), ' ******** ' ) where ID = 1
-- 例子3:
update tbPersonalInfo set Email = replace (Email, ' chinamobile ' , ' ****** ' ) where ID = 1
SQL查询一个表内相同纪录 having
select ID from 表 group by ID having sum ( 1 ) > 1 )
( select ID1 + ID2 + ID3 from 表 group by ID1,ID2,ID3 having sum ( 1 ) > 1 )
SELECT * FROM zy_bho a WHERE EXISTS
( SELECT 1 FROM zy_bho WHERE [ PK ] <> a. [ PK ] AND ZYH = a.ZYH)
-- 方法2:
select a. * from zy_bho a join zy_bho b
on (a. [ pk ] <> b. [ pk ] and a.zyh = b.zyh)
-- 方法3:
select * from zy_bbo where zyh in
( select zyh from zy_bbo group by zyh having count (zyh) > 1 )
-- 其中pk是主键或是 unique的字段。
把多行SQL数据变成一条多列数据,即新增列
DeptName = O.OUName,
' 9G ' = Sum ( Case When PersonalGrade = 9 Then 1 Else 0 End ),
' 8G ' = Sum ( Case When PersonalGrade = 8 Then 1 Else 0 End ),
' 7G4 ' = Sum ( Case When PersonalGrade = 7 AND JobGrade = 4 Then 1 Else 0 End ),
' 7G3 ' = Sum ( Case When PersonalGrade = 7 AND JobGrade = 3 Then 1 Else 0 End ),
' 6G ' = Sum ( Case When PersonalGrade = 6 Then 1 Else 0 End ),
' 5G3 ' = Sum ( Case When PersonalGrade = 5 AND JobGrade = 3 Then 1 Else 0 End ),
' 5G2 ' = Sum ( Case When PersonalGrade = 5 AND JobGrade = 2 Then 1 Else 0 End ),
' 4G ' = Sum ( Case When PersonalGrade = 4 Then 1 Else 0 End ),
' 3G2 ' = Sum ( Case When PersonalGrade = 3 AND JobGrade = 2 Then 1 Else 0 End ),
' 3G1 ' = Sum ( Case When PersonalGrade = 3 AND JobGrade = 1 Then 1 Else 0 End ),
' 2G ' = Sum ( Case When PersonalGrade = 2 Then 1 Else 0 End ),
' 1G ' = Sum ( Case When PersonalGrade = 1 Then 1 Else 0 End ),
-- ' 未定级'=Sum(Case When PersonalGrade=NULL Then 1 Else 0 End)
表复制
SELECT [ IMSI ]
, count ( [ IMEI ] ) as num
FROM [ Test ] . [ dbo ] . [ PhoneChange ] group by [ IMSI ] order by num desc
语法1:Insert INTO table(field1,field2,...) values(value1,value2,...)
语法2:Insert into Table2(field1,field2,...) select value1,value2,... from Table1(要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还可以插入常量。)
语法3:SELECT vale1, value2 into Table2 from Table1(要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中。)
语法4:使用导入导出功能进行全表复制。如果是使用【编写查询以指定要传输的数据】,那么在大数据表的复制就会有问题?因为复制到一定程度就不再动了,内存爆了?它也没有写入到表中。而使用上面3种语法直接执行是会马上刷新到数据库表中的,你刷新一下mdf文件就知道了。
利用带关联子查询Update语句更新数据
Update Table1 set c = ( select c from Table2 where a = Table1.a) where c is null
-- 方法2:
update A
set newqiantity = B.qiantity
from A,B
where A.bnum = B.bnum
-- 方法3:
update
( select A.bnum ,A.newqiantity,B.qiantity from A left join B on A.bnum = B.bnum) AS C
set C.newqiantity = C.qiantity
where C.bnum = XX
连接远程服务器
select * from openrowset ( ' SQLOLEDB ' , ' server=192.168.0.67;uid=sa;pwd=password ' , ' SELECT * FROM BCM2.dbo.tbAppl ' )
-- 方法2:
select * from openrowset ( ' SQLOLEDB ' , ' 192.168.0.67 ' ; ' sa ' ; ' password ' , ' SELECT * FROM BCM2.dbo.tbAppl ' )
Date 和 Time 样式
不带世纪数位 (yy) (1) | 带世纪数位 (yyyy) | 标准 | 输入/输出 (3) |
---|---|---|---|
- | 0 或 100 (1,2) | 默 认 | mon dd yyyy hh:miAM(或 PM) |
1 | 101 | 美 国 | mm/dd/yyyy |
2 | 102 | ANSI | yy.mm.dd |
3 | 103 | 英 国/法国 | dd/mm/yyyy |
4 | 104 | 德 国 | dd.mm.yy |
5 | 105 | 意 大利 | dd-mm-yy |
6 | 106(1) | - | dd mon yy |
7 | 107(1) | - | mon dd, yy |
8 | 108 | - | hh:mi:ss |
- | 9 或 109 (1,2) | 默 认设置 + 毫秒 | mon dd yyyy hh:mi:ss:mmmAM(或 PM) |
10 | 110 | 美 国 | mm-dd-yy |
11 | 111 | 日 本 | yy/mm/dd |
12 | 112 | ISO | yymmdd yyyymmdd |
- | 13 或 113 (1,2) | 欧 洲默认设置 + 毫秒 | dd mon yyyy hh:mi:ss:mmm(24h) |
14 | 114 | - | hh:mi:ss:mmm(24h) |
- | 20 或 120 (2) | ODBC 规范 | yyyy-mm-dd hh:mi:ss(24h) |
- | 21 或 121 (2) | ODBC 规范(带毫秒) | yyyy-mm-dd hh:mi:ss.mmm(24h) |
- | 126 (4) | ISO8601 | yyyy- mm-ddThh:mi:ss.mmm(无空格) |
- | 127(6, 7) | 带时区 Z 的 ISO8601。 | yyyy-mm-ddThh:mi:ss.mmmZ (无 空格) |
- | 130 (1,2) | 回历 (5) | dd mon yyyy hh:mi:ss:mmmAM |
- | 131 (2) | 回历 (5) | dd/mm/yy hh:mi:ss:mmmAM |
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 0 ): 05 16 2006 10 :57AM
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 1 ): 05 / 16 / 06
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 2 ): 06.05 . 16
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 3 ): 16 / 05 / 06
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 4 ): 16.05 . 06
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 5 ): 16 - 05 - 06
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 6 ): 16 05 06
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 7 ): 05 16 , 06
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 8 ): 10 : 57 : 46
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 9 ): 05 16 2006 10 : 57 : 46 :827AM
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 10 ): 05 - 16 - 06
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 11 ): 06 / 05 / 16
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 12 ): 060516
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 13 ): 16 05 2006 10 : 57 : 46 : 937
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 14 ): 10 : 57 : 46 : 967
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 20 ): 2006 - 05 - 16 10 : 57 : 47
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 21 ): 2006 - 05 - 16 10 : 57 : 47.157
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 22 ): 05 / 16 / 06 10 : 57 : 47 AM
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 23 ): 2006 - 05 - 16
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 24 ): 10 : 57 : 47
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 25 ): 2006 - 05 - 16 10 : 57 : 47.250
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 100 ): 05 16 2006 10 :57AM
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 101 ): 05 / 16 / 2006
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 102 ): 2006.05 . 16
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 103 ): 16 / 05 / 2006
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 104 ): 16.05 . 2006
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 105 ): 16 - 05 - 2006
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 106 ): 16 05 2006
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 107 ): 05 16 , 2006
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 108 ): 10 : 57 : 49
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 109 ): 05 16 2006 10 : 57 : 49 :437AM
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 110 ): 05 - 16 - 2006
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 111 ): 2006 / 05 / 16
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 112 ): 20060516
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 113 ): 16 05 2006 10 : 57 : 49 : 513
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 114 ): 10 : 57 : 49 : 547
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 120 ): 2006 - 05 - 16 10 : 57 : 49
SELECT CONVERT ( varchar ( 100 ), GETDATE (), 121 ): 2006
对上面进行动态生成字符串:
declare @count nvarchar ( 100 );
set @sql1 = ' SELECT CONVERT(varchar(100), GETDATE(), 0) '
set @sql2 = ' SELECT @count = CONVERT(varchar(100), GETDATE(), 0) '
exec sp_executesql @sql2 ,N ' @count nvarchar(50) out ' , @count out
print @sql1 + ' : ' + @count
DECLARE @myval decimal ( 5 , 2 )
SET @myval = 193.57
SELECT CAST ( CAST ( @myval AS varbinary ( 20 )) AS decimal ( 10 , 5 ))
-- Or, using CONVERT
SELECT CONVERT ( decimal ( 10 , 5 ), CONVERT ( varbinary ( 20 ), @myval ))
-- 输出193.57000
-- 输出193.57000
select substring ( CONVERT ( varchar ( 15 ),字段名), 11 , 9 ) from 表名
select substring ( cast (字段名 as varchar ( 50 ), 6 , 9 )) from 表名
SQL中的相除
SELECT CASE WHEN ISNULL (A + B, 0 ) <> 0 THEN
LTRIM ( CONVERT ( DEC ( 18 , 2 ),A * 100.0 / (A + B))) + ' % ' ELSE '' END AS ' 百分数 '
FROM TB
select
LTRIM ( CONVERT ( DEC ( 18 , 2 ), 42 * 100.0 / 96 )) + ' % ' AS ' 百分数string ' , -- DEC=decimal
CONVERT ( decimal ( 10 , 2 ), 42 * 100.0 / 96 ) AS ' 百分数dec ' , -- 100 与 100.0是不一样的
CONVERT ( decimal ( 10 , 2 ), 42 * 100 / 96 ) AS ' 没有保留到小数点 '
-- from Tb
order by 百分数dec desc
-- 43.75% 43.75 43.00
-- 方法二:
Select ( Convert ( varchar ( 50 ), Round ( 42 * 100.0 / 96 , 3 )) + ' % ' ) as 百分比 -- from A
-- 43.750000%
四舍五入
function 必须为 tinyint、smallint 或 int。
如果省略 function 或其值为 0(默认值),则将舍入 numeric_expression。
如果指定了0以外的值,则将截断 numeric_expression。 */
SELECT ROUND ( 150.45648 , 2 ); -- 保留小数点后两位,需要四舍五入
SELECT ROUND ( 150.45648 , 2 , 0 ); -- 保留小数点后两位,0为默认值,表示进行四舍五入
SELECT ROUND ( 150.45648 , 2 , 1 ); -- 保留小数点后两位,不需要四舍五入,这里除0以外都是有同样的效果
SELECT ROUND ( 150.45648 , 2 , 2 ); -- 保留小数点后两位,不需要四舍五入,这里除0以外都是有同样的效果
-- 150.46000
-- 150.45000
-- 150.45000
-- 150.45000
对字段出现NULL值的处理
-- case
select case when ' 字段名 ' is null then ' \N ' else convert ( varchar ( 20 ), ' 字段名 ' ) end as ' NewName '
select case when null is null then ' \N ' else convert ( varchar ( 20 ), null ) end as ' NewName '
-- SQL Server 2005:coalesce
select coalesce ( ' 字符串类型字段 ' , ' \N ' ) as ' NewName '
select coalesce ( convert ( varchar ( 20 ), ' 非字符串类型字段 ' ), ' \N ' ) as ' NewName '
select coalesce ( convert ( varchar ( 20 ), null ), ' \N ' ) as ' NewName '
-- coalesce,返回其参数中的第一个非空表达式
select Coalesce ( null , null , 1 , 2 , null ) union
select Coalesce ( null , 11 , 12 , 13 , null ) union
select Coalesce ( 111 , 112 , 113 , 114 , null )
count的几种情况
select count ( * ) from tablename
-- 第二种
select count (ID) from tablename
-- 第三种,1换成其它值也是可以的
select count ( 1 ) from tablename
/*
--第四种,这个不存在性能问题
idint 表ID(如果 indid = 0 或255)。否则为索引所属表的ID
Indid smallint 索引ID:
0=表
1=聚簇索引
>1=非聚簇索引
255=具有text或image数据的表条目。
rows int 基于indid=0 和 indid=1地数据级行数,该值对于indid>1重复。如果indid=255,rows设置为0。
当表没有聚簇索引时,Indid = 0 否则为 1。
*/
select rows,indid from sysindexes where id = object_id ( ' tablename ' ) and indid in ( 0 , 1 )
Union all
select *
into table_now
from table_1
union all select * from table_2
-- Truncate table table_now
查看数据库缓存的SQL
use master
declare @dbid int
Select @dbid = dbid from sysdatabases where name = ' Test ' -- 修改成数据库的名称
select
dbid,UseCounts ,RefCounts,CacheObjtype,ObjType,
DB_Name (dbid) as DatabaseName,SQL
from syscacheobjects
where dbid = @dbid
order by dbid,useCounts desc ,objtype
删除计划缓存
DBCC FREEPROCCACHE
-- 删除某个数据库的计划缓存
USE master
DECLARE @dbid INT
SELECT @dbid = dbid FROM sysdatabases WHERE NAME = ' 表名 '
DBCC FLUSHPROCINDB ( @dbid )
导出时加入特殊字符
情况一:全部字段都需要加字符,在这里设置【文本限定符】就可以了。
情况二:
SELECT
[ ID ]
, '''' + convert ( varchar ( 25 ), [ ts ] , 121 ) + '''' as [ ts ]
, '''' + convert ( varchar ( 25 ), [ otherParty ] , 121 ) + '''' as [ otherParty ]
, '''' + convert ( varchar ( 25 ), [ StartTime ] , 121 ) + '''' as [ StartTime ]
, [ CcCause ]
, [ RrCause ]
FROM [ 表 ]
效果:74983006,'2010-03-09 23:59:10.000'
newid()的妙用
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ dbo ] . [ test ] (
[ name ] [ varchar ] ( 20 ) NULL ,
[ quarter ] [ int ] NULL ,
[ profile ] [ int ] NULL ,
[ dates ] [ smallint ] NULL ,
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
CONSTRAINT [ PK_test ] PRIMARY KEY CLUSTERED
(
[ ID ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [ dbo ] . [ test ] ON
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' a ' , 1 , 1000 , 421 , 1 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' Aa ' , 2 , 2000 , 421 , 2 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' AA ' , 3 , 4000 , 421 , 3 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' a ' , 4 , 5000 , 421 , 4 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 1 , 3000 , 421 , 5 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 2 , 3500 , 421 , 6 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 3 , 4200 , 421 , 7 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 4 , 5500 , 421 , 8 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' a ' , 1 , 1000 , 421 , 9 )
SET IDENTITY_INSERT [ dbo ] . [ test ] OFF
--从数据库中随机取出N条记录的方法:newid()
select top 5 * from test order by newid ()
(图:效果图)
查询时区分大小写
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [ dbo ] . [ test ] (
[ name ] [ varchar ] ( 20 ) NULL ,
[ quarter ] [ int ] NULL ,
[ profile ] [ int ] NULL ,
[ dates ] [ smallint ] NULL ,
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
CONSTRAINT [ PK_test ] PRIMARY KEY CLUSTERED
(
[ ID ] ASC
) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [ dbo ] . [ test ] ON
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' a ' , 1 , 1000 , 421 , 1 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' Aa ' , 2 , 2000 , 421 , 2 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' AA ' , 3 , 4000 , 421 , 3 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' A ' , 4 , 5000 , 421 , 4 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 1 , 3000 , 421 , 5 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 2 , 3500 , 421 , 6 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 3 , 4200 , 421 , 7 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' b ' , 4 , 5500 , 421 , 8 )
INSERT [ dbo ] . [ test ] ( [ name ] , [ quarter ] , [ profile ] , [ dates ] , [ ID ] ) VALUES (N ' A ' , 1 , 1000 , 421 , 9 )
SET IDENTITY_INSERT [ dbo ] . [ test ] OFF
select * from test where [ name ] = ' A ' collate Chinese_PRC_CS_AI
select * from test where ascii (name) = ascii ( ' A ' )
(图:效果图)
SQL换行
SQL的换行
制表符 CHAR(9)
换行符 CHAR(10)
回车 CHAR(13)
*/
PRINT ' Test ' + CHAR ( 13 ) + ' Name '
PRINT ' Test ' + CHAR ( 10 ) + ' Age '
PRINT ' Test ' + CHAR ( 9 ) + ' Tel '
-- 以文本格式显示结果
SELECT ' AAA ' + CHAR ( 10 ) + ' BBB ' -- AAA BBB
SELECT ' AAA ' + CHAR ( 13 ) + ' BBB ' -- AAA BBB
SELECT ' AAA ' + CHAR ( 10 ) + CHAR ( 13 ) + ' BBB ' -- AAA BBB
TRUNCATE TABLE [Table Name]
下面是对Truncate语句在MSSQLServer2000中用法和原理的说明:
Truncate是SQL中的一个删除数据表内容的语句,用法是:
Truncate table 表名 速度快,而且效率高,因为:
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。
DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
其它
dbcc memorystatus
-- 查看哪个引起的阻塞,blk
EXEC sp_who active
-- 查看锁住了那个资源id,objid
EXEC sp_lock
-- 当前查询分析器SPID
-- 查询分析器的状态栏中可以看到
-- 比如sa(57),这就表示当前查询分析器SPID为57,这样在使用profile的时候就可以指定当前窗体进行监控
declare @timediff datetime
select @timediff = getdate ()
select * from tablename
print ' 1耗时: ' + convert ( varchar ( 10 ), datediff (ms, @timediff , getdate ()))
参考文献
SELECT INTO 和 INSERT INTO SELECT 两种表复制语句