Tag: how to

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

Posted on by Ian

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.

Continue reading →

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

Posted on by Ian

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.

Continue reading →

How to Return a List of Data Types in SQL Server (T-SQL)

Posted on by Ian

If you ever need to get a list of data types in SQL Server, you can use one of the system views to do just that.

In particular, you can use the sys.types system catalog view. This view returns all system-supplied and user-defined data types defined in the database. If you’re using SQL Server 2000 sys.systypes should do the trick.

Continue reading →

How to Format Negative Values with Brackets in SQL Server (T-SQL)

Posted on by Ian

Here’s a quick way to add brackets around negative numbers in SQL Server when using the FORMAT() function.

The goal here is that brackets are only added to negative values. No brackets are added to positive values or zeros. Also, the brackets replace any minus sign that would otherwise be displayed (in other words, no minus sign is displayed when the brackets are used).

Although formatting is often best left to the presentation layer, there may be cases that dictate a T-SQL solution in SQL Server. In such cases, hopefully this article helps.

Continue reading →

How to Apply Conditional Formatting to a Number in SQL Server using FORMAT()

Posted on by Ian

Perhaps one of the lesser-known features of the FORMAT() function in SQL Server is one that enables you to apply conditional formatting to a number.

It’s more of a .NET feature than it is a SQL Server (or T-SQL) feature, but SQL Server/T-SQL supports it all the same, allowing you to take full advantage of the ability to apply conditional formatting to numbers.

It all comes down to the format string you pass to the FORMAT() function.

You can pass a format string that specifies how the number should be formatted, depending on whether it’s positive, negative, or zero.

Continue reading →

How to Specify the Invariant Culture when using FORMAT() in SQL Server

Posted on by Ian

In SQL Server, you can use the FORMAT() function to format date/time and number values as strings. In particular, the function provides “locale-aware” formatting, and the function accepts a “culture” argument, which allows you to specify a culture or language to use for the actual format. For example, you can pass en-us to ensure the results are formatted in US English format.

The culture argument is optional, so if you don’t provide it, the output will be determined by the language of the current session.

The FORMAT() function accepts any culture supported by the .NET Framework as an argument (you’re not limited to the languages explicitly supported by SQL Server).

One of the cultures supported by the .NET Framework is the invariant culture. The invariant culture is culture-insensitive. More specifically, this culture is associated with the English language but not with any country/region.

To specify that FORMAT() should output the results using the invariant culture, simply use "iv" for the culture argument (the third argument).

Continue reading →

How to Return the Unix Timestamp in SQL Server (T-SQL)

Posted on by Ian

You might have noticed that SQL Server doesn’t have an equivalent of MySQL‘s UNIX_TIMESTAMP() function.

However, it’s not that difficult to return the Unix timestamp in SQL Server.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is simply the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC). Therefore, in SQL Server we can use a couple of T-SQL functions to return this.

Continue reading →

How to Display a Date in German Format in SQL Server (T-SQL)

Posted on by Ian

When formatting a date using the FORMAT() function in SQL Server, the date will be formatted according to the language of your local session. However, you can override this by specifying a culture to use, or using a custom date format.

This article demonstrates how to explicitly specify a German date format by using the optional “culture” argument of the FORMAT() function. It also demonstrates how to use your own custom date format if that is more desirable.

Continue reading →

How to Display a Date in British Format in SQL Server (T-SQL)

Posted on by Ian

This article demonstrates how to explicitly format a date in Great Britain English format when using the T-SQL FORMAT() function in SQL Server.

You may or may not need to use this argument, depending on the language of your local session. However, here’s how to explicitly specify Great Britain English date format.

Continue reading →