Sql server 数据库管理-基础-笔记

数据库管理

1.在创建数据库之前，必须确定数据库名称、所有者、大小、及存储该数据文件和文件组。
2.创建数据库之前注意事项：
    A.创建数据库，至少拥有 CREATE DATABASE,CREATE ANY DATABASE 或ALTER ANY DATABASE权限。
    B.对创建数据库用户将成为该数据库的所有者。
    C.对于一个服务实例，最多可以创建32767个数据库。
    D.数据库名称必须遵循为标识符指定的规则，在数据创建时，MODEL数据库中的所有用户定义对象都将复制到所有新创建的数据库中，因此，可以向model数据库中添加任何对象，以便将这些对象包含到所有新创建的数据库中。
    
3.数据和日志信息绝不能混合在同一个文件中，而且一个文件只能由一个数据库使用。

4.数据库文件分为：    
    主文件：包含着数据库的启动信息。每个数据库都有一个主文件,文件扩展名为.mdf
    次要文件：包含不能旋转在主数据文件的所有数据,文件扩展名为.ndf
    事务日志文件：包含用于恢复数据库的日志信息，每个数据库必须至少有一个事务日志文件，扩展名为.ldf 
    
5.每个数据库有一个 PRIMARY文件组：
    文件组包含主文件和未放入其他文件组的所有次要文件。
    文件组对组内的所有文件都使用按比例填充策略。
    文件组中的所有文件满时，数据库引擎就自动按自动循环一次扩展一个文件（数据库设置为自动增长）。
    文件和文件组可以改善数据库的性能，因为这样允许跨多个磁盘，多个磁盘控制器或RAID系统创建数据库。
    文件和文件组还允许数据布局，因为可以在特定的文件组中创建表。
    
6.文件和文件组设计原则：
    A.一个文件或文件组不能由多个数据库使用。
    B.一个文件只能是一个文件组的成员。
    C.数据和事务日志信息不能属于同一个文件或文件组。
    D.事务日志文件不能属于任何一个文件组。
    
7.使用文件和文件组的一些建议：
    A.大多数据库在只有单个数据文件和单个事务日志文件的情况下性能良好。
    B.如果使用多个文件，应当附加文件创建第2个文件组，并将其设置为默认文件组，这样，主文件将只包含系统表和对象。
    C.要使性能最大化，应当在尽可能多的不同的可用本地物理磁盘上创建文件或文件组。应当将争夺空间最激烈的对象放置在不同的文件组中。
    D.使用文件组将对象放置在特定的物理磁盘上。
    F.将在同一联接查询中使用的不同表置于不同文件组中。由于采用并行磁盘I/O对联接数据进行搜索，所以性能将得以改善。
    G.将最常访问的表和属于这些表的非聚集索引置于不同的文件组中，如果文件位于不同的物理磁盘上，由于采用并行I/O，所以性能将得以改善。
    H.不要将事务日志文件置于其中已有其它文件和文件组的物理磁盘上。
    
8.创建数据库的语法和参数：
    CREATE DATABASE database_name
        [ON 
            [PRIMARY][<filespec> ]
        [LOG ON ]
        [COLLATE collation_name]
        
    ON:指定以显式定义方式指定存储数据库数据部分的磁盘文件。
    PRIMARY:指定<filespec>列表中的主文件。
    LOG ON :指定存储数据库日志的磁盘文件。
    COLLATE collation_name：指定数据库的默认排序规则。
    
    <filespec>：用于控制文件属性：
        NAME logical_file_name:指定文件逻辑名称。且必须在数据库中唯一。
        FILENAME 'os_file_name':指定操作系统（物理）文件名，在创建数据库前指定路径必须成在。
        SIZE size:指定文件的初始大小。
        MAXSIZE max_size:指定文件可增大到最大大小。
        UNLIMITED:指定文件将增长到磁盘已满。
        FILEGROWTH growth_increment:指定每次需要新空间时为文件添加的空间量。growth_increment不能超过max_size设置值。growth_increment=0 表时关闭自动增长，不允许增加空间。
        
    <filegroup> 用于控制文件组属性：
        FILEGROUP filegroup_name:文件组的逻辑名称，filegroup_name必须在数据库中唯一，不能是系统提供的名称PRIMARY和PPRIMARY_LOG.
        DEFAULT:指定文件组为数据库中的默认文件组。
        
9.在创建数据库后，应当备份master数据库。因为创建数据库将更新master中的系统表。
10.可以使用DB_ID函数判断数据库是否已经存在，该函数用于返回数据库的标识号：
    SELECT DB_ID(N'数据库名') AS [Database ID]
    
11.创建数据库时先判断创建的数据库是否已存在，如果存在删除，然后再创建：
    USE master;
    GO 
    IF DB_ID(N'数据库名') IS NOT NULL
    DROP DATABASE 数据库名;
    GO 
    CREATE DATABSE 数据库名;
    
12.向数据为添加数据文件
    当数据库自动增长设置为关闭，磁盘没有空间时会出现 1105错误。
    
    使用ALTER DATABASE 设置数据库大小或向数据库添加文件，语法：
        ALTER DATABASE database_name
            ADD FILE <filespec>
                [TO FILEGROUP {filegroup_name | DEFAULLT}]
            ADD LOG FILE <filespec>
            REMOVE FILE logical_file_name
            MODIFY FILE <filespec>
            
        <filespec> 部分用于设置文件组属性：
            OFFLINE:用于将文件设置为脱机并使用文件组中的所有对象不可访问。仅在文件已损坏但可以还原时，才能使用该选项。
            
    修改数据库文件方法一：
        扩展数据库文件大小：
        ALTER DATABASE 数据库名
        MODIFY FILE
        (
            NAME = ‘文件名’
            SIZE = 新的大小
            MAXSIZE = 最大值 
                 )
    
    修改数据库文件方法二：
        向数据库文件组里添加一个新文件：
        ALTER DATABASE 
        ADD FILE
        (
            NAME = ‘数据文件名’
            FILENAME = ‘文件路径’
            SIZE = 新的大小
            FILEGROWTH = 大小
        )
        TO FILEGROUP 文件组名
    
    从文件组中删除文件成员：
        ALTER DATABASE 数据库名
        REMOVE FILE 需要移除的文件名
    
13.修改数据库文件组：
    添加文件组：
        ALTER DATABASE 数据库名 
        ADD FILEGROUP 新增文件组名;
    
    重命名文件组：
        ALTER DATABASE 数据库名    
        MODIFY FILEGROUP 旧文件组名
        NAME = 新文件组名;
    
    删除文件组：
        ALTER DATABASE 数据库名 
        MODIFY FILEGROUP 文件组名;
    
14.可以使用DBCC SHRINKDATABASE 语句或 DBCC SHRINKFILE语句来手动收缩数据库或者数据库文件。
    数据库中的每个文件都可以通过删除末使用的页的方法来减小。
    可以成组或单独手动收缩数据库文件，也可以设置数据库的AUTO_SHRINK选项为ON 来指定按间隔自动收缩。
    在使用DBCC SHRINKDATABASE语句时，无法将整个数据库收缩得比其初始大小更小。即使已经删除数据库的所有数据也不行。
    
    但使用DBCC SHRINKFILE时，可以将各个数据文件收缩得比其初始大小更小。必须对每个文件分别进行收缩，而不能尝试收缩整个数据库。
    
    手动收缩数据库：
        DBCC SHRINKDATABASE 
        (
            database_name | database_id | 0   --要收缩的数据库名或ID,0表示当前数据库。
            target_percent:数据库收缩后的数据文件中所需的剩余可用空间百分比。
            NOTRUNCATE:指定数据库文件中保留释放的文件空间。
            truncateonly:将数据文件中任何末使用空间释放给操作系统。
            WITH NO_INFOMSGS:取消严重级别 0-10 的所有信息性消息。
        )
    
    自动收缩：
    将数据库的AUTO_SHRINK选项设置为ON后，数据库引擎将自动收缩有可用空间的数据库。
        ALTER DATABASE 数据库名
        SET AUTO_SHRINK ON;
        
15.收缩数据文件
    USE 数据库名
    GO 
    DBCC SHRINKFILE(数据文件，收缩到的大小M)
    
16.数据库选项：
    AUTO_CLOSE:
        设置为ON时，数据库将最后一个用户退出后完全关闭，它占用的资源也将释放，当用户再次使用该数据库时，数据库将自动重新打开。
        设置为OFF时：最后一个用户退出数据库仍保持打开。
        
    AUTO_CREATE_STATISTICS: 
        ON :将自动创建谓词所使用的列的统计信息。默认为ON.
        OFF:需要手动创建统计信息。
        
    AUTO_UPDATE_STATISTICS:
        ON:优化查询所需的任何缺少的统计信息将在查询优化过程中自动生成。默认为ON。
        OFF:设置成OFF时，统计信息必须手动创建。
        
    AUTO_SHRINK:
        ON:数据库文件可作为定期收缩的对象，
        OFF:在定期检查未使用的空间的过程中，数据库文件不自动收缩，默认为OFF.
    
    CURSOR_CLOSE_ON_COMMIT:
        ON:所有打开的游标都将在提交或回滚事务时关闭。
        OFF:打开的游标将在提交仍保持打开，回滚事务将关闭所有游标，但定义为INSENSITIVE或STATIC的游标除外，默认为OFF.
    
    CURSOR_DEFAULT:    
        如果指定了 LOCAL,并且创建游标时没有将其定义为GLOBAL,则游标的作用域将局限于创建游标时所在的批处理，存储过程或触发器。游标名仅在该作用域内有效。
        如果指定了GLOBAL,并且创建游标时没有将其定义为LOCAL，则游标的作用域将是相应连接的全局范围。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称，默认值为GLOBAL.
        
        
    OFFLINE:数据库将完全关闭和退出，并标记为脱机。
    ONLINE:数据库处于打开状态并且可供使用。
    EMERGENCY:数据库将标记为READ_NLY,日志记录将被禁用，并且只有sysadmin固定服务器角色的成员才能进行访问。默认值断ONLINE
    
    SINGLE_USER:一次只允许一个用户连接到数据库：
    RESTRICTED_USER:只允许db_owner固定数据库角色的成员以及dbcreator和sysadmin固定服务器角色的成员连接到数据库，不过连接数据没有限制。
    MULTI_USER:允许所有具有相应权限的用户连接到数据库。默认值为MULTI_USER
    
    DATE_CORRELATION_OPTIMIZATION:
        ON:服务器将维护数据库中所有由FOREIGN KEY 约束链接的包含datetime列的两个表中的相关统计信息。
        OFF:不会维护相关统计信息。默认为OFF 
    
    DB_CHAINING:
        ON:数据库可以是跨数据库所有权链的源或目标。
        OFF：数据库不能参与跟数据库的所有权链接，默认为OFF 
        
    TRUSTWORTHY:
        ON:使用了模拟上下文的数据库模块可以访问数据库以外的资源；
        OFF:模拟上下文中无法访问数据库以外的资源，默认为OFF 
        
    PARAMETERIZATION:
        SIMPLE:将根据数据库的默认行为参数化查询
        FORCED:将参数化数据库中所有查询，默认为SIMPLE.
    
    RECOVERY:
        FULL:将使用事务日志备份在发生媒体故障后进行完全恢复，如果数据文件损坏，媒体恢复可以还原所有已提交的事务。
        BULK_LOGGED:将综合某些大规模或大容量操作的最佳性能和日志空间的最小占用量，在发生故障后进行恢复，
        SIMPLE:将提供占用最小日志空间的简单备份策略，默认为FULL
    
    PAGE_VERIFY:
        CHECKSUM:数据库引擎将在页写入磁盘时计算整个页的内容的校验和并存储页头中的值，从磁盘中读取页时，将重新计算校验和，并与存储在页头中的校验和值进行比较。
        TORN_PAGE_DETECTION:在将8KB的数据库页写入磁盘时，该页的每个512字节的扇区都有一个特定的保存并存储在数据库的页头中，从磁盘中读取页时，页头中存储的残缺位将与实际的页扇区信息进行比较，
        NONE:数据库页写入将不生成CHECKSUM或TORN_PAGE_DETECTION值。默认值为CHECKSUM.

Server Broker选项：        
    ENABLE_BROKER:将为指定数据库启动Service BROKER.
    DISABLE_BROKER:将对指定的数据库禁用service Broker 
    NEW_BROKER:数据库将收到新的代理标识符。
    ERROR_BROKER_CONVERSATIONS:数据训中的会话将在附加数据时收到一个错误信息，默认值为DISABLE_BROKER 
    
快照隔离选项：
    ALLOW_SNAPSHOT_ISOLATION:
        ON:事务可以指定SNAPSHOT事务隔离级别。当事务在SNAPSHOT隔离级别运行时，所有的语句都将数据快照视为位于事务的开头。
        OFF，事务无法指定SNAPSHOT事务隔离级别，默认值为OFF
    
    READ_COMMITTED_SNAPSHOT:
        ON: 指定READ_COMMITTED隔离级别的事务将使用行版本控制而不是锁定。当事务在READ_COMMITTED隔离级别运行时，所有的语句都将数据快照视为位于语句的开头。
        OFF:指定READ_COMMITTED隔离级别的事务将使用锁定。默认为OFF.
        
SQL选项
    ANSI_NULL_DEFAULT :指定在CREATE TABLE或ALTER TABLE语句中未显示定义为空性的别名数据类型或CLR用户定义数据类型列的默认值（NULL或NOT NULL）
        ON:默认值为NULL
        OFF:默认值为 NOT NULL 
    
    ANSI_NULLS:
        ON:所有与空值的比较运算计算结果为UNKNOWN
        OFF:非UNICODE值与空值的比较运算在两者均为NULL时结果为TRUE,默认值为OFF.
        
    ANSI_PADDING:
        ON:在除现如除以零或聚合函数中出现空值这类情形时，将发出错误警告。
        OFF:在出现如除以零这类情形时，不会发出警告，并返回空值，默认值为OFF 
    
    ARITHABORT:
        ON:在执行查询期间发生溢出或除以零的错误时，该查询将结束。
        OFF：出现其中一个错误时将显示警告信息，而查询批处理或事务将继续处理，就像没有出现错误一样，默认值为OFF.
    
17.重命名数据库：
    A.将数据库设置为单用户模式
        ALTER DATABASE database_name
        SET SINGLE_USER;
        GO
    B.重命名数据库：
        ALTER DATABASE database_name
        MODIFY NAME=new_database_name;
    C.将数据库改为多用户模式：
        ALTER DATABASE database_name
        SET MULTI_USER;
        GO
        
18.删除数据库
    A.删除数据库满足条件：
        如果数据库涉及日志传送操作，在删除数据库之前应当取消日志传送操作。
        要删除为事务复制发布的数据库，或删除为合并复制发布或订阅的数据库，应当首先从数据库中删除复制，如果数据库已损坏，不能首先删除复制，通常可以通过首先使用ALTER DATABASE 将数据库设置为脱机然后再删除的方法来删除数据库。
        必须先删除数据库存在的数据库快照。
        
    B.删除数据库后，应备份master数据库，因为删除数据库将更新master数据库中的信息。
    C.删除数据库语法：
        DROP DATABASE 
            database_name        
            database_snapshot_name         --指定要删除数据库快照的名称。