Michael Green's questions

很明显，省略 varchar 的长度是一件坏事。不幸的是，我现在正在使用发生这种情况的代码库。广泛地。我想纠正这一点。第一步是查找出现的情况。这就是我需要帮助的地方。

使用我能想到的所有同义词在各种网络引擎上进行搜索都没有返回权威答案。我要问的是

• 我错过的其他测试用例
• 一种全面、规范的查找无长度声明的方法

通常在 Windows 开发环境（SSMS、Powershell、.Net 等）上可用的任何技术都是好的。采用更多利基技术的答案对于更广泛的社区来说会很有趣，但对我个人来说就不那么有趣了。

测试

由于所讨论的四种数据类型 - char、nchar、varchar 和 nvarchar - 都以字符 CHAR 结尾，因此我在下面的测试中单独使用它。这可以避免列表变得臃肿，并使添加更多测试变得更简单。如果需要的话，复制-粘贴-替换会很容易。

-- These are all legal; the regex must not return these
char(9)
char (9)            -- with a space
char    (9)         -- with a tab
char         (9)    -- tab space tab space
char(max)
char
(9)                 -- a new line between type and length

character(9)
CAST(999 AS character(9))

char varying(9)
character varying(9)
CAST(999 AS char varying(9))
CAST(999 AS character varying(9))


-- These also are legal; ugly, but legal
[char](9)
[char] (9)          -- with a space
[char]  (9)         -- with a tab
[char]       (9)    -- tab space tab space
[char](max)
[char]
(9)                 -- a new line between type and length

-- The type can also be delimited by double-quote
"char"(9)
-- All the tests using square brackets should be duplicated with other delimiters.

[character](9)
CAST(999 AS [character](9))

-- SQL Server 2022 throws an error for [character varying]
-- Msg 243, Level 16, State 1, Line 15
-- Type character varying is not a defined system type.


-- These are business terms which the regex should not return
characteristic
charge
chart

-- These are valid SQL but missing the length. These are what the search should return
char;
char ;      -- a space
char    ;   -- a tab
char,
char ,
char = 'lorem'
cast(9 as char)
convert(char, 9)

[char];
[char] ;        -- a space
[char]  ;   -- a tab
[char],
[char] ,
[char] = 'lorem'
cast(9 as [char])
convert([char], 9)

character
CAST(999 AS character)

char varying
character varying
CAST(999 AS char varying)
CAST(999 AS character varying)

我有一张小桌子，只有几行

create table dbo.p(i int);
insert dbo.p(i)
values (1), (2), (3), (4), (4);

我创建了统计数据；没有索引，只有统计数据。

create statistics p_c on dbo.p(i) with fullscan;

我正在调查各种事情，所以我尝试将 ROWCOUNT 设置为 bigint 的上限。

update statistics dbo.p p_c with rowcount = 9223372036854775807;

此操作失败并显示错误消息

消息 3739，级别 11，状态 3，第 346 行
无法更新索引“p_c”，因为它不是统计信息集合。

ROWCOUNT = 1;尽管成功了，但它也失败了update statistics dbo.p p_c。

该错误不在文档中。我在网上找不到任何相关的内容。

与普通的表统计数据相比，什么是统计数据收集？为什么设置 ROWCOUNT 可能会在这里失败？

SQL Server 2017 (RTM-CU31-GDR) (KB5021126)

SQL Server 跟踪各种内部位图中的页面分配。其中包括全局分配图 (GAM) 和页面可用空间 (PFS) 页。我们知道 GAM 页面以511232 页的设定间隔出现，PFS 页面以 8088 页的设定间隔出现。

给定一个足够大的数据文件，这种重复最终将导致一页成为 GAM 和 PFS 的下一行。计算一下，这发生在页码 516,855,552 处的 1,011 个 GAM 或 63,904 个 PFS 页之后。这相当于一个不到 4TB 的操作系统文件。由于单个数据文件的最大大小为 16TB（来源），这是允许的。

我的问题：当单个数据文件达到 4TB 时，哪种页面类型是页面 516855552 - GAM 还是 PFS？另一个去哪儿了？Paul Randal 的这条评论表明它被分流到 GAM 范围内其他未使用的页面之一：

除了第一个之外的 GAM extents 有 GAM, SGAM, DIFF_MAP, ML_MAP。每 4TB GAM extent 也将有一个 PFS 页面。

我发现这里引用了这个但没有明确解释：

-- There may be an issue with the ML map page position
-- on the four extents where PFS pages and GAM pages live
-- (at page IDs 516855552, 1033711104, 1550566656, 2067422208)
-- but I think we'll be ok.

在谷歌上没有其他有意义的结果显示给我。

我有权访问的数据库有多个文件，因此没有一个符合大小要求。

另一个问题的图表是针对 100 万行的表。当为 10,000 行绘制时，该图具有相当不同且更有趣的形状。

当列长度介于 524 和 903 之间时，存在一个平台期。

这种形状出现在我的工作和家用笔记本电脑上。（分别是 SQL Server 2017 CU27 和 2017 CU22、2019 CU8。两个盒子都有 16GB RAM。）

不同版本之间的坡度略有不同。

这让我认为它是经过调整的真正设计选择，而不是人工制品。

为 SQL Server 2019 设置 BATCH_MODE_ON_ROWSTORE = OFF 几乎没有任何区别。除 t800 为 6688kB 关闭和 6656kB 开启外，所需内存在所有大小上都相同。

这张图怎么会有这样的形状？哪些考虑因素意味着三区域解决方案在中等行数（10,000 行）而不是较大行数（100 万行）时是最佳的？

我知道 SQL Sever 开发团队之外的任何人都无法最终说明这个特定图表是这种形状的原因。但总的来说，从学术和行业经验来看，如果我开始为假设的 DBMS 编写内存管理模块，什么样的考虑会导致这种结果？

Lookup-ForEach 模式在 Azure 数据工厂 (ADF) 中很常见。Lookup 产生的项目如何分配给 ForEach 的工人，其数量由 Batch Count 控制？

我有两张桌子。每个都包含业务实体的一些属性以及这些属性有效的日期范围。我想将这些表合并为一个，匹配通用业务键上的行并拆分时间范围。

真实世界的示例是两个源时态表，为数据仓库中的类型 2 维表提供数据。

该实体在任何时间点都可以不存在于任何一个源系统中，也可以存在于一个源系统中，也可以同时存在于两个源系统中。一旦实体被记录在源系统中，间隔就会表现良好 - 没有间隙、重复或其他猴子业务。来源中的成员资格可以在不同的日期结束。

业务规则规定我们只想返回实体同时出现在两个源中的时间间隔。

什么查询会给出这个结果？

这说明了这种情况：

Month          J     F     M     A     M     J     J
Source A:  <--><----------><----------><---->
Source B:            <----><----><----------------><-->
               
Result:              <----><----><----><---->

样本数据

为简单起见，我使用了封闭的日期间隔；很可能任何解决方案都可以通过少量输入扩展到半开区间。

drop table if exists dbo.SourceA;
drop table if exists dbo.SourceB;
go

create table dbo.SourceA
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);

create table dbo.SourceB
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);
GO


insert dbo.SourceA(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990101', '19990113', 'black'),
    (1, '19990114', '19990313', 'red'),
    (1, '19990314', '19990513', 'blue'),
    (1, '19990514', '19990613', 'green'),
    (2, '20110714', '20110913', 'pink'),
    (2, '20110914', '20111113', 'white'),
    (2, '20111114', '20111213', 'gray');

insert dbo.SourceB(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990214', '19990313', 'left'),
    (1, '19990314', '19990413', 'right'),
    (1, '19990414', '19990713', 'centre'),
    (1, '19990714', '19990730', 'back'),
    (2, '20110814', '20110913', 'top'),
    (2, '20110914', '20111013', 'middle'),
    (2, '20111014', '20120113', 'bottom');

期望的输出

BusinessKey StartDate   EndDate     a_Colour  b_Placement
----------- ----------  ----------  --------- -----------
1           1999-02-14  1999-03-13  red       left     
1           1999-03-14  1999-04-13  blue      right    
1           1999-04-14  1999-05-13  blue      centre   
1           1999-05-14  1999-06-13  green     centre   
2           2011-08-14  2011-09-13  pink      top      
2           2011-09-14  2011-10-13  white     middle   
2           2011-10-14  2011-11-13  white     bottom   
2           2011-11-14  2011-12-13  gray      bottom    

SQL Server 2019 CTP3.1引入了一项优化来解决最后一页插入争用。这采用名为的索引选项OPTIMIZE_FOR_SEQUENTIAL_KEY的形式。

人们认为这可能是Bw-Tree或Bz-Tree的一种改编。然而，这些依赖于可变大小的页面，而当前的存储引擎需要固定大小的页面。

优化是如何实现的？此优化如何改变当前的 B 树算法？在什么情况下我会选择不部署这个选项？

研究

反向键方法的专利。

我使用 DBCC PAGE 快速浏览了一下，比较了 2017 年与 2019 年以及 2019 年在 int IDENTITY 列的唯一聚集索引上使用和不使用 OPTIMIZE_FOR_SEQUENTIAL_KEY 的情况。没有什么可以明显解释新行为。这让我觉得它是一个算法的东西，而不是一个结构的东西，这是有道理的。

来自 MS的博客文章。

此功能似乎以检测和避免车队为中心。

我有一个看起来像这样的层次结构：

作为 TSQL 中的 JSON，它是这样的：

declare @Employees nvarchar(max) =
'{
  "person": "Amy",
  "staff": [
    { "person": "Bill" },
    {
      "person": "Chris",
      "staff": [
        { "person": "Dan" },
        { "person": "Emma" }
      ]
    }
  ]
}';

这只是一个例子。实际数据可以是任何深度或宽度不确定的树。

我发现的文档和所有示例都显示了自上而下的遍历。每个 JSON 路径都从根节点开始，并通过已知节点名称导航到所需节点。我没有发现从层次结构中的不确定深度开始向上工作。我觉得我需要像传递闭包这样的东西。

给定一个名字，我想得到这个名字的祖先。例如，给定“Emma”，结果将是“Emma / Chris / Amy”。给定“Bill”，答案将是“Bill / Amy”。输出格式不重要；它可以是 JSON、字符串或结果集。名字是独一无二的。

这是我自己的学习练习。可以将原始 JSON 表示更改为任何等效的表示，只要它仍然是 JSONy。在层次结构的邻接列表表示上的 JSON_QUERY 不会达到我的目标。

我有一个具有多种边缘类型的图形数据库。例如

create table Person(Name varchar(50)) as node;
create table Friend as edge;
create table Manager as edge;

insert Person(Name) values ('Alice'), ('Bob'), ('Chris');

insert Friend($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Bob');

insert Manager($from_id, $to_id)
select
    (select $node_id from Person where Name = 'Alice'),
    (select $node_id from Person where Name = 'Chris');

我可以通过 UNION 查询找到连接到给定节点（图多态性）的所有节点：

select
    b.Name
from Person a, Person b, Friend f
where a.Name = 'Alice'
and match(a-(f)->b)

union

select
    b.Name
from Person a, Person b, Manager m
where a.Name = 'Alice'
and match(a-(m)->b);

然而，这很快变得很麻烦。SQL Server没有内置于语法中的更优雅的解决方案。

是否有允许多态性的设计模式？

在线书籍对 PolyBase 中的查询优化有这样的说法：

查询优化器做出基于成本的决定，将计算推送到 Hadoop，这样做会提高查询性能。它使用外部表的统计信息来做出基于成本的决策。推送计算创建 MapReduce 作业并利用 Hadoop 的分布式计算资源。

我的问题 - 优化器如何以及何时收集这些统计信息？

在 SQL Server 关系引擎内部，可以隐式或显式创建统计信息，它们会随着写入的发生而老化，并且可以显式刷新或在表维护期间刷新。PolyBase 中是否采用了类似的策略？

鉴于数据可能会被加载到 Hadoop/Azure blob 中而 SQL Server 没有看到它，并且数据量将很大（很可能）通过采样或类似方法创建运行时临时统计数据对我来说似乎不太可能。

最近的一个项目中的一项要求是报告资源何时会被完全消耗。除了用尽日历日期外，我还被要求以类似英语的格式显示剩余时间，例如“1 year, 3 months to go”。

内置DATEDIFF函数

返回指定开始日期和结束日期之间跨越的指定日期部分边界的计数。

如果按原样使用，可能会产生误导或混淆的结果。例如，使用 YEAR 的间隔将显示 1999-12-31 (YYYY-MM-DD) 和 2000-01-01 相隔一年，而常识会说这些日期仅相隔 1 天。相反，使用 DAY 1999-12-31 和 2010-12-31 的间隔相隔 4,018 天，而大多数人会将“11 年”视为更好的描述。

从天数开始计算月份和年份，容易出现闰年和月份大小错误。

我想知道如何在各种 SQL 方言中实现这一点？示例输出包括：

create table TestData(
    FromDate date not null,
    ToDate date not null,
    ExpectedResult varchar(100) not null); -- exact formatting is unimportant

insert TestData (FromDate, ToDate, ExpectedResult)
values ('1999-12-31', '1999-12-31', '0 days'),
       ('1999-12-31', '2000-01-01', '1 day'),
       ('2000-01-01', '2000-02-01', '1 month'),
       ('2000-02-01', '2000-03-01', '1 month'),              -- month length not important
       ('2000-01-28', '2000-02-29', '1 month, 1 day'),       -- leap years to be accounted for
       ('2000-01-01', '2000-12-31', '11 months, 30 days'),
       ('2000-02-28', '2000-03-01', '2 days'),
       ('2001-02-28', '2001-03-01', '1 day'),                -- not a leap year
       ('2000-01-01', '2001-01-01', '1 year'),
       ('2000-01-01', '2011-01-01', '11 years'),
       ('9999-12-30', '9999-12-31', '1 day'),                -- catch overflow in date calculations
       ('1900-01-01', '9999-12-31', '8099 years 11 months 30 days');  -- min(date) to max(date)

我碰巧使用的是 SQL Server 2008R2，但我有兴趣了解其他方言如何处理这个问题。

通常，SQL Server 会在更新期间获取锁。它还支持锁升级：

锁升级是将许多细粒度锁转换为较少粗粒度锁的过程，在降低系统开销的同时增加并发竞争的概率。

在事务 ROLLBACK 期间，SQL Server 是否降级锁，放回细粒度锁？

在这篇文章中，作者多次运行查询。我注意到逻辑读取在执行过程中略有不同。总共读了几千页，相差2页左右。从上下文来看，我似乎很清楚，在两次之间不会有写活动。如果计划发生变化，我预计会有比零点几分之一更大的变化。

Q：在没有数据写入的情况下，哪些因素会导致SQL Server对同一个查询报告不同的逻辑读取计数？