澳门金沙vipSqlServer 实用操作小技巧集合第1/2页

包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等
挂起操作
在安装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 SQL
SERVER日志清除的两种方法
在使用过程中大家经常碰到数据库日志非常大的情况,在这里介绍了两种处理方法……
方法一
一般情况下,SQL数据库的收缩并不能很大程度上减小数据库大小,其主要作用是收缩日志大小,应当定期进行此操作以免数据库日志过大
1、设置数据库模式为简单模式:打开SQL企业管理器,在控制台根目录中依次点开Microsoft
SQL Server–SQL
Server组–双击打开你的服务器–双击打开数据库目录–选择你的数据库名称–然后点击右键选择属性–选择选项–在故障还原的模式中选择“简单”,然后按确定保存
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
删除数据库中重复数据的几个方法
数据库的使用过程中由于程序方面的问题有时候会碰到重复数据,重复数据导致了数据库部分设置不能正确设置……
方法一 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
就可以得到无重复记录的结果集。
如果该表需要删除重复的记录,可以按以下方法删除 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不重复的结果集
更改数据库中表的所属用户的两个方法
大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了,原因是建表的时候采用了当时的数据库用户……
–更改某个表 exec sp_澳门金沙vip,changeobjectowner ‘tablename’,’dbo’
–存储更改全部表 CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128), @NewOwner as NVARCHAR(128) AS DECLARE @Name
as NVARCHAR(128) DECLARE @Owner as NVARCHAR(128) DECLARE @OwnerName as
NVARCHAR(128) DECLARE curObject CURSOR FOR select ‘Name’ = name, ‘Owner’
= user_name(uid) from sysobjects where user_name(uid)=@OldOwner order
by name OPEN curObject FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0) BEGIN if @Owner=@OldOwner begin set @OwnerName
= @OldOwner + ‘.’ + rtrim(@Name) exec sp_changeobjectowner @OwnerName,
@NewOwner end — select @name,@NewOwner,@OldOwner FETCH NEXT FROM
curObject INTO @Name, @Owner END close curObject deallocate curObject GO
SQL SERVER中直接循环写入数据 没什么好说的了,大家自己看,有时候有点用处
declare @i int set @i=1 while @i30 begin insert into test (userid)
values(@i) set @i=@i+1 end 无数据库日志文件恢复数据库方法两则
数据库日志文件的误删或别的原因引起数据库日志的损坏 方法一
1.新建一个同名的数据库 2.再停掉sql server(注意不要分离数据库)
3.用原数据库的数据文件覆盖掉这个新建的数据库 4.再重启sql server
5.此时打开企业管理器时会出现置疑,先不管,执行下面的语句 重启SQL
Server服务; 运行以下语句,把应用数据库设置为Single User模式;
运行“sp_dboption ‘XXX’, ‘single user’, ‘true’” 执行结果:
命令已成功完成。 ü 做DBCC CHECKDB; 运行“DBCC CHECKDB(‘XXX’)” 执行结果:
‘XXX’ 的 DBCC 结果。 ‘sysobjects’ 的 DBCC 结果。 对象 ‘sysobjects’ 有
273 行,这些行位于 5 页中。 ‘sysindexes’ 的 DBCC 结果。 对象
‘sysindexes’ 有 202 行,这些行位于 7 页中。 ‘syscolumns’ 的 DBCC 结果。
……… 12阅读全文

三、技巧

1、1=1,1=2的使用,在SQL语句组合时用的较多

“where 1=1” 是表示选择全部    “where 1=2”全部不选,

如:

if

@strWhere !=”

begin

set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘] where
‘ +@strWhere

end

else

begin

set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]’

end

我们可以直接写成

错误!未找到目录项。

set @strSQL = ‘select count(*) as Total from [‘ + @tblName + ‘]
where 1=1 安定 ‘+ @strWhere

2、收缩数据库

–重建索引

DBCC REINDEX DBCC INDEXDEFRAG

–收缩数据和日志

DBCC SHRINKDB DBCC SHRINKFILE

3、压缩数据库

dbccshrinkdatabase(dbname)

4、转移数据库给新用户以已存在用户权限

execsp_change_users_login ‘update_one’,’newname’,’oldname’ go

5、检查备份集

RESTORE VERIFYONLY fromdisk=’E:\dvbbs.bak’

6、修复数据库

ALTER DATABASE [dvbbs]SET SINGLE_USER

GO

DBCC CHECKDB(‘dvbbs’,repair_allow_data_loss) WITH TABLOCK

GO

ALTER DATABASE [dvbbs] SET MULTI_USER

GO

7、日志清除

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 notexpired  AND @OriginalSize = (SELECT size
FROMsysfiles 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
pagesor ‘ +  CONVERT(VARCHAR(30),(size*8/1024)) +’MB’  FROM sysfiles
 WHERE name = @LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF

8、说明:更改某个表

exec sp_changeobjectowner’tablename’,’dbo’

9、存储更改全部表

CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch @OldOwner as
NVARCHAR(128), @NewOwner as NVARCHAR(128) AS

DECLARE @Name    as NVARCHAR(128) DECLARE @Owner   as NVARCHAR(128)
DECLARE @OwnerName   as NVARCHAR(128)

DECLARE curObject CURSOR FOR select ‘Name’    = name,    ‘Owner’    =
user_name(uid) from sysobjects where user_name(uid)=@OldOwner order by
name

OPEN   curObject FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0) BEGIN      if @Owner=@OldOwner begin    set
@OwnerName = @OldOwner + ‘.’ + rtrim(@Name)    exec
sp_changeobjectowner @OwnerName, @NewOwner end — select
@name,@NewOwner,@OldOwner

FETCH NEXT FROM curObject INTO @Name, @Owner END

close curObject deallocate curObject GO

10、SQL SERVER中直接循环写入数据

declare @i int set @i=1 while @i<30 begin    

insert into test (userid) values(@i)    

set @i=@i+1 end

案例:
有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:

    Name     score

    Zhangshan   80

    Lishi       59

    Wangwu      50

    Songquan    69

while((select min(score) from tb_table)<60)

begin

update tb_table set score =score*1.01

where score<60

if (select min(score) from tb_table)>60

 break

 else

   continue

end