mssql

How to Create a Primary Key in SQL Server (T-SQL Examples)

by

This article demonstrates how to create a primary key in SQL Server when creating a table using Transact-SQL.

A primary key is one or more columns that have been configured as the unique identifier for a given table. Primary keys can be used to enforce data integrity in the table.

A table can only have one primary key, and primary keys can only be added to columns that are defined as NOT NULL.

This article demonstrates how to create a primary key in a new table (i.e. when creating the table). If you need to create a primary key in an existing table, see How to Add a Primary Key to an Existing Table in SQL Server.

Read more

How to Add a Primary Key to an Existing Table in SQL Server (T-SQL Examples)

by

This article demonstrates how to add a primary key to an existing table in SQL Server using Transact-SQL.

A primary key is a column that has been configured as the unique identifier for a given table.

You would normally create a primary key constraint when you create the table, but you can also add a primary key to an existing table.

Note that a table can only have one primary key. So you can’t add a primary key if the table already has one.

Also primary keys can only be added to columns that are defined as NOT NULL.

Read more

How to Create a User-Defined Data Type Alias in SQL Server using T-SQL

by

In addition to the many data types available in SQL Server, you also have the option of creating your own data type. Some of these are referred to as “user-defined data types”, while others are referred to as “alias data types”.

A user-defined data type is implemented through a class of an assembly in the Microsoft.NET Framework common language runtime (CLR).

An alias data type is based on a SQL Server native system type. In other words, you use an existing data type for the basis of your alias data type.

Having said that, I’ve seen Microsoft use the term “user-defined data type alias” when referring to an alias data type. I’ve also seen it referred to as simply a “data type alias”.

Either way, this article demonstrates how to a create a user-defined data type alias using Transact-SQL.

Read more

datetime vs datetimeoffset in SQL Server: What’s the Difference?

by

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.

Read more

datetime2 vs datetimeoffset in SQL Server: What’s the Difference?

by

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.

Read more

datetime vs smalldatetime in SQL Server: What’s the Difference?

by

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.

Read more

datetime2 vs smalldatetime in SQL Server: What’s the Difference?

by

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.

Read more

Understanding ‘time’ Storage Size in SQL Server

by

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
  • Data stored in a database
    • Length in bytes using COL_LENGTH()
    • Length in bytes using DBCC PAGE()

Read more

Understanding ‘datetimeoffset’ Storage Size in SQL Server

by

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
  • Data stored in a database
    • Length in bytes using COL_LENGTH()
    • Length in bytes using DBCC PAGE()

Read more