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.
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).
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.
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.
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.
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.
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.
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.
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.
This article looks at the main differences between the datetime2 and datetimeoffset data types in SQL Server.
Both data types are used for storing date and time values. Both are very similar, but with one key difference; the datetimeoffset stores the time zone offset.
This also results in datetimeoffset using more storage space than datetime2, so you would only use datetimeoffset if you need the time zone offset.