Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / newid() по группе / 21 сообщений из 21, страница 1 из 1
17.09.2008, 13:36
    #35544253
step_ks
step_ks
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Необходимо каждой группе записей, определяемой полем gr присвоить свой newid().
Одним селектом, без промежуточных таблиц и без update-ов.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
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

Результат:

grrowid1AB777FFA-609A-47B8-82F4-C7C60A41922016EE07AFF-14F7-4A3E-AEE7-19DC1738D6BA2EF3099D5-63EB-4BAC-BF6B-C2A242CEB13720EAFC893-0A4B-4B5A-8513-C667821E51E93134E2614-383D-4DB8-B06B-126846BA66DE

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

grrowid1AB777FFA-609A-47B8-82F4-C7C60A4192201AB777FFA-609A-47B8-82F4-C7C60A4192202EF3099D5-63EB-4BAC-BF6B-C2A242CEB1372EF3099D5-63EB-4BAC-BF6B-C2A242CEB1373134E2614-383D-4DB8-B06B-126846BA66DE

Код: plaintext
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 )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 13:43
    #35544277
Knyazev Alexey
Knyazev Alexey
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 13:55
    #35544313
step_ks
step_ks
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Спасибо, так то оно конечно понятно, но к сожалению
step_ksОдним селектом, без промежуточных таблиц и без update-ов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 13:57
    #35544316
Glory
Glory
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
step_ksСпасибо, так то оно конечно понятно, но к сожалению
step_ksОдним селектом, без промежуточных таблиц и без update-ов.

Ну так что мешает добавить еще один подзапрос ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 14:10
    #35544380
pkarklin
pkarklin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
GloryНу так что мешает добавить еще один подзапрос ?

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

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
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)

НО:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
;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().

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

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
;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

Я понимаю, что функция недетерминированная, но зачем же так жесто перевирать мой запрос?!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 14:18
    #35544411
pkarklin
pkarklin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Ну, впринципе, и в доке об этом сказано:

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.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 14:31
    #35544465
Glory
Glory
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Код: plaintext
1.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 14:32
    #35544470
aleks2
aleks2
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 14:36
    #35544480
pkarklin
pkarklin
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Glory
Код: plaintext
1.
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


Мдя...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 15:46
    #35544702
step_ks
step_ks
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Glory
Код: plaintext
1.
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 наводит порядок. Остается только гадать, а не "поумнеет" ли оптимизатор ли в один прекрасный день, начав его игнорировать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
17.09.2008, 15:56
    #35544742
step_ks
step_ks
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
aleks2
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
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-ов.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.03.2009, 13:12
    #35855784
step_ks
step_ks
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
В продолжение старого разговора, дабы окончательно разобраться.
Модифицируем пример из первого поста (используем 2 таблицы) и применим фокус с max по совету Glory:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
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

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

grrowid15EC65C27-0785-4F9F-B42A-376451349DFB15EC65C27-0785-4F9F-B42A-376451349DFB2416C0C76-C9F1-4240-8B75-BB83FFFA339B2416C0C76-C9F1-4240-8B75-BB83FFFA339B

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

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

grrowid1F897E384-5011-4E17-B67C-457BD7F09FEB1CD9075FA-E3A6-4220-BE1A-355DEECCBC1020F25E3A6-7EF9-482D-8AB6-96EF37FA697724E3557D0-19AB-4A66-B89E-6396E7B8076F
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.03.2009, 13:16
    #35855798
Glory
Glory
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
step_ks

Теперь даем оптимизатору знать, что у нас в @tt поле gr уникально (изменим определение @tt):
Код: plaintext
1.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.03.2009, 13:16
    #35855801
step_ks
step_ks
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Вот, что пишет по этому поводу 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:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
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:
Код: plaintext
1.
2.
3.
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_val115232647A-0279-4F3C-9B7F-EA88B062A1AF11B87D2681-26C4-4B9A-8895-35EF138BC96422F69FE4D3-26AE-4642-9622-C8416C8FFC0C220ED5950C-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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.03.2009, 13:20
    #35855814
step_ks
step_ks
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Glorystep_ks

Теперь даем оптимизатору знать, что у нас в @tt поле gr уникально (изменим определение @tt):
Код: plaintext
1.
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
Код: plaintext
1.
2.
declare @tt table (id int identity( 1 , 1 ), gr int primary key)
insert @tt (gr) select  1 
insert @tt (gr) select  2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.03.2009, 13:23
    #35855819
Glory
Glory
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
step_ksGlorystep_ks

Теперь даем оптимизатору знать, что у нас в @tt поле gr уникально (изменим определение @tt):
Код: plaintext
1.
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
Код: plaintext
1.
2.
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 ?
Типа давайте усложним работу оптимизатору ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.03.2009, 13:31
    #35855837
step_ks
step_ks
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Glory
Да вы и задачу изменили
А зачем тогда делать group by по полю primary key ?
Типа давайте усложним работу оптимизатору ?
Да, изменил, и решения уже никакого не прошу (но было бы интересно, появись оно вдруг в новой задаче). Просто случайно попалась на глаза вышеприведенная статья и я решил проверить фокус с max в описанной в ней ситуации, о результатах чего и сообщаю. Возможно, будет кому-то полезно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.03.2009, 13:34
    #35855847
step_ks
step_ks
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Glory
А зачем тогда делать group by по полю primary key ?

Незачем, только для демонстрации на одном и том же селекте.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
18.01.2018, 20:01
    #39586702
ATI.HeNRy
ATI.HeNRy
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
step_ksНеобходимо каждой группе записей, определяемой полем gr присвоить свой newid().
Одним селектом, без промежуточных таблиц и без update-ов.


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
  
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)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.01.2018, 09:55
    #39586861
Ролг Хупин
Ролг Хупин
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
ATI.HeNRystep_ksНеобходимо каждой группе записей, определяемой полем gr присвоить свой newid().
Одним селектом, без промежуточных таблиц и без update-ов.


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
  
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"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.01.2018, 10:24
    #39586889
ATI.HeNRy
ATI.HeNRy
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
newid() по группе
Ролг Хупин,
Ага, после того как увидел.
Между сообщениями интервал более 1 года.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / newid() по группе / 21 сообщений из 21, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]