Jon of All Trades's questions

MySQL 文档指出：

当您在表上定义 PRIMARY KEY 时，InnoDB 将其用作聚集索引。

但这不是唯一的可能性，您可以改为使用唯一索引进行聚类：

如果你没有为你的表定义 PRIMARY KEY，MySQL 会定位第一个唯一索引，其中所有键列都不是 NULL，InnoDB 将它用作聚集索引。

似乎可以这样说，如果我首先在表上创建唯一索引，它将被标记为聚集；然后我可以创建一个主键，它将是非聚集的：

CREATE TABLE Tmp_CUQTest
(
ID1 INT NOT NULL,  -- Desired clustering field
ID2 INT NOT NULL   -- Desired PK field
);

CREATE UNIQUE INDEX CUC_Tmp ON Tmp_CUQTest (ID1);
ALTER TABLE Tmp_CUQTest ADD CONSTRAINT PRIMARY KEY PK_Tmp (ID2);

但是，当我检查结果表时，我的希望破灭了：

Key      Type               Uni  Columns
PRIMARY  BTREE (clustered)  YES  ID2
ID1      BTREE              YES  ID1

DBA.SE 上的另一个问题意味着创建索引作为CREATE TABLE脚本的一部分，而不是之后，作为单独的CREATE INDEX语句，可能会有所作为。但是，我得到相同的结果。

有没有办法强制 MySQL 在我选择的唯一索引上进行集群，而不是主键，或者这个“后备选项”是否只适用于没有主键的表？

我需要计算连续值的序列号。这听起来像是一份工作ROW_NUMBER()！

DECLARE @Data TABLE
    (
    Sequence    TINYINT NOT NULL PRIMARY KEY,
    Subset  CHAR(1) NOT NULL
    )
INSERT INTO @Data (Sequence, Subset) VALUES
    (1, 'A'),
    (2, 'A'),
    (3, 'A'),
    (4, 'B'),  -- New subset
    (5, 'B'),
    (6, 'A')   -- New subset

SELECT
    Sequence, Subset,
    ROW_NUMBER() OVER (PARTITION BY Subset ORDER BY Sequence) AS SeqWithinGroup
FROM
    @Data

我希望该PARTITION子句在每次更改时重置计数Subset，但 SQL Server 会收集给定Subset值的所有值并对它们进行编号。这是我所期望的，也是我得到的：

Sequence Subset Expected Actual
-------- ------ -------- -----
1        A      1        1
2        A      2        2
3        A      3        3
4        B      1        1
5        B      2        2
6        A      *1*      *4*

当 SQL 到达第 6 行时，它继续对子集“A”进行编号，而我将其视为恰好也被命名为“A”的新子集的第一行。

有没有办法ROW_NUMBER()严格地进行分区，而不是默认行为？

这里和其他地方有很多关于用 SQL 计算连续值的问题。但是，我还没有看到解决PARTITION BY字段中重复值的问题。大多数只处理增加的值，通常是日期。

我想从 SQL Server 中访问 MySQL 数据库。我创建了一个系统 DSN，它测试正常。我在 SQL Server 中创建了一个链接服务器：

• 提供程序 = 用于 ODBC 驱动程序的 Microsoft OLE DB 提供程序
• 产品名称 = MySQL
• 数据源 =（DSN 的名称）
• 安全性 = 我的每个本地登录都有一个条目，带有我的 MySQL 用户名和密码

但是，如果我尝试对其进行测试，则会收到以下错误消息：

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked
server "X".
OLE DB provider "MSDASQL" for linked server "X" returned message "[MySQL][ODBC
5.3(w) Driver]Access denied for user 'me'@'localhost' (using password: NO)".
(Microsoft SQL Server, Error: 7303)

密码是绝对设置好的，并且在 MySQL Workbench 中工作得很好。它是 MySQL 的管理员。两台机器都是本地的。我尝试将我的凭据添加为默认值（“对于不在列表中的登录，请使用此安全上下文”）；没变。

我将我的 DSN 设置为 64 位，匹配我的操作系统，使用“MySQL ODBC 5.3 Unicode 驱动程序”，我收集到它是随 MySQL 引擎和/或 MySQL Workbench 一起安装的。32 位 ODBC 应用程序提供常用的葡萄牙语驱动程序（为什么是葡萄牙语？！） - Access、dBase、Excel、Paradox 等。

亚马逊文档声明您无法增加分配给 SQL Server RDS 实例的存储空间：

请注意，由于附加到 Windows Server 环境的条带存储的限制，您无法更改 SQL Server 数据库实例的存储容量或存储类型。

哦，好吧，所以我想您拍摄快照并将其还原到具有更多存储空间的新实例......但这似乎也是不可能的：

从数据库快照还原时，Amazon RDS 不支持更改 Microsoft SQL Server 数据库实例的存储配置。

该引用可能是指在普通 SSD 和预置 I/O 之间进行更改，但没有--allocated-storage 参数for restore-db-instance-from-db-snapshot，因此在恢复快照时似乎是不可能的。就此而言，只需指定新的 IOPS 数量就会出现错误：

无法在 Windows 操作系统中恢复此实例，因为请求的 iops 与备份不同。

增加分配给 SQL Server 实例的存储空间最方便的方法是什么？在这种情况下，没有什么比数据库迁移服务更好的了？

Amazon 支持确认 S3 和 DMS 是 SQL Server 的唯一选项。

这个问题已在 SE上提出。我暂时打开了这个问题；如果Meta中达成共识，我们不应该重复SO 上其他地方存在的 DBA 问题，我将删除这个问题。

2019：自从我提出这个问题以来，亚马逊似乎解除了一些限制。我无需采取任何特殊步骤即可调整 SQL Server 实例的大小：只需编辑配置并允许它重新启动。

我最近为 RDS 数据库调整了数据和日志文件的大小，只留下了几 GB 的未分配空间。此后不久，分配了 1 GB 的误差范围，然后又解除了分配。这可能是什么原因造成的？

所有数据库（包括 tempdb）要么很小 (MB)，要么与设置的大小完全相同；它们加起来为 197 GB，在 200 GB 实例上剩余 3 GB 可用空间。但是：

显然，如果这种情况再次以稍微大一点的幅度发生，就会出现问题。现在，我可以简单地缩小其中一个数据库文件以提供更大的余量，但我想了解实际发生的情况。

在处理碎片磁盘空间时，我将第二个数据文件添加到我的一个数据库中。大多数数据现在都在第二个文件中，我想删除第一个文件以简化事情。但是，它是主要数据文件：

Msg 5020, Level 16, State 1, Line 3
The primary data or log file cannot be removed from a database.

那么如何将第二个文件标记为主要文件，以便删除第一个文件？

在谷歌搜索这个问题时，我看到很多人试图删除文件组，但我只是想从组中删除一个文件。

同样的情况，但没有关于“主数据文件”的问题：https ://stackoverflow.com/questions/2183742/remove-merge-sql-servers-database-secondary-data-file 。

类似，但对于具有不同问题的日志文件：https ://stackoverflow.com/questions/10839374/sql-server-database-has-2-log-files-and-i-want-to-remove-one-how . 我有兴趣删除数据文件。

类似的情况，但问题在于清空文件：http ://www.sqlservercentral.com/Forums/Topic464326-5-1.aspx 。

我正在开始一个加密项目，EncryptByPassPhrase()主要使用VARCHAR值。自然，加密值比原始值长。是否有一个公式可以用来计算创建新VARBINARY字段需要多长时间，以便保留原始VARCHAR字段的任何可能值？

例如，我检查的第一个字段的值最长为 37 个字符，加密后的值最长为 100 个字节；另一个值最多为 50 个字符，加密值最多为 124 个字节。但是，短至两个或三个字符的值可以加密为 76 个字节。如果我将新字段的大小设置为 75 + X 字节，我是否有空间存储长度为 X 或更短的任何可能文本值的加密版本？

我已经绑定了Ctrl-F1查询快捷方式sp_whoisactive多年（此技术在How to assign CTRL+F1 to sp_whoisactive in ssms 2012? 中有描述）。几天前，它停止工作。如果我点击Ctrl- F1，SQL Studio 现在会在 Internet Explorer 中启动 Books Online。有没有其他人看到这个问题？有解决办法吗？

我确认它仍然设置的快捷方式。更改快捷方式并不能解决问题，Ctrl-F1仍会启动 BoL。循环 SQL Studio 没有任何效果。

FWIW，这发生在我登录时 IT 重新启动服务器之后，因此 SSMS 没有干净地关闭。

我当然可以使用另一个键盘快捷键（尽管很遗憾，它似乎Ctrl被1硬编码为 sp_help），它只是让我的强迫症患者在不同的机器上为同一命令使用不同的快捷键。

尝试使用 dtexec.exe 运行 SSIS 包时，我收到以下错误消息：

Could not load package "D:\Filename.dtsx" because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer "" failed
with the following error: "Access is denied."

By default, only administrators have access to the Integration Services service.  On
Windows Vista and later, the process must be running with administrative privileges in
order to connect to the Integration Services service. See the help topic for information
on how to configure access to the service.

通常对于 Microsoft，没有说明哪个帮助主题包含此信息。当然，dtexec /?帮助没有。

从以管理员权限启动的命令提示符或从具有管理员权限的应用程序运行 dtexec 并不能解决问题。

我最近在搜索时未能找到一个已知的过程ALL_PROCEDURES，并且心血来潮尝试了一下DBA_PROCEDURES：瞧，就是这样。文档页面（ALL_；DBA_）都说“......列出所有函数和过程及其相关属性。” 甲骨文似乎对“全部”有一个奇怪的定义。

我将此发布在 DBA 而不是 SO 上，尽管这是一个非常基本的问题，因为模式管理通常是 DBA 角色而不是开发人员角色。

重新启动托管 MS SQL Server 实例的服务器时，是否需要采取任何特殊步骤来防止数据损坏？

比如我最近遇到了手动停止 SQL 服务的建议。我的理解是，这是由 Windowsshutdown进程处理的。

我确信个人可能会推荐无数的步骤，就像我刚才提到的那样，但我想避免重复过时或迷信的做法。是否有来自 Microsoft 的建议或广泛的行业标准？

这个问题与重新启动机器的短期过程有关。还有一个问题是关于在永久关闭机器之前确保机器未被使用的长期程序。

我想让用户能够管理 SQL 代理作业。根据文档页面，我可以通过在 msdb 上授予“适当”角色来做到这一点。我已授予所有三个 SQLAgent% 角色，但是当（作为测试）尝试重命名作业时，用户会收到此错误：

Only members of sysadmin role are allowed to update or delete jobs owned by a different login.
(Microsoft SQL Server, Error: 14525)

这个问题很相似，但没有解决 SQL 代理的答案。在 MS SQL 2K8 中，系统管理员角色是唯一的解决方案吗？

从 Microsoft SQL Server 检索的数据是否经过压缩？如果这是由连接字符串控制的，是否有任何简单的方法来判断是否有任何特定应用程序正在使用它？

我正在研究分析工具，通过我们的网络传输大量数据可能需要几分钟。我想知道如果我们从同一远程服务器上的压缩数据存储中提取数据，是否应该期望性能提高。

只要我们谈到这个话题，我就很好奇：数据是以二进制还是 ASCII 传输的？例如，如果12345从列中查询该值，是否以INT0x31、0x32、0x33、0x34、0x35 五个字节传输；该值所需的两个字节；还是列所需的四个字节？

需要明确的是，我知道有一些关于压缩存储数据和备份数据的选项。我在问数据是如何传输的。

如果这已经得到回答，我很抱歉。我已经搜索过 SO，当然也搜索过 DBA，并且很惊讶地发现没有接近的匹配项。具体来说，我正在寻找一种能够在架构差异中幸存下来的解决方案。

我终于得到了一个开发服务器，可以用来映射生产数据仓库服务器。我将在这里测试架构和存储过程更改，最终将这些更改发布到产品服务器。为此，我需要合理的最新数据，但不需要实时更新。我的计划是将最终状态数据从主服务器复制到开发服务器，作为隔夜 ETL 的最后一步。实现这一目标的最佳方法是什么？

参数

所有自动复制都将从 prod 到 dev。从 dev 到 prod 的任何复制都将手动进行，并且通常只是 DDL。

因为开发服务器的架构预计会独立于生产服务器发生变化，所以我不希望在目标架构不同时复制过程失败（当然，如果特定的表无法保持同步，那也没关系） . 否则我只会DROP DATABASE恢复昨晚的备份。

保持模式、SP、视图和 UDF 同步是不必要的，甚至是不可取的。只有当我专门更改它们时，它们才应该更改。

大多数表中的记录都可以追溯更改，因此增量更新可能不切实际。

最关键的表的数据量为 14 GB，加上大约 200 GB 的不太重要的数据，可以每周更新一次。

我的目标是在两个小时内完成这个过程。服务器位于同一地点，并且应该具有高吞吐量。复制 ( INSERT INTO..SELECT * FROM ProdServer..) 一个包含 600 MB 数据和 300 个索引的表需要 7.5 分钟；不是很好。令人担忧的是，当我取消另一个具有 11 GB 数据和 8 GB 索引的表时，它并没有在 130 分钟内完成。我将在没有索引的情况下再次测试它。

如果可以保持简短（不超过 30 分钟）使 prod 数据库脱机是可以接受的。如有必要，开发数据库可以离线数小时。

如果我可以暂时排除一个特定的表，在测试时让它保持静态几天，那将是有价值的。

选项

1：复制

我可以在 ETL 结束后创建每日快照，并将其发布到开发服务器。我以前没有使用过复制，但这似乎是它的意思。是时候让我学习这项技术的新方面了吗？

2：批量复制和批量插入

我可以编写一个脚本，它遍历 prod 数据库中的每个表并将内容吐出到一个唯一命名的文件中。在开发方面，我将遍历这些文件和TRUNCATE/BULK INSERT进入目标表，并TRY..CATCH在架构发生更改时使用一个块。我不确定这是否可以接受，但实现起来相当简单。

3：从链接服务器插入

我可以为每个表运行一个TRUNCATE/ ，可能是从开发端拉的。INSERT..SELECT这将很简单，并且应该很快，尤其是在删除并重新创建索引的情况下。为了应对模式变化，可以识别每对表的字段列表的交集，并且只尝试复制这些字段；如果许多字段可以为空，这将有所帮助。

还有其他选择吗？有没有更简单的方法可以让我忽略？在从事类似项目时，您是否遇到过任何问题？

这个问题描述了导出方面，但所需的输出是 CSV，所以选项是有限的（BCP 工作得很好，正如回答的那样）。

这个问题谈论快速复制整个数据库，包括模式。它特别排除了复制。

这个问题讨论了双向同步，建议使用复制。

我有一个视图可以快速（几秒钟）运行多达 41 条记录（例如，TOP 41），但需要几分钟才能运行 44 条或更多记录，如果使用TOP 42or运行，则会产生中间结果TOP 43。具体来说，它将在几秒钟内返回前 39 条记录，然后在返回剩余记录之前停止近三分钟。这种模式在查询TOP 44or时是一样的TOP 100。

这个视图最初是从基础视图派生的，在基础视图中添加了一个过滤器，即下面代码中的最后一个过滤器。如果我将子视图从基础链接起来，或者我用基础内联的代码编写子视图，似乎没有区别。基本视图在几秒钟内返回 100 条记录。我想我可以让子视图像基地一样快地运行，而不是慢 50 倍。有没有人见过这种行为？关于原因或解决方案的任何猜测？

在我测试所涉及的查询时，这种行为在过去几个小时内一直保持一致，尽管在事情开始变慢之前返回的行数略有上下波动。这并不新鲜。我现在正在查看它，因为总运行时间是可以接受的（<2 分钟），但我已经看到相关日志文件中的这种暂停至少有几个月了。

阻塞

我从未见过查询被阻止，即使数据库上没有其他活动（由 sp_WhoIsActive 验证），问题仍然存在。基本视图包括NOLOCK所有内容，这是值得的。

查询

这是子视图的简化版本，为简单起见，内嵌了基本视图。它仍然在大约 40 条记录处显示运行时间的跳跃。

SELECT TOP 100 PERCENT
    Map.SalesforceAccountID AS Id,
    CAST(C.CustomerID AS NVARCHAR(255)) AS Name,
    CASE WHEN C.StreetAddress = 'Unknown' THEN '' ELSE C.StreetAddress                 END AS BillingStreet,
    CASE WHEN C.City          = 'Unknown' THEN '' ELSE SUBSTRING(C.City,        1, 40) END AS BillingCity,
                                                       SUBSTRING(C.Region,      1, 20)     AS BillingState,
    CASE WHEN C.PostalCode    = 'Unknown' THEN '' ELSE SUBSTRING(C.PostalCode,  1, 20) END AS BillingPostalCode,
    CASE WHEN C.Country       = 'Unknown' THEN '' ELSE SUBSTRING(C.Country,     1, 40) END AS BillingCountry,
    CASE WHEN C.PhoneNumber   = 'Unknown' THEN '' ELSE C.PhoneNumber                   END AS Phone,
    CASE WHEN C.FaxNumber     = 'Unknown' THEN '' ELSE C.FaxNumber                     END AS Fax,
    TransC.WebsiteAddress AS Website,
    C.AccessKey AS AccessKey__c,
    CASE WHEN dbo.ValidateEMail(C.EMailAddress) = 1 THEN C.EMailAddress END,  -- Removing this UDF does not speed things
    TransC.EmailSubscriber
    -- A couple dozen additional TransC fields
FROM
    WarehouseCustomers AS C WITH (NOLOCK)
    INNER JOIN TransactionalCustomers AS TransC WITH (NOLOCK) ON C.CustomerID = TransC.CustomerID
    LEFT JOIN  Salesforce.AccountsMap AS Map WITH (NOLOCK) ON C.CustomerID = Map.CustomerID
WHERE
        C.DateMadeObsolete IS NULL
    AND C.EmailAddress NOT LIKE '%@volusion.%'
    AND C.AccessKey IN ('C', 'R')
    AND C.CustomerID NOT IN (243566)  -- Exclude specific test records
    AND EXISTS (SELECT * FROM Orders AS O WHERE C.CustomerID = O.CustomerID AND O.OrderDate >= '2010-06-28')  -- Only count customers who've placed a recent order
    AND Map.SalesforceAccountID IS NULL  -- Only count customers not already uploaded to Salesforce
-- Removing the ORDER BY clause does not speed things up
ORDER BY
    C.CustomerID DESC

该过滤器会丢弃由;Id IS NULL返回的大部分记录。BaseView如果没有TOP子句，它们分别返回 1,100 条记录和 267K。

统计数据

运行时TOP 40：

SQL Server parse and compile time:    CPU time = 234 ms, elapsed time = 247 ms.
SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 0 ms.

(40 row(s) affected)
Table 'CustomersHistory'. Scan count 2, logical reads 39112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 752, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AccountsMap'. Scan count 1, logical reads 458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:   CPU time = 2199 ms,  elapsed time = 7644 ms.

运行时TOP 45：

(45 row(s) affected)
Table 'CustomersHistory'. Scan count 2, logical reads 98268, physical reads 1, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Orders'. Scan count 1, logical reads 1788, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AccountsMap'. Scan count 1, logical reads 2152, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 41980 ms,  elapsed time = 177231 ms.

我很惊讶地看到实际输出的这种适度差异导致读取次数增加了约 3 倍。

比较执行计划，除了返回的行数之外，它们是相同的。与上面的统计数据一样，TOP 45查询中早期步骤的实际行数要高得多，而不仅仅是高出 12.5%。

概括地说，它是从 Orders 中扫描一个覆盖索引，从 WarehouseCustomers 中寻找相应的记录；将此循环连接到 TransactionalCustomers（远程查询，确切计划未知）；并将其与 AccountsMap 的表扫描合并。远程查询是估计成本的 94%。

杂项说明

早些时候，当我将视图的扩展内容作为独立查询执行时，它运行得非常快：100 条记录需要 13 秒。我现在正在测试一个没有子查询的查询的精简版本，这个更简单的查询需要三分钟才能返回超过 40 行，即使作为独立查询运行也是如此。

子视图包含大量读取（每个 sp_WhoIsActive 约 1M），但在这台机器上（8 个内核，32 GB RAM，95% 专用 SQL 框）通常不是问题。

我已经多次删除并重新创建了这两个视图，没有任何变化。

数据不包括任何 TEXT 或 BLOB 字段。一个领域涉及UDF；删除它不会阻止暂停。

无论是在服务器本身上查询，还是在 1,400 英里外的工作站上查询，时间都是相似的，因此延迟似乎是查询本身固有的，而不是向客户端发送结果。

我已经看到这种情况发生了几次： sp_WhoIsActive 显示了各种 sp_OA% sprocs，以及我的用户名，但我当然不会直接运行它们。这是否反映了某种查询重写过程？它似乎与跨远程服务器的查询相关。

我有一个相当复杂的查询，它自己运行只需几秒钟，但是当包装在一个表值函数中时，它要慢得多；我实际上并没有让它完成，但它运行了长达十分钟而没有结束。唯一的变化是用日期参数替换两个日期变量（用日期文字初始化）：

七秒跑

DECLARE @StartDate DATE = '2011-05-21'
DECLARE @EndDate   DATE = '2011-05-23'

DECLARE @Data TABLE (...)
INSERT INTO @Data(...) SELECT...

SELECT * FROM @Data

运行至少十分钟

CREATE FUNCTION X (@StartDate DATE, @EndDate DATE)
  RETURNS TABLE AS RETURN
  SELECT ...

SELECT * FROM X ('2011-05-21', '2011-05-23')

我之前曾将该函数编写为带有 RETURNS @Data TABLE (...) 子句的多语句 TVF，但是将其替换为内联结构并没有产生明显的变化。TVF的长期运行时间为实际SELECT * FROM X时间；实际上创建 UDF 只需要几秒钟。

我可以发布有问题的查询，但它有点长（约 165 行），并且基于第一种方法的成功，我怀疑还有其他事情发生。浏览执行计划，它们似乎是相同的。

我尝试将查询分成更小的部分，而不做任何更改。单独执行时，没有一个部分需要超过几秒钟的时间，但 TVF 仍然挂起。

我看到一个非常相似的问题，https://stackoverflow.com/questions/4190506/sql-server-2005-table-valued-function-weird-performance，但我不确定该解决方案是否适用。也许有人已经看到了这个问题并且知道更通用的解决方案？谢谢！

这是经过几分钟处理后的 dm_exec_requests：

session_id              59
request_id              0
start_time              40688.46517
status                  running
command                 UPDATE
sql_handle              0x030015002D21AF39242A1101ED9E00000000000000000000
statement_start_offset  10962
statement_end_offset    16012
plan_handle             0x050015002D21AF3940C1E6B0040000000000000000000000
database_id                 21
user_id                 1
connection_id           314AE0E4-A1FB-4602-BF40-02D857BAD6CF
blocking_session_id         0
wait_type               NULL
wait_time                   0
last_wait_type          SOS_SCHEDULER_YIELD
wait_resource   
open_transaction_count  0
open_resultset_count    1
transaction_id              48030651
context_info            0x
percent_complete        0
estimated_completion_time   0
cpu_time                    344777
total_elapsed_time          348632
scheduler_id            7
task_address            0x000000045FC85048
reads                   1549
writes                  13
logical_reads           30331425
text_size               2147483647
language                us_english
date_format             mdy
date_first              7
quoted_identifier           1
arithabort              1
ansi_null_dflt_on       1
ansi_defaults           0
ansi_warnings           1
ansi_padding            1
ansi_nulls                  1
concat_null_yields_null 1
transaction_isolation_level 2
lock_timeout            -1
deadlock_priority           0
row_count                   105
prev_error              0
nest_level              1
granted_query_memory    170
executing_managed_code  0
group_id                2
query_hash              0xBE6A286546AF62FC
query_plan_hash         0xD07630B947043AF0

这是完整的查询：

CREATE FUNCTION Routine.MarketingDashboardECommerceBase (@StartDate DATE, @EndDate DATE)
RETURNS TABLE AS RETURN
    WITH RegionsByCode AS (SELECT CountryCode, MIN(Region) AS Region FROM Staging.Volusion.MarketingRegions GROUP BY CountryCode)
        SELECT
            D.Date, Div.Division, Region.Region, C.Category1, C.Category2, C.Category3,
            COALESCE(V.Visits,          0) AS Visits,
            COALESCE(Dem.Demos,         0) AS Demos,
            COALESCE(S.GrossStores,     0) AS GrossStores,
            COALESCE(S.PaidStores,      0) AS PaidStores,
            COALESCE(S.NetStores,       0) AS NetStores,
            COALESCE(S.StoresActiveNow, 0) AS StoresActiveNow
            -- This line causes the run time to climb from a few seconds to over an hour!
            --COALESCE(V.Visits,          0) * COALESCE(ACS.AvgClickCost, GAAC.AvgAdCost, 0.00) AS TotalAdCost
            -- This line alone does not inflate the run time
            --ACS.AvgClickCost
            -- This line is enough to increase the run time to at least a couple minutes
            --GAAC.AvgAdCost
        FROM
            --Dates AS D
            (SELECT SQLDate AS Date FROM Dates WHERE SQLDate BETWEEN @StartDate AND @EndDate) AS D
            CROSS JOIN (SELECT 'UK' AS Division UNION SELECT 'US' UNION SELECT 'IN' UNION SELECT 'Unknown') AS Div
            CROSS JOIN (SELECT Category1, Category2, Category3 FROM Routine.MarketingDashboardCampaignMap UNION SELECT 'Unknown', 'Unknown', 'Unknown') AS C
            CROSS JOIN (SELECT DISTINCT Region FROM Staging.Volusion.MarketingRegions) AS Region
            -- Visitors
            LEFT JOIN
                (
                SELECT
                    V.Date,
                    CASE    WHEN V.Country IN ('United Kingdom', 'Guernsey', 'Ireland', 'Jersey') THEN 'UK'
                        WHEN V.Country IN ('United States', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
                        ELSE 'IN' END AS Division,
                    COALESCE(MR.Region, 'Unknown') AS Region,
                    C.Category1, C.Category2, C.Category3,
                    SUM(V.Visits) AS Visits
                FROM
                             RawData.GoogleAnalytics.Visits        AS V
                    INNER JOIN Routine.MarketingDashboardCampaignMap AS C ON V.LandingPage = C.LandingPage AND V.Campaign = C.Campaign AND V.Medium = C.Medium AND V.Referrer = C.Referrer AND V.Source = C.Source
                    LEFT JOIN  Staging.Volusion.MarketingRegions     AS MR ON V.Country = MR.CountryName
                WHERE
                    V.Date BETWEEN @StartDate AND @EndDate
                GROUP BY
                    V.Date,
                    CASE    WHEN V.Country IN ('United Kingdom', 'Guernsey', 'Ireland', 'Jersey') THEN 'UK'
                        WHEN V.Country IN ('United States', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
                        ELSE 'IN' END,
                    COALESCE(MR.Region, 'Unknown'), C.Category1, C.Category2, C.Category3
                ) AS V ON D.Date = V.Date AND Div.Division = V.Division AND Region.Region = V.Region AND C.Category1 = V.Category1 AND C.Category2 = V.Category2 AND C.Category3 = V.Category3
            -- Demos
            LEFT JOIN
                (
                SELECT
                    OD.SQLDate,
                    G.Division,
                    COALESCE(MR.Region,   'Unknown') AS Region,
                    COALESCE(C.Category1, 'Unknown') AS Category1,
                    COALESCE(C.Category2, 'Unknown') AS Category2,
                    COALESCE(C.Category3, 'Unknown') AS Category3,
                    SUM(D.Demos) AS Demos
                FROM
                             Demos            AS D
                    INNER JOIN Orders           AS O  ON D."Order" = O."Order"
                    INNER JOIN Dates            AS OD ON O.OrderDate = OD.DateSerial
                    INNER JOIN MarketingSources AS MS ON D.Source = MS.Source
                    LEFT JOIN  RegionsByCode    AS MR ON MS.CountryCode = MR.CountryCode
                    LEFT JOIN
                        (
                        SELECT
                            G.TransactionID,
                            MIN (
                                CASE WHEN G.Country IN ('United Kingdom', 'Guernsey', 'Ireland', 'Jersey') THEN 'UK'
                                    WHEN G.Country IN ('United States', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
                                    ELSE 'IN' END
                                ) AS Division
                        FROM
                            RawData.GoogleAnalytics.Geography AS G
                        WHERE
                                TransactionDate BETWEEN @StartDate AND @EndDate
                            AND NOT EXISTS (SELECT * FROM RawData.GoogleAnalytics.Geography AS G2 WHERE G.TransactionID = G2.TransactionID AND G2.EffectiveDate > G.EffectiveDate)
                        GROUP BY
                            G.TransactionID
                        ) AS G  ON O.VolusionOrderID = G.TransactionID
                    LEFT JOIN  RawData.GoogleAnalytics.Referrers     AS R  ON O.VolusionOrderID = R.TransactionID AND NOT EXISTS (SELECT * FROM RawData.GoogleAnalytics.Referrers AS R2 WHERE R.TransactionID = R2.TransactionID AND R2.EffectiveDate > R.EffectiveDate)
                    LEFT JOIN  Routine.MarketingDashboardCampaignMap AS C  ON MS.LandingPage = C.LandingPage AND MS.Campaign = C.Campaign AND MS.Medium = C.Medium AND COALESCE(R.ReferralPath, '(not set)') = C.Referrer AND MS.SourceName = C.Source
                WHERE
                        O.IsDeleted = 'No'
                    AND OD.SQLDate BETWEEN @StartDate AND @EndDate
                GROUP BY
                    OD.SQLDate,
                    G.Division,
                    COALESCE(MR.Region,   'Unknown'),
                    COALESCE(C.Category1, 'Unknown'),
                    COALESCE(C.Category2, 'Unknown'),
                    COALESCE(C.Category3, 'Unknown')
                ) AS Dem ON D.Date = Dem.SQLDate AND Div.Division = Dem.Division AND Region.Region = Dem.Region AND C.Category1 = Dem.Category1 AND C.Category2 = Dem.Category2 AND C.Category3 = Dem.Category3
            -- Stores
            LEFT JOIN
                (
                SELECT
                    OD.SQLDate,
                    CASE WHEN O.VolusionCountryCode = 'GB' THEN 'UK'
                        WHEN A.CountryShortName IN ('U.S.', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
                        ELSE 'IN' END AS Division,
                    COALESCE(MR.Region,     'Unknown') AS Region,
                    COALESCE(CpM.Category1, 'Unknown') AS Category1,
                    COALESCE(CpM.Category2, 'Unknown') AS Category2,
                    COALESCE(CpM.Category3, 'Unknown') AS Category3,
                    SUM(S.Stores) AS GrossStores,
                    SUM(CASE WHEN O.DatePaid <> -1 THEN 1 ELSE 0 END) AS PaidStores,
                    SUM(CASE WHEN O.DatePaid <> -1 AND CD.WeekEnding <> OD.WeekEnding THEN 1 ELSE 0 END) AS NetStores,
                    SUM(CASE WHEN O.DatePaid <> -1 THEN SH.ActiveStores ELSE 0 END) AS StoresActiveNow
                FROM
                             Stores           AS S
                    INNER JOIN Orders           AS O   ON S."Order" = O."Order"
                    INNER JOIN Dates            AS OD  ON O.OrderDate = OD.DateSerial
                    INNER JOIN Dates            AS CD  ON O.CancellationDate = CD.DateSerial
                    INNER JOIN Customers        AS C   ON O.CustomerNow = C.Customer
                    INNER JOIN MarketingSources AS MS  ON C.Source = MS.Source
                    INNER JOIN StoreHistory     AS SH  ON S.MostRecentHistory = SH.History
                    INNER JOIN Addresses        AS A   ON C.Address = A.Address
                    LEFT JOIN  RegionsByCode    AS MR  ON MS.CountryCode = MR.CountryCode
                    LEFT JOIN  Routine.MarketingDashboardCampaignMap AS CpM ON CpM.LandingPage = 'N/A' AND MS.Campaign = CpM.Campaign AND MS.Medium = CpM.Medium AND CpM.Referrer = 'N/A' AND MS.SourceName = CpM.Source
                WHERE
                        O.IsDeleted = 'No'
                    AND OD.SQLDate BETWEEN @StartDate AND @EndDate
                GROUP BY
                    OD.SQLDate,
                    CASE WHEN O.VolusionCountryCode = 'GB' THEN 'UK'
                        WHEN A.CountryShortName IN ('U.S.', 'Canada', 'Puerto Rico', 'U.S. Virgin Islands') THEN 'US'
                        ELSE 'IN' END,
                    COALESCE(MR.Region,     'Unknown'),
                    COALESCE(CpM.Category1, 'Unknown'),
                    COALESCE(CpM.Category2, 'Unknown'),
                    COALESCE(CpM.Category3, 'Unknown')
                ) AS S ON D.Date = S.SQLDate AND Div.Division = S.Division AND Region.Region = S.Region AND C.Category1 = S.Category1 AND C.Category2 = S.Category2 AND C.Category3 = S.Category3
            -- Google Analytics spend
            LEFT JOIN
                (
                SELECT
                    AC.Date, C.Category1, C.Category2, C.Category3, SUM(AC.AdCost) / SUM(AC.Visits) AS AvgAdCost
                FROM
                    RawData.GoogleAnalytics.AdCosts AS AC
                    INNER JOIN
                        (
                        SELECT Campaign, Medium, Source, MIN(Category1) AS Category1, MIN(Category2) AS Category2, MIN(Category3) AS Category3
                        FROM Routine.MarketingDashboardCampaignMap
                        WHERE Category1 <> 'Affiliate'
                        GROUP BY Campaign, Medium, Source
                        ) AS C ON AC.Campaign = C.Campaign AND AC.Medium = C.Medium AND AC.Source = C.Source
                WHERE
                    AC.Date BETWEEN @StartDate AND @EndDate
                GROUP BY
                    AC.Date, C.Category1, C.Category2, C.Category3
                HAVING
                    SUM(AC.AdCost) > 0.00 AND SUM(AC.Visits) > 0
                ) AS GAAC ON D.Date = GAAC.Date AND C.Category1 = GAAC.Category1 AND C.Category2 = GAAC.Category2 AND C.Category3 = GAAC.Category3
            -- adCenter spend
            LEFT JOIN
                (
                SELECT Date, SUM(Spend) / SUM(Clicks) AS AvgClickCost
                FROM RawData.AdCenter.Spend
                WHERE Date BETWEEN @StartDate AND @EndDate
                GROUP BY Date
                HAVING SUM(Spend) > 0.00 AND SUM(Clicks) > 0
                ) AS ACS ON D.Date = ACS.Date AND C.Category1 = 'PPC' AND C.Category2 = 'adCenter' AND C.Category3 = 'N/A'
        WHERE
            V.Visits > 0 OR Dem.Demos > 0 OR S.GrossStores > 0
GO


SELECT * FROM Routine.MarketingDashboardECommerceBase('2011-05-21', '2011-05-23')

我有几个表，其中的记录可以用几个广泛的业务领域进行唯一标识。过去，我将这些字段用作 PK，考虑到这些好处：

• 简单; 没有无关的字段，只有一个索引
• 集群允许快速合并连接和基于范围的过滤器

但是，我听说过创建合成IDENTITY INTPK 的案例，而不是使用单独的UNIQUE约束来强制执行业务密钥。优点是窄 PK 使得二级索引小得多。

如果一个表除了 PK 之外没有其他索引，我看不出有任何理由支持第二种方法，尽管在一个大表中最好假设将来可能需要索引，因此支持窄合成 PK . 我是否遗漏了任何注意事项？

顺便说一下，我并不是反对在数据仓库中使用合成密钥，我只是对何时使用单个宽泛 PK 以及何时使用窄 PK 加上宽泛 UK 感兴趣。

我有一个包含 4.9 亿行和 55 GB 表空间的表，因此每行大约 167 个字节。该表包含三列： a VARCHAR(100)、 aDATETIME2(0)和 a SMALLINT。字段中文本的平均长度VARCHAR约为 21.5，因此原始数据应约为每行 32 个字节： 22+2 用于VARCHAR， 6 用于DATETIME2， 2 用于 16 位整数。

请注意，上面的空间只是数据，而不是索引。我正在使用属性下报告的值 | 存储 | 一般 | 数据空间。

当然必须有一些开销，但是每行 135 个字节似乎很多，尤其是对于一个大表。为什么会这样？有没有其他人见过类似的乘数？哪些因素会影响所需的额外空间量？

为了比较，我尝试创建一个包含两个INT字段和 1 M 行的表。所需的数据空间为 16.4 MB：每行 17 个字节，而原始数据为 8 个字节。另一个测试表使用与真实表相同的文本填充了一个INT和一个，每行使用 39 个字节（44 K 行），我预计会多出 28 个字节。VARCHAR(100)

所以生产表的开销要大得多。这是因为它更大吗？我希望索引大小大约为 N * log(N)，但我不明白为什么实际数据所需的空间是非线性的。

提前感谢您的任何指点！

编辑：

列出的所有字段都是NOT NULL. VARCHAR真实表在字段和DATETIME2字段上按该顺序具有聚集的 PK 。对于这两个测试，第一个INT是（集群）PK。

如果重要：该表是 ping 结果的记录。这些字段是 URL、ping 日期/时间和延迟（以毫秒为单位）。数据会不断地添加，并且永远不会更新，但会定期删除数据以将其减少到每个 URL 每小时仅几条记录。

编辑：

这里的一个非常有趣的答案表明，对于具有大量读写的索引，重建可能没有好处。在我的情况下，消耗的空间是一个问题，但如果写入性能更重要，那么使用松弛的索引可能会更好。

我们有一个“有机”的环境，这意味着人们在代码上堆积了十年的代码，而几乎没有监督或文档。我使用的服务器有几个我认为不再使用的数据库；我很想删除它们，只留下我实际使用的三个。

在鲁莽的极端情况下，我可以禁用这些数据库并等待有人尖叫；另一方面，我可以让它们永远运行“以防万一”。您发现哪些步骤在确定是否正在使用服务器以及如何使用方面有价值？

此外，您建议采取哪些步骤来确保随着禁用系统的推进，它们在一段时间内仍可方便地可逆（例如，重命名对象而不是彻底删除它们）？

谢谢！