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.

The Difference

The difference between FILE_IDEX() and FILE_ID() is in the return types.

• FILE_IDEX() returns an int.
• FILE_ID() returns a smallint.

The fact that FILE_IDEX() returns an integer means that it can handle larger file IDs. For example, it can handle full-text catalogs.

The smallint data type can only handle values up to 32,767, whereas an int can handle values up to 2,147,483,647. In SQL Server, the file identification number assigned to full-text catalogs exceeds 32,767, and therefore, FILE_ID() doesn’t support full-text catalogs.

Example

Here’s a quick example to demonstrate the limitations of FILE_ID() when compared to FILE_IDEX():

USE WideWorldImportersDW;
SELECT 
  FILE_IDEX('WWIDW_InMemory_Data_1') AS [FILE_IDEX()],
  FILE_ID('WWIDW_InMemory_Data_1') AS [FILE_ID()];

Result:

+---------------+-------------+
| FILE_IDEX()   | FILE_ID()   |
|---------------+-------------|
| 65537         | NULL        |
+---------------+-------------+

In this case, the file ID exceeds 32,767 and therefore FILE_ID() returns NULL.