December 21, 2007 at 12:44 pm
Hello,
I would like to know whether there is a way to add a dynamic column to the select query so that it gives the information as follows:
select descr from table A
will give
Descr
-----
This
This is
This is to
This is to test
This is to test the
but I would like to display as
Counter Descr
------- -----
1 This
2 This is
3 This is to
4 This is to test
5 This is to test the
Hope there is no identity column on the table. I know by adding the identity column it is much more easy to get the results.
TIA
Lucky
December 21, 2007 at 12:46 pm
What is the primary key of the table? Please include the datatype...
--Jeff Moden
December 21, 2007 at 1:21 pm
I'm sure Jeff will have a superior answer, but here is my inefficient answer:
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP TABLE #TempTable
CREATE TABLE #TempTable (
Descr varchar(100)
)
INSERT #TempTable
SELECT 'This is to test the'
DECLARE @Results TABLE (
Counter int IDENTITY(1,1) primary key,
Descr varchar(100)
)
DECLARE @Descr varchar(100),
@pos tinyint
SELECT @Descr = LTRIM(RTRIM(Descr)) + ' ' from #TempTable
SELECT @pos = CHARINDEX(' ', @Descr)
WHILE @pos > 0
BEGIN
INSERT @Results
SELECT LEFT(@Descr, @pos)
SELECT @pos = CHARINDEX(' ', @Descr, @pos+1)
END
SELECT Counter, Descr FROM @results
December 21, 2007 at 1:38 pm
Hello Jeff / David,
Thanks for your inputs. As indicated in my post let us hope that there is no primary key on the table. Also I can write the logic by having a temp table. But I would like to know whether there is any way to dynamically represent an incremental counter value in the results.
TIA
Lucky
December 21, 2007 at 2:20 pm
As long as the data is unique in some way this is a simple task. I am assuming from your example that Descr is a varchar with some max length and each subsequent line is larger than the one prior. So in the first case I use datalength but if you had a unique key you could use that and I did that in the secnd case. If you have a compound key it is a little more involved, but just as easy:
You can run this sample code anywhere, it will create and populate a temp table then clean up after itself.
create table #temp
(
descr varchar(50)
)
go
insert into #temp
select 'This'
union all
select 'This is'
union all
select 'This is to'
union all
select 'This is to test'
union all
select 'This is to test the'
go
select (select count(*)
from #temp t2
where datalength(t2.descr) <= datalength(t1.descr)
) Counter
,descr from #temp t1
go
select (select count(*)
from #temp t2
where t2.descr <= t1.descr
) Counter
,descr from #temp t1
go
drop table #temp
December 21, 2007 at 6:04 pm
The best answer for SQL Server 2000 is, in fact, the Temp Table solution. It's nasty fast, especially if you use a SELECT/INTO.
The solution that Absinthe showed ...
select (select count(*)
from #temp t2
where t2.descr <= t1.descr
) Counter
,descr from #temp t1
... is fine for very small result sets, but has huge performance and resource problems because it contains a "Triangular Join". Just to calculate what amounts to a "running count" or "ordinal rank" like this on a lousy 1,000 rows means that SQL Server has to generate more than 500,500 internal rows.
Please see my article titled "Hidden RBAR: Triangular Joins" at the following URL:
http://qa.sqlservercentral.com/articles/T-SQL/61539/
If you look at the Estimated Execution Plan for the above code, everything looks just fine. If you look at the Actual Execution Plan, you see that the right most arrow contains 15 rows... (52 + 5)/2 = 15 just like I say in the article. Then, on top of that, you have the original 5 rows on the top leg... that's a total of 20 internal rows generated just to return 5. That's what I call "RBAR on Steroids" 😉
--Jeff Moden
December 21, 2007 at 11:14 pm
Jeff Moden (12/21/2007)
The best answer for SQL Server 2000 is, in fact, the Temp Table solution. It's nasty fast, especially if you use a SELECT/INTO.The solution that Absinthe showed ...
select (select count(*)from #temp t2
where t2.descr <= t1.descr
) Counter
,descr from #temp t1
... is fine for very small result sets, but has huge performance and resource problems because it contains a "Triangular Join". Just to calculate what amounts to a "running count" or "ordinal rank" like this on a lousy 1,000 rows means that SQL Server has to generate more than 500,500 internal rows.
Please see my article titled "Hidden RBAR: Triangular Joins" at the following URL:
http://qa.sqlservercentral.com/articles/T-SQL/61539/
If you look at the Estimated Execution Plan for the above code, everything looks just fine. If you look at the Actual Execution Plan, you see that the right most arrow contains 15 rows... (52 + 5)/2 = 15 just like I say in the article. Then, on top of that, you have the original 5 rows on the top leg... that's a total of 20 internal rows generated just to return 5. That's what I call "RBAR on Steroids" 😉
I don't argue with what you have said, I agree 100% relative to efficacy and such. I like it merely for it's gee-whiz factor.
However, the original poster specifically did not want a temp table:
Hello Jeff / David,
Thanks for your inputs. As indicated in my post let us hope that there is no primary key on the table. Also I can write the logic by having a temp table. But I would like to know whether there is any way to dynamically represent an incremental counter value in the results.
TIA
Lucky
December 21, 2007 at 11:27 pm
Jeff Moden (12/21/2007)
The best answer for SQL Server 2000 is, in fact, the Temp Table solution. It's nasty fast, especially if you use a SELECT/INTO.
Red Flag time again. I would never allow a SELECT INTO out into a production environment. I rarely even type them when i am testign stuff. I almost always just create the table and insert into ... select..
December 22, 2007 at 8:10 pm
Hmm, I agree with Jeff that SQL 2000 you need a temp table. You can use the ROW_NUMBER() function in SQL 2005 though...
I haven't checked the syntax but the idea's there (still using SQL 2000 for most of my stuff) (borrowed temp table insertion for testing as I don't have a table called
with your data in it - imagination to be used please)
create table #temp(descr varchar(50))
insert into #temp
select 'This'
union all select 'This is'
union all select 'This is to'
union all select 'This is to test'
union all select 'This is to test the'
select ROW_NUMBER() OVER (order by descr),
descr
from #temp
drop table #temp
That ought to do the trick
December 23, 2007 at 1:04 am
Absinthe (12/21/2007)
Jeff Moden (12/21/2007)
The best answer for SQL Server 2000 is, in fact, the Temp Table solution. It's nasty fast, especially if you use a SELECT/INTO.Red Flag time again. I would never allow a SELECT INTO out into a production environment. I rarely even type them when i am testign stuff. I almost always just create the table and insert into ... select..
That's a bit of a shame... you're missing out on a lot of speed. If you're worried about locking sysobjects in TempDB, don't... the lock doesn't last very long.
--Jeff Moden
December 23, 2007 at 11:40 am
Faster for whom? If it is faster for SQL Server and the computer/network etc... Fine! However, if it is just faster for me to type, it is not worth the risk. Problem, is when somethgin presents as a problem in one version then maybe corrects in a newer version, once you have accustommed yourself to not using, you continue to do so.
Don't get me wrong, if I am workign in Querty Analyzer and need to do somethgin quick, I dont' have a problem with it. However, if I am writing a sproc that is to be part of an ongoing production environment then absolutely not.
December 23, 2007 at 5:19 pm
Not worth the risk? What risk? I think you're over reacting to "Clone SQL Advise". SELECT INTO will create a new table with a million rows in seconds... that's if the underlying query is actually a properly written set-based query.
So far as the triangular join and the "gee-whiz" factor you mention... yeah, that's what I say... "Gee-Whiz!!!! Why is this taking so long to run???!!!" 😉
If you want a real gee-whiz factor, wear electric pants... if you want fast code, avoid the triangular join.
--Jeff Moden
December 23, 2007 at 5:58 pm
Here's an example of how bad your "gee-whiz" factor code is... first, create the following test table... yeah, I know... It uses SELECT INTO... just do it...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
SELECT TOP 20000
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO dbo.JBMTest
FROM Master.dbo.SysColumns t1,
Master.dbo.SysColumns t2 --Lack of join criteria makes this a CROSS-JOIN
Now, you write code using the triangular join method you just recommended to return all columns in order by the SomeDate column. You'll probably end up with something along the lines of this...
SELECT RowNum = (SELECT COUNT(*) FROM dbo.JBMTest t1 WHERE t1.SomeDate <= t2.SomeDate),
t2.*
FROM dbo.JBMTest t2
ORDER BY t2.SomeDate
(20000 row(s) affected)
Table 'Worktable'. Scan count 20000, logical reads 717313, physical reads 0, read-ahead reads 0.
Table 'JBMTest'. Scan count 2, logical reads 592, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 57094 ms, elapsed time = 62458 ms.
Lemme translate for ya... that little bit of "gee-whiz" code locked up my single CPU for over 57 seconds, scanned an internal table 20,000 times, and did 3/4 of a MILLION logical reads.
Now, check this out...
SELECT RowNum = IDENTITY(INT,1,1),
t2.*
INTO #MyHead
FROM dbo.JBMTest t2
ORDER BY t2.SomeDate
SELECT *
FROM #MyHead
ORDER BY SomeDate
Table '#MyHead_____________________________________________________________________________________________________________00000000002A'.
Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'JBMTest'. Scan count 1, logical reads 296, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 203 ms, elapsed time = 233 ms.
(20000 row(s) affected)
(20000 row(s) affected)
Table '#MyHead_____________________________________________________________________________________________________________00000000002A'.
Scan count 1, logical reads 305, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 93 ms, elapsed time = 604 ms.
Again, lemme translate... the SELECT INTO code only did 2 scans of a total of 606 logical reads, took just over a quarter quarter second of CPU time and took just over another quarter second to return all the rows.
Now, "faster for who"? The answer is faster for the developer to write, faster for the CPU to execute, faster for the I/O system to move, and faster for all the other folks waiting on the CPU that your code locked out because it slammed the CPU into the wall for almost a minute. And because the SELECT INTO occurred in TempDB and it's recovery mode is set to "simple", the transaction log didn't grow a bit.
I don't know why you're so afraid of SELECT INTO, but like any other tool, it can bring great benefit as well as being abused. Learn how to use it to great benefit and don't let all of the "SQL Clone Nay-Sayers" talk you out of it.
--Jeff Moden
December 23, 2007 at 6:30 pm
Woohoo!! Electric pants!!
Actually we have 2 differnt issues here:
1.
I was not saying that the code was fast, merely that it specifically satisfied the described problem of doing it inline without using a temp table. It is a mere parlor trick.
2. (completely unrelated to #1)
As for the "select into" I was red flagging it relative to its corresponding
Create Table #temp
(
...
)
insert into #temp
select ...
Other than the fact that it is easier to type, it is no faster to use. Also, it will cause more than necessary recompiles since it does ddl in the middle of the sproc. I am pretty sure it is not ansi standard. It works differntly in PL sql as it does in TSQL and if I am nto mistaken doesn't even exist in MYSQL at all.
The fact that it is locking both tempdb (the whole database) adds to the issues. As well as the fact that the locks of system objects and all the other overhead depending on what is happening when you call it. Maybe 2005 has changed thsi behavior, but I will not know because I will not be using it in a production environment.
If you however don't know enough do do Create Table (because you won't know until runtime or whatever, not because you don't understand syntax 😀 )
You can always do the insert into but with a false where clause such as
select *
into #temp
from MyTable
where 0 = 1
Followed by a proper
insert into #temp
select ... from MyTable
I have yet to come on a situateion where I have had to do this, but this would be my plan if I was forced to use select ... into syntax.
December 23, 2007 at 7:07 pm
Absinthe (12/23/2007)
Actually we have 2 differnt issues here:1.
I was not saying that the code was fast, merely that it specifically satisfied the described problem of doing it inline without using a temp table. It is a mere parlor trick.
Why would you teach someone who is asking for help a parlor trick instead of the right way to do things... if they take your word for it instead of taking it as a parlor trick, they're in trouble.
2. (completely unrelated to #1)
As for the "select into" I was red flagging it relative to its corresponding
Create Table #temp
(
...
)
insert into #temp
select ...
Other than the fact that it is easier to type, it is no faster to use. Also, it will cause more than necessary recompiles since it does ddl in the middle of the sproc. I am pretty sure it is not ansi standard. It works differntly in PL sql as it does in TSQL and if I am nto mistaken doesn't even exist in MYSQL at all.
I agree, but since you're using some form of an identity column to get the numbering in this case, your select would require a full column list as least in the insert clause. 😉
Yes, I agree about the recompiles... unless you execute the SELECT INTO at the beginning of the code so you don't interlace the DDL with DML. And, don't forget... if as little as 4 or 5 rows change in a table, you may get a recompile on the next call, anyway.
Who cares about Ansi Standard? If you did, you wouldn't use a Temp Table at all. 😉 And why are you talking about Oracle and MySQL? You can't possibly believe in the myth of portable code.
The fact that it is locking both tempdb (the whole database) adds to the issues.
At least it's not a parlor trick that cripples a CPU for a minute on only 20,000 rows 😛
But I will agree that it depends... if it's GUI code that's making hundreds or thousands of hits per second, then you should probably avoid SELECT INTO and use CREATE TABLE. In fact, if it's GUI code and you need a temp table, you're probably doing something wrong anyway. :hehe:
If it's reporting or other batch code, no problem.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply