Use NEWSEQUENTIALID() to Create an Incrementing GUID in SQL Server

In SQL Server, you can use the NEWSEQUENTIALID() function to create incremental unique values.

It creates a GUID (Globally Unique IDentifier) that is greater than any GUID previously generated by this function on a specified computer since the operating system was started. After restarting the operating system, the GUID can start again from a lower range, but is still globally unique.

The NEWSEQUENTIALID() function can only be used with DEFAULT constraints on table columns of type uniqueidentifier. Therefore, you can’t just run a query like SELECT NEWSEQUENTIALID() and expect it to work (but you can do that with the NEWID() function).

Continue reading

Use NEWID() to Create a Unique Value in SQL Server

In SQL Server, you can use the NEWID() function to create a unique value.

More specifically, it’s an RFC4122-compliant function that creates a unique value of type uniqueidentifier.

The value that NEWID() produces is a randomly generated 16-byte GUID (Globally Unique IDentifier). This is also known as a UUID (Universally Unique IDentifier).

Continue reading

Non-Number Characters that Return Positive when using ISNUMERIC() in SQL Server

The ISNUMERIC() function in SQL Server enables you to check whether or not an expression is numeric.

However, there may be times where you get results that you didn’t expect. This could happen if you have an expression that contains a character that is not a number, but is still accepted by ISNUMERIC() as being numeric.

There are a bunch of characters that ISNUMERIC() accepts as numeric that you might not have thought of as numeric. These include characters such as plus (+), minus (-), and the various currency symbols. Also, depending on its placement, the letter e could also allow the whole expression to be interpreted as numeric.

Continue reading

FILE_ID() vs FILE_IDEX() in SQL Server: What’s the Difference?

In SQL Server, you can use the FILE_IDEX() function to return the ID of a given database file. You can also use the FILE_ID() function to do the same thing. Both functions serve pretty much the same purpose, so why does T-SQL have both functions?

It appears that FILE_IDEX() is a replacement for FILE_ID(), and it supports a larger range of file IDs. Microsoft now recommends against using FILE_ID(), as it’s in maintenance mode and may be removed in a future version of SQL Server.

So if you’re looking for a quick answer for which function to use, use FILE_IDEX().

But if you’re interested in the difference between these two functions, read on.

Continue reading

Use FILE_IDEX() to Return the ID of a Database File in SQL Server

In SQL Server, you can use the FILE_IDEX() function to return the ID of a given database file.

To do this, pass the logical file name of the database file to the function. This is the name that corresponds to the name column in the sys.master_files catalog view or the sys.database_files catalog view. Those views also contain the file ID, but FILE_NAME() saves you from having to query those views.

Continue reading