Friday, January 1, 2016

SQL Server Brain Basher of the Week #038 - TSQL

Here is the first question of the year and this something related quotes we used with TSQL. Have a look on below query. It refers Person.Person table in AdventureWorks database. This table has set of columns including FirstName and LastName. What would be the output of the query? Note that FirstName and LastName are encased with double quotes.

USE AdventureWorks2014;

SELECT "FirstName", "LastName"
FROM Person.Person;

  1. Returns one record with FirstName as the value of first column and LastName as the value for the second column.
  2. Throws an error saying Invalid column name 'FirstName'.
  3. Returns all records from the table with relevant values for FirstName and LastName columns.
  4. Returns NO records but columns are named as FirstName and LastName.
In order to understand this and answer, you need to understand how SQL Server treats double-quotes. By default, SQL Server engine considers content encased with double-quotes as keywords. Therefore, with this query, FirstName and LastName will be considered as keywords, means will be considered as column names. That interpretation completes the query and it returns all records from the table showing values related to those two columns.

No comments: