How To Get Table Row Counts Quickly and Easily

Generally we are used SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take more time for the large table. 

The alternate way to determine the total row count in a table. 

1. we can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, we can use the following select statement instead of SELECT COUNT(*): 

Query 1:
 
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 

In the similar way we can find the all table row counts in a current database by using the following query

Query 2:
 

SELECT so.NAME,si.rowcnt FROM sysindexes AS si  

INNER JOIN sysobjects AS so ON si.id = so.id  

WHERE si.indid < 2 AND OBJECTPROPERTY(so.id, 'IsMSShipped') = 0 
ORDER BY so.NAME

Note:

Remove is_ms_shipped = 0 check to include system objects 
i.index_id < 2 indicates clustered index (1) or hash table (0) 

If we are using SQL 2005 or 2008 querying sysindexes will still work but Microsoft advises that sysindexes may be removed in a future version of SQL Server so as a good practice we should use the DMVs (Dynamic Management Views For more details Click Here) instead, like so:

Query 3:
 
Row Count Using DMV's:
  

SELECT o.name,ddps.row_count FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 AND o.is_ms_shipped = 0 
ORDER BY o.NAME

 

To Get only Date from datetime in Sql server

Unfortunately, In Sql server there is no such predefined function available. Now we have to achieve that one by creating one simple user defined function like this.

CREATE FUNCTION DateOnly(@DateTime datetime) 
--Returns only the date by removing the time 
RETURNS datetime 
AS 
BEGIN 
 return dateadd(dd,0,datediff(dd,0,@DateTime)) 
END

How to Usage this function in queries:

For this i will create one table with following columns. Those are
--Create Table with one column with datetime data type
create table TestTable(DateInfo datetime)

After that i will insert one record in the similar way

insert into TestTable(DateInfo) values(getdate())

--Normal Query
select DateInfo from TestTable   --Output is: 2011-03-29 11:50:06.487

--To Get the only Date by using above function
select dbo.dateonly(DateInfo) as OnlyDateInfo from TestTable   --Output is :2011-03-29 00:00:00.000