sqlserver study

Sqlserver用了很长时间了，觉得很好用啊。

                                                                                              红楼梦魇

 

NOTE 时刻注意，下面很多内容都是sql server的联机文档中有的，要时时注意查文档

基础

sql server对象的开发使用的是Transact-sql语言

如何获得帮助

sql server提供了大量的联机文档，非常完备，非常好用，让人感动

概念

开发

sql server对象的开发

oracle的存储过程和函数等的开发可以使用sqlserver自带的查询分析器就可以了，一修改马上就可以调试，调完了马上就可以改，很好用。

开发中还可以在对象中使用print函数输出，方便调试

典型的sql server对象的开发例子

下面是特意做的一个存储过程的例子：

--this is a example,is show how to

-- .create a procudure whith in and out param

-- .use cursor

-- .use array type

-- .string operate

-- this function select objectid from the table com_basicinfo and use a cursor

-- to store the objectid into a array, at last,scrabbe all the objectid to a string

 

CREATE      PROCEDURE TestProcedure(@param1 int, @param2 varchar(40), @param3 varchar(1024) OUTPUT)  AS

declare

@ary_objectid table (temp_id varchar(128))

declare

@temp_objectid varchar(258),

@loopFlag int ,

@nLen int

 

Declare cur_test Cursor Local For

       Select objectid From com_basicinfo where objecttype = @param1

 

set @loopFlag = 0

set @nLen = 0

set @param3 = ''

 

--put the objectid values to a temp table

Open cur_test

       Fetch Next From cur_test Into @temp_objectid

       While @@FETCH_STATUS = 0

              begin

                     set @loopFlag = @loopFlag + 1

                     insert into @ary_objectid (temp_id) values (@temp_objectid)

                     fetch next from cur_test into @temp_objectid

              end

close cur_test

 

--get the objectid values from the temp table and scrabbe them to a string

declare cur_test2 cursor local for

       select temp_id from @ary_objectid

open cur_test2

       fetch next from cur_test2 into @temp_objectid

       While @@FETCH_STATUS = 0

              begin

                     set @param3 = @param3 + @temp_objectid + '||chr(13)||'

                     fetch next from cur_test2 into @temp_objectid                    

              end 

close cur_test2

GO

对象如何互相引用

这个也很简单啊，比如两个存储过程，pro1和pro2，

pro1要调用pro2，那么要在pro1中加如下代码

exec pro2 @param1,@param2

如果pro2还有一个输出参数，pro1想得到这个参数，要这样用

declare @result

exec pro2 @param1,@param2,@result output

sql server对象开发中如何使用事务

使用TRANSACTION关键字，例子，详情见帮助。

BEGIN TRANSACTION

…….

 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION         

GOTO   EndSave             

QuitWithRollback:

  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Sql server 触发器

触发器是属于表的，开发也甚简单，需要知道的是deleted和inserted分别代表被删除的表和被insert的表，那么update的表呢，则deleted代表被更新前的表，inserted代表被更新后的表

例子如下

CREATE TRIGGER TG_DELETE_BASICINFO ON com_basicinfo

FOR DELETE

AS

declare @objectid varchar(40)

select  @objectid = objectid from deleted

delete com_fulltext where objectid = @objected

（董章震）

sql server代理

什么是sql server代理？

SQLServerAgent 是执行作业、监视 Microsoft SQL Server 及激发警报的Windows NT 4.0 或 Windows 2000 服务。SQLServerAgent 是允许您自动执行某些管理任务的服务。所以必须启动 SQLServerAgent 服务，才能使本地或多服务器的管理任务自动运行。

如何使用sql server代理执行作业

这个功能在我们希望sql server自动某些动作――比如定时执行某些存储过程――时非常有用。

sql server中作业的使用非常的方便，通过sql server的控制台创建一个作业，很多的工作都是由向导来完成的，包括如何让一个任务定时执行，而且从sql server控制台还可以查看作业执行的日志。详情见联机帮助。

2004-1-9 在对字段进行操作时的关于null的问题

在sql server中，有这么一个问题，比如下面一句话

select field01 + field02 + field04 from table1

其中，field0x都是字符串类型，只要field0X中有一个为null，那么结果就为null，这有时候不是我们想要的结果，我们希望当其中一个null时，就忽略它，把剩下的还是拼起来。

Oracle中这是默认的方式，在sql server中如何处理呢。

方法就是不要让上面的sql语句出现

比如field02是null，那么sql语句就得是

select field01 + field04 from table1

要做到这点能，上面的sql语句应该改为

SELECT CASE WHEN field01 IS NULL THEN '' ELSE field26 END + char(13)

      + (CASE WHEN field02 IS NULL THEN '' ELSE field01 END) + char(13)

      + (CASE WHEN field04 IS NULL THEN '' ELSE field03 END)

FROM COB_PROGRAM_NEWS

看，还是很有技巧的吧

（董章震）

如何更新text字段

更新text字段不能用update content set content = convert(varchar, content) + “aaa”这样啊。这样做content字段将更新不正常，真是麻烦。需要用关键字UPDATETEXT来做，下面是例子，具体参数的意义见help：

              DECLARE @ptrval binary(16)

              SELECT @ptrval = TEXTPTR(content)

                 FROM COM_FULLTEXT pr

                    WHERE (OBJECTID = @l_objectid)

                     if(@ptrval is not null)

                     begin

                            UPDATETEXT com_fulltext.content @ptrval NULL 0 @l_mulitValue

                     End

（周海滨）

Transact-SQL

在开发各种sqlserver对象的过程中，免不了要用到transact-sql的各种语法。数据库的语法各各不同，每种数据库都有自己的语法，所以开发数据库对象是个很有趣的过程

常用的Transact-SQL有如下这些

比如赋值，

游标操作，

字符串操作

等等，上面的例子已经用到了一些

常常会查的地方比如函数参考等

其它语法可以查transact-SQL的联机帮助，ms的帮助，很好很好

如何加入特殊字符

使用CHAR，例如，回车符号是char(13)

sqlserver的连字符号是” +”

日期类型

非常简单，比oracle简单，例子：

WHERE (CREATED > '2004-1-1')

WHERE (CREATED > '2004-1-1 10:11:22')

execute

这个函数可以用来执行存储过程或者字符串，执行字符串时必须要使用括号，开始不知道，费了半天劲，真是ft。

执行存储过程：

[ [ EXEC [ UTE ] ]

    {

        [ @return_status = ]

            { procedure_name [ ;number ] | @procedure_name_var

    }

    [ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] ]

        [ ,...n ]

[ WITH RECOMPILE ]

 

执行字符串：

EXEC [ UTE ] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] )

控制流语言

Transact-sql的流程控制语言甚简单实用

只有的只有while和if else，详情见联机文档。

赋值

set比较常用的用法就不说了，它还可以通过查询赋值，很有用，例子

DECLARE @rows int

SET @rows = (SELECT COUNT(*) FROM Customers)

注意，一定要括号。

更新

更新是我们非常常用的，但是有些比较灵活的用法必须掌握，前言万语不如看例子：

例子1：

update test1 set col1 = col1 + col1(col1为varcahr)

例子2：

update test1 set col1 = convert（varchar, col1） + ‘aaaa’(col1为text)

动态游标，动态的表，动态的…

这是个困恼了我一段时间的问题，sql server 到底是否支持动态游标，就像oracle那样，后来终于知道了，支持！！！例子：

use pubs

go

declare @sql varchar(8000)

set @sql = 'declare csr_test cursor for select * from titles'

exec (@sql)

 

open csr_test

fetch next from csr_test

close csr_test

deallocate csr_test

(程序员大本营)

于是，其它的也照此办理吧

set @sql = ' CREATE TABLE #tempTable (valueCount INT)'

exec (@sql)

这不也是动态的吗

sql语句中的通配符

% 包含零个或更多字符的任意字符串。

_ 任何单个字符。

[ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。

[^]不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。

详情见sqlserver的联机帮助。

执行的效率

实践证明，相对于游标的申明，打开，释放等操作，update，insert等普通的数据库操作是开销非常小的。我在实际的工作中写的一个存储过程，开始因为在大循环内部使用了动态游标，对每条记录都有游标的申明和释放结果导致速度奇慢无比，17万条记录，3个小时还执行不完，后来将申明游标放到了大循环的外面，结果用10分钟就执行完了

管理

sql server的全文索引目录存放的规律

默认情况下，ver的全文目录存放在C:/Program Files/Microsoft SQL Server/MSSQL/FTData下面，比如SQL0001000005目录下

有个问题，两个数据库要用ligatal做备份，要求全文索引的目录是一致的，否则一台瘫了，全文索引就不能用了。但是发现在两台机器上建全文索引目录不同，比如另外一台机器是SQL00009005，这个目录是sql server自动生成的，是怎么生成的呢？

牛人周海滨经过分析，发现加粗的部分是和数据库相关的，他很快就在master数据库中的sysdatabases表中发现原来是数据库id，于是问题解决了

至于最后面的5，则是在同一个库中的全文索引的序号，为什么是5打头，就不深究了。

导的库，全文目录用不了，也删除不了

1 选择重建目录，如果可以建的话，就ok了

2 第1步不行，就只有这样了，根据全文目录的索引的属性建一个新的目录，并将一个什么schema.xml文件copy到那个目录下就可以了。

（石国华）

sql server的全文索引的更新

sqlserver全文索引的更新有三种，完全，增量和跟踪更新

使用的sql语句分别是：

exec sp_fulltext_catalog N'ft_dymam', N'start_full'

exec sp_fulltext_catalog N' ft_dymam ', N'start_incremental'

exec sp_fulltext_table N'[dbo].[COM_FULLTEXT]', N'Update_Index'

其中，更踪是针对一个表的，和另外两种不同，而且使用跟踪更新之前，比如运行一下

exec sp_fulltext_table 'com_fulltext',  'start_change_tracking'

增量更新有些限制，如果不满足的话就实际上是完全更新。

而跟踪更新似乎是个不错的选择。

详细情形见联机文档。