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 each of the SQL Server datatypes:

Datatype	Quotes needed for literal values
Binary	No
Bit	No
Char(n)	Yes
Datetime	Yes
Decimal(p[,s])	No
Float(n)	No
Image	No
Int	No
Money	No
Nchar	Yes
Ntext	Yes
Numeric	No
Nvarchar	Yes
Real	No
Smalldatetime	Yes
Smallint	No
Smallmoney	No
Text	Yes
Timestamp	No
Tinyint	No
Uniqueidentifier	Yes if string, No if hexadecimal
Varbinary	No
Varchar(n)	No

Single versus Double Quotations

Microsoft recommends using single quotation marks for literal values, but you should know that you can, if desired, use double quotes by issuing the following Transact-SQL statement first:

SET QUOTED_IDENTIFIER OFF

Once you issue this Transact-SQL statement, you can use double quotes for the entire lifetime of your database connection. You do not have to issue this Transact-SQL statement before every statement that uses double quotes. The following series of images below shows examples of both kinds of datatypes.

Punctuation in SQL

1) SS has asked you to update Anthony Mann with new salary information, and you issue the <tt>INSERT</tt> statement shown above.

2) Notice that the literal values for FirstName and LastName are enclosed in quotes (single quotes to be exact), whereas the literal value for Salary is not. This is because SQL Server requires quotations for the character datatype, but not for the money datatype.

3) Next, note that the literal value for Salary does not have a dollar sign. This is because punctuation is necessary with literal values only when its omission will change the definition of the value, such as with a decimal. It is important to only use necessary punctuation with literal values.

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?
The answer is yes. Any literal value that contains an apostrophe must have another apostrophe directly next to it. For example, this Transact-SQL statement will fail:

INSERT INTO Employees(FirstName, LastName, Salary)  
VALUES ('Johnny','D'Angelo', 20000)

The above statement attempts to 'D'Angelo' into the LastName column of the Employees table. However, this makes a total of five single quotes in the Transact-SQL statement. Therefore, SQL Server does not know how to parse the Transact-SQL statement into distinct values and an error results. The correct Transact-SQL statement is the following:

INSERT INTO Employees(FirstName, LastName, Salary)  
VALUES ('Johnny','D''Angelo', 20000)

Simply adding another apostrophe after the first one makes the syntax correct. In the next lesson, you will learn how to insert data using values from another table.