HoneyHoney

Adding NOT NULL Columns as an Online Operation

Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. For example, the constant expression "My temporary data", or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table. Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.

1. 创建一个新的column
2. 为新的column赋值
3. 将前一个column标记dropped

问题

解决

1. 创建测试数据
   CREATE TABLE Test
   ([Student] varchar(1), [Class] varchar(8), [Score] int);
   
   INSERT INTO Test
   ([Student], [Class], [Score])
   VALUES
   ('A', 'English', 90),
   ('B', 'Computer', 78),
   ('B', 'Math', 88),
   ('C', 'Math', 55),
   ('C', 'Computer', 86),
   ('C', 'English', 64);
2. 将数据按照student进行partition，按照class排序。
       select *, ROW_NUMBER() OVER (PARTITION BY Student ORDER BY Class) AS rn FROM Test
3. 用这个query在SSRS里面添加一个新的dataset。
4. 添加一个table，并添加Class和Score。
5. 添加一个column group on Student。
6. 添加一个row group on rn，并且添加一个group header。
7. 清除rn column。因为没法隐藏，只能够把里面的内容清掉，然后去掉边框去掉。
8. group header里面分别用表达式 =Min(Fields!Class.Value) & =Min(Fields!Score.Value)
9. 最后把Details row隐藏起来。

问题

解决方案

解决方法