Louis Somers's questions

我有一个设置层次结构，每个人都有默认设置，可以被组或单个用户覆盖，用于特定屏幕等......

这些存储在一个带有 Hirarchy-ID 的表中以进行排序，如下所示：

HIERARCHY | SETTING | OPTION | PREFERENCE | COLOR |
----------+---------+--------+------------+-------+
100       | NULL    | 1      | NULL       | NULL  |
10        | NULL    | 2      | square     |       |
1         | 0       | 3      | rounded    | green |

我追求的结果是每一列的第一个非空值（如果我也可以压缩一个 NULLIF(COLOR, '') 那就太好了）。在此示例中，结果将是：

SETTING | OPTION | PREFERENCE | COLOR |
--------+--------+------------+-------+
0       | 1      | square     | green |

目前我正在对每一列进行单独的查询，但我希望这可以以更优雅的方式实现？

我正在使用 MS SQL Server（并且需要支持 2005 版）。

我正在编写批处理插入语句，并希望使用临时表来跟踪插入的 ID，而不是自己循环遍历项目并为每个插入的行调用 SCOPE_IDENTITY()。

需要插入的数据具有（临时）ID 将其链接到也应插入另一个表的其他数据，因此我需要实际 Id 和临时 Id 的交叉引用。

这是我到目前为止的一个例子：

-- The existing table 
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));

-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');

DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);

INSERT INTO @MyTable ( [Name] )
   OUTPUT Inserted.ID, INS.ID INTO @MyCrossRef
   SELECT [NAME] FROM @MyInsertData INS

-- Check the result
SELECT * FROM @MyCrossRef

问题是我无法让 OUTPUT INTO 子句接受 ID，我已经尝试过@MyInsertData.ID将表连接到自身的其他技巧，但似乎没有任何效果。

我正在尝试构建一个 where 子句，该子句需要 X 数量的子项与具有 N 对 N 关系的特定父项建立关系。类似于“获取这些产品一起销售的所有收据”。一个示例场景更容易解释：

DECLARE @Nodes TABLE(
  Id INT
);

DECLARE @Arc TABLE(
  Id INT IDENTITY(1,1),
  Source INT, -- FK @Nodes.Id
  Dest INT    -- FK @Nodes.Id
);

INSERT INTO @Nodes (Id) VALUES (1),(2),(3),(4);

INSERT INTO @Arc (Source, Dest) VALUES
   (1, 2)
  ,(1, 3)
  ,(1, 3)
  ,(2, 1)
  ,(2, 3)
  ,(2, 4);

在 SP 中，输入被预处理成这样的表：

DECLARE @InputConnectedNodes TABLE ( Id INT );
INSERT INTO @InputConnectedNodes (Id) VALUES (3),(4); -- not a fixed number of nodes

在此示例中，我们希望所有节点都与节点 3 和 4 有弧（上例中只有节点 2）。目前我们有这个：

SELECT DISTINCT Source 
    FROM @Arc
    WHERE Dest IN (SELECT Id FROM @InputConnectedNodes);

然后我们有一些丑陋的程序代码循环检查所有关系的输入和（嵌套）输出。我想用一些声明来代替它。到目前为止我想到的最好的是：

SELECT Source 
    FROM @Arc 
    WHERE Dest IN (SELECT Id FROM @InputConnectedNodes) 
    GROUP BY Source
    HAVING COUNT(Source) = (SELECT COUNT(*) FROM @InputConnectedNodes)

但在我的示例中，当节点具有多个（相同的）弧（如 (1->3)）时，它将失败。

我也可以通过动态生成 where 子句来解决这个问题，但我更喜欢完全声明式的解决方案。

看起来表达式中的第二个变量正在确定我的第一个变量的最大长度。

一个更容易解释的例子：

DECLARE @MyDest AS NVARCHAR(MAX) = 'Some long text, for testing';
DECLARE @MySrc AS NVARCHAR(6) = '';

SELECT @MyDest = ISNULL(NULLIF(@MySrc, N''), @MyDest);

SELECT @MyDest;

结果是'Some l'我所期望'Some long text, for testing'的。

这是我正在尝试修复的更复杂错误的简化版本。这是 SQL Server 中的错误还是我应该以不同的方式执行此操作？

我正在寻找一种特定的“最佳实践”或“模式”，涉及在不同实体之间共享的实体，与其中一个实体有关系。

例如，一个人可能有通用实体“地址”，它可用于存储客户、供应商、员工等的公共地址字段......

经验丰富的 DBA 会采用这种方式还是愿意将字段添加到相应的实体中？我也在考虑可维护性，可能（在未来）因实体而异的约束，诸如此类。

我很想获得有关该主题的任何权威或已建立作品的参考。

我正在尝试优化来自第 3 方的数据库中表的触发器。我不允许更改或向模式添加任何列。

该表有一个复合主键，一列是整数，另一列是nvarchar。

如何在不使用临时表的情况下检索已删除的行？

我目前正在 where 子句中的字符串中连接键，如下所示：

SELECT FROM deleted WHERE (deleted.a + CAST(deleted.b as nvarchar) 
       NOT IN (SELECT deleted.a + CAST(deleted.b as nvarchar) 
       FROM inserted, deleted 
       WHERE inserted.a = deleted.a 
         and inserted.b = deleted.b))

我也尝试了几种不同deleted.%%physloc%%的尝试，但没有成功。有没有更优雅的方法来过滤掉已删除的记录？

我有一个连接几个表的视图，只是为了简化一个经常调用的查询。此视图在计算字段上有一个 ORDER BY 子句（顺序永远不会不同）。（在 SQL Server Express 2008 R2 上）。

基本思路是这样的（简化）：

SELECT [EventType].Name, 
    [EventType].TotalOccurrences, 
    [Session].ID, 
    [Session].TotalOccurrences, 
    [Session].TotalOccurrences / [EventType].TotalOccurrences AS saturation
FROM [EventType]
    INNER JOIN [Session] ON [EventType].ID = [Session].Event
ORDER BY saturation

但是，如果没有 where 子句，则永远不会调用此视图（在我的情况下，它几乎会返回整个数据库）。它将永远与WHERE [EventType].ID = x. 但是，只要我添加 where 子句，ORDER BY 子句就会被忽略！如果没有 where 子句，它会按预期工作。

实际上是一个不同的问题，但我在这里把它扔在这里，有没有更好的方法来检索这些信息？[EventType].Name和[EventType].TotalOccurrences对于所有返回的行都是相同的，这是网络带宽的一小部分。这不是一个问题，但我想知道是否有任何东西可以解决这个问题而没有多次往返数据库的延迟开销？

[编辑] 上面的示例过于简单，计算字段有其他因素，因此它返回的顺序与ORDER BY [Session].TotalOccurrences本示例中的简单顺序不同，但原理应该很清楚。

[Edit2] 从答案中我得出的结论是图形设计器比底层数据库具有更多功能：

基本信息

• 数据库：SQL Server Express 2008 R2
• 客户端：SQL Server 管理工作室

背景（不感兴趣可跳过）：

我正在维护的一个项目使用 ORM，它显然将我的枚举值（从 Byte 继承）存储到存储在 varbinary(max) 字段中的二进制序列化 .Net 对象中。我只是在出现一个新要求要求我的代码在中等信任下运行之后才发现这种情况发生了。由于 .Net 二进制格式化程序需要完全信任才能被调用，它开始在枚举上崩溃。

为了清理混乱，我需要创建迁移脚本，将这些 (varbinary(max)) 值转换回整数值。只有少数几个不同的值，所以它不应该是一个大问题（我认为）。

问题：

选择时，我能够获得 blob 的字符串表示形式：

SELECT BinValue FROM MyTable where Type = 'Object';

它返回一个字符串'0x...（十六进制值数组）'。

但是，当我尝试使用复制和粘贴在列上进行选择时，请确保我具有确切的二进制等效项：

SELECT ItemId FROM MyTable WHERE Value=convert(varbinary(max), '0x...')

它不返回任何记录。

那么是否可以使用客户端（例如 Management Studio）来解决这个问题？

如果是这样，正确的语法是什么？