精华内容
下载资源
问答
  • sql实用经验技巧

    千次阅读 2005-12-16 09:51:00
    转自老猫包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 (一)挂起操作在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作,要求重启,...

    转自老猫

    包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 
     (一)挂起操作
    在安装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组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称(如论坛数据库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 


    删除数据库中重复数据的几个方法
    数据库的使用过程中由于程序方面的问题有时候会碰到重复数据,重复数据导致了数据库部分设置不能正确设置…… 

    方法一

    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子句中省去此列)


    更改数据库中表的所属用户的两个方法
    大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了,原因是建表的时候采用了当时的数据库用户…… 


    --更改某个表
    exec sp_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 @i<30
    begin
       insert into test (userid) values(@i)
       set @i=@i+1
    end


    无数据库日志文件恢复数据库方法两则
    数据库日志文件的误删或别的原因引起数据库日志的损坏 


    方法一

    1.新建一个同名的数据库

    2.再停掉sql server(注意不要分离数据库)

    3.用原数据库的数据文件覆盖掉这个新建的数据库

    4.再重启sql server

    5.此时打开企业管理器时会出现置疑,先不管,执行下面的语句(注意修改其中的数据库名)

    6.完成后一般就可以访问数据库中的数据了,这时,数据库本身一般还要问题,解决办法是,利用
    数据库的脚本创建一个新的数据库,并将数据导进去就行了.

    USE MASTER
    GO

    SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
    GO

    UPDATE SYSDATABASES SET STATUS =32768 WHERE NAME='置疑的数据库名'
    Go

    sp_dboption '置疑的数据库名', 'single user', 'true'
    Go

    DBCC CHECKDB('置疑的数据库名') 
    Go

    update sysdatabases set status =28 where name='置疑的数据库名'
    Go

    sp_configure 'allow updates', 0 reconfigure with override
    Go 

    sp_dboption '置疑的数据库名', 'single user', 'false'
    Go

    方法二

    事情的起因
    昨天,系统管理员告诉我,我们一个内部应用数据库所在的磁盘空间不足了。我注意到数据库事件日志文件XXX_Data.ldf文件已经增长到了3GB,于是我决意缩小这个日志文件。经过收缩数据库等操作未果后,我犯了一个自进入行业以来的最大最愚蠢的错误:竟然误删除了这个日志文件!后来我看到所有论及数据库恢复的文章上都说道:“无论如何都要保证数据库日志文件存在,它至关重要”,甚至微软甚至有一篇KB文章讲如何只靠日志文件恢复数据库的。我真是不知道我那时候是怎么想的?!

    这下子坏了!这个数据库连不上了,企业管理器在它的旁边写着“(置疑)”。而且最要命的,这个数据库从来没有备份了。我唯一找得到的是迁移半年前的另外一个数据库服务器,应用倒是能用了,但是少了许多记录、表和存储过程。真希望这只是一场噩梦!

    没有效果的恢复步骤
    附加数据库
    _Rambo讲过被删除日志文件中不存在活动日志时,可以这么做来恢复:

    1,分离被置疑的数据库,可以使用sp_detach_db
    2,附加数据库,可以使用sp_attach_single_file_db

    但是,很遗憾,执行之后,SQL Server质疑数据文件和日志文件不符,所以无法附加数据库数据文件。

    DTS数据导出
    不行,无法读取XXX数据库,DTS Wizard报告说“初始化上下文发生错误”。

    紧急模式
    怡红公子讲过没有日志用于恢复时,可以这么做:

    1,把数据库设置为emergency mode 

    2,重新建立一个log文件

    3,把SQL Server 重新启动一下

    4,把应用数据库设置成单用户模式

    5,做DBCC CHECKDB

    6,如果没有什么大问题就可以把数据库状态改回去了,记得别忘了把系统表的修改选项关掉

     

    我实践了一下,把应用数据库的数据文件移走,重新建立一个同名的数据库XXX,然后停掉SQL服务,把原来的数据文件再覆盖回来。之后,按照怡红公子的步骤走。

    但是,也很遗憾,除了第2步之外,其他步骤执行非常成功。可惜,重启SQL Server之后,这个应用数据库仍然是置疑!

    不过,让我欣慰的是,这么做之后,倒是能够Select数据了,让我大出一口气。只不过,组件使用数据库时,报告说:“发生错误:-2147467259,未能在数据库 'XXX' 中运行 BEGIN TRANSACTION,因为该数据库处于回避恢复模式。”

     

    最终成功恢复的全部步骤
    设置数据库为紧急模式
            停掉SQL Server服务;

           把应用数据库的数据文件XXX_Data.mdf移走;

          重新建立一个同名的数据库XXX;

           停掉SQL服务;

          把原来的数据文件再覆盖回来;

         运行以下语句,把该数据库设置为紧急模式;

        运行“Use Master

    Go

    sp_configure 'allow updates', 1

    reconfigure with override

    Go”

    执行结果:

    DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

    已将配置选项 'allow updates' 从 0 改为 1。请运行 RECONFIGURE 语句以安装。

     

    接着运行“update sysdatabases set status = 32768 where name = 'XXX'”

    执行结果:

    (所影响的行数为 1 行)

     

          重启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 结果。

    ………

     

    ü         运行以下语句把系统表的修改选项关掉;

          运行“sp_resetstatus "XXX"

    go

    sp_configure 'allow updates', 0

    reconfigure with override

    Go”

    执行结果:

    在 sysdatabases 中更新数据库 'XXX' 的条目之前,模式 = 0,状态 = 28(状态 suspect_bit = 0),

    没有更新 sysdatabases 中的任何行,因为已正确地重置了模式和状态。没有错误,未进行任何更改。

    DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

    已将配置选项 'allow updates' 从 1 改为 0。请运行 RECONFIGURE 语句以安装。

     

        重新建立另外一个数据库XXX.Lost;

    DTS导出向导
         运行DTS导出向导;

         复制源选择EmergencyMode的数据库XXX,导入到XXX.Lost;

            选择“在SQL Server数据库之间复制对象和数据”,试了多次,好像不行,只是复制过来了所有表结构,但是没有数据,也没有视图和存储过程,而且DTS向导最后报告复制失败;

           所以最后选择“从源数据库复制表和视图”,但是后来发现,这样总是只能复制一部分表记录;

          于是选择“用一条查询指定要传输的数据”,缺哪个表记录,就导哪个;

            视图和存储过程是执行SQL语句添加的。

     

    维护Sql Server中表的索引
    在使用和创建数据库索引中经常会碰到一些问题,在这里可以采用一些另类的方法解决…

    --第一步:查看是否需要维护,查看扫描密度/Scan Density是否为100%
    declare @table_id int
    set @table_id=object_id('表名')
    dbcc showcontig(@table_id)

    --第二步:重构表索引
    dbcc dbreindex('表名',pk_索引名,100)

    --重做第一步,如发现扫描密度/Scan Density还是小于100%则重构表的所有索引
    --杨铮:并不一定能达100%。
    dbcc dbreindex('表名','',100)


    SQL Server补丁安装常见问题
    谁碰到问题就看看咯:) 

    一、补丁安装过程中常见问题 


    如果在安装补丁的时候遇到如下类似错误: 

    1、安装过程中出现“以前进行的程序创建了挂起的文件操作,运行安装程序前,必须重新启动”,请按照下面步骤解决: 

    a、重启机器,再进行安装,如果发现还有该错误,请按下面步骤 
    b、在开始->运行中输入regedit 
    c、到HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Control/Session Manager 位置 
    d、选择文件->倒出,保存 
    e、在右边窗口右击PendingFileRenameOperations,选择删除,然后确认 
    f、重启安装,问题解决 

    如果还有同样问题,请检查其它注册表中是否有该值存在,如有请删掉。 


    2、在安装SQL Server SP3,有时候会出现:无论用windows认证还是混和认证,都出现密码错误的情况,这时查看临时目录下的sqlsp.out,会发现以下描述: 
    [TCP/IP Sockets]Specified SQL server not found. 
    [TCP/IP Sockets]ConnectionOpen (Connect()). 
    其实这是SQL Server SP3的一个小bug,在安装sp3的时候,没有监听tcp/ip端口,可以按照以下步骤进行: 

    1、打开SQL server客户器网络实用工具和服务器网络工具,确保启用的协议中包含name pipe,并且位置在第一位. 
    2、确保[HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/MSSQLServer/Client/ConnectTo] 
    "DSQUERY"="DBNETLIB". 
    如果没有,请自己建立 
    3、停止mssql. 
    4、进行安装. 

    这样就可以进行正确安装了。

    二、SQL Server补丁版本的检查 

    SQL Server的补丁版本检查不如Windows 补丁版本检查直接,一个系统管理员,如果不了解SQL Server版本对应的补丁号,可能也会遇到一点麻烦,因此在这说明一下,通过这样的办法判别机器是安全的办法,不会对系统产生任何影响。 
    1、用Isql或者SQL查询分析器登录到SQL Server,如果是用Isql,请在cmd窗口输入isql -U sa,然后输入密码,进入;如果是用SQL查询分析器,请从程序中启动,输入sa和密码(也可以用windows验证)。 
    2、在ISQL中输入: 
    Select @@Version; 
    go 

    或者SQL查询分析器中输入(其实如果不想输入,只要打开帮助的关于就可以了:)) 
    Select @@Version; 
    然后按执行; 
    这时会返回SQL的版本信息,如下: 
    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3) 
    其中的8.00.760就是SQL Server的版本和补丁号。对应关系如下: 

    8.00.194 -——————SQL Server 2000 RTM 
    8.00.384 -——————(SP1) 
    8.00.534 -——————(SP2) 
    8.00.760 -——————(SP3) 

    这样我们就能看到SQL Server的正确版本和补丁号了。 

    我们也可以用xp_msver看到更详细的信息

    Sql Server数据库的备份和恢复措施
    最常用的操作,新手们看看…… 

    一、备份数据库 

    1、打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server
    2、SQL Server组-->双击打开你的服务器-->双击打开数据库目录
    3、选择你的数据库名称(如论坛数据库Forum)-->然后点上面菜单中的工具-->选择备份数据库
    4、备份选项选择完全备份,目的中的备份到如果原来有路径和名称则选中名称点删除,然后点添加,如果原来没有路径和名称则直接选择添加,接着指定路径和文件名,指定后点确定返回备份窗口,接着点确定进行备份 

    二、还原数据库

    1、打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server
    2、SQL Server组-->双击打开你的服务器-->点图标栏的新建数据库图标,新建数据库的名字自行取
    3、点击新建好的数据库名称(如论坛数据库Forum)-->然后点上面菜单中的工具-->选择恢复数据库
    4、在弹出来的窗口中的还原选项中选择从设备-->点选择设备-->点添加-->然后选择你的备份文件名-->添加后点确定返回,这时候设备栏应该出现您刚才选择的数据库备份文件名,备份号默认为1(如果您对同一个文件做过多次备份,可以点击备份号旁边的查看内容,在复选框中选择最新的一次备份后点确定)-->然后点击上方常规旁边的选项按钮
    5、在出现的窗口中选择在现有数据库上强制还原,以及在恢复完成状态中选择使数据库可以继续运行但无法还原其它事务日志的选项。在窗口的中间部位的将数据库文件还原为这里要按照你SQL的安装进行设置(也可以指定自己的目录),逻辑文件名不需要改动,移至物理文件名要根据你所恢复的机器情况做改动,如您的SQL数据库装在D:/Program Files/Microsoft SQL Server/MSSQL/Data,那么就按照您恢复机器的目录进行相关改动改动,并且最后的文件名最好改成您当前的数据库名(如原来是bbs_data.mdf,现在的数据库是forum,就改成forum_data.mdf),日志和数据文件都要按照这样的方式做相关的改动(日志的文件名是*_log.ldf结尾的),这里的恢复目录您可以自由设置,前提是该目录必须存在(如您可以指定d:/sqldata/bbs_data.mdf或者d:/sqldata/bbs_log.ldf),否则恢复将报错
    6、修改完成后,点击下面的确定进行恢复,这时会出现一个进度条,提示恢复的进度,恢复完成后系统会自动提示成功,如中间提示报错,请记录下相关的错误内容并询问对SQL操作比较熟悉的人员,一般的错误无非是目录错误或者文件名重复或者文件名错误或者空间不够或者数据库正在使用中的错误,数据库正在使用的错误您可以尝试关闭所有关于SQL窗口然后重新打开进行恢复操作,如果还提示正在使用的错误可以将SQL服务停止然后重起看看,至于上述其它的错误一般都能按照错误内容做相应改动后即可恢复

    三、收缩数据库

    一般情况下,SQL数据库的收缩并不能很大程度上减小数据库大小,其主要作用是收缩日志大小,应当定期进行此操作以免数据库日志过大
    1、设置数据库模式为简单模式:打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称(如论坛数据库Forum)-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”,然后按确定保存
    2、在当前数据库上点右键,看所有任务中的收缩数据库,一般里面的默认设置不用调整,直接点确定
    3、收缩数据库完成后,建议将您的数据库属性重新设置为标准模式,操作方法同第一点,因为日志在一些异常情况下往往是恢复数据库的重要依据 

    四、设定每日自动备份数据库

    强烈建议有条件的用户进行此操作!
    1、打开企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器
    2、然后点上面菜单中的工具-->选择数据库维护计划器
    3、下一步选择要进行自动备份的数据-->下一步更新数据优化信息,这里一般不用做选择-->下一步检查数据完整性,也一般不选择
    4、下一步指定数据库维护计划,默认的是1周备份一次,点击更改选择每天备份后点确定
    5、下一步指定备份的磁盘目录,选择指定目录,如您可以在D盘新建一个目录如:d:/databak,然后在这里选择使用此目录,如果您的数据库比较多最好选择为每个数据库建立子目录,然后选择删除早于多少天前的备份,一般设定4-7天,这看您的具体备份要求,备份文件扩展名一般都是bak就用默认的
    6、下一步指定事务日志备份计划,看您的需要做选择-->下一步要生成的报表,一般不做选择-->下一步维护计划历史记录,最好用默认的选项-->下一步完成
    7、完成后系统很可能会提示Sql Server Agent服务未启动,先点确定完成计划设定,然后找到桌面最右边状态栏中的SQL绿色图标,双击点开,在服务中选择Sql Server Agent,然后点击运行箭头,选上下方的当启动OS时自动启动服务
    8、这个时候数据库计划已经成功的运行了,他将按照您上面的设置进行自动备份 

    修改计划:
    1、打开企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->管理-->数据库维护计划-->打开后可看到你设定的计划,可以进行修改或者删除操作 

    五、数据的转移(新建数据库或转移服务器)

    一般情况下,最好使用备份和还原操作来进行转移数据,在特殊情况下,可以用导入导出的方式进行转移,这里介绍的就是导入导出方式,导入导出方式转移数据一个作用就是可以在收缩数据库无效的情况下用来减小(收缩)数据库的大小,本操作默认为您对SQL的操作有一定的了解,如果对其中的部分操作不理解,可以咨询动网相关人员或者查询网上资料
    1、将原数据库的所有表、存储过程导出成一个SQL文件,导出的时候注意在选项中选择编写索引脚本和编写主键、外键、默认值和检查约束脚本选项
    2、新建数据库,对新建数据库执行第一步中所建立的SQL文件
    3、用SQL的导入导出方式,对新数据库导入原数据库中的所有表内容

    利用数据库日志恢复数据到时间点的操作
    由于不正常的数据丢失,而又不想使用备份数据还原,只要原来有备份且当前日志保存完好,可以采用这个方法试试,说不定可挽回损失…… 

    1,如果误操作之前存在一个全库备份(或已有多个差异备份或增量备份),首先要做的事就是进
    进行一次日志备份(如果为了不让日志文件变大而置trunc. log on chkpt.选项为1那你就死翘了)
        backup log dbName to disk='fileName'
    2,恢复一个全库备份,注意需要使用with norecovery,如果还有其他差异或增量备份,则逐个恢

        restore database dbName from disk='fileName' with norecovery
    3,恢复最后一个日志备份即刚做的日志备份,指定恢复时间点到误操作之前的时刻
        restore log dbName from disk='fileName'
        with stopat='date_time'

    以上这些操作都可以在SQL SERVER企业管理器里完成,难度不大。。。

    当然,如果误操作是一些不记日志的操作比如truncate table,select into等操作,那么是无法利
    用上述方法来恢复数据的...

    SQL Server2000数据库文件损坏时如何恢复
    出现这样的问题是比较严重的了,能否修复只能看你的运气…… 

    SQL Server2000中,如果数据库文件(非系统数据库文件)遇到错误的时候,仅适用于非master,msdb的数据库。

    说明如下:

    1 建一个测试数据库test(数据库类型为完全)
    2 建一个表,插入点记录
      create table a(c1 varchar(2))
      go
      insert into a values('aa')
      go
      insert into a values('bb')
      go
    3 作完全备份,到文件test_1.bak
    4 在作一点修改
      insert into a values('cc')
      go
      create table b(c1 int)
      go
      insert into b values(1)
      go
      insert into b values(2)
      go
    5 shutdown 数据库服务器
    6 用ultraedit编辑数据库文件test_data.mdf,随便修改点字节内容,相当于数据库遭到致命的损坏。
    7 启动数据库,并且运行企业管理器,点开数据库,看到test变成灰色,而且显示置疑。
    8 运行isql -SLocalhost -Usa -P
    1> backup log test TO DISK='D:Program FilesMicrosoft SQL ServerMSSQLBACKUP 
    est_2.bak' WITH NO_TRUNCATE
    2>go
    已处理 2 页,这些页属于数据库 'test' 的文件 'TEST_Log'(位于文件 1 上)。
    BACKUP LOG 操作成功地处理了 2 页,花费了 0.111 秒(0.087 MB/秒)。

    9 进行恢复最老的完全备份
    1> RESTORE DATABASE test FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQL
    BACKUP est_1.bak' WITH NORECOVERY
    2> go
    已处理 96 页,这些页属于数据库 'test' 的文件 'TEST_Data'(位于文件 1 上)。
    已处理 1 页,这些页属于数据库 'test' 的文件 'TEST_Log'(位于文件 1 上)。
    RESTORE DATABASE 操作成功地处理了 97 页,花费了 0.107 秒(7.368 MB/秒)。

    10 恢复最近的日志
    1> RESTORE LOG test FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQLBACKU
    P est_2.bak' WITH RECOVERY
    2> go
    已处理 2 页,这些页属于数据库 'test' 的文件 'TEST_Log'(位于文件 1 上)。
    RESTORE LOG 操作成功地处理了 2 页,花费了 0.056 秒(0.173 MB/秒)。

     


    存储过程编写经验和优化措施
    经验之谈,看看…… 

    一、适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。   

      二、介绍:在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。如果项目的SP较多,书写又没有一定的规范,将会影响以后的系统维护困难和大SP逻辑的难以理解,另外如果数据库的数据量大或者项目对SP的性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。   

      三、内容:   

      1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databse.dbo.table_name”,因为sp_depends不能显示出该SP所使用的跨库table或view,不方便校验。   

      2、开发人员在提交SP前,必须已经使用set showplan on分析过查询计划,做过自身的查询优化检查。   

      3、高程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点:    

      a)SQL的使用规范: 

       i. 尽量避免大事务操作,慎用holdlock子句,提高系统并发能力。 

       ii. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。 

       iii. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。 

       iv. 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。 

       v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。 

       vi. 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。 

       vii. 尽量使用“>=”,不要使用“>”。 

       viii. 注意一些or子句和union子句之间的替换 

       ix. 注意表之间连接的数据类型,避免不同类型数据之间的连接。 

       x. 注意存储过程中参数和数据类型的关系。 

       xi. 注意insert、update操作的数据量,防止与其他应用冲突。如果数据量超过200个数据页面(400k),那么系统将会进行锁升级,页级锁会升级成表级锁。    

      b)索引的使用规范: 

       i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过6个索引。 

       ii. 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引 

       iii. 避免对大表查询时进行table scan,必要时考虑新建索引。 

       iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。 

       v. 要注意索引的维护,周期性重建索引,重新编译存储过程。   

      c)tempdb的使用规范: 

       i. 尽量避免使用distinct、order by、group by、having、join、cumpute,因为这些语句会加重tempdb的负担。 

       ii. 避免频繁创建和删除临时表,减少系统表资源的消耗。 

       iii. 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。 

       iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。 

        v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。 

        vi. 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。   

      d)合理的算法使用:    

      根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令:set statistics io on, set statistics time on , set showplan on 等

    展开全文
  • SQL SERVER实用经验技巧集

    千次阅读 2007-03-02 23:56:00
    Sql Server实用操作小技巧集合包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 (一)挂起操作在安装Sql或sp补丁的时候系统提示之前有挂起的...
      

    Sql Server实用操作小技巧集合
    包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 
     (一)挂起操作
    在安装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组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称(如论坛数据库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



    删除数据库中重复数据的几个方法
    数据库的使用过程中由于程序方面的问题有时候会碰到重复数据,重复数据导致了数据库部分设置不能正确设置……

    方法一

    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子句中省去此列)



    更改数据库中表的所属用户的两个方法
    大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了,原因是建表的时候采用了当时的数据库用户……

    --更改某个表
    exec sp_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 @i<30
    begin
       insert into test (userid) values(@i)
       set @i=@i+1
    end



    无数据库日志文件恢复数据库方法两则
    数据库日志文件的误删或别的原因引起数据库日志的损坏

    方法一

    1.新建一个同名的数据库

    2.再停掉sql server(注意不要分离数据库)

    3.用原数据库的数据文件覆盖掉这个新建的数据库

    4.再重启sql server

    5.此时打开企业管理器时会出现置疑,先不管,执行下面的语句(注意修改其中的数据库名)

    6.完成后一般就可以访问数据库中的数据了,这时,数据库本身一般还要问题,解决办法是,利用
    数据库的脚本创建一个新的数据库,并将数据导进去就行了.

    USE MASTER
    GO

    SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
    GO

    UPDATE SYSDATABASES SET STATUS =32768 WHERE NAME='置疑的数据库名'
    Go

    sp_dboption '置疑的数据库名', 'single user', 'true'
    Go

    DBCC CHECKDB('置疑的数据库名')
    Go

    update sysdatabases set status =28 where name='置疑的数据库名'
    Go

    sp_configure 'allow updates', 0 reconfigure with override
    Go

    sp_dboption '置疑的数据库名', 'single user', 'false'
    Go

    方法二

    事情的起因
    昨天,系统管理员告诉我,我们一个内部应用数据库所在的磁盘空间不足了。我注意到数据库事件日志文件XXX_Data.ldf文件已经增长到了3GB,于是我决意缩小这个日志文件。经过收缩数据库等操作未果后,我犯了一个自进入行业以来的最大最愚蠢的错误:竟然误删除了这个日志文件!后来我看到所有论及数据库恢复的文章上都说道:“无论如何都要保证数据库日志文件存在,它至关重要”,甚至微软甚至有一篇KB文章讲如何只靠日志文件恢复数据库的。我真是不知道我那时候是怎么想的?!

    这下子坏了!这个数据库连不上了,企业管理器在它的旁边写着“(置疑)”。而且最要命的,这个数据库从来没有备份了。我唯一找得到的是迁移半年前的另外一个数据库服务器,应用倒是能用了,但是少了许多记录、表和存储过程。真希望这只是一场噩梦!

    没有效果的恢复步骤
    附加数据库
    _Rambo讲过被删除日志文件中不存在活动日志时,可以这么做来恢复:

    1,分离被置疑的数据库,可以使用sp_detach_db
    2,附加数据库,可以使用sp_attach_single_file_db

    但是,很遗憾,执行之后,SQL Server质疑数据文件和日志文件不符,所以无法附加数据库数据文件。

    DTS数据导出
    不行,无法读取XXX数据库,DTS Wizard报告说“初始化上下文发生错误”。

    紧急模式
    怡红公子讲过没有日志用于恢复时,可以这么做:

    1,把数据库设置为emergency mode

    2,重新建立一个log文件

    3,把SQL Server 重新启动一下

    4,把应用数据库设置成单用户模式

    5,做DBCC CHECKDB

    6,如果没有什么大问题就可以把数据库状态改回去了,记得别忘了把系统表的修改选项关掉

     

    我实践了一下,把应用数据库的数据文件移走,重新建立一个同名的数据库XXX,然后停掉SQL服务,把原来的数据文件再覆盖回来。之后,按照怡红公子的步骤走。

    但是,也很遗憾,除了第2步之外,其他步骤执行非常成功。可惜,重启SQL Server之后,这个应用数据库仍然是置疑!

    不过,让我欣慰的是,这么做之后,倒是能够Select数据了,让我大出一口气。只不过,组件使用数据库时,报告说:“发生错误:-2147467259,未能在数据库 'XXX' 中运行 BEGIN TRANSACTION,因为该数据库处于回避恢复模式。”

     

    最终成功恢复的全部步骤
    设置数据库为紧急模式
            停掉SQL Server服务;

           把应用数据库的数据文件XXX_Data.mdf移走;

          重新建立一个同名的数据库XXX;

           停掉SQL服务;

          把原来的数据文件再覆盖回来;

         运行以下语句,把该数据库设置为紧急模式;

        运行“Use Master

    Go

    sp_configure 'allow updates', 1

    reconfigure with override

    Go”

    执行结果:

    DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

    已将配置选项 'allow updates' 从 0 改为 1。请运行 RECONFIGURE 语句以安装。

     

    接着运行“update sysdatabases set status = 32768 where name = 'XXX'”

    执行结果:

    (所影响的行数为 1 行)

     

          重启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 结果。

    ………

     

    ü         运行以下语句把系统表的修改选项关掉;

          运行“sp_resetstatus "XXX"

    go

    sp_configure 'allow updates', 0

    reconfigure with override

    Go”

    执行结果:

    在 sysdatabases 中更新数据库 'XXX' 的条目之前,模式 = 0,状态 = 28(状态 suspect_bit = 0),

    没有更新 sysdatabases 中的任何行,因为已正确地重置了模式和状态。没有错误,未进行任何更改。

    DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

    已将配置选项 'allow updates' 从 1 改为 0。请运行 RECONFIGURE 语句以安装。

     

        重新建立另外一个数据库XXX.Lost;

    DTS导出向导
         运行DTS导出向导;

         复制源选择EmergencyMode的数据库XXX,导入到XXX.Lost;

            选择“在SQL Server数据库之间复制对象和数据”,试了多次,好像不行,只是复制过来了所有表结构,但是没有数据,也没有视图和存储过程,而且DTS向导最后报告复制失败;

           所以最后选择“从源数据库复制表和视图”,但是后来发现,这样总是只能复制一部分表记录;

          于是选择“用一条查询指定要传输的数据”,缺哪个表记录,就导哪个;

            视图和存储过程是执行SQL语句添加的。

     

    维护Sql Server中表的索引
    在使用和创建数据库索引中经常会碰到一些问题,在这里可以采用一些另类的方法解决…

    --第一步:查看是否需要维护,查看扫描密度/Scan Density是否为100%
    declare @table_id int
    set @table_id=object_id('表名')
    dbcc showcontig(@table_id)

    --第二步:重构表索引
    dbcc dbreindex('表名',pk_索引名,100)

    --重做第一步,如发现扫描密度/Scan Density还是小于100%则重构表的所有索引
    --杨铮:并不一定能达100%。
    dbcc dbreindex('表名','',100)


    SQL Server补丁安装常见问题
    谁碰到问题就看看咯:)

    一、补丁安装过程中常见问题


    如果在安装补丁的时候遇到如下类似错误:

    1、安装过程中出现“以前进行的程序创建了挂起的文件操作,运行安装程序前,必须重新启动”,请按照下面步骤解决:

    a、重启机器,再进行安装,如果发现还有该错误,请按下面步骤
    b、在开始->运行中输入regedit
    c、到HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Control/Session Manager 位置
    d、选择文件->倒出,保存
    e、在右边窗口右击PendingFileRenameOperations,选择删除,然后确认
    f、重启安装,问题解决

    如果还有同样问题,请检查其它注册表中是否有该值存在,如有请删掉。


    2、在安装SQL Server SP3,有时候会出现:无论用windows认证还是混和认证,都出现密码错误的情况,这时查看临时目录下的sqlsp.out,会发现以下描述:
    [TCP/IP Sockets]Specified SQL server not found.
    [TCP/IP Sockets]ConnectionOpen (Connect()).
    其实这是SQL Server SP3的一个小bug,在安装sp3的时候,没有监听tcp/ip端口,可以按照以下步骤进行:

    1、打开SQL server客户器网络实用工具和服务器网络工具,确保启用的协议中包含name pipe,并且位置在第一位.
    2、确保[HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/MSSQLServer/Client/ConnectTo]
    "DSQUERY"="DBNETLIB".
    如果没有,请自己建立
    3、停止mssql.
    4、进行安装.

    这样就可以进行正确安装了。

    二、SQL Server补丁版本的检查

    SQL Server的补丁版本检查不如Windows 补丁版本检查直接,一个系统管理员,如果不了解SQL Server版本对应的补丁号,可能也会遇到一点麻烦,因此在这说明一下,通过这样的办法判别机器是安全的办法,不会对系统产生任何影响。
    1、用Isql或者SQL查询分析器登录到SQL Server,如果是用Isql,请在cmd窗口输入isql -U sa,然后输入密码,进入;如果是用SQL查询分析器,请从程序中启动,输入sa和密码(也可以用windows验证)。
    2、在ISQL中输入:
    Select @@Version;
    go

    或者SQL查询分析器中输入(其实如果不想输入,只要打开帮助的关于就可以了:))
    Select @@Version;
    然后按执行;
    这时会返回SQL的版本信息,如下:
    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
    其中的8.00.760就是SQL Server的版本和补丁号。对应关系如下:

    8.00.194 -——————SQL Server 2000 RTM
    8.00.384 -——————(SP1)
    8.00.534 -——————(SP2)
    8.00.760 -——————(SP3)

    这样我们就能看到SQL Server的正确版本和补丁号了。

    我们也可以用xp_msver看到更详细的信息

    Sql Server数据库的备份和恢复措施
    最常用的操作,新手们看看……

    一、备份数据库

    1、打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server
    2、SQL Server组-->双击打开你的服务器-->双击打开数据库目录
    3、选择你的数据库名称(如论坛数据库Forum)-->然后点上面菜单中的工具-->选择备份数据库
    4、备份选项选择完全备份,目的中的备份到如果原来有路径和名称则选中名称点删除,然后点添加,如果原来没有路径和名称则直接选择添加,接着指定路径和文件名,指定后点确定返回备份窗口,接着点确定进行备份

    二、还原数据库

    1、打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server
    2、SQL Server组-->双击打开你的服务器-->点图标栏的新建数据库图标,新建数据库的名字自行取
    3、点击新建好的数据库名称(如论坛数据库Forum)-->然后点上面菜单中的工具-->选择恢复数据库
    4、在弹出来的窗口中的还原选项中选择从设备-->点选择设备-->点添加-->然后选择你的备份文件名-->添加后点确定返回,这时候设备栏应该出现您刚才选择的数据库备份文件名,备份号默认为1(如果您对同一个文件做过多次备份,可以点击备份号旁边的查看内容,在复选框中选择最新的一次备份后点确定)-->然后点击上方常规旁边的选项按钮
    5、在出现的窗口中选择在现有数据库上强制还原,以及在恢复完成状态中选择使数据库可以继续运行但无法还原其它事务日志的选项。在窗口的中间部位的将数据库文件还原为这里要按照你SQL的安装进行设置(也可以指定自己的目录),逻辑文件名不需要改动,移至物理文件名要根据你所恢复的机器情况做改动,如您的SQL数据库装在D:/Program Files/Microsoft SQL Server/MSSQL/Data,那么就按照您恢复机器的目录进行相关改动改动,并且最后的文件名最好改成您当前的数据库名(如原来是bbs_data.mdf,现在的数据库是forum,就改成forum_data.mdf),日志和数据文件都要按照这样的方式做相关的改动(日志的文件名是*_log.ldf结尾的),这里的恢复目录您可以自由设置,前提是该目录必须存在(如您可以指定d:/sqldata/bbs_data.mdf或者d:/sqldata/bbs_log.ldf),否则恢复将报错
    6、修改完成后,点击下面的确定进行恢复,这时会出现一个进度条,提示恢复的进度,恢复完成后系统会自动提示成功,如中间提示报错,请记录下相关的错误内容并询问对SQL操作比较熟悉的人员,一般的错误无非是目录错误或者文件名重复或者文件名错误或者空间不够或者数据库正在使用中的错误,数据库正在使用的错误您可以尝试关闭所有关于SQL窗口然后重新打开进行恢复操作,如果还提示正在使用的错误可以将SQL服务停止然后重起看看,至于上述其它的错误一般都能按照错误内容做相应改动后即可恢复

    三、收缩数据库

    一般情况下,SQL数据库的收缩并不能很大程度上减小数据库大小,其主要作用是收缩日志大小,应当定期进行此操作以免数据库日志过大
    1、设置数据库模式为简单模式:打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称(如论坛数据库Forum)-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”,然后按确定保存
    2、在当前数据库上点右键,看所有任务中的收缩数据库,一般里面的默认设置不用调整,直接点确定
    3、收缩数据库完成后,建议将您的数据库属性重新设置为标准模式,操作方法同第一点,因为日志在一些异常情况下往往是恢复数据库的重要依据

    四、设定每日自动备份数据库

    强烈建议有条件的用户进行此操作!
    1、打开企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器
    2、然后点上面菜单中的工具-->选择数据库维护计划器
    3、下一步选择要进行自动备份的数据-->下一步更新数据优化信息,这里一般不用做选择-->下一步检查数据完整性,也一般不选择
    4、下一步指定数据库维护计划,默认的是1周备份一次,点击更改选择每天备份后点确定
    5、下一步指定备份的磁盘目录,选择指定目录,如您可以在D盘新建一个目录如:d:/databak,然后在这里选择使用此目录,如果您的数据库比较多最好选择为每个数据库建立子目录,然后选择删除早于多少天前的备份,一般设定4-7天,这看您的具体备份要求,备份文件扩展名一般都是bak就用默认的
    6、下一步指定事务日志备份计划,看您的需要做选择-->下一步要生成的报表,一般不做选择-->下一步维护计划历史记录,最好用默认的选项-->下一步完成
    7、完成后系统很可能会提示Sql Server Agent服务未启动,先点确定完成计划设定,然后找到桌面最右边状态栏中的SQL绿色图标,双击点开,在服务中选择Sql Server Agent,然后点击运行箭头,选上下方的当启动OS时自动启动服务
    8、这个时候数据库计划已经成功的运行了,他将按照您上面的设置进行自动备份

    修改计划:
    1、打开企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->管理-->数据库维护计划-->打开后可看到你设定的计划,可以进行修改或者删除操作

    五、数据的转移(新建数据库或转移服务器)

    一般情况下,最好使用备份和还原操作来进行转移数据,在特殊情况下,可以用导入导出的方式进行转移,这里介绍的就是导入导出方式,导入导出方式转移数据一个作用就是可以在收缩数据库无效的情况下用来减小(收缩)数据库的大小,本操作默认为您对SQL的操作有一定的了解,如果对其中的部分操作不理解,可以咨询动网相关人员或者查询网上资料
    1、将原数据库的所有表、存储过程导出成一个SQL文件,导出的时候注意在选项中选择编写索引脚本和编写主键、外键、默认值和检查约束脚本选项
    2、新建数据库,对新建数据库执行第一步中所建立的SQL文件
    3、用SQL的导入导出方式,对新数据库导入原数据库中的所有表内容

    利用数据库日志恢复数据到时间点的操作
    由于不正常的数据丢失,而又不想使用备份数据还原,只要原来有备份且当前日志保存完好,可以采用这个方法试试,说不定可挽回损失……

    1,如果误操作之前存在一个全库备份(或已有多个差异备份或增量备份),首先要做的事就是进
    进行一次日志备份(如果为了不让日志文件变大而置trunc. log on chkpt.选项为1那你就死翘了)
        backup log dbName to disk='fileName'
    2,恢复一个全库备份,注意需要使用with norecovery,如果还有其他差异或增量备份,则逐个恢

        restore database dbName from disk='fileName' with norecovery
    3,恢复最后一个日志备份即刚做的日志备份,指定恢复时间点到误操作之前的时刻
        restore log dbName from disk='fileName'
        with stopat='date_time'

    以上这些操作都可以在SQL SERVER企业管理器里完成,难度不大。。。

    当然,如果误操作是一些不记日志的操作比如truncate table,select into等操作,那么是无法利
    用上述方法来恢复数据的...


    SQL Server2000数据库文件损坏时如何恢复
    出现这样的问题是比较严重的了,能否修复只能看你的运气……

    SQL Server2000中,如果数据库文件(非系统数据库文件)遇到错误的时候,仅适用于非master,msdb的数据库。

    说明如下:

    1 建一个测试数据库test(数据库类型为完全)
    2 建一个表,插入点记录
      create table a(c1 varchar(2))
      go
      insert into a values('aa')
      go
      insert into a values('bb')
      go
    3 作完全备份,到文件test_1.bak
    4 在作一点修改
      insert into a values('cc')
      go
      create table b(c1 int)
      go
      insert into b values(1)
      go
      insert into b values(2)
      go
    5 shutdown 数据库服务器
    6 用ultraedit编辑数据库文件test_data.mdf,随便修改点字节内容,相当于数据库遭到致命的损坏。
    7 启动数据库,并且运行企业管理器,点开数据库,看到test变成灰色,而且显示置疑。
    8 运行isql -SLocalhost -Usa -P
    1> backup log test TO DISK='D:Program FilesMicrosoft SQL ServerMSSQLBACKUP
    est_2.bak' WITH NO_TRUNCATE
    2>go
    已处理 2 页,这些页属于数据库 'test' 的文件 'TEST_Log'(位于文件 1 上)。
    BACKUP LOG 操作成功地处理了 2 页,花费了 0.111 秒(0.087 MB/秒)。

    9 进行恢复最老的完全备份
    1> RESTORE DATABASE test FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQL
    BACKUP est_1.bak' WITH NORECOVERY
    2> go
    已处理 96 页,这些页属于数据库 'test' 的文件 'TEST_Data'(位于文件 1 上)。
    已处理 1 页,这些页属于数据库 'test' 的文件 'TEST_Log'(位于文件 1 上)。
    RESTORE DATABASE 操作成功地处理了 97 页,花费了 0.107 秒(7.368 MB/秒)。

    10 恢复最近的日志
    1> RESTORE LOG test FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQLBACKU
    P est_2.bak' WITH RECOVERY
    2> go
    已处理 2 页,这些页属于数据库 'test' 的文件 'TEST_Log'(位于文件 1 上)。
    RESTORE LOG 操作成功地处理了 2 页,花费了 0.056 秒(0.173 MB/秒)。

     



    存储过程编写经验和优化措施
    经验之谈,看看……

    一、适合读者对象:数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。  

      二、介绍:在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。如果项目的SP较多,书写又没有一定的规范,将会影响以后的系统维护困难和大SP逻辑的难以理解,另外如果数据库的数据量大或者项目对SP的性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。  

      三、内容:  

      1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databse.dbo.table_name”,因为sp_depends不能显示出该SP所使用的跨库table或view,不方便校验。  

      2、开发人员在提交SP前,必须已经使用set showplan on分析过查询计划,做过自身的查询优化检查。  

      3、高程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点:   

      a)SQL的使用规范:

       i. 尽量避免大事务操作,慎用holdlock子句,提高系统并发能力。

       ii. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。

       iii. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。

       iv. 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。

       v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

       vi. 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。

       vii. 尽量使用“>=”,不要使用“>”。

       viii. 注意一些or子句和union子句之间的替换

       ix. 注意表之间连接的数据类型,避免不同类型数据之间的连接。

       x. 注意存储过程中参数和数据类型的关系。

       xi. 注意insert、update操作的数据量,防止与其他应用冲突。如果数据量超过200个数据页面(400k),那么系统将会进行锁升级,页级锁会升级成表级锁。   

      b)索引的使用规范:

       i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过6个索引。

       ii. 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引

       iii. 避免对大表查询时进行table scan,必要时考虑新建索引。

       iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。

       v. 要注意索引的维护,周期性重建索引,重新编译存储过程。  

      c)tempdb的使用规范:

       i. 尽量避免使用distinct、order by、group by、having、join、cumpute,因为这些语句会加重tempdb的负担。

       ii. 避免频繁创建和删除临时表,减少系统表资源的消耗。

       iii. 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。

       iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。

        v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。

        vi. 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。  

      d)合理的算法使用:   

      根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令:set statistics io on, set statistics time on , set showplan on 等

     
    展开全文
  • SQL Server 2008高级程序设计 6/6

    热门讨论 2012-03-03 19:35:54
    SQL Server 2008高级程序设计 6/6 SQL Server 2008 2010 高级程序设计 作者:(美)维埃拉 著,杨华,腾灵灵 译 出版社:清华大学 出版日期:2010-4-1 ISBN:9787302222729 字数:1250000 页码:730 ----------------...
  • sql server实用经验与技巧大汇

    千次阅读 2006-11-18 02:01:00
    作者tag:windows/.net sql server实用经验与技巧大汇集 SQL Server实用经验与技巧大汇集 包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等。...
    作者tag:
    windows/.net 
    sql server实用经验与技巧大汇集  
    

    SQL Server实用经验与技巧大汇集
    包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等。
    1.挂起操作

    在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作,要求重启,这里往往重启无用,解决办法:

    到HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Control/Session Manager

    删除PendingFileRenameOperations

    2.收缩数据库

    --重建索引

    DBCC REINDEX

    DBCC INDEXDEFRAG

    --收缩数据和日志

    DBCC SHRINKDB

    DBCC SHRINKFILE

    3.压缩数据库

    dbcc shrinkdatabase(dbname)

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

    exec sp_change_users_login 'update_one','newname','oldname'

    go

    5.检查备份集

    RESTORE VERIFYONLY from disk='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

    --CHECKDB 有3个参数:

    --REPAIR_ALLOW_DATA_LOSS

    -- 执行由 REPAIR_REBUILD 完成的所有修复,包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,以及删除已损坏的文本对象。这些修复可能会导致一些数据丢失。修复操作可以在用户事务下完成以允许用户回滚所做的更改。如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。修复完成后,备份数据库。

    --REPAIR_FAST 进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。这些修复可以很快完成,并且不会有丢失数据的危险。

    --REPAIR_REBUILD 执行由 REPAIR_FAST 完成的所有修复,包括需要较长时间的修复(如重建索引)。执行这些修复时不会有丢失数据的危险。

    --DBCC CHECKDB('dvbbs') with NO_INFOMSGS,PHYSICAL_ONLY

    7.SQL Server日志清除的两种方法

    在使用过程中大家经常碰到数据库日志非常大的情况,在这里介绍了两种处理方法……

    方法一:

    一般情况下,SQL数据库的收缩并不能很大程度上减小数据库大小,其主要作用是收缩日志大小,应当定期进行此操作以免数据库日志过大。

    1、设置数据库模式为简单模式:打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称(如论坛数据库Forum)-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”,然后按确定保存。

    2、在当前数据库上点右键,看所有任务中的收缩数据库,一般里面的默认设置不用调整,直接点确定。

    3、收缩数据库完成后,建议将您的数据库属性重新设置为标准模式,操作方法同第一点,因为日志在一些异常情况下往往是恢复数据库的重要依据。

    方法二:

    SET NOCOUNT ON

    DECLARE @LogicalFileName sysname,

    @MaxMinutes INT,

    @NewSize INT

    USE databasename -- 要操作的数据库名

    SELECT @LogicalFileName = 'databasename_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

     
    8.删除数据库中重复数据的几个方法

    数据库的使用过程中由于程序方面的问题有时候会碰到重复数据,重复数据导致了数据库部分设置不能正确设置……

    方法一

    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字段重复,而其他字段不一定重复或都重复可以忽略。

    A、对于第一种重复,比较容易解决,使用

    select distinct * from tableName

    就可以得到无重复记录的结果集。

    如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除:

    select distinct * into #Tmp from tableName

    drop table tableName

    select * into tableName from #Tmp

    drop table #Tmp

    发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。

    B、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下

    假设有重复的字段为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子句中省去此列)。

    9.更改数据库中表的所属用户的两个方法

    大家可能会经常碰到一个数据库备份还原到另外一台机器结果导致所有的表都不能打开了,原因是建表的时候采用了当时的数据库用户……

    --更改某个表

    exec sp_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

    10.SQL Server中直接循环写入数据

    declare @i int

    set @i=1

    while @i<30

    begin

    insert into test (userid) values(@i)

    set @i=@i+1

    end

    11.无数据库日志文件恢复数据库方法两则

    数据库日志文件的误删或别的原因引起数据库日志的损坏。

    方法一:

    1.新建一个同名的数据库;

    2.再停掉SQL Server(注意不要分离数据库);

    3.用原数据库的数据文件覆盖掉这个新建的数据库;

    4.再重启sql server;

    5.此时打开企业管理器时会出现置疑,先不管,执行下面的语句(注意修改其中的数据库名);

    6.完成后一般就可以访问数据库中的数据了,这时,数据库本身一般还要问题,解决办法是,利用。

    数据库的脚本创建一个新的数据库,并将数据导进去就行了。

    USE MASTER

    GO

    SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE

    GO

    UPDATE SYSDATABASES SET STATUS =32768 WHERE NAME='置疑的数据库名'

    Go

    sp_dboption '置疑的数据库名', 'single user', 'true'

    Go

    DBCC CHECKDB('置疑的数据库名')

    Go

    update sysdatabases set status =28 where name='置疑的数据库名'

    Go

    sp_configure 'allow updates', 0 reconfigure with override

    Go

    sp_dboption '置疑的数据库名', 'single user', 'false'

    Go

    方法二:

    设置数据库为紧急模式;

    停掉SQL Server服务;

    把应用数据库的数据文件XXX_Data.mdf移走;

    重新建立一个同名的数据库XXX;

    停掉SQL服务;

    把原来的数据文件再覆盖回来;

    运行以下语句,把该数据库设置为紧急模式;

    运行

    “Use Master

    Go

    sp_configure 'allow updates', 1

    reconfigure with override

    Go”

    执行结果:

    DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

    已将配置选项 'allow updates' 从 0 改为 1。请运行 RECONFIGURE 语句以安装。

    接着运行“update sysdatabases set status = 32768 where name = 'XXX'”

    执行结果:

    (所影响的行数为 1 行)

    重启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 结果。

    ………

    运行以下语句把系统表的修改选项关掉;

    运行“sp_resetstatus "XXX"

    go

    sp_configure 'allow updates', 0

    reconfigure with override

    Go”

    执行结果:

    在 sysdatabases 中更新数据库 'XXX' 的条目之前,模式 = 0,状态 = 28(状态 suspect_bit = 0),

    没有更新 sysdatabases 中的任何行,因为已正确地重置了模式和状态。没有错误,未进行任何更改。

    DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

    已将配置选项 'allow updates' 从 1 改为 0。请运行 RECONFIGURE 语句以安装。

    重新建立另外一个数据库XXX.Lost;

    DTS导出向导

    运行DTS导出向导;

    复制源选择EmergencyMode的数据库XXX,导入到XXX.Lost;

    选择“在SQL Server数据库之间复制对象和数据”,试了多次,好像不行,只是复制过来了所有表结构,但是没有数据,也没有视图和存储过程,而且DTS向导最后报告复制失败;

    所以最后选择“从源数据库复制表和视图”,但是后来发现,这样总是只能复制一部分表记录;

    于是选择“用一条查询指定要传输的数据”,缺哪个表记录,就导哪个;

    视图和存储过程是执行SQL语句添加的。

    12.维护SQL Server中表的索引

    在使用和创建数据库索引中经常会碰到一些问题,在这里可以采用一些另类的方法解决…

    --第一步:查看是否需要维护,查看扫描密度/Scan Density是否为100%

    declare @table_id int

    set @table_id=object_id('表名')

    dbcc showcontig(@table_id)

    --第二步:重构表索引

    dbcc dbreindex('表名',pk_索引名,100)

    --重做第一步,如发现扫描密度/Scan Density还是小于100%则重构表的所有索引

    --杨铮:并不一定能达100%。

    dbcc dbreindex('表名','',100)

    13.SQL Server补丁版本的检查

    SQL Server的补丁版本检查不如Windows 补丁版本检查直接,一个系统管理员,如果不了解SQL Server版本对应的补丁号,可能也会遇到一点麻烦,因此在这说明一下,通过这样的办法判别机器是安全的办法,不会对系统产生任何影响。

    1、用Isql或者SQL查询分析器登录到SQL Server,如果是用Isql,请在cmd窗口输入isql -U sa,然后输入密码,进入;如果是用SQL查询分析器,请从程序中启动,输入sa和密码(也可以用windows验证)。

    2、在ISQL中输入:

    Select @@Version;

    go

    或者SQL查询分析器中输入(其实如果不想输入,只要打开帮助的关于就可以了:))

    Select @@Version;

    然后按执行;

    这时会返回SQL的版本信息,如下:

    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation EntERPrise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)

    其中的8.00.760就是SQL Server的版本和补丁号。对应关系如下:

    SQL Server 2000 版本和级别 @@VERSION 产品级别

    SQL Server 2000 原始版本 8.00.194 RTM

    Database Components SP1 8.00.384 SP1

    Database Components SP2 8.00.534 SP2

    Database Components SP3、SP3a 或 MSDE 2000 Release A 8.00.760 SP3

    Database Components SP4 8.00.2039 SP4

    这样我们就能看到SQL Server的正确版本和补丁号了。

    我们也可以用xp_msver看到更详细的信息。

    14.Sql Server数据库的备份和恢复措施

    14.1、备份数据库

    1、打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server;

    2、SQL Server组-->双击打开你的服务器-->双击打开数据库目录;

    3、选择你的数据库名称(如论坛数据库Forum)-->然后点上面菜单中的工具-->选择备份数据库;

    4、备份选项选择完全备份,目的中的备份到如果原来有路径和名称则选中名称点删除,然后点添加,如果原来没有路径和名称则直接选择添加,接着指定路径和文件名,指定后点确定返回备份窗口,接着点确定进行备份。

    14.2、还原数据库

    1、打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server;

    2、SQL Server组-->双击打开你的服务器-->点图标栏的新建数据库图标,新建数据库的名字自行取;

    3、点击新建好的数据库名称(如论坛数据库Forum)-->然后点上面菜单中的工具-->选择恢复数据库;

    4、在弹出来的窗口中的还原选项中选择从设备-->点选择设备-->点添加-->然后选择你的备份文件名-->添加后点确定返回,这时候设备栏应该出现您刚才选择的数据库备份文件名,备份号默认为1(如果您对同一个文件做过多次备份,可以点击备份号旁边的查看内容,在复选框中选择最新的一次备份后点确定)-->然后点击上方常规旁边的选项按钮;

    5、在出现的窗口中选择在现有数据库上强制还原,以及在恢复完成状态中选择使数据库可以继续运行但无法还原其它事务日志的选项。在窗口的中间部位的将数据库文件还原为这里要按照你SQL的安装进行设置(也可以指定自己的目录),逻辑文件名不需要改动,移至物理文件名要根据你所恢复的机器情况做改动,如您的SQL数据库装在D:/Program Files/Microsoft SQL Server/MSSQL/Data,那么就按照您恢复机器的目录进行相关改动改动,并且最后的文件名最好改成您当前的数据库名(如原来是bbs_data.mdf,现在的数据库是forum,就改成forum_data.mdf),日志和数据文件都要按照这样的方式做相关的改动(日志的文件名是*_log.ldf结尾的),这里的恢复目录您可以自由设置,前提是该目录必须存在(如您可以指定d:/sqldata/bbs_data.mdf或者d:/sqldata/bbs_log.ldf),否则恢复将报错;

    6、修改完成后,点击下面的确定进行恢复,这时会出现一个进度条,提示恢复的进度,恢复完成后系统会自动提示成功,如中间提示报错,请记录下相关的错误内容并询问对SQL操作比较熟悉的人员,一般的错误无非是目录错误或者文件名重复或者文件名错误或者空间不够或者数据库正在使用中的错误,数据库正在使用的错误您可以尝试关闭所有关于SQL窗口然后重新打开进行恢复操作,如果还提示正在使用的错误可以将SQL服务停止然后重起看看,至于上述其它的错误一般都能按照错误内容做相应改动后即可恢复。

    14.3、收缩数据库

    一般情况下,SQL数据库的收缩并不能很大程度上减小数据库大小,其主要作用是收缩日志大小,应当定期进行此操作以免数据库日志过大。

    1、设置数据库模式为简单模式:打开SQL企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->双击打开数据库目录-->选择你的数据库名称(如论坛数据库Forum)-->然后点击右键选择属性-->选择选项-->在故障还原的模式中选择“简单”,然后按确定保存;

    2、在当前数据库上点右键,看所有任务中的收缩数据库,一般里面的默认设置不用调整,直接点确定;

    3、收缩数据库完成后,建议将您的数据库属性重新设置为标准模式,操作方法同第一点,因为日志在一些异常情况下往往是恢复数据库的重要依据。

    14.4、设定每日自动备份数据库

    强烈建议有条件的用户进行此操作!

    1、打开企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器;

    2、然后点上面菜单中的工具-->选择数据库维护计划器;

    3、下一步选择要进行自动备份的数据-->下一步更新数据优化信息,这里一般不用做选择-->下一步检查数据完整性,也一般不选择;

    4、下一步指定数据库维护计划,默认的是1周备份一次,点击更改选择每天备份后点确定;

    5、下一步指定备份的磁盘目录,选择指定目录,如您可以在D盘新建一个目录如:d:/databak,然后在这里选择使用此目录,如果您的数据库比较多最好选择为每个数据库建立子目录,然后选择删除早于多少天前的备份,一般设定4-7天,这看您的具体备份要求,备份文件扩展名一般都是bak就用默认的;

    6、下一步指定事务日志备份计划,看您的需要做选择-->下一步要生成的报表,一般不做选择-->下一步维护计划历史记录,最好用默认的选项-->下一步完成;

    7、完成后系统很可能会提示Sql Server Agent服务未启动,先点确定完成计划设定,然后找到桌面最右边状态栏中的SQL绿色图标,双击点开,在服务中选择Sql Server Agent,然后点击运行箭头,选上下方的当启动OS时自动启动服务;

    8、这个时候数据库计划已经成功的运行了,他将按照您上面的设置进行自动备份。

    修改计划:

    1、打开企业管理器,在控制台根目录中依次点开Microsoft SQL Server-->SQL Server组-->双击打开你的服务器-->管理-->数据库维护计划-->打开后可看到你设定的计划,可以进行修改或者删除操作

    14.5、数据的转移(新建数据库或转移服务器)

    一般情况下,最好使用备份和还原操作来进行转移数据,在特殊情况下,可以用导入导出的方式进行转移,这里介绍的就是导入导出方式,导入导出方式转移数据一个作用就是可以在收缩数据库无效的情况下用来减小(收缩)数据库的大小,本操作默认为您对SQL的操作有一定的了解,如果对其中的部分操作不理解,可以咨询动网相关人员或者查询网上资料。

    1、将原数据库的所有表、存储过程导出成一个SQL文件,导出的时候注意在选项中选择编写索引脚本和编写主键、外键、默认值和检查约束脚本选项;

    2、新建数据库,对新建数据库执行第一步中所建立的SQL文件;

    3、用SQL的导入导出方式,对新数据库导入原数据库中的所有表内容;

    利用数据库日志恢复数据到时间点的操作

    由于不正常的数据丢失,而又不想使用备份数据还原,只要原来有备份且当前日志保存完好,可以采用这个方法试试,说不定可挽回损失……

    1,如果误操作之前存在一个全库备份(或已有多个差异备份或增量备份),首先要做的事就是进行一次日志备份(如果为了不让日志文件变大而置trunc. log on chkpt.选项为1那你就死翘了);

    backup log dbName to disk='fileName'

    2,恢复一个全库备份,注意需要使用with norecovery,如果还有其他差异或增量备份,则逐个恢复;

    restore database dbName from disk='fileName' with norecovery

    3,恢复最后一个日志备份即刚做的日志备份,指定恢复时间点到误操作之前的时刻。

    restore log dbName from disk='fileName'

    with stopat='date_time'

    以上这些操作都可以在SQL SERVER企业管理器里完成,难度不大。。。

    当然,如果误操作是一些不记日志的操作比如truncate table,select into等操作,那么是无法利用上述方法来恢复数据的...

    15.SQL Server2000数据库文件损坏时如何恢复

    出现这样的问题是比较严重的了,能否修复只能看你的运气……

    SQL Server2000中,如果数据库文件(非系统数据库文件)遇到错误的时候,仅适用于非master,msdb的数据库。

    说明如下:

    1 建一个测试数据库test(数据库类型为完全);

    2 建一个表,插入点记录:

    create table a(c1 varchar(2))

    go

    insert into a values('aa')

    go

    insert into a values('bb')

    go

    3 作完全备份,到文件test_1.bak;

    4 再作一点修改:

    insert into a values('cc')

    go

    create table b(c1 int)

    go

    insert into b values(1)

    go

    insert into b values(2)

    go

    5 shutdown 数据库服务器;

    6 用ultraedit编辑数据库文件test_data.mdf,随便修改点字节内容,相当于数据库遭到致命的损坏;

    7 启动数据库,并且运行企业管理器,点开数据库,看到test变成灰色,而且显示置疑;

    8 运行isql -SLocalhost -Usa -P:

    1> backup log test TO DISK='D:Program FilesMicrosoft SQL ServerMSSQLBACKUP

    est_2.bak' WITH NO_TRUNCATE

    2>go

    已处理 2 页,这些页属于数据库 'test' 的文件 'TEST_Log'(位于文件 1 上)。

    BACKUP LOG 操作成功地处理了 2 页,花费了 0.111 秒(0.087 MB/秒)。

    9 进行恢复最老的完全备份:

    1> RESTORE DATABASE test FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQL

    BACKUP est_1.bak' WITH NORECOVERY

    2> go

    已处理 96 页,这些页属于数据库 'test' 的文件 'TEST_Data'(位于文件 1 上)。

    已处理 1 页,这些页属于数据库 'test' 的文件 'TEST_Log'(位于文件 1 上)。

    RESTORE DATABASE 操作成功地处理了 97 页,花费了 0.107 秒(7.368 MB/秒)。

    10 恢复最近的日志:

    1> RESTORE LOG test FROM DISK='D:Program FilesMicrosoft SQL ServerMSSQLBACKU

    P est_2.bak' WITH RECOVERY

    2> go

    已处理 2 页,这些页属于数据库 'test' 的文件 'TEST_Log'(位于文件 1 上)。

    RESTORE LOG 操作成功地处理了 2 页,花费了 0.056 秒(0.173 MB/秒)。

    16.存储过程编写经验和优化措施

    一、适合读者对象:

    数据库开发程序员,数据库的数据量很多,涉及到对SP(存储过程)的优化的项目开发人员,对数据库有浓厚兴趣的人。  

    二、介绍:

    在数据库的开发过程中,经常会遇到复杂的业务逻辑和对数据库的操作,这个时候就会用SP来封装数据库操作。如果项目的SP较多,书写又没有一定的规范,将会影响以后的系统维护困难和大SP逻辑的难以理解,另外如果数据库的数据量大或者项目对SP的性能要求很,就会遇到优化的问题,否则速度有可能很慢,经过亲身经验,一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。  

    三、内容:  

    1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databse.dbo.table_name”,因为sp_depends不能显示出该SP所使用的跨库table或view,不方便校验。  

    2、开发人员在提交SP前,必须已经使用set showplan on分析过查询计划,做过自身的查询优化检查。  

    3、高程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点:   

    a)SQL的使用规范:

    i. 尽量避免大事务操作,慎用holdlock子句,提高系统并发能力。

    ii. 尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接。

    iii. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作。

    iv. 注意where字句写法,必须考虑语句顺序,应该根据索引顺序、范围大小来确定条件子句的前后顺序,尽可能的让字段顺序与索引顺序相一致,范围从大到小。

    v. 不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

    vi. 尽量使用exists代替select count(1)来判断是否存在记录,count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。

    vii. 尽量使用“>=”,不要使用“>”。

    viii. 注意一些or子句和union子句之间的替换

    ix. 注意表之间连接的数据类型,避免不同类型数据之间的连接。

    x. 注意存储过程中参数和数据类型的关系。

    xi. 注意insert、update操作的数据量,防止与其他应用冲突。如果数据量超过200个数据页面(400k),那么系统将会进行锁升级,页级锁会升级成表级锁。   

    b)索引的使用规范:

    i. 索引的创建要与应用结合考虑,建议大的OLTP表不要超过6个索引。

    ii. 尽可能的使用索引字段作为查询条件,尤其是聚簇索引,必要时可以通过index index_name来强制指定索引。

    iii. 避免对大表查询时进行table scan,必要时考虑新建索引。

    iv. 在使用索引字段作为条件时,如果该索引是联合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用。

    v. 要注意索引的维护,周期性重建索引,重新编译存储过程。  

    c)tempdb的使用规范:

    i. 尽量避免使用distinct、order by、group by、having、join、cumpute,因为这些语句会加重tempdb的负担。

    ii. 避免频繁创建和删除临时表,减少系统表资源的消耗。

    iii. 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。

    iv. 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。

    v. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。

    vi. 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。  

    d)合理的算法使用:   

    根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令:set statistics io on, set statistics time on , set showplan on 等。
     

    展开全文
  • PL/SQL简介与简单实用

    2017-04-03 04:59:11
    一、PL/SQL简介: (1)PL/SQL俗称过程化SQL语言(Procedural ... 所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。 (2)PL/SQL中提

    一、PL/SQL简介:
    (1)PL/SQL俗称过程化SQL语言(Procedural Language/SQL),是Oracle对SQL的

    过程化扩充 而形成的程序开发语言。在普通SQL语句的使用上增加了编程语言的特点,
       所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言。


    (2)PL/SQL中提供了和其他高级语言类似的块操作,条件判断,循环结构等特性,
       将SQL的数据操纵功能与过程化语言的流程控制结构结合起来,能够完善更复杂的业务处理

    (3)PL/SQL程序不能在Oracle以外的其他关系型数据库管理系统中运行

    PL/SQL的特点:
      (1)PL/SQL是一种块结构言,一个PL/SQL程序就是一个块,块中可以嵌套子块,能够使一组SQL语句的功能更具模块化程序特点;
      (2)每个块中可以定义变量,变量的作用范围仅限于该块;
      (3)PL/SQL程序块中可以使用SQL的查询语句,DML(表的增删改操作)语句及事务处理语句,可以对程序中的错误进行自动处理,使程序能够在遇到错误的时候不会被中断;
      (4)PL/SQL程序大小写不敏感
      (5)Oracle中的PL/SQL引擎负责解释和执行PL/SQL程序,PL/SQL程序可以直接和SQL引擎进行交互
         PL/SQL引擎的作用:编写的SQL语句,通过网络、java程序或者客户端工具发送给关系型数据库管理系统,PL/SQL引擎负责拿到这个字符串(SQL语句就是一个字符串文本格式),对其SQL语句进行语法分析,判断该SQL语句否符合Oracle中的语法要求,若符合,则执行SQL语句.

    PL/SQL程序块与SQL语言的区别:
    (1)SQL语句:通过多条SQL语句实现功能时,每条语句都需要在客户端和服务端传递,而且每条语句的执行结果也需要在网络中进行交互,占用了大量的网络带宽,消耗了大量网络传递的时间,而在网络中传输的那些结果,往往都是中间结果,而不是我们所关心的。
    (2)PL/SQL程序块:而使用PL/SQL程序是因为程序代码存储在数据库中,程序的分析和执行完全在数据库内部进行,用户所需要做的就是在客户端发出调用PL/SQL的执行命令,数据库接收到执行命令后,在数据库内部完成整个PL/SQL程序的执行,并将最终的执行结果反馈给用户。在整个过程中网络里只传输了很少的数据,减少了网络传输占用的时间,所以整体程序的执行性能会有明显的提高。

    PL/SQL块有以下三部分组成
      声明部分(可选):声明常量、变量、复杂数据类型、游标等;
      (执行部分(必须):包含若干语句以实现特定功能;
      异常处理部分(可选):处理运行错误
      
    PL/SQL块结构:
      declare

         声明部分
      begin
         执行部分,可包含SQL语句或PL/SQL控制语句
      exception
         异常处理部分
      end;

    二、从表中检索单行数据

    在PL/SQL中,select语句汇总可使用 into 子句将检索的数据赋值给变量
    ◆语法格式:
      select columnList
      into variableNameList
      from tableName
      where condition

    注意:

      (1)variableNameList中变量的个数、数据类型必须和columnList匹配
      (2)使用select into检索数据,必须保证只能返回一条数据,否则均会抛出错误

    【示例】编写PL/SQL程序,查询并输出编号为 2 的产品编号、名称和价格
    declare 
        v_id products.product_id%type;   --v_i使用的 %type用于指定products表中的列类型
        v_name products.name%type;
        v_price products.price%type;    
        v_count int;
    begin
         select count(*)
         into v_count
         from products p
         where p.product_id = 2;
         
         if v_count = 0 then
            dbms_output.put_line('记录不存在!');
         else
              select p.product_id,p.name,p.price
              into v_id,v_name,v_price
              from products p
              where p.product_id = 2;
              dbms_output.put_line(v_id||','||v_name||','||v_price);
         end if;
    end;

    ◆记录类型(Record)   --类似于java中的类
      在PL/SQL中,可使用记录类型的变量来接收检索的多列数据。每个记录类型变量中包含了多个成员
      --要使用记录类型,必须先定义该类型,然后才能定义该类型的变量
      
    ◆定义Record类型的语法
      type typeName is record (memberDefineList)
      
    注意:
           (1)typeName为类型名
           (2)memberDefineList是以逗号隔开的成员定义列表
           (3)Oracle建议类型名带t_前缀,带_record_type后缀;记录类型变量带_record后缀
           (4)select子句中的列名顺序和记录类型中的成员顺序保持一致

    ◆使用 . 可访问记录类型变量中的成员

    【示例】将检索到的数据放入记录类型的变量中,然后输出每个成员的值
    --记录类型的变量相互赋值时均为值拷贝

    declare
           --定义记录类型
           type t_prd_record_type is record(m_id products.product_id%type,
                                            m_name products.name%type,
                                            m_price products.price%type);
           --定义变量
           v_prd_record1 t_prd_record_type;
           v_prd_record2 t_prd_record_type;
           v_count int;
    begin
         select count(*)
         into v_count
         from products p
         where p.product_id = 2;
         
         if v_count = 0 then
            dbms_output.put_line('记录不存在!');
         else
             select p.product_id,p.name,p.price
             into v_prd_record1
             from products p
             where p.product_id = 2;
             
             v_prd_record2 := v_prd_record1;    --记录类型变量的值拷贝
             --修改其记录类型1的各值

             v_prd_record1.m_id := 4;
             v_prd_record1.m_name := 'prd4';
             v_prd_record1.m_price := 23.6;
             --输出两个记录类型变量的值
             dbms_output.put_line(v_prd_record1.m_id||','||v_prd_record1.m_name||','||v_prd_record1.m_price);
             dbms_output.put_line(v_prd_record2.m_id||','||v_prd_record2.m_name||','||v_prd_record2.m_price);
        end if;
        dbms_output.put_line(v_prd_record2.m_id||','||v_prd_record2.m_name||','||v_prd_record2.m_price);
    end;

    ◆使用%rowtype获取表的行类型
      行类型也属于记录类型
      如:
          v_prd_record products%rowType;
          表明v_prd_record变量可以存储产品表中的一行记录
          
          可以直接将一行记录赋值

          select * into v_product_record from products where product_id = 3;
       使用%rowType定义的记录变量中的成员名称、类型与表中的列完全一致

    【示例】PL/SQL中使用行类型来操作表

    declare
           v_prd_record products%Rowtype;
    begin
         select *
         into v_prd_record
         from products p
         where p.product_id = 3;
         dbms_output.put_line(v_prd_record.product_id||','||v_prd_record.name||','||v_prd_record.price);
    end;



    展开全文
  • SQL SERVER实用经验技巧集

    千次阅读 2004-09-09 15:13:00
    Sql Server实用操作小技巧集合包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 (一)挂起操作在安装Sql或sp补丁的时候系统提示之前有挂起的...
  • SQLSTATE SQL SERVER 驱动程序错误描述

    千次阅读 2013-10-06 09:37:55
    HY000   所有绑定列都是只读的。   必须是可升级的列,以使用 SQLSetPos 或 SQLBulkOperations 更改或插入行。...请删除并重新启动应用程序。...驱动程序请求一个较新的 ...问题可能出在应用程序当前目录中的 netli
  • SQL Server 2000数据库程序设计

    千次阅读 2010-10-24 19:37:00
     有个用户的计算机能连接到中心机房的SQL Server 2000上。你在调试过程中发现这个用户的计算机的网络功能是正常的,而且其他用户都能正常地连接到SQL Server 2000。下面的哪些工具有助于你诊断和解决该问题?...
  • Sql Server实用操作小技巧集合

    千次阅读 2006-12-01 15:36:00
    Sql Server实用操作小技巧集合包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 (一)挂起操作在安装Sql或sp补丁的时候系统提示之前有挂起的...
  • SQL Server数据库程序设计认证试题

    千次阅读 2011-09-23 23:51:15
    1 1.你是一家公司的数据库开发员,你正在创建一个数据库用来存储15所中学运动会的统计信息,这些信息将被 50个公司用来在他们网站上发布运动会信息。每个公司的Web站点用不同的格式排列和显示...(SQL对XML支持)
  • [转]SQL SERVER实用经验技巧集

    千次阅读 2005-05-26 01:24:00
    SQL SERVER实用经验技巧集Night @ 2004-09-11 11:06Sql Server实用操作小技巧集合 包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 (一)挂起...
  • SQL SERVER实用经验技巧集 Sql Server实用操作小技巧集合 包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 (一)挂起操作 在安装Sql或sp补丁...
  • SQL Server实用操作小技巧集合

    千次阅读 2005-04-09 23:27:00
    包括安装时提示有挂起的操作、收缩数据库、压缩数据库、转移数据库给新用户以已存在用户权限、检查备份集、修复数据库等 (一)挂起操作 在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作,要求重启,这里往往...
  • 实用干货|Python+SQL无敌组合,菜鸟必读!

    万次阅读 多人点赞 2019-06-19 19:47:01
    SQL是结构化查询语言Structured Query Language的简称,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。 在正式讲解代码之前,先来科普一下数据库相关的知识。 数据库是以一定...
  • SQL Server实用经验与技巧大汇集

    千次阅读 2006-11-13 09:29:00
    1.挂起操作 在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作,要求重启,这里往往重启无用,解决办法: 到HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Control/Session Manager 删除PendingFil
  • SQL Server实用经验与技巧大汇集

    千次阅读 2006-11-13 00:23:00
    1.挂起操作 在安装Sql或sp补丁的时候系统提示之前有挂起的安装操作,要求重启,这里往往重启无用,解决办法: 到HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Control/Session Manager 删除PendingF
  • sql

    千次阅读 2011-09-17 00:11:36
    您还未登录!|登录|注册|帮助 CSDN首页资讯论坛博客下载搜索 更多 CTO俱乐部学生大本营培训充电移动开发软件研发云计算程序员TUP matail的专栏 ...常用SQL命令和ASP编程 发表人
  • 使用用于 SQL Server 的 IIS 虚拟目录管理实用工具...在运行 Microsoft Internet 信息服务 (IIS) 的 计算机上,使用用于 SQL Server 的 IIS 虚拟目录管理实用工具(在"SQL Server 工具"程序组中单击"在 IIS 中配置 SQL
  • ORACLE PL/SQL编程详解之七:  程序包的创建与应用(聪明在于学习,天才在于积累!)
  • 本页内容Reporting Services 概述设计第一个报表部署和测试将报表添加到 Web 应用程序将报表嵌入到 Web 应用程序中保证报表的安全使用 Reporting Services Web 服务订阅报表接...利用 SQL Server® 2000 Reporting Ser
  • SQL_Server数据库试卷

    2009-03-12 00:14:56
    2.下列四项中,不属于SQL2000实用程序的是( )。 A.企业管理器 B.查询分析器 C.服务管理器 D.媒体播放器 3.SQL Server安装程序创建4个系统数据库,下列哪个不是( )系统数据库。 A.master B.model C.pub D.msdb 4....
  • 数据库习题A 一单项选择题(每小题1分共10分) 数据库特点 数据按一定数据模型组织描述和储存 可...下列四项中不属于SQL实用程序是 D A.企业管理器 B.查询分析器 C.服务管理器 D.媒体播放器 3.SQL Server安装程序创建4个
  • 利用 SQL Server Reporting Services 从应用程序生成用户友好的报表 发布日期: 09/03/2004 | 更新日期: 09/03/2004John C. Hancock ...
  • 这里以官方的Microsoft SQL ...Microsoft SQL Server 2012 Express LocalDB 是面向程序开发人员的 SQL Server Express 的执行模式。 LocalDB 安装将复制启动 SQL Server 数据库引擎 所需的最少的文件集。 安装 L...
  • [强烈推荐]ORACLE PL/SQL编程详解之七:   程序包的创建与应用(聪明在于学习,天才在于积累... PL/SQL编程详解之一:PL/SQL 程序设计简介(千里之行,始于足下)  [顶]ORACLE PL/SQL编程详解之二:PL/SQL块结构和组

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 23,642
精华内容 9,456
关键字:

不属于sql实用程序