{"id":6063,"date":"2019-09-24T21:35:22","date_gmt":"2019-09-25T04:35:22","guid":{"rendered":"https:\/\/database.guide\/?p=6063"},"modified":"2019-11-19T22:57:03","modified_gmt":"2019-11-20T05:57:03","slug":"how-count_big-works-in-sql-server","status":"publish","type":"post","link":"https:\/\/database.guide\/how-count_big-works-in-sql-server\/","title":{"rendered":"How COUNT_BIG() Works in SQL Server"},"content":{"rendered":"

In SQL Server<\/a>, the COUNT_BIG()<\/code> function returns the number of items found in a group. You can use it to find out how many rows are in a table or result set.<\/p>\n

This function works similar to the COUNT()<\/code><\/a> function. The difference is that COUNT()<\/code> returns its result as an int<\/strong>, whereas COUNT_BIG()<\/code> returns its result as a bigint<\/strong>. <\/p>\n

Therefore COUNT_BIG()<\/code> could come in handy if you expect your result set to have a very large number of rows (i.e. larger than 2,147,483,647). <\/p>\n

<\/p>\n

Syntax<\/h2>\n

The syntax goes like this:<\/p>\n

\r\n-- Aggregation Function Syntax  \r\nCOUNT_BIG ( { [ [ ALL | DISTINCT ] expression ] | * } )  \r\n  \r\n-- Analytic Function Syntax  \r\nCOUNT_BIG ( [ ALL ] { expression | * } ) OVER ( [  ] )\r\n<\/pre>\n
ALL<\/code> applies the aggregate function to all values. This is the default value.<\/p>\n
DISTINCT<\/code> specifies that the function returns the number of unique nonnull values.<\/p>\n
expression<\/var><\/code> is an expression of any type. Aggregate functions and subqueries are not supported in the expression.<\/p>\n
*<\/code> specifies that all rows should be counted and returned, including duplicate rows, and rows that contain null values. COUNT(*)<\/code> takes no parameters and does not support the use of DISTINCT<\/code>. It also doesn’t require an expression<\/var> parameter (because it doesn’t use information about any particular column).<\/p>\n
OVER ( [ <partition_by_clause> ] <\/code> divides the result set produced by the FROM<\/code> clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.<\/p>\n
Example 1 – Basic Usage<\/h2>\n
Here’s a basic example showing how this function works:<\/p>\n
\r\nUSE WideWorldImportersDW;\r\nSELECT COUNT_BIG(*) AS 'Row Count' \r\nFROM Fact.[Order];\r\n<\/pre>\n
Result:<\/p>\n
\r\n+-------------+\r\n| Row Count   |\r\n|-------------|\r\n| 231412      |\r\n+-------------+\r\n<\/pre>\n
In this case there are 231412 rows in the Fact.[Order]<\/samp> table.<\/p>\n
In this case, I could’ve used COUNT()<\/code> to return the same result, because the row count is small enough for an int<\/strong> to handle.<\/p>\n
Example 2 – A Larger Result Set<\/h2>\n
The real benefit of using COUNT_BIG()<\/code> is when your result set is much larger than the previous example.<\/p>\n
Example:<\/p>\n
\r\nSELECT COUNT_BIG(*) AS 'Row Count' \r\nFROM ReallyBigTable;\r\n<\/pre>\n
Result:<\/p>\n
\r\n+-----------------+\r\n| Row Count       |\r\n|-----------------|\r\n| 9147483648      |\r\n+-----------------+\r\n<\/pre>\n
In this case, the row count is so big that an int<\/strong> wouldn’t be able to handle it. Fortunately we can use COUNT_BIG()<\/code>, because it returns its result as a bigint<\/strong>.<\/p>\n
More Examples<\/h2>\n
For more examples, see How COUNT()<\/code> Works in SQL Server<\/a>. That article provides more examples than is listed here, all of which are also applicable to COUNT_BIG()<\/code>.<\/p>\n
An Alternative: APPROX_COUNT_DISTINCT()<\/h2>\n
If you’re working with very large data sets, you might consider using APPROX_COUNT_DISTINCT()<\/code><\/a> instead of COUNT_BIG(DISTINCT )<\/code> in some cases. <\/p>\n
APPROX_COUNT_DISTINCT()<\/code> returns an approximate value, rather than a precise value. However, it’s designed to be much more responsive than COUNT_BIG()<\/code>, so it could be useful for times that responsiveness is more important than precision.<\/p>\n
It’s designed to return unique, non-null values, so it would only be relevant for times where you would normally be using the DISTINCT<\/code> clause with COUNT_BIG()<\/code>.<\/p>\n
Also note that, at the time of writing APPROX_COUNT_DISTINCT()<\/code> is in public preview status.<\/p>\n","protected":false},"excerpt":{"rendered":"
In SQL Server, the COUNT_BIG() function returns the number of items found in a group. You can use it to find out how many rows are in a table or result set. This function works similar to the COUNT() function. The difference is that COUNT() returns its result as an int, whereas COUNT_BIG() returns its … Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[93,77,61],"class_list":["post-6063","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-functions","tag-mssql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/6063","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=6063"}],"version-history":[{"count":7,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/6063\/revisions"}],"predecessor-version":[{"id":6124,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/6063\/revisions\/6124"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=6063"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=6063"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=6063"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}