Using Literal Values in SQL Server

A literal value is a fixed value written directly into a Transact-SQL statement, a string, a number, a date, rather than a variable or a column reference. Every INSERT, UPDATE, and DELETE example earlier in this module has used literal values: inserting a row with specific column values, setting a column to a specific new value, filtering a WHERE clause down to a specific row. What hasn't been covered yet is the actual syntax rules for writing a literal correctly, which is what this lesson focuses on.

Enclose string values in single quotes:

'Mann'

To include an apostrophe inside the string itself, double it:

'O''Brien'

SQL Server reads the doubled quote as a single literal apostrophe character, not as the end of the string.

For Unicode text (NVARCHAR/NCHAR columns), prefix the string with N:

N'Widget'

Without the N prefix, a string literal is interpreted using the database's non-Unicode code page, which can silently corrupt characters outside that code page's range. If a column is Unicode, use the N prefix as a habit, not just when you happen to need an unusual character.

Dates are also written as quoted strings, but the format you choose matters more than it looks like it should:

'2025-08-03'

The ISO 8601 style shown above, YYYY-MM-DD, is unambiguous regardless of the server's language or DATEFORMAT setting. A format like '08/03/2025' is not: depending on how the server is configured, that could be interpreted as August 3rd or March 8th. Mixing up MM/DD/YYYY and DD/MM/YYYY this way is a classic source of silent, hard-to-notice data errors, since the statement doesn't fail, it just inserts or matches the wrong date. Preferring the ISO format removes the ambiguity entirely.

Numbers are written without quotes:

101
19.99
1.5E2

1.5E2 is scientific notation for 150. Plain integers and decimals need no special formatting, just make sure the literal's precision and scale are compatible with the target column.

A few less common but still-current literal formats:

• Binary values use a 0x prefix: 0xAE.
• Bit values are written as 0 or 1.
• Money values can be written with or without a currency symbol: $12.34 or plain 12.34 both work for a MONEY or SMALLMONEY column.

A literal has to be compatible with the data type of the column it's going into. SQL Server will implicitly convert a literal where it reasonably can, a numeric string into an integer column, for instance, but an outright type mismatch still fails. When in doubt, write the literal in the format that matches the column's actual type rather than relying on implicit conversion to bridge a larger gap.

Before running an UPDATE or DELETE built around a literal value in the WHERE clause, it's worth running the same condition as a SELECT first, to confirm exactly which rows it matches:

SELECT * FROM Employees WHERE EmployeeID = 101;

If that returns the row or rows you expect, converting it to an UPDATE or DELETE is a small, low-risk step. This is the same defensive habit covered in the previous lesson on DELETE, and it applies just as well here.

As a related point already raised earlier in this module: literal values are fine for examples, ad hoc queries, and one-off fixes, but application code should generally use parameterized queries rather than building statements out of literals directly, both for SQL injection safety and because SQL Server can reuse a cached execution plan across calls with different parameter values in a way it can't for a string of literals that changes every time.

A short recap before the next lesson:

• Strings and dates are quoted with single quotes; numbers are not. A doubled apostrophe inside a string represents a literal apostrophe character, not the end of the string.
• Unicode strings need the N prefix; without it, characters outside the database's non-Unicode code page can be silently corrupted rather than causing an obvious error.
• Date literals in ISO 8601 format (YYYY-MM-DD) avoid the ambiguity that slash-separated formats carry across different server configurations.
• Binary, bit, and money values each have their own literal conventions: 0x for binary, 0/1 for bit, and an optional currency symbol for money.
• Previewing a WHERE condition with SELECT before converting it to an UPDATE or DELETE remains good practice regardless of how simple the literal looks.

In the next lesson, you will explore advanced techniques for using literal values, such as combining them with expressions and variables.