合 SQL Server常见面试题
Tags： MSSQLSQL Server面试笔试
- SqlServer2012系统数据类型有哪些？
- WHERE子句与HAVING子句有何不同?
- 试说明游标的种类和用途。
- 举例说明游标的使用方法和步骤。
- 在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
- sqlserver2000是一种大型数据库，他的存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。
- SQL Server简答题
- SQL Server如何获取系统时间？
- SQL Server有Linux版本吗？
- SQL Server如何查看版本？
- SQL Server 数据库如何启动？
- SQL Server有哪些默认的系统数据库？
- SQL Server物理有哪几种类型的文件？
- SQL Server哪类视图是可以更新的？
- SQL Server标准的SQL与T-SQL的区别是什么？
- SQL Server采用什么方法可以保证数据的完整性？
- 登录名、服务器角色、用户名和数据库角色
- SQL Server中的完全备份、差异备份和日志备份的区别是什么？
- SQL Server提供的3种恢复模型都是什么？有什么区别？
- SQL Server数据库有哪3类触发器？
- SQL Server中的锁
- SQL Server选择题
- SQL Server有哪些高可用方案
SQL Server数据库也是一种比较重要的数据库，在面试笔试中也会经常涉及到，因此，有必要对这种数据库有一些基本的了解。SQL Server部分内容较少，也比MySQL和Oracle简单，最核心的内容还是公共部分的SQL查询，应该以此为重点。
SqlServer2012系统数据类型有哪些？
| 数据类型 | 符号标识 |
|---|---|
| 数据类型 | bigint、int、smallint、tinyint |
| 整数型精确数值型 | decimal、numeric |
| 浮点型 | float、real |
| 货币型 | money、smallmoney |
| 位型 | bit |
| 字符型 | char、varchar、varchar(MAX) |
| Unicode字符型 | nchar、nvarchar、nvarchar(MAX) |
| 文本型 | text、ntext |
| 日期时间类型 | datetime、smalldatetime、date、time、datetime2、datetimeoffset |
| 时间截型 | timestamp |
| 图像型 | image |
| 其他 | cursor、sql_variant、table、uniqueidentifier、xml、hierarchyi |
WHERE子句与HAVING子句有何不同?
Where是一个约束声明，是在查询结果集返回之前约束来自数据库的数据，且Where中不能使用聚合函数。
Having是一个过滤声明，是在查询结果集返回以后对查询结果进行的过滤操作，在Having中可以使用聚合函数。
试说明游标的种类和用途。
种类：前端（客户端）游标、后端（服务器端）游标
用途：游标提供了对一个结果集进行逐行处理的能力，游标可看做一种特殊的指针，它与某个查询结果相联系，可以指向结果集的任意位置，以便对指定位置的数据进行处理。
举例说明游标的使用方法和步骤。
- 声明游标
- 打开游标
- 读取数据
- 关闭游标
- 删除游标
在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
用户临时表:create table #xx(ID int, IDValues int)
系统临时表:create table ##xx(ID int, IDValues int)
区别:
用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
当创建它的进程消失时这个临时表就自动删除.
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
sqlserver2000是一种大型数据库，他的存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。
它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的.
SQL Server 2000 数据库有三种类型的文件：
主要数据文件
主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。
次要数据文件
次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
日志文件
日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。
SQL Server简答题
真题1、请写出四条最基本SQL语句。
答案：SELECT * FROM 表名;
INSERT INTO表名(字段,字段,…);
UPDATE表名SET (字段=值,字段＝值,…) WHERE (条件);
DELETE FROM 表名 WHERE (条件);
真题2、学生信息管理系统中有张表STUDENT，其中，有字段ID、NAME、SEX、BIRTH，请回答如下问题：
（1）找出NAME相同的学生（用一句SQL语句）。
（2）用一句SQL语句把学生SEX为男的改为女，女的改为男。
答案：（1）SELECT * FROM STUDENT WHERE NAME IN (SELECT NAME FROM STUDENT GROUP BY NAME HAVING COUNT(NAME)>1);
（2）UPDATE STUDENT SET SEX = CASE SEX WHEN '男' THEN '女' ELSE '男' END;
真题3、SQL Server、Access、Oracle三种数据库之间的区别是什么？
答案：Access是一种桌面数据库，只适合于数据量少的应用系统，在处理少量数据和单机访问的数据时是很好的，效率也很高。但是Access数据库有一定的极限，如果数据达到100M左右，那么很容易造成Access假死，或者消耗掉服务器的内存导致服务器崩溃。
SQL Server是基于服务器端的中型的数据库，可以适合大容量数据的应用。在处理海量数据的效率，后台开发的灵活性，可扩展性等方面强大。因为现在数据库都使用标准的SQL语言对数据库进行管理，所以，如果是标准SQL语言，那么两者基本上都可以通用的。SQL Server还有更多的扩展，可以用存储过程、函数等。
Oracle是基于服务器的大型数据库，主要应用于银行、证券类业务等。
真题4、SQL Server的两种存储结构是什么？
答案：SQL Server的两种存储结构是页与区间。
（1）页：用于数据存储的连续的磁盘空间块，SQL Server中数据存储的基本单位是页，磁盘I/O操作在页级执行，页的大小为8KB，每页的开头是96字节的页头，用于存储有关页的系统信息，包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元ID。
（2）区间：区是管理空间的基本单位，一个区是8个物理上连续的页（即64KB)的集合，所有页都存储在区中。SQL Server有两种类型的区：统一区和混合区。
l 统一区：由单个对象所有，区中的所有8页只能由一个对象使用。
l 混合区：最多可由8个对象共享。区中8页中的每页可以由不同对象所有，但是一页总是只能属于一个对象。
\&*** *说明：****
有关SQL Server页和区间的更多内容可以参考我的博客：http://blog.itpub.net/26736162/viewspace-2142922/。
真题5、描述下ASP.NET中使用DataReader对象和DataSet对象的区别？
答案：ASP.NET是一个统一的Web开发模型，它包括使用尽可能少的代码生成企业级Web应用程序所必需的各种服务。ASP.NET作为.NET Framework的一部分提供。当编写ASP.NET应用程序的代码时，可以访问.NET Framework中的类。可以使用与公共语言运行库（CLR）兼容的任何语言来编写应用程序的代码，这些语言包括Microsoft Visual Basic、C#、JScript.NET和J#。使用这些语言，可以开发利用公共语言运行库、类型安全、继承等方面的优点的ASP.NET应用程序。
DataReader对象是只向前的连接数据读取器，使用它可以快速有效地访问数据。DataSet对象提供一个内存中数据的关系表示形式，一整套包括一些表在内的数据（这些表包含数据、对数据进行排序并约束数据），以及表之间的关系．DataReader对象会使得SQL Server的连接处于忙碌桩，以便只有在关闭DataReader之后才可以在这个连接上执行其他操作。DataSet对象可以看作是DATATABLE对象的容器，可以在数据库操作时将多个表的所有数据都放在DataSet中，用在单独一次调用中将其返回，避免了重复调用的过程。DataSet对象的调用将在内存开辟一个虚拟的表，因此将占有相当的内存空间。
真题6、SQL Server如何查询阻塞？
答案：SQL Server的阻塞查询主要来自sys.sysprocesses。通常在处理时需要加入其它相关的视图或表，例如如sys.dm_exec_connections，sys.dm_exec_sql_text。通过如下几个语句的查询，可以找到阻塞的语句。
SELECT BL.SPID BLOCKING_SESSION,
BL.BLOCKED BLOCKED_SESSION,
ST.TEXT BLOCKEDTEXT
FROM (SELECT SPID, BLOCKED
FROM SYS.SYSPROCESSES A
WHERE BLOCKED > 0
AND NOT EXISTS (SELECT 1
FROM SYS.SYSPROCESSES B
WHERE BLOCKED > 0
AND A.BLOCKED = B.SPID)
UNION
SELECT SPID, BLOCKED
FROM SYS.SYSPROCESSES
WHERE BLOCKED > 0) BL,
(SELECT T.TEXT, C.SESSION_ID
FROM SYS.DM_EXEC_CONNECTIONS C
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) ST
WHERE BL.BLOCKED = ST.SESSION_ID;
或：
SELECT A.BLOCKING_SESSION_ID, A.WAIT_DURATION_MS, A.SESSION_ID, B.TEXT
FROM SYS.DM_OS_WAITING_TASKS A,
(SELECT T.TEXT, C.SESSION_ID
FROM SYS.DM_EXEC_CONNECTIONS C
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) B
WHERE A.SESSION_ID = B.SESSION_ID
AND A.BLOCKING_SESSION_ID IS NOT NULL;
包含阻塞与被阻塞的SQL脚本：
SELECT BL.SPID BLOCKING_SESSION,
BL.BLOCKED BLOCKED_SESSION,
ST.TEXT BLOCKEDTEXT,
SB.TEXT BLOCKINGTEXT
FROM (SELECT SPID, BLOCKED
FROM SYS.SYSPROCESSES A
WHERE BLOCKED > 0
AND NOT EXISTS (SELECT 1
FROM SYS.SYSPROCESSES B
WHERE BLOCKED > 0
AND A.BLOCKED = B.SPID)
UNION
SELECT SPID, BLOCKED
FROM SYS.SYSPROCESSES
WHERE BLOCKED > 0) BL,
(SELECT T.TEXT, C.SESSION_ID
FROM SYS.DM_EXEC_CONNECTIONS C
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) ST,
(SELECT T.TEXT, C.SESSION_ID
FROM SYS.DM_EXEC_CONNECTIONS C
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(C.MOST_RECENT_SQL_HANDLE) T) SB
WHERE BL.BLOCKED = ST.SESSION_ID
AND BL.SPID = SB.SESSION_ID;
查询死锁：
SELECT *
FROM MASTER ..SYSPROCESSES
WHERE DB_NAME(DBID) = '数据库名'
AND SPID <> @@SPID
AND DBID <> 0
AND BLOCKED > 0;
真题7、在SQL Server中，请用SQL创建一张本地临时表和全局临时表，里面包含两个字段ID和IDVALUES，类型都是INT型，并解释下两者的区别？
答案：在SQL Server中，临时表有两种类型：本地临时表和全局临时表。临时表与永久表相似，但临时表存储在Tempdb中，当不再使用时会自动删除。本地临时表只对创建这个表的用户的SESSION可见，对其它进程是不可见的。当创建它的进程消失时这个临时表就自动删除。本地临时表的名称以单个数字符号（#）打头。全局临时表对整个SQL Server实例都可见，但是所有访问它的SESSION都消失的时候，它也自动删除。全局临时表的名称以两个数字符号（##）打头。它们的创建语句如下所示：
本地临时表：CREATE TABLE #XX(ID INT, IDVALUES INT);
全局临时表：CREATE TABLE ##XX(ID INT, IDVALUES INT);
\&*** *说明：****
有关SQL Server中的临时表的更多内容可以参考我的博客：http://blog.itpub.net/26736162/viewspace-2143061/
SQL Server如何获取系统时间？
利用函数GETDATE可以获取系统时间，查询语句为SELECT GETDATE()。实际运行过程如下图所示：
图 5-1 SQL Server获取系统时间
SQL Server有Linux版本吗？
微软在2016年推出了Linux系统的SQL Server预览版，并将于2017年全面发布这款产品。微软云计算和企业业务负责人斯科特●格里斯（Scott Guthrie）表示，该公司将会推出本地版和云计算版两个版本。格里斯称，Linux版SQL Server将包含SQL Server 2016中的Stretch Database功能，但该公司并未明确披露其它新技术是否也会整合到Linux版中，而微软发言人证实，不会将SQL Server 2016的所有功能都引入Linux，只会提供“核心关系型数据库功能”。
红帽产品和技术总裁保罗·康美尔（Paul Cormier）表示，该公司将在红帽企业版Linux中提供SQL Server。Canonical创始人马克·沙特沃斯（Mark Shuttleworth）也表示，Ubuntu开发者也将可以使用SQL Server数据库。微软还在预览版页面上披露，Linux版SQL Server已经可以在Ubuntu中使用。微软发言人表示，预览版已经支持Ubuntu，该公司今后还将支持红帽企业版Linux和其它平台。微软为了在Linux上使用SQL Server，创建了SQL平台抽象层（SQLPAL，SQL Platform Abstraction Layer）。正因为有了这个SQL平台抽象层更加加快了SQL Server的移植速度。
2017和2019的linux版本已发布，可以参考：https://www.dbaup.com/wsl2zhonganzhuangdockerbingkuaisugoujianduozhongshujukuceshihuanjing.html#SQL_Server
SQL Server如何查看版本？
方法一：图形界面查询：
图 5-2 查看SQL Server版本
图 5-3 查看SQL Server版本
方法二：可以通过SQL语句查询来获取数据库的版本，SQL语句为：SELECT @@VERSION，如下所示：
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.1
下图为SQL语句查看SQL Server版本的截图：
图 5-4 SQL语句查看SQL Server版本
SQL Server 数据库如何启动？
在Windows服务控制台里手动启动，这个也是最常用的方式。键入“Win+R”键打开运行窗口，然后输入services.msc打开服务窗口，如下所示：
图 5-5 使用服务控制台启动
另外，SQL Server自己提供的启动方式可以手动启动。
图 5-6 启动SQL Server
在SQL Server的SSMS里面手动启动它，利用这种方式可以进行手动重启数据库。
另外，通过Windows命令窗口，可以使用“net start mssqlserver”命令手动启动SQL Server数据库。
SQL Server有哪些默认的系统数据库？
总体而言，SQL Server有如下4个默认的数据库：Master、Model、Tempdb和Msdb。
（1）Master
Master数据库（主数据库）保存放在SQL Server实体上的所有数据库元数据的详细信息，它还是将引擎固定起来的粘合剂。由于如果不使用Master数据库，那么SQL Server就不能启动，所以，必须要小心地管理好这个数据库。因此，对这个数据库进行常规备份是十分必要的。这个数据库还包括了诸如系统登录、配置设置、已连接的Server信息、扩展存储过程等。
（2）Model
Model数据库（模型数据库）是一个用来在实体上创建新用户数据库的模版数据库，可以把任何存储过程、视图、用户等放在模型数据库里，这样在创建新数据库的时候，新数据库就会包含存放在模型数据库里的所有对象了。
（3）Tempdb
Tempdb数据库存有临时对象，例如全局和本地临时表和存储过程。这个数据库在SQL Server每次重启的时候都会被重新创建，而其中包含的对象是依据模型数据库里定义的对象被创建的。除了这些对象，Tempdb还存有其它对象，例如表变量、来自表值函数的结果集，以及临时表变量。由于Tempdb会保留SQL Server实体上所有数据库的对象类型，所以，对数据库进行优化配置是非常重要的。
（4）Msdb
Msdb数据库用来保存数据库备份、SQL Agent信息、DTS程序包和SQL Server任务等信息，以及诸如日志转移这样的复制信息。
从SQL Server Studio中可以查看所有的数据库，如下图所示：
真题1、在SQL Server中，（）保存所有的临时表和临时存储过程
A、master数据库 B、tempdb数据库 C、model数据库 D、msdb数据库
答案：B。
SQL Server物理有哪几种类型的文件？
SQL Server数据库文件组成如下所示：
（1）主数据文件：默认扩展名为.mdf。
（2）辅助数据文件：默认扩展名为.ndf（一个数据库可以创建多个.ndf文件）。
（3）事务日志文件：默认扩展名为.ldf（记录对数据库的所有操作，但不包含所操作的数据）。
所有的数据文件和日志文件默认位置在C:/Program Files/Microsoft SQL Server/MSSQL.n/MSSQL/Data（其中，n是标识已安装的SQL Server实例名称_实例名）。需要注意的是，应当将所有的数据和对象存储在.ndf文件中，而.mdf文件只负责存储数据目录，这样可以有效地避免访问时的磁盘争用。
物理文件组成也可以参考下图：
SQL Server哪类视图是可以更新的？
SQL Server 2000有两种方法增强可更新视图的类别：
（1）INSTEAD OF触发器
可以在视图上创建INSTEAD OF触发器，从而使视图可更新。当对一个定义了INSTEAD OF触发器的视图执行操作的时候，实际上执行的是触发器中定义的操作，而不是触发了触发器的数据修改语句。因此，如果在视图上存在INSTEAD OF触发器，那么通过该语句可更新相应的视图。
（2）分区视图
如果视图属于“分区视图”的指定格式，那么该视图的可更新性将受到限制。如果视图没有INSTEAD OF触发器，或者视图不是分区视图，那么视图只有满足下列条件才可更新：
l SELECT_statement在选择列表中没有聚合函数，也不包含TOP、GROUP BY、UNION或DISTINCT子句。
l SELECT_statement的选择列表中没有派生列。派生列是由任何非简单列表达式（使用函数、加法或减法运算符等）所构成的结果集列。
l SELECT_statement中的FROM子句至少引用一个表。SELECT_statement必须不能只包含非表格格式的表达式（即不是从表派生出的表达式）。例如，以下视图是不可更新的：
CREATE VIEW NoTable AS
