8kb's questions

我试图理解为什么使用表变量会阻止优化器使用索引查找然后书签查找与索引扫描。

填充表格：

CREATE TABLE dbo.Test 
(
    RowKey INT NOT NULL PRIMARY KEY, 
    SecondColumn CHAR(1) NOT NULL DEFAULT 'x',
    ForeignKey INT NOT NULL 
) 

INSERT dbo.Test 
(
    RowKey, 
    ForeignKey
) 
SELECT TOP 1000000 
    ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
    ABS(CHECKSUM(NEWID()) % 10)     
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2 

CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey) 

使用单个记录填充表变量，并尝试通过搜索外键列来查找主键和第二列：

DECLARE @Keys TABLE (RowKey INT NOT NULL) 

INSERT @Keys (RowKey) VALUES (10)

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    @Keys k
ON
    t.ForeignKey = k.RowKey

下面是执行计划：

现在使用临时表进行相同的查询：

CREATE TABLE #Keys (RowKey INT NOT NULL) 

INSERT #Keys (RowKey) VALUES (10) 

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    #Keys k
ON
    t.ForeignKey = k.RowKey

此查询计划使用搜索和书签查找：

为什么优化器愿意使用临时表而不是表变量进行书签查找？

此示例中使用 table 变量来表示来自存储过程中用户定义的表类型的数据。

我意识到如果外键值出现数十万次，则索引搜索可能不合适。在这种情况下，扫描可能是更好的选择。对于我创建的场景，没有值为 10 的行。我仍然认为该行为很有趣，并想知道是否有原因。

SQL小提琴

添加OPTION (RECOMPILE)并没有改变行为。UDDT 有一个主键。

@@VERSION是 SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)（内部版本 7601：Service Pack 1）（管理程序）

我在AdventureWorks2012数据库中运行此查询：

SELECT 
    s.SalesOrderID,
    d.CarrierTrackingNumber,
    d.ProductID,
    d.OrderQty
FROM Sales.SalesOrderHeader s 
JOIN Sales.SalesOrderDetail d 
    ON s.SalesOrderID = d.SalesOrderID
WHERE s.CustomerID = 11077

如果我查看估计的执行计划，我会看到以下内容：

初始索引搜索（右上角）使用 IX_SalesOrderHeader_CustomerID 索引并搜索文字 11077。它估计有 2.6192 行。

如果我使用DBCC SHOW_STATISTICS ('Sales.SalesOrderHeader', 'IX_SalesOrderHeader_CustomerID') WITH HISTOGRAM，则表明值 11077 在两个采样键 11019 和 11091 之间。

11019 和 11091 之间的平均不同行数为 2.619718，或四舍五入为 2.61972，这是为索引查找显示的估计行的值。

我不明白的部分是聚集索引查找 SalesOrderDetail 表的估计行数。

如果我运行DBCC SHOW_STATISTICS ('Sales.SalesOrderDetail', 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID')：

所以 SalesOrderID（我加入）的密度是 3.178134E-05。这意味着 1/3.178134E-05 (31465) 等于 SalesOrderDetail 表中唯一 SalesOrderID 值的数量。

如果 SalesOrderDetail 中有 31465 个唯一的 SalesOrderID，则在均匀分布的情况下，每个 SalesOrderID 的平均行数为 121317（总行数）除以 31465。平均值为 3.85561

因此，如果估计要循环的行数是 2.61972，并且要在 3.85561 中返回平均值，我认为估计的行数将是 2.61972 * 3.85561 = 10.10062。

但估计的行数是 11.4867。

我认为我对第二个估计的理解是不正确的，不同的数字似乎表明了这一点。我错过了什么？

我今天在一些源代码中发现了这个：

        SELECT
            @Error = ERROR_NUMBER (),
            @ErrorMsg = N'An Error occured while populating the TABLE ' + @DestinationTableName +
                        N' with data. Error Code: ' + CAST ( @Error AS nvarchar(20) ) + 
                        N', Error Description: "' + ERROR_MESSAGE () + N'".' 
                        + NCHAR(0X0D) + NCHAR(0X0A) + NCHAR(0X0D) + NCHAR(0X0A),
            @ErrorSeverity = ERROR_SEVERITY (),
            @ErrorState = ERROR_STATE () ;

看起来错误消息字符串在描述后添加了两个换行符。0X0D 是 13，0X0A 是 10。

是否有理由使用十六进制而不仅仅是整数？

通常我所做的是 NCHAR(13) + NCHAR(10)...

我们的许多数据库都将数字跟踪代码（如序列号）存储为整数。这些代码几乎不可能每次都获得字母字符，但它似乎仍然是不正确的数据类型，因为您永远不会对它们执行数学运算。

对于这些类型的标识符，什么是合适的数据类型？

过去，我的团队有一项政策，即任何存储过程修改都需要在两个地方进行注释：

1. 存储过程的顶部，包含所做更改的总体摘要
2. 对进行更改的每一行的评论

它通常看起来像这样：

CREATE PROCEDURE usp_Test
/*****************************************************************
The purpose of this stored procedure is to get data.
Created by 8kb 2001-01-01
Modified: removed OR clauses from JOIN statement..8kb 2001-06-01
******************************************************************/
AS
BEGIN

SELECT * 
FROM t1
JOIN t2
ON t1.colA = t2.colA
-- Removed OR clause..8kb 2001-06-01
-- OR t1.ColB = t2.ColB

END

但现在有了集成的源代码管理，我可以总结源代码管理中的变化，然后使用比较/差异功能来查看新旧版本之间的差异。

在作为集成源代码控制一部分的存储过程中编写逐行更改注释是否仍然有价值？

根据我的分析，我们数据仓库的完整维度模型需要从 200 多个源表中提取。其中一些表将作为增量加载的一部分被提取，而其他表将被完全加载。

需要注意的是，我们有大约 225 个源数据库，它们都具有相同的模式。

据我所知，在 SSIS 中使用 OLE DB 源和 OLE DB 目标构建简单的数据流需要在设计时确定列和数据类型。这意味着我最终将获得 200 多个仅用于提取的数据流。

从可维护性的角度来看，这让我觉得这是一个大问题。如果我需要对提取代码进行某种彻底的更改，我将不得不修改 200 个不同的数据流。

另一种选择是，我编写了一个小脚本，它读取我想从一组元数据表中提取的源数据库、表名和列。代码在多个循环中运行，并使用动态 SQL 通过链接服务器和 OPENQUERY 从源表中提取。

根据我的测试，这仍然不如使用具有 OLEDB 源和目标的 SSIS 数据流快。所以我想知道我有什么样的选择。到目前为止的想法包括：

1. 使用EZAPI以编程方式生成具有简单数据流的 SSIS 包。要提取的表和列将来自前面提到的相同元数据表。
2. 购买第三方软件（动态数据流组件）

解决这个问题的最佳方法是什么？当谈到 .NET 编程时，我是一个初学者，因此仅掌握基础知识所需的时间也是一个问题。

我正在使用一个拥有大约十年客户数据的遗留应用程序。这些数据中的大部分不用于日常运营，但存在业务要求，在客户从系统退休之前，这些数据可供客户使用。

我们正在探索将数据存档到现有数据库的副本，然后在某个时间点之后从生产中清除记录。

我担心的是，由于开发工作，数据库每个季度都会发生重大的架构更改。

如果我要归档数据的镜像副本，我是否还需要应用每一个与生产不相符的更改脚本？

有没有替代策略？似乎无论您选择哪种存储形式（即数据库、平面文件、xml），您总是需要某种方式将旧模式映射到新模式。

假设我有一个简单的 OLTP 数据库，其中包含订单、产品和客户：

从它开始，我正在构建一个包含订单事实表、产品维度、客户维度和日期维度的数据集市：

将订单表加载到 fact_orders 时（假设我使用 SSIS 查找转换来分配代理键），这是否意味着订单的数据源也需要具有关联的自然“外键”值OLTP系统中的订单？

换句话说，正在加载的数据会来自这样的查询吗？

SELECT 
  order_date,                   -- needed to get date surrogate key
  customer_name,                -- needed to get customer surrogate key
  product_name,                 -- needed to get product surrogate key
  order_number,                 -- denegenerate dimension,
  qty_ordered AS order_qty,     -- measure
  total_amount AS order_amount  -- measure
FROM orders o 
  INNER JOIN customers c 
    ON o.customer_id = c.customer_id
  INNER JOIN products p 
    ON o.product_id = p.product_id 

我在同一实例的发布者和订阅者上运行了一些简单的测试：

1. 我在一篇已发表的文章中插入了 50,000 行，数据被正确地推送给了订阅者。
2. 我无意中删除了订阅者上的第 49,985 条记录（但当时没有意识到）
3. 我从发表的文章中删除了 50,000 行

当我观察订户表的大小时，我注意到它的行数几乎下降到零，然后又回到 50,000。删除将再次开始运行，行数将下降，然后又回到 50,000。这种情况一遍又一遍地发生。

我运行了标准跟踪并看到所有删除都在正常运行。差不多完成后，出现了这样的语句：

IF @@TRANCOUNT > 0 ROLLBACK 

然后我修改了跟踪以包含错误消息并看到了这个：

The row was not found at the Subscriber when applying the replicated command.

所以看起来当分发代理正在一个一个地执行所有删除时，该过程仍然包含在一个事务中（原来的 DELETE 是一行）。如果出现错误，它会执行 ROLLBACK，然后重新开始。

我的问题：

1. 有没有标准的方法来跳出循环？
2. 是否可以重新创建丢失的行以完成流程？
3. 如果在生产中发生这种情况，您会怎么做？我想这不是一个真正的问题。但这似乎是一个简单的错误，除非非常仔细地监控复制，否则可能会导致一些相当严重的并发症。

我正在使用从 MS Access 导入的旧数据库。在 MS Access > SQL Server 升级期间创建了大约 20 个具有非群集、唯一主键的表。

其中许多表还具有唯一的非聚集索引，它们是主键的副本。

我正在尝试清理它。

但是我发现在我将主键重新创建为聚集索引之后，然后尝试重建外键，外键引用了旧的重复索引（这是唯一的）。

我知道这一点，因为它不会让我删除重复的索引。

我认为如果存在一个主键，SQL Server 总是会选择一个主键。SQL Server 是否有在唯一索引和主键之间进行选择的方法？

要复制问题（在 SQL Server 2008 R2 上）：

IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Child') DROP TABLE Child
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name = 'Parent') DROP TABLE Parent
GO

-- Create the parent table
CREATE TABLE Parent (ParentID INT NOT NULL IDENTITY(1,1)) 

-- Make the parent table a heap
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY NONCLUSTERED (ParentID) 

-- Create the duplicate index on the parent table
CREATE UNIQUE NONCLUSTERED INDEX IX_Parent ON Parent (ParentID) 

-- Create the child table
CREATE TABLE Child  (ChildID  INT NOT NULL IDENTITY(1,1), ParentID INT NOT NULL ) 

-- Give the child table a normal PKey
ALTER TABLE Child ADD CONSTRAINT PK_Child PRIMARY KEY CLUSTERED (ChildID) 

-- Create a foreign key relationship with the Parent table on ParentID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID) 
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to clean this up
-- Drop the foreign key constraint on the Child table
ALTER TABLE Child DROP CONSTRAINT FK_Child

-- Drop the primary key constraint on the Parent table
ALTER TABLE Parent DROP CONSTRAINT PK_Parent

-- Recreate the primary key on Parent as a clustered index
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY CLUSTERED (ParentID) 

-- Recreate the foreign key in Child pointing to parent ID
ALTER TABLE Child ADD CONSTRAINT FK_Child FOREIGN KEY (ParentID) 
REFERENCES Parent (ParentID) ON DELETE CASCADE NOT FOR REPLICATION

-- Try to drop the duplicate index on Parent 
DROP INDEX IX_Parent ON Parent 

错误信息：

消息 3723，级别 16，状态 6，第 36 行索引“Parent.IX_Parent”上不允许显式 DROP INDEX。它被用于 FOREIGN KEY 约束强制执行。

今天在解决服务代理问题时，我发现数据库所有者是一名离职员工的 Windows 登录名。他的登录名已被删除，因此查询通知失败。

据说处理这个问题的最佳实践是让“sa”成为数据库所有者。我们更改了它并清除了队列。

我的（非常基本的）问题：什么是数据库所有者，它的目的是什么？

每天将大约 200 个 SQL Server 2005 源数据库（相同架构）加载到暂存区域以准备数据仓库清理、重复数据删除和转换的最佳提取策略是什么？

到目前为止，我已经设想了以下可能性：

1. 事务复制：在 2005 年创建 200 个 SQL Server 2008 R2 订阅者从各自的发布者提取数据。在订阅者和影子表中的所需表上启用更改数据捕获，以便对我们的临时数据库执行增量加载。
2. Rowversion：在每个所需的源表上添加一个 rowversion 列，并将其与 SSIS 进程结合使用，以将更改数据直接提取到暂存数据库。
3. BCP 文件：创建一个自动化任务，每晚从所有源表中转储 BCP 文件。使用 SSIS 将这些表作为完全加载（而不是增量）的一部分加载到临时数据库中。

额外的想法：

1. 我对在 200 个数据库上支持全新事务复制拓扑所需的管理和硬件开销感到紧张。
2. 数据库的总组合大小约为 100GB。但其中大部分是事务日志和其他表的一部分，不会在任何事实或维度中使用。换句话说，BCP 文件不会很大，这就是为什么我正在考虑一个完整的提取策略，即使我读过的所有内容都反对它。
3. 我愿意接受建议、文件等。

有许多博客文章和最佳实践文章颂扬将 SQL Server 数据文件放在一个硬盘驱动器上并将事务日志放在另一个硬盘驱动器上的优点。给出的原因是数据库文件将经历随机读取和写入，而事务日志将只有顺序写入。

但是，如果您有数百个数据库怎么办？将数百个事务日志文件放在单独的磁盘上是否真的有性能优势？如果要写入多个事务日志，那么我认为事务日志的写入将与数据库的写入一样随机。

我正在使用一个旧数据库，该数据库已针对名为ColorList的表实现了查询通知。

在为不相关的问题运行服务器端跟踪时，我注意到查询

SELECT color FROM ColorList

每 10 毫秒执行一次。

这是查询通知的工作原理吗？SQL Server 是否存储原始查询的结果，然后无休止地运行查询，直到检测到更改？

在Ralph Kimball 的The Data Warehouse Toolkit的第 2 章 Retail Sales中，作者描述了一个包含三个字段的日期维度，这对我来说似乎完全陌生：

Date Key (PK)
Date
Full Date Description
Day of Week
**Day Number in Epoch**
**Week Number in Epoch**
**Month Number in Epoch**
Day Number in Calendar Month
Day Number in Calendar Year
...

我知道纪元是“选择作为特定时代的时间实例”，J2000.0 的当前标准纪元从 2000 年 1 月 1 日开始。但我看不出这些数字对日期维度有何帮助或用于获取有关零售销售信息的多维数据集。

1. Epoch 中的数字通常用于日期维度吗？
2. 什么时代将用于现代业务？
3. 用户将如何基于 Epoch 对数据进行切片，为什么？

当我为我的存储过程编写单元测试时，它们通常看起来像这样：

/* Unit test for stored procedure which runs a query */

/* Define #temp table with structure of query results */

/* Define and set variables for stored procedure parameters */ 

/* If stored procedure queries from a particular table...*/ 

/* Truncate table and populate with sample data */ 

/* Call stored procedure using variables but insert results into table */ 

/* INSERT #temp EXEC usp_TestProc @P1=1, @P2 = 'X' */

/* Query #temp table to confirm that all fields have certain values */

/* Query #temp table to confirm that certain number of rows exist */ 

/* If results are incorrect, display error message */ 

虽然这会奏效，但在某种程度上感觉笨拙，并且在需求不断变化的长期项目过程中很难维护。

1. 有没有更好的方法（或标准方法）来测试这样的存储过程？
2. 维护的困难是否只是工作的一部分，值得为进行单元测试而付出努力？我不得不说，在我的大部分工作中，DBA 很少首先进行单元测试。

我最近在 SO 上看到了这个答案.nodes()，它在使用. 我以前从未见过这个：

select T.N.value('local-name(.)', 'varchar(128)') as Name,
       T.N.value('.', 'varchar(max)') as Value
from @xml.nodes('/data/*') as T(N);

在哪里可以找到有关诸如.nodes()和之类的命令的权威文档.value()？

BOL中的建议相当模糊：

根据需要经常备份主数据，以充分保护数据以满足您的业务需求。我们建议定期备份计划，您可以在大量更新后补充额外的备份。

如果你进一步冒险，你会发现这些细节：

导致 master 更新并需要进行备份的操作类型包括以下内容：

• 创建或删除用户数据库。
• 如果用户数据库自动增长以容纳新数据，
  则 master 不会受到影响。
• 添加或删除文件和文件组。
• 添加登录或其他与登录安全相关的操作。
• 数据库安全操作，例如向数据库添加用户，不会影响 master。
• 更改服务器范围或数据库配置选项。
• 创建或删除逻辑备份设备。
• 为分布式查询和远程过程调用 (RPC) 配置服务器，例如添加链接服务器或远程登录。

那么，如果我们所有的登录都是通过 Windows 组添加的，并且我们不对数据库进行任何其他更改，这是否意味着对 master 进行一次备份就足够了？

如果不是，主数据库的标准备份间隔是多少？

我有两个 SQL 代理作业，它们计划以不同的时间间隔运行。第一个作业每天运行一次完整备份。第二个作业每十五分钟运行一次事务日志备份。

随着数据库的增长，完整备份所需的时间比最初计划的要长（即使使用压缩）。而且我从我的日志中注意到事务日志备份现在正在同时运行。

我是否应该更改事务日志备份的计划，以便在运行完整备份时不运行？有关系吗？

假设我有两个将经常针对我的数据库运行的查询：

SELECT 
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserID = @User

SELECT 
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserName LIKE @Name + '%

它们应该在两个单独的存储过程中，还是只在一个使用 sp_executesql 动态创建语句的存储过程中？

如果它们在两个存储过程中，那么如果我想在 SELECT 语句中添加或删除列，我将需要修改这两个过程。如果我使用动态 SQL，那么我可能会牺牲少量性能。

在这种情况下，通过使用动态 SQL 可维护性和遵守 DRY 原则（不要重复自己）是否优先于存储过程的性能增益？