sqlrx logo
SQLRx Tip of the Month
 

September 2013
______________________________________________________________________________
wrench  SQL Server Development:  The COUNT_BIG function is used much like the COUNT function to return the number of items in a group. COUNT_BIG returns a bigint data type and has a maximum value returned of 9,223,372,036,854,775,807. COUNT returns an int data type and has a maximum value returned of 2,147,483,647. COUNT_BIG is useful for rowcounts of really large tables like those commonly found in data warehouses.

 

Syntax: COUNT_BIG ( { [ ALL | DISTINCT ] expression } | * )

  • COUNT_BIG(*) returns the number of items in a group including NULL values and duplicates.
  • COUNT_BIG(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values. COUNT_BIG(ALL *) will cause a syntax error.
  • COUNT_BIG(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values. COUNT_BIG(DISTINCT *) will cause a syntax error.

Here is a simple example of each:

   

   

 

 

 

 

 

  
  
      
______________________________________________________________________________
As always, don't forget to regularly monitor the 11 VitalSigns  used by SQLRx to debug performance bottlenecks.

If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month, email it to us!  If you missed any of our previous tips, you can view them here.

For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at [email protected].


Sincerely,

 

Lori Brown | SQLRx Senior Consultant | Integrated Services, Inc.
4144 N. Central Expwy, Suite 430  |  Dallas, TX  75204
Phone: 214.526.7680 x 113 | 800.85.SQLRx

 

 

Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit

 

25 Years of Credibility & Trust

 

Join our email list          Check out our events          SQLRx Downloads