sql server 面试题（9） - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

sql server 面试题（9）

sql server 面试题（9）# Database - 数据库

z*y2008-11-07 08:11

1 楼

本周最后一期:有包子呵！
1. As DBA you maintain a data warehouse, one table inside the db is about 30
million rows, the total size of this table is about 30G.
Now we need add a new integer column to this table with default value 0.
What would you do? The db is in simple recovery model. There's no trigger
associated with this table. There's no indexed view with this table neither.
版主给大家弄个投票，最佳答案给360空间币。

B*g2008-11-07 08:11

2 楼

ding.
能抽空把题目和答案总结一下吗，明年我再看

30
neither.

【在 z***y 的大作中提到】

: 本周最后一期:有包子呵！
: 1. As DBA you maintain a data warehouse, one table inside the db is about 30
: million rows, the total size of this table is about 30G.
: Now we need add a new integer column to this table with default value 0.
: What would you do? The db is in simple recovery model. There's no trigger
: associated with this table. There's no indexed view with this table neither.
: 版主给大家弄个投票，最佳答案给360空间币。

m*02008-11-07 08:11

3 楼

let me try 1st lah
since it's 30G table, swap table is out of the question,
1)i would add the new column with nullable first
2)do batch update, maybe 5k-10k each time to fill that column with 0s
3)do final check on that column to make sure all rows are 0
4)add default constraint

30
neither.

【在 z***y 的大作中提到】

j*n2008-11-07 08:11

4 楼

alright, lets assume that you have enough space... more than 30G for data
and also enough space for log file (simple recovery model still needs a lot
log space while operation).
1. create a new table with same definitions plus your new integer column
with default = 0
2. execute
INSERT INTO newTable (...)
SELECT ...
FROM oldTable WITH (NOLOCK)
3. add all associated indexes same as the old one
NOTE: clustered index can not be the same name.
4. rename the old table
5. rename the new table

m*02008-11-07 08:11

5 楼

good alternative.
don't forget to copy new data that come in while you are doing step 2 at the
end

lot

【在 j*****n 的大作中提到】

: alright, lets assume that you have enough space... more than 30G for data
: and also enough space for log file (simple recovery model still needs a lot
: log space while operation).
: 1. create a new table with same definitions plus your new integer column
: with default = 0
: 2. execute
: INSERT INTO newTable (...)
: SELECT ...
: FROM oldTable WITH (NOLOCK)
: 3. add all associated indexes same as the old one

j*n2008-11-07 08:11

6 楼

it is a data warehouse, usually you don't have to worry about it.
see my NOLOCK hint? I didn't consider the dirty read as well.

the

【在 m***0 的大作中提到】

: good alternative.
: don't forget to copy new data that come in while you are doing step 2 at the
: end
:
: lot

m*02008-11-07 08:11

7 楼

well... if you are assuming it's data warehouse and there is no writes to
that table... then you can update that table directly.... ppl can still
work on it use no lock hint, right?

【在 j*****n 的大作中提到】

: it is a data warehouse, usually you don't have to worry about it.
: see my NOLOCK hint? I didn't consider the dirty read as well.
:
: the

j*n2008-11-07 08:11

8 楼

no...
remember that, when you do DDL (ALTER TABLE), there is an exclusive lock on
table level, no body else can access this table till the lock is released.

【在 m***0 的大作中提到】

: well... if you are assuming it's data warehouse and there is no writes to
: that table... then you can update that table directly.... ppl can still
: work on it use no lock hint, right?

m*02008-11-07 08:11

9 楼

but adding a nullable column only takes 1 sec, no matter how big the table
is...

on

【在 j*****n 的大作中提到】

: no...
: remember that, when you do DDL (ALTER TABLE), there is an exclusive lock on
: table level, no body else can access this table till the lock is released.

j*n2008-11-07 08:11

10 楼

really? have you try it out? what I remember last time I was working the
same thing on a millions rows table and it took me a while (30 seconds?).
besides, your solution requires a sets of updates, updating on such big
table takes long long! and how can you let other ppl access the data during
this time (exclusive lock) ?

【在 m***0 的大作中提到】

: but adding a nullable column only takes 1 sec, no matter how big the table
: is...
:
: on

w*e2008-11-07 08:11

11 楼

你这做法就是Red Gate干的....

lot

【在 j*****n 的大作中提到】

w*e2008-11-07 08:11

12 楼

同意mjd的....我在99M rows上加nullable, 只要one second.

during

【在 j*****n 的大作中提到】

: really? have you try it out? what I remember last time I was working the
: same thing on a millions rows table and it took me a while (30 seconds?).
: besides, your solution requires a sets of updates, updating on such big
: table takes long long! and how can you let other ppl access the data during
: this time (exclusive lock) ?

z*y2008-11-07 08:11

13 楼

如果加一个新column，没有任何的contraint，没有别的process访问这个表，不应该
是这么长的时间。你上次的代码还在不在？

during

【在 j*****n 的大作中提到】

j*n2008-11-07 08:11

14 楼

nop. anyway, if you want your previous rows also have the default zero value
, you got do the update, isn't it?
btw, M$ does not recommend the way I did... however, I did many times
without problem.

【在 z***y 的大作中提到】

: 如果加一个新column，没有任何的contraint，没有别的process访问这个表，不应该
: 是这么长的时间。你上次的代码还在不在？
:
: during

z*y2008-11-07 08:11

15 楼

bz 没发起投票，我觉得jackrun的答案好一些，大家有什么看法呢？
木有看法就发包子了哈！

B*g2008-11-07 08:11

16 楼

顶一个

【在 z***y 的大作中提到】

: bz 没发起投票，我觉得jackrun的答案好一些，大家有什么看法呢？
: 木有看法就发包子了哈！

B*g2008-11-07 08:11

17 楼

ft。今天就遇到了, 大家来分析一下。
oracle 9i, 36M rows tabA
Each row has 5 columns each with thousands bytes but <4000 bytes.(also other
columns,)
Not data warehouse, but also don't need 24*7(Sunday can stop everything)
Need add one new column colA with default value 'N'; and constraint on colA
(colA IN ('?', '?' ,'?'...).
No index on colA.
DBA created an empty column colA.
UPDATE value to 'N'. I was told after 6 hours not finish, so DBA stop the
process.
Now some records will have 'N', some will have NULL.
解决

【在 z***y 的大作中提到】

: bz 没发起投票，我觉得jackrun的答案好一些，大家有什么看法呢？
: 木有看法就发包子了哈！

z*y2008-11-07 08:11

18 楼

到周五没有人反对就转帐！

B*g2008-11-07 08:11

19 楼

自己顶

other
colA

【在 B*****g 的大作中提到】

: ft。今天就遇到了, 大家来分析一下。
: oracle 9i, 36M rows tabA
: Each row has 5 columns each with thousands bytes but <4000 bytes.(also other
: columns,)
: Not data warehouse, but also don't need 24*7(Sunday can stop everything)
: Need add one new column colA with default value 'N'; and constraint on colA
: (colA IN ('?', '?' ,'?'...).
: No index on colA.
: DBA created an empty column colA.
: UPDATE value to 'N'. I was told after 6 hours not finish, so DBA stop the

z*y2008-11-07 08:11

20 楼

你的问题呢?

B*g2008-11-07 08:11

21 楼

发信人: Beijing (学什么都不如有绿卡), 信区: Database
标题: Re: sql server 面试题（9）
发信站: BBS 未名空间站 (Thu Nov 13 15:28:11 2008)
ft。今天就遇到了, 大家来分析一下。
oracle 9i, 36M rows tabA
Each row has 5 columns each with thousands bytes but <4000 bytes.(also other
columns,)
Not data warehouse, but also don't need 24*7(Sunday can stop everything)
Need add one new column colA with default value 'N'; and constraint on colA
(colA IN ('?', '?' ,'?'...).
No index on colA.
DBA created an empty column colA.
UPDATE value to 'N'. I was to

【在 z***y 的大作中提到】

: 你的问题呢?

z*y2008-11-07 08:11

22 楼

给jackrun 360。

【在 z***y 的大作中提到】

: 到周五没有人反对就转帐！

j*n2008-11-07 08:11

23 楼

收到！咔咔，好大的包子 :)

【在 z***y 的大作中提到】

: 给jackrun 360。

B*g2008-11-07 08:11

24 楼

好大的棉花糖呀。

【在 j*****n 的大作中提到】

: 收到！咔咔，好大的包子 :)