rowcount in ''select into''

Is there a way to insert the rowcount in a 'select into' clause? Example:

select @.@.rowcount as id,* into mytable_with_rowcount from mytable

If mytable has 100 records then I want an id column in 'mytable_with_rowcount' and have the id column numbered 1 - 100. I know this can be done in a loop, but can it be done in the select w/out doing a loop?

Thanks,

Phil

if its sql server 2005 you can use Ranking Function to number the row and then insert to table. Read about Ranking Functions in BOL.

If its 2000, then you can create a table with ID as indentity column and then insert the rows to that table.

http://www.sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk

http://www.sql-server-performance.com/ak_ranking_functions.asp

Madhu

|||Very nice. Thanks.

rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

rowcount help

i'm trying to get total rows found by query that uses top clause...
for example:
select top 10 myTable.* from myTable where myTable.number > 200
let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.
is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().
tnx...> is there any way to get total row count, without affecting the TOP
> clause?
One method is with a subquery:
SELECT TOP 10
myTable.*,
(SELECT COUNT(*)
FROM myTable
WHERE myTable.number > 200) AS TotalRows
FROM myTable
WHERE myTable.number > 200
--
Hope this helps.
Dan Guzman
SQL Server MVP
"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128944.554703.296710@.38g2000cwa.googlegroups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...
>|||well, it helped...
but i'm worried about the execution time when using full-text search...
anyway, thanx for your help...|||You could select the entire set into a temp table or table variable.
Then, get the count of the temp table, then select the number you want.
e.g.
declare @.tAllRows as table(...)
insert into @.tAllRows
select ... from myTable where myTable.number>200
declare @.iTotalCount int
set @.iTotalCount = @.@.rowcount
select top 10 myTable.*, @.@.rowcount from @.tAllRows
While I usually try to avoid temp tables and table variables,
_sometimes_ they help a lot with performance. Benchmarking would be a
good idea, though, as my approach could be a disaster.
Also, are you paging based on an identity field? If so, you might not
get the results you expect when the numbers are not continuous.|||err...
select top 10 myTable.*, @.iTotalCount from @.tAllRows
my bad...

rowcount help

i'm trying to get total rows found by query that uses top clause...

for example:
select top 10 myTable.* from myTable where myTable.number > 200

let's say there are 13 rows matching that condition, and by using
@.@.rowcount my result would be: 10.

is there any way to get total row count, without affecting the TOP
clause? i believe that the mysql equivalent would be
SQL_CALC_FOUND_ROWS().

tnx...I answered this question in microsoft.public.sqlserver.server.

It's bad etiquette to post the same question independently to multiple
groups because that causes unnecessary duplication of effort by persons
trying to help. If you have a question appropriate for multiple groups,
specify all groups in a single post so that all responses appear in all
groups.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"D.B." <dejan.bukovic@.gmail.com> wrote in message
news:1148128056.678229.59640@.i39g2000cwa.googlegro ups.com...
> i'm trying to get total rows found by query that uses top clause...
> for example:
> select top 10 myTable.* from myTable where myTable.number > 200
>
> let's say there are 13 rows matching that condition, and by using
> @.@.rowcount my result would be: 10.
>
> is there any way to get total row count, without affecting the TOP
> clause? i believe that the mysql equivalent would be
> SQL_CALC_FOUND_ROWS().
>
> tnx...sql

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

You could use dynamic sql (execute a string using EXEC or sp_executesql).|||Thanks

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

There are several options:

1. Use dynamic SQL to generate the entire SELECT statement

2. Use CASE expression in the ORDER BY clause like:

ORDER BY case @.SortColumn when 1 then col1 end,

case @.SortColumn when 2 then col2 end

3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.

There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.

|||Thank you so much

Row_Number function in WHERE clause

What is the reason that you cannot use the results of the ROW_NUMBER function in a WHERE clause? I can achieve the results that I want by using a derived table, but I was just looking for the exact reason.

I have my speculations that it is because the results of ROW_NUMBER are applied after the rows are selected and filtered, but I'd like something definitive.

Thanks a bunch in advance.

That would be the exact reason. The ROW_NUMBER function numbers output rows, so if you used it in the WHERE it would have to ignore rows that would not meet the criteria to be output.

Otherwise if it applied at the FROM level, there might be gaps in the sequence.

|||Louis,

Thank you very much for the answer.

Row Order on View Results

When I run a view on SQL 2005 the resulting rows are not in order, even if the SQL statement defining the view includes an order by clause.

Within the Microsoft SQL Manager Studio (SQL 2005), when the view is opened, the rows are in no specific order.

Records viewed remotely via ADO likewise are not displayed in order. Neither are records viewed via ODBC.

Interestingly, when opened in modify mode within the Microsoft SQL Manager Studio (SQL 2005), the view does display the records according to the ORDER BY clause.

On the other hand, the same view on SQL 2000 produces result sets organized according to the ORDER BY clause. This is true whether the view is opened normally or in design mode.

And records viewed remotely via ADO are displayed in order, as are records viewed via ODBC.

I find this disappointing and a stumbling block in moving databases out of SQL 2000 and into SQL 2005.

The Database that I used was one pulled into a SQL 2005 64 bit server out of a back up made by a SQL 2000 server of a SQL 2000 database.

In general it's not recommended to include an ORDER BY clause in a view. A view should define a new relation of attributes derived from existing attributes in the datamodel. A query using the view should apply an order by on the data represented by the view to produce an ordered resultset.

row filter where clause (busy db performance hit?)

Hello.
I need to setup a filter for a really busy db
that we are replicating. I was wondering what
type of filter to use. I am afraid that the
where clause of a row filter will create a major
performance hit. I was looking into dts and
Transformable Subscriptions but am not real clear
if that would be better.
any advice would be appreciated.
thanks,
-comb
Would this be a static filter or a dynamic filter, ie where store_id=2
(static) or where store_id=hostname() (dynamic)?
There is more processing involved in using filters. The impact may vary
widely.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c0e66c5405f97bc9896ef@.news.newsreader.co m...
> Hello.
> I need to setup a filter for a really busy db
> that we are replicating. I was wondering what
> type of filter to use. I am afraid that the
> where clause of a row filter will create a major
> performance hit. I was looking into dts and
> Transformable Subscriptions but am not real clear
> if that would be better.
> any advice would be appreciated.
> thanks,
> -comb
|||In article <OmLTYxk0EHA.1932
@.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
says...
> Would this be a static filter or a dynamic filter, ie where store_id=2
> (static) or where store_id=hostname() (dynamic)?
> There is more processing involved in using filters. The impact may vary
> widely.
>
it's static. where fkstoreid in (5001,5002)
like that.
-comb
|||is fkstoreid indexed?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c0e87f9669ff2889896f0@.news.newsreader.co m...
> In article <OmLTYxk0EHA.1932
> @.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
> says...
> it's static. where fkstoreid in (5001,5002)
> like that.
> -comb
|||In article <#qRjNSl0EHA.3072
@.TK2MSFTNGP11.phx.gbl>, hilary.cotter@.gmail.com
says...
> is fkstoreid indexed?
>
yes
|||This filter probably is optimal.
You might want to look at replicating the execution of stored procedures -
this can lead to radical performance increases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c0ea5cf51e7c2e59896f1@.news.newsreader.co m...
> In article <#qRjNSl0EHA.3072
> @.TK2MSFTNGP11.phx.gbl>, hilary.cotter@.gmail.com
> says...
> yes
|||In article <OP2$6Gv0EHA.3368
@.TK2MSFTNGP10.phx.gbl>, hilary.cotter@.gmail.com
says...
> This filter probably is optimal.
> You might want to look at replicating the execution of stored procedures -
> this can lead to radical performance increases.
>
thanks,
-comb