The QUOTENAME function in SQL Server is used to safely enclose identifiers, such as table names, column names, and other object names, by adding the appropriate delimiters. This helps prevent SQL injection attacks and allows the use of special characters or reserved keywords in identifiers by ensuring they are properly quoted.
Syntax
QUOTENAME('identifier' [, 'quote_character'])
- identifier: The name of the database object that you want to quote.
- quote_character: This is optional. It allows you to specify the quote character. By default, it is [ for opening and ] for closing, but you can also use double quotes (").
Usage Examples
1. Basic Usage:
If you have a table named My Table (with a space), you can use QUOTENAME to quote it:
SELECT * FROM QUOTENAME('My Table');
This will safely return the table name as [My Table].
2. Using Special Characters:
If your identifier contains special characters (like @, ., or -), QUOTENAME will also handle them:
SELECT * FROM QUOTENAME('my-table@2021');
This returns [my-table@2021].
3. Using Custom Quotes:
If you want to use double quotes instead of square brackets:
SELECT * FROM QUOTENAME('My Table', '"');
This will return "My Table".
4. Combining with Dynamic SQL:
It's common to use QUOTENAME when creating dynamic SQL statements to avoid issues with special characters or reserved keywords. For example:
DECLARE @tableName NVARCHAR(128) = 'My Table';
DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM ' + QUOTENAME(@tableName);
EXEC sp_executesql @sql;
Important Considerations
- Always use QUOTENAME for any dynamic SQL or when using identifiers that might potentially be malformed or contain dangerous characters or reserved keywords.
- Be mindful of the maximum length of identifiers in SQL Server (128 characters for names); QUOTENAME will truncate identifiers longer than this length, but it will still be a good practice to ensure identifiers are trimmed appropriately before passing them to QUOTENAME.
Using QUOTENAME is a best practice in SQL development, ensuring that your SQL statements are secure, valid, and resilient against various issues related to identifier naming conventions.