Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 newid() по группе  [new]
step_ks
Member

Откуда:
Сообщений: 936
Необходимо каждой группе записей, определяемой полем gr присвоить свой newid().
Одним селектом, без промежуточных таблиц и без update-ов.

declare @t table (id int identity(1,1), gr int, rowid uniqueidentifier)
insert @t (gr) select 1
insert @t (gr) select 1
insert @t (gr) select 2
insert @t (gr) select 2
insert @t (gr) select 3

select t1.gr,t2.rowid
  from @t t1 inner join (select gr, newid() as rowid from @t group by gr) t2 on t2.gr=t1.gr

Результат:

grrowid
1AB777FFA-609A-47B8-82F4-C7C60A419220
16EE07AFF-14F7-4A3E-AEE7-19DC1738D6BA
2EF3099D5-63EB-4BAC-BF6B-C2A242CEB137
20EAFC893-0A4B-4B5A-8513-C667821E51E9
3134E2614-383D-4DB8-B06B-126846BA66DE


К сожалению, здесь newid() отрабатывает на каждую запись результирующего набора.
Нужный же результат выглядит так:

grrowid
1AB777FFA-609A-47B8-82F4-C7C60A419220
1AB777FFA-609A-47B8-82F4-C7C60A419220
2EF3099D5-63EB-4BAC-BF6B-C2A242CEB137
2EF3099D5-63EB-4BAC-BF6B-C2A242CEB137
3134E2614-383D-4DB8-B06B-126846BA66DE


Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) 
17 сен 08, 13:36    [6195113]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10240
Блог
declare @t table (id int identity(1,1), gr int, rowid uniqueidentifier)
insert @t (gr) select 1
insert @t (gr) select 1
insert @t (gr) select 2
insert @t (gr) select 2
insert @t (gr) select 3

declare @t2 table (gr int, rowid uniqueidentifier)
insert @t2 
select gr, newid() as rowid from @t group by gr

select t1.gr,t2.rowid
  from @t t1 inner join @t2 t2 
on t2.gr=t1.gr
17 сен 08, 13:43    [6195172]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
step_ks
Member

Откуда:
Сообщений: 936
Спасибо, так то оно конечно понятно, но к сожалению
step_ks
Одним селектом, без промежуточных таблиц и без update-ов.
17 сен 08, 13:55    [6195257]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
Glory
Member

Откуда:
Сообщений: 104764
step_ks
Спасибо, так то оно конечно понятно, но к сожалению
step_ks
Одним селектом, без промежуточных таблиц и без update-ов.

Ну так что мешает добавить еще один подзапрос ?
17 сен 08, 13:57    [6195266]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Glory
Ну так что мешает добавить еще один подзапрос ?


А вот тут мы попадаем на засаду... Не знаю, обсуждалось ли это где-то или нет, но, похоже, разработчики ввели CTE только как синтаксический сахар, унаследовав поведение derived tables, ибо:

declare @t table (id int identity(1,1), gr int, rowid uniqueidentifier)
insert @t (gr) select 1
insert @t (gr) select 1
insert @t (gr) select 2
insert @t (gr) select 2
insert @t (gr) select 3

;WITH CTE1(gr, row_id) AS
(SELECT
  gr,
  NEWID()
FROM
  @T
GROUP BY gr
)
SELECT * FROM CTE1

gr          row_id                               
----------- ------------------------------------ 
1           144767F1-D4E9-4241-8F19-DE8A1E87521F
2           AD36B342-1E38-49F4-A401-E8E668BED96F
3           6BCC33BD-D70C-4B48-9A82-016C3FCCBD04

(3 row(s) affected)

НО:

;WITH CTE1(gr, row_id) AS
(SELECT
  gr,
  NEWID()
FROM
  @T
GROUP BY gr
)
SELECT
  T.id, T.gr, C.row_id
FROM
  @t T
  INNER JOIN CTE1 C ON
  T.gr = C.gr

id          gr          row_id                               
----------- ----------- ------------------------------------ 
1           1           CF56DC2D-82DC-4CBB-9D03-B2A54A5AD956
2           1           50DA40BE-092A-4C4E-ABC0-A867C2DAF951
3           2           9D27F6FE-D704-40EE-B025-2959D4057A19
4           2           056D7BFF-752F-4EA3-8191-ECED441FBB86
5           3           82A374DF-2CA7-4855-8FAC-56BA3453D7B7

и план показывает, когда сервер высчитывает NEWID().

И даже вот так...

;WITH CTE1(gr, row_id) AS
(SELECT
  gr,
  NEWID()
FROM
  @T
GROUP BY gr
)
SELECT
  T.id, T.gr,
  (SELECT row_id FROM CTE1 WHERE gr = T.gr)
FROM
  @t T

id          gr                                               
----------- ----------- ------------------------------------ 
1           1           77609250-10E4-446C-8A9B-6437F21B4BB8
2           1           D6817B61-490A-435C-80EA-029A302F4D32
3           2           4A9A8943-DCFE-4ED7-9EE4-552CD0A6A614
4           2           03F36632-1E8B-4E72-AB09-7F8663D0C9D3
5           3           97A87401-36F9-4369-92B6-4838D0FBBCE6

Я понимаю, что функция недетерминированная, но зачем же так жесто перевирать мой запрос?!
17 сен 08, 14:10    [6195406]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Ну, впринципе, и в доке об этом сказано:

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
17 сен 08, 14:18    [6195476]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
Glory
Member

Откуда:
Сообщений: 104764
select t1.gr,cast(t2.rowid as uniqueidentifier)
  from @t t1 inner join (select gr, max(cast(newid() as binary)) as rowid from @t group by gr) t2 on t2.gr=t1.gr


Сообщение было отредактировано: 17 сен 08, 14:34
17 сен 08, 14:31    [6195582]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
aleks2
Guest
declare @gr int, @id uniqueidentifier

UPDATE T SET 
  @id=(CASE t.gr WHEN @gr THEN @id ELSE newid() END)
, @gr=(CASE t.gr WHEN @gr THEN @gr ELSE t.gr END)
, rowid=@id
  from @t t

select * from @t
17 сен 08, 14:32    [6195592]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74927
Glory
select t1.gr,cast(t2.rowid as uniqueidentifier)
  from @t t1 inner join (select gr, max(cast(newid() as binary)) as rowid from @t group by gr) t2 on t2.gr=t1.gr


Мдя...
17 сен 08, 14:36    [6195627]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
step_ks
Member

Откуда:
Сообщений: 936
Glory
select t1.gr,cast(t2.rowid as uniqueidentifier)
  from @t t1 inner join (select gr, max(cast(newid() as binary)) as rowid from @t group by gr) t2 on t2.gr=t1.gr


Да, так max наводит порядок. Остается только гадать, а не "поумнеет" ли оптимизатор ли в один прекрасный день, начав его игнорировать.
17 сен 08, 15:46    [6196197]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
step_ks
Member

Откуда:
Сообщений: 936
aleks2
declare @gr int, @id uniqueidentifier

UPDATE T SET 
  @id=(CASE t.gr WHEN @gr THEN @id ELSE newid() END)
, @gr=(CASE t.gr WHEN @gr THEN @gr ELSE t.gr END)
, rowid=@id
  from @t t

select * from @t


step_ks
... и без update-ов.
17 сен 08, 15:56    [6196268]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
step_ks
Member

Откуда:
Сообщений: 936
В продолжение старого разговора, дабы окончательно разобраться.
Модифицируем пример из первого поста (используем 2 таблицы) и применим фокус с max по совету Glory:
declare @t table (id int identity(1,1), gr int)
insert @t (gr) select 1
insert @t (gr) select 1
insert @t (gr) select 2
insert @t (gr) select 2
insert @t (gr) select 3

declare @tt table (id int identity(1,1), gr int)
insert @tt (gr) select 1
insert @tt (gr) select 2

    
select t1.gr,cast(t2.rowid as uniqueidentifier)
  from @t t1 inner join (select gr, max(cast(newid() as binary)) as rowid from @tt group by gr) t2 on t2.gr=t1.gr

Результат остается нужным:

grrowid
15EC65C27-0785-4F9F-B42A-376451349DFB
15EC65C27-0785-4F9F-B42A-376451349DFB
2416C0C76-C9F1-4240-8B75-BB83FFFA339B
2416C0C76-C9F1-4240-8B75-BB83FFFA339B


Теперь даем оптимизатору знать, что у нас в @tt поле gr уникально (изменим определение @tt):
declare @tt table (id int identity(1,1), gr int primary key)

и видим в плане, что max отброшен оптимизатором и newid() опять красуется в каждой строке результата:

grrowid
1F897E384-5011-4E17-B67C-457BD7F09FEB
1CD9075FA-E3A6-4220-BE1A-355DEECCBC10
20F25E3A6-7EF9-482D-8AB6-96EF37FA6977
24E3557D0-19AB-4A66-B89E-6396E7B8076F
6 мар 09, 13:12    [6896425]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
Glory
Member

Откуда:
Сообщений: 104764
step_ks


Теперь даем оптимизатору знать, что у нас в @tt поле gr уникально (изменим определение @tt):
declare @tt table (id int identity(1,1), gr int primary key)


А как вы при gr int primary key смогли добавить в таблицу

insert @t (gr) select 1
insert @t (gr) select 1
insert @t (gr) select 2
insert @t (gr) select 2
6 мар 09, 13:16    [6896464]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
step_ks
Member

Откуда:
Сообщений: 936
Вот, что пишет по этому поводу Itzik Ben-Gan в своей статье (привожу статью полностью, так как для доступа к статье вроде нужен логин). Там же в статье есть ссылка на feedback Microsoft и их мнение на этот счет.

Itzik Ben-Gan

* [July 21, 2008]
* Bug with NEWID Function
* Return to Blog Index
* By: Itzik Ben-Gan
* Puzzled by T-SQL
* InstantDoc #99807
* Web Exclusive from SQL Server Magazine

This bug was first described by Thomas Glörfeld here.

Related Microsoft Connect item can be found here.

The bug has to do with invoking the NEWID function in a query defining a table expression (derived table, CTE, view, inline table-valued function), and then joining the table expression with another table. If the relationship between the table expression and the other table is 1:M, each row from the table expression can appear multiple times in the result of the join. The bug is that depending on the optimizer’s choices in terms of physical processing, SQL Server may end up evaluating the NEWID function once per each target row instead of once per each source row.

This bug was tested on SQL Server versions 2000/SP4, 2005/SP2 and 2008/RC0.

As an example, the following code creates the tables t1 and t2 that are related in a 1:M relationship, and a view that invokes the NEWID function per each row from t1:
set nocount on;

use tempdb;

go

if object_id('dbo.v1', 'V') is not null drop view dbo.v1;
if object_id('dbo.t2', 'U') is not null drop table dbo.t2;
if object_id('dbo.t1', 'U') is not null drop table dbo.t1;

go

create table dbo.t1(id int not null primary key);
insert into dbo.t1(id) values(1);
insert into dbo.t1(id) values(2);

 
create table dbo.t2(id int not null references dbo.t1);
insert into dbo.t2(id) values(1);
insert into dbo.t2(id) values(1);
insert into dbo.t2(id) values(2);
insert into dbo.t2(id) values(2);

go

create view dbo.v1

as

select id, newid() as newid_val
from dbo.t1

go

Consider the following query:
select a.id as a_id, b.id as b_id, a.newid_val
from dbo.v1 as a
  join dbo.t2 as b
    on a.id = b.id;

You would expect the NEWID function to be evaluated per each source row; namely, per each row from t1. However, the function ends up being evaluated per each target row; namely, per each row in the result of the join. Here’s the output I got from one of the invocations of this query:
a_idb_idnewid_val
115232647A-0279-4F3C-9B7F-EA88B062A1AF
11B87D2681-26C4-4B9A-8895-35EF138BC964
22F69FE4D3-26AE-4642-9622-C8416C8FFC0C
220ED5950C-12BF-4778-90A3-2AC55CD59AAD


Instead of getting two distinct GUIDs (one per each source row from t1), you get four (one per each target row in the result of the join.

I posted the bug on Microsoft Connect (FeedbackID=350485), and after consideration, Microsoft decided to close the item and mark it as “Won’t Fix”. The reasoning behind the decision not to fix the bug is that in the vast majority of the cases, the optimization aspects that lead to the bug yield better performance without sacrificing the correctness of the query, and if you fall into one of the unusual cases where the correctness of the query is compromised, you can consider alternatives (e.g., physically materialize the data along with the NEWID values in a table).

Here’s the response from Microsoft:

“Closing the loop . . . I've discussed this question with the Dev team. And eventually we have decided not to change current behavior, for the following reasons:

1) The optimizer does not guarantee timing or number of executions of scalar functions. This is a long-established tenet. It's the fundamental 'leeway' that allows the optimizer enough freedom to gain significant improvements in query-plan execution.

2) This "once-per-row behavior" is not a new issue, although it's not widely discussed. We started to tweak its behavior back in the Yukon release. But it's quite hard to pin down precisely, in all cases, exactly what it means! For example, does it a apply to interim rows calculated 'on the way' to the final result? - in which case it clearly depends on the plan chosen. Or does it apply only to the rows that will eventually appear in the completed result? - there's a nasty recursion going on here, as I'm sure you'll agree!

3) As I mentioned earlier, we default to "optimize performance" - which is good for 99% of cases. The 1% of cases where it might change results are fairly easy to spot - side-effecting 'functions' such as NEWID - and easy to 'fix' (trading perf, as a consequence). This default to "optimize performance" again, is long-established, and accepted. (Yes, it's not the stance chosen by compilers for conventional programming languages, but so be it).

So, our recommendations are:

a) Avoid reliance on non-guaranteed timing and number-of-executions semantics.

b) Avoid using NEWID() deep in table expressions.

c) Use OPTION to force a particular behavior (trading perf)

Hope this explanation helps clarify our reasons for closing this bug as "won't fix".

Thanks,

Jim”

I wanted to point out the bug so that you would be aware of it and of the fact that a fix is not planned. If you need the NEWID function to be evaluated once per each source row, make sure you materialize the data in a table first.

Cheers,
BG
6 мар 09, 13:16    [6896468]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
step_ks
Member

Откуда:
Сообщений: 936
Glory
step_ks


Теперь даем оптимизатору знать, что у нас в @tt поле gr уникально (изменим определение @tt):
declare @tt table (id int identity(1,1), gr int primary key)


А как вы при gr int primary key смогли добавить в таблицу

insert @t (gr) select 1
insert @t (gr) select 1
insert @t (gr) select 2
insert @t (gr) select 2

Там же инсерт тоже изменен:
step_ks
declare @tt table (id int identity(1,1), gr int primary key)
insert @tt (gr) select 1
insert @tt (gr) select 2
6 мар 09, 13:20    [6896495]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
Glory
Member

Откуда:
Сообщений: 104764
step_ks
Glory
step_ks


Теперь даем оптимизатору знать, что у нас в @tt поле gr уникально (изменим определение @tt):
declare @tt table (id int identity(1,1), gr int primary key)


А как вы при gr int primary key смогли добавить в таблицу

insert @t (gr) select 1
insert @t (gr) select 1
insert @t (gr) select 2
insert @t (gr) select 2

Там же инсерт тоже изменен:
step_ks
declare @tt table (id int identity(1,1), gr int primary key)
insert @tt (gr) select 1
insert @tt (gr) select 2

Да вы и задачу изменили
А зачем тогда делать group by по полю primary key ?
Типа давайте усложним работу оптимизатору ?
6 мар 09, 13:23    [6896508]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
step_ks
Member

Откуда:
Сообщений: 936
Glory

Да вы и задачу изменили
А зачем тогда делать group by по полю primary key ?
Типа давайте усложним работу оптимизатору ?

Да, изменил, и решения уже никакого не прошу (но было бы интересно, появись оно вдруг в новой задаче). Просто случайно попалась на глаза вышеприведенная статья и я решил проверить фокус с max в описанной в ней ситуации, о результатах чего и сообщаю. Возможно, будет кому-то полезно.
6 мар 09, 13:31    [6896540]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
step_ks
Member

Откуда:
Сообщений: 936
Glory

А зачем тогда делать group by по полю primary key ?

Незачем, только для демонстрации на одном и том же селекте.
6 мар 09, 13:34    [6896560]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: newid() по группе  [new]
ATI.HeNRy
Member

Откуда:
Сообщений: 23
step_ks
Необходимо каждой группе записей, определяемой полем gr присвоить свой newid().
Одним селектом, без промежуточных таблиц и без update-ов.


  
Select 
  *,
  guid_gr=first_value(newid()) over(partition by gr order by (select null))  
  FROM
	(
	values(1),(1),(2),(2),(3),(3)
	) as A(gr)
18 янв 18, 20:01    [21117968]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3466
ATI.HeNRy
step_ks
Необходимо каждой группе записей, определяемой полем gr присвоить свой newid().
Одним селектом, без промежуточных таблиц и без update-ов.


  
Select 
  *,
  guid_gr=first_value(newid()) over(partition by gr order by (select null))  
  FROM
	(
	values(1),(1),(2),(2),(3),(3)
	) as A(gr)


Т.е. версию в вопросе видел?
"Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38"
19 янв 18, 09:55    [21118878]     Ответить | Цитировать Сообщить модератору
 Re: newid() по группе  [new]
ATI.HeNRy
Member

Откуда:
Сообщений: 23
Ролг Хупин,
Ага, после того как увидел.
Между сообщениями интервал более 1 года.
19 янв 18, 10:24    [21119021]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить