Создание временной таблицы

@Unston · Регистрация: 27.01.2013

Author24 — интернет-сервис помощи студентам

Здравствуйте.
Есть таблица с полями А,Б оба int. Значения А может повторяться несколько раз, но не более 3х. В Б все значения различны. Нужно в Т-SQL создать временную таблицу из четырех колонок, в которой в первой колонке бы были только уникальные значения, во второй, третьей, четвертой соответсвующие им значения Б. Если соответствующих значений меньше 3х тогда просто заполнялся бы НУЛЛ.
A1 Б1
А2 Б3
А1 Б2
A2 Б4
А2 Б5

Нужно получить:

А1 Б1 Б2 НУЛЛ
А2 Б3 Б4 Б5

Подскажите в каком направление двигаться.

@cygapb-007 · 27.01.2013, 11:45

можно попробовать select...for xml и затем парсинг xml

Добавлено через 9 часов 53 минуты

Сообщение от Unston

Есть таблица с полями А,Б оба int. Значения А может повторяться несколько раз, но не более 3х. В Б все значения различны. Нужно в Т-SQL создать временную таблицу из четырех колонок, в которой в первой колонке бы были только уникальные значения, во второй, третьей, четвертой соответсвующие им значения Б.

Что-то вроде

T-SQL

select a.A, b.Bxml
from (select distinct t.A from tab) a
cross apply (
   select t.B as Bxml
   from tab t where t.A = a.A
   for xml raw )b

а дальше как тут

Ну или еще проще

T-SQL

declare @t table(A int, B int, N int)
insert into @t
select t.A, t.B
   ROW_NUMBER() OVER(PARTITION BY t.A ORDER BY t.B)
 
select t.A, t.B, t2.B, t3.B
from @t t
left join @t t2 on t2.A=t.A and t2.N=2
left join @t t3 on t3.A=t.A and t3.N=3
where t.N=1

@Unston · 27.01.2013, 22:18 **[ТС]**

спасибо. попробую.

@Devil_FoX · 28.01.2013, 11:52

cygapb-007, эм. А зачем всё делать через кхм?
Есть специальные временные таблицы
http://msdn.microsoft.com/ru-r... .100).aspx

T-SQL

if object_id('tempdb..#tabl') is not null drop table #tabl  -- проверка на существование таблицы
create table #tabl -- создание временной таблицы
    (A int,
    B int,
    C int)
 
insert into #table -- внос инфы во временную таблицу
select
.....

@cygapb-007 · 28.01.2013, 15:31

Сообщение от Devil_FoX

cygapb-007, эм. А зачем всё делать через кхм?
Есть специальные временные таблицы
http://msdn.microsoft.com/ru-r... .100).aspx

Через кхм - это через @ ? Напомню, что правильное обозначение для кхм - ¤, или "солнышко", если вы играете в преф, например.
В свою очередь тоже могу подкинуть несколько ссылок, например эту: DECLARE @local_variable (Transact-SQL), или вот эту:INF: Frequently Asked Questions - SQL Server 2000 - Table Variables.
Если коротко - то преимущества и недостатки @ по сравнению с # отлично изложены вот здесь. Приведу одну цитату:

Общие рекомендации Microsoft, относительно использования табличных переменных таковы: "Используйте их везде, где это возможно, кроме тех случаев, когда у вас хранятся значительные объёмы данных, и присутствует повторное использование таблиц"

@iap · 28.01.2013, 16:15

Сообщение от cygapb-007

Если коротко - то преимущества и недостатки @ по сравнению с # отлично изложены вот здесь.

Чего-то Денис Резник по данной ссылке напоминает чайника.
Одно утверждение

"На табличных переменных нельзя создавать некластерные индексы"

чего стоит!
А вот тут, например, разве не создаются аж два некластерных индекса?!

T-SQL

1	DECLARE @T TABLE (ID INT NOT NULL IDENTITY PRIMARY KEY NONCLUSTERED, X INT, UNIQUE NONCLUSTERED (X,ID));

@cygapb-007 · 28.01.2013, 16:33

Я так понимаю, по поводу преимуществ возражений не последовало))?

А если учесть дату публикации (Опубліковані 31-01-2010) - то может так оно и было...
просто первая попавшаяся ссылка в поисковике...

@Devil_FoX · 28.01.2013, 17:43

cygapb-007, ну с параметром нет возможности работать в последствии. он правильно не воспринимает команды Delete, Update и т.д.
+ я работаю от миллионников до десятков миллиардов строк - а параметр вроде бы помещает определённое количество строк.

@cygapb-007 · 28.01.2013, 17:57

Сообщение от Devil_FoX

cygapb-007, ну с параметром нет возможности работать в последствии. он правильно не воспринимает команды Delete, Update и т.д.
+ я работаю от миллионников до десятков миллиардов строк - а параметр вроде бы помещает определённое количество строк.

В смысле не параметр, а табличная переменная? Почему же, @table прекрасно понимает и Delete, и Update, и Insert. А поскольку @table хранится в той же TempDB, то и по размеру ограничена только размером жесткого диска. В приводимой сслылке ведь написано, что

Табличные переменные не содержат статистику
Табличные переменные не могут использоваться в INSERT EXEC или SELECT INTO
Запросы, изменяющие табличные переменные, не создают параллельных планов выполнения запроса

Первый из указанных по ссылке недостатков был успешно опровергнут, чему я очень рад (хотя имелась в виду скорее всего невозможность "alter table @"), а вот последний - действительно может убедить в необходимости (в этом конкретном случае) использовать # вместо @
Но опять же

в каждом конкретном случае нужно смотреть и пробовать что вам больше подходит

Например,

T-SQL

declare @BAZSPEC TABLE (
    [CUDA] [varchar] (25) COLLATE Cyrillic_General_CI_AS NULL CHECK ([CUDA] > '') ,
    [PRR] [varchar] (1) COLLATE Cyrillic_General_CI_AS NULL ,
    [PRU] [varchar] (1) COLLATE Cyrillic_General_CI_AS NULL ,
    [CHTO] [varchar] (25) COLLATE Cyrillic_General_CI_AS NULL CHECK ([CHTO] > ''),
    [KVO] [float] NULL ,
    [TABN] [int] NULL DEFAULT (0),
    [DAT] [smalldatetime] NULL, 
    check ( [CUDA] <> [CHTO])
    )

прекрасно обругала меня при попытке вставки пустых строк или дубликатов согласно check-ам

(пример c sql.ru)

@Devil_FoX · 28.01.2013, 18:31

cygapb-007, No comments
http://msdn.microsoft.com/ru-r... 75010.aspx

Рекомендации
Не используйте табличные переменные для хранения больших объемов данных (более 100 строк). Плановые запросы могут оказаться неоптимальными или нестабильными при использовании табличных переменных с большим объемом данных. Попробуйте переписать такие запросы, чтобы они использовали временные таблицы или воспользуйтесь указанием запроса USE PLAN с тем, чтобы обеспечить использование оптимизатором существующего плана запроса, который хорошо работает в выбранном сценарии.

Добавлено через 3 минуты
P.S. По своему опыту знаю - автора книг очень часто не правы и у них очень много ошибок, самое качественное вникание в тему это прочитать минимум 3 независимых источника и потом ещё самому поэкспериментировать с данными.

@iap · 28.01.2013, 20:02

Сервер, строя план выполнения запроса с участием табличной переменной, всегда полагает,
что в этой переменной одна запись! Статистики-то нет!
Можно себе представить качество этого плана, если в наличии на самом деле миллион записей.

Однако, есть и плюсы. Например, если надо в транзакции сохранить нечто для того,
чтобы после отката получить это "нечто", то проще всего воспользоваться табличной переменной -
она же в откате не участвует!

@cygapb-007 · 29.01.2013, 12:08

230к строк... Порядок результатов одинаков
Там, где ожидается 1 строка результата, # всегда отстает.

Кликните здесь для просмотра всего текста

T-SQL

if OBJECT_ID('tempdb..#msg','U') is not null drop table #msg
create table #msg (
    message_id int, language_id smallint, severity tinyint, is_event_logged bit, 
    text nvarchar(100), hash_text as checksum(text) persisted,
    primary key(message_id,language_id))
create index #msg_hash on #msg (hash_text)
create index #msg_text on #msg (text)
 
insert into #msg
SELECT m.message_id, m.language_id, m.severity, m.is_event_logged, left(m.text,100)--, CHECKSUM(left(m.text,100))
    FROM sys.messages m --where m.language_id in (1049, 1033,1031)
 
declare @msg table (
    message_id int, language_id smallint, severity tinyint, is_event_logged bit, 
    text nvarchar(100), hash_text as checksum(text) persisted,
    primary key(message_id,language_id,text))
insert @msg select message_id, language_id, severity, is_event_logged, text  from #msg
--select * from #msg order by len(text)
 
declare @timing table(id int /*identity*/, timer datetime)
insert @timing values (1,getdate())
SELECT * FROM @msg where hash_text=CHECKSUM('уведомление') and text = 'уведомление'
insert @timing values (2,getdate())
SELECT * FROM #msg where hash_text=CHECKSUM('уведомление') and text = 'уведомление'
insert @timing values (3,getdate())
SELECT * FROM @msg where text like '%уведомление%'
insert @timing values (4,getdate())
SELECT * FROM #msg where text like '%уведомление%'
insert @timing values (5,getdate())
SELECT * FROM @msg where text = 'уведомление'
insert @timing values (6,getdate())
SELECT * FROM #msg where text = 'уведомление'
insert @timing values (7,getdate())
SELECT * FROM @msg where message_id=14210 and language_id=1049
insert @timing values (8,getdate())
SELECT * FROM #msg where message_id=14210 and language_id=1049
insert @timing values (9,getdate())
;with delta as (
    select t.id, t.timer start, n.timer stop, DATEDIFF(mcs,t.timer,n.timer) delta
        from @timing t
        join @timing n on n.id=t.id+1
    )
select *, [as#].delta-[as@].delta [#-@]
    from delta [as@] 
    join delta [as#] on [as#].id=[as@].id+1
    where [as@].id%2=1

Код

id          start                   stop                    delta       id          start                   stop                    delta       #-@
----------- ----------------------- ----------------------- ----------- ----------- ----------------------- ----------------------- ----------- -----------
1           2013-01-29 09:22:09.717 2013-01-29 09:22:09.757 40000       2           2013-01-29 09:22:09.757 2013-01-29 09:22:11.783 2026000     1986000
3           2013-01-29 09:22:11.783 2013-01-29 09:22:14.430 2646000     4           2013-01-29 09:22:14.430 2013-01-29 09:22:16.777 2346000     -300000
5           2013-01-29 09:22:16.777 2013-01-29 09:22:16.817 40000       6           2013-01-29 09:22:16.817 2013-01-29 09:22:17.007 190000      150000
7           2013-01-29 09:22:17.007 2013-01-29 09:22:17.067 60000       8           2013-01-29 09:22:17.067 2013-01-29 09:22:17.443 376000      316000

Добавлено через 7 минут
* порядок результата на отборе 13 строк из 230К - одинаков

Добавлено через 2 часа 29 минут
(Конечно, именно в этом конкретном случае :-))

@Unston 0 / 0 / 0 Регистрация: 27.01.2013 Сообщений: 20
		1
	Создание временной таблицы 27.01.2013, 01:29. Показов 50727. Ответов 11 Метки нет (Все метки) Здравствуйте. Есть таблица с полями А,Б оба int. Значения А может повторяться несколько раз, но не более 3х. В Б все значения различны. Нужно в Т-SQL создать временную таблицу из четырех колонок, в которой в первой колонке бы были только уникальные значения, во второй, третьей, четвертой соответсвующие им значения Б. Если соответствующих значений меньше 3х тогда просто заполнялся бы НУЛЛ. A1 Б1 А2 Б3 А1 Б2 A2 Б4 А2 Б5 Нужно получить: А1 Б1 Б2 НУЛЛ А2 Б3 Б4 Б5 Подскажите в каком направление двигаться. 0

@Unston 0 / 0 / 0 Регистрация: 27.01.2013 Сообщений: 20
	27.01.2013, 22:18 [ТС]	3
	спасибо. попробую. 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	28.01.2013, 15:31	5
	Сообщение от Devil_FoX cygapb-007, эм. А зачем всё делать через кхм? Есть специальные временные таблицы http://msdn.microsoft.com/ru-r... .100).aspx Через кхм - это через @ ? Напомню, что правильное обозначение для кхм - ¤, или "солнышко", если вы играете в преф, например. В свою очередь тоже могу подкинуть несколько ссылок, например эту: DECLARE @local_variable (Transact-SQL), или вот эту:INF: Frequently Asked Questions - SQL Server 2000 - Table Variables. Если коротко - то преимущества и недостатки @ по сравнению с # отлично изложены вот здесь. Приведу одну цитату: Общие рекомендации Microsoft, относительно использования табличных переменных таковы: "Используйте их везде, где это возможно, кроме тех случаев, когда у вас хранятся значительные объёмы данных, и присутствует повторное использование таблиц" 0

@cygapb-007 1313 / 945 / 144 Регистрация: 17.01.2013 Сообщений: 2,348
	28.01.2013, 16:33	7
	Я так понимаю, по поводу преимуществ возражений не последовало))? А если учесть дату публикации (Опубліковані 31-01-2010) - то может так оно и было... просто первая попавшаяся ссылка в поисковике... 0

@Devil_FoX 168 / 142 / 1 Регистрация: 01.04.2010 Сообщений: 474
	28.01.2013, 17:43	8
	cygapb-007, ну с параметром нет возможности работать в последствии. он правильно не воспринимает команды Delete, Update и т.д. + я работаю от миллионников до десятков миллиардов строк - а параметр вроде бы помещает определённое количество строк. 0

@Devil_FoX 168 / 142 / 1 Регистрация: 01.04.2010 Сообщений: 474
	28.01.2013, 18:31	10
	cygapb-007, No comments http://msdn.microsoft.com/ru-r... 75010.aspx Рекомендации Не используйте табличные переменные для хранения больших объемов данных (более 100 строк). Плановые запросы могут оказаться неоптимальными или нестабильными при использовании табличных переменных с большим объемом данных. Попробуйте переписать такие запросы, чтобы они использовали временные таблицы или воспользуйтесь указанием запроса USE PLAN с тем, чтобы обеспечить использование оптимизатором существующего плана запроса, который хорошо работает в выбранном сценарии. Добавлено через 3 минуты P.S. По своему опыту знаю - автора книг очень часто не правы и у них очень много ошибок, самое качественное вникание в тему это прочитать минимум 3 независимых источника и потом ещё самому поэкспериментировать с данными. 0

@iap 1116 / 761 / 183 Регистрация: 27.11.2009 Сообщений: 2,270
	28.01.2013, 20:02	11
	Сервер, строя план выполнения запроса с участием табличной переменной, всегда полагает, что в этой переменной одна запись! Статистики-то нет! Можно себе представить качество этого плана, если в наличии на самом деле миллион записей. Однако, есть и плюсы. Например, если надо в транзакции сохранить нечто для того, чтобы после отката получить это "нечто", то проще всего воспользоваться табличной переменной - она же в откате не участвует! 1

	29.01.2013, 12:08