内存优化表将 LOB 类型存储在内部表中。

我有一个没有显式 LOB 类型的表，但是当我对sys.memory_optimized_tables_internal_attributes运行查询时，我看到列出的 varchar(255) 列的 type_desc 值为INTERNAL OFF-ROW DATA TABLE。

SET ANSI_NULLS ON;
GO

SET QUOTED_IDENTIFIER ON;
GO

SET ANSI_PADDING ON;
GO

CREATE TABLE dbo.DERP
(
    RECORD_KEY numeric(30, 0) NOT NULL
,   COL_1 bigint NOT NULL
,   COL_2 datetime2(0) NOT NULL
,   COL_3 datetime2(0) NOT NULL
,   COL_4 varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_5 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_6 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_7 date NULL
,   COL_8 bigint NULL
,   COL_9 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_10 int NULL
,   COL_11 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_12 int NULL
,   COL_13 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_14 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_15 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_16 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_17 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_18 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_19 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_20 char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_21 bigint NULL
,   COL_22 bigint NULL
,   COL_23 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_24 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_25 int NULL
,   COL_26 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_27 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_28 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_29 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_30 decimal(7, 4) NULL
,   COL_31 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_32 decimal(8, 4) NULL
,   COL_33 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_34 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_35 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_36 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_37 int NULL
,   COL_38 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_39 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_40 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_41 int NULL
,   COL_42 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_43 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_44 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_45 int NULL
,   COL_46 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_47 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_48 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_49 int NULL
,   COL_50 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_51 int NULL
,   COL_52 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_53 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_54 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_55 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_56 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_57 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_58 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_59 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_60 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_61 date NULL
,   COL_62 char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_63 int NULL
,   COL_64 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_65 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_66 int NULL
,   COL_67 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_68 int NULL
,   COL_69 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_70 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_71 char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_72 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_73 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_74 char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_75 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_76 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_77 char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_78 char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_79 varchar(255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,   COL_80 bigint NULL
,   CONSTRAINT dbo_DERP
        PRIMARY KEY NONCLUSTERED
        (
            RECORD_KEY ASC
        )
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

GO

SET ANSI_PADDING OFF;
GO

运行以下查询会产生 10 列（COL_58、COL_64、COL_65、COL_67、COL_70、COL_72、COL_73、COL_75、COL_76、COL_79）列为行外数据表

SELECT
    QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(OBJECT_NAME(moa.object_id)) AS 'table'
,   c.name AS 'column'
,   c.max_length
,   moa.type_desc
FROM
    sys.memory_optimized_tables_internal_attributes moa
    INNER JOIN
        sys.columns c
        ON moa.object_id = c.object_id
           AND moa.minor_id = c.column_id
    INNER JOIN
        sys.objects o
        ON moa.object_id = o.object_id
WHERE
    o.name = 'DERP'
ORDER BY
    1;

我假设这个存储决定是基于这个超宽的表（将近 10,400 字节宽），是吗？

SQL Server 2016 SP1，CU2，又名 13.0.4422.0

我登录到一个新的客户端系统并运行 sp_blitz 看看发生了什么。它报告说“ CPU Schedulers Offline ”对我来说是一个新的。

由于关联屏蔽或许可问题，SQL Server 无法访问某些 CPU 内核。

很公平，我运行基本查询

SELECT
    DOS.is_online
,   DOS.status
,   DOS.*
FROM
    sys.dm_os_schedulers AS DOS
ORDER BY
    1;

报告说我有 8 个设置为离线可见，43 个设置为在线。据我所知，这个客户端上没有人会故意设置任何 CPU 亲和性。

我决定看看能不能解开它。当我查看属性窗口时，我看到有 40 个处理器可用，并且没有一个设置为具有亲和力。

为什么在 is_online 为 true 的 dm_os_scheduler 中有 40 个显示但 43 个条目似乎也很奇怪。8 个离线的 cpu_id 是 32 到 39。

sys.configurations 似乎与未明确启用的亲和力一致

name                 value  value_in_use  description
affinity I/O mask    0      0             affinity I/O mask
affinity mask        0      0             affinity mask
affinity64 I/O mask  0      0             affinity64 I/O mask
affinity64 mask      0      0             affinity64 mask

这不是企业版，因此​​ CAL 祖父的东西不应该是这里的因素，但如果需要，我明天可以问这个问题

ProductVersion  ProductLevel  ProductUpdateLevel  Edition
11.0.5058.0     SP2           NULL                Standard Edition (64-bit)

运行 Glenn Berry 的诊断查询，这些东西可能是相关的

• 系统制造商：“Dell Inc.”，系统型号：“PowerEdge R720”。
• Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz

那么，什么给了？我被虚拟化了却不知道吗？我应该寻找其他地方来确定为什么 SQL Server 不能使用某些 CPU 吗？

参考文章

我读过的文章列表，但显然理解得不够好，无法回答我自己的问题

• BOL sys.dm_os_schedulers
• 格伦·贝瑞 (Glenn Berry) 的 DMV 一天
• SQLServerGeeks DMV 一天

如何判断我的 SQL Server 实例是否已修补？是否有本机功能可以识别我的服务器是否有可用的补丁？哎呀，我什至有可用的版本数据吗？

我正在编写一个脚本来将环境从一台服务器移植到另一台服务器。我遇到了一个调用问题，catalog.create_environment_variable其中出现错误“输入值的数据类型与‘字符串’的数据类型不兼容。” 来自过程“check_data_type_value”。

奇怪的是，如果我让 GUI 脚本输出变量，该查询将起作用

DECLARE @var sql_variant = N'\\myserver\ssisdata'
EXEC [catalog].[create_environment_variable]
    @variable_name = N'FolderBase'
,   @sensitive = False
,   @description = N''
,   @environment_name = N'Development'
,   @folder_name = N'POC'
,   @value = @var
,   @data_type = N'String'
GO

但是，采用这种脚本方法是行不通的。我所做的工作表明此错误消息通常通过使用 nvarchar 数据类型而不是 varchar 来解决。但是，我的东西不是这样。

以下脚本的第 108 行。我的假设是sql_variant 有点奇怪，但我不知道那是什么。

USE SSISDB;
GO

DECLARE
    @folder_id bigint
,   @folder_name nvarchar(128) = N'POC'
,   @environment_name nvarchar(128) = N'Development'
,   @environment_description nvarchar(1024)
,   @reference_id bigint
,   @variable_name nvarchar(128)
,   @data_type nvarchar(128)
,   @sensitive bit
,   @value sql_variant
,   @description nvarchar(1024);

IF NOT EXISTS
(
    SELECT * FROM catalog.folders AS F WHERE F.name = @folder_name
)
BEGIN
    EXECUTE catalog.create_folder
        @folder_name = @folder_name
    ,   @folder_id = @folder_id OUTPUT;

    PRINT CONCAT('Folder "', @folder_name, '" has been created with a folder_id of ', @folder_id)
END

IF NOT EXISTS
(
    SELECT * FROM catalog.environments AS E WHERE E.name = @environment_name 
    AND E.folder_id = (SELECT F.folder_id FROM catalog.folders AS F WHERE F.name = @folder_name)
)
BEGIN
    PRINT CONCAT('Creating environment ',  @environment_name);

    EXECUTE catalog.create_environment
        @folder_name = @folder_name
    ,   @environment_name = @environment_name
    ,   @environment_description = @environment_description;
END

DECLARE
    @EnvironmentVariables TABLE
(
    folder_name nvarchar(128)
,   environment_name nvarchar(128)
,   variable_name nvarchar(128)
,   description nvarchar(1024)
,   data_type nvarchar(128)
,   sensitive bit
,   value sql_variant
);

INSERT INTO
    @EnvironmentVariables
SELECT
    E.folder_name
,   E.environment_name
,   S.name
,   S.description
,   S.type
,   S.sensitive
,   S.value
FROM
(
    SELECT 'FolderBase','Root for ssis processing','String',CAST(0 AS bit),'\\myserver\ssisdata'
    UNION ALL SELECT 'AuditConnectionString','Conn to audit db','String',CAST(0 AS bit),'Data Source=SQLETL01;Initial Catalog=Audit;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
) AS S (name, description, type, sensitive, value)
CROSS APPLY
(
    SELECT
        E.name AS environment_name
    ,   F.name AS folder_name
    FROM
        catalog.folders AS F
        INNER JOIN
            catalog.environments AS E
            ON E.folder_id = F.folder_id
    WHERE
        F.name = @folder_name
        AND E.name = @environment_name
) E;


DECLARE Csr CURSOR FORWARD_ONLY STATIC FOR
SELECT
    EV.variable_name
,   EV.description
,   EV.data_type
,   EV.sensitive
,   EV.value
FROM
    @Environmentvariables AS EV;

OPEN Csr;
FETCH NEXT FROM Csr INTO
    @variable_name
,   @description
,   @data_type
,   @sensitive
,   @value;

WHILE @@FETCH_STATUS = 0
BEGIN

    BEGIN TRY
            -- THERE BE MONSTERS AHEAD
        -- The data type of the input value is not compatible with the data type of the 'String'. 
        EXECUTE catalog.create_environment_variable
            @variable_name = @variable_name
        ,   @sensitive = @sensitive
        ,   @description = @description
        ,   @environment_name = @environment_name
        ,   @folder_name = @folder_name
        ,   @value = @value
        ,   @data_type = @data_type
    END TRY
    BEGIN CATCH
        SELECT 
            @folder_name        AS folder_name
        ,   @environment_name   AS environment_name
        ,   @variable_name      AS variable_name
        ,   @data_type          AS data_type
        ,   @sensitive          AS sensitive
        ,   @value              AS value
        ,   @description        AS description
        ,   ERROR_NUMBER()AS error_number --returns the number of the error.
        ,   ERROR_SEVERITY() AS error_severity --returns the severity.
        ,   ERROR_STATE()AS error_state  --returns the error state number.
        ,   ERROR_PROCEDURE() AS error_procedure --returns the name of the stored procedure or trigger where the error occurred.
        ,   ERROR_LINE() AS error_line --returns the line number inside the routine that caused the error.
        ,   ERROR_MESSAGE() AS error_message; --returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

    END CATCH  

    FETCH NEXT FROM Csr INTO
        @variable_name
    ,   @description
    ,   @data_type
    ,   @sensitive
    ,   @value;
END

CLOSE Csr;
DEALLOCATE Csr;

SQL Server Integration Services 的 2012 版 SSIS 提供了一个 SSISDB 目录，用于跟踪包的操作（以及其他内容）。使用项目部署模型的解决方案的默认包执行将启用对 SSISDB 的日志记录。

当一个包执行时，将System::ExecutionInstanceGUID填充一个值，如果使用显式日志记录（到sys.sysdtslog90/ sys.sysssislog）将记录特定包执行的所有事件。

我想知道的是如何将 ExecutionInstanceGUID 绑定到SSISDB 目录中的任何内容。或者，在 SSISDB 中执行的 SSIS 包是否知道其值catalog.executions.execution_id

最终，我尝试使用现有的自定义审计表并将其链接回 SSISDB 目录中的详细历史记录，但似乎找不到链接。

问题定义

我们的用户需要能够查询大部分是最新的数据库。数据最长可达 24 小时，这是可以接受的。使用生产副本获取第二个数据库并使其保持最新的成本最低的方法是什么？有没有我没有想到的方法？

工作量

我们有一个第三方应用程序用于监控股票交易活动。白天，作为各种工作流程的一部分，会发生许多小变化（是的，这笔交易是有效的。不，这是可疑的，等等）。晚上，我们执行基于大集合的操作（加载前一天的交易）。

当前的解决方案和问题

我们利用数据库快照。晚上 10 点，我们删除并重新创建快照。然后开始 ETL 处理。这显然对我们的磁盘造成了负担，但允许我们的用户在不锁定数据库的情况下查询数据库（他们使用 Access 前端）。他们在深夜和清晨使用它，因此他们会注意到停机时间。

这种方法的问题是双重的。第一个是如果夜间处理失败，这并不少见，我们要恢复数据库，这会导致快照被删除。另一个问题是我们的处理时间正在超过我们的 SLA。在发现查询写得不好和缺乏索引后，我们正试图通过与供应商合作来解决这个问题。数据库快照也是造成这种减速的罪魁祸首，当它存在与不存在时的速度差异就证明了这一点——我知道，这很令人震惊。

考虑的方法

聚类

我们打开了数据库集群，但这并不能满足使数据可用的需求，而且通常会使管理员的生活变得复杂。它已被关闭。

SQL Server 复制

我们上周开始研究复制。我们的理论是，我们可以建立第二个目录并与生产数据库同步。在 ETL 开始之前，我们将断开连接并仅在 ETL 过程完成后重新启用它。

管理员从快照复制开始，但他担心生成快照需要数天的高 CPU 使用率以及所需的磁盘消耗。他指出，它似乎在发送给订阅者之前将所有数据写入物理文件，因此我们的 .6TB 数据库将花费 1.8TB 的存储成本。此外，如果生成快照需要数天时间，则它不适合所需的 SLA。

在阅读了这篇精彩的文章之后，似乎 Snapshot 可能是初始化订阅者的方式，但之后我们想切换到事务复制以保持同步。我假设打开/关闭事务复制不会强制完全重新初始化？否则，我们将打破我们的时间窗口

数据库镜像

我们的数据库处于完全恢复模式，因此数据库镜像是一种选择，但我对它的了解甚至比复制还要少。我确实找到了表明“数据库镜像阻止直接访问数据，镜像数据只能通过数据库快照访问”的答案。

日志运输

听起来日志传送也可能是一种选择，但这是我一无所知的另一件事。它会是比其他任何解决方案（实施和维护）成本更低的解决方案吗？根据 Remus 的评论“日志传送允许对副本副本进行只读访问，但在应用收到的下一个备份日志时（例如每 15-30 分钟）将断开所有用户的连接。” 我不确定停机时间会转化为多长时间，这可能会导致用户有些焦虑。

微软同步

我只是在上周末听说过使用Sync，还没有调查过。我不想为像这个问题那样具有高知名度的东西引入新技术，但如果它是最好的方法，那就这样吧。

SSIS

我们在这里做了很多 SSIS，因此生成数百个 SSIS 包以保持辅助同步是我们的一种选择，尽管它很丑陋。我不喜欢这样做，因为我希望我的团队不要承担很多维护开销。

SAN“神奇”快照

过去，我听说我们的管理员使用一些 SAN 技术对整个磁盘进行即时备份。也许有一些 EMC 魔法可以用来制作 mdf/ldf 的超快速副本，然后我们可以分离/附加目标数据库。

备份还原

我认为我们每周进行一次完整备份，每晚进行一次差异，每 15 分钟进行一次 tlog。如果用户可以忍受 3-4 小时的完全恢复中断，我想这可能是一种方法。

约束

Windows 2008 R2、SQL Server 2008 R2（企业版）、VMWare v5 企业版、带有映射到 vmdk 文件的驱动器的 EMC SAN 存储、commvault 处理备份以及源目录中的 0.6TB 数据。这是我们内部托管的第三方应用程序。修改它们的结构通常是不受欢迎的。用户不能不查询数据库，并拒绝通过主动识别他们监控的表来完成工作而受到限制。

目前，我们的 DBA 纯粹是承包商。全职人员已经启航，我们还没有更换他们。应用程序管理员并不精通 SQL Server 事务，我们有一个存储/VM 管理员团队可以帮助/阻碍这项工作。开发团队目前不参与，但可以根据方法加入。因此，更易于实施和维护的解决方案将是可取的。

我，我在房子的开发方面，所以我只能提出方法，而不必处理管理方面的事情。因此，由于没有时间在管理鞍上，我不敢说一种方法优于另一种方法——根据论文，这一切看起来都很棒。我完全愿意按照你们建议的任何方向前进，因为在我看来，这只会让我作为一名 DB 专业人士更有价值。我有一辆独轮车，但没有可用的大屠杀斗篷。

相关问题

• https://stackoverflow.com/questions/525637/what-are-the-scenarios-for-using-mirroring-log-shipping-replication-and-cluste

• https://stackoverflow.com/questions/434982/mirroring-vs-replication

• https://stackoverflow.com/questions/4303020/sync-databases-mirroring-replication-log-shipping

• https://stackoverflow.com/questions/4303020/sync-databases-mirroring-replication-log-shipping

• http://nilebride.wordpress.com/2011/07/24/log-shipping-vs-mirroring-vs-replication/

编辑

解决@onpnt 的问题

数据延迟接受

用户当前查看的数据最多滞后 24 小时。该数据仅是截至 2200 年的最新数据

给定一分钟、一小时和一天内的数据变化量 不确定如何量化。营业时间，可能每小时数百次更改。每晚处理，每个工作日数百万行

连接到二级

内部网络，独立的虚拟主机和专用存储

读取辅助实例的要求

Windows 组将具有对辅助所有表的读取权限

辅助实例的正常运行时间

没有对正常运行时间要求的严格定义。用户希望它始终可用，但他们愿意为此付费，可能不会那么多。实际上，我会说一天 23 小时就足够了。

对现有架构和所有对象的更改

不经常修改，对于表格对象可能每季度一次。对于代码对象，可能每月一次。

安全

没有特殊的安全需求。生产权限将匹配副本的权限。尽管在我看来，我们可以撤销用户对 prod 的读取权限，只允许他们读取副本……但这不是要求。

@darin海峡

恢复到快照可能是一种选择，但我认为他们没有追求它是有原因的。我会和管理员核对

@cfradenburg

我的假设是我们只会使用其中一种方法，但这是一个很好的点，恢复会破坏“其他”同步技术。他们正在研究使用 EMC 快照魔法。正如管理员所描述的，他们将在 1900 拍摄快照并将映像迁移到辅助节点的区域。这应该在 2200 年完成，然后他们将执行辅助数据库的分离和重新连接。

包起来

2012-10-29 我们评估了 EMC 快照魔法和其他一些复制选项，但 DBA 认为他们最能解决镜像问题。赞成答案，因为他们都提供了帮助并给了我很多选择以及“家庭作业”来调查。

问题

用户定义的表类型作为sp_executesql的参数是否存在已知问题？

设置脚本

此脚本创建表、过程和用户​​定义的表类型中的每一个（仅限 SQL Server 2008+）。

• 堆的目的是提供一个审计，是的，数据进入了程序。没有任何限制，没有任何东西可以阻止数据被插入。
• 该过程将用户定义的表类型作为参数。proc 所做的只是插入到表中。
• 用户定义的表类型也很简单，只有一列

我已经针对以下内容运行了11.0.1750.32 (X64) ，10.0.4064.0 (X64)是的，我知道可以修补该框，但我无法控制。

-- this table record that something happened
CREATE TABLE dbo.UDTT_holder
(
    ServerName varchar(200)
,   insert_time datetime default(current_timestamp)
)
GO

-- user defined table type transport mechanism
CREATE TYPE dbo.UDTT
AS TABLE
(
    ServerName varchar(200)
)
GO

-- Stored Procedure to reproduce issue
CREATE PROCEDURE dbo.Repro
(
    @MetricData dbo.UDTT READONLY
)
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.UDTT_holder 
    (ServerName)
    SELECT MD.* FROM @MetricData MD
END
GO

问题再现

此脚本演示了该问题，执行需要五秒钟。我创建了我的用户定义表类型的两个实例，然后尝试了两种不同的方法将它们传递给sp_executesql 第一次调用中的参数映射模仿了我从 SQL Profiler 捕获的内容。然后我在没有 sp_executesql 包装器的情况下调用该过程。

SET NOCOUNT ON
DECLARE 
    @p3 dbo.UDTT
,   @MetricData dbo.UDTT
INSERT INTO @p3 VALUES(N'SQLB\SQLB')
INSERT INTO @MetricData VALUES(N'SQLC\SQLC')

-- nothing up my sleeve
SELECT * FROM dbo.UDTT_holder

SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing sp_executesql' AS commentary
-- This does nothing
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData=@p3
-- makes no matter if we're mapping variables
EXECUTE sp_executesql N'dbo.Repro',N'@MetricData dbo.UDTT READONLY',@MetricData

-- Five second delay
waitfor delay '00:00:05'
SELECT CONVERT(varchar(24), current_timestamp, 121) + ' Firing proc' AS commentary
-- this does
EXECUTE dbo.Repro @p3

-- Should only see the latter timestamp
SELECT * FROM dbo.UDTT_holder

GO

结果

下面是我的结果。该表最初是空的。我发出当前时间并对 sp_executesql 进行两次调用。我等待 5 秒过去并发出当前时间，然后调用存储过程本身，最后转储审计表。

从时间戳可以看出，B 记录的记录对应于直接的存储过程调用。此外，没有 SQLC 记录。

ServerName                                       insert_time
------------------------------------------------------------------------

commentary
-------------------------------------------------
2012-02-02 13:09:05.973 Firing sp_executesql

commentary
-------------------------------------------------
2012-02-02 13:09:10.983 Firing proc

ServerName                                       insert_time
------------------------------------------------------------------------
SQLB\SQLB                                        2012-02-02 13:09:10.983

拆掉脚本

此脚本以正确的顺序删除对象（在删除过程的引用之前不能删除类型）

-- cleanup
DROP TABLE dbo.UDTT_holder
DROP PROCEDURE dbo.Repro
DROP TYPE dbo.UDTT
GO

为什么这很重要

代码看起来很傻，但我对 sp_execute 的使用与对 proc 的“直接”调用没有太多控制。在调用链的上层，我正在使用 ADO.NET 库并像以前一样传入 TVP 。参数的类型正确设置为System.Data.SqlDbType.Structured并且 CommandType 设置为System.Data.CommandType.StoredProcedure。

我们在生产环境中过滤了索引。在对它们进行一些研究时，我遇到了这篇文章“过滤的索引和过滤的统计信息可能会严重过时”

这是一个基于代码值 0 的相当简单的过滤索引

CREATE NONCLUSTERED INDEX 
    [IX_InsuranceOffer_FIX_OfferCode0] 
    ON [dbo].[InsuranceOffer]
(
    [OfferId] ASC
)
WHERE ([OfferStatus]=(0))
WITH (PAD_INDEX = OFF,   STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF,  DROP_EXISTING = OFF
, ONLINE = OFF,          ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

分布看起来像

code   codeCount   code_distribution
------ ----------- -----------------
6      26186769    93.7526
0      1743401     6.2416
5      1107        0.0040
7      495         0.0018

我们的意图是修改现有索引以包含代码 5。根据这篇引爆点文章，我相信这两个查询都应该继续使用过滤索引。

我对试图了解代码波动性的系统所有者有疑问。

在那之前，我查看了sys.dm_db_index_physical_stats，试图了解我们当前的索引重建/重组策略是否足以跟上过滤后的索引。我怀疑不是，但我的内功很弱。

index_level avg_fragmentation_in_percent            fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent          record_count
----------- --------------------------------------- -------------------- -------------------------- -------------------- --------------------------------------- --------------------
0           0.6276                                  20                   143.4                      2868                 90.0984                                 1743401
1           42.8571                                 7                    1                          7                    86.0285                                 2868
2           0.0000                                  1                    1                          1                    1.4455                                  7

sys.stats因为该索引显示它的最后更新时间为“2012-01-06 22:03:11.147”

上述数据是否足以作为索引重建决策的依据，或者我是否需要涉及其他指标？或者，对于过滤索引，我们是否甚至关心碎片化并且应该只在 X 间隔显式更新统计信息？

我声称答案“取决于”

只有半相关的问题是Minimum rowcount for filtered index?

SSMS 2008 R2，连接到 2008 实例在 SSMS 对象资源管理器中显示上述字形。我们的其他实例都没有显示该图标。我徒劳地把鼠标悬停在它上面，希望我能得到一个有用的工具提示，但无济于事。

我认为这与我们上周末的一次失败有关。有人可以就该图标的含义以及我应该指导我的 DBA 采取什么行动来清除它提供更权威的回应吗？

我们正在将数据从遗留表（所有 varchar 字段）移动到强类型对应提示欢呼

作为这项工作的一部分，如果所有数据都可以正确转换为适当的类型，我们将从基本 Entity 表中获取数据并将其转储到 Entity_New 中。否则，它将进入名为 Entity_Bad 的现有表的副本。

我们有一个规则引擎来验证数据和类型，理论上，即使数据存储在字符字段中，数据也应该是干净的。现实是我在这里发帖，因为有些东西已经关闭，我找不到它。Entity 中的 CompletionDate 字段为 varchar(46) NULL

环境是

productversion  productlevel   edition
10.0.4064.0     SP2            Enterprise Edition (64-bit)

我的脚本展示了我在做什么以及什么是但不工作

SET NOCOUNT ON

DECLARE
    @startid int = 0
,   @stopid int = 796833


-------------------------------------------------------------------------------
-- Check doesn't find anything wrong with CompletionDate
-------------------------------------------------------------------------------
SELECT
    1
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND E.AAKey = PB.AAKey
            AND PB.ProcessResultCode IN ('A','W','M')
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND
    (
        -- check
        (isDate(E.[CompletionDate]) = 0 AND E.[CompletionDate] IS NOT NULL)
        AND (isDate(E.[CompletionDate]) = 1 AND CAST(E.[CompletionDate] AS datetime) BETWEEN '1753-01-01T00:00:00.000' AND '9999-12-31T23:59:59.997')
    )

-------------------------------------------------------------------------------
-- Only row that shows as non-date is the NULL one, which is expected
-------------------------------------------------------------------------------
SELECT DISTINCT
    (E.[CompletionDate] )
,  isDate(E.[CompletionDate])
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND PB.ProcessResultCode IN ('A','W','M')
    -- Ensure we aren't pulling something we have already processed
    LEFT OUTER JOIN
        [dbo].[Entity_new] N
        ON N.HashCode = E.HashCode
            AND N.FiscalYear = E.FiscalYear
                AND E.AAKey = N.AAKey
    -- Ensure we aren't pulling something we have already processed (or was bad)
    LEFT OUTER JOIN
        [dbo].[Entity_bad] BAD
        ON BAD.HashCode = E.HashCode
            AND BAD.FiscalYear = E.FiscalYear
                AND E.AAKey = BAD.AAKey
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND N.FiscalYear IS NULL
    AND BAD.FiscalYear IS NULL
ORDER BY 2

-------------------------------------------------------------------------------
-- Make the cast and it blows with
-- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
-------------------------------------------------------------------------------
SELECT DISTINCT
    (E.[CompletionDate] )
,   CAST(E.[CompletionDate] AS datetime) AS [CompletionDate]
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND PB.ProcessResultCode IN ('A','W','M')
    -- Ensure we aren't pulling something we have already processed
    LEFT OUTER JOIN
        [dbo].[Entity_new] N
        ON N.HashCode = E.HashCode
            AND N.FiscalYear = E.FiscalYear
                AND E.AAKey = N.AAKey
    -- Ensure we aren't pulling something we have already processed (or was bad)
    LEFT OUTER JOIN
        [dbo].[Entity_bad] BAD
        ON BAD.HashCode = E.HashCode
            AND BAD.FiscalYear = E.FiscalYear
                AND E.AAKey = BAD.AAKey
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND N.FiscalYear IS NULL
    AND BAD.FiscalYear IS NULL


-------------------------------------------------------------------------------
-- Dump the values into a temporary table to slice and dice the values
-------------------------------------------------------------------------------
DECLARE @debug TABLE
(
    CompletionDate varchar(46) NULL
)
INSERT INTO
    @debug
SELECT DISTINCT
    (E.[CompletionDate] )
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND PB.ProcessResultCode IN ('A','W','M')
    -- Ensure we aren't pulling something we have already processed
    LEFT OUTER JOIN
        [dbo].[Entity_new] N
        ON N.HashCode = E.HashCode
            AND N.FiscalYear = E.FiscalYear
                AND E.AAKey = N.AAKey
    -- Ensure we aren't pulling something we have already processed (or was bad)
    LEFT OUTER JOIN
        [dbo].[Entity_bad] BAD
        ON BAD.HashCode = E.HashCode
            AND BAD.FiscalYear = E.FiscalYear
                AND E.AAKey = BAD.AAKey
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND N.FiscalYear IS NULL
    AND BAD.FiscalYear IS NULL


-------------------------------------------------------------------------------
-- This is operating on all the same values as the failing query but magically works
-------------------------------------------------------------------------------
SELECT ALL
    CAST(E.[CompletionDate] AS datetime) AS [CompletionDate]
FROM
    @debug E



-------------------------------------------------------------------------------
-- Clearly, something is amiss when we extract the data so process each row
-- and find the culprit that way. Except this finds nothing wrong
-------------------------------------------------------------------------------
DECLARE @hash uniqueidentifier
,   @zee_date varchar(46)
,   @real_date datetime

DECLARE
    CSR CURSOR READ_ONLY
FOR
SELECT
    E.HashCode
,   E.[CompletionDate]
FROM
    [dbo].[Entity] E
    INNER JOIN
        dbo.EntityBatch_New PB
        ON E.FiscalYear = PB.FiscalYear
            AND E.HashCode = PB.HashCode
            AND PB.ProcessResultCode IN ('A','W','M')
    -- Ensure we aren't pulling something we have already processed
    LEFT OUTER JOIN
        [dbo].[Entity_new] N
        ON N.HashCode = E.HashCode
            AND N.FiscalYear = E.FiscalYear
                AND E.AAKey = N.AAKey
    -- Ensure we aren't pulling something we have already processed (or was bad)
    LEFT OUTER JOIN
        [dbo].[Entity_bad] BAD
        ON BAD.HashCode = E.HashCode
            AND BAD.FiscalYear = E.FiscalYear
                AND E.AAKey = BAD.AAKey
WHERE
    PB.EntityBatchId BETWEEN @StartId AND @StopId
    AND N.FiscalYear IS NULL
    AND BAD.FiscalYear IS NULL


OPEN CSR

FETCH NEXT FROM CSR INTO
    @hash, @zee_date

WHILE (@@fetch_status = 0)
BEGIN
    BEGIN TRY
        SELECT @real_date = cast(@zee_date AS datetime)
    END TRY
    BEGIN CATCH
        print 'In here'
        print @hash
        print @zee_date
        SELECT @hash, @zee_date
    END CATCH


    FETCH NEXT FROM CSR INTO
        @hash, @zee_date

END

CLOSE csr
DEALLOCATE csr

在用上面的代码墙批评你之后，这里是上面查询操作的 406 个唯一值。

DECLARE @REAL_DATES TABLE
(
    CompletionDate varchar(46) NULL
)

INSERT INTO
    @REAL_DATES 
SELECT
    NULL
UNION ALL SELECT '19000101'
UNION ALL SELECT '20100208'
UNION ALL SELECT '20100228'
UNION ALL SELECT '20100309'
UNION ALL SELECT '20100314'
UNION ALL SELECT '20100401'
UNION ALL SELECT '20100409'
UNION ALL SELECT '20100420'
UNION ALL SELECT '20100427'
UNION ALL SELECT '20100429'
UNION ALL SELECT '20100507'
UNION ALL SELECT '20100615'
UNION ALL SELECT '20100617'
UNION ALL SELECT '20100629'
UNION ALL SELECT '20100701'
UNION ALL SELECT '20100703'
UNION ALL SELECT '20100704'
UNION ALL SELECT '20100706'
UNION ALL SELECT '20100709'
UNION ALL SELECT '20100713'
UNION ALL SELECT '20100714'
UNION ALL SELECT '20100715'
UNION ALL SELECT '20100716'
UNION ALL SELECT '20100720'
UNION ALL SELECT '20100721'
UNION ALL SELECT '20100726'
UNION ALL SELECT '20100727'
UNION ALL SELECT '20100728'
UNION ALL SELECT '20100729'
UNION ALL SELECT '20100731'
UNION ALL SELECT '20100801'
UNION ALL SELECT '20100802'
UNION ALL SELECT '20100803'
UNION ALL SELECT '20100804'
UNION ALL SELECT '20100807'
UNION ALL SELECT '20100809'
UNION ALL SELECT '20100810'
UNION ALL SELECT '20100811'
UNION ALL SELECT '20100813'
UNION ALL SELECT '20100817'
UNION ALL SELECT '20100819'
UNION ALL SELECT '20100820'
UNION ALL SELECT '20100822'
UNION ALL SELECT '20100823'
UNION ALL SELECT '20100825'
UNION ALL SELECT '20100827'
UNION ALL SELECT '20100828'
UNION ALL SELECT '20100830'
UNION ALL SELECT '20100831'
UNION ALL SELECT '20100901'
UNION ALL SELECT '20100902'
UNION ALL SELECT '20100904'
UNION ALL SELECT '20100907'
UNION ALL SELECT '20100908'
UNION ALL SELECT '20100909'
UNION ALL SELECT '20100910'
UNION ALL SELECT '20100911'
UNION ALL SELECT '20100913'
UNION ALL SELECT '20100916'
UNION ALL SELECT '20100919'
UNION ALL SELECT '20100920'
UNION ALL SELECT '20100922'
UNION ALL SELECT '20100923'
UNION ALL SELECT '20100925'
UNION ALL SELECT '20100928'
UNION ALL SELECT '20101002'
UNION ALL SELECT '20101004'
UNION ALL SELECT '20101007'
UNION ALL SELECT '20101009'
UNION ALL SELECT '20101010'
UNION ALL SELECT '20101013'
UNION ALL SELECT '20101016'
UNION ALL SELECT '20101018'
UNION ALL SELECT '20101019'
UNION ALL SELECT '20101020'
UNION ALL SELECT '20101022'
UNION ALL SELECT '20101023'
UNION ALL SELECT '20101025'
UNION ALL SELECT '20101028'
UNION ALL SELECT '20101030'
UNION ALL SELECT '20101102'
UNION ALL SELECT '20101107'
UNION ALL SELECT '20101108'
UNION ALL SELECT '20101109'
UNION ALL SELECT '20101111'
UNION ALL SELECT '20101112'
UNION ALL SELECT '20101114'
UNION ALL SELECT '20101117'
UNION ALL SELECT '20101119'
UNION ALL SELECT '20101124'
UNION ALL SELECT '20101126'
UNION ALL SELECT '20101127'
UNION ALL SELECT '20101129'
UNION ALL SELECT '20101201'
UNION ALL SELECT '20101203'
UNION ALL SELECT '20101204'
UNION ALL SELECT '20101206'
UNION ALL SELECT '20101209'
UNION ALL SELECT '20101210'
UNION ALL SELECT '20101213'
UNION ALL SELECT '20101214'
UNION ALL SELECT '20101215'
UNION ALL SELECT '20101216'
UNION ALL SELECT '20101220'
UNION ALL SELECT '20101222'
UNION ALL SELECT '20101227'
UNION ALL SELECT '20101228'
UNION ALL SELECT '20101229'
UNION ALL SELECT '20101230'
UNION ALL SELECT '20110102'
UNION ALL SELECT '20110103'
UNION ALL SELECT '20110104'
UNION ALL SELECT '20110106'
UNION ALL SELECT '20110107'
UNION ALL SELECT '20110108'
UNION ALL SELECT '20110109'
UNION ALL SELECT '20110110'
UNION ALL SELECT '20110111'
UNION ALL SELECT '20110114'
UNION ALL SELECT '20110116'
UNION ALL SELECT '20110118'
UNION ALL SELECT '20110119'
UNION ALL SELECT '20110120'
UNION ALL SELECT '20110122'
UNION ALL SELECT '20110123'
UNION ALL SELECT '20110125'
UNION ALL SELECT '20110126'
UNION ALL SELECT '20110128'
UNION ALL SELECT '20110130'
UNION ALL SELECT '20110203'
UNION ALL SELECT '20110205'
UNION ALL SELECT '20110206'
UNION ALL SELECT '20110208'
UNION ALL SELECT '20110210'
UNION ALL SELECT '20110212'
UNION ALL SELECT '20110213'
UNION ALL SELECT '20110215'
UNION ALL SELECT '20110218'
UNION ALL SELECT '20110221'
UNION ALL SELECT '20110224'
UNION ALL SELECT '20110226'
UNION ALL SELECT '20110301'
UNION ALL SELECT '20110302'
UNION ALL SELECT '20110304'
UNION ALL SELECT '20110307'
UNION ALL SELECT '20110309'
UNION ALL SELECT '20110311'
UNION ALL SELECT '20110314'
UNION ALL SELECT '20110316'
UNION ALL SELECT '20110317'
UNION ALL SELECT '20110320'
UNION ALL SELECT '20110321'
UNION ALL SELECT '20110323'
UNION ALL SELECT '20110326'
UNION ALL SELECT '20110328'
UNION ALL SELECT '20110329'
UNION ALL SELECT '20110331'
UNION ALL SELECT '20110403'
UNION ALL SELECT '20110405'
UNION ALL SELECT '20110406'
UNION ALL SELECT '20110408'
UNION ALL SELECT '20110410'
UNION ALL SELECT '20110415'
UNION ALL SELECT '20110416'
UNION ALL SELECT '20110417'
UNION ALL SELECT '20110418'
UNION ALL SELECT '20110421'
UNION ALL SELECT '20110422'
UNION ALL SELECT '20110423'
UNION ALL SELECT '20110426'
UNION ALL SELECT '20110429'
UNION ALL SELECT '20110501'
UNION ALL SELECT '20110503'
UNION ALL SELECT '20110504'
UNION ALL SELECT '20110506'
UNION ALL SELECT '20110508'
UNION ALL SELECT '20110509'
UNION ALL SELECT '20110511'
UNION ALL SELECT '20110512'
UNION ALL SELECT '20110514'
UNION ALL SELECT '20110517'
UNION ALL SELECT '20110518'
UNION ALL SELECT '20110519'
UNION ALL SELECT '20110520'
UNION ALL SELECT '20110522'
UNION ALL SELECT '20110526'
UNION ALL SELECT '20110531'
UNION ALL SELECT '20110603'
UNION ALL SELECT '20110604'
UNION ALL SELECT '20110605'
UNION ALL SELECT '20110606'
UNION ALL SELECT '20110608'
UNION ALL SELECT '20110611'
UNION ALL SELECT '20110613'
UNION ALL SELECT '20110614'
UNION ALL SELECT '20110616'
UNION ALL SELECT '20110622'
UNION ALL SELECT '20110624'
UNION ALL SELECT '20110627'
UNION ALL SELECT '20110703'
UNION ALL SELECT '20110704'
UNION ALL SELECT '20110711'
UNION ALL SELECT '20110712'
UNION ALL SELECT '20110713'
UNION ALL SELECT '20110714'
UNION ALL SELECT '20110719'
UNION ALL SELECT '20110720'
UNION ALL SELECT '20110725'
UNION ALL SELECT '20110726'
UNION ALL SELECT '20110802'
UNION ALL SELECT '20110804'
UNION ALL SELECT '20110811'
UNION ALL SELECT '20090611'
UNION ALL SELECT '20091124'
UNION ALL SELECT '20100201'
UNION ALL SELECT '20100202'
UNION ALL SELECT '20100204'
UNION ALL SELECT '20100220'
UNION ALL SELECT '20100305'
UNION ALL SELECT '20100323'
UNION ALL SELECT '20100414'
UNION ALL SELECT '20100417'
UNION ALL SELECT '20100508'
UNION ALL SELECT '20100512'
UNION ALL SELECT '20100527'
UNION ALL SELECT '20100616'
UNION ALL SELECT '20100702'
UNION ALL SELECT '20100705'
UNION ALL SELECT '20100707'
UNION ALL SELECT '20100708'
UNION ALL SELECT '20100710'
UNION ALL SELECT '20100711'
UNION ALL SELECT '20100712'
UNION ALL SELECT '20100717'
UNION ALL SELECT '20100719'
UNION ALL SELECT '20100722'
UNION ALL SELECT '20100723'
UNION ALL SELECT '20100724'
UNION ALL SELECT '20100725'
UNION ALL SELECT '20100730'
UNION ALL SELECT '20100805'
UNION ALL SELECT '20100806'
UNION ALL SELECT '20100808'
UNION ALL SELECT '20100812'
UNION ALL SELECT '20100814'
UNION ALL SELECT '20100815'
UNION ALL SELECT '20100816'
UNION ALL SELECT '20100818'
UNION ALL SELECT '20100821'
UNION ALL SELECT '20100824'
UNION ALL SELECT '20100826'
UNION ALL SELECT '20100829'
UNION ALL SELECT '20100903'
UNION ALL SELECT '20100906'
UNION ALL SELECT '20100912'
UNION ALL SELECT '20100914'
UNION ALL SELECT '20100915'
UNION ALL SELECT '20100917'
UNION ALL SELECT '20100918'
UNION ALL SELECT '20100921'
UNION ALL SELECT '20100924'
UNION ALL SELECT '20100926'
UNION ALL SELECT '20100927'
UNION ALL SELECT '20100929'
UNION ALL SELECT '20100930'
UNION ALL SELECT '20101001'
UNION ALL SELECT '20101003'
UNION ALL SELECT '20101005'
UNION ALL SELECT '20101006'
UNION ALL SELECT '20101008'
UNION ALL SELECT '20101011'
UNION ALL SELECT '20101012'
UNION ALL SELECT '20101014'
UNION ALL SELECT '20101015'
UNION ALL SELECT '20101017'
UNION ALL SELECT '20101021'
UNION ALL SELECT '20101024'
UNION ALL SELECT '20101026'
UNION ALL SELECT '20101027'
UNION ALL SELECT '20101029'
UNION ALL SELECT '20101031'
UNION ALL SELECT '20101101'
UNION ALL SELECT '20101103'
UNION ALL SELECT '20101104'
UNION ALL SELECT '20101105'
UNION ALL SELECT '20101106'
UNION ALL SELECT '20101110'
UNION ALL SELECT '20101113'
UNION ALL SELECT '20101115'
UNION ALL SELECT '20101116'
UNION ALL SELECT '20101118'
UNION ALL SELECT '20101120'
UNION ALL SELECT '20101122'
UNION ALL SELECT '20101123'
UNION ALL SELECT '20101125'
UNION ALL SELECT '20101128'
UNION ALL SELECT '20101130'
UNION ALL SELECT '20101202'
UNION ALL SELECT '20101205'
UNION ALL SELECT '20101207'
UNION ALL SELECT '20101208'
UNION ALL SELECT '20101212'
UNION ALL SELECT '20101217'
UNION ALL SELECT '20101218'
UNION ALL SELECT '20101219'
UNION ALL SELECT '20101221'
UNION ALL SELECT '20101223'
UNION ALL SELECT '20101224'
UNION ALL SELECT '20101226'
UNION ALL SELECT '20101231'
UNION ALL SELECT '20110101'
UNION ALL SELECT '20110105'
UNION ALL SELECT '20110112'
UNION ALL SELECT '20110113'
UNION ALL SELECT '20110115'
UNION ALL SELECT '20110117'
UNION ALL SELECT '20110121'
UNION ALL SELECT '20110124'
UNION ALL SELECT '20110127'
UNION ALL SELECT '20110129'
UNION ALL SELECT '20110131'
UNION ALL SELECT '20110201'
UNION ALL SELECT '20110202'
UNION ALL SELECT '20110204'
UNION ALL SELECT '20110207'
UNION ALL SELECT '20110209'
UNION ALL SELECT '20110211'
UNION ALL SELECT '20110214'
UNION ALL SELECT '20110216'
UNION ALL SELECT '20110217'
UNION ALL SELECT '20110219'
UNION ALL SELECT '20110220'
UNION ALL SELECT '20110222'
UNION ALL SELECT '20110223'
UNION ALL SELECT '20110225'
UNION ALL SELECT '20110228'
UNION ALL SELECT '20110303'
UNION ALL SELECT '20110305'
UNION ALL SELECT '20110306'
UNION ALL SELECT '20110308'
UNION ALL SELECT '20110310'
UNION ALL SELECT '20110312'
UNION ALL SELECT '20110313'
UNION ALL SELECT '20110315'
UNION ALL SELECT '20110318'
UNION ALL SELECT '20110322'
UNION ALL SELECT '20110324'
UNION ALL SELECT '20110325'
UNION ALL SELECT '20110327'
UNION ALL SELECT '20110330'
UNION ALL SELECT '20110401'
UNION ALL SELECT '20110404'
UNION ALL SELECT '20110407'
UNION ALL SELECT '20110409'
UNION ALL SELECT '20110411'
UNION ALL SELECT '20110412'
UNION ALL SELECT '20110413'
UNION ALL SELECT '20110414'
UNION ALL SELECT '20110419'
UNION ALL SELECT '20110420'
UNION ALL SELECT '20110425'
UNION ALL SELECT '20110427'
UNION ALL SELECT '20110428'
UNION ALL SELECT '20110430'
UNION ALL SELECT '20110502'
UNION ALL SELECT '20110505'
UNION ALL SELECT '20110507'
UNION ALL SELECT '20110510'
UNION ALL SELECT '20110513'
UNION ALL SELECT '20110515'
UNION ALL SELECT '20110516'
UNION ALL SELECT '20110521'
UNION ALL SELECT '20110523'
UNION ALL SELECT '20110524'
UNION ALL SELECT '20110525'
UNION ALL SELECT '20110527'
UNION ALL SELECT '20110528'
UNION ALL SELECT '20110530'
UNION ALL SELECT '20110601'
UNION ALL SELECT '20110602'
UNION ALL SELECT '20110607'
UNION ALL SELECT '20110609'
UNION ALL SELECT '20110610'
UNION ALL SELECT '20110615'
UNION ALL SELECT '20110617'
UNION ALL SELECT '20110618'
UNION ALL SELECT '20110620'
UNION ALL SELECT '20110621'
UNION ALL SELECT '20110623'
UNION ALL SELECT '20110626'
UNION ALL SELECT '20110628'
UNION ALL SELECT '20110629'
UNION ALL SELECT '20110630'
UNION ALL SELECT '20110701'
UNION ALL SELECT '20110706'
UNION ALL SELECT '20110707'
UNION ALL SELECT '20110708'
UNION ALL SELECT '20110715'
UNION ALL SELECT '20110717'
UNION ALL SELECT '20110721'
UNION ALL SELECT '20110722'
UNION ALL SELECT '20110727'
UNION ALL SELECT '20110729'
UNION ALL SELECT '20110801'
UNION ALL SELECT '20110810'

SELECT 
    CAST(RD.CompletionDate AS datetime) AS casts_fine
FROm
    @REAL_DATES RD

我很欣赏关于 SSIS 或其他方法的评论，但在游戏的这一点上，我们已经与 TSQL 转换方法结合了。如果有人能指出我遗漏了什么，我会以你的名字命名我的第一个孩子，假设你不介意把你的名字改成詹姆斯。