SELECT COUNT(*) and SELECT * - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SELECT COUNT(*) and SELECT *

Author

Topic

ST0320
Starting Member

6 Posts

Posted - 2005-10-07 : 16:09:30

I have a query as below

SELECT COUNT(*) FROM EMP_TBL WHERE EMP_NAME LIKE 'KE%'

It fetched me 46 rows.

SELECT * FROM EMP_TBL WHERE EMP_NAME LIKE 'KE%'

It fetched me 47 rows.

Both queries are similar but still I get a mismatch.

Can anybody let me know if this is the usual behavior of DB2 and if it is, then why is it so?

Thanks
Sandy

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-07 : 16:19:32

Well, this is a SQL Server site, but no.

There are no miracles, a row was added before you ran your next statement.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

ST0320
Starting Member

6 Posts

Posted - 2005-10-07 : 16:28:20

No operations were performed on the tables.
I executed these two statements over and over again. And the result was the same. 46 rows with SELECT COUNT(*) and 47 with SELECT *

The complete query looks like this. I was just substituting * with count(*)

SELECT *
FROM MSRN.RULE_PARM RP
WHERE RP.RULE_SPEC_ID IN
(
SELECT RS.RULE_SPEC_ID
FROM MSRN.RULE_SPEC RS
WHERE UPPER(RS.TYPE_NM) = 'TEMPLATERULE'
)
;

Kristen
Test

22859 Posts

Posted - 2005-10-08 : 05:42:23

Is it possible SELECT COUNT(*) is choosing a column to "count" which has NULL values?

Just a wild guess ... what happens if you do

SELECT COUNT(MyPrimaryKeyColumn)

insert of COUNT(*) ?

If this is right I'm gonna eat my hat!

Kristen

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-10-08 : 06:20:47

>> Is it possible SELECT COUNT(*) is choosing a column to "count" which has NULL values?

That is my pick... Man those thing SUCK!

DavidM

A front-end is something that tries to violate a back-end.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-09 : 21:51:43

Ok, i'm really curious how this went

Did Kristen eat his hat?

If he's right then this changes our notion about count(*) being faster than count(fieldname)

accuracy is more important than speed

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2005-10-10 : 01:25:44

"this changes our notion ..."

... on DB2

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 02:09:48

Strange
Can you post table structure?

Test


Declare @t table(i int primary key , n varchar(10) null)
insert into @t values(1,'test')
insert into @t values(2,'test1')
insert into @t values(3,'testing')
insert into @t values(4,'sqltesting')
insert into @t values(5,'ttesting')
insert into @t values(6,null)

select count(*) from @t where n like 'test%'
select count(i) from @t where n like 'test%' -- Count(PrimaryKey)
select * from @t where n like 'test%'

I didnt see anything wrong

Madhivanan

Failing to plan is Planning to fail

anuj164
Starting Member

49 Posts

Posted - 2005-10-11 : 16:23:33

I tried with couple of example, looks fine to me! can you post your table structure ,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources