[TIPS]Some Teach Topics about MSSQL
信息来源:邪恶八进制信息安全团队本主题记录一些MSSQL数据库的使用技巧 TIPS里不准做任何形式的灌水
谢谢合作....
欢迎大家跟帖参与完善：） /*
author:amjn
date:2003-03-28
version:1.0
function:替换text,ntext列中的字符串(不支持中文)
*/
declare @ptr varbinary(16)
declare @amjnId int
declare @Position int,@len int
set @len=datalength('ILOVEHJL')
declare wux_Cursor scroll Cursor
for
select textptr([Name]),[amjnId] from USA201
for read only
open wux_Cursor
fetch next from wux_Cursor into @ptr,@amjnId
while @@fetch_status=0
begin
select @Position=patindex('%ILOVEHJL%',[Name]) from USA201 where [amjnId]=@amjnId
while @Position>0
begin
set @Position=@Position-1
updatetext USA201.[Name] @ptr @Position @len 'i love hjl'
select @Position=patindex('%ILOVEHJL%',[Name]) from USA201 where [amjnId]=@amjnId
end
fetch next from wux_Cursor into @ptr,@amjnId
end
close wux_cursor
deallocate wux_cursor
go IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AccountMessage')
DROP TABLE AccountMessage
GO
CREATE TABLE AccountMessage(FFundCode VARCHAR(6) NOT NULL,FAccName VARCHAR(20) NOT NULL,FAccNum INT NOT NULL);
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AccountBalance')
DROP TABLE AccountBalance
GO
CREATE TABLE AccountBalance(FFundCode VARCHAR(6) NOT NULL,FAccNum INT NOT NULL,FDate DATETIME DEFAULT (getdate()) NOT NULL,FBal NUMERIC(10,2) NOT NULL);
INSERT INTO AccountMessage VALUES('000001','北京存款',1)
INSERT INTO AccountMessage VALUES('000001','上海存款',2)
INSERT INTO AccountMessage VALUES('000001','深圳存款',3)
INSERT INTO AccountMessage VALUES('000002','北京存款',1)
INSERT INTO AccountMessage VALUES('000002','上海存款',2)
INSERT INTO AccountMessage VALUES('000002','天津存款',3)
INSERT INTO AccountMessage VALUES('000003','上海存款',1)
INSERT INTO AccountMessage VALUES('000003','福州存款',2)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000001',1,1000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000001',2,1000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000001',3,1120.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000002',1,2000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000002',2,1000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000002',3,1000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000003',1,2000.00)
INSERT INTO AccountBalance(FDate, FFundCode, FAccNum, FBal) VALUES ('2004-07-28','000003',2,1000.00)
go
两种不同的方法
declare @s nvarchar(4000)
set @s=''select @s=@s+','+quotename(FAccName) +'=isnull(sum(case a.FAccName when '+quotename(FAccName,'''') +' then b.FBal end),0)'from AccountMessage group by FAccNameexec('select 基金代码=a.FFundCode'+@s+'from AccountMessage a,AccountBalance bwhere a.FFundCode=b.FFundCode and a.FAccNum=b.FAccNumgroup by a.FFundCode')
go
select * into #t from(select a.*,b.fbal from AccountMessage a join AccountBalance b on a.ffundcode=b.ffundcode and a.faccnum=b.faccnum) t
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT ffundcode' SELECT @SQL= @SQL+ ',sum(CASE WHEN FAccName = ''' + tt + ''' THEN FBal else 0 END) [' +tt+ ']' FROM (SELECT DISTINCT FAccName as tt FROM #t) A SET @SQL=@SQL+' FROM #t group by ffundcode'
exec (@SQL) <P>数据转换服务（DTS）在数据库管理和开发的多种领域都有会涉及DTS：数据仓库-将数据从原始的处理系统和表格中提取出来以供报表使用<BR><BR><STRONG>建立OLAP<BR></STRONG> 将大量数据从文本文件或其它非数据库格式的文件中拷贝到数据库<BR> 生成Microsoft Office文档报表<BR> 使用 Distributed Transaction Coordinator (DTC)实现多数据库操作<BR> 在客户的桌面程序或网站上，经常需要允许用户按需执行DTS包。在这种情况下，在部署DTS包时，你应该决定将DTS包安置在何处，以及通过何种手段调用它。<BR><BR><STRONG>你的选择<BR></STRONG> 要建立一个按需执行的DTS包，可以有多种选择。下面就对这些选择进行逐一说明。<BR> SQL Server job<BR> 你可以在SQL Server里建立一个job，并调用sp_start_job存储过程。使用sp_start_job的不足之处在于它是一个异步过程。由于它不能返回成功或失败指示，你必须强制使用sp_help_job系统存储过程查询job的结果。除非不关心job调用后的结果，否则异步的job将使桌面程序或Web程序变得很复杂。一个job可以被设置成非管理员（sa）模式，但需要一些额外的步骤。<BR><BR><STRONG>在客户端桌面使用DTS DLLs</STRONG><BR> 第二种方法是用户电脑载入Enterprise Manager或DTS DLLs，在用户的电脑上调用DTS包。虽然用户电脑执行DTS包有一定可行性，但也有不足：必须考虑到升级DTS包带来的分发和安装问题。<BR> 在服务器上使用sp_OA 扩展存储过程<BR> 第三种选择，也就是本文所介绍的核心内容，就是使用sp_OA系统存储过程族并有计划的调用DTS包。这种方案可以有效的避免上两种方案的弊端。<BR> 使用 VBScript调用DTS包 实现一个可以运行DTS包的存储过程的第一步是，编写一段VBScript代码。因为sp_OA存储过程使用起来有些麻烦，因此在利用sp_OA存储过程实现目标之前，要用VBScript编写你希望实现的代码。一般倾向于使用Visual Basic进行简单的脚本开发工作。如图A所示，通过在项目引用窗口中加入DTS包对象库，就可以在脚本中引用DTS包对象了。<BR></P>
<P></P>
<P align=center><A href="http://www.goalercn.com/upload/images/2005112755001.gif" target=_blank><IMG title=点击在新窗口中打开 src="http://www.goalercn.com/upload/images/2005112755001.gif" onload="java script:if(this.width>screen.width-450)this.style.width=screen.width-450;" border=0></A></P>
<P>代码中使用了LoadFromStorageFile函数。一般说，开发工作应该在一个测试环境进行。了解DTS格式的结构化，对将测试产品变为实际产品时很有帮助。<BR><BR><STRONG>Sp_OA 实现<BR></STRONG> 写好了VBScript代码，就可用sp_OA扩展存储过程实现代码。和VBScript类似，sp_OA系统存储过程允许与对象库的COM+ API进行交互。 Sp_OACreate和在VB或VBScript中调用的CreateObject函数类似。Sp_OAGetProperty、sp_OASetProperty以及sp_OAMethod用来连接对象库中的特性和函数。和VB或 VBScript不同的是，sp_OA存储过程导致的COM+错误不会令SQL语句失败，因此必须手动检查每个使用sp_OA的函数是否工作正常。<BR> 同时，很多sp_OA存储过程都会引用参数，因此必须在sp_OA存储过程中的适当参数后加入OUTPUT语句。如果省略了OUTPUT 语句，T-SQL也不会发出警告信息。因此在运行时状态，虽然存储过程运行正常但也不会返回正确值。列表B是一个详细的实现代码。<BR> 解决方案中包括可以重命名DTS包的表格以及实现的过程。其中sp_AdRunDTSPackageOnServer存储过程接收一个ID参数。在继续执行前，程序会从T_AdDTSPackageSetup表中，查找到达DTS包的SQL Server路径。<BR><BR><STRONG>安全性<BR></STRONG> 详细的安全性问题不在本文的讨论范围，这里要说的是一些必须考虑到的基本问题：<BR> 在主数据库的sp_OA扩展系统存储过程中，实现sp_AdRunDTSPackageOnServer存储过程需要EXECUTE权限。为了防止一些恶意用户通过sp_OA过程实现某种目的，可以针对应用程序修改SQL Server规则，以加强安全性。<BR> T-SQL的CURRENT_USER函数对系统安全会有稍许帮助。使用CURRENT_USER和T_AdDTSPackageSetup表格中的区域，可以查询某个用户是否被设为：使用给定的DTS包。<BR> DTS包在SQL Server上执行时，会受到SQL Server Agent服务的帐户设置影响，如果从文件系统中读取ASCII文件，应该确定SQL Server Agent的帐户设置对该文件有通过许可。<BR><BR><STRONG>扩展范例<BR></STRONG> 可以使用sp_OA系统存储过程与其他COM+库进行交互。同时在其他使用ODBC和ActiveX数据对象（ADO）的非SQL Server系统上，sp_OA也可以有效的调用存储过程。一个仅10行左右的VBScript脚本根本没有实用价值，而最后合成的T-SQL代码会变得非常冗长。网上的SQL Server 2000 Books 包括详细的COM+对象库的支持说明，并包含了sp_OA系统存储过程的相关文档。当用户再需要按需运行DTS包时，不妨考虑使用sp_OA系统存储过程来实现。 </P> declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) > 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0
方法二
有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
1、对于第一种重复，比较容易解决，使用
select distinct * from tableName
就可以得到无重复记录的结果集。
如果该表需要删除重复的记录(重复记录保留1条)，可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
最后一个select即得到了Name，Address不重复的结果集(但多了一个autoID字段，实际写时可以写在select子句中省去此列)
如果大家还有好的办法,希望您能将思路或例子与大家分享!! 在使用过程中大家经常碰到数据库日志非常大的情况，在这里介绍了两种处理方法……
方法一
一般情况下，SQL数据库的收缩并不能很大程度上减小数据库大小，其主要作用是收缩日志大小，应当定期进行此操作以免数据库日志过大
1、设置数据库模式为简单模式:打开SQL企业管理器，在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称(如论坛数据库Forum)-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”，然后按确定保存
2、在当前数据库上点右键，看所有任务中的收缩数据库，一般里面的默认设置不用调整，直接点确定
3、收缩数据库完成后，建议将您的数据库属性重新设置为标准模式，操作方法同第一点，因为日志在一些异常情况下往往是恢复数据库的重要依据
方法二
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF 包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等
(一)挂起操作
在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作，要求重启，这里往往重启无用，解决办法:
到HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
删除PendingFileRenameOperations
(二)收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
(三)压缩数据库
dbcc shrinkdatabase(dbname)
(四)转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go
(五)检查备份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
(六)修复数据库
ALTER DATABASE [dvbbs] SET SINGLE_USER
GO
DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [dvbbs] SET MULTI_USER
GO
--CHECKDB 有3个参数:
--REPAIR_ALLOW_DATA_LOSS
-- 执行由 REPAIR_REBUILD 完成的所有修复，包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，以及删除已损坏的文本对象。这些修复可能会导致一些数据丢失。修复操作可以在用户事务下完成以允许用户回滚所做的更改。如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。修复完成后，备份数据库。
--REPAIR_FAST 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。这些修复可以很快完成，并且不会有丢失数据的危险。
--REPAIR_REBUILD 执行由 REPAIR_FAST 完成的所有修复，包括需要较长时间的修复(如重建索引)。执行这些修复时不会有丢失数据的危险。
--DBCC CHECKDB('dvbbs') with NO_INFOMSGS,PHYSICAL_ONLY
页:
[1]