sqlcmd 实用工具
使用 sqlcmd 实用工具，可以在命令提示符处、SQLCMD 模式下的查询编辑器、Windows 脚本文件或 SQL Server 代理作业的操作系统 (Cmd.exe) 作业步骤中，输入 Transact-SQL 语句、系统过程和脚本文件。 此实用工具使用 OLE DB 执行 Transact-SQL 批处理。
sqlcmd
[{ { -U login_id [ -P password ] } | –E trusted connection }]
[ -z new password ] [ -Z new password and exit]
[ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]
[ -l login time_out ] [ -A dedicated admin connection ]
[ -i input_file ] [ -o output_file ]
[ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]
[ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ]
[ -R use client regional settings ]
[ -q "cmdline query" ] [ -Q "cmdline query" and exit ]
[ -e echo input ] [ -t query time_out ]
[ -I enable Quoted Identifiers ]
[ -v var = "value"...] [ -x disable variable substitution ]
[ -h headers ][ -s col_separator ] [ -w column_width ]
[ -W remove trailing spaces ]
[ -k [ 1 | 2 ] remove[replace] control characters ]
[ -y display_width ] [-Y display_width ]
[ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ]
[ -a packet_size ][ -c cmd_end ]
[ -L [ c ] list servers[clean output] ]
[ -p [ 1 ] print statistics[colon format] ]
[ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit]
[ -? show syntax summary ]
简例：
sqlcmd -U sa -P 1234567 -S 192.168.1.1
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
-SQL语句参考手册2010-12-03 22:30一、基础1、说明：创建数据库CREATE DATABASE database-name 2、说明：删除数据库drop database dbname3、说明：备份sql server--- 创建 备份数据的 deviceUSE masterEXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'--- 开始 备份BACKUP DATABASE pubs TO testBack 4、说明：创建新表create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根据已有的表创建新表： A：create table tab_new like tab_old (使用旧表创建新表)B：create table tab_new as select col1,col2… from tab_old definition only5、说明：删除新表drop table tabname 6、说明：增加一个列Alter table tabname add column col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。7、说明：添加主键： Alter table tabname add primary key(col) 说明：删除主键： Alter table tabname drop primary key(col) 8、说明：创建索引：create [unique] index idxname on tabname(col….) 删除索引：drop index idxname注：索引是不可更改的，想更改必须删除重新建。9、说明：创建视图：create view viewname as select statement 删除视图：drop view viewname10、说明：几个简单的基本的sql语句选择：select * from table1 where 范围插入：insert into table1(field1,field2) values(value1,value2)删除：delete from table1 where 范围更新：update table1 set field1=value1 where 范围查找：select * from table1 where field1 like ’%value1%’ ---like的语法很精妙，查资料!排序：select * from table1 order by field1,field2 [desc]总数：select count as totalcount from table1求和：select sum(field1) as sumvalue from table1平均：select avg(field1) as avgvalue from table1最大：select max(field1) as maxvalue from table1最小：select min(field1) as minvalue from table111、说明：几个高级查询运算词A： UNION 运算符 UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 B： EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 C： INTERSECT 运算符INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。 注：使用运算词的几个查询结果行必须是一致的。 12、说明：使用外连接 A、left （outer） join： 左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。 SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.cB：right （outer） join: 右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。 C：full/cross （outer） join： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。12、分组:Group by:一张表，一旦分组 完成后，查询后只能得到组相关的信息。组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准) 在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据在selecte统计函数中的字段，不能和普通的字段放在一起；13、对数据库进行操作：分离数据库： sp_detach_db; 附加数据库：sp_attach_db 后接表明，附加需要完整的路径名14.如何修改数据库的名称:sp_renamedb 'old_name', 'new_name'
&"\data.mdb" &"' where..4、说明：子查询(表名1：a 表名2：b)select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)5、说明：显示文章、提交人和最后回复时间select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b6、说明：外连接查询(表名1：a 表名2：b)select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c7、说明：在线视图查询(表名1：a )select * from (SELECT a,b,c FROM a) T where t.a > 1;8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括select * from table1 where time between time1 and time2select a,b,c, from table1 where a not between 数值1 and 数值29、说明：in 的使用方法select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)10、说明：两张关联表，删除主表中已经在副表中没有的信息 delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )11、说明：四表联查问题：select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....12、说明：日程安排提前五分钟提醒 SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>513、说明：一条sql 语句搞定数据库分页select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段具体实现：关于数据库分页： declare @start int,@end int @sql nvarchar(600) set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’ exec sp_executesql @sql
,@NewOwner as NVARCHAR(12
ASDECLARE @Name as NVARCHAR(12
DECLARE @Owner as NVARCHAR(12
DECLARE @OwnerName as NVARCHAR(12
DECLARE curObject CURSOR FOR select 'Name' = name, 'Owner' = user_name(uid)from sysobjectswhere user_name(uid)=@OldOwnerorder by nameOPEN curObjectFETCH NEXT FROM curObject INTO @Name, @OwnerWHILE(@@FETCH_STATUS=0)BEGIN if @Owner=@OldOwner begin set @OwnerName = @OldOwner + '.' + rtrim(@Name) exec sp_changeobjectowner @OwnerName, @NewOwnerend-- select @name,@NewOwner,@OldOwnerFETCH NEXT FROM curObject INTO @Name, @OwnerENDclose curObjectdeallocate curObjectGO
ECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocessesWHERE program_name IN('SQL profiler',N'SQL 事件探查器')EXEC sp_msforeach_worker '?'7.记录搜索:开头到N条记录Select Top N * From 表-------------------------------N到M条记录(要有主索引ID)Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc----------------------------------N到结尾记录Select Top N * From 表 Order by ID Desc案例例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段， 写一个SQL语句， 找出表的第31到第40个记录。select top 10 recid from A where recid not in(select top 30 recid from A)分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。解决方案1，用order by select top 30 recid from A order by ricid 如果该字段不是自增长，就会出现问题2，在那个子查询中也加条件：select top 30 recid from A where recid>-1例2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'print @s exec sp_executesql @s9：获取当前数据库中的所有用户表select Name from sysobjects where xtype='u' and status>=010：获取某一个表的所有字段select name from syscolumns where id=object_id('表名')select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')两种方式的效果相同11：查看与某一个表相关的视图、存储过程、函数select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'12：查看当前数据库中所有存储过程select name as 存储过程名称 from sysobjects where xtype='P'13：查询用户创建的所有数据库select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')或者select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x0114：查询某一个表的字段和数据类型select column_name,data_type from information_schema.columnswhere table_name = '表名' 15：不同服务器数据库之间的数据操作--创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例 select * from ITSV.数据库名.dbo.表名 --导入示例 select * into 表 from ITSV.数据库名.dbo.表名 --以后不再使用时删除链接服务器 exec sp_dropserver 'ITSV ', 'droplogins '
二、提升1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)法一：select * into b from a where 1<>1（仅用于SQlServer）法二：select top 0 * into b from a2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)insert into b(a, b, c) select d,e,f from b;3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件例子：..from b in '"&Server.MapPath("."
注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）14、说明：前10条记录select top 10 * form table1 where 范围15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表(select a from tableA ) except (select a from tableB) except (select a from tableC)17、说明：随机取出10条数据select top 10 * from tablename order by newid()18、说明：随机选择记录select newid()19、说明：删除重复记录1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)2),select distinct * into temp from tablename delete from tablename insert into tablename select * from temp评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段alter table tablename--添加一个自增列add column_b int identity(1,1)delete from tablename where column_b not in(select max(column_b) from tablename group by column1,column2,...)alter table tablename drop column column_b20、说明：列出数据库里所有的表名select name from sysobjects where type='U' // U代表用户21、说明：列出表里的所有的列名select name from syscolumns where id=object_id('TableName')22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type显示结果：type vender pcs电脑 A 1电脑 A 1光盘 B 2光盘 A 2手机 B 3手机 C 323、说明：初始化表table1TRUNCATE TABLE table124、说明：选择从10到15的记录select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc三、技巧1、1=1，1=2的使用，在SQL语句组合时用的较多“where 1=1” 是表示选择全部 “where 1=2”全部不选，如：if @strWhere !='' beginset @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere endelse beginset @strSQL = 'select count(*) as Total from [' + @tblName + ']' end 我们可以直接写成错误！未找到目录项。set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库--重建索引DBCC REINDEXDBCC INDEXDEFRAG--收缩数据和日志DBCC SHRINKDBDBCC SHRINKFILE3、压缩数据库dbcc shrinkdatabase(dbname)4、转移数据库给新用户以已存在用户权限exec sp_change_users_login 'update_one','newname','oldname'go5、检查备份集RESTORE VERIFYONLY from disk='E:\dvbbs.bak'6、修复数据库ALTER DATABASE [dvbbs] SET SINGLE_USERGODBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCKGOALTER DATABASE [dvbbs] SET MULTI_USERGO7、日志清除SET NOCOUNT ONDECLARE @LogicalFileName sysname,@MaxMinutes INT,@NewSize INT
USE tablename -- 要操作的数据库名SELECT @LogicalFileName = 'tablename_log', -- 日志文件名@MaxMinutes = 10, -- Limit on time allowed to wrap log.@NewSize = 1 -- 你想设定的日志文件的大小(M)Setup / initializeDECLARE @OriginalSize intSELECT @OriginalSize = size FROM sysfilesWHERE name = @LogicalFileNameSELECT 'Original Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'FROM sysfilesWHERE name = @LogicalFileNameCREATE TABLE DummyTrans(DummyColumn char (8000) not null)
DECLARE @Counter INT,@StartTime DATETIME,@TruncLog VARCHAR(255)SELECT @StartTime = GETDATE(),@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'DBCC SHRINKFILE (@LogicalFileName, @NewSize)EXEC (@TruncLog)-- Wrap the log if necessary.WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expiredAND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize BEGIN -- Outer loop.SELECT @Counter = 0WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))BEGIN -- updateINSERT DummyTrans VALUES ('Fill Log') DELETE DummyTransSELECT @Counter = @Counter + 1ENDEXEC (@TruncLog) ENDSELECT 'Final Size of ' + db_name() + ' LOG is ' +CONVERT(VARCHAR(30),size) + ' 8K pages or ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'FROM sysfiles WHERE name = @LogicalFileNameDROP TABLE DummyTransSET NOCOUNT OFF 8、说明：更改某个表exec sp_changeobjectowner 'tablename','dbo'9、存储更改全部表CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch@OldOwner as NVARCHAR(12
10、SQL SERVER中直接循环写入数据declare @i intset @i=1while @i<30begin insert into test (userid) values(@i) set @i=@i+1end案例：有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:Name scoreZhangshan80Lishi 59Wangwu 50Songquan69while((select min(score) from tb_table)<60)beginupdate tb_table set score =score*1.01 where score<60if (select min(score) from tb_table)>60 breakelse continueend
数据开发-经典
1.按姓氏笔画排序:Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多2.数据库加密:select encrypt('原始密码')select pwdencrypt('原始密码')select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码')select pwdencrypt('原始密码')select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同3.取回表中字段:declare @list varchar(1000),@sql nvarchar(1000) select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'set @sql='select '+right(@list,len(@list)-1)+' from 表A' exec (@sql)4.查看硬盘分区:EXEC master..xp_fixeddrives5.比较A,B表是否相等:if (select checksum_agg(binary_checksum(*)) from A) = (select checksum_agg(binary_checksum(*)) from B)print '相等'elseprint '不相等'6.杀掉所有的事件探察器进程
--连接远程/局域网数据(openrowset/openquery/opendatasource) --1、openrowset --查询示例 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --生成本地表 select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
请按步骤进行,未进行前面的步骤时,请不要做后面的步骤，以免损坏你的数据库.
一般不建议做第4,6两步，第4步不安全,有可能损坏数据库或丢失数据。第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复。
1.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG
2.截断事务日志
BACKUP LOG 数据库名 WITH NO_LOG
3.收缩数据库文件(如果不压缩,数据库的文件不会减小
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
·选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
·选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了，也可以用SQL语句来完成
·收缩数据库
DBCC SHRINKDATABASE(客户资料)
·收缩指定数据文件,1是文件号,可以通过这个语句查询到:
select * from sysfiles
DBCC SHRINKFILE(1)
4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)
·分离数据库:
企业管理器--服务器--数据库--右键--分离数据库
·在我的电脑中删除LOG文件
·附加数据库:
企业管理器--服务器--数据库--右键--附加数据库
此法将生成新的LOG，大小只有500多K
或用代码：
下面的示例分离 pubs，然后将 pubs 中的一个文件附加到当前服务器。
·分离
EXEC sp_detach_db @dbname = 'pubs'
·删除日志文件
·再附加
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft
SQL Server\MSSQL\Data\pubs.mdf'
5.为了以后能自动收缩,做如下设置
企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"
SQL语句设置方式:
EXEC sp_dboption '数据库名',
'autoshrink', 'TRUE'
6.如果想以后不让它日志增长得太大
企业管理器--服务器--右键数据库--属性--事务日志
将文件增长限制为xM(x是你允许的最大数据文件大小)
SQL语句的设置方式:
alter database 数据库名 modify file(name=逻辑文件名,maxsize=20)
个人的验证：
--收缩数据库日志，在这之前需要有完整的数据库备份
BACKUP LOG chinatown2 With no_log
DBCC SHRINKFILE (chinatown_log, 20480)
上面这两步可以
在学习查询调优知识时，有必要对数据页和不同类型的索引页加以理解．这有助于对基本概念的更好理解以及日后更好地性能调优．这里采用＂一步一步＂的教程与初学者分享我的知识．
建议SQL Server DBA花一些时间学习不同类型页的结构，这有助于DBA在运行DBCC CHECKDB而得到的警告信息可以更好的做出判断或提供更好的调优方案等等。
在写本文时，不可避免地要参考一些未归档的命令，强烈建议初学者在本地或开发环境进行脚本的测试。对于不熟悉SQL Server 2005/2008系统视图中的系统表的初学者，可以参考“Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views”。掌握这些系统视图使你的日常工作变得容易。
本篇内容适用于了解SQL Server存储内幕的初学者，长话短说，下面我们进入示例。
首先我们需要创建一个数据库，这里取名为LearningInternals，脚本如下：
CREATEDATABASELearningInternals
GO
USELearningInternalsGO
知识点：数据文件和日志文件
一个数据库有3种文件类型：
1. 主数据文件（扩展名为.MDF)－每个数据库有一个主数据文件，如果没有辅助数据文件时，所有用户表均存储在PRIMARY文件组
2. 辅助数据文件（扩展名为.NDF)－每个数据库可以有0个或多个辅助数据文件，这有助于提高性能和有效管理表。
3. 事务日志文件（扩展名为.LDF)－默认数据库有一个日志文件，用于记录表的记录修改历史，有助于数据的备份和灾难恢复
下面的命令是查询并列出当前数据库所有的文件组信息（包括主文件、辅助文件信息）
select*fromsys.filegroups
go
以下查询将列出与其相关的所有文件的信息：
selectdatabase_id, file_id, type, type_desc, name, physical_name, state_desc,size, is_read_only, backup_lsn, differential_base_guidfromsys.master_fileswhereDB_NAME(database_id) ='LearningInternals'
go
提示：
建议将数据文件和日志文件存储在不同的驱动器上，这有助于提高性能，此外不要把TEMPDB数据库与用户数据库存储在同一磁盘。
在继续学习之前，需要了解一些关键知识点：
页面（Page)----SQL Server中数据存储的基础单位，数据库创建后，主文件和辅助文件中的数据空间逻辑上被划分为8K的数据页。页号引用数据文件号，例如：主文件组的第1页命名为1:0（这里的1表示文件组，0表示页号）。
盘区（Extent)----盘区由8个数据页组成（每页8K，即64KB），可以存储1到8个表。
有2种类型的盘区：
1）统一盘区（Uniform)，所有8个数据页分配给一个对象，如表或索引；
2）混合盘区（Mixed），可以存储表和索引
当创建表，然后插入一行数据时，表就从混合盘区中获取一个页面，随着表中记录的增加，表由混合盘区移入统一盘区，这样做有助于空间得到有效的管理。
为方便起见，逻辑上我们把页划分为如下：
1. 系统页－存储引擎用于管理用户数据
2. 数据页或索引页－用户创建的表或索引
系统表：
序号 页面 描述
1 Header页 存储数据文件的详细信息，如大小、类型等等
2 GAM Global Allocation Map，GAM是数据文件的第3页（即1：2），一个GAM可以容纳4GB的数据空间，超过4GB，则会产生另一个GAM
3 SGAM Shared Global Allocation Map.SGAM是数据文件的第4页（即1：3），一个SGAM可以容纳4GB的数据空间，超过4GB，则会产生另一个SGAM。
4 IAM Index Allocation Map,IAM页可以找出与某表关联的页面（对某表来说，如果是分区表或表中存储了overflow页（LOB数据等）则可能有多个IAM
5 PFS Page Free Space.PFS表示每个页面上可用的空间，每页面代表一个字节，对于8000个页面，仅只有一个PFS。
6 DCM Differential Change Map.
7 BCM Bulk change Map.
数据页和索引页
序号 页面 描述
1 数据页 容纳记录行，当执行INSERT语句时，PFS找出保存该记录的正确页
2 行溢出页 自SQL2005以来，可以使用4个长度为5000的varchar字段，也就是说不受每行最大8000个字节的限制，允许行溢出页，该页仅用于记录大小超过8000字节
3 大对象LOB LOB数据，如text,varchar(max)页面，这些页面则会与数据页或索引页分开存储
数据库创建时，数据文件大致包含如下的页面结构：
Page Header PFS GAM SGAM Unused Unused DCM BCM … Data Data Data Data Data Data Data
Page
No.
1:0
1:1 1:2 1:3 1:4 1:5 1:6 1:7 … 1:15001 1:15002 1:15003 1:15004 1:15005 1:15006 1:15007
下面的查询列出所有表与其表对应的页面信息：
selectso.name, so.object_id, sp.index_id, sp.partition_id, sp.hobt_id, sa.container_id, internals.total_pages, internals.used_pages, internals.data_pages, first_page, root_page, first_iam_pagefromsys.objects soinnerjoinonso.object_id = sp.object_idinnerjoinsys.allocation_units saonsa.container_id = sp.hobt_idinnerjoinsys.system_internals_allocation_units internalsoninternals.container_id = sa.container_idwhereso.namenotlike'sys%'
小结：
当数据库创建时，默认会有一个主数据文件和一个日志文件。数据文件逻辑上分成数百个8K的页面，一些与系统相关的页面，如GAM,SGAM,IAM和PFS用于管理数据页。
PS:以上源自http://www.sqlservercentral.com/ ... -part-1-basics.aspx，如有翻译不当，请指正！
如何修改sql server 2005数据库系统日志保存路径
把sql server 2005装在了C盘，怎么把数据库的系统日志文件保存到其他盘。
问题补充 2009-09-25 15:25
是“数据库系统日志”，也就是事务日志。
问题补充 2009-09-27 09:27
step 1: 日志模式设为简单,并将自动输增长改为每次增长 200M左右。
step 2:重启sql 服务可以恢复tempdb原始大小
step 3:移动tempdb到d:\
use tempdb;
go
alter database tempdb
modify file (name = tempdev,filename='E:\sqldata\tempdbmodified.mdf');
go
alter database tempdb
modify file (name = templog,filename='E:\sqldata\templogmodified.ldf');
go
step 4:别外看来你的系统临时数据生成的比较大，可以增加多个数据文件，可以放在不同的Raid盘上,并且的一定将空间预先手动扩到需要的大小。
step 5:数据库安装很忌讳将系统数据库安装在跟操作系统一个物理盘下,看来你的master等数据库也在C盘下。
我原来的sqlserver2000的日志文件（transaction logs)路径设成了和data files同样的盘符中，我现在想修改到其他盘符去，请问如何修改？
我原来的sqlserver2000的日志文件（transaction logs)路径设成了和data files同样的盘符中，我现在想修改到其他盘符去，请问如何修改?
=========================================================
1、所有用户退出系统，关闭数据库，重新启动数据库
2、detattc h 数据库
3、attach 数据库，但不包括日志
4、在新目录下建立数据库日志
alter database的filename只能修改tempdb数据库文件的物理路径，而且还要重起后才能生效~
用楼上的方法，妙！~
最初由 ub40 发布
[B]我原来的sqlserver2000的日志文件（transaction logs)路径设成了和data files同样的盘符中，我现在想修改到其他盘符去，请问如何修改？ [/B]
问一句，好像采用sp_attach_single_file_db的话，前提是只有一个数据库文件，没有而且同样目录下，要先把日志文件删除。
但是attach以后，系统会自动创建一个日志文件？
这样，至少会有一个日志文件和数据文件在同一个目录。
sp_attach_single_file_db 不是只对一个数据文件的数据库，这个命令相对的是.mdf的数据文件，由主数据文件来识别其他的数据文件，对于只有一个日志文件的数据库，如果在detach的过程中日志丢失，用 sp_attach_single_file_db系统会自动给s数据库增加一个日志文件，如果数据本身是多个日志文件，则方法不同
因此，我认为，考虑为了提供系统性能。将日志文件移动到一个新的硬盘上，最简单的方法应该是：采用备份和恢复。
1 全备份数据库
2 恢复，在选项里面可以修改日志文件的路径，而且，你可以修改数据文件的路径，无论你有多少个文件。
2楼的方法不行呀，我用的attachDB语句：
exec sp_attach_single_file_db @dbname = 'Siemens',
@physname = 'e:\sqlserver\MSSQL\Data\Siemens.MDF'
但出现如下错误：
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Siemens'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'e:\sqlserver\MSSQL\data\Siemens_Log.LDF' may be incorrect.
attach_db需要日志文件的。没有日志文件是不行的。
而且，即使使用sp_attach_single_file_db，也还会在相同的目录下，自动创建一个日志文件。
[B]移动日志文件的最好方法是备份和恢复。attach不是用来解决这类问题的。[/B]
不好意思，方法有一些问题，
先让所有用户退出，分离数据库
1、exec sp_detach_db @dbname = 'database1'
然后，在操作系统级别将log 拷贝到新目录下。再执行
2、EXEC sp_attach_db @dbname = 'database1',
@filename1 = 'd:\data1_Data.mdf',
@filename2 = 'd:\log\data1_log.ldf'
其中 @filename2就是你拷贝的日志文件
已经经过测试 ，没有问题
整个操作不到3分钟，应该是最快的把
１． ＳＴＯＰ ＳＱＬ ＳＥＲＶＥＲ ＳＥＲＶＩＣＥ
２． ＭＯＶＥ ＦＩＬＥＳ
３． ＲＥＡＴＴＡＣＨ ＴＨＥＭ
那移动mdf ndf也用同样步骤么？
自己建立一个小库试验一下把
可以通过该系统表，然后net stop mssqlserver ；move物理文件实现；最后net start mssqlserver
BACKUP DATABASE dbname TO disk = 'd:\dbname.bak'
RESTORE DATABASE dbname FROM disk = 'd:\dbname.bak'
with move 'dbname_DAT' to 'e:\??????????.mdf',
move 'dbname_LOG' to 'f:\????????.ldf'
EXECUTE master..xp_cmdshell 'copy d:\备份\progbak.bat'
http://hi.baidu.com/ligq/blog/item/931a31fac083078f9e514615.html
很多情况下，需要给客户更新数据库，不值得跑一趟，客户自己也不懂得如何操作，远程操作网速还跟不上...这时可以把要更新的sql语句保存成文件，写一个批处理在命令行状态下调用查询分析器来执行这个sql文件里的语句。
下面就是sqlserver帮助里对于查询分析器(isqlw)命令行参数的解释：
isqlw 实用工具（SQL 查询分析器）使您得以输入 Transact-SQL 语句、系统存储过程和脚本文件。通过设置快捷方式或创建批处理文件，可以启动预配置的 SQL 查询分析器。
语法
isqlw
[-?] |
[
[-S server_name[\instance_name]]
[-d database]
[-E] [-U user] [-P password]
[{-i input_file} {-o output_file} [-F {U|A|O}]]
[-f file_list]
[-C configuration_file]
[-D scripts_directory]
[-T template_directory]
]
参数
-?
显示用法信息。
-S server_name[\instance_name]：
指定要连接到的 Microsoft® SQL Server™ 2000 实例。指定用于连接到该服务器上的 SQL Server 2000 默认实例的 server_name。指定用于连接到该服务器上的 SQL Server 2000 命名实例的 server_name\instance_name。如果未指定服务器，isqlw 将连接到本地计算机上的 SQL Server 默认实例。从网络上的远程计算机执行 isqlw 时，此选项是必需的。
-d database
当启动 isqlw 时，发出一个 USE database 语句。默认值为用户的默认数据库。
-E
使用信任连接而不请求密码。
-U user
用户登录 ID。登录 ID 区分大小写。
-P password
是登录密码。默认设置为 NULL。
-i input_file
标识包含一批 SQL 语句或存储过程的文件。必须同时指定 -i 和 -o 选项。如果指定 -i 和 -o 选项，将执行输入文件中的查询，并将结果保存到输出文件中。在查询执行过程中不显示用户接口。当执行完成后，进程退出。
-o output_file
标识接收来自 isqlw 的输出的文件。必须同时指定 –i 和 –o 选项。如果指定 -i 和 -o 选项，将执行输入文件中的查询，并将结果保存到输出文件中。在查询执行过程中不显示用户接口。当执行完成后，进程退出。如果未使用 -F 指定文件格式,则输出文件使用与输入文件相同的类型。
-F {U|A|O}
是输入文件和输出文件的格式。值包括 Unicode、ANSI 和 OEM。如果未指定 -F，则使用自动模式（如果文件标为 Unicode 格式，则以 Unicode 格式打开；否则，以 ANSI 格式打开文件）。
-f file_list
将列出的文件装载到 SQL 查询分析器中。使用 -f 选项，可以装载一个或多个文件（文件名以单个空格分开）。如果指定了多个文件，则以相同的连接上下文将这些文件打开。文件名可以包含该文件所驻留的目录路径。可以使用通配符，如 C:\Test\*.sql 中的星号 (*)。
-C configuration_file
使用配置文件中指定的设置。其它在命令提示下显式指定的参数将重写相应配置文件中的设置。
-D scripts_directory
重写在注册表中或在用 –C 指定的配置文件中指定的默认存储脚本目录。该值不保留在注册表或配置文件中。若要在 SQL 查询分析器中查看该选项的当前值，请单击"工具"菜单，然后单击"选项"命令。
-T template_directory
重写在注册表中或在用 –C 指定的配置文件中指定的默认模板目录。该值不保留在注册表或配置文件中。若要在 SQL 查询分析器中查看该选项的当前值，请单击"工具"菜单，然后单击"选项"命令。
注释
有用户界面或没有用户界面时都可使用 isqlw 实用工具。若要在没有用户界面的情况下运行 isqlw，请指定有效登录信息（具有信任连接或有效登录 ID 及密码的 SQL Server 2000 实例）和输入及输出文件。isqlw 将执行输入文件的内容，并将结果保存到输出文件中。
如果没有指定输入和输出文件，isqlw 将交互运行并启动 SQL 查询分析器。如果指定了有效登录信息，isqlw 将直接连接 SQL Server 2000 实例。如果指定的连接信息不充足，将出现"连接到 SQL Server"对话框。
isqlw 和 SQL 查询分析器使用 ODBC API。该实用工具使用 SQL-92 的 Microsoft® SQL Server ODBC 驱动程序默认设置。
示例
A. 执行 SQL 语句
本例使用 Windows 身份验证连接到 MyServer 上的 pubs 数据库，并执行 Input.sql 文件。结果保存在 Output.txt 文件中。这些文件以 Unicode 文件方式打开。
isqlw -S MyServer -d pubs -E -i input.sql -o output.txt -FU
B. 使用通配符
本例将两个文件装载到 SQL 查询分析器中。将使用 Windows 身份验证连接到本地服务器。
isqlw -d pubs -E -f "c:\Program Files\Microsoft SQL Server\MSSQL\Install\instpubs.sql" "c:\Program Files\Microsoft SQL Server\MSSQL\Install\instcat.sql"
C. 装载多个文件
本例将所有 .sql 文件装载到 SQL 查询分析器中。所有连接均使用 Windows 身份验证并指向本地服务器上的 pubs 数据库。
isqlw -d pubs -E -f "c:\Program Files\Microsoft SQL Server\MSSQL\Install\*.sql"
D. 使用 Unicode 文件
本例连接到 MyServer（pubs 数据库)，并执行 input_file 中的 SQL 语句，执行结果存储在 output_file 中。
isqlw -S MyServer -d pubs -U sa -P -i input_file -o output_file
制表符 CHAR(9)
换行符 CHAR(10)
回车 CHAR(13)
http://topic.csdn.net/t/20040107/23/2644011.html
osql 实用工具
osql 实用工具使您得以输入 Transact-SQL 语句、系统过程和脚本文件。该实用工具通过 ODBC 与服务器通讯。
语法
osql
[-?] |
[-L] |
[
{
{-U login_id [-P password]}
| -E
}
[-S server_name[\instance_name]] [-H wksta_name] [-d db_name]
[-l time_out] [-t time_out] [-h headers]
[-s col_separator] [-w column_width] [-a packet_size]
[-e] [-I] [-D data_source_name]
[-c cmd_end] [-q "query "] [-Q "query "]
[-n] [-m error_level] [-r {0 | 1}]
[-i input_file] [-o output_file] [-p]
[-b] [-u] [-R] [-O]
]
参数-?
显示 osql 开关的语法摘要。
-L
列出在本地配置的服务器和在网络上广播的服务器的名称。
-U login_id
用户登录 ID。登录 ID 区分大小写。
-P password
是用户指定的密码。如果未使用 -P 选项，osql 将提示输入密码。如果在命令提示符的末尾使用 -P 选项而不带密码，osql 使用默认密码 (NULL)。密码区分大小写。
OSQLPASSWORD 环境变量使您得以为当前会话设置默认密码。因此，不需要通过硬编码来在批处理文件中设置密码。
如果没有为 -P 选项指定密码，osql 首先检查 OSQLPASSWORD 变量。如果未设置值，osql 使用默认密码 (NULL)。以下示例在命令提示符处设置 OSQLPASSWORD 变量，然后访问 osql 实用工具：
C:\> SET OSQLPASSWORD=abracadabra
C:\> osql
-E
使用信任连接而不请求密码。
-S server_name[\instance_name]
指定要连接的 Microsoft® SQL Server™ 2000 实例。在该服务器上指定 server_name 以连接到 SQL Server 的默认实例。在该服务器上指定 server_name\instance_name 以连接到一个已命名的 SQL Server 2000 的实例。如果未指定服务器，osql 将连接到本地计算机上的 SQL Server 默认实例。从网络上的远程计算机执行 osql 时，此选项是必需的。
-H wksta_name
是工作站名称。工作站名称存储在 sysprocesses.hostname 中并由 sp_who 显示。如果未指定此选项，则采用当前计算机名称。
-d db_name
启动 osql 时发出一个 USE db_name 语句。
-l time_out
指定 osql 登录超时之前的秒数。登录到 osql 的默认超时为 8 秒。
-t time_out
指定命令超时之前的秒数。如果未指定 time_out 值，则命令不会超时。
-h headers
指定要在列标题之间打印的行数。默认为每一查询结果集打印一次标题。用 –1 指定不打印标题。如果使用 -1，则在参数和设置之间一定不能有空格（可以是 -h-1，不能是 -h –1）。
-s col_separator
指定列分隔符字符，其默认为空格。若要使用对操作系统有特殊含义的字符（例如 | ; & < > ），请将该字符用双引号 ( ") 引起来。
-w column_width
允许用户设置屏幕输出的宽度。默认为 80 个字符。当输出行达到其最大屏幕宽度时，会拆分为多个行。
-a packet_size
使您得以请求不同大小的数据包。packet_size 的有效值在 512 到 65535 之间。osql 的默认值为服务器的默认值。数据包大小的增加可以提高较大脚本执行的性能，在这种执行中 GO 命令之间 SQL 语句的数量很重要。Microsoft 的测试表明 8192 是大容量复制操作典型的最快设置。可以请求更大的数据包大小，但如果请求不能得到批准，则 osql 默认为服务器的默认值。
-e
回显输入。
-I
设置 QUOTED_IDENTIFIER 连接选项为开启。
-D data_source_name
连接到用 Microsoft SQL Server 的 ODBC 驱动程序定义的 ODBC 数据源。osql 连接使用该数据源中指定的选项。
说明 该选项不适用于其它驱动程序定义的数据源。
-c cmd_end
指定命令终止符。默认情况下，通过单独在一行中输入 GO 来终止命令并将其发送到 SQL Server 2000。在重置命令终止符时，不要使用对操作系统有特殊含义的 Transact-SQL 保留字或字符，无论其前面是否有反斜杠。
-q "query "
启动 osql 时执行查询，但是在查询完成时不退出 osql。（注意查询语句不应包含 GO）。如果从批处理文件中发出查询，请使用 %variables 或环境 %variables%。例如：
SET table = sysobjects
osql /q "Select * from %table% "
将查询用双引号引起来，将查询中嵌入的任何内容用单引号引起来。
-Q "query "
执行查询并立即退出 osql。将查询用双引号引起来，将查询中嵌入的任何内容用单引号引起来。
-n
从输入行中删除编号和提示符号 (> )。
-m error_level
自定义错误信息的显示。显示指定的或更高严重级别错误的消息数、状态和错误级别。不显示严重级别低于指定级别的错误的任何信息。用 -1 指定与消息一起返回所有标题，即使是信息类的消息。如果用 –1，则在参数和设置之间不能有空格（可以是 -m-1，不能是 -m -1）。
-r {0 | 1}
将消息输出重定向到屏幕 (stderr)。如果未指定参数，或指定参数为 0，则仅重定向严重级别为 17 或更高的错误信息。如果指定参数为 1，则将重定向所有消息输出（包括 "print "）。
-i input_file
标识包含一批 SQL 语句或存储过程的文件。小于 ( <) 比较运算符可以用来代替 –i。
-o output_file
标识从 osql 接收输出的文件。大于 (> ) 比较运算符可以用来代替 –o。
如果 input_file 不是 Unicode 并且没有指定 -u，则 output_file 将存储为 OEM 格式。如果 input_file 是 Unicode 或者指定了 -u，则 output_file 将存储为 Unicode 格式。
-p
打印性能统计。
-b
指定发生错误时 osql 退出并返回一个 DOS ERRORLEVEL 值。当 SQL Server 错误信息的严重级别为 10 或更高时，返回给 DOS ERRORLEVEL 变量的值为 1；否则返回 0。Microsoft MS-DOS® 批处理文件可以测试 DOS ERRORLEVEL 的值并适当处理错误。
-u
指定 output_file 存储为 Unicode 格式，而不管 input_file 为何种格式。
-R
指定在将货币、日期和时间数据转换为字符数据时 SQL Server ODBC 驱动程序使用客户端设置。
-O
为与 isql 的早期版本行为匹配，指定停用某些 osql 功能。下列功能停用：
EOF 批处理
控制台宽度自动调整
宽信息
同时还将 DOS ERRORLEVEL 的默认值设置为 –1。
注释
osql 实用工具从操作系统直接启动，并且使用本文中列出的区分大小写的选项。启动后，osql 接受 SQL 语句并将它们交互地发送到 SQL Server。结果被格式化并显示在屏幕上 (stdout)。可使用 QUIT 或 EXIT 退出 osql。
如果启动 osql 时未指定用户名，SQL Server 2000 将检查环境变量并使用它们，例如 osqluser=(user) 或 osqlserver=(server)。如果未设置环境变量，则使用工作站用户名。如果未指定服务器，则使用工作站名称。
如果 -U 或 -P 选项都没有使用，则 SQL Server 2000 将尝试使用 Windows 身份验证模式进行连接。身份验证基于运行 osql 的 Microsoft Windows NT® 用户帐户。交互性使用 osql
若要交互性使用 osql，请在命令提示符处键入 osql 命令（以及任何选项）。
可以通过键入类似下行的命令，在包含由 osql 执行的查询的文件（例如 Stores.qry）中进行读取：
osql /U alma /P /i stores.qry
该文件必须包含命令终止符。
可以通过键入类似下行的命令，在包含查询的文件（例如 Titles.qry）中进行读取并将结果导向另一文件：
osql /U alma /P /i titles.qry /o titles.res
交互性使用 osql 时，为把操作系统文件读入到命令缓冲区，可使用：r file_name。不要在文件中包含命令终止符；在完成编辑后交互输入终止符。
本帖最后由 liyihongcug 于 2011-08-25 10:35 编辑
http://msdn.microsoft.com/en-us/library/ms188929.aspx
http://www.programbbs.com/doc/1216.htm
http://zh-cn.w3support.net/index.php?db=so&id=919971
sqlserver没有mysql的枚举类型 用函数来实现
create function enumDBName
(@dbName int)
returns varchar(20)
as
begin
if @dbName is null
return null
declare @temp as varchar(20)
set @temp=case @dbName
when 0 then 'cm1'
when 1 then 'cm2'
when 3 then 'cm3'
end
return @temp
end
使用select id, dbo.enumDBName(IsCurrent) from dbo.table
SET XACT_ABORT { ON | OFF }
备注
当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。
当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。
编译错误（如语法错误）不受 SET XACT_ABORT 的影响。
对于大多数 OLE DB 提供程序（包括 SQL Server），必须将隐式或显示事务中的数据修改语句中的 XACT_ABORT 设置为 ON。唯一不需要该选项的情况是在提供程序支持嵌套事务时。有关详细信息，请参阅分布式查询和分布式事务。
SET XACT_ABORT 的设置是在执行或运行时设置，而不是在分析时设置。
示例
下列代码示例导致在含有其他 Transact-SQL 语句的事务中发生外键冲突错误。在第一个语句集中产生错误，但其他语句均成功执行且事务成功提交。在第二个语句集中，将 SET XACT_ABORT 设置为 ON。这导致语句错误使批处理终止，并使事务回滚。
复制代码
USE AdventureWorks;
GO
IF OBJECT_ID(N't2', N'U') IS NOT NULL
DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
DROP TABLE t1;
GO
CREATE TABLE t1
(a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
(a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
-- SELECT shows only keys 1 and 3 added.
-- Key 2 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.
-- Key 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
SELECT *
FROM t2;
GO
SET XACT_ABORT { ON | OFF }
备注
当 SET XACT_ABORT 为 ON 时，如果执行 Transact-SQL 语句产生运行时错误，则整个事务将终止并回滚。
当 SET XACT_ABORT 为 OFF 时，有时只回滚产生错误的 Transact-SQL 语句，而事务将继续进行处理。如果错误很严重，那么即使 SET XACT_ABORT 为 OFF，也可能回滚整个事务。
编译错误（如语法错误）不受 SET XACT_ABORT 的影响。
对于大多数 OLE DB 提供程序（包括 SQL Server），必须将隐式或显示事务中的数据修改语句中的 XACT_ABORT 设置为 ON。唯一不需要该选项的情况是在提供程序支持嵌套事务时。有关详细信息，请参阅分布式查询和分布式事务。
SET XACT_ABORT 的设置是在执行或运行时设置，而不是在分析时设置。
示例
下列代码示例导致在含有其他 Transact-SQL 语句的事务中发生外键冲突错误。在第一个语句集中产生错误，但其他语句均成功执行且事务成功提交。在第二个语句集中，将 SET XACT_ABORT 设置为 ON。这导致语句错误使批处理终止，并使事务回滚。
复制代码
USE AdventureWorks;
GO
IF OBJECT_ID(N't2', N'U') IS NOT NULL
DROP TABLE t2;
GO
IF OBJECT_ID(N't1', N'U') IS NOT NULL
DROP TABLE t1;
GO
CREATE TABLE t1
(a INT NOT NULL PRIMARY KEY);
CREATE TABLE t2
(a INT NOT NULL REFERENCES t1(a));
GO
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (6);
GO
SET XACT_ABORT OFF;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (1);
INSERT INTO t2 VALUES (2); -- Foreign key error.
INSERT INTO t2 VALUES (3);
COMMIT TRANSACTION;
GO
SET XACT_ABORT ON;
GO
BEGIN TRANSACTION;
INSERT INTO t2 VALUES (4);
INSERT INTO t2 VALUES (5); -- Foreign key error.
INSERT INTO t2 VALUES (6);
COMMIT TRANSACTION;
GO
-- SELECT shows only keys 1 and 3 added.
-- Key 2 insert failed and was rolled back, but
-- XACT_ABORT was OFF and rest of transaction
-- succeeded.
-- Key 5 insert error with XACT_ABORT ON caused
-- all of the second transaction to roll back.
SELECT *
FROM t2;
GO
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/cxzhq2002/archive/2009/07/31/4396558.aspx
SET XACT_ABORT on
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
第一个，如果事务中发生错误，on 则会终止整个事务的执行，如果OFF，继续错误的下面一句
第二个用于和NULL的比较，如：null=null在off时会返回 true,在on 时为false ET NOCOUNT
使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。
语法
SET NOCOUNT { ON | OFF }
注释
当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数。
即使当 SET NOCOUNT 为 ON 时，也更新 @@ROWCOUNT 函数。
当 SET NOCOUNT 为 ON 时，将不给客户端发送存储过程中的每个语句的 DONE_IN_PROC 信息。当使用 Microsoft® SQL Server™ 提供的实用工具执行查询时，在 Transact-SQL 语句（如 SELECT、INSERT、UPDATE 和 DELETE）结束时将不会在查询结果中显示 "nn rows affected "。
如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。
SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。
本帖最后由 liyihongcug 于 2011-08-26 13:52 编辑
http://1000copy.iteye.com/blog/690906
Exec dbo.sp_send_dbmail @profile_name='Alerts via Betbrain mail server', @recipients='lh@hhh.com', @subject='test', @body='100.0.4.18test'
字符串换为datetime(使用datetime 千万不要哦用datestamptime
) as a1
),2,2)+':'+RIGHT(a.[run_time],2) as a2
)+' '+ LEFT(a.[run_time],2)+':'+SUBSTRING(cast(a.[run_time] as char(
),2,2)+':'+RIGHT(a.[run_time],2)
否则反复出现 Msg 273, Level 16, State 1, Line 1 不能将显式值插入时间戳列。
) datetime--》string(用convert)
insert into dbo.a(id,mydate,mytime,myall)
SELECT TOP 10 a.[instance_id]
,cast(a.[run_date] as CHAR(
,LEFT(a.[run_time],2)+':'+SUBSTRING(cast(a.[run_time] as char(
,cast(a.[run_date] as CHAR(
FROM [msdb].[dbo].[sysjobhistory] a,
[msdb].[dbo].[sysjobs] b
where
a.[job_id] =b.[job_id]
and b.[name]='LSResjobname'
and a.[run_status]=0
--创建函数 IF OBJECT_ID('f_formatdate') > 0 DROP FUNCTION f_formatdate GO CREATE FUNCTION f_formatdate( @value VARCHAR(20), @type SMALLINT )RETURNS DATETIME AS BEGIN DECLARE @r DATETIME IF @type=1 BEGIN SET @value=REPLACE(REPLACE(@value,'-','.'),'月','') SET @r=PARSENAME(@value,1)+'-'+PARSENAME(@value,2)+'-'+PARSENAME(@value,3) END ELSE SET @r=STUFF(@value,3,0,':') RETURN @r END GO --测试效果 declare @a nvarchar(20),@b nvarchar(20) select @a=N'31-5月-11' ,@b='0830' DECLARE @t TABLE(d DATETIME) INSERT @t SELECT dbo.f_formatdate(@a,1)+dbo.f_formatdate(@b,2) SELECT * FROM @t /* d ----------------------- 2011-05-31 08:30:00.000 (1 行受影响) */
MySql与SqlServer的一些常用用法的差别
由于工作的原因：上家公司的数据库全采用MySql，所以不得不用它。因此也学到了MySql的一些知识，但考虑到今后可能没机会使用了，所以想趁现在离职在家休息，打算把这些东西整理一下，也为了万一今后能用上，留个参考的资源。考虑到一直在使用SqlServer，所以就打算直接与 SqlServer对比来写。
本文将主要列出MySql与SqlServer不同的地方，且以常用的存储过程的相关内容为主。
1. 标识符限定符
SqlServer []
MySql ``
2. 字符串相加
SqlServer 直接用 +
MySql concat()
3. isnull()
SqlServer isnull()
MySql ifnull()
注意：MySql也有isnull()函数，但意义不一样
4. getdate()
SqlServer getdate()
MySql now()
5. newid()
SqlServer newid()
MySql uuid()
6. @@ROWCOUNT
SqlServer @@ROWCOUNT
MySql row_count()
注意：MySql的这个函数仅对于update, insert, delete有效
7. SCOPE_IDENTITY()
SqlServer SCOPE_IDENTITY()
MySql last_insert_id()
8. if ... else ...
SqlServer
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]
-- 若要定义语句块，请使用控制流关键字 BEGIN 和 END。
MySql
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
注意：对于MySql来说，then, end if是必须的。类似的还有其它的流程控制语句，这里就不一一列出。
9. declare
其实，SqlServer和MySql都有这个语句，用于定义变量，但差别在于：在MySql中，DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。这个要求在写游标时，会感觉很BT.
10. 游标的写法
SqlServer
declare @tempShoppingCart table (ProductId int, Quantity int)
insert into @tempShoppingCart (ProductId, Quantity)
select ProductId, Quantity from ShoppingCart where UserGuid = @UserGuid
declare @productId int
declare @quantity int
declare tempCartCursor cursor for
select ProductId, Quantity from @tempShoppingCart
open tempCartCursor
fetch next from tempCartCursor into @productId, @quantity
while @@FETCH_STATUS = 0
begin
update Product set SellCount = SellCount + @quantity where productId = @productId
fetch next from tempCartCursor into @productId, @quantity
end
close tempCartCursor
deallocate tempCartCursor
MySql
declare m_done int default 0;
declare m_sectionId int;
declare m_newsId int;
declare _cursor_SN cursor for select sectionid, newsid from _temp_SN;
declare continue handler for not found set m_done = 1;
create temporary table _temp_SN
select sectionid, newsid from SectionNews group by sectionid, newsid having count(*) > 1;
open _cursor_SN;
while( m_done = 0 ) do
fetch _cursor_SN into m_sectionId, m_newsId;
if( m_done = 0 ) then
-- 具体的处理逻辑
end if;
end while;
close _cursor_SN;
drop table _temp_SN;
注意：为了提高性能，通常在表变量上打开游标，不要直接在数据表上打开游标。
11. 分页的处理
SqlServer
create procedure GetProductByCategoryId(
@CategoryID int,
@PageIndex int = 0,
@PageSize int = 20,
@TotalRecords int output
)
as
begin
declare @ResultTable table
(
RowIndex int,
ProductID int,
ProductName nvarchar(50),
CategoryID int,
Unit nvarchar(10),
UnitPrice money,
Quantity int
);
insert into @ResultTable
select row_number() over (order by ProductID asc) as RowIndex,
p.ProductID, p.ProductName, p.CategoryID, p.Unit, p.UnitPrice, p.Quantity
from Products as p
where CategoryID = @CategoryID;
select @TotalRecords = count(*) from @ResultTable;
select *
from @ResultTable
where RowIndex > (@PageSize * @PageIndex) and RowIndex <= (@PageSize * (@PageIndex+1));
end;
当然，SqlServer中并不只有这一种写法，只是这种写法是比较常见而已。
MySql
create procedure GetProductsByCategoryId(
in _categoryId int,
in _pageIndex int,
in _pageSize int,
out _totalRecCount int
)
begin
set @categoryId = _categoryId;
set @startRow = _pageIndex * _pageSize;
set @pageSize = _pageSize;
prepare PageSql from
'select sql_calc_found_rows * from product where categoryId = ? order by ProductId desc limit ?, ?';
execute PageSql using @categoryId, @startRow, @pageSize;
deallocate prepare PageSql;
set _totalRecCount = found_rows();
end
MySql与SqlServer的差别实在太多，以上只是列出了本人认为经常在写存储过程中会遇到的一些具体的差别之处。
去年本人将一些MySql的常用函数作了一番整理
http://www.cnblogs.com/fish-li/archive/2011/04/05/2006107.html
谢谢分享。
如何修改sql server 2005数据库系统日志保存路径
把sql server 2005装在了C盘，怎么把数据库的系统日志文件保存到其他盘。
问题补充 2009-09-25 15:25
是“数据库系统日志”，也就是事务日志。
问题补充 2009-09-27 09:27
step 1: 日志模式设为简单,并将自动输增长改为每次增长 200M左右。
step 2:重启sql 服务可以恢复tempdb原始大小
step 3:移动tempdb到d:\
use tempdb;
go
alter database tempdb
modify file (name = tempdev,filename='E:\sqldata\tempdbmodified.mdf');
go
alter database tempdb
modify file (name = templog,filename='E:\sqldata\templogmodified.ldf');
go
step 4:别外看来你的系统临时数据生成的比较大，可以增加多个数据文件，可以放在不同的Raid盘上,并且的一定将空间预先手动扩到需要的大小。
step 5:数据库安装很忌讳将系统数据库安装在跟操作系统一个物理盘下,看来你的master等数据库也在C盘下。
我原来的sqlserver2000的日志文件（transaction logs)路径设成了和data files同样的盘符中，我现在想修改到其他盘符去，请问如何修改？
我原来的sqlserver2000的日志文件（transaction logs)路径设成了和data files同样的盘符中，我现在想修改到其他盘符去，请问如何修改?
=========================================================
1、所有用户退出系统，关闭数据库，重新启动数据库
2、detattc h 数据库
3、attach 数据库，但不包括日志
4、在新目录下建立数据库日志
alter database的filename只能修改tempdb数据库文件的物理路径，而且还要重起后才能生效~
用楼上的方法，妙！~
最初由 ub40 发布
[B]我原来的sqlserver2000的日志文件（transaction logs)路径设成了和data files同样的盘符中，我现在想修改到其他盘符去，请问如何修改？ [/B]
问一句，好像采用sp_attach_single_file_db的话，前提是只有一个数据库文件，没有而且同样目录下，要先把日志文件删除。
但是attach以后，系统会自动创建一个日志文件？
这样，至少会有一个日志文件和数据文件在同一个目录。
sp_attach_single_file_db 不是只对一个数据文件的数据库，这个命令相对的是.mdf的数据文件，由主数据文件来识别其他的数据文件，对于只有一个日志文件的数据库，如果在detach的过程中日志丢失，用 sp_attach_single_file_db系统会自动给s数据库增加一个日志文件，如果数据本身是多个日志文件，则方法不同
因此，我认为，考虑为了提供系统性能。将日志文件移动到一个新的硬盘上，最简单的方法应该是：采用备份和恢复。
1 全备份数据库
2 恢复，在选项里面可以修改日志文件的路径，而且，你可以修改数据文件的路径，无论你有多少个文件。
2楼的方法不行呀，我用的attachDB语句：
exec sp_attach_single_file_db @dbname = 'Siemens',
@physname = 'e:\sqlserver\MSSQL\Data\Siemens.MDF'
但出现如下错误：
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Siemens'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'e:\sqlserver\MSSQL\data\Siemens_Log.LDF' may be incorrect.
attach_db需要日志文件的。没有日志文件是不行的。
而且，即使使用sp_attach_single_file_db，也还会在相同的目录下，自动创建一个日志文件。
[B]移动日志文件的最好方法是备份和恢复。attach不是用来解决这类问题的。[/B]
不好意思，方法有一些问题，
先让所有用户退出，分离数据库
1、exec sp_detach_db @dbname = 'database1'
然后，在操作系统级别将log 拷贝到新目录下。再执行
2、EXEC sp_attach_db @dbname = 'database1',
@filename1 = 'd:\data1_Data.mdf',
@filename2 = 'd:\log\data1_log.ldf'
其中 @filename2就是你拷贝的日志文件
已经经过测试 ，没有问题
整个操作不到3分钟，应该是最快的把
１． ＳＴＯＰ ＳＱＬ ＳＥＲＶＥＲ ＳＥＲＶＩＣＥ
２． ＭＯＶＥ ＦＩＬＥＳ
３． ＲＥＡＴＴＡＣＨ ＴＨＥＭ
那移动mdf ndf也用同样步骤么？
自己建立一个小库试验一下把
可以通过该系统表，然后net stop mssqlserver ；move物理文件实现；最后net start mssqlserver
BACKUP DATABASE dbname TO disk = 'd:\dbname.bak'
RESTORE DATABASE dbname FROM disk = 'd:\dbname.bak'
with move 'dbname_DAT' to 'e:\??????????.mdf',
move 'dbname_LOG' to 'f:\????????.ldf'