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

lunggz

我顶！
不错，要做成CHM文件贴一下，效果会好很多！

泥菩萨

已经down下来慢慢地看。觉得这样一问一答的形式简单明了，学习起来方便多了。
谢谢！

enhydraboy

我也来凑个热闹。

ccwlm741212

如何创建区分大小写的 SQL Server 2000 实例（安装程序）
创建区分大小写的 SQL Server 2000 实例

  1.. 运行 SQL Server 安装程序来安装"SQL Server 2000 组件"，并选择"安装数据
库服务器"，然后在 SQL Server 安装向导的"欢迎"屏幕上单击"下一步"按钮。


  2.. 在"计算机名"对话框中，"本地计算机"是默认选项，本地计算机名显示在编辑框
中。单击"下一步"按钮。


  3.. 在"安装选项"对话框中单击"创建 SQL Server 新实例或安装客户端工具"，然后
单击"下一步"按钮。


  4.. 按照"用户信息"和相关屏幕上的指导进行操作。


  5.. 在"安装定义"对话框中，单击"服务器和客户端工具"选项，然后单击"下一步"按
钮。


  6.. 在"实例名称"对话框中：
    a.. 若要创建区分大小写的默认实例，请接受"默认"复选框并单击"下一步"按钮。


    b.. 若要创建区分大小写的命名实例，请清除"默认"复选框并键入实例名称。
  7.. 在"安装类型"对话框中单击"自定义"选项，然后单击"下一步"按钮。


  8.. 在"选择组件"、"服务帐户"和"身份验证模式"对话框中，更改或接受默认设置，
然后单击"下一步"按钮。


  9.. 在"排列规则设置"对话框中有两个选项：
    a.. 若要使 Windows 区域设置排序规则区分大小写，请选择"排列规则指示器"，
然后从列表中选择正确的排序规则指示器。清除"二进制"复选框，然后选择"区分大小
写"复选框。


    b.. 若要使 SQL 排序规则区分大小写，请选择"SQL 排序规则"，然后键入正确的
排序规则名称。
  有关排序规则选项的更多信息，请单击"帮助"按钮。完成选项的设置后，请单击"下
一步"按钮。

  10.. 在随后出现的各对话框中，更改或接受默认设置，然后单击"下一步"按钮。


  11.. 在完成选项的指定后，请在"开始复制文件"对话框中单击"下一步"按钮。


  12.. 在"选择授权模式"对话框中，按照许可协议进行选择，然后单击"继续"按钮开
始安装。


  13.. 有关授权的信息，请单击"帮助"按钮或向系统管理员进行咨询。

enhydraboy

昨天遇到一个这样的问题。检查了服务器端和客户端都没有问题，防火墙设置也没有问题。排除了网络问题后，检查服务器端，发现这样的现象：
1 SQL Server占用的内存数达到700多兆，服务器物理内存是1G。
2 在服务器本机上，采用named pipe协议可以登陆服务器，而采用tcp协议不能登陆。

因此猜想，可能是内存资源不够，导致采用tcp/ip协议请求，服务器无法创建连接必须的内存。重起SQLServer服务后，正常了。
一般情况下，我们都是让SQL Server自动管理内存的，因此SQL Server会考虑操作系统需要的内存空间。反之，我们可以考虑自己手动设置SQL Server内存管理。

ccwlm741212

使用命名管道访问SQL Server

一、意义
　　SQL Server安装后缺省配置是允许客户端以TCP/IP协议来访问，用户只需要知道SQL Server账号的密码就可以访问，这样很不安全，因为很多WEB程序是直接把连接信息(数据库服务器、连接账号、连接密码)写在程序里面的，破坏者或黑客可以通过某些方法直接查看源代码获取SQL Server账号及密码，他们就可以直接用TCP/IP协议连接到该数据库服务器，对数据或配置进行修改甚至作破坏。使用命名管道时用户必须得具有访问SQL Server所在机器的资源以后才有可能访问SQL Server，如果用户没有访问资源的权限，即便他知道SQL Server账号密也连不上。

二、实现
　　１、服务器配置
　　　　打开SQL Server的Server Network Utility：“开始->程序->Microsoft SQL Server 7.0->Server Network Utility”，在“General”栏中，删除不必要的连接配置，然后按“Add...”增加一个Named Pipes连接：Network librarys选Named Pipes，Server name为服务器的机器名
　　２、客户机配置
　　　　打开SQL Server的Client Network Utility：“开始->程序->Microsoft SQL Server 7.0->Client Network Utility”，在“General”栏中，删除不必要的连接配置，然后按“Add...”增加一个Named Pipes连接：Network librarys选Named Pipes，Server alias为待连接的SQL Server所在服务器的机器名(局域网)或IP地址(广域网)
　　３、连接方法
　　　　可以分为两种情况：
　　　　Ａ、客户机已经登陆到服务器所在的域
　　　　　　客户机已经具有访问服务器资源的权限，可以直接连接SQL Server
　　　　Ｂ、客户机没有登陆到服务器所在的域
　　　　　　客户机不具备访问服务器资源的权限，可以在“开始->运行”中输入\\<服务器机器名或IP地址>，然后输入NT账号和密码，验证通过后客户机就具有访问服务器资源的权限，此时该客户机就可以连接SQL Server了

ccwlm741212

奉献给大家一些难点问题的解决方案：

前段时间费了九牛二虎之力，终于搞掂了SQL mail的配置与使用。 (ZT)
_Rambo要我整理出来有关的文档大家共享。
我就把我配置SQL mail的过程描述一遍。

我用SQL mail主要是要完成这样的功能：
>用户在网上注册后,系统将随机产生的密码发送到用户登记的Email
>用户在论坛的帖子有回复时将内容发送到用户的Email
因为上述过程都是在存储过程中完成的，所以避免了前台程序对参数的
传输处理，也不需要再用第三方的组件完成，感觉比较方便。

1.为了使用SQL mail,首先你的服务器上得有SMTP服务，我没有安装win2000 server自带的SMTP，而是用imail6.04的SMTP,感觉比较稳定，功能也比较强。
2.安装一个邮件系统,我安装了outLook 2000，我发现在配置邮件profile时，如果
不安装outLook而是用别的第三方程序，win2k中文server版在控制面板中就找不到“邮件”一项.
3.安装完outlook后再刷新控制面板,就会找到“邮件”一项，双击进行邮件的配置，为配置文件起一个名字(假设为myProfile)，以便以后SQL mail使用，在该配置文件中设置各项属性。
4.启动outlook(设置为用myProfile作为默认的配置文件),测试进行收发邮件，确认outlook工作正常。
5.用当前的域帐户启动SQL server,在企业管理器的支持服务中，点击SQL mail的属性，可以看到在配置文件选择中，出现了刚才定义的myProfile配置文件(你也可以定义多个profile)，选择这个配置文件进行测试，SQL将返回成功开始和结束一个MAPI会话的信息，如果出现错误或是没有找到邮件配置文件，那一定是你启动SQL server用的帐号有问题
6.现在你就可以在查询分析器中用XP_sendmail这个扩展存储过程发送SQL mail了，格式如下：
xp_sendmail {[@recipients =] 'recipients [;...n]'}
[,][@message =] 'message']
[,][@query =] 'query']
[,][@attachments =] attachments]
[,][@copy_recipients =] 'copy_recipients [;...n]'
[,][@blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,][@subject =] 'subject']
[,[@type =] 'type']
[,][@attach_results =] 'attach_value']
[,][@no_output =] 'output_value']
[,][@no_header =] 'header_value']
[,][@width =] width]
[,][@separator =] 'separator']
[,][@echo_error =] 'echo_value']
[,][@set_user =] 'user']
[,][@dbuse =] 'database']

其中@recipients是必需的

参数说明：

参数 说明
@recipients 收件人，中间用逗号分开
@message 要发送的信息
@query 确定执行并依附邮件的有效查询，除触发器中的插入表及删除表外，此查询能引用任何对象
@attachments 附件
@copy_recipients 抄送
@blind_copy_recipients 密送
@subject 标题
@attach_results 指定查询结果做为附件发送
@no_header 不发送查询结果的列名
@set_user 查询联接的用户名，默认为Guset
@dbuse 查询所用的数据库，默认为缺省数据库


7.不过，如果是在web应用中使用SQL mail,还有一些问题要解决:首先，就是应用程序中连接数据库的帐号，我在网站程序中的数据库连接是使用UDL文件，帐号为DbGuest,这是一个普通帐户，所以还必须在master库的扩展存储过程找到XP_sendmail，并在其属性中增加DbGuest这个用户，并选择EXEC权限。
好了，现在设置完毕，运行网站程序，测试用户注册，几乎没有什么延迟，我测试用的邮箱中就收到了这封SQL mail发出的Email:
"谢谢你的注册，你的用户名是 [心歌],密码是 123456789,建议你首次登录后修改密码"

大功告成!看起来很简单的过程，却折腾了我一个星期!希望你能比我顺利.

不过，现在虽然能用SQLmail发送附件，附加数据库查询结

ccwlm741212

Stored Procedure（存储过程）编写经验和优化措施
一、前言：在经过一段时间的存储过程开发之后，写下了一些开发时候的小结和经验与大家共享，希望对大家有益，主要是针对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

关于SQL Server中几个未公布的访问注册表的扩展存储过程
SQL Server中包含了几个可以访问系统注册表的扩展存储过程.但实际上这几个扩展存储过程是未公开的,从sql server 7.0就有了,
在SQL server 2000中仍然保留,他们以后可能会删除.但是这几个存储过程却提供了在当前的SQL Server版本中访问系统注册表的能力,
而且很多人利用SQL Server来进行攻击系统时,往往都会用到这几个扩展存储过程.所以最好在SQL Server中禁用他们.

xp_regenumvalues 以多个记录集方式返回所有键值
使用方法:
xp_regenumvalues 注册表根键, 子键
比如说,想看看HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Run 的所有键值:
use master
exec xp_regenumvalues 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion\Run'

xp_regread 返回制定键的值
使用方法:
xp_regread 根键,子键,键值名
use master
exec xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion','CommonFilesDir'

xp_regwrite 写入注册表
使用方法:
xp_regwrite 根键,子键, 值名, 值类型, 值
use master
exec xp_regwrite 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion','TestValueName','reg_sz','hello'
注意值类型有2种REG_SZ 表示字符型,REG_DWORD 表示整型

xp_regdeletevalue 删除某个值
使用方法:
xp_regdeletevalue 根键,子键,值名
use master
exec xp_regdeletevalue 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion','TestValueName'

xp_regdeletekey 删除键,包括该键下所有值

使用方法:
use master
xp_regdeletekey 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows\CurrentVersion\Testkey'

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'