8 Essential SQL Server Interview Questions *

Select CHARINDEX(' 0 '，FIRST_NAME,0) from employee where FIRST_NAME ='John'

CHARINDEX is the SQL Server Equivalent of INSTR in Oracle.

有人可能会认为这个SQL语句无效，因为它使用了别名 BillingYear in the ORDER BY phrase. Even though BillingYear cannot be used in the WHERE or GROUP BY phrases, it can be used in the ORDER BY phrase.

按照处理的逻辑顺序列出短语有助于清楚地解释为什么会这样:

FROM Invoices
WHERE CustomerId = 42
GROUP BY YEAR(BillingDate)
HAVING COUNT(*) > 1
选择YEAR(BillingDate)作为BillingYear, COUNT(*)作为numberoffinvoices
ORDER BY BillingYear
TOP 5

Although the reordered statement is not valid T-SQL syntax, 对于候选人来说，能够在头脑中重新排序语句是至关重要的，这样他们就可以在编写复杂查询时解决遇到的任何问题. 大多数T-SQL程序员从不需要显式地记住这个处理顺序. There is a logic to it that becomes very familiar with experience, 所以这个问题是用来衡量候选人的经验的.

It doesn’t. SQL Server永远不会终止连接，除非被明确告知这样做.e., by a user, by a KILL command, 或者由操作系统通知它网络连接已断开).

操作系统终止网络连接所需的时间长度可能差别很大. It largely depends on the net-lib and network protocol used.

Typically, named pipe connections over NetBEUI will time out quickly. Named pipes over IP will time out almost as quickly as well. 如果您使用的是TCP/IP套接字，那么这些会话在默认情况下根本不会超时.

有关排除SQL Server中孤立连接的更多信息，请参阅 here.

For SQL 7.0 and above, 安装在SQL Server程序组中的“服务器网络实用程序”告诉您安装了哪些网络库，并允许您配置新库.

For SQL 6.5 and earlier, 运行SQL setup并选择Configure Server，然后选择Network Support来访问此功能.

For all versions of SQL Server, 您可以查看SQL错误日志中的“监听”行，这些行告诉您正在使用哪些Net库.

It depends.

There are two types of Views in SQL Server. One is a “simple” view that contains data from one table only, 另一种是包含来自多个表的数据的“复杂”视图.

在简单视图中可以对记录执行删除操作，但在复杂视图中不能执行删除操作.

Merge Join和Lookup组件的使用取决于两个表之间的数据关系类型, i.e. a one-to-one relationship or a one-to-many relationship.

Merge Join用于连接来自两个或多个数据源的数据，并将合并的数据插入到一个非规范化的目标数据源中. Merge Join组件接受两个输入源，合并后的数据被传输到单个目标数据输出.

Lookup组件用于SSIS中，其中一个输入记录在目标源中有一个匹配的记录. Both the source and target satisfy the one-to-one relation.

NOLOCK 通过在SQL读取/处理行时不锁定行来提高性能. 应该在访问大型表和包含很少更改数据的表时使用它.

如果创建的锁太多，可以将行级锁升级为块级锁，然后再升级为表级锁. 表锁(尤其是核心表上的表锁)将暂停该数据库实例上的大多数处理，直到SQL操作完成.

NOLOCK 允许查询访问已提交和未提交的数据，并可能返回脏数据. 如果系统没有编码来处理这种情况，脏数据可能会导致数据完整性问题.

Data integrity issues can be avoided by adding a TIMESTAMP column and referencing it in UPDATE and DELETE statements. 这可以防止在查询读取脏数据时发生数据更改. 这需要更多的代码来确定没有发生更新或删除，然后采取适当的操作来恢复. Comparing all original values of every column in a row on every UPDATE or DELETE may be performed instead of verifying the TIMESTAMP column has not changed.

这里的重点是看候选人是否能够将一个复杂的查询分解成更简单的部分. 第一步是找出发票数量最多的客户是谁, taking into account there may be a tie:

SELECT TOP 1 WITH TIES CustomerId
FROM Invoices
GROUP BY CustomerId
ORDER BY COUNT(*) DESC;

现在我们知道了谁是“赢家”，我们可以查询该客户的订单. 为了通过ID查询客户列表的订单，我们可以使用In操作符:

SELECT *
FROM Invoices
WHERE CustomerId IN (…);

现在将第一个查询作为子查询插入到第二个查询中，我们得到:

SELECT * FROM Invoices
WHERE CustomerId IN (
 SELECT TOP 1 WITH TIES CustomerId
 FROM Invoices
 GROUP BY CustomerId
 ORDER BY COUNT(*) DESC
);

极其复杂的查询可以以这种方式分解为子查询, then used as building blocks to produce the final query.