Run the following code
select $ as value
The result is
value
———————
0.00
The question is “Why does it return 0?”
Counting rows in a table quickly
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
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
2 Use system table sys.sysfiles
select name,filename,size*8 as size_in_kb from tempdb.sys.sysfiles
The result is
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
Alias name – IN vs INNER JOIN
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
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
