Counting rows in a table quickly

Posted on by madhivanan

One of my friends asked me if there is a way to find total rows in a table without using COUT(*) as it takes very long time for big table. Well COUNT(*) is the most accurate way of counting rows from a table.

However you can also use any of these methods to find the total rows if you want to know the approximate count very quickly.

1 Use sp_spaceused system stored procedure

EXEC sp_spaceused mytable

2 Use sys.partitions system view


select rows from sys.partitions
where object_id=object_id('mytable') and index_id<1

3 Use sys.dm_db_partition_stats  system view


select row_count from sys.dm_db_partition_stats
where object_id=object_id('mytable') and index_id<1

All of the above will get you the count quickly.

NOTE : The count is approximate and to have the accurate count you need to run

DBCC UPDATEUSAGE('mydatabase','mytable')

which will reports and corrects the row count inaccuracies. After running this you can run the above methods to get correct count

 

 

Different methods to know size of the database files

Posted on by madhivanan

A database basically contains two type of files such as data file and log file. If you want to know the size of these files, you can use any one of the methods shown below

1 Use System stored procedure sp_spaceused

EXEC sp_helpdb tempdb

Look at the second result set and see the column named size
size

2 Use system table sys.sysfiles

select name,filename,size*8 as size_in_kb from tempdb.sys.sysfiles

The result is

size2

3 Use system view sys.database_files

SELECT name, physical_name as filename,size*8 as size_in_mb FROM tempdb.sys.database_files ;

The result is

size2

 

Alias name – IN vs INNER JOIN

Posted on by madhivanan

If you want to join two tables and get data from only one of the tables you can use either IN operator or INNER JOIN

Let us consider the following data

create table #item_master (id int, name varchar(100))
create table #sales_details (sales_id int identity(1,1), item_id int, amount decimal(12,2))

insert into #item_master(id,name)
select 1,'Helmet' union all
select 2,'Mobile' union all
select 3,'Cycle' union all
select 4,'Purse' union all
select 5,'Shirt'

 

insert into #sales_details(item_id,amount)
select 1,2000 union all
select 3,1240 union all
select 4,5660

Suppose you want to get the data from #item_master for the items that are available in #sales_details table, you can use IN operator or INNER JOIN as shown below

select * from #item_master as t1 where id in (select item_id from #sales_details)

select t1.* from #item_master as t1 inner join #sales_details as t2 on t1.id =t2.item_id


You get the following result for the above two queries

id name
----------- -------------
1 Helmet
3 Cycle
4 Purse

Now see what happens when you run the following code

select * from #item_master as t1 where id in (select id from #sales_details)

You get all data from #item_master although there is no column named id in #sales_details table it is because that if there is no alias name used and if the column is not available in #sales_details table, id is taken from #item_master table. If you look at the execution plan Predicate says it does take id from #item_master

execution_plan

So how do we solve this? Use table alias name as shown below

select * from #item_master as t1 where id in (select t2.id from #sales_details as t2 )

Now the error is

Msg 207, Level 16, State 1, Line 1
Invalid column name ‘id’.

So it is always better to use table alias when you use IN operator to avoid unexpected result

Note : This is not a problem in INNER JOIN as you can not avoid using Alias name unlike IN operator