Finding Table is empty or not without using count() function

Question

Post reply

Finding Table is empty or not without using count() function

karthik M

One Orange Chip

Points: 29139
More actions
October 1, 2008 at 3:15 am

#75329

Hi All,
I want to find whether a table is empty or not without using count() function.
Say for example,
Create table Emp
(
Eno int,
Ename varchar(50)
)
Insert into Emp
Select 1,'AAAAA'
union all
Select 2,'AAAAA'
union all
Select 3,'AAAAA'
union all
Select 4,'AAAAA'
union all
Select 5,'AAAAA'
Just I want to know whether Emp table has contain records or not. I dont want to use count(eno) function. Because if the table has more number of rows say for example, 50000000, count(eno) will take some time to do it. As the count(eno) value is not going to be used in anywhere in my code, i dont want to use count() function.
Just i wanted to know whether the table has records or not. Thats my requirement.
Note: Table doesn't have any indexes. It is a flat table.
karthik

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

You don't have to COUNT() all of the rows...

DECLARE @HasRows BIT

SELECT @HasRows = COUNT(*) FROM (SELECT TOP 1 * FROM YourTable) d

SELECT @HasRows

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 2

Thanks Chris for your quick help.

But i got confused.

When i change your code as below

DECLARE @HasRows INT

SELECT @HasRows = COUNT(*) FROM (SELECT TOP 1 * FROM YourTable) d

SELECT @HasRows

Assume yourtable has 10000 records.

SELECT @HasRows is showing 10000 , if i changed it to BIT again, it is showing 1. How ?

Becuase SELECT TOP 1 * FROM YourTable should fetch only one row, so d has only one row.

i.e select count(*) from d should display 1 only. But how it is displaying 10000.

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Karthik, if I run your slight modification of my code as displayed in your post:

DECLARE @HasRows INT

SELECT @HasRows = COUNT(*) FROM (SELECT TOP 1 * FROM INVOICES_Monthly) d

SELECT @HasRows

Then the result returned is 1.

Post the exact code which you are running which returns a value other than 0 or 1 ("SELECT @HasRows is showing 10000") - I'm sure it won't be too difficult to figure out what's going wrong.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 4

IF EXISTS (select * from Emp) print 'exists'.

This only needs to verify that a row exists in the table, and should only do a single I/O, regardless of how many rows are in the table.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Ian Scarlett (10/1/2008)
IF EXISTS (select * from Emp) print 'exists'.
This only needs to verify that a row exists in the table, and should only do a single I/O, regardless of how many rows are in the table.

Nice one Ian, and less numbers in it to confuse the unwary 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 6

Test 1:

DECLARE @HasRows INT

SELECT @HasRows = COUNT(*) FROM (SELECT TOP 1 * FROM mf_wk..aac_mf_ob) d

SELECT @HasRows

mf_wk..aac_mf_ob = No of Rows = 9064

SELECT @HasRows = 9064

Test 2:

DECLARE @HasRows BIT

SELECT @HasRows = COUNT(*) FROM (SELECT TOP 1 * FROM mf_wk..aac_mf_ob) d

SELECT @HasRows

SELECT @HasRows = 1

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

[font="Courier New"]DROP TABLE #Temp

CREATE TABLE #Temp (SomeColumn INT)

DECLARE @HasRowsINT INT, @HasRowsBIT BIT

SELECT @HasRowsINT = COUNT(*) FROM (SELECT TOP 1 * FROM #Temp) d

SELECT @HasRowsBIT = COUNT(*) FROM (SELECT TOP 1 * FROM #Temp) d

SELECT @HasRowsINT, @HasRowsBIT

-- 0, 0

INSERT INTO #Temp SELECT number FROM Numbers

SELECT @HasRowsINT = COUNT(*) FROM (SELECT TOP 1 * FROM #Temp) d

SELECT @HasRowsBIT = COUNT(*) FROM (SELECT TOP 1 * FROM #Temp) d

SELECT @HasRowsINT, @HasRowsBIT

-- 1, 1

SELECT COUNT(*) FROM #Temp

-- 1000000[/font]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Chris,

Thanks a lot for your prompt reply with example. It is working fine now.

Can you tell me why you used BIT instead of INT ? Becuase both of them are giving same result.

is there any specific reason ?

karthik

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

karthikeyan (10/1/2008)
Chris,
Thanks a lot for your prompt reply with example. It is working fine now.
Can you tell me why you used BIT instead of INT ? Becuase both of them are giving same result.
is there any specific reason ?

Yes - only one of two possible values can be returned. These are 0 and 1 (as int), which is implicitly cast as bit.

The result (in this case) depends upon the query, not on the datatype of the receiving variable.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 10

Off Topic:

Chris, how are you getting your SQL code color coded rather than all Black and White?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Hi Garadin, use this:

http://extras.sqlservercentral.com/prettifier/prettifier.aspx

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Garadin One Orange Chip Points: 29613 More actions · Answer 12

Beautiful, thank you sir.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]