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.
Tag: functions
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.
Find Out if a CHECK Constraint is Column-Level or Table-Level in SQL Server (T-SQL Examples)
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.
How to Create a Composite Primary Key in SQL Server (T-SQL Example)
A composite primary key is a primary key consisting of multiple columns. Microsoft usually refers to these as multicolumn primary keys in its documentation.
This article provides an example of creating a composite primary key using Transact-SQL in SQL Server.
How to Create a Composite Foreign Key in SQL Server (T-SQL Example)
A composite foreign key is a foreign key consisting of multiple columns.
This article provides an example of creating a composite foreign key using Transact-SQL in SQL Server.
11 Ways to Retrieve a Primary Key in SQL Server (T-SQL Examples)
There are often many ways to do the same (or similar) thing in SQL Server. Retrieving all primary keys from a database (or even just the primary key for a given table) is a case in point.
This article presents eleven different approaches to retrieving primary key information in SQL Server.
Some of these will be more useful than others, depending on your exact situation.
datetime vs datetimeoffset in SQL Server: What’s the Difference?
This article highlights the main differences between the datetime and datetimeoffset data types in SQL Server.
Both data types are used for storing date and time values. But there are significant differences between the two.
Perhaps the most obvious difference is that the datetimeoffset stores the time zone offset, whereas datetime doesn’t.
Another important difference is that datetimeoffset allows you to specify the precision (up to 7 decimal places). This means that datetimeoffset values can vary in their storage size, depending on the precision being used.
The datetime type on the other hand, has a fixed storage size and precision.