主页 / user-101163

Alsin's questions

Martin Hope
Alsin
Asked: 2019-12-21 07:08:53 +0800 CST
  • 2

我想知道表的压缩是否真的会改变ALTER TABLE规则。我没有找到关于这个主题的信息。让我们考虑一个简单的例子。我将创建一个表并将其中一列从 更改INT为BIGINT。

CREATE TABLE dbo.test1
(
  c1 int not null,
  c2 int not null,
  primary key (c1)
)

物理行看起来就像我们可以建议的那样，两个字段，每个 4 个字节。

SELECT pc.leaf_offset, pc.max_length
FROM sys.system_internals_partitions p
  join sys.system_internals_partition_columns pc 
    on p.partition_id = pc.partition_id
WHERE p.object_id = object_id('dbo.test1')

leaf_offset max_length
4           4
8           4

让我们将 c2 从 更改INT为BIGINT

ALTER TABLE dbo.test1 ALTER COLUMN c2 bigint not null

我们增加了固定长度列的长度，因此 SQL Server 在行尾再添加一列。相同的查询显示

leaf_offset max_length
4           4
8           4
12          8

到目前为止一切顺利，一切都像书籍和许多文章描述的那样工作。让我们尝试对压缩表做同样的事情。

CREATE TABLE dbo.test2
(
  c1 int not null,
  c2 int not null,
  primary key (c1)
) with (data_compression = page)

物理结构如下所示：

leaf_offset max_length
-1          4
-2          4

所有“偏移量”都是负数。只有可变长度列假设有负偏移量。一开始我很惊讶，我从来没有见过这个记录。但这是有道理的，压缩页面不能从行的开头有固定的偏移量。现在让我们尝试更改列。

ALTER TABLE dbo.test2 ALTER COLUMN c2 bigint not null

第二个惊喜，“新”栏没有添加。

leaf_offset max_length
-1          4
-2          8

更令人惊讶的是，在 SQL Server 2017 上，此操作似乎是“仅限元数据”，仍待确认。SQL Server 2014 以旧方式处理它。

我的问题

  1. 我们是否有任何关于表压缩如何影响的文档ALTER TABLE？谁能解释一下？
  2. SQL Server 2017 是否改进了这一点？

我已经阅读了 Microsoft SQL Server 2012 Internals 中的这个示例章节。它涵盖了压缩数据的列描述符行格式，但仅涵盖所涉及的物理结构，根本不涉及架构更改。

sql-server alter-table
Martin Hope
Alsin
Asked: 2019-12-17 05:27:06 +0800 CST
  • 1
REPLICATE ( string_expression ,integer_expression )

正如文档所说，它

返回与string_expression相同的类型。

我看到返回类型总是VARCHAR.

declare @vb varbinary(10) = 0x01
declare @res sql_variant = REPLICATE(@vb, 2)

SELECT sql_variant_property(@res, 'BaseType')

varchar

文档有错吗？

我将此作为文档问题提出。

sql-server t-sql
Martin Hope
Alsin
Asked: 2019-03-20 08:14:10 +0800 CST
  • 3

我有一个非常大的表，并添加了一个带有 DEFAULT 的新 NOT NULL 列。在 DWH 环境中并不罕见。这是一个在线操作，运行速度非常快，并将默认值存储在表元数据中，正如我们可以在那里了解到的那样。它工作得很好，SELECTs 返回一个默认值。一段时间后，我决定将默认值更改为不同的值。而且看起来我们无法通过更改默认值来实现它。我的意思是，我们可以放弃旧的默认值并创建新的默认值，但所有 SELECTS 都会显示旧的默认值。我会尽量避免大量更新，因为它们又长又痛苦。可能吗？

--DROP TABLE #t

CREATE TABLE #t
(
  c1 int not null
)

INSERT INTO #t VALUES (1)

ALTER TABLE #t ADD c2 int not null CONSTRAINT t#c2def default -1
ALTER TABLE #t DROP CONSTRAINT t#c2def

SELECT * FROM #t
-- c2 = -1
ALTER TABLE #t ADD CONSTRAINT t#c2def DEFAULT -2 FOR c2

SELECT * FROM #t
-- still c2 = -1

更一般地说，我们是否有关于此功能如何工作的更多详细信息？我希望现有的未更新页面应该显示新的默认值。这就是我对“默认值仅存储在表的元数据中，并根据需要查找该值”的理解方式。不幸的是，它不是这样工作的。所有行都显示旧的默认值。我正在 SQL Server 2014 上检查它。

sql-server default-value
Martin Hope
Alsin
Asked: 2019-01-05 09:34:16 +0800 CST
  • 7

我想知道为什么 SQL Server 在这种简单的情况下会做出错误的估计。有一个场景。

CREATE PARTITION FUNCTION PF_Test (int) AS RANGE RIGHT 
FOR VALUES (20140801, 20140802, 20140803)

CREATE PARTITION SCHEME PS_Test AS PARTITION PF_Test ALL TO ([Primary])

CREATE TABLE A
(
  DateKey int not null,
  Type int not null,
  constraint PK_A primary key (DateKey, Type) on PS_Test(DateKey)
)

INSERT INTO A (DateKey, Type)
SELECT
  DateKey = N1.n  + 20140801,
  Type = N2.n + 1
FROM dbo.Numbers N1
  cross join dbo.Numbers N2
WHERE N1.n BETWEEN 0 AND 2
  and N2.n BETWEEN 0 AND 10000 - 1

UPDATE STATISTICS A (PK_A) WITH FULLSCAN, INCREMENTAL = ON

CREATE TABLE B
(
  DateKey int not null,
  SubType int not null,
  Type int not null,
  constraint PK_B primary key (DateKey, SubType) on PS_Test(DateKey)
)

INSERT INTO B (DateKey, SubType, Type)
SELECT
  DateKey,
  SubType = Type * 10000 + N.n,
  Type
FROM A
  cross join dbo.Numbers N
WHERE N.n BETWEEN 1 AND 10

UPDATE STATISTICS B (PK_B) WITH FULLSCAN, INCREMENTAL = ON

所以设置非常简单，统计信息就位，当我们查询一个表时，SQL Server 可以产生正确的估计。

select COUNT(*) from A where DateKey = 20140802
--10000
select COUNT(*) from B where DateKey = 20140802
--100000

但是在这个简单的选择中，估计值相差甚远，我看不出原因。

SELECT a.DateKey, a.Type
FROM A
  JOIN B
    ON b.DateKey = a.DateKey
    AND b.Type = a.Type
WHERE a.DateKey = 20140802

执行计划

Clustered Index Seek 之后估计是实际值的 57%！真实世界的查询更糟，估计是实际的 2%。

用于重现设置的 PS 编号表

DECLARE @UpperBound INT = 1000000;

;WITH cteN(Number) AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
)
SELECT n = [Number] INTO dbo.Numbers
FROM cteN WHERE [Number] <= @UpperBound;

CREATE UNIQUE CLUSTERED INDEX CIX_Number ON dbo.Numbers(n)
WITH 
(
  FILLFACTOR = 100,      -- in the event server default has been changed
  DATA_COMPRESSION = ROW -- if Enterprise & table large enough to matter
);

PPS 相同场景但未分区完美运行。

sql-server partitioning