fwd: SQL 2000 tips

SQL2000 tip
--地宝原创，转贴请注明出自微软BI开拓者www.windbi.com


1.建立索引
create [UNIQUE or CLUSTERED or NONCLUSTERED] index index_name on table(column,...)

2.建立unique,clustered,nonclustered,primary key 约束
alter table tablename add
constraint constraint_name [unique or clustered or nonclustered or primary key] (conlumn,...)
注：primary key 约束，默认为索引

3.建立Foreign key约束
alter table tablename (with nochek) add
constraint constraint_name foreign key(conlumn,...) references anothertable(conlumn,...)

anothertable(conlumn)为参照的主键

4.建立check约束/default约束
alter table tablename add
constraint constraint_name Check(condition)

alter table tablename add
constraint constraint_name default 'default_value' for conlumn_name

5.删除约束
sp_helpconstraint table_name 查看一个表中的约束情况
alter table table_name drop constraint constraint_name 删除约束

6.删除索引
sp_helpindex table_name 查看表中索引情况
drop index table_name.index_name 删除索引

7.恢复备份
restore database putonrecord from disk= 'f:\putonrecord(完全).dat' with norecovery
restore database putonrecord from disk= 'f:\putonrecord.dat' with recovery
restore database DBname from disk= 'c:\DBname.bak'
WITH recoverty,
MOVE 'OnSouth_Data' TO 'd:\DBname.mdf',
MOVE 'OnSouth_Log' TO 'e:\DBname.ldf'

RESTORE LOG MyNwind FROM MyNwindLog1 WITH NORECOVERY
RESTORE LOG MyNwind FROM MyNwindLog2 WITH RECOVERY, STOPAT = 'Apr 15, 1998 12:00 AM'
--追加备份的恢复方法
restore HEADERONLY from disk='c:\bak\bak.dat' --记录一下position的值
restore database from disk='c:\bak\bak.dat' with file=Position的值

8.察看备份文件信息
restore FILELISTONLY from disk='f:\stat\stat'
restore HEADERONLY from disk='f:\stat\stat'
restore LABELONLY from disk='f:\stat\stat'
restore VERIFYONLY from disk='f:\stat\stat'
--验证备份但不还原备份。检查备份集是否完整以及所有卷是否都可读。但是，RESTORE VERIFYONLY 不尝试验证备份卷中的数据结--构。如果备份有效，则返回"该备份集有效"。

9.导入/导出
BULK INSERT DB..TableName FROM 'd:\trvca.txt' with (fieldterminator='|',rowterminator='|\n')

--Output to XLS
exec master..xp_cmdshell 'bcp gtdjgl.dbo.gtdj out c:\a.xls -c -q -S"(local)" -U"sa" -P"sunny"'

--Output to XLS with queryout
exec master..xp_cmdshell 'bcp "select qymc from sydjgl.dbo.qydj" queryout c:\a.xls -c -q -S"(local)" -U"sa" -P"sunny"'

--Input from XLS
exec master..xp_cmdshell 'bcp gtdjgl.dbo.gtdj in c:\a.xls -c -q -S"(local)" -U"sa" -P"sunny"'

SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\xyz.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...sheetname$

--读取txt文件
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Text;Database=E:\;' , 'SELECT * FROM aa#txt')

--将sql文件导入
osql -U 用户名 -P 密码 -i c:\**1.sql

10.查看索引情况
select table_Name=sysobjects.Name ,
index_Name=sysindexes.Name,
Type=sysobjects.type,
分配索引页=sysindexes.reserved,
使用索引页=sysindexes.used,
叶子层页=sysindexes.Dpages,
非叶子层页=sysindexes.used-sysindexes.Dpages,
rows=sysindexes.rowcnt
from sysindexes left outer join sysobjects on sysindexes.id=sysobjects.id
where sysindexes.indid>0 and sysindexes.indid<255 and sysindexes.status & 64=0
--若发现非叶子层的页数为负数,最好是运行DBCC UPDATEUSAGE ('dbname','tbname','ixname')来更新一下sysindexes的信息

注意indid列的取值
1 = 聚集索引
>1 = 非聚集
255 = 具有 text 或 image 数据的表条目


11.查看表锁情况
if object_id('tempdb..#lock')>0
drop table #lock
go

create table #lock(
spid int,
dbid int,
ObjId int,
IndID int,
Type varchar(10),
Resource varchar(100),
Mode varchar(10),
Status varchar(10)
)

insert into #lock exec sp_lock
select dbName=d.Name,ObjName=o.Name,l.* from master..sysdatabases D,#lock L,sysobjects O
where D.dbid=L.dbid and O.ID=L.ObjID

12.消除孤立用户
exec sp_addlogin 'LoginUser'
use db
go
exec sp_grantdbaccess 'LoginUser','db_User' --在当前数据库中添加帐户(public)
use db
go
exec sp_revokedbaccess 'LoginUser' --从数据库中删除帐户
use db
go
exec sp_grantdbaccess 'LoginUser' --重新为数据库添加账号

或者
use db
go
sp_change_users_login 'Report' --显示存在孤立用户的数据库帐号
sp_change_users_login 'Auto_Fix','sameName' --将数据库帐号='sameName'与登陆帐号='sameName'的建立连接
sp_change_users_login 'Update_One', 'dbUser', 'LoginUser' --将db数据库帐号dbUser与登陆帐号LoginUser建立连接

或者
用dts来导入帐号信息：
[在源数据库上新建一个包
然后将“传输登陆任务”从左边工具栏的 拖到右边框里
在弹出的框中设置相应的登录信息，最后运行包即可]

或者
bcp master..syslogins out localpath\syslogins.dat /N /S current_primary_servername /U sa /P sa_password
EXEC xp_cmdshell 'copy localpath\syslogins.dat destination_share'
EXEC sp_resolve_logins
@dest_db = 'dbname',
@dest_path = 'destination_path',
@filename = 'filename'
GO

注意：使用dts或sp_resolve_logins最后还都是要作一遍sp_change_user_login才能将登陆帐号与db帐号连接起来

可以用sp_helpuser来察看数据库账号情况
可以用sp_helplogins来查看登陆账号情况

13.添加帐号
exec sp_addlogin 'sys_accounts','pass','default db'
use db
go
exec sp_grantdbaccess 'sys_accounts','db_accounts' --添加到当前数据库下
sp_addrolemember 'db_owner','db_accounts' --调整数据库登陆角色为db_owner

或者
(1).exec sp_addlogin 'sys_accounts','pass','default db' --sp_droplogin 为删除登陆帐号
(2).sp_addsrvrolemember 'sys_accounts',sysadmin --调整系统登陆角色为administrator

--更改默认数据库
sp_defaultdb 'sa','newdb'

14.更改数据所有者
use db
go
sp_changedbowner 'newLogin' --将db库dbo默认的系统登陆帐号(sa)更改为新系统登陆帐号newLogin,也就是说newlogin账号有了db数据库的db_owner权限
--说得再白一些，newLogin登陆账号，将拥有db数据库的db_owner权限，但他却不需要在db中添加新的数据库账号（也就是
--说在db数据库中没有newLogin这个帐号，而是将dbo帐号与newLogin系统帐号连接起来了）
--因为，它直接利用dbo这个数据库账号。

use db
go
exec sp_changeobjectowner 'gsscowner.sp_tjbm_sci','dbo' --更改表、存储、试图对象的所有者，'dbo'处可以使用其他的数据库账号，但非系统账号

15.分布连接
SELECT * FROM Opendatasource('SQLOLEDB','Data Source=ServerName;User ID=MyUID;Password=MyPass').Northwind.dbo.Categories

16.查询表使用情况
EXEC sp_MSforeachtable @command1='sp_spaceused ''?''',@command2='sp_mstablespace ''?'''

17.测试T-SQL的执行效率
dbcc DropCleanBuffers --清除缓冲区
dbcc FreeProcCache --清除过程高速缓存

set statistics io on
select count(*) from one --报告查询中包含的每个表所进行的I/O活动信息，包含扫描的行数和读取的次数。
set statistics io off

set statistics time on
select count(*) from one --报告关于CPU和查询的全部执行时间及其查询结果而运行的其他系统过程(如：编译和存储查询的过程)的信息
set statistics time off

set showplan_all on
go
select count(*) from one --以文本的形式报告查询执行的详细计划
go
set showplan_all off

18.整理索引
dbcc showcontig(tbName,ixName) --with ALL_INDEXES

- 扫描页数.....................................: 3 --总的分页数目
- 扫描扩展盘区数...............................: 2 --一个扩展分区相当于8个连续的分页
- 扩展盘区开关数...............................: 1 --当dbcc顺着分页指针浏览整个结构时，扩展分区间切换了的次数。说的白一点就是在 --扫描中一个页与前一页位于不同盘区的次数
- 每个扩展盘区上的平均页数.....................: 1.5--用 扩展分区数(2)/扫描页数(3) 得出的数据
- 扫描密度［最佳值:实际值］....................: 50.00%［1:2］--最佳值：3个分页应该是在一个扩展分区中；实际值：扩展盘区更改的
--实际次数。2的由来实际是由盘区开关数+1得出的。
- 逻辑扫描碎片.................................: 0.00%--Out of order pages/扫描页数(3) .
Out of order pages 含义：本来按照分页的连续性，数据分布的分页应该是
紧紧挨着的，但由于数据的修改、删除而整页的搬移，造成了数据分布的不连
续性，不连续的页就是out of orders pages .
--在这里没有任何一个分页不连续，因此为0
- 扩展盘区扫描碎片.............................: 0.00%--与上面的意思基本一致，仅仅是将分页换成了扩展分区
- 每页上的平均可用字节数.......................: 246.7--代表每页空下来未放数据的字节数（平均值）
- 平均页密度（完整）...........................: 96.95%--代表分页填满的程度（平均值）

backup log logname to disk='D:\log\db.log'

alter database dbName set recovery simple /*set recovery bulk_logged*/set recovery full

dbcc DBReIndex ('db.owner.tbName','ixName','Fill Factor')

--DBCC INDEXDEFRAG (dbName, tbName, ixName)

alter database dbName set recovery full

backup database dbname to disk='D:\db\db.bak'

19.sp_server_info 用来察看数据库系统状态

20.alter database northwind set MULTI_USER /single_user --设置数据库为单用户/多用户访问模式
sp_dboption 'pubs', 'single user' --判断数据库是单用户还是多用户
sp_helpdb --也可判断
SELECT DATABASEPROPERTY('master',IsSingleUser)

21.alter database northwind ... WITH ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
--...为数据库设置操作
--回滚未完成的事务

22.exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','10.50.80.11' --添加连接服务器
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'sa','suntting'
sp_dropserver 'srv_lnk', 'droplogins'--删除登陆信息
sp_helpserver

23.DBCC UPDATEUSAGE/sp_refreshview/sp_recompile/sp_spaceused objectName,true/false
DBCC UPDATEUSAGE --报告和更正 sysindexes 表的不正确内容，可能会导致 sp_spaceused 产生不正确的空间使用报表。
sp_refreshview --解决重建视图的问题，还可以通过重建，发现系统中视图的语法错误。
sp_recompile --使存储过程和触发器在下次运行时重新编译,若调整了表结构，发现存储过程运行很慢，可以用这个来解决
sp_spaceused objectName,true/false --但每次更新表或索引时,sysindexes表中的数据并不立刻更新,因此sp_spaceused输出的结果不能反映表或索引的空间总数,需要加参数 --true强制更新

24.修改默认的数据文件组
ALTER DATABASE dbName MODIFY FILEGROUP dbGroupName/[PRIMARY] DEFAULT

25.如何把20060126转成2006-01-26
select convert(varchar(10),cast('20060126' as datetime),120)

26.随机选出数据
select top 1 * from table order by newid()

27.怎样删除sa
(1).sa可以删除但不建议你删除，其实只要设置好密码不用它既可
删除办法：
先获取修改系统表的权限
sp_configure ’allow updates’, 1
go
RECONFIGURE WITH OVERRIDE
go

update sysxlogins set name='modify' where sid=0x01
update sysxlogins set sid=0x1823BA32F3E6F84EBD5EA43973D810AC where name='modify'

sp_droplogin 'modify'删除刚刚修改的账号即可

28.删除/添加扩展存储过程
(1).exec master..sp_dropextendedproc 'xp_cmdshell' --删除
(2).exec master..sp_addextendedproc xp_cmdshell, 'xplog70.dll'
(3).exec master..sp_helpextendedproc [exprocname]

29.select CAST('Aug 1 1996 12:00AM' AS datetime)出错
设置默认语言即可
SET LANGUAGE us_english
select CAST('Aug 1 1996 12:00AM' AS datetime)

30.强制使用索引
with(index(索引名))

31.跳过被锁定的行
select * from tb with(READPAST)
强制跳过聚集索引的索引页和索引叶节点页（数据页）中行锁定的部分。
select * from test with(FASTFIRSTROW) where c1<>1

32.dts包转移
先打开dts包，选择“另存为”，在位置处选择“结构化存储文件”，将包导出
导入时，右键点“数据转换服务”，选择“打开包”，将刚刚导出的包导入即可

33.更新系统表
Use Master
Go
sp_configure 'allow updates', 1
reconfigure with override
Go

34.获取字节长度
select DATALENGTH('str')

35.使用自定义的索引
select count(*) from equipment with (index(PK_equipment))

36.目前登陆名、数据帐号
select SYSTEM_USER
select user
select suser_sname

37.查询语言支持
SELECT * FROM ::fn_helpcollations() --系统所支持的所有的语言
select DATABASEPROPERTYEX( 'dbname','Collation') --查询当前某库使用的语言
sp_helpsort --查看服务器默认的排序情况
要看表中的排序情况，请参看syscolumns 或者 sp_help 'tbname'

39.DBCC check修复步骤
USE master
go
--EXEC sp_dboption 'equipment', 'single user', 'TRUE'
alter database equipment set single_user with ROLLBACK IMMEDIATE
go
--dbcc checkdb(equipment,REPAIR_REBUILD)
dbcc checkdb(equipment,REPAIR_ALLOW_DATA_LOSS)
go
alter database equipment set MULTI_USER with ROLLBACK IMMEDIATE
go

40.查询磁盘使用空间
EXECUTE master..xp_fixeddrives

41.快速查询表的行数
SELECT @num_rows=rowcnt,@min_indid=indid FROM sysindexes WHERE id=OBJECT_ID(@qualified_table_name)and indid < 2

42.检验两个表是否相同
select sum (convert(numeric, binary_checksum(*/colname) ) ) from tbname

43.获取一个错误的返回值方法
(1).declare @ret int
EXEC @ret = sp_addlinkedserver 'srv_lnk2','','SQLOLEDB','10.50.80.14'
select @ret
(2).用@@error来判断

44.怎么获得所有的odbc数据源名称？
xp_enumdsn

44.获取类似sysobjects的属性
exec sp_tables @table_type="'TABLE'"
exec sp_tables
select * from INFORMATION_SCHEMA.TABLES where table_type='Base TABLE' and table_name<>'dtproperties'

45.sysprocesses的应用
select spid,uid,syslogins.name,login_time,net_address from sysprocesses,syslogins where sysprocesses.sid=syslogins.sid

46.使用索引服务
sp_addlinkedserver filesystem,'indexing service','MSIDXS','doc'
select * from openquery(filesystem,'select directory,filename,docauthor,size,create from scope() where contains(contents,''模块'')')

47.ISNUMERIC 确定表达式是否为一个有效的数字类型。

48.len(trim()) 判断是否为空

49.让自动编号的ID从1开始
truncate table tablename --清表后会自动回到原始
dbcc checkident('tablename',reseed,1) --重新设置为1
SET IDENTITY_INSERT Tablename ON --设置这个选项后就可以insert自定义的id值了

alter table t_b add newcol int identity(1,1) not null

50.汉字排序问题
--按笔划排序
select * from sheet2 order by col1 collate Chinese_PRC_Stroke_CS_AS_KS_WS
Select * From sheet2 Order By col1 Collate Chinese_PRC_Stroke_ci_as
--按拼音排序
select * from sheet2 order by col1 collate Chinese_PRC_CS_AS_KS_WS

SQL基本
In the search string following LIKE
% matches any sequence of characters (possibly zero characters).
_ (underscore) matches any single character.
Any other character matches itself providing no "escape" clause is specified.
An escape clause makes it possible to match the characters % and _ themselves by preceding
them with an "escape" character. For example
WHERE CITY LIKE ’\%%\_A%’ ESCAPE ’\’