COUNT vs COUNT_BIG

As you know COUNT function is used to count number of rows from a table or a derived result set. But did you know that there is another function named COUNT_BIG?

The return type of COUNT_BIG function is BIGINT whereas return type of COUNT is INT. So if you have really huge dataset you can use COUNT_BIG function to avoid overflow error

Another difference is that when you use COUNT function in a view, you can’t index it

Consider the following objects

create table test
(
prod_id int,
qty int,
price decimal(12,2),
sales_date datetime
)

Create this view

create view [dbo].[test_view] with schemabinding
as
select year(sales_date) as year, count(*) as total_volumne from dbo.test
group by year(sales_date)

Create clustered index on year column

create unique clustered index test_index on test_view (year)

You get the following error

Msg 10136, Level 16, State 1, Line 3
Cannot create index on view "tempdb.dbo.test_view" because it uses the aggregate COUNT. Use COUNT_BIG instead.

Now alter the view and use COUNT_BIG function

alter view [dbo].[test_view] with schemabinding
as
select year(sales_date) as year, count_big(*) as total_volumne from dbo.test
group by year(sales_date)

You will be able to create clustered index now

create unique clustered index test_index on test_view (year)

You need to keep this in mind whenever you use use COUNT function in a view

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s