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'
增量更新有些限制,如果不满足的话就实际上是完全更新。
而跟踪更新似乎是个不错的选择。
详细情形见联机文档。