June 9, 2009 at 3:52 am
declare @tbl table(id int)
insert @tbl select 12
insert @tbl select 13
insert @tbl select 16
insert @tbl select 17
insert @tbl select 18
insert @tbl select 15
insert @tbl select 14
insert @tbl select 19
insert @tbl select 23
insert @tbl select 34
insert @tbl select 10
insert @tbl select 45
insert @tbl select 27
insert @tbl select 46
insert @tbl select 47
--I got a requirement to assign first 10 Ids to 10 variables.
--I got required output using ( 1 ) Cursor ( 2 ) Table variable
--but it will kill the performance.
--How can i do that keeping performance in view?
Thank in Advance 🙂
June 9, 2009 at 4:32 am
--I got a requirement to assign first 10 Ids to 10 variables.
What does it mean? What will you do with these variables? Will you use the variables in a stored procedure?
--I got required output using ( 1 ) Cursor ( 2 ) Table variable
What do you mean "output" with cursor/table variable? Will you return multiple result sets?
I think you might be interested in reading this article:
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
and coming back with more detailed info.
As soon as I can understand what you need I'll be glad to help you.
Regards
Gianluca
-- Gianluca Sartori
June 9, 2009 at 5:43 am
Hi
Gianluca Sartori
declare @tbl table(id int)
insert @tbl select 12
insert @tbl select 13
insert @tbl select 16
insert @tbl select 17
insert @tbl select 18
insert @tbl select 15
insert @tbl select 14
insert @tbl select 19
insert @tbl select 23
insert @tbl select 34
insert @tbl select 10
insert @tbl select 45
insert @tbl select 27
insert @tbl select 46
insert @tbl select 47
Declare @Q1 BIGINT
Declare @Q2 BIGINT
Declare @Q3 BIGINT
Declare @Q4 BIGINT
Declare @Q5 BIGINT
Declare @Q6 BIGINT
Declare @Q7 BIGINT
Declare @Q8 BIGINT
DECLARE @Q9 BIGINT
DECLARE @Q10 BIGINT
DECLARE@I INT,@QuestionID bigint
SET @I = 1
DECLARE CUR CURSOR DYNAMIC FOR SELECT top 10 ID FROM @tbl
OPEN CUR
FETCH FIRST FROM CUR INTO @QuestionID
WHILE (@@FETCH_STATUS = 0 )
BEGIN
IF(@I = 1)
SET @Q1 = @QuestionID
ELSE IF (@I = 2)
SET @Q2 = @QuestionID
ELSE IF (@I = 3)
SET @Q3 = @QuestionID
ELSE IF (@I = 4)
SET @Q4 = @QuestionID
ELSE IF (@I = 5)
SET @Q5 = @QuestionID
ELSE IF (@I = 6)
SET @Q6 = @QuestionID
ELSE IF (@I = 7)
SET @Q7 = @QuestionID
ELSE IF (@I = 8)
SET @Q8 = @QuestionID
ELSE IF (@I = 9)
SET @Q9 = @QuestionID
ELSE IF (@I = 10)
SET @Q10= @QuestionID
SET @I = @I + 1
FETCH NEXT FROM CUR INTO @QuestionID
END
CLOSE CUR
DEALLOCATE CUR
SELECT @Q1 ,@Q2 , @Q3 ,@Q4 ,@Q5 ,@Q6 ,@Q7 ,@Q8 ,@Q9 ,@Q10
--I need the above output. I have done this using cursor.As it kills performance
--will u suggest another way of doing the same?
Thanks in advance 🙂
June 9, 2009 at 5:55 am
Have you tried this?
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY GETDATE())as NUM, Id FROM @tbl
) p
PIVOT (MIN(ID) FOR NUM IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) PVT
You can just use the same sample data and tables that you supplied
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 9, 2009 at 6:32 am
And another solution:
; WITH cte AS
(
SELECT
id,
ROW_NUMBER() OVER (ORDER BY id) RowNum
FROM @tbl
)
SELECT
@Q1 = CASE WHEN RowNum = 1 THEN id ELSE @Q1 END,
@Q2 = CASE WHEN RowNum = 2 THEN id ELSE @Q2 END,
@Q3 = CASE WHEN RowNum = 3 THEN id ELSE @Q3 END,
@Q4 = CASE WHEN RowNum = 4 THEN id ELSE @Q4 END,
@Q5 = CASE WHEN RowNum = 5 THEN id ELSE @Q5 END,
@Q6 = CASE WHEN RowNum = 6 THEN id ELSE @Q6 END,
@Q7 = CASE WHEN RowNum = 7 THEN id ELSE @Q7 END,
@Q8 = CASE WHEN RowNum = 8 THEN id ELSE @Q8 END,
@Q9 = CASE WHEN RowNum = 9 THEN id ELSE @Q9 END,
@Q10 = CASE WHEN RowNum = 10 THEN id ELSE @Q10 END
FROM cte
SELECT
@Q1, @Q2, @Q3, @Q4, @Q5, @Q6, @Q7, @Q8, @Q9, @Q10
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
June 9, 2009 at 6:47 am
Thanks Christopher Stobbs
Ur Query is performing btter than mine:-)
June 9, 2009 at 6:48 am
Thanks Florian Reischl
ur query wil perform better than mine
June 9, 2009 at 9:15 pm
Sorry... wrong post.
--Jeff Moden
June 9, 2009 at 9:20 pm
Heh... no, I had the right post.
No one has correctly defined what the "first ten" will be. Without something to do an ORDER BY in the data, all of the methods could run as expected and then suddenly change their mind. You cannot rely on the "natural order" of SELECTs even with TOP. There must be an ORDER BY to be guaranteed.
Now, if you want what will usually be the first 10 in the natural order of a select with the understanding that it could change any second, then they work. But, the fact that they could change any second should be a little unnerving to folks if the first 10 are truly important.
--Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply