SQL Server教程 - SQL Server介绍

更新记录
转载请注明出处：https://www.cnblogs.com/cqpanda/p/16519559.html
2022年8月2日 发布。
2022年7月26日 从笔记迁移到博客。

为什么使用SQL Server

良好的商业支持
强大的可视化界面
使用最方便、开发最方便、运维最方便
Windows 系统下数据库的不二选择
SQL Server是Microsoft公司的数据库平台，集成开发更加方便

SQL Server版本

版本	说明
Enterprise（企业版）	This is the top-end edition with a full feature set.
Standard（标准版）	This has less features than Enterprise, when there is no requirement of advanced features
Developer（开发者版）	This is similar to Enterprise, but licensed to only one user for development, testing and demo. It can be easily upgraded to Enterprise without reinstallation
Express（简化免费版）	This is free entry level database. It can utilize only 1 CPU and 1 GB memory, the maximum size of the database is 10 GB
Compact（紧凑版）	This is free embedded database for mobile application development.The maximum size of the database is 4 GB
Enterprise Evaluation（企业评估版）	The SQL Server Evaluation Edition is a great way to get a fully functional and free instance of SQL Server for learning and developing solutions. This edition has a built-in expiry of 6 months from the time that you install it.
Workgroup（工作组版本）	This is suitable for remote offices of a larger company
Web（Web应用版）	This is designed for web applications
Datacenter（数据中心版）	The major change in new SQL Server 2008 R2 is Datacenter Edition.The Datacenter edition has no memory limitation and offers support for more than 25 instances
Business Intelligence（商业智能版）	Business Intelligence Edition is a new introduction in SQL Server 2012. This edition includes all the features in the Standard edition and support for advanced BI features such as Power View and PowerPivot, but it lacks support for advanced availability features like AlwaysOn Availability Groups and other online operations.

SQL Server的组成

概述

分为两大块：数据库模块、商务智能模块

数据库模块组成：

组成	描述
数据库引擎（Database Engine）	数据库引擎是SQL Server 2019系统的核心服务，负责完成数据的存储、处理和安全管理。包括数据库引擎（用于存储、处理和保护数据的核心服务）、复制、全文搜索以及用于管理关系数据和XML数据的工具。例如，创建数据库、创建表、创建视图、数据查询和访问数据库等操作，都是由数据库引擎完成的。通常情况下，使用数据库系统实际上就是在使用数据库引擎。数据库引擎是一个复杂的系统，它本身就包含了许多功能组件，如复制、全文搜索等。使用它可以完成CRUD和安全控制等操作。

商务智能模块组成：

组成	描述
分析服务（SQL Server Analysis Services，SSAS）（已拆分）	分析服务的主要作用是通过服务器和客户端技术的组合提供联机分析处理（On-Line Analytial Processing,OLAP)和数据挖掘功能。通过分析服务，用户可以设计、创建和管理包含来自于其他数据源的多维结构，通过对多维数据进行多角度分析，可以使管理人员对业务数据有更全面的理解。另外，使用分析服务，用户可以完成数据挖掘模型的构造和应用，实现知识的发现、表示和管理。
报表服务（SQL Server Reporting Services，SSRS）	报表服务用于创建和发布报表及报表模型的图形工具，也用于管理报表服务器的管理工具，以及作为对报表服务对象模型进行编程和扩展的API(应用程序编程接口）。报表服务是一种基于服务器的解决方案，用于从多种关系数据源和多维数据源提取内容以生成企业报表，可以根据需要发布能以各种格式查看的报表，具有集中管理的安全性，并提供报表订阅功能。SQL Server 2019的报表服务所创建的报表可以通过网页（Web)方式进行查看，也可以作为Microsoft Windows 应用程序的一部分来查看
集成服务（SQL Server Integration Services，SSIS）	用于生成高性能数据集成和工作流解决方案的平台，负责完成数据的提取、转换和加载等操作。其他的3种服务就是通过Integration Services来进行联系的。除此之外，使用数据集成服务可以高效地处理各种各样的数据源，例如：SQL Server、Oracle、Excel、XML文档、文本文件等。

数据库引擎

数据库引擎作为系统服务运行在服务器上，通常称为SQL Server实例（Instance），一台服务器上可以有多个SQL Server实例。我们通常连接到数据库，就是连接的SQL Server实例。

其他组件

以下这些组件依赖于数据库引擎：
T-SQL编程接口
安全子系统
复制
SQL Server代理
高可用和灾难恢复工具
SQL Server集成服务
SQL Server管理工具

SQL Server服务类型

数据库服务（Database Services）
分析服务（Analysis Services）
报表服务（Reporting Services）
集成服务（Integration Services）

SQL Server安装模式

单独安装（Stand-Alone）
集群安装（Cluster based）

SQL Server数据库对象（Database Objects）

类型（Types）
表（Table）
视图（View）
索引（Indexes）
默认值（Default）
规则（Rule）
约束（Constraints）
触发器（Triggers）
存储过程（Stored procedures）
自定义定义函数（User-defined functions）
用户（User）
计划指南（Plan guides）
序列（Sequences）
程序集（Assemblies）

SQL Server数据库管理工具

SSMS（SQL Server Management System） 可视化GUI管理工具
SQL-SMO API 用代码管理SQL Server
T-SQL脚本
Windows Management Instrumentation（WMI接口）
应用程序API（ADO.NET等）

设置SQL Server服务器配置

进入服务器属性页面

配置【内存】

（1)服务器内存选项
最小服务器内存：分配给SQL Server的最小内存量，低于该值的内存不会被释放。
最大服务器内存：分配给SQL Server的最大内存量。

（2)其他内存选项
创建索引占用的内存：指定在创建索引排序过程中要使用的内存量，数值0表示由操作
系统动态分配。
每次查询占用的最小内存：为执行查询操作分配的内存量，默认值为1024KB.

配置值：显示并运行更改选项卡中的配置内容。
运行值：查看本对话框中选项的当前运行的值。

配置【处理器】

在【选择页】列表中选择【处理器】选项，在该选项卡中可以查看或修改CPU选项，一般来说，只有安装了多个处理器才需要配置此项。选项卡中有以下选项：自动设置所有处理器的处理器关联掩码、自动设置所有处理器的I/O关联掩码、处理器关联、I/O关联和最大工作线程数。

自动设置所有处理器的处理器关联掩码：设置是否允许SQL Server设置处理器关联。如果启用，操作系统将自动为SQL Server 2019分配CPU

自动设置所有处理器的I/O关联掩码：此项是设置是否允许SQL Server设置I/O关联。如果启用，操作系统将自动为SQL Server 2019分配磁盘控制器。

处理器关联：对于操作系统而言，为了执行多任务，同进程可以在多个CPU之间移动，提高处理器的效率，但对于高负荷的SQL Server而言，该活动会降低其性能，因为会导致数据的不断重新加载。这种线程与处理器之间的关联就是“处理器关联”。如果将每个处理器分配给特定线程，那么就会消除处理器重新加载的需要以及减少处理器之间的线程迁移。I/O关联与处理器关联类似，设置是否将SQL Server磁盘I/O绑定到指定的CPU子集。

最大工作线程数：允许 SQL Server 动态设置工作线程数，默认值为0.一般来说，不用修改该值。

配置【安全性】

在【选择页】列表中选择【安全性】选项，此选项卡中的内容主要为了确保服务器的安全运行，可以配置的内容有：服务器身份验证、登录审核、服务器代理账户和选项。
（1)服务器身份验证：表示在连接服务器时采用的验证方式，默认在安装过程中设定为【Windows 身份验证模式】，也可以采用【SQL Server和Windows 身份验证模式】的混合模式。
（2)登录审核：对用户是否登录SQL Server 2019服务器的情况进行审核。
（3)服务器代理账户：是否启用服务器代理账户。
（4)【选项】选项组：
符合通用标准符合性：是否启用通用条件。
启用C2审核跟踪：是否启用C2审核跟踪。
跨数据库所有权链接：是否允许数据库成为跨数据库所有权限的源或目标。

注意：更改安全性配置之后需要重新启动服务。

配置【连接】

在【选择页】列表中选择【连接】选项，此选项卡中有以下选项：最大并发连接数、使用查询调控器防止查询长时间运行、默认连接选项、允许远程连接到此服务器和需要将分布式事务用下服务器到服务器的通信。

（1)最大并发连接数：默认值为0,表示无限制。也可以输入数字来限制SQL Server 2019允许的连接数。注意，如果将此值设置过小，可能会阻止管理员进行连接，但是“专用管理员连接”始终可以连接。
（2)使用查询调控器防止查询长时间运行：为了避免使用SQL查询语句执行过长时间，导致SQL Server服务器的资源被长时间占用，可以设置此项。选择此项后输入最长的查询运行时间，超过这个时间后，会自动中止查询，以释放更多的资源。

（3)默认连接选项：默认连接选项的内容比较多，各个选项的作用如下：

配置选项	作用
隐式事务	控制在运行一条语句时，是否隐式启动一项事务
提交时关闭游标	控制执行提交操作后游标的行为
ANSI 警告	控制集合警告中的截断和NULL
ANSI 填充	控制固定长度的变量的填充
ANSI NULLS	在使用相等运算符时控制 NULL的处理
算术终止	在查询执行过程中发生溢出或被零除错误时终止查询
算术忽略	在查询过程中发生溢出或被零除错误时返回NULL
带引号的标识符	计算表达式时区分单引号和双引号
未计算	关闭在每条语句执行后所返回的说明有多少行受影响的消息
null默认启用	更改会话的行为，使用 ANSI 兼容为空性。未显式定义为空性的新列定义为
允许使用空值	更改会话的行为，不使用 ANSI 兼容为空性。未显式定义为空性的新列定义
null默认禁用	为不允许使用空值
串联null时得到null	当将 NULL 值与字符串连接时返回 NULL
数值舍入中止	当表达式中出现失去精度的情况时生成错误
Xact 中止	如果 Transact-SQL 语句（简称T-SQL语句）引发运行时错误，则回滚事务

（4)允许远程连接到此服务器：选中此项则允许从运行的SQL Server 实例的远程服务器控制存储过程的执行。远程查询超时值是指定在SQL Server超时之前远程操作可执行的时间，默认为600秒。
（5)要将分布式事务用于服务器到服务器的通信：选中此项则允许通过 Microsoft 分布式事务处理协调器（MSDTC),保护服务器到服务器过程的操作。

配置【数据库设置】

在【选择页】列表中选择【数据库设置】选项，该选项卡可以设置针对该服务器上的全部数据库的一些选项，包含默认索引填充因子、默认备份介质保留期（天）、备份和还原、恢复和数据库默认位置、配置值和运行值等

（1)默认索引填充因子：指定在SQL Server 使用目前数据创建新索引时对每一页的填充程度。索引的填充因子就是规定向索引页中插入索引数据最多可以占用的页面空间。例如填充因子为70%那么在向索引页面中插入索引数据时最多可以占用页面空间的70%,剩下30%的空间保留给索引的数据更新时使用。默认值为0,有效值为0~100.
（2)备份和还原：指定SQL Server 等待更换新磁带的时间。
无限期等待：SQL Server在等待新备份磁带时永不超时。
尝试一次：是指如果需要备份磁带时，但它却不可用，那么SQL Server将超时。
尝试：它的分钟数是指如果备份磁带在指定的时间内不可用，SQL Server将超时。
（3)默认备份介质保持期（天）：指在用于数据库备份或事务日志备份后每一个备份媒体的保留时间。此选项可以防止在指定的日期前覆盖备份。
（4)恢复：设置每个数据库恢复时所需的分钟数。数值0表示让SQL Server 自动配置。
（5)数据库默认位置：指定数据文件和日志文件的默认位置。

配置【高级】

（1)并行的开销阈值：指定数值，单位为秒，如果一条SQL查询语句的开销超过这个数值，那么就会启用多个CPU来并行执行高于这个数值的查询，以优化性能。
（2)查询等待值：指定在超时之前查询等待资源的秒数，有效值为0~2147 483 647.默认值为-1,其意思是按估计查询开销的25倍计算超时值。
（3)锁：设置可用锁的最大数目，以限制SQL Server为锁分配的内存量。默认值为0,表示允许SQL Server 根据系统要求来动态分配和释放锁。
（4)最大并行度：设置执行并行计划时能使用的CPU的数量，最大值为64.0值表示使用
所有可用的处理器；1值表示不生成并行计划。默认值为0.
（5)网络数据包大小：设置整个网络使用的数据包的大小，单位为字节。默认值为4096
字节。如果应用程序经常执行大容量复制操作或者是发送、接收大量的文本数据（text)和图像（image)数据，则可以将此值设置大一点。如果应用程序接收和发送的信息量都很小，那么可以将其设为512字节。
（6)远程登录超时值：指定从远程登录尝试失败返回之前等待的秒数。默认值为20秒，如果设为0,则允许无限期等待。此项设置影响为执行异类查询所创建的与OLEDB访问接口的连接。
（7)两位数年份截止：指定从1753~9999之间的整数，该整数表示将两位数年份解释为四位数年份的截止年份。
（8)默认全文语言：指定全文索引列的默认语言。全文索引数据的语言分析取决于数据的语言。默认值为服务器的语言。
（9)默认语言：指定默认情况下所有新创建的登录名使用的语言。
（10)启动时扫描存储过程：指定SQL Server将在启动时是否扫描并自动执行存储过程。如果设为TRUE,则SQL Server 在启动时将扫描并自动运行服务器上定义的所有存储过程。

配置【权限】

【权限】选项卡用于授予或撤销账户对服务器的操作权限。

【登录名或角色】列表框中显示的是多个可以设置权限的对象。在【显式】列表框中，可以看到【登录名或角色】列表框中对象的权限。在【登录名或角色】列表框中选择不同的对象，在【显式】的列表框中会有不同的权限显示。此处可以为【登录名或角没有色】列表框中的对象设置权限。

SQL Server实例（Instances）

实例介绍（是什么）

An instance of SQL Server consists of all the files, settings, and services required to run SQL Server

SQL Server实例就是SQL Server服务器引擎
每个 SQL Server 数据库引擎实例各有一套不为其他实例共享的系统及用户数据库
在一台计算机上可以安装多个 SQ Server
每个 SQL Server 服务就可以理解为一个实例

局限：一个 SQL Server实例中最多可以指定 32767 个数据库
SQL Server 2000版本支持16个实例
SQL Server 2005之后的版本支持50个实例

实例命名规则

实例又分为"默认实例"和"命名实例"

默认实例（Default Instance）：
默认实例是指SQL Server安装时默认设置的实例名称
每个计算机同时只能运行一个默认实例
默认的实例是MSSQLSERVER
命名实例（Named Instance）：
格式：<计算机名>/<实例名称>
每个计算机可以运行多个不同的命名实例

如果在一台计算机上安装一个SQL Server， 实例命名设置保持默认即可

多实例的好处（Advantages of Instances）

可以在一台机器上安装不同版本的SQL Server
减少成本开销
将产品的维护、开发、测试区分开
减少临时数据库问题
提高安全性，可以分离安全权限

SQL SERVER FILES（文件）

数据库文件

数据库文件是指数据库中用来存放数据库数据和数据库对象的文件，一个数据库可以有一个多个数据库文件，一个数据库文件只能属于一个数据库。当有多个数据库文件时，有一个文件被定为主数据库文件，它用来存储数据库的启动信息和部分或者全部数据，一个数据库只能有一个主数据库文件。数据库文件则划分为不同的页面和区域，页是SQLServer存储数据的基本单位。

主数据文件是数据库的起点，指向数据库文件的其他部分，每个数据库都有一个主要数据文件，其扩展名为：.mdf。次数据文件包含除主数据库文件外的所有数据文件，一个数据库可以没有次数据文件，但也可能有多个次数据文件，次数据文件的扩展名为.ndf。

SQL Server 2019不制使用.mdf、.ndf、.ldf作为文件的扩展名，但建议使用这些扩展名帮助标识文件的用途。在SQL Server 2019中，数据库中的所有文件的位置都记录在master数据库和该数据库的主数据文件中。

日志文件

SQL Server的日志文件是由一系列日志记录组成，日志文件中记录了存储数据库及更新情况等事务日志信息，用户对数据库进行的插入、删除和更新等操作也都会记录在日志文件中。当数据库发生损坏时，可以根据日志文件来分析出错的原因，或者数据丢失时，还可以使用事务日志恢复数据库。每一个数据库至少拥有一个事务日志文件，并且允许拥有多个日志文件。

SQL Server数据库恢复模式
完整
完整记录日志
完整的恢复
大容量日志
当操作容量较大时,只记录操作,不记录数据。可以提高运行效率.
恢复时,只能恢复到最后一次备份到日志中的内容
简单
备份时清除事务日志
根据最后一次对数据库备份进行恢复