i****a
发帖数: 36252 | 1 如果我從 CTE 做 UPDATE, for example:
;with CTE as
(select columnA
, row_number() over (order by columnB) as row_num
from TableA
)
Update C
SET columnA = 'testing'
from CTE C
where row_num < 10
Question is, will the query pull all records from TableA, or only 10 rows
with row_num < 10? |
B*****g
发帖数: 34098 | 2 this is not a simple question
【在 i****a 的大作中提到】
: 如果我從 CTE 做 UPDATE, for example:
: ;with CTE as
: (select columnA
: , row_number() over (order by columnB) as row_num
: from TableA
: )
: Update C
: SET columnA = 'testing'
: from CTE C
: where row_num < 10
|
i****a
发帖数: 36252 | 3 Do you see any problem with set rowcount?
BOL says:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT
, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
As I understand it should be ok because the select statement will be still
limited to 10 results.
SET ROWCOUNT 10
;with CTE as
(select columnA
, row_number() over (order by columnB) as row_num
from TableA
)
Update C
SET columnA = 'testing'
from CTE C
【在 B*****g 的大作中提到】
: this is not a simple question
|
y****w
发帖数: 3747 | 4 I believe it is SQL compiler dependent.
【在 i****a 的大作中提到】
: 如果我從 CTE 做 UPDATE, for example:
: ;with CTE as
: (select columnA
: , row_number() over (order by columnB) as row_num
: from TableA
: )
: Update C
: SET columnA = 'testing'
: from CTE C
: where row_num < 10
|
i****a
发帖数: 36252 | 5 I've find out th
in
INSERT
use
[发表自未名空间手机版 - m.mitbbs.com]
【在 i****a 的大作中提到】
: Do you see any problem with set rowcount?
: BOL says:
: Important:
: Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
: the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT
: , and UPDATE statements in new development work, and plan to modify
: applications that currently use it. We recommend that DELETE, INSERT, and
: UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
: TOP.
: As I understand it should be ok because the select statement will be still
|
i****a
发帖数: 36252 | 6 I've find out this doesn't work. The update would hit 10 records out of the
full result, not necessarily the same result as the 10 the select would show.
in
INSERT
use
[发表自未名空间手机版 - m.mitbbs.com]
【在 i****a 的大作中提到】
: Do you see any problem with set rowcount?
: BOL says:
: Important:
: Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
: the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT
: , and UPDATE statements in new development work, and plan to modify
: applications that currently use it. We recommend that DELETE, INSERT, and
: UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
: TOP.
: As I understand it should be ok because the select statement will be still
|
B*****g
发帖数: 34098 | 7 看来和oracle差不多了？
the
show.
and
still
【在 i****a 的大作中提到】
: I've find out this doesn't work. The update would hit 10 records out of the
: full result, not necessarily the same result as the 10 the select would show.
:
: in
: INSERT
: use
: [发表自未名空间手机版 - m.mitbbs.com]
|
y****w
发帖数: 3747 | 8 the "order by" in your CTE may not be as strong as enough to lock the 10 specific rows. RANK may works much better if applicable.
the
show.
【在 i****a 的大作中提到】
: I've find out this doesn't work. The update would hit 10 records out of the
: full result, not necessarily the same result as the 10 the select would show.
:
: in
: INSERT
: use
: [发表自未名空间手机版 - m.mitbbs.com]
|
i****a
发帖数: 36252 | 9 I thought Rank and Row_Number does the same thing except the numbering. didn
't know they would lock records differently.
I think any order by method would result in the same behavior? only
difference may affect it is the indexing on the column.
Let's say columnB in my example is indexed, does SQL server still need to
retrieve all records for sorting in the CTE example?
specific rows. RANK may works much better if applicable.
【在 y****w 的大作中提到】
: the "order by" in your CTE may not be as strong as enough to lock the 10 specific rows. RANK may works much better if applicable.
:
: the
: show.
|
g***l
发帖数: 18555 | 10 CTE就不能UPDATE吧，反正我从不UPDATE CTE，就跟VIEW一样，一旦产生了就不能改了
，而且是用完就扔。你的这个用TABLE VARIABLE会好一些，
Common Table Expressions, or CTE, are a new construct introduced in
Microsoft SQL Server 2005 that offer a more readable form of the derived
table that can be declared once and referenced multiple times in a query. |
|
|
y****w
发帖数: 3747 | 11 with row_number, for the biggest "N"th, you may have a random subset
selected. RANK will guarantee you won't lose something.
didn
【在 i****a 的大作中提到】
: I thought Rank and Row_Number does the same thing except the numbering. didn
: 't know they would lock records differently.
: I think any order by method would result in the same behavior? only
: difference may affect it is the indexing on the column.
: Let's say columnB in my example is indexed, does SQL server still need to
: retrieve all records for sorting in the CTE example?
:
: specific rows. RANK may works much better if applicable.
|
y****w
发帖数: 3747 | 12 对有些问题，delete from view now CTE是经典解。不过我同意你的说法，如果是真正
的view就不要拿来做更新, confusing.
【在 g***l 的大作中提到】
: CTE就不能UPDATE吧，反正我从不UPDATE CTE，就跟VIEW一样，一旦产生了就不能改了
: ，而且是用完就扔。你的这个用TABLE VARIABLE会好一些，
: Common Table Expressions, or CTE, are a new construct introduced in
: Microsoft SQL Server 2005 that offer a more readable form of the derived
: table that can be declared once and referenced multiple times in a query.
|
g***l
发帖数: 18555 | 13 CTE就是在内存里暂时存一下，尤其是这种自己UPDATE自己的，搞不好就乱套，写到
TABLE里存进DISC,一般是这样用CTE的，记住是在内存里，随用随扔，如果要UPDATE CTE就再产生一个CTE，扔掉以前那个，
;WITH CTE_1
(
SELECT BLAH BLAH
FROM
TABLE A
),
CTE_2
(
SELECT BLAH BLAH
FROM
CTE_1
JOIN OTHERS
),
CTE_3
(
SELECT BLAH BLAH
FROM
CTE_2
JOIN ...
),
UPDATE
TABLE A
FROM
CTE_3
WHERE
【在 y****w 的大作中提到】
: 对有些问题，delete from view now CTE是经典解。不过我同意你的说法，如果是真正
: 的view就不要拿来做更新, confusing.
|
y****w
发帖数: 3747 | 14 多层的Cte 没几个能update的 术语：updatable view
CTE就再产生一个CTE，扔掉以前那个，
【在 g***l 的大作中提到】
: CTE就是在内存里暂时存一下，尤其是这种自己UPDATE自己的，搞不好就乱套，写到
: TABLE里存进DISC,一般是这样用CTE的，记住是在内存里，随用随扔，如果要UPDATE CTE就再产生一个CTE，扔掉以前那个，
: ;WITH CTE_1
: (
: SELECT BLAH BLAH
: FROM
: TABLE A
: ),
: CTE_2
: (
|
g***l
发帖数: 18555 | 15 always try to use the simplest way to get job done, not the complicated way
【在 y****w 的大作中提到】
: 多层的Cte 没几个能update的 术语：updatable view
:
: CTE就再产生一个CTE，扔掉以前那个，
|
B*****g
发帖数: 34098 | 16 昨天俺还向全组人作检讨，设计太简单，又没有啥bug，维护太容易，几个程序把全组需
要干的事全集中起来，直接结果是接下来的4-9年没活干。
way
【在 g***l 的大作中提到】
: always try to use the simplest way to get job done, not the complicated way
|
y****w
发帖数: 3747 | 17 昨天我们新来的CIO在发飙，说公司的BI项目如何如何，她找的consultant说能10个星期都把活干完。那个项目设计是烂了太多，柔和了太多东西在ETL里面，但十个星期是打死我也不信的。
组需
【在 B*****g 的大作中提到】
: 昨天俺还向全组人作检讨，设计太简单，又没有啥bug，维护太容易，几个程序把全组需
: 要干的事全集中起来，直接结果是接下来的4-9年没活干。
:
: way
|
B*****g
发帖数: 34098 | 18 一般最后会花3-5倍的时间，都这样，先把核心做的能工作的，其它的慢慢延
星期都把活干完。那个项目设计是烂了太多，柔和了太多东西在ETL里面，但十个星期
是打死我也不信的。
【在 y****w 的大作中提到】
: 昨天我们新来的CIO在发飙，说公司的BI项目如何如何，她找的consultant说能10个星期都把活干完。那个项目设计是烂了太多，柔和了太多东西在ETL里面，但十个星期是打死我也不信的。
:
: 组需
|
g***l
发帖数: 18555 | 19 不会吧,想优化开发有的是活可干的,我老板最喜欢给我们找活干
组需
【在 B*****g 的大作中提到】
: 昨天俺还向全组人作检讨，设计太简单，又没有啥bug，维护太容易，几个程序把全组需
: 要干的事全集中起来，直接结果是接下来的4-9年没活干。
:
: way
|
