I previously explained how to install SQL Server on a Mac via a Docker container. When I wrote that, SQL Server 2017 was the latest version of SQL Server, and it had just been made available for Linux and Docker (which means that you can also install it on MacOS systems).
mssql
COUNT() vs COUNT_BIG() in SQL Server: What’s the Difference?
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).
How COUNT_BIG() Works in SQL Server
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).
How COUNT() Works in SQL Server
In SQL Server, the COUNT() 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.
How NTILE() Works in SQL Server
In SQL Server, the NTILE() function allows you to distribute the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at 1. For each row, NTILE() returns the number of the group to which the row belongs.
You simply provide the number of groups you desire when calling the function.
How ROW_NUMBER() Works in SQL Server
In SQL Server, the ROW_NUMBER() function allows you to number the output of a result set. It returns the sequential number of each row, starting at 1.
If you specify partitions for the result set, each partition causes the numbering to start over again (i.e. the numbering will start at 1 for the first row in each partition).
How RANK() Works in SQL Server
In SQL Server, the RANK() function returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row.
How DENSE_RANK() Works in SQL Server
In SQL Server, the DENSE_RANK() function returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of distinct ranks that come before the row.
This function is similar to RANK(), but without the gaps in the ranking values that can occur with RANK() when ties exist in the result set.
How to Include Results that Tie for Last Place when Using the TOP Clause in SQL Server
When using the TOP clause in a query in SQL Server, you may encounter occasions where two or more results tie for last place. You probably wouldn’t even know when this happens, because the default behavior of TOP is to return no more than the number of rows you specify.
The TOP clause accepts a WITH TIES argument that allows you to specify whether or not to include all results that tie for last place. Rows can tie for last place due to their ORDER BY column containing the same value. Using this argument may therefore result in more rows being returned than you actually specified.
Pagination in SQL Server using OFFSET/FETCH
Pagination is often used in applications where the user can click Previous/Next to navigate the pages that make up the results, or click on a page number to go directly to a specific page.
When running queries in SQL Server, you can paginate the results by using the OFFSET and FETCH arguments of the ORDER BY clause. These arguments were introduced in SQL Server 2012, therefore you can use this technique if you have SQL Server 2012 or higher.
In this context, pagination is where you divide the query results into smaller chunks, each chunk continuing where the previous finished. For example, if a query returns 1000 rows, you could paginate them so that they’re returned in groups of 100. An application can pass the page number and page size to SQL Server, and SQL Server can then use it to return just the data for the requested page.