Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
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 MVPhttp://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 MVPhttp://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 MVPhttp://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