count the row number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

count the row number

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2013-06-11 : 12:38:45

Hi there,

I need count row number in a column. I know I can use @@RowCount but I need run select to make it work. But at the end I need run another select statement to feed the results to dataset in a report. But the dataset takes the first select results. So my question is how can I count the row without using select. Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-11 : 12:46:46

do you mean this?


SELECT COUNT(1) OVER () as RowCount,*
FROM Table1
...
--your actual query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

allan8964
Posting Yak Master

249 Posts

Posted - 2013-06-11 : 13:01:10

I am not following ... not working anyway.
I need a way to count the row without using SELECT.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-11 : 13:06:21

Isn't what you really need a way to count the rows so it does not generate a recordset?

SET NOCOUNT ON;
DECLARE @Rows INT;
SELECT @Rows = COUNT(*) FROM Table1

You could also use the following, which is faster:

SET NOCOUNT ON;
DECLARE @Rows INT;

SELECT @Rows = SUM (row_count) 
FROM sys.dm_db_partition_stats 
where  (index_id=0 or index_id=1)
AND object_name(object_id) = 'Table1'

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-06-11 : 17:03:52

quote:
Originally posted by allan8964

<snip> So my question is how can I count the row without using select. Thanks in advance.

You can't

allan8964
Posting Yak Master

249 Posts

Posted - 2013-06-11 : 21:47:03

Thanks James, the last piece works!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-12 : 01:03:40

quote:
Originally posted by allan8964

Thanks James, the last piece works!!!

that still uses a SELECT isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

neeraj_sharma
Starting Member

15 Posts

Posted - 2013-06-12 : 03:04:50

quote:
Originally posted by visakh16

quote:
Originally posted by allan8964

Thanks James, the last piece works!!!

that still uses a SELECT isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Haha i this Allan just wanted to ask i want it in a variable,
noting wrong with your Answer you always does good work here.

BLOG:
http://www.tutorialsqlserver.com

[url][/url][url][/url]

@nrj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources