It all has to do about strings ... And what is truely a string?

First example does not have quotes in the string.

-- Simple string

DECLARE @X VARCHAR(128);

SET @X = 'A SIMPLE STRING';

PRINT @X;

Second example uses extra quote to denote usage as a literal, not a start of a string.

-- Escaping the quotes

DECLARE @Y VARCHAR(128);

SET @Y = 'A STRING WITH ''' + CONVERT(CHAR(10), GETDATE(), 101) + ''' QUOTES AROUND THE DATE';

PRINT @Y;

Third example uses the ANSI character for quote.

-- Using CHAR()

DECLARE @Z VARCHAR(128);

SET @Z = 'A STRING WITH ' + CHAR(39) + CONVERT(CHAR(10), GETDATE(), 101) + CHAR(39) + ' QUOTES AROUND THE DATE';

PRINT @Z;

You can use the system function QUOTENAME() to make quoted strings.

http://msdn.microsoft.com/en-us/library/ms176114.aspx

You can even change the character used for escaping (ESCAPE) in a LIKE statement.

http://msdn.microsoft.com/en-us/library/ms179859.aspx

I hope this helps.

Sincerely

John