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

Posted on by Ian

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 →

How to Delete Files in SQL Server 2019

Posted on by Ian

In SQL Server 2019, you can use the sys.xp_delete_files stored procedure to delete a file on the file system.

This stored procedure was introduced in SQL Server 2019, and it can be used in conjunction with sys.xp_copy_file and sys.xp_copy_files (both of which were also introduced in SQL Server 2019), which enable you to copy files.

Prior to SQL Server 2019, you would need to use xp_cmdshell, which spawns a Windows command shell and passes in a string for execution. The new stored procedures introduced in SQL Server 2019 allow you to copy and delete files without relying on xp_cmdshell.

Continue reading →

New Way to Copy Files in SQL Server 2019

Posted on by Ian

Two new stored procedures introduced in SQL Server 2019 are sys.xp_copy_file and sys.xp_copy_files, which enable you to copy files.

Prior to SQL Server 2019, you would need to use xp_cmdshell, which spawns a Windows command shell and passes in a string for execution. The new stored procedures introduced in SQL Server 2019 allow you to copy files without relying on xp_cmdshell.

Continue reading →

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

Posted on by Ian

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 →