Ms SQL常见问题收集整理集(不断收集中...)

ccwlm741212

SQLSERVER内部函数汇总:

select @@CONNECTIONS
select @@CURSOR_ROWS
select @@DATEFIRST
SELECT @@DBTS
select @@ERROR
select @@rowcount
select @@FETCH_STATUS
SELECT @@CPU_BUSY AS 'CPU ms', GETDATE() AS 'As of'
sELECT @@IDENTITY AS 'Identity'
sELECT @@IDLE AS 'Idle ms', GETDATE() AS 'As of'
SELECT @@IO_BUSY AS 'IO ms', GETDATE() AS 'As of'
SELECT @@LANGID AS 'Language ID'
SELECT @@LANGUAGE AS 'Language Name'
SELECT @@LOCK_TIMEOUT
SELECT @@MAX_CONNECTIONS
SELECT @@MAX_PRECISION
select @@NESTLEVEL AS 'Inner Level'
select @@OPTIONS
SELECT @@PACKET_ERRORS
SELECT @@PACK_RECEIVED
SELECT @@PACK_SENT
SELECT @@PROCID AS 'ProcID'
SELECT @@REMSERVER
SELECT @@SERVERNAME
SELECT @@SERVICENAME
SELECT @@SPID
SELECT @@TEXTSIZE
SET TEXTSIZE 2048
SELECT @@TEXTSIZE
SELECT @@TIMETICKS
SELECT @@TOTAL_ERRORS AS 'Errors', GETDATE() AS 'As of'
SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'
SELECT @@TOTAL_READ AS 'Reads', @@TOTAL_WRITE AS 'Writes', GETDATE() AS 'As of'
select @@TRANCOUNT
SELECT @@VERSION
select @@FETCH_STATUS

内部变量：

APP_NAME IDENTITY (Function)
CASE ISDATE
CAST and CONVERT ISNULL
COALESCE ISNUMERIC
CURRENT_TIMESTAMP NEWID
CURRENT_USER NULLIF
DATALENGTH PARSENAME
@@ERROR PERMISSIONS
FORMATMESSAGE @@ROWCOUNT
GETANSINULL SESSION_USER
HOST_ID STATS_DATE
HOST_NAME SYSTEM_USER
IDENT_INCR @@TRANCOUNT
IDENT_SEED USER_NAME
@@IDENTITY

ccwlm741212

如何配置SQL Server来做远程备份？
(ASE版本12.0以上)
　　在本地机器上：
　　1.修改interfaces文件,添加远程机器上Backup Server的条目(Backup Server的名称,地址,端口号)
　　　——份服务器名称不能使用别名
　　　——UNIX平台使用dscp实用工具添加
　　　——Windows平台使用dsedit实用工具添加

　　2.在sysservers系统表中添加远程Backup Server名
　　　1>sp_addserver SYB_BACKUP,null,BACKUP_SERVER_NAME
　　　2>go

　　3.使用sp_helpserver查看至少存在以下3个条目:
　　　——本地ASE名称
　　　——本地Backup Server名称
　　　——远程Backup Server名称

　　4.在本地机器上实现远程备份：
　　　1>dump database DATABASE_NAME to "remote_machine_path/..." at REMOTE_BACKUP_SERVER_NAME
　　　2> go
　　　在本地机器上恢复备份:
　　　1>load database DATABASE_NAME from "remote_machine_path/..." at REMOTE_BACKUP_SERVER_NAME
　　　2> go

　　注：
　　1.如果要实现远程的双向备份,请在远程机器上做与本地机器相同的配置.
　　2.本地的Backup Server的名称要求与远程的Backup Server的名称不一样.

ccwlm741212

请教：数据库二层结构是指哪两层？三层是指哪三层？在线等待！  
---------------------------------------------------------------  

两层：c/s    client  server        
三层：两层加中间件即应用服务器  


在基于网络的数据处理中，客户机/服务器（Client/Server）二层计算模式具有里程碑意义。  

什么是三层结构呢？顾名思义，它是将数据处理过程分为三部分：第一层是客户端（用户界面），提供用户与系统的友好访问；第二层是应用服务器，专司业务逻辑的实现;第三层是数据服务器，负责数据信息的存储、访问及其优化。由于业务逻辑被提取到应用服务器，大大降低了客户端负担，因此也称为瘦客户（Thin  Client）结构  
---------------------------------------------------------------  

三层结构，不仅只是数据库。他是只整个应用系统的结构：  
前端浏览层，中间处理层和后端数据库层。  
主要目的是将前端和后端的数据库，用中间层分离开，以保证数据库的安全，并使前端的程序不依赖于某中数据库类型，使前端升级或后端的数据库升级分开，互不影响。  
所以数据库的设计，也是根据应用的需要设计的  




三层多了个馊客户模型,在客户很多时有点用.  
在数据处理速度方面肯定不如典型的C/S模型.  
瘦客户端的优点很多，主要一点是方便维护升级，  
如果需要修改业务逻辑的话，修改中间层业务逻辑就行了，而这一点非常重要  
。因为随着业务的不断发展，业务逻辑会经常变动；同时，因为客户端没有多少计算开销，  
这样客户端的硬件要求也会大大降低，  
这样为数众多的客户机也会省下不少开销  

将表现层和业务逻辑层分离  从而使系统的开发分工明确，同时增加系统的安全性和可移植性

ccwlm741212

怎么通过系统表(而不OBJECTPROPERTY函数)知道触发器的类型?(UPDATE,DELETE,INSERT或它们的组合)  



---------------------------------------------------------------  

SELECT  NAME,CASE  WHEN  
STATUS  &  0X2000<>0  THEN  'Y'  ELSE  NULL  END  AS  [INSTEAD  OF],  
CASE  WHEN  
STATUS  &  0X400<>0  THEN  'Y'  ELSE  NULL  END  AS  [INSERT],  
CASE  WHEN  
STATUS  &  0X100<>0  THEN  'Y'  ELSE  NULL  END  AS  [DELETE],  
CASE  WHEN  
STATUS  &  0X200<>0  THEN  'Y'  ELSE  NULL  END  AS  [UPDATE]  
FROM  SYSOBJECTS  
WHERE  TYPE='TR'

ccwlm741212

SQL  Server利用日志备份恢复数据到时间点的问题  

可能有不少朋友遇到过这样的问题：  
update或delete语句忘带了where子句，或where子句精度不够，执行之后造成了严重的后果，  
这种情况的数据恢复只能利用事务日志的备份来进行，所以如果你的SQL没有进行相应的全库备份  
或不能备份日志（truncate  log  on  checkpoint选项为1），那么就无法进行数据的恢复了，或者  
只能恢复到最近一次的备份的数据了。  

以下简单说明恢复数据方法：  
1，如果误操作之前存在一个全库备份（或已有多个差异备份或增量备份），首先要做的事就是进  
进行一次日志备份（如果为了不让日志文件变大而置trunc.  log  on  chkpt.选项为1那你就死翘了）  
       backup  log  dbName  to  disk='fileName'  
2，恢复一个全库备份，注意需要使用with  norecovery，如果还有其他差异或增量备份，则逐个恢  
复  
       restore  database  dbName  from  disk='fileName'  with  norecovery  
3，恢复最后一个日志备份即刚做的日志备份，指定恢复时间点到误操作之前的时刻  
       restore  log  dbName  from  disk='fileName'  
       with  stopat='date_time'  

以上这些操作都可以在SQL  SERVER企业管理器里完成，难度不大。。。  

当然，如果误操作是一些不记日志的操作比如truncate  table，select  into等操作，那么是无法利  
用上述方法来恢复数据的...  


---------------------------------------------------------------  

如果数据库的主文件被删或被破坏，如果没有做过数据库的备份，就不能够通过日志文件来恢复或者挽救主库的数据。

ccwlm741212

Stored Procedure（存储过程）编写经验和优化措施
作者：s00n


一、前言：在经过一段时间的存储过程开发之后，写下了一些开发时候的小结和经验与大家共享，希望对大家有益，主要是针对Sybase和SQL Server数据库，但其它数据库应该有一些共性。

二、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP（存储过程）的优化的项目开发人员，对数据库有浓厚兴趣的人。

三、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。如果项目的SP较多，书写又没有一定的规范，将会影响以后的系统维护困难和大SP逻辑的难以理解，另外如果数据库的数据量大或者项目对SP的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。

四、　　　　内容：

1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。



2、开发人员在提交SP前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。



3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点：

a)　　　　SQL的使用规范：

　　　　　　　　　　　　 i.　　　　　　　尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。

　　　　　　　　　　　 ii.　　　　　　　尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。

　　　　　　　　　　　iii.　　　　　　　尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

　　　　　　　　　　 iv.　　　　　　　注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。

　　　　　　　　　　　 v.　　　　　　　不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

　　　　　　　　　　 vi.　　　　　　　尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。

　　　　　　　　　　vii.　　　　　　　尽量使用“>=”，不要使用“>”。

　　　　　　　　　viii.　　　　　　　注意一些or子句和union子句之间的替换

　　　　　　　　　　 ix.　　　　　　　注意表之间连接的数据类型，避免不同类型数据之间的连接。

　　　　　　　　　　　 x.　　　　　　　注意存储过程中参数和数据类型的关系。

　　　　　　　　　　 xi.　　　　　　　注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。



b)　　　　索引的使用规范：

　　　　　　　　　　　　 i.　　　　　　　索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。

　　　　　　　　　　　 ii.　　　　　　　尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引

　　　　　　　　　　　iii.　　　　　　　避免对大表查询时进行table scan，必要时考虑新建索引。

　　　　　　　　　　 iv.　　　　　　　在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

　　　　　　　　　　　 v.　　　　　　　要注意索引的维护，周期性重建索引，重新编译存储过程。



c)　　　　tempdb的使用规范：

　　　　　　　　　　　　 i.　　　　　　　尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。

　　　　　　　　　　　 ii.　　　　　　　避免频繁创建和删除临时表，减少系统表资源的消耗。

　　　　　　　　　　　iii.　　　　　　　在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。

　　　　　　　　　　 iv.　　　　　　　如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。

　　　　　　　　　　　 v.　　　　　　　如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。

　　　　　　　　　　 vi.　　　　　　　慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。



d)　　　　合理的算法使用：

根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。


来源：开发者俱乐部

ccwlm741212

如何使Microsoft SQL Server的日志文件不会增大？
作者：怡红公子


如何缩小MSSQL的日志文件已经是一个经常性的问题了，不过这个问题在精华区已经有不少答案了，我这里也不再赘述。
现在我们讨论一下治本的问题，即如何使日志文件不再增大？

先介绍一个简单的方法。
就是把数据库的故障还原模型设置为“简单”(SQL2K)。这样它就会在Checkpoint的时候截断日志。
具体操作方法是：
1、在Enterprise Manager中右键点数据库，“属性|选项|故障还原”，选择“简单”就可以了，如果是SQL7，在“属性|选项”中有一个“trunc. log on chkpt. ”，选中就可以了。
2、如果不想用Enterprise Manager，在Query Analyser或者isql里面执行
EXEC sp_dboption 'your_dbname', 'trunc. log on chkpt.', 'TRUE'
就可以了
但是，要注意的是，这样做了之后，虽然日志不会增大，但是也意味着你一旦出现误操作，将不会有利用日志恢复的机会。(如何利用日志来恢复请参见精华区的FAQ)
所以，绝对不建议在生产数据库上截断日志，除非你有充足的理由和足够的把握，或者……
承担责任的不是你。

既然这种方法不安全，下面我将介绍一种安全的方法。
大家都知道，SQL Server 在完成事务日志备份时将自动截断事务日志中的不活动部分。这些不活动的部分包含已完成的事务，因此在恢复过程中不再使用。相反，事务日志的活动部分包含仍在运行但尚未完成的事务。SQL Server 将重新使用事务日志中这些截断的非活动空间，而不是任由事务日志继续增大并占用更多的空间。
所以，我们备份事务日志就可以使日志文件不再增大了。
但是呢，日志文件一直放着也不是个办法，删除呢，又会失去恢复的可能性。
我们可以结合完全备份来做。做过完全备份之前的事务日志就可以删除了。
比如说，一个备份计划，每天一次完全备份，保留7天内的，每15分钟一次事务日志备份，保留2天的。
用数据库维护计划向导可以很方便的建立备份计划，不过一定要记得设置保留多久的备份哦，否则硬盘空间被备份给占满了就坏事了。


来源：开发者俱乐部

ccwlm741212

如何区分大小写比较？
作者：怡红公子


问：如何在不区分大小写的 SQL Server 7.0 实例上编写能够对用户输入密码进行大小写比较的程序？

答： 如果已经将系统升级为 SQL Server 2000，则可以在列级别指定数据排序规则。（SQL Server 2000 Books Online 词汇表将排序规则定义为“一组确定如何比较、排列和呈现数据的规则。字符数据是使用排序规则存储的，这些规则包括区域设置、排序标准和区分大小写”。）

但是，只有升级到 SQL Server 2000，您才可以使用上述技术。假设存储在表中的密码值为 BamBi2000（注意“B”是大写，其他所有字符都是小写）：

DECLARE @user_password varchar(12)

IF CAST (@user_password AS varbinary(12)) =
CAST ('BamBi2000' AS varbinary(12))
PRINT 'Password match'
ELSE
PRINT 'Password mismatch'

-SQL Server MVPs



来源：开发者俱乐部

ccwlm741212

TOP N 和SET ROWCOUNT N 哪个更快
作者：怡红公子


懒得翻译了，大意：
在有合适的索引的时候，Top n和set rowcount n是一样快的。但是对于一个无序堆来说，top n更快。
原理自己看英文去。

Q. Is using the TOP N clause faster than using SET ROWCOUNT N to return a specific number of rows from a query?

A. With proper indexes, the TOP N clause and SET ROWCOUNT N statement are equally fast, but with unsorted input from a heap, TOP N is faster. With unsorted input, the TOP N operator uses a small internal sorted temporary table in which it replaces only the last row. If the input is nearly sorted, the TOP N engine must delete or insert the last row only a few times. Nearly sorted means you're dealing with a heap with ordered inserts for the initial population and without many updates, deletes, forwarding pointers, and so on afterward.

A nearly sorted heap is more efficient to sort than sorting a huge table. In a test that used TOP N to sort a table with the same number of rows but with unordered inserts, TOP N was not as efficient anymore. Usually, the I/O time is the same both with an index and without; however, without an index SQL Server must do a complete table scan. Processor time and elapsed time show the efficiency of the nearly sorted heap. The I/O time is the same because SQL Server must read all the rows either way.


来源：开发者俱乐部

ccwlm741212

关于DTS导入导出丢失默认值等对象
作者：_Rambo


使用DTS向导在SQL SERVER之间或两个数据库之间进行数据导入导出按默认值配置，则只导入导出表对象以及视图对象，象其他的比如默认值、存储过程等对象可能丢失，如果希望所有的数据库对象都在导入导出之列，请在DTS向导过程中选择“在SQL SERVER数据库复制对象和数据”，接下来就可以选择各个对象进行导入导出了

但要注意，在导的过程中容易出错，可能是由于对象创键的顺序造成的，如果出错建议分多次导，导的顺序可以为：自定义数据类型，表，视图，默认值，约束，自定义函数，存储过程等