--
WAITFOR
-- 例 等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay ' 01:02:03 '
select * from employee
-- 例 等到晚上11 点零8 分后才执行SELECT 语句
waitfor time ' 23:08:00 '
select * from employee
-- 例 等待1 小时2 分零3 秒后才执行SELECT 语句
waitfor delay ' 01:02:03 '
select * from employee
-- 例 等到晚上11 点零8 分后才执行SELECT 语句
waitfor time ' 23:08:00 '
select * from employee
2. 查看sqlserver版本
select
serverproperty(
'
productversion
'
),serverproperty(
'
productlevel
'
),serverproperty(
'
edition
'
)
3. 在SQLSERVER,简单的组合sp_spaceused和sp_MSforeachtable这两个存储过程,可以方便的统计出用户数据表的大小,包括记录总数和空间占用情况,非常实用,在SqlServer2K和SqlServer2005中都测试通过。
/**/
/*
1. exec sp_spaceused '表名' (SQL统计数据,大量事务操作后可能不准)
2. exec sp_spaceused '表名', true (更新表的空间大小,准确的表空大小,但可能会花些统计时间)
3. exec sp_spaceused (数据库大小查询)
4. exec sp_MSforeachtable "exec sp_spaceused '?'" (所有用户表空间表小,SQL统计数据,,大量事务操作后可能不准)
5. exec sp_MSforeachtable "exec sp_spaceused '?',true" (所有用户表空间表小,大数据库慎用)
*/
create table #t(name varchar ( 255 ), rows bigint , reserved varchar ( 20 ), data varchar ( 20 ), index_size varchar ( 20 ), unused varchar ( 20 ))
exec sp_MSforeachtable " insert into #t exec sp_spaceused ' ? ' "
select * from #t
drop table #t
1. exec sp_spaceused '表名' (SQL统计数据,大量事务操作后可能不准)
2. exec sp_spaceused '表名', true (更新表的空间大小,准确的表空大小,但可能会花些统计时间)
3. exec sp_spaceused (数据库大小查询)
4. exec sp_MSforeachtable "exec sp_spaceused '?'" (所有用户表空间表小,SQL统计数据,,大量事务操作后可能不准)
5. exec sp_MSforeachtable "exec sp_spaceused '?',true" (所有用户表空间表小,大数据库慎用)
*/
create table #t(name varchar ( 255 ), rows bigint , reserved varchar ( 20 ), data varchar ( 20 ), index_size varchar ( 20 ), unused varchar ( 20 ))
exec sp_MSforeachtable " insert into #t exec sp_spaceused ' ? ' "
select * from #t
drop table #t
4.从表中随即抽取N个数据
SELECT
TOP
10
*
FROM LargeTable
ORDER BY NEWID ()
FROM LargeTable
ORDER BY NEWID ()
5. SQL语句附加数据库
use
master
EXEC sp_attach_db @dbname = N ' pubs ' ,
@filename1 = N ' c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf ' ,
@filename2 = N ' c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf '
EXEC sp_attach_db @dbname = N ' pubs ' ,
@filename1 = N ' c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf ' ,
@filename2 = N ' c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf '
6.数据库表间数据复制
--
1.表结构相同的表,且在同一数据库(如,table1,table2)
Sql : insert into table1 select * from table2 (完全复制)
insert into table1 select distinct * from table2(不复制重复纪录)
insert into table1 select top 5 * from table2 (前五条纪录)
-- 2.不在同一数据库中(如,db1 table1,db2 table2)
sql: insert into db1..table1 select * from db2..table2 (完全复制)
insert into db1..table1 select distinct * from db2table2(不复制重复纪录)
insert into tdb1..able1 select top 5 * from db2table2 (前五条纪录)
Sql : insert into table1 select * from table2 (完全复制)
insert into table1 select distinct * from table2(不复制重复纪录)
insert into table1 select top 5 * from table2 (前五条纪录)
-- 2.不在同一数据库中(如,db1 table1,db2 table2)
sql: insert into db1..table1 select * from db2..table2 (完全复制)
insert into db1..table1 select distinct * from db2table2(不复制重复纪录)
insert into tdb1..able1 select top 5 * from db2table2 (前五条纪录)
7.按姓氏笔画排序
Select
*
From
TableName
Order
By
CustomerName Collate Chinese_PRC_Stroke_ci_as
//****************************************
by: Amen cnblogs博客 Sina博客
//****************************************
by: Amen cnblogs博客 Sina博客
//****************************************