主页 / user-26463

John's questions

Martin Hope
John
Asked: 2022-12-22 07:08:29 +0800 CST
  • 7

SQL Server 要求用户具有 ALTER TABLE 权限才能将特定值插入 IDENTITY 列。

这似乎有点奇怪，因为插入显然是 DML 操作。

要解释为什么这对我来说有点问题，首先要记住数据库不会在事务回滚时回滚标识值。这是因为数据库允许同时插入多个事务。

因此，如果稍后插入行的事务失败并且必须重复，则可以安全地重新使用已经从第一次尝试中检索到的标识值。

我正在编写一个数据库编辑器，这种方法使复杂插入的实现变得更加容易：用户逐渐构建需要键的相关表行的图形，但键是数据库生成的。ORM 为此使用临时键并在提交后重写所有内容，但这显然更难实现。对于每个新的插入步骤总是去数据库，获取标识值，然后回滚会更容易。只有当用户最后点击保存时，事务才会被提交，并且值应该还是没问题的。

这适用于其他一些数据库，如 MySQL 和 Postgres，但 SQL Server 有一个需要惊人的高级别特权的怪癖。

我的问题是为什么会这样，以及这是否可能表明我到目前为止错过的关于显式插入标识值的其他一些问题。

sql-server
Martin Hope
John
Asked: 2022-10-11 12:54:36 +0800 CST
  • 0

作为应用程序开发人员，我习惯于仅将数据库事务用作在用户单击“保存”后进行修改的一种方式。

这是我熟悉的大多数数据库服务器期望它们的事务被使用的方式，它们不喜欢长期事务。在许多情况下，它们会导致其他作者甚至读者无法处理此类阻塞。

但我只熟悉数据库世界的一部分——我主要使用 SQL Server 并且见过一些 MySQL。这些数据库主要用作应用程序存储，数据库本身的业务逻辑大多被简化为以一种或另一种方式生成唯一 ID。

我可以想象其他服务器，例如 Oracle，有不同的期望。

我感兴趣的方法是，当用户单击“编辑”时，会打开一个事务，并且用户所做的所有编辑都会立即发送到数据库。因此，所有业务逻辑都会立即应用，因此甚至在“保存”（又名“提交”）之前就可以在用户界面中看到。

这种范式将使几件事变得更容易：

  • 进行编辑的应用程序不必像 ORM 那样管理未保存行的初步 id。
  • 用户甚至可以在提交更改之前从数据库服务器中的业务逻辑获得反馈，例如计算列的值或触发器的结果效果。
  • 如果在提交之前进行了大量更改，则可以在编辑的早期检测到约束违规。
  • 如果数据库服务器对其有很好的支持，来自多个事务的冲突编辑可能会导致更好的错误消息，例如“用户 abc 正在编辑行 xyz”。

我已经调查了 SQL Server 对这种方法的支持状态，它即将成为可能，但在实践中通常可能是个坏主意。主要问题是即使在快照隔离下，那里的编写者也会相互锁定。特别是，先写的作者获胜，而不是先提交的作者。

我的问题是：是否有更好地支持这种场景的数据库服务器？例如，Oracle 对此有何评论？特别是，服务器必须

  • 允许并发写入同一行而不会阻塞并让第一个提交者获胜。
  • 因此，已写入的未提交的悬空事务根本不应该影响其他用户。如果另一个用户提交了一个应该可以工作的冲突写入，并且悬空事务只是变得不可提交（或者只是在那时自动回滚）。

我正在为我的通用数据库浏览器研究这个；

sql-server oracle
Martin Hope
John
Asked: 2022-10-11 12:11:17 +0800 CST
  • 3

SQL Server 中的已提交读快照和快照隔离级别取消了大多数锁定，除了一个：写入者仍然锁定其他写入者。

文档蹑手蹑脚地说了这么多，随后没有记录任何其他非常有趣的内容：

它真的只是一个被独占锁定的修改行吗？或者它也可以是不相关的行（例如，在索引中相邻）或页面？

我确实查看了锁，sys.dm_tran_locks并且我只在未提交的事务期间看到修改行上的排他锁 - 页面仅锁定为IX.

我还测试了两个事务是否可以在一个非常小的表中的两个未提交事务期间同时修改两个不同的行，该表可能适合一页并且效果也很好。

如果确实只有修改的行被独占锁定，那么如果它确保没有两个连接同时写入同一行，那么这将为具有对数据库的独占访问权限的应用程序提供无锁写入的保证。

这在我想到的场景中是可能的——但是如果页面锁定起作用，几乎没有办法做这样的事情，因为无法预测哪些行会受到影响。

sql-server locking
Martin Hope
John
Asked: 2022-09-09 07:54:54 +0800 CST
  • 5

一个LIKE子句可以测试一个字符串是否出现在另一个字符串中，并且该CHARINDEX函数可以给出第一个匹配项的开始位置。

就我而言，我对结束位置感兴趣，因为排序规则的复杂性，不能从开始位置推导出来。例如，在德语归类 ( German_PhoneBook_100_CI_AS_SC_UTF8) 中，

  • hä出现在位置 1 的“Häger”中并在位置 2 处结束，并且
  • hä出现在位置 1 的 'Haeger' 中，并在位置 3 结束。

这样做的问题是为用户的利益标记搜索结果文本的匹配部分。

我一直在考虑反转字符串，但是我仍然只能得到第一个匹配项，CHARINDEX在这种反转的情况下，我需要最后一个匹配项。

有什么想法吗？

sql-server collation
Martin Hope
John
Asked: 2022-08-31 07:34:38 +0800 CST
  • 8

考虑以下具有多列索引的示例表：

create table BigNumbers (
    col1 tinyint not null,
    col2 tinyint not null,
    col3 tinyint not null,

    index IX_BigNumbers clustered (col1, col2, col3)
)

DECLARE @n INT = 100;

DECLARE @x1 INT = 0;
DECLARE @x2 INT = 0;
DECLARE @x3 INT = 0;

SET NOCOUNT ON;

WHILE @x3 <= @n BEGIN
    SET @x2 = 0;
    WHILE @x2 <= @n BEGIN
        SET @x1 = 0;
        WHILE @x1 <= @n BEGIN
            insert into BigNumbers values (@x1, @x2, @x3);
            SET @x1 = @x1 + 1;
        END;
        SET @x2 = @x2 + 1;
    END;

 SET @x3 = @x3 + 1;
END;

我现在的目标是从该索引中获取几行，从给定的键开始。

听起来微不足道的事情有点复杂，因为在 SQL 中没有简单的方法来表达索引所在的字典顺序：

DECLARE @x1 INT = 60;
DECLARE @x2 INT = 40;
DECLARE @x3 INT = 98;

select top 5 *
from BigNumbers 
where
  col1 > @x1 or
 (col1 = @x1 and
   (col2 > @x2 or
   (col2 = @x2 and col3 >= @x3)))
order by col1, col2, col3

正确的结果是：

60  40  98
60  40  99
60  40  100
60  41  0
60  41  1

但是，查询计划告诉我这使用索引扫描。

底层索引应该能够查找并返回大于或等于(@x1, @x2, @3)索引顺序的前几行，但由于 SQL 无法轻松表达此意图，因此查询规划器似乎无法接受提示，而是进行扫描.

索引提示无济于事，并且FORCESEEK给出了一个可怕的计划。

有趣的是，以下两列版本有效：

select top 5 *
from BigNumbers 
where
  col1 = @x1 and
   (col2 > @x2 or
   (col2 = @x2 and col3 >= @x3))
order by col1, col2, col3

我不确定为什么会这样，但该计划不仅使用搜索，它还正确报告只触及了 5 行：

在此处输入图像描述

我想知道是否有人知道一种方法可以通过简单的搜索可靠地查询大于或等于给定值元组的几行索引。

数据库在其更高层次的抽象下掩盖了这种基本能力，这似乎很奇怪。

如果有人有兴趣知道这是什么问题，我正在为 SQL 数据库开发一个通用 UI。您需要此功能的最明显的地方是“加载更多”按钮，您希望在该按钮上继续显示给定起点的索引内容。如果这通常是不可能的，解决方法是首先查询修复除最后一列以外的所有列，然后进行第二次查询，依此类推。不过，不得不这样做会有点可惜。

sql-server index
Martin Hope
John
Asked: 2017-08-03 04:25:15 +0800 CST
  • 2

Sql Server的更改跟踪功能允许识别修改后的数据库副本的修改行。

这显然对同步作业有帮助，因为数据库随后可以增量同步。

我的问题是：是否有使用此功能实际进行增量同步的工具？

我知道

  • SSDT：可以同步，但据我所知对更改跟踪数据不感兴趣。
  • MS Sync Framework：是一个框架，我正在寻找一个应用程序。

我真的必须自己写点什么吗？

如果将源作为链接服务器引入，是否有一些很酷的 SQL 脚本可以做到这一点？

sql-server data-synchronization
Martin Hope
John
Asked: 2017-07-24 02:46:02 +0800 CST
  • 10

SQL Server 有一个叫做“多列统计”的东西，但这并不是人们认为的意思。

让我们看一下下面的示例表：

CREATE TABLE BadStatistics 
(
    IsArchived BIT NOT NULL,
    Id INT NOT NULL IDENTITY PRIMARY KEY,
    Mystery VARCHAR(200) NOT NULL
);

CREATE NONCLUSTERED INDEX BadIndex 
    ON BadStatistics (IsArchived, Mystery);

这样，我们就在我们拥有的两个索引上创建了两个统计信息：

BadIndex 的统计数据：

+--------------+----------------+-------------------------+
| All density  | Average Length | Columns                 |
+--------------+----------------+-------------------------+
| 0.5          | 1              | IsArchived              |
+--------------+----------------+-------------------------+
| 4.149378E-06 | 37             | IsArchived, Mystery     |
+--------------+----------------+-------------------------+
| 4.149378E-06 | 41             | IsArchived, Mystery, Id |
+--------------+----------------+-------------------------+

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| 0            | 0          | 24398   | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+
| 1            | 0          | 216602  | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+

聚集索引的统计信息：

+--------------+----------------+---------+
| All density  | Average Length | Columns |
+--------------+----------------+---------+
| 4.149378E-06 | 4              | Id      |
+--------------+----------------+---------+

+--------------+------------+---------+---------------------+----------------+
| RANGE_HI_KEY | RANGE_ROWS | EQ_ROWS | DISTINCT_RANGE_ROWS | AVG_RANGE_ROWS |
+--------------+------------+---------+---------------------+----------------+
| 1            | 0          | 1       | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+
| 240999       | 240997     | 1       | 240997              | 1              |
+--------------+------------+---------+---------------------+----------------+
| 241000       | 0          | 1       | 0                   | 1              |
+--------------+------------+---------+---------------------+----------------+

（我用随机样本数据填充了表，其中大约十分之一的行未​​归档。之后我运行了完整的扫描统计更新。）

为什么两列统计的直方图只使用一列？我知道很多人都写过它确实如此，但理由是什么？在这种情况下，它会使整个直方图的用处大大降低，因为第一列只有两个值。为什么统计数据会被这样任意限制？

请注意，这个问题不是指多维直方图，它是完全不同的野兽。它是关于单维直方图，单维是包含各自多列的元组。

sql-server statistics
Martin Hope
John
Asked: 2016-07-08 00:57:37 +0800 CST
  • 1

部分出于好奇，我想知道是否可以使用索引（物化）视图来加速对某些基表的计数查询。

查询是这样的

SELECT COUNT(*)
FROM BaseTable
WHERE Slot = ?;

所以我创建了一个视图

CREATE VIEW IndexedView
WITH SCHEMABINDING AS
SELECT bt.Slot, COUNT_BIG(*) AS COUNT
FROM dbo.BaseTable bt
GROUP BY bt.Slot;

带有聚簇索引

CREATE UNIQUE CLUSTERED INDEX IX_Main
ON IndexedView (Slot);

这行得通，我现在可以将原始查询写为

SELECT COUNT
FROM IndexedView
WHERE Slot = ?

并更快地获得所需的结果。

las，这对我来说几乎没有用，因为我的查询通常不是手工制作的。我真的需要通过使用索引视图作为某种索引来使原始BaseTable查询变得更快- 我想我在某处读到这可能在某些情况下发生，但根据我的测试，不是在这个。

所以我的问题是：

  • 在这种情况下，索引视图还能以某种方式帮助我吗？
  • 任何人都可以推荐资源/文献而不是解释在哪些情况下索引视图确实用于优化它们所基于的表的查询？

编辑：关于重复的问题——我对索引视图的 GROUP BY 和聚合方面更感兴趣。答案帮助我找到了我犯的一个愚蠢的错误，它现在也对我有用。

JOYOUS ADDENDUM：现在我让它工作了，我成功地测试了它甚至在查询包含左连接的情况下也能工作，在这些情况下左连接实际上可以被优化掉（即 on- 子句涵盖唯一索引在联接表中）。

这真的很棒，因为这意味着即使在使用左连接的查询的情况下，也可以以这样一种方式设计模式，即快速获取所有内容或特定分组的总行数。

sql-server materialized-view
Martin Hope
John
Asked: 2016-07-07 05:55:46 +0800 CST
  • 4

我发现了一个类似的问题，并且知道为了查询表格

SELECT COUNT(1)
FROM foo f
LEFT OUTER JOIN bar b ON f.Value = b.Value AND f.Value = b.Value2

要在不接触的情况下执行bar，需要在相关的两列上有一个唯一索引。

事实上，到目前为止，这仍然有效，表格被定义为：

CREATE TABLE [dbo].[Foo](
    [Value] [varchar](255) NOT NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Bar](
    [Value] [varchar](1024) NULL,
    [Value2] [varchar](1024) NULL
)

CREATE UNIQUE CLUSTERED INDEX [IX] ON [dbo].[Bar]
(
    [Value] ASC,
    [Value2] ASC
)

查询计划不受影响bar，很好。

在我寻求为什么这在我编写的某些应用程序的实际查询中不起作用时，我将那里的查询减少为以下简单的测试，并且查询计划程序无法将其手离开bar表：

WITH numbers AS (
    SELECT 1 AS i
    UNION ALL SELECT i + 1
    FROM numbers
    WHERE i < 10
)
SELECT COUNT(1)
FROM numbers n
LEFT OUTER JOIN bar b ON n.i = b.Value AND n.i = b.Value2
;

这的确是touch吧。嗯。

有任何想法吗？为什么查询规划器认为这与其他查询有任何不同？

（我的实际问题没有使用这个递归表表达式，它实际上就像演示案例一样，对两列进行简单的连接 - 我无法理解为什么它不会在计数场景中单独留下连接表。）

sql-server sql-server-2014