mheptinstall's questions

我有一个包含 80 多个稀疏列以及一个列集列的表，这是一个简短的示例：

DROP TABLE IF EXISTS #ColumnSet
GO

CREATE TABLE #ColumnSet
(
    Id        INT          NOT NULL
  , Value1    VARCHAR(100) SPARSE NULL 
  , Value2    VARCHAR(100) SPARSE NULL 
  , Value3    VARCHAR(100) SPARSE NULL 
  , Value4    VARCHAR(100) SPARSE NULL 
  , AllValues XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
GO

INSERT INTO #ColumnSet
(Id, Value1, Value2, Value3, Value4)

VALUES
(1, 'POSITIVE', NULL, NULL, NULL),
(2, 'NEGATIVE', NULL, 'NEGATIVE', NULL),
(3, NULL, NULL, 'NEGATIVE', 'POSITIVE'),
(4, 'NEGATIVE', NULL, 'THIS IS NOT A POSITIVE RESULT', NULL)
GO

我想查询列集以识别任何列的值为 POSITIVE 的行。

在列集上使用该value方法会将所有值连接到一个字符串中，我可以使用LIKE，但我不想要值在另一个字符串中的结果。

SELECT
    *

FROM
    #ColumnSet

WHERE
    AllValues.value('/', 'nvarchar(4000)') LIKE '%POSITIVE%'

是否有查询列集以实现上述目标的替代方法？APPLY与该方法一起使用可nodes提供相同的串联字符串输出，尽管我的语法可能不正确。

所需的输出：

id
1
3

我们在 SQL Server 数据库项目中有源代码控制下的现有数据库，其中许多数据库相互引用。

将它们纳入项目和源代码控制非常具有挑战性，尽管我发现在不使用新的 SQL 服务器实例的情况下很难设置开发/测试环境。

目前，我们在一个测试 SQL 实例上有一个数据库副本，它被用作持续部署的一部分。我想要做的是在一个分支中开发并在同一实例上使用 Dev 前缀的数据库，尽管我意识到这些将引用用于持续部署的数据库。

这是否可以通过在数据库引用中使用数据库变量而不仅仅是名称来实现，如果是这样的话，如何以及是否有办法在项目之间共享 SQLCMD 变量？有没有其他方法可以考虑？

当事实表与日期维度（例如每月）的粒度更高时，应该如何将其表示为数据源视图中的关系？

对于包含列DateKey和MonthDateKey事实表的日期维度MonthDateKey，关系应该是：

Fact.MonthDateKey -> Date.DateKey或者Fact.MonthDateKey -> Date.MonthDateKey

如果在数据源视图中设置不正确，会有什么影响？

我的事实表包含典型维度的投诉，包括谁、什么和何时。我们有一个目标，即投诉应在一定时间内得到答复。

我不确定如何最好地模拟投诉是否在该时间段内得到回应。

我可以将目标值和结果作为整数存储在事实表中，并将结果作为度量。或者我可以使用一个值为是/否的维度，而不是将其表示为一个度量，然后我可以在我的多维数据集中使用计算的度量。或者我可以使用两者的组合。

以上述方式对事实进行建模是否有任何优点、缺点和陷阱？

我预计该事实表将用于获取投诉总数、及时响应的投诉数量、及时响应的百分比以及识别未及时响应的个人投诉。

将现有数据库 (SQL2012) 导入到 VS2017 中的 SSDT 项目，并在尝试将其部署到我的本地实例 (SQL2016) 时收到错误消息：

为程序集“System.DirectoryServices”创建程序集失败，因为程序集“System.DirectoryServices”验证失败。

将它指向以下内容时，我可以在 SSMS 的数据库中创建程序集：

C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.DirectoryServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.DirectoryServices.dll

在我的 SSDT 项目中，我将目标框架设置为 SQLCLR 下的 .NET Framework 4。

对 System.DirectoryServices 的引用已将 Generate Sql Script 和 Model Aware 选项设置为 true，并将权限设置为 Unsafe。

数据库设置还启用了可信任选项。

select * from sys.dm_clr_properties在我的实例上运行返回：

directory   C:\Windows\Microsoft.NET\Framework64\v4.0.30319\
version v4.0.30319 
state   CLR is initialized

我需要更改什么才能使我的项目成功发布？

完整的错误信息如下。

Creating [System.DirectoryServices]...
Warning: The Microsoft .NET Framework assembly 'system.directoryservices, version=4.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
(221,1): SQL72014: .Net SqlClient Data Provider: Msg 6218, Level 16, State 2, Line 1 CREATE ASSEMBLY for assembly 'System.DirectoryServices' failed because assembly 'System.DirectoryServices' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.DirectoryServices.ActiveDirectorySecurity::PurgeAccessRules][mdToken=0x6000009][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::PurgeAuditRules][mdToken=0x6000010][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::get_AccessRightType][mdToken=0x6000015][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::get_AccessRuleType][mdToken=0x6000016][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::get_AuditRuleType][mdToken=0x6000017][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::ModifyAccessRule][mdToken=0x6000008][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::ModifyAuditRule][mdToken=0x600000f][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AccessRuleFactory][mdToken=0x6000011][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AuditRuleFactory][mdToken=0x6000013][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AccessRuleFactory][mdToken=0x6000012][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AuditRuleFactory][mdToken=0x6000014][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::.ctor][mdToken=0x6000001][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurity::AddAccessRule][mdToken=0x6000002][offset 0x00000000] Code size is zero.
[ : System.DirectoryServices.ActiveDirectorySecurit...
(221,0): SQL72045: Script execution error.  The executed script:
CREATE ASSEMBLY [System.DirectoryServices]
    AUTHORIZATION [dbo]
    FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300A1E3A14B0000000000000000E00022200B01080000E801000008000000000000C205020000200000002002000000AC65002000000002000004000000000000000400000000000000006002000002000062580200030040850000100000100000000010000010000000000000100000000000000000000000700502004F000000002002006C040000000000000000000000F2010070170000004002000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000038E601000020000000E8010000020000000000000000000000000000200000602E727372630000006C040000002002000006000000EA0100000000000000000000000000400000402E72656C6F6300000C00000000400200
An error occurred while the batch was being executed.

事件的持续时间事件字段没有描述，module_end我正在努力寻找任何其他文档。

我将其解释为毫秒，但查看某些结果时这是不正确的，因为我的执行持续了 121 天。此持续时间是否以微秒表示？

我想捕获存储过程执行细节，这需要更改现有过程以在开始时执行插入并在结束时执行更新。

记得前段时间找到一篇博文，演示在存储过程的开头/结尾添加语句，但是找不到。

我如何在 C# 或 T-SQL 中以编程方式进行这些更改，以确保原始存储过程定义按预期工作？

编辑：至少我想捕获用户执行、存储过程名称、开始和结束时间。参数和存储过程定义是可选的。

我有一个关键字表，我想查询另一个表并返回其中一列至少包含一个关键字的行。

是否可以使用全文索引进行查询？

如果没有，是否有比下面的方法更有效的方法——也许是动态 SQL？

SELECT
    *

FROM
    dbo.Notes AS NotesContainingKeyword

WHERE
    EXISTS
    (
        SELECT
            *

        FROM
            dbo.Notes
            CROSS JOIN dbo.Keywords

        WHERE
            Notes.Note LIKE '%' + Keywords.Word '%'
            AND Notes.Id = NotesContainingKeyword.Id
    )

如果我查询存在于两个不同模式中但具有相同名称的表，那么什么决定了使用哪个表？

例如，我有Bar一个同时存在于dboand中的表foo，如果我执行下面的语句，则返回一个：

SELECT * FROM Bar

我认为这是由用户默认模式决定的，但似乎并非如此。

我问是因为我发现其他人编写的查询没有指定架构名称，但被查询的数据库在 ETL 的不同架构中具有相同的表。

我曾在 SQL Server 环境中工作过，在此之前，日志记录已成为我们存储过程的一部分，以捕获执行开始/结束、参数值和错误消息，我发现它们非常有用，并且我希望在新环境中引入这些内容。

用于此日志记录的表如下所示，使用INSERT语句将参数捕获到表中，其中的值被隐式转换为NVARCHAR.

CREATE TABLE dbo.Execution
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   SchemaName          NVARCHAR(128)               NOT NULL
,   ProcedureName       NVARCHAR(128)               NOT NULL
,   ExecutionStart      DATETIME                    NOT NULL
,   ExecutionEnd        DATETIME                    NULL
,   ExecutionFailed     BIT                         NOT NULL
)

CREATE TABLE dbo.ExecutionError
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   ExecutionId         INT                         NOT NULL
,   CustomErrorMessage  NVARCHAR(8000)              NULL
,   SqlErrorMessage     NVARCHAR(8000)              NULL
)

CREATE TABLE dbo.ExecutionParameter
(
    Id                  INT IDENTITY(1,1)           NOT NULL
,   ExecutionId         INT                         NOT NULL
,   ParameterName       NVARCHAR(128)               NOT NULL
,   ParameterValue      NVARCHAR(MAX)               NULL
)

我一直在重新考虑ExecutionParameter使用SQL_VARIANT数据类型的可能性的表格，因此如果需要分析和报告目的，我可以获得基本数据类型，而不必根据名称/值进行猜测。

但是，这不适用于数据类型为NVARCHAR(MAX)or的参数，因此在大多数情况下VARCHAR(MAX)仍需要该NVARCHAR(MAX)列。NULL

的使用SQL_VARIANT很诱人，但我觉得原始表结构工作正常，如果不使过程更复杂，就不能真正做得更好。

这是您以前做过的事情，如果是，您是如何实现的？你能看到上述模式的改进空间而不使其过于复杂吗？

也许还有一个包含存储过程详细信息及其参数的附加表，可以参考？虽然我觉得随着时间的推移存储过程被修改，这将难以维护并且变得混乱。

我有一个视图涉及 20 多个表上的 LEFT JOINS 到基表，但是在查询此视图并查看执行计划时，我得到了一些完全出乎意料的结果。

例如，对于下面的查询，我希望在表上进行聚簇索引查找：

SELECT id FROM dbo.TableName WHERE id = 256

而当我使用视图时，我会在表中查找聚集索引以及 3 个嵌套循环（左外连接）。

SELECT id FROM dbo.ViewName WHERE id = 256

为什么会出现这种情况？上面是一个非常简单的例子，但是当从视图中选择额外的列时，执行计划中会出现越来越多不必要的 LEFT JOINS。

我正在尝试实现一个服务器范围的 DDL 触发器，它将使用与此处找到的文章非常相似的脚本来审核服务器上数据库中的 DDL 更改

触发器会将 DDL 事件记录到一个数据库中的一个表中，但是我遇到了权限问题，一些用户，甚至我自己拥有系统管理员权限，都会收到以下错误消息。

Msg 297, Level 16, State 1, Procedure LogDDLEvent, Line 19
The user does not have permission to perform this action.

我已经阅读了有关使用该WITH EXECUTE AS子句并专门为执行触发器创建登录的信息，尽管这似乎并没有解决问题。

有人可以就实现此类触发器的正确​​方法提出建议，以及用户或特定登录需要哪些权限才能在 WITH EXECUTE AS 中使用？

编辑：一些进一步的信息

服务器正在运行 SQL Server 2008 R2，并且 DDL 触发器设置为 DDL_EVENTS，如下所示：

CREATE TRIGGER LogDDLEvent
ON ALL SERVER
FOR DDL_EVENTS
AS

DECLARE     @eventInfo XML
SET         @eventInfo = EVENTDATA()

INSERT INTO Tools.audit.DDLEvent
VALUES
(
      REPLACE(CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' ') -- EventTime
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/EventType)')) -- EventType
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/LoginName)')) -- LoginName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/UserName)')) -- UserName
      , CAST(HOST_NAME() AS VARCHAR(128)) -- MachineName
      , (SELECT   CAST(client_net_address AS VARCHAR(128))
            FROM  sys.dm_exec_connections
            WHERE Session_id = CONVERT(INT, @eventInfo.value('data(/EVENT_INSTANCE/SPID)[1]', 'int'))) -- IPAddress
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/DatabaseName)')) -- DatabaseName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/SchemaName)')) -- SchemaName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectName)')) -- ObjectName
      , CONVERT(VARCHAR(128), @eventInfo.query('data(/EVENT_INSTANCE/ObjectType)')) -- ObjectType
      , CONVERT(VARCHAR(MAX), @eventInfo.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)')) -- DDLCommand
      , @eventInfo -- DDLEventXML
)

上面的脚本是我最初创建触发器的方式，这对我和其他一些用户来说效果很好，尽管对特定数据库具有 db_owner 角色的用户不断收到上面的错误消息。

在尝试实现 WITH EXECUTE AS 后，我发现触发器对我自己不起作用。

使用 SQL Server 2008 和基于集合的解决方案，我如何操作以下数据：

GroupID     Code    StartSeq    EndSeq  StartDayNo  EndDayNo
1755549     3506    0           0       59442       59444
1755549     2928    1           3       59444       59465
1755549     2928    4           5       59465       59467
1755549     2928    6           6       59467       59481
1755549     2928    7           8       59481       59482
1755549     2928    9           9       59482       59494
1755549     3429    10          10      59494       59494
1755549     2928    11          11      59494       59496
1755549     3429    12          12      59496       59496
1755549     2928    13          13      59496       59501

要得到这个结果：

GroupID     Code    StartSeq    EndSeq  StartDayNo  EndDayNo
1755549     3506    0           0       59442       59444
1755549     2928    1           9       59444       59494
1755549     3429    10          10      59494       59494
1755549     2928    11          11      59494       59496
1755549     3429    12          12      59496       59496
1755549     2928    13          13      59496       59501

我最初提出的查询只会通过使用连接将 2 行组合在一起，在示例中有 5 行需要仅组合为 1 行。

下面的脚本包含一些示例数据。请注意，除了原始示例之外还有其他数据。

DECLARE @SampleData TABLE (
    [GroupID] [int] NOT NULL,
    [Code] [varchar](4) NOT NULL,
    [StartSeq] [int] NOT NULL,
    [EndSeq] [int] NOT NULL,
    [StartDayNo] [int] NOT NULL,
    [EndDayNo] [int] NOT NULL
)

INSERT INTO @SampleData
VALUES
 (1622494, N'2082', 0, 0, 59136, 59137)
, (1622494, N'2082', 1, 1, 59137, 59167)
, (1622494, N'2934', 2, 2, 59167, 59335)
, (1622494, N'3613', 3, 3, 59335, 59350)
, (1755549, N'3506', 0, 0, 59442, 59444)
, (1755549, N'2928', 1, 3, 59444, 59465)
, (1755549, N'2928', 4, 5, 59465, 59467)
, (1755549, N'2928', 6, 6, 59467, 59481)
, (1755549, N'2928', 7, 8, 59481, 59482)
, (1755549, N'2928', 9, 9, 59482, 59494)
, (1755549, N'3429', 10, 10, 59494, 59494)
, (1755549, N'2928', 11, 11, 59494, 59496)
, (1755549, N'3429', 12, 12, 59496, 59496)
, (1755549, N'2928', 13, 13, 59496, 59501)