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.
Tag: functions
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.
datetime2 vs datetimeoffset in SQL Server: What’s the Difference?
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.
datetime vs smalldatetime in SQL Server: What’s the Difference?
This article explores the main differences between the datetime and smalldatetime data types in SQL Server.
Both data types are used for storing date and time values, however, there are differences between the two. In most cases you’re better off avoiding both types and using datetime2 instead (Microsoft also recommends this). In any case, here’s a comparison of these two data types.
datetime2 vs smalldatetime in SQL Server: What’s the Difference?
This article explores the main differences between the datetime2 and smalldatetime data types in SQL Server.
Both data types are used for storing date and time values, however, there are some important differences between the two. In most cases you’re better off using datetime2 (Microsoft also recommends this), however there might be some scenarios where you need to use smalldatetime.
datetime vs datetime2 in SQL Server: What’s the Difference?
This article explores the main differences between the datetime and datetime2 data types in SQL Server.
If you’re not sure which one to use, use datetime2 (see its advantages below).
Understanding ‘time’ Storage Size in SQL Server
In this article I look at the storage size of the time data type in SQL Server.
In particular, I look at the following:
- Microsoft’s documentation
- Data stored in a variable
- Length in bytes using
DATALENGTH() - Length in bytes using
DATALENGTH()after converting to varbinary
- Length in bytes using
- Data stored in a database
- Length in bytes using
COL_LENGTH() - Length in bytes using
DBCC PAGE()
- Length in bytes using
Understanding ‘datetimeoffset’ Storage Size in SQL Server
In this article I look at how the datetimeoffset data type is stored in SQL Server, and how you can get different reported storage size results, depending on what you’re doing with it.
This is similar to what I did with the datetime2 data type.
In particular, I look at the following:
- Microsoft’s documentation
- Data stored in a variable
- Length in bytes using
DATALENGTH() - Length in bytes using
DATALENGTH()after converting to varbinary
- Length in bytes using
- Data stored in a database
- Length in bytes using
COL_LENGTH() - Length in bytes using
DBCC PAGE()
- Length in bytes using
Understanding ‘datetime2’ Storage Size in SQL Server
In this article I share some observations I’ve had regarding the datetime2 data type’s storage size in SQL Server. Perhaps I will clarify some points about the actual storage size used by this data type when stored in a database.
In particular, I look at the following:
- Microsoft’s documentation
- Data stored in a variable
- Length in bytes using
DATALENGTH() - Length in bytes using
DATALENGTH()after converting to varbinary
- Length in bytes using
- Data stored in a database
- Length in bytes using
COL_LENGTH() - Length in bytes using
DBCC PAGE()
- Length in bytes using
Some of those seem to contradict each other, and you will see two different storage size amounts for the same value, depending on where you look.