是时候通过方法向新列添加默认值了 [英] Time to add default values to a new column by approach

问题描述

我需要向现有 SQL 表添加一个新的非空列并将其设置为默认值.我知道有两种不同的方法:

I need to add a new non-null column to an existing SQL table and set it to a default value. I understand that there are two different approaches to this:

1. ALTER TABLE FooADD Col CHAR(1) NOT NULL DEFAULT('N')

ALTER TABLE Foo添加列字符(1)去更新 Foo SET Col = 'N'去ALTER TABLE Foo ALTER COLUMN Col CHAR(1) NOT NULL DEFAULT('N')

也就是说，在第二种方式中，有三个单独的批次:首先创建列，然后回填默认值，然后添加not null约束和默认值.

That is, in the second approach, there are three separate batches: first the column is created, then the default value is backfilled, and then the not null constraint and the default value is added.

这里的一位开发人员断言，第二种方法更快，锁定时间更短，而这种方法 #1 会产生巨大的日志.我一直在尝试重现这个，但还没有成功.可能是我的样本数据集太小了，他所描述的内容仅在大型数据库或特定模式下才明显.

One of the devs around here has asserted that the second approach is faster, results in less time with a lock, and that approach #1 results in a huge log. I have been trying to reproduce this, but haven't been able to yet. It could be that my sample dataset is too small, and what he's describing is only apparent on large DBs, or with particular schemata.

与其花时间生成越来越大的数据集并反复修改架构，我想我会求助于这里的专家并获得瘦身.对于大型数据集，第二种方法实际上更好吗?

Rather than spending the time to generate increasingly large datasets and repeatedly modify the schema, I figured I would turn to the experts here and get the skinny. Is the second approach actually better for large datasets?

推荐答案

所以我决定测试一下.
在 Sql server 2012 上，在具有超过 1360 万条记录的表上执行第一个更改表花费了不到一秒.

So I've decided to test that.
On Sql server 2012, It took less then a second to perform the first alter table on a table that has more then 13.6 million records.

对于第二种方法，需要 49 秒，所以我会说你的开发是错误的.

For the second approach, it took 49 seconds, so I would say that your dev is wrong.

这篇关于是时候通过方法向新列添加默认值了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！