蝈蝈俊的共享空间: February, 2009

红俊's profile蝈蝈俊的共享空间PhotosBlogListsMore

Tools

Help

<< First < Previous Next > Last >>

February 27

同时建立主键(PRIMARY KEY)与聚集索引(Clustered Index)的先后顺序

主键(PRIMARY KEY)与聚集索引(Clustered Index)的对比可以参看我之前的博客：SQL Server 索引基础知识(4)----主键与聚集索引

一个表最多一个主键，最多一个聚集索引。

数据库在创建主键同时，会自动建立一个唯一索引。如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引。

所以建立主键(PRIMARY KEY)与聚集索引(Clustered Index)的先后顺序就只有下面几种情况：

为了更方面的描述，假设我们有如下表：

CREATE TABLE [dbo].[User](
	[UserName] [nvarchar](10) NOT NULL,
	[Email] [nvarchar](100) NULL
) ON [PRIMARY]

GO

情况一、在没有任何索引的表上建主键，同时自动产生唯一的聚集索引。

这种情况最简单。在表设计中，通过下图方式设置主键，同时不做任何其他修改，就会在建主键的同时，自动建立了唯一的聚集索引。

同时自动建立的主键和聚集索引

情况二、在没有任何索引的表上先建聚集索引，再建主键。

我们如下面2幅图方式创建索引：

这个索引的参数设置如下图：

这个聚集索引创建好后如下：

这时候，我们再按照情况一的方式在表设计中创建主键。

完成后，主建和索引的情况如下图：

显然，这种情况下在创建主键的同时，建立了一个唯一的非聚集索引。

上面演示情况下，显然 PK_User 这个唯一的非聚集索引没有任何存在的价值。

情况三、在有非聚集索引表（即堆）上，先建主键。

情况三和情况四的初始环境都如下，存在一个非聚集索引。

结果如下，类似于情况一，建主键时自动建立一个唯一的聚集索引。

需要注意的是：

没有聚集索引,有非聚集索引的表非聚集索引的行指针会指向数据堆，聚集索引重建时非聚集索引的行指针又会指回聚集索引的行位置。建聚集索引会导致现有的非聚集索引全部重新组织一边。

情况四，在有非聚集索引的表，先建聚集索引，再建主键。

这时候的结果如下图：

类似于情况二。

其他情况：

其他情况都可以归入上述四类。

比如：在一个同时存在聚集索引，非聚集索引的表上建主键，就可以归入情况二。

参考资料：

SQL Server 索引基础知识(2)----聚集索引，非聚集索引
http://blog.joycode.com/ghj/archive/2008/01/02/113291.aspx

如何修改主键 (Visual Database Tools)
http://technet.microsoft.com/zh-cn/library/ms189251.aspx
http://technet.microsoft.com/en-us/library/ms189251.aspx

5:04 PM | Add a comment | Permalink | Blog it | 数据库相关

February 26

改变 SQL Server Management Studio的模板

我前面一篇文章介绍了SQL Server Management Studio的模板。我们可以很方便的通过模板提高我们编写脚本的效率。这篇文章如下：

SQL Server的模板 http://blog.joycode.com/ghj/archive/2009/02/16/115466.aspx

但是如果我们修改模板时，是在Template Explorer 中修改模板的，则Template Explorer 中的模板变了，但是Object Explorer 中的模板没变。

导致这个问题的原因是 Template Explorer 模板的保存目录和 Object Explorer 中模板使用的目录不是一个目录。

以存储过程的模板为例：

Template Explorer 中修改新建存储过程模板如下：
这时使用的模板是在如下目录：

C:\Users\Administrator\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\Templates\Sql\Stored Procedure

Create Stored Procedure (New Menu).sql 的模板。

Object Explorer 中新建存储过程如下：

这时候新建的存储过程是调用的

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\Stored Procedure

目录下的 Create Stored Procedure (New Menu).sql 模板文件。

这两个创建存储过程使用的模板是2个目录下的不同模板。

我们在制作自己规范的模板时，为了更宜用，应该同时更新这两个目录下的模板。

参考资料：

Changing SQL Server's Management Studio Stored Procedure Template
http://sharpdudes.com/blog/post/2008/08/StoredProcedureTemplate.aspx

How to change default right click CREATE NEW STORED PROCEDURE template in SQL Server 2005
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22054256.html

4:56 PM | Add a comment | Permalink | Blog it | 数据库相关

February 25

SQL Server 2005 配置发送邮件

这个教程是使用SQL Server Management Studio的管理工具完成配置的，其实这个配置也可以通过一系列的SQL语句完成配置。使用SQL语句完成配置的请参看后面文章：http://www.sqlstudy.com/sql_article.php?id=2008072403

配置前说明：

就像我们其他场景发送邮件一样，我们需要配置SMTP服务器地址，账户，密码，邮箱等信息。

在SQL Server2005 中，为了避免某个邮件服务器的问题导致无法发送邮件的问题，SQL Server 2005 引入了 mail profile 这个东东。一个 profile 中可以包含多个 account （邮件帐户），这样，SQL Server 发邮件的时候会依次尝试 profile 中的多个邮件帐户，如果发送成功，则退出，否则，利用下一个邮件帐户发送邮件。

我们需要先完成mail profile 的配置，以及数据库邮箱的一些设置，然后发送邮件就只需要一个简单的SQL语句就搞定了。

配置数据库邮箱功能

如下图所示，在SQL Server Management Studio中启动配置数据库邮件。

我们会依次看到下面的步骤：

在如下的步骤，我们来设置数据库Mail。

下面这一步，我们需要给Mail Profile起个名字，之后发送邮件时会用到。

同时在 SMTP accounts 中加入可用的发送邮件服务器以及帐号，密码

点击 add 可以看到下面的图

根据你的情况，增加发送的帐号。至少一个。

完成邮件帐号配置后，继续下去则是如下图：

Private Profiles 部分的信息如下：

这里是邮件的一些限制设置

这一步是要让我们确认一下我们的设置

点击完成，系统将完成数据库邮件的设置

完成上述步骤，我们就设置好了数据库邮件。

发送邮件的测试

我们可以通过下面地址，发送封测试邮件

就会出现下面的发送测试邮件的窗口，输入要接收者的邮箱地址，点击发送测试邮件，测试邮件就发送出去了。

这时候，SQL 方式发送测试邮件则是如下的SQL 语句

exec msdb..sp_send_dbmail
 @profile_name =  '报表通知邮件配置'          　-- profile 名称
,@recipients   =  'ghj1976@csdn.net'          -- 收件人邮箱
,@subject      =  'SQL Server 2005 Mail Test' -- 邮件标题
,@body         =  'Hello Mail!'               -- 邮件内容
,@body_format  =  'TEXT'                      -- 邮件格式

参考资料：

在 SQL Server 2005/2008 中，使用 Gmail 帐号配置数据库邮件功能
http://space.zdnet.com.cn/html/82/289382-2777798.html

4:07 PM | Add a comment | Permalink | Blog it | 数据库相关

February 24

SQL Server 生成脚本的一些参数说明

我们通过下面菜单，就可以在SQL Server Management Studio中，把我们选择的数据库中每一个对象都产生脚本。这对我们做数据库的版本管理，数据库的升级文档都很方便。

在生成脚本过程中，有很多参数可以选择，合理的配置这些参数，可以让我们很方便的按照我们的期望生成脚本。

生成脚本的一些选项，如下图：

我这里是SQL 2005 的选项， SQL 2008 的选项跟这个稍有差异。

这个差异可以参看下面两篇文章

SQL Server 2005 的生成 SQL Server 脚本向导（“选择脚本选项”页）
http://technet.microsoft.com/zh-cn/library/ms186472(SQL.90).aspx SQL Server 2008 的生成 SQL Server 脚本向导（“选择脚本选项”页）
http://technet.microsoft.com/zh-cn/library/ms186472.aspx

参数说明，其中红色部分是我比较常用的。

常规选项部分：

Ansi Padding
在每个 CREATE TABLE 语句前后添加 SET ANSI_PADDING ON 语句。默认值为 True。

Append to File
将此脚本添加到现有脚本的底部，在“输出选项”页中指定。默认值为 False，指示新脚本将覆盖以前的脚本。

Continue Scripting on Error
True 指示出错时停止编写脚本。值为 False 时，则继续编写脚本。默认值为 False。

Convert UDDTs to Base Types
值为 True 时，用户定义数据类型被转换为用于创建用户定义数据类型的基本数据类型。将运行脚本的数据库中不存在用户定义数据类型时，请使用 True。默认值为 False，指示将使用 UDDT 为用户定义数据类型编写脚本。

Generate Script for Dependent Objects
生成依赖对象的脚本
为在执行所选对象的脚本时必须存在的任何对象生成脚本。默认值为 True。

Include Descriptive Headers
值为 True 时，说明性注释被添加到脚本中，将脚本分成若干个部分，每个对象为一个部分。默认值为 False。

此项我生成时习惯修改为true，这样可以看到注释

Include If NOT EXISTS
值为 True 时，脚本包含一个用于检查对象在数据库中是否已经存在的语句，并在对象已经存在的情况下不尝试创建新对象。默认值为 True。

Include system constraint names
包含系统生成的约束名称，以强制声明性引用完整性。默认值为 False。有

Script Collation
将排序规则信息包含在脚本中。默认值为 False。

参看 http://technet.microsoft.com/zh-cn/library/ms187582(SQL.90).aspx

Script Create
包含每个对象的 CREATE 语句。默认值为 True。

Script Defaults
列存在于原始对象中时包含它们的默认值。默认值为 True。

Script Drop
包含每个对象的 DROP 语句。默认值为 False。

Script Extended Properties
如果对象具有扩展属性，则在脚本中包含扩展属性。默认值为 True。

Script for Server Version
创建可在选定的 SQL Server 版本上运行的脚本。无法为早期版本编写 SQL Server 2005 新增功能的脚本。某些为 SQL Server 2005 创建的脚本无法在运行 SQL Server 较早版本的服务器上运行，也无法在具有较早数据库兼容级别设置的数据库中运行。默认为 SQL Server 2005。

Script Logins
要编写脚本的对象是数据库用户时，请使用“编写登录脚本”选项创建用户所依赖的登录。默认值为 False。

Script Object-Level Permissions
包含对数据库中的对象设置权限的脚本。默认值为 False。

Script Owner
值为 True 时，CREATE 语句包含当前的对象所有者或架构。值为 False 时，CREATE 语句不包含对象所有者或架构，而且脚本创建的对象将对正在执行该脚本的用户使用默认值。默认值为 True。

如果 true，比如存储过程名字就可能是 dbo.*** 如果 false，则没有dbo.这个信息。我习惯于false。

Script Statistics
设置为“编写统计信息脚本”时，将包含 CREATE STATISTICS 语句以重新创建有关对象的统计信息。“编写统计信息和直方图脚本”设置还会创建直方图信息。默认为不编写统计信息脚本。

Script USE DATABASE
将 USE DATABASE 语句添加到脚本中。要确保在正确的数据库中创建数据库对象，请包含 USE DATABASE 语句。如果预计脚本将在其他数据库中使用，请选择 False 以省略 USE DATABASE 语句。默认值为 False。

Script VarDecimal Options
包含脚本的 vardecimal 存储选项。默认值为 False。
参看 http://technet.microsoft.com/zh-cn/library/bb326755(SQL.90).aspx

表和视图部分

Script Check Constraints
将 CHECK 约束添加到脚本中。默认值为 True。CHECK 约束要求输入表中的数据满足某些指定的条件。

Script Foreign Keys
将外键添加到脚本中。默认值为 True。外键可指示和强制保持表间的关系。

Script Full-Text Indexes
包含每个表的全文本索引或脚本的索引视图。默认值为 False。

Script Indexes
添加每个表的聚集索引、非聚集索引和 XML 索引，或添加脚本的索引视图。默认值为 False。

Script Primary Keys
添加对表创建主键脚本。默认值为 True。主键可唯一标识表的每一行。

Script Triggers
添加对表创建触发器脚本。默认值为 True。触发器会在数据被修改时引发操作。

Script Unique Keys
添加对表创建唯一键脚本。唯一键可防止输入重复的数据。默认值为 True。

其他的一些选项大家都很熟悉，就是让我们选择那些数据库对象要生成。忽略不计。

在生成何种格式时，SQL Server 很方便的给我们提供了一些选项，如下：

这里可以方便的选择以 Unicode 或“ANSI 文本”格式将脚本生成到 .sql 文件中。

而且可以所有脚本在一个文件，也可以每个对象的脚本在一个自己的文件。

再后面就是确认生成选项，然后就生成了。

参考资料：

生成 SQL Server 脚本向导的 F1 帮助
http://technet.microsoft.com/zh-cn/library/ms181421.aspx

How to: Generate a Script (SQL Server Management Studio)
http://technet.microsoft.com/en-us/library/ms178078.aspx

How can I automate the “generate scripts” task in SQL Server Management Studio 2008 ?
http://stackoverflow.com/questions/483568/how-can-i-automate-the-generate-scripts-task-in-sql-server-management-studio-2008

Microsoft SQL Server Database Publishing Wizard 1.1
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a

3:53 PM | Add a comment | Permalink | Blog it | 数据库相关

February 16

SQL Server的模板

我们在编写SQL 脚本的时候，有很多通用的地方，这时候模板就可以给我们带来很大的方便。下面就用新建存储过程为例，来说明使用模板的好处，以及如何维护这个模板。

以下提到的特点适用于 SQL Server 2005 和 SQL Server 2008。

模板的好处：

我们在 SQL Server 中，新建一个存储过程，如下方式：

默认情况下，这样我们就会获得一个如下的SQL语句

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO