Tag: functions

COUNT() vs COUNT_BIG() in SQL Server: What’s the Difference?

Posted on by Ian

In SQL Server, the COUNT_BIG() function and the COUNT() do essentially the same thing: return the number of items found in a group. Basically, you can use these functions to find out how many rows are in a table or result set.

In many cases, you’ll be able to choose whichever one you prefer. However, there’s a difference between these two functions that might dictate that you to use one over the other.

The difference is that COUNT() returns its result as an int, whereas COUNT_BIG() returns its result as a bigint.

In other words, you’ll need to use COUNT_BIG() if you expect its results to be larger than 2,147,483,647 (i.e. if the query returns more than 2,147,483,647 rows).

Continue reading →

How COUNT_BIG() Works in SQL Server

Posted on by Ian

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 result as a bigint.

Therefore COUNT_BIG() 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).

Continue reading →

Find Out if a CHECK Constraint is Column-Level or Table-Level in SQL Server (T-SQL Examples)

Posted on by Ian

When you create a CHECK constraint in SQL Server, you might not even think about whether it’s a table-level constraint or a column-level constraint.

A table-level CHECK constraint applies to the table, whereas a column-level constraint applies to a specific column. With a table-level CHECK constraint, it’s the row that is checked when it checks the data. With a column-level CHECK constraint, it’s the specific column that is checked.

Generally you’ll know whether or not the constraint you’re creating is a table-level or column-level constraint by the definition you give it. If only one column is being checked in the expression, it will be a column-level constraint. Otherwise it will be a table-level constraint.

But how do you know if your existing constraints are column-level or table-level?

You can run any of the code examples below to determine whether your existing constraints are column-level or table-level. These retrieve all CHECK constraints for the current database, but you can always use a WHERE clause to narrow it down to a specific constraint.

Continue reading →