Lesson 7	Punctuation
Objective	Describe how to use punctuation in your statements.

SQL Server Statement Punctuation When Inserting Information

When using literal values with your queries, you will receive fewer errors if you adhere to SQL Server's punctuation rules.

Quotations

SQL Server requires quotation marks with some datatypes, but not for others.

Using Literal Values

The following table indicates whether quotes are needed for the literal value of each SQL Server datatype:

Datatype	Quotes needed for literal values
Binary	No
Bit	No
Char(n)	Yes
Datetime	Yes
Decimal(p[,s])	No
Float(n)	No
Int	No
Money	No
Nchar	Yes
Numeric	No
Nvarchar(n)	Yes
Real	No
Rowversion	No (system-generated; not typically supplied as a literal at all)
Smalldatetime	Yes
Smallint	No
Smallmoney	No
Tinyint	No
Uniqueidentifier	Yes
Varbinary(max)	No
Varchar(n)	Yes

A few older datatypes, image, text, and ntext, have been deprecated in SQL Server for many releases, still listed as deprecated as of SQL Server 2025, and are left out of this table in favor of their current replacements: varbinary(max) in place of image, varchar(max) in place of text, and nvarchar(max) in place of ntext. The quote rules for each replacement follow the same pattern as their fixed-length counterparts above; varbinary(max) needs no quotes, varchar(max) and nvarchar(max) do. Likewise, timestamp has long been deprecated in favor of rowversion, which is what's shown in the table above.

Single Versus Double Quotations

Microsoft recommends using single quotation marks for literal values. You can, if desired, use double quotes instead by issuing this statement first:


SET QUOTED_IDENTIFIER OFF;

Once you issue this statement, you can use double quotes for string literals for the rest of your database connection; you do not have to repeat it before every statement that uses double quotes. That said, Microsoft does not recommend turning QUOTED_IDENTIFIER off: several modern SQL Server features, including indexed views, computed columns, and XML data type methods, require it to be ON, and the default is ON for exactly that reason. Single quotes for literals and double quotes reserved for identifiers, when needed, is the safer convention to build the habit of using.

Punctuation in Practice

Consider this scenario: you've been asked to add a new employee, Anthony Mann, with his starting salary, and you issue the following INSERT statement:

Punctuation in Transact-SQL, SQL Server 2025. INSERT adds a new employee row. INSERT INTO dbo.Employees (FirstName, LastName, Salary) VALUES (N'Anthony', N'Mann', 20000.00); Callout 1, text literals: single quotes enclose text, the N prefix creates a Unicode literal, shown on N'Anthony'. Callout 2, numeric literals: 20000.00 is an unquoted decimal, no currency symbol is needed. Callout 3, statement punctuation: commas separate columns and values, parentheses group each list, a semicolon ends the statement. Notes state the example assumes NVARCHAR names and DECIMAL(12,2) salary, that money literals can include a currency prefix while this decimal example uses none, and that changing an existing salary requires UPDATE. — Punctuation in a Transact-SQL INSERT statement: text literals, numeric literals, and statement punctuation.


INSERT INTO dbo.Employees (FirstName, LastName, Salary)
VALUES (N'Anthony', N'Mann', 20000.00);

This example assumes NVARCHAR name columns and a DECIMAL(12,2) salary column. The N prefix marks the string literals as Unicode, the decimal value needs no quotes and no currency symbol, and the statement itself relies on punctuation throughout: commas separate the column list and the value list, parentheses group each list, and the semicolon ends the statement. To change this employee's salary later rather than insert a new row, you would use UPDATE, not INSERT.

How to Deal With Apostrophes

You may be wondering what happens if your character string contains an apostrophe, which is also a single quote.

Question: Does this throw off SQL Server?

Answer: Yes. Any literal value that contains an apostrophe must have another apostrophe directly next to it. For example, this statement will fail:


INSERT INTO dbo.Employees (FirstName, LastName, Salary)
VALUES (N'Johnny', N'D'Angelo', 20000.00);

The problem is in 'D'Angelo'. SQL Server reads the apostrophe inside the name as the end of the string literal, not as part of the text, which leaves the rest of the characters unparsable and the statement fails with a syntax error.

The correct version doubles that internal apostrophe:


INSERT INTO dbo.Employees (FirstName, LastName, Salary)
VALUES (N'Johnny', N'D''Angelo', 20000.00);

Adding the second apostrophe tells SQL Server to read it as a literal character within the string rather than as the string's closing delimiter. The rest of the statement parses correctly once that's fixed.

Summary

A short recap before the next lesson:

Character and date-style datatypes need single-quoted literals; most numeric and binary datatypes do not.
Unicode string literals should carry the N prefix, matching the Unicode name columns they're typically paired with.
Double quotes can stand in for single quotes around literals only with QUOTED_IDENTIFIER OFF, which Microsoft recommends against since several modern features depend on it staying ON.
An apostrophe inside a string literal has to be doubled, or SQL Server reads it as the end of the string rather than as a character within it.
Punctuation in a statement should be used only where it's actually required; a currency symbol on a decimal literal, for instance, isn't needed and isn't part of the value.

In the next lesson, you will learn how to insert data using values from another table.