SQL Server基础知识

SQL Server 简介

SQL Server 是微软的一种关系型数据库管理系统，也是最流行的企业级数据库管理系统之一。它拥有优秀的可靠性、安全性、抗并发能力及性能，被广泛应用于网络、商务、财务、分析及其他工业领域。是一个全面的数据库平台，使用集成的商业智能(BI)工具提供了企业级的数据管理。

SQL Server 被设计为在中央服务器上运行，使多个使用者可以同时访问相同的数据，用户通常通过应用程序访问数据库。数据库引擎为关系型数据和结构化数据提供了更可靠安全的存储功能，使用户能够搭建和管理用于业务流程的高可用性和性能卓越的程序。

SQL Server 构建在SQL之上，SQL是一种用于与关系数据库交互的标准编程语言。SQL Server与Transact-SQL或T-SQL相关联，T-SQL是Microsoft的SQL实现，添加了一组专有的编程结构。

SQL Server 版本

Enterprise：作为高级产品/服务，SQL Server EnterpriseEdition提供了全面的高端数据中心功能，具有极高的性能和无限虚拟化，还具有端到端商业智能，可为任务关键工作负载和最终用户访问数据见解提供高服务级别。

Standard：提供了基本数据管理和商业智能数据库，使部门和小型组织能够顺利运行其应用程序并支持将常用开发工具用于内部部署和云部署，有助于以最少的IT资源获得高效的数据库管理。

Web：对于Web主机托管服务提供商（包括在Azure上的IaaS上选择Web版）和WebVAP而言，SQL Server Web版本是一项总拥有成本较低的选择，可针对从小规模到大规模Web资产等内容提供可伸缩性、经济性和可管理性能力。

Developer：支持开发人员基于SQL Server 构建任意类型的应用程序。它包括Enterprise版的所有功能，但有许可限制，只能用作开发和测试系统，而不能用作生产服务器。SQL Server Developer是构建和测试应用程序的人员的理想之选。

ExpressEdition：是入门级的免费数据库，是学习和构建桌面及小型服务器数据驱动应用程序的理想选择。它是独立软件供应商、开发人员和热衷于构建客户端应用程序的人员的最佳选择。如果您需要使用更高级的数据库功能，则可以将SQL Server Express无缝升级到其他更高端的SQL Server 版本。SQL Server ExpressLocalDB是Express版本的一种轻型版本，该版本具备所有可编程性功能，在用户模式下运行，并且具有快速零配置安装和必备组件要求较少的特点。

SQL Server 数据库引擎服务

SQL Server 作为服务在操作系统上运行。服务是一种在系统后台运行的应用程序。服务通常提供一些核心操作系统功能，例如Web服务、事件日志或文件服务。运行的服务可以不在计算机桌面上显示用户界面。SQL Server 数据库引擎、SQL Server 代理，以及其他几个SQL Server 组件作为服务运行。这些服务通常会在操作系统启动时自动启动。但是，也有些服务默认情况下不会自动启动，这取决于安装过程中如何进行指定。本部分说明了如何管理各种SQL Server 服务。登录到SQL Server 实例之前，需要了解如何启动、停止、暂停、恢复和重新启动SQL Server 实例。登录成功之后，就可以执行各种任务，如管理服务器或查询数据库。

启动SQL Server 数据库引擎实例时，即启动了SQL Server 服务。启动SQL Server 服务之后，用户便可以与服务器建立新的连接。SQL Server 服务可以在本地或通过远程方式作为服务来启动和停止。如果SQL Server 服务是默认实例，则被称为SQL Server (MSSQL SERVER )。如果它是命名实例，则被称为MSSQL$<实例名>。

SQL Server 配置管理器可以停止、启动、或暂停各种SQL Server 服务。

SQL Server 服务作用

SQL Server (MSSQL SERVER)：数据库引擎服务。该服务必须要开启，否则SQL Server 将无法使用。

SQL Server 代理(MSSQL SERVER)：代理服务。如果有一些自动运行的，定时作业，或者是一些维护计划，比如定时备份数据库等操作，那么就要开启服务，否则，就不会备份数据库。

SQL Server AnalysisServices(MSSQL SERVER)：分析服务。如果做多位分析和数据挖掘，那么就要开启服务，一般不需要开启。

SQLFull-textFilterDaemonLauncher(MSSQL SERVER)：全文检索服务。如果需要使用全文检索技术，那么就要开启服务，一般不需要开启。

SQL Server VSSWriter：SQL编写器服务。允许备份和还原应用程序以便在影拷贝服务框架(VolumeShadowCopyService，也叫VSS)框架中进行操作。该框架能让用户在没有IT专业人员协助的情况下，更轻松地恢复丢失的文件。服务器上的所有SQL实例只有一个SQL编写器服务实例。一般不需要开启，因为在SQL Server 中，还是要通过常规的数据库备份、日志备份来保证数据的安全。

SQL Server Browser

背景

在SQL Server 2000(8.x)之前，一台计算机上只能安装一个SQL Server 实例。SQL Server 侦听1433端口上的传入请求，该端口由官方的Internet号码分配机构(IANA)分配给SQL Server 。只有SQL Server 的一个实例可以使用端口，因此，在SQL Server 2000(8.x)引入了对多个SQL Server 实例的支持时，SQL Server 解析协议(SSRP)是为侦听UDP端口1434而开发的。此侦听器服务使用已安装实例的名称以及该实例使用的端口或命名管道响应客户端请求。

为了解决SSRP系统的限制，SQL Server 2005(9.x)引入了SQL Server Browser服务来替换SSRP。

工作原理

启动一个SQL Server 实例后，如果
为SQL Server 启用了TCP/IP协议，服务器将分配到一个TCP/IP端口。若已启用命名管道协议，SQL Server 将侦听特定的命名管道。该特定实例将使用此端口（或“管道”）与客户端应用程序交换数据。在安装过程中，TCP1433端口和管道\sql\query将分配给默认实例，但服务器管理员可以随后使用SQL Server 配置管理器进行更改。

由于只有一个SQL Server 实例可以使用端口或管道，因此，会将不同的端口号和管道名称分配给命名实例，包括SQL Server Express。默认情况下，命名实例和SQL Server Express在启用时便配置为使用动态端口，也就是说，当SQL Server 启动时就分配了可用端口。

如果需要，可以为SQL Server 实例分配特定端口。连接时，客户端可以指定特定端口，但是如果端口是动态分配的，端口号可能会在重新启动SQL Server 时被更改，因此正确的端口号对于客户端来说是不确定的。

在启动后，SQL Server 浏览器将启动并使用UDP1434端口。SQL Server 浏览器将读取注册表，识别计算机上的所有SQL Server 实例，并注明它们使用的端口和命名管道。当一台服务器具有两个或多个网卡时，SQL Server 浏览器会为SQL Server 返回其遇到的第一个已启用的端口。SQL Server 浏览器支持ipv6和ipv4。

当SQL Server 客户端请求SQL Server 资源时，客户端网络库将使用1434端口向服务器发送一条UDP消息。SQL Server 浏览器将用请求的实例的TCP/IP端口或命名管道做出响应。然后，客户端应用程序中的网络库将使用所需实例的端口或命名管道向服务器发送请求来完成连接。

使用

当SQL Server Browser服务不运行时，如果提供了正确的端口号或命名管道，仍可以连接到SQL Server 。例如，如果SQL Server 的默认实例在1433端口上运行，则可以使用TCP/IP连接到此默认实例。

但是，如果SQL Server Browser服务未运行，则以下连接无效：

1.在未完全指定所有参数（例如TCP/IP端口或命名管道）的情况下，组件尝试连接到命名实例。

2.生成或传递其他组件随后要用来进行重新连接的服务器/实例信息的组件。

3.未提供端口号或管道就连接到命名实例。

4.在未使用TCP/IP1433端口的情况下，将DAC连接到命名实例或默认实例。

5.OLAP重定向程序服务。

6.枚举SQL Server ManagementStudio或AzureDataStudio中的服务器。

如果在客户端服务器方案中使用SQL Server （例如，应用程序通过网络访问SQL Server ），那么，若要停止或禁用SQL Server Browser服务，必须为每个实例分配一个特定端口号，并编写客户端应用程序代码以便始终使用该端口号。此方法存在两个问题：必须更新和维护客户端应用程序代码才能确保它连接到正确的端口以及如果服务器上的其他服务或应用程序可以使用您为每个实例选择的端口，则会导致SQL Server 实例不可用。

SQL Server 存储过程

SQL Server 中的存储过程是由一个或多个Transact-SQL语句或对Microsoft.NETFramework公共语言运行时(CLR)方法的引用构成的一个组。过程与其他编程语言中的构造相似，这是因为它们都可以接受输入参数并以输出参数的格式向调用程序返回多个值；可以包含用于在数据库中执行操作的编程语句；可以向调用程序返回状态值，以指明成功或失败（以及失败的原因）。

使用存储过程的好处

1.减少了服务器/客户端网络流量：过程中的命令作为代码的单个批处理执行。这可以显著减少服务器和客户端之间的网络流量，因为只有对执行过程的调用才会跨网络发送。如果没有过程提供的代码封装，每个单独的代码行都不得不跨网络发送。

2.更强的安全性：多个用户和客户端程序可以通过过程对基础数据库对象执行操作，即使用户和程序对这些基础对象没有直接权限。过程控制执行哪些进程和活动，并且保护基础数据库对象。这消除在了单独的对象级别授予权限的要求，并且简化了安全层。

3.可执行某些数据库活动：可在CREATEPROCEDURE语句中指定EXECUTEAS子句以便实现对其他用户的模拟，或者使用户或应用程序无需针对基础对象和命令的直接权限。

4.恶意用户无法看到数据：在通过网络调用过程时，只有对执行过程的调用是可见的。因此，恶意用户无法看到表和数据库对象名称、嵌入自己的Transact-SQL语句或搜索关键数据。

5.有助于避免SQL注入攻击：因为参数输入被视作文字值而非可执行代码，所以，攻击者将命令插入过程内的Transact-SQL语句并损害安全性将更为困难。

6.代码的重复使用：任何重复的数据库操作的代码都非常适合于在过程中进行封装。这消除了不必要地重复编写相同的代码、降低了代码不一致性，并且允许拥有所需权限的任何用户或应用程序访问和执行代码。

7.更容易维护：在客户端应用程序调用过程并且将数据库操作保持在数据层中时，对于基础数据库中的任何更改，只有过程是必须更新的。应用程序层保持独立，并且不必知道对数据库布局、关系或进程的任何更改的情况。

8.提高了性能：默认情况下，在首次执行过程时将编译过程，并且创建一个执行计划，供以后的执行重复使用。因为查询处理器不必创建新计划，所以，它通常用更少的时间来处理过程。

存储过程的类型

用户定义：用户定义的过程可在用户定义的数据库中创建，或者在除了Resource数据库之外的所有系统数据库中创建。该过程可在Transact-SQL中开发，或者作为对Microsoft.NETFramework公共语言运行时(CLR)方法的引用开发。

临时：临时过程是用户定义过程的一种形式。临时过程与永久过程相似，只是临时过程存储于tempdb中。临时过程有两种类型：本地过程和全局过程。它们在名称、可见性以及可用性上有区别。本地临时过程的名称以单个数字符号(#)开头；它们仅对当前的用户连接是可见的；当用户关闭连接时被删除。全局临时过程的名称以两个数字符号(##)开头，创建后对任何用户都是可见的，并且在使用该过程的最后一个会话结束时被删除。

系统：系统过程是SQL Server 随附的。它们物理上存储在内部隐藏的Resource数据库中，但逻辑上出现在每个系统定义数据库和用户定义数据库的sys架构中。此外，msdb数据库还在dbo架构中包含用于计划警报和作业的系统存储过程。因为系统过程以前缀sp_开头，所以，我们建议你在命名用户定义过程时不要使用此前缀。

扩展的用户定义：扩展过程允许使用编程语言（例如C）创建外部例程。这些过程是指SQL Server 的实例可以动态加载和运行的DLL。

SQL Server 的优缺点

优点：

1.便捷性、合适分布式系统的可伸缩性、用以决策支持的数据服务、与很多别的服务器软件密不可分的集成性、优良的性价比等。

2.为用户的数据管理与分析提供了协调能力，容许企业在迅速转变的环境中快速响应，从而提高核心竞争力，获取竞争方面的优势。

3.具备完全web支持的数据库系统，提供了了对可拓展编译语言(XML)的核心支持及其在Internet上和服务器防火墙外进行查询的能力。

缺点：

1.开放性不够好，只有运行在windows平台才能获得最大的性能支撑。

2.并行处理执行和共存模型并不成熟，难以解决日渐增加的用户量和数据信息，伸缩性比较有限，和同类数据库比缺点显著。

3.因为SQL Server 彻底重写了SQL语言的底层编码，经历了长期性的检测，不断延迟，很多功能需要时间来证明，并不十分适配早期的产品，在应用上存在一定风险性。