Db4Breakfast: April 2014

Wednesday, April 30, 2014

Memory Training Daily Exercise: Binary Digits

Lately, I start practicing some memory training exercises. I'll start posting memory exercises so everyone can try. I think training our brain will help us learning database.
For all the exercises below: score 1 point for every digit in the correct order. If you list the first 3 digits correctly, but the 4th one incorrect, you'll get 3 points. You'll need a sheet of paper and pen to write down your answers. Also a clock for timer. Here's a link that has a timer.

Exercise 1: You have 1 minutes to memory the order of these 10 binary digits. After that you have 1 minute to recall the digits in exact order.
0100011011

Exercise 2: You have 1 minutes to memory the order of these 10 binary digits. After that you have 1 minute to recall the digits in reversed order.
1100110101

Exercise 3: You have 3 minutes to memory the order of these 30 binary digits. After that you have 1 minute to recall the digits in exact order.
010001101100110101001101110110

XPath Expression Part I

Before leaning FOR XML PATH option and XQuery, today, we'll learn about XPath Expression. This post will go over some XPath terminologies. Next post we'll look at the XPath syntax and work through some examples. If you want to learn more about XPath, please read the W3C XPath documentation here. W3schools website also has a good tutorial here.

Xpath (XML Path Language)
Is a standard for define path to element in XML fragments/documents.
Like a query language for XML
Expressed in hierarchical way (like path in UNIX file system)
Used in XQuery, XSLT, Xpointer, XML Schema
Level are delimited with / character
By default every column are element
Prefix the column alias with @ character to covert column to attribute-centric formatting.

XPath Nodes:
Root
Element
Attribute
Text
Namespace
Comment
Processing Instruction

XPath Axis: defines how a node-set(contain one or more node) is related to the current node.

Below are XPath Axes:
Ancestor
Ancestor-or-self
Attribute
Child
Descendant
Descendant-or-self
Following
Following-sibling
Namespace
Parent
Preceding
Preceding-sibling
Self

Below is a drawing that summarize the axes. The drawing is not mine. I get the drawing from this website.

Reference:
W3Schools Xpath Tutorial
W3C Xpath 1.0
W3C Xpath 2.0

Friday, April 25, 2014

Character Replacement With Regular Expression Range

I was working on a project at work, and came up with the 2 scripts below. Some people may find the scripts useful. The first sql script will replace a range of characters within a single string. The second script will replace a range of characters within a rowset.
Real life use of these scripts example:
Let's assume that we have an application(or a spreadsheet) tracking people annual income but the income column data type is varchar or text, and users may enter these:
100000.00
$100000
$100000
$100000/yr
$ 100000/annual

If we try to convert the values above(except the first one) to a decimal or money, the conversion will fail. Our goal is to remove all character accept character 0 to 9 and the period(.). After that we can convert it to a different data type such decimal or money.
Notice that I use the same technique on the example in this old post, Replace Function with Recursive CTE

--Script 1: replace a range of characters for a single string.
DECLARE @original_str  VARCHAR(50) = '$100000.00/yr'
      , @characters_range_str VARCHAR(50) = '[^0-9.]'--regex range, any character except for number 0 to 9 and period (.)
      , @replacement_str VARCHAR(10) = ''; -- an empty string. Note: if use CHAR, recursion will max out. I don't know why. I will do research and let you know.

; WITH T AS (--recursive cte
      --anchor member
      SELECT 
         @original_str AS string 
       , row_id = 1
      UNION ALL
     --Convert to VARCHAR(50) for data type and length to match with anchor member
      SELECT 
         CAST(REPLACE(string, SUBSTRING(string, PATINDEX('%'+@characters_range_str+'%', string), 1), @replacement_str) AS VARCHAR(50))
       , row_id = row_id + 1
      FROM T
      WHERE PATINDEX('%'+@characters_range_str+'%', string) <> 0 --PATINDEX return 1 when match is found
)
SELECT string
FROM T
ORDER BY row_id DESC
OFFSET 0 ROW FETCH NEXT 1 ROW ONLY --get final result from cte above 
--OPTION(MAXRECURSION 0); --change recursion levels. default = 100. Use 0 for max limit = 32767.

--Script 2: Replace a range of characters for a table
--temp table to hold test data
CREATE TABLE #salary (
 empid INT IDENTITY(1,1) PRIMARY KEY,
 income VARCHAR(20)
);
GO
--insert some rows for testing
INSERT INTO #salary (income)
VALUES   ('80000.00')
        ,('$90000')
        ,('$110000/year')
        ,('$200000/yr')
        ,('$ 150000 per year');
GO

--remove all characters except character 0 to 9.
DECLARE @characters_range_str VARCHAR(10) = '[^0-9.]' --regular expression character range, any character that not number 0 to 9 and period (.)
      , @replacement_str VARCHAR(10) = ''; -- an empty string. Note: if use CHAR, recursion will max out. I don't know why. I will do research and let you know.

; WITH T AS ( --recursive cte
      --anchor member
      SELECT 
          empid 
        , income
        , 1 AS row_id -- will be used to identify final output.
      FROM #salary
       UNION ALL
      SELECT 
          empid
          --Convert to VARCHAR(20) for data type and length to match with anchor member
        , CAST(REPLACE(income, SUBSTRING(income,PATINDEX('%'+@characters_range_str+'%', income),1), @replacement_str) AS VARCHAR(20))
        , row_id + 1
      FROM T
      WHERE PATINDEX('%'+@characters_range_str+'%', income) <> 0 --PATINDEX returns 1 When match is found
)
, T2 AS (--rank the rowset from cte above to get final results
      SELECT 
         empid
       , income
        --rank the row in descending order to get the result
       , ROW_NUMBER() OVER(PARTITION BY empid ORDER BY row_id DESC) AS result_row_id
      FROM T 
)
SELECT empid, income 
FROM T2
WHERE result_row_id = 1
ORDER BY empid
--OPTION(MAXRECURSION 0); --change recursion levels. default = 100. Use 0 for max limit = 32767.

--drop temp table
DROP TABLE #salary;

Note that if the data type for the variable @replacement_str is CHAR, the queries above yield this error:
Msg 530, Level 16, State 1, Line 5
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I don't know why this happens. I think because Replace function returns nvarchar or varchar, thus to avoid implicit casting, all arguments need to be either nvarchar or varchar. If you know the reason why this error occurs, please share. Anyway, please let me know if you have other way to implement this.

Reference:
PATINDEX function: http://technet.microsoft.com/en-us/library/ms188395.aspx

Tuesday, April 22, 2014

FOR XML Clause With Join

Today, we will explore how to transforms rows into XML elements when dealing with joining multiple tables. The examples below will demonstrate the important of the columns ordering when using join.

-- Example 1.
-- This query returns the first 2 customers' orders 
-- by INNER JOIN Sales.Customers and Sales.Orders table
SELECT c.custid, c.contactname, o.orderid, o.orderdate
FROM Sales.Customers AS c
JOIN Sales.Orders AS o ON o.custid = c.custid
WHERE c.custid <= 2;

Result: The customers' orders are not ordered based on the custid. Without the ORDER BY clause, there's no guarantee of the rows ordering.

-- Example 2
--In this query, ORDER BY clause is not specified and result is transformed to XML.
SELECT c.custid, c.contactname, o.orderid, o.orderdate
FROM Sales.Customers AS c
JOIN Sales.Orders AS o ON o.custid = c.custid
WHERE c.custid <= 2
FOR XML AUTO;

Result: Notice just like, in term of ordering, the result of this query is the same as previous query above, the customers' orders are scattered. The element from Sales.Customers table are repeated multiple time. Also the XML element names are the tables' aliases, and XML result formatted using attribute-centric presentation since ELEMENTS directive wasn't specified.

<c custid="2" contactname="Hassall, Mark">
  <o orderid="10308" orderdate="2006-09-18T00:00:00" />
  <o orderid="10625" orderdate="2007-08-08T00:00:00" />
</c>
<c custid="1" contactname="Allen, Michael">
  <o orderid="10643" orderdate="2007-08-25T00:00:00" />
  <o orderid="10692" orderdate="2007-10-03T00:00:00" />
  <o orderid="10702" orderdate="2007-10-13T00:00:00" />
</c>
<c custid="2" contactname="Hassall, Mark">
  <o orderid="10759" orderdate="2007-11-28T00:00:00" />
</c>
<c custid="1" contactname="Allen, Michael">
  <o orderid="10835" orderdate="2008-01-15T00:00:00" />
</c>
<c custid="2" contactname="Hassall, Mark">
  <o orderid="10926" orderdate="2008-03-04T00:00:00" />
</c>
<c custid="1" contactname="Allen, Michael">
  <o orderid="10952" orderdate="2008-03-16T00:00:00" />
  <o orderid="11011" orderdate="2008-04-09T00:00:00" />
</c>

-- Example 3.
--this query include an ORDER BY clause, result in tabular format.
SELECT c.custid, c.contactname, o.orderid, o.orderdate
FROM  Sales.Customers AS c
JOIN  Sales.Orders AS o ON o.custid = c.custid
WHERE c.custid <= 2
ORDER BY c.custid, o.orderid;

Result: Notice that all order are listed in ascending order based on custid and orderid.

-- Example 4.
--this query include an ORDER BY clause and output result as XML.
SELECT c.custid, c.contactname, o.orderid, o.orderdate
FROM Sales.Customers AS c
JOIN Sales.Orders AS o ON o.custid = c.custid
WHERE c.custid <= 2
ORDER BY c.custid, o.orderid
FOR XML AUTO;

Result: since we list column from table Sales.Customers first in the SELECT and ORDER BY clause, Sales.Customers is the parent. Thus all elements from Sales.Orders are the children and will be grouped under Sales.Customers's elements. In this case a customer can have one or many orders.

<c custid="1" contactname="Allen, Michael">
  <o orderid="10643" orderdate="2007-08-25T00:00:00" />
  <o orderid="10692" orderdate="2007-10-03T00:00:00" />
  <o orderid="10702" orderdate="2007-10-13T00:00:00" />
  <o orderid="10835" orderdate="2008-01-15T00:00:00" />
  <o orderid="10952" orderdate="2008-03-16T00:00:00" />
  <o orderid="11011" orderdate="2008-04-09T00:00:00" />
</c>
<c custid="2" contactname="Hassall, Mark">
  <o orderid="10308" orderdate="2006-09-18T00:00:00" />
  <o orderid="10625" orderdate="2007-08-08T00:00:00" />
  <o orderid="10759" orderdate="2007-11-28T00:00:00" />
  <o orderid="10926" orderdate="2008-03-04T00:00:00" />
</c>

-- Example 5.
-- This query, we swap the ordering of the columns by specifying columns 
--  in the Sales.Orders table first in both SELECT and ORDER BY clause
SELECT o.orderid, o.orderdate, c.custid, c.contactname 
FROM Sales.Customers AS c
JOIN Sales.Orders AS o ON o.custid = c.custid
WHERE c.custid <= 2
ORDER BY o.orderid, c.custid
FOR XML AUTO;

Result:
In this case, the parents elements are from Sales.Orders, and all elements from Sales.Customers are the children elements. The XML fragment is not well formatted compared to the result of example 4.

<o orderid="10308" orderdate="2006-09-18T00:00:00">
  <c custid="2" contactname="Hassall, Mark" />
</o>
<o orderid="10625" orderdate="2007-08-08T00:00:00">
  <c custid="2" contactname="Hassall, Mark" />
</o>
<o orderid="10643" orderdate="2007-08-25T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="10692" orderdate="2007-10-03T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="10702" orderdate="2007-10-13T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="10759" orderdate="2007-11-28T00:00:00">
  <c custid="2" contactname="Hassall, Mark" />
</o>
<o orderid="10835" orderdate="2008-01-15T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="10926" orderdate="2008-03-04T00:00:00">
  <c custid="2" contactname="Hassall, Mark" />
</o>
<o orderid="10952" orderdate="2008-03-16T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="11011" orderdate="2008-04-09T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>

-- Example 6.
-- This query, we swap the ordering of the column by specifying columns 
--  in the Sales.Orders table first in ORDER BY clause
SELECT c.custid, c.contactname, o.orderid, o.orderdate
FROM Sales.Customers AS c
JOIN Sales.Orders AS o ON o.custid = c.custid
WHERE c.custid <= 2
ORDER BY o.orderid, c.custid
FOR XML AUTO;

Resutl:
This XML output is similar as in Example 2. Elements from Sales.Customers are repeated multiple time.

<c custid="2" contactname="Hassall, Mark">
  <o orderid="10308" orderdate="2006-09-18T00:00:00" />
  <o orderid="10625" orderdate="2007-08-08T00:00:00" />
</c>
<c custid="1" contactname="Allen, Michael">
  <o orderid="10643" orderdate="2007-08-25T00:00:00" />
  <o orderid="10692" orderdate="2007-10-03T00:00:00" />
  <o orderid="10702" orderdate="2007-10-13T00:00:00" />
</c>
<c custid="2" contactname="Hassall, Mark">
  <o orderid="10759" orderdate="2007-11-28T00:00:00" />
</c>
<c custid="1" contactname="Allen, Michael">
  <o orderid="10835" orderdate="2008-01-15T00:00:00" />
</c>
<c custid="2" contactname="Hassall, Mark">
  <o orderid="10926" orderdate="2008-03-04T00:00:00" />
</c>
<c custid="1" contactname="Allen, Michael">
  <o orderid="10952" orderdate="2008-03-16T00:00:00" />
  <o orderid="11011" orderdate="2008-04-09T00:00:00" />
</c>

-- Example 7.
-- This query, we swap the ordering of the column
-- by specifying columns in the Sales.Orders table first in SELECT clause
SELECT o.orderid, o.orderdate, c.custid, c.contactname
FROM Sales.Customers AS c
JOIN Sales.Orders AS o ON o.custid = c.custid
WHERE c.custid <= 2
ORDER BY c.custid, o.orderid
FOR XML AUTO;

Result:
The result is not the same as example 4, even though the Sales.Customer.custid is listed first in the ORDER BY clause. Thus the columns order in the SELECT clause will also effect the XML output.

<o orderid="10643" orderdate="2007-08-25T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="10692" orderdate="2007-10-03T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="10702" orderdate="2007-10-13T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="10835" orderdate="2008-01-15T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="10952" orderdate="2008-03-16T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="11011" orderdate="2008-04-09T00:00:00">
  <c custid="1" contactname="Allen, Michael" />
</o>
<o orderid="10308" orderdate="2006-09-18T00:00:00">
  <c custid="2" contactname="Hassall, Mark" />
</o>
<o orderid="10625" orderdate="2007-08-08T00:00:00">
  <c custid="2" contactname="Hassall, Mark" />
</o>
<o orderid="10759" orderdate="2007-11-28T00:00:00">
  <c custid="2" contactname="Hassall, Mark" />
</o>
<o orderid="10926" orderdate="2008-03-04T00:00:00">
  <c custid="2" contactname="Hassall, Mark" />
</o>

In conclusion, when output XML from joining the tables, use ORDER BY clause to group element together. This is important because if the ORDER BY clause is not specified, the XML elements are not group together. That's not all children elements are grouped under their parent element. Also, the order of the columns in the SELECT clause will influence the XML result. Recommendation: list the order of the columns in the ORDER BY and SELECT clause in one-to-many order. Example: a customer can have one or many orders.

That's all for now. Next several posts, we'll learn about XML Path Mode, XPath Expression and XQuery.

Saturday, April 19, 2014

Transforms Rows Into XML Elements: FOR XML AUTO

This post we'll explore the XML AUTO mode.

FOR XML AUTO
By default:
No nesting
Use attribute-centric presentation.
Element/row tag names = schema.table or the table alias.
It's not allowed to change the row/element tag names by specifying an argument for AUTO mode.
Note it's only allowed to specify an argument to RAW or PATH mode of FOR XML to change the element/row tag name.
Example:
SELECT custid
,contactname
FROM Sales.Customers
FOR XML RAW('Customers');

By adding the ELEMENT directive, the xml result is nested and formatted using element-centric presentation.
WHEN joining multiple table:
Use ORDER BY clause to group children elements under one parent element.
The columns order in the SELECT and ORDER BY clause will influence the nesting of the XML result. I will include some examples for this on the next post.

Below are some examples.

--default presentation when no directive is specified
SELECT  custid
       ,contactname
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO;

Result: attribute-centric presentation, no nesting, row/element tag name = schema.table

<Sales.Customers custid="1" contactname="Allen, Michael" />
<Sales.Customers custid="2" contactname="Hassall, Mark" />

--using table alias
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML AUTO;

Result: In this example, the row tag names = the table alias.

<c custid="1" contactname="Allen, Michael" /> 
<c custid="2" contactname="Hassall, Mark" />

-- with ELEMENTS directive
SELECT  custid  
       ,contactname 
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO, ELEMENTS;

Result: By adding the ELEMENT directive, the XML result is nested and formatted using element-centric presentation.

<Sales.Customers>
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
</Sales.Customers>
<Sales.Customers>
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</Sales.Customers>

-- with ELEMENTS and ROOT directive
SELECT  custid  
       ,contactname 
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO, ELEMENTS, ROOT('Customers');

Result: we can add the ROOT directive to make the XML result well-formed.

<Customers>
  <Sales.Customers>
    <custid>1</custid>
    <contactname>Allen, Michael</contactname>
  </Sales.Customers>
  <Sales.Customers>
    <custid>2</custid>
    <contactname>Hassall, Mark</contactname>
  </Sales.Customers>
</Customers>

--changing the row/element name by specifying an argument for AUTO.
SELECT  custid
       ,contactname
FROM    Sales.Customers 
WHERE   custid <= 2
FOR XML AUTO('Customer'), ELEMENTS;

Result: This query fails. For AUTO mode, it's not allowed to change the row/element tag names by specifying an argument for AUTO mode. Note row/element tag name is only allowed with RAW or PATH mode of FOR XML.

Next post I will provide some example output a query result to XML when joining multiple tables.

Reference:
http://technet.microsoft.com/en-us/library/ms188273.aspx
MS 70-461 Training Kit

Monday, April 14, 2014

Create Table With Constraints

Let's take a break from XML. : )
To build a foundation for other topics(indexing, insert, update, merge) today we'll learn how to create a table. This is part of chapter 8, lesson 1 in 70-461 training kit. You can read more about the syntax here.

--
--Create customers and orders table on dbo schema of the AdventureWorks2012 sample database.
USE AdventureWorks2012;
GO
CREATE TABLE dbo.Customers (
   custid    BIGINT NOT NULL PRIMARY KEY,
   lastname  VARCHAR(30) NOT NULL,
   firstname VARCHAR(30) NOT NULL 
);
GO
CREATE TABLE dbo.Orders (
   orderid BIGINT NOT NULL PRIMARY KEY,
   orderdate DATE NOT NULL ,
   totalamount DECIMAL(10,4) NOT NULL,
   returned CHAR(1) NOT NULL DEFAULT('N'), --indicate the order was returned by customer
   custid BIGINT NOT NULL FOREIGN KEY REFERENCES dbo.Customers(custid),
   CHECK (totalamount > 0.00), 
   CHECK (returned IN ('Y','N'))
);
GO

Notice that the dbo.Customers table has a primary key constraint. The dbo.Orders table has a primary key, 1 foreign key, 1 default and 2 check constraints. Let's write some queries to insert some records to the 2 tables above and explore about the constraints.

 
--
--This query fails because it doesn't specify firstname, 
-- but dbo.Customers.firstname column doesn't allow NULL mark.
INSERT INTO dbo.Customers(custid, lastname, firstname)
VALUES(1, 'Foster', NULL);

Result: Cannot insert the value NULL into column 'firstname', table 'AdventureWorks2012.dbo.Customers'; column does not allow nulls. INSERT fails. The statement has been terminated.

 
--
--This query fails because it violates the Primary Key constraint. 
--Every custid must be unique in the dbo.Customers table.
INSERT INTO dbo.Customers(custid, lastname, firstname)
VALUES
 (1, 'Foster', 'Jack')
,(1, 'Corby' , 'Larry');

Result: Violation of PRIMARY KEY constraint 'PK__Customer__973AFEFEABC60791'. Cannot insert duplicate key in object 'dbo.Customers'. The duplicate key value is (1). The statement has been terminated.

 
--
--This query fails because totalamount < 0.00 
-- but dbo.Orders has a constraint for this totalamount > 0.00
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', -10.00, 'N', 1);

Result: The INSERT statement conflicted with the CHECK constraint "CK__Orders__totalamo__4F9CCB9E". The conflict occurred in database "AdventureWorks2012", table "dbo.Orders", column 'totalamount'. The statement has been terminated.

 
--
--This query fails because returned value is not in ('Y','N').
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', 10.00, '?', 1);

Result: The INSERT statement conflicted with the CHECK constraint "CK__Orders__returned__5090EFD7". The conflict occurred in database "AdventureWorks2012", table "dbo.Orders", column 'returned'. The statement has been terminated.

--
-- This query fails because the dbo.Orders.custid column is a foreign key
--   that references the dbo.Customers.custid column,
--   but no customer with custid = 101 in the dbo.Customers.
-- Basically, in this case an order must have a customer exists in the dbo.Customers table.
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', 10.00, 'N', 101);

Result: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Orders__custid__4EA8A765". The conflict occurred in database "AdventureWorks2012", table "dbo.Customers", column 'custid'. The statement has been terminated.

--
-- This query fails because the can't insert a string into a column with a decimal data type.
-- Note data type is also considered as a constraint.
INSERT INTO dbo.Orders(orderid, orderdate, totalamount, returned, custid)
VALUES
 (1, '20140115', 'money', 'N', 101);

Result: Error converting data type varchar to numeric.

So, table constraints are rules to keep unwanted data out of a table.

Reference:
MS 70-461 Training Kit
http://technet.microsoft.com/en-us/library/ms174979.aspx

Sunday, April 13, 2014

FOR XML Clause: get XDR or XSD Schema only Without XML Data

Previous 2 posts, we learned about XMLDATA and XMLSCHEMA directive of the FOR XML clause.
These 2 directives returns the XDR(XML-Data Reduced) and XSD(XML Schema Definition) schema along with the XML data. In this post, we will learn how to get only the XDR or XSD schema without XML data.

--This query returns the XML-Data Schema only without the XML data
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  1 <> 1 -- this condition always evaluated to false, thus empty set is returned.
FOR    XML RAW
     , XMLDATA;

Result: notice that only the XDR schema is returned.

 
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="empty" model="closed">
    <AttributeType name="custid" dt:type="i4" />
    <AttributeType name="contactname" dt:type="string" />
    <attribute type="custid" />
    <attribute type="contactname" />
  </ElementType>
</Schema>

--This query returns the XSD Schema only without the XML data.
SELECT  custid, contactname, fax
FROM    Sales.Customers
WHERE   1 <> 1 -- this condition always evaluated to false, thus empty set is returned.
FOR XML RAW('Customer')
      , ELEMENTS XSINIL
      , ROOT('Customers')
      , XMLSCHEMA;

Result: Notice that only the XSD schema is returned.

<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Customer">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="custid" type="sqltypes:int" nillable="1" />
          <xsd:element name="contactname" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="30" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="fax" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="24" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
</Customers>

Reference:
MS 70-461 Training Kit.

FOR XML RAW with XMLSCHEMA Directive

Today, we'll explore the XMLSCHEMA directive of the FOR XML clause.

XMLSCHEMA Directive
Return XSD (XML Schema Definition) schema along with XML data of XML document/fragment.
Can only use with RAW and AUTO mode.

SELECT  custid, contactname, fax
FROM    Sales.Customers
WHERE   custid <= 3
FOR XML RAW('Customer')
      , ELEMENTS XSINIL
      , ROOT('Customers')
      , XMLSCHEMA;

Result:
Notice the targetNamespce contains an Uniform Resource Name(URN) with format as: schemas-microsoft-com:sql:SqlRowSetn. Every time we execute the same query above in the same query window, n is incremented by 1. The sqltypes namespace is the link to SQL Server datatype to XSD type mapping.

 
<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
    <xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
    <xsd:element name="Customer">
      <xsd:complexType>
        <xsd:sequence>
          <xsd:element name="custid" type="sqltypes:int" nillable="1" />
          <xsd:element name="contactname" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="30" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
          <xsd:element name="fax" nillable="1">
            <xsd:simpleType>
              <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
                <xsd:maxLength value="24" />
              </xsd:restriction>
            </xsd:simpleType>
          </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
    </xsd:element>
  </xsd:schema>
  <Customer xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <custid>1</custid>
    <contactname>Allen, Michael</contactname>
    <fax>030-0123456</fax>
  </Customer>
  <Customer xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <custid>2</custid>
    <contactname>Hassall, Mark</contactname>
    <fax>(5) 456-7890</fax>
  </Customer>
  <Customer xmlns="urn:schemas-microsoft-com:sql:SqlRowSet1">
    <custid>3</custid>
    <contactname>Peoples, John</contactname>
    <fax xsi:nil="true" />
  </Customer>
</Customers>

There're a lot more information about XSD. You can read more about XSD here.

Reference:
http://technet.microsoft.com/en-us/library/bb510461.aspx
http://technet.microsoft.com/en-us/library/ms175113.aspx
http://www.w3.org/TR/xmlschema11-1/

Friday, April 11, 2014

FOR XML RAW with XMLDATA Directive

Today we'll learn more about XMLDATA directive for the FOR XML clause.

XMLDATA Directive
Return XML-Data schema (XML-Data Reduced schema aka XDR schema) of XML document/fragment.
Cannot be use with ROOT directive.
Namespace identifier = Schema+n where n is an integer. Everytime we execute the same query, n is incremented by 1.
Will be deprecated on future release of SQL Server. Recommendation: use XMLSCHEMA directive to return XSD schema instead.

--This query returns the XML-Data Schema of the XML result.
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  custid <= 2
FOR    XML RAW
     , XMLDATA;

--
--Result: Notice, if the same query above is executed one more time in the same query window,
--          the schema's name will change from Schema1 to Schema2.
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="empty" model="closed">
    <AttributeType name="custid" dt:type="i4" />
    <AttributeType name="contactname" dt:type="string" />
    <attribute type="custid" />
    <attribute type="contactname" />
  </ElementType>
</Schema>
<row xmlns="x-schema:#Schema1" custid="1" contactname="Allen, Michael" />
<row xmlns="x-schema:#Schema1" custid="2" contactname="Hassall, Mark" />

--
--The schema changes compared to result of previous query, when ELEMENTS directive is specified.
--ELEMENTS directive will changes the XML document/fragment's presentation 
--  from attribute-centric to element-centric presentation.
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  custid <= 2
FOR    XML RAW
     , XMLDATA
     , ELEMENTS;

Result:
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="eltOnly" model="closed" order="many">
    <element type="custid" />
    <element type="contactname" />
  </ElementType>
  <ElementType name="custid" content="textOnly" model="closed" dt:type="i4" />
  <ElementType name="contactname" content="textOnly" model="closed" dt:type="string" />
</Schema>
<row xmlns="x-schema:#Schema2">
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
</row>
<row xmlns="x-schema:#Schema2">
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</row>

--
--This query will yield an error because ROOT directive cannot be used with XMLDATA directive.
SELECT custid, contactname
FROM   Sales.Customers 
WHERE  custid <= 2
FOR    XML RAW
     , XMLDATA
     , ELEMENTS
     , ROOT; -- error, ROOT directive cannot be used with XMLDATA directive

Result:
Msg 6860, Level 16, State 1, Line 1
FOR XML directive XMLDATA is not allowed with ROOT directive or row tag name specified.

If you know any other restrictions or uses of the XMLDATA directive for the FOR XML RAW, please share. Next post we'll learn about XMLSCHEMA Direction for the FOR XML RAW clause.

Reference:
http://technet.microsoft.com/en-us/library/bb510461.aspx
http://technet.microsoft.com/en-us/library/ms178035.aspx

Tuesday, April 8, 2014

Help for Beginners: Install SQL Server 2012 and AdventureWorks Sample Database

This post is for those that are brand new to SQL Server. I will guide you through how to set up a test environment for your database learning. After completing this tutorials you will have a SQL Server 2012 instance on your computer along with a sample database.

Step 1: You can download a 180 day trial version of SQL Server 2012 here, or spent $40 to get the developer version with all the feature here.

Step 2: Follow this instruction from Andy Leonard to install a default SQL Server Instance on your computer. Note make sure you follow this instruction exactly or else instruction on step 5 won't work.

Step 3: Download a copy of the AdventureWorks database here. This database will be used for your learning.

Step 4: Read the instruction below to launch SQL Server Management Studio(SSMS):
Windows 7: go to Start menu > All Programs > Go to "Microsoft SQL Server 2102" folder > launch SQL Server Management Studio
Windows 8: on your keyboard press this combo keys: Windows Key (the button with windows logo on your keyboard) + S, on the search box type: SQL Server Management Studio then launch the application.

Step 5: Connect to your computer default SQL Server Instance with the info below.

Once the "Connect to Server" screen open, enter the info below
Server Type: Database Engine
Server Name: just type in a single period or MSSQLSERVER
Authentication: Windows Authentication

Step 6: Watch this YouTube video from Katie for how to attach the AdventureWorkds database to your SQL Server Instance.

That's it. You now have a test environment for learning.

Now let's run a test query. The query below returns 100 people from the person table in the AdventureWorks2012 database. In your SQL Server Management Studio, open a new query window by pressing this combo keys: Ctrl + N
Type the query below and Press F5 to execute the query.

USE AdventureWorks2012;
GO

SELECT TOP(100) FirstName, LastName
FROM PERSON.PERSON;

Monday, April 7, 2014

Transforms Rows Into XML Elements: FOR XML RAW

In this post we'll learn how to transforms rows from a table or queries results to XML using the FOR XML RAW.

FOR XML RAW

By default:

No root element

Attribute-centric presentation (all data is display as attributes)

All row tag name = row

Exclude NULL mark

Use ELEMENTS XSINIL directive to include NULL marks.

Doesn't include nesting (XML document/fragment is format flat).

We'll go through a series of examples and explore the FOR XML RAW.

--
-- When no directive is specified after the RAW keyword, this query uses 
--   default setting above for the result: 
--     * use attribute-centric presentation(row data is stored inside row tag)
--     * no nesting
--     * no root node
--     * all XML elements' name = row
--     * NULL marks are excluded. 
USE TSQL2012;
GO

SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW;

Result: Notice that the result is flat - no nesting. 
<row custid="1" contactname="Allen, Michael" />
<row custid="2" contactname="Hassall, Mark" />

--
-- This query add nesting to the result by adding the ELEMENTS directive, root node. 
-- Notice we change row tag name from row to Customer. 
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW('Customer')    -- Change the row tag name from row to Customer 
       ,ELEMENTS           -- Use element-centric. 
       ,ROOT('Customers'); -- Root node with name = Customers. 
                           -- Note Root node's name cannot be an empty string. 
                           -- If ELEMENT directive is used but exclude the ROOT directive,
                           --   the result is a XML fragment (has no root node). 
Result
<Customers>
  <Customer>
    <custid>1</custid>
    <contactname>Allen, Michael</contactname>
  </Customer>
  <Customer>
    <custid>2</custid>
    <contactname>Hassall, Mark</contactname>
  </Customer>
</Customers>

-- If we specify an empty string for the RAW mode argument, 
-- all rows tag name will be removed. 
-- Note: In this case, we also need to include ELEMENT directive, 
-- else error will occurs. 
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW('') -- Remove all rows tag name 
       ,ELEMENTS 
       ,ROOT('Customers');

Result:
<Customers>
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</Customers>

-- This query will yield an error because by default XML RAW mode 
-- use attribute-centric presentation, and attributes are enclosed 
-- in the row tags. 
-- Therefore if the row tag are removed(by specified empty string for the RAW's argument)
--  and ELEMENT directive is not specified,
-- so there's no place to store the attributes.
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW('') -- Remove all rows tag name 
       ,ROOT('Customers');

Result: 
Msg 6864, Level 16, State 1, Line 1 
Row tag omission (empty row tag name) cannot be used 
    with attribute-centric FOR XML serialization.

--This query will yield an error because root tag name 
--   can't be an empty string.
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW
       ,ELEMENTS 
       ,ROOT('');

Result:
Msg 6861, Level 16, State 1, Line 1
Empty root tag name cannot be specified with FOR XML.

-- This query include NULL marks and convert result to XML type.
SELECT  c.custid
       ,c.contactname
       ,c.fax
FROM    Sales.Customers AS c
WHERE   c.custid = 3
FOR XML RAW('Customer') -- Change the row tag name from row to Customer 
       ,ELEMENTS XSINIL -- Indent the XML elements, use element-centric, and include NULL mark 
       ,TYPE            -- Convert to result to XML type
       ,ROOT('Customers');

Result: Notice customer with custid equals 3, has missing fax number. 
 The result of missing fax is display as: <fax xsi:nil="true" />
<Customers xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Customer>
    <custid>3</custid>
    <contactname>Peoples, John</contactname>
    <fax xsi:nil="true" />
  </Customer>
</Customers>

--
-- This query uses XMLDATA directive to return the XML-Data schema that
--   describe the XML document structure.
SELECT  c.custid
       ,c.contactname
FROM    Sales.Customers AS c
WHERE   c.custid <= 2
FOR XML RAW
       ,ELEMENTS 
       ,XMLDATA; -- return the XML-Data schema

Result:
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <ElementType name="row" content="eltOnly" model="closed" order="many">
    <element type="custid" />
    <element type="contactname" />
  </ElementType>
  <ElementType name="custid" content="textOnly" model="closed" dt:type="i4" />
  <ElementType name="contactname" content="textOnly" model="closed" dt:type="string" />
</Schema>
<row xmlns="x-schema:#Schema2">
  <custid>1</custid>
  <contactname>Allen, Michael</contactname>
</row>
<row xmlns="x-schema:#Schema2">
  <custid>2</custid>
  <contactname>Hassall, Mark</contactname>
</row>

Reference:
Use RAW Mode with FOR XML: http://technet.microsoft.com/en-us/library/ms175140.aspx
70-461 Training Kit

Sunday, April 6, 2014

70-461 Training Kit Chapter 7, Lesson 1: XML Basics Part II

More XML terminologies.

Element-centric presentation

Data is stored between start tag and end tag of XML elements.

Recommendation to store data.

Example:

<contactname>Allen, Michael</contactname>

</Customer>

<contactname>Hassall, Mark</contactname>

</Customer>

</Customers>

Attribute-centric presentation

Data is stored inside of element tags.

Recommendation to store metadata

Example:

</Customers>

Mixed presentation

XML documents can contain both element-centric and attribute-centric presentation.

Example:

<contactname>Allen, Michael</contactname>

</Customer>

Prolog

Denoting the XML version & encoding

Must be the first line of the XML document

Example:

<?xml version="1.0" encoding="ISO-8859-15"?>

Namespaces

Declared in the root element

Can have alias

Example:

<emp:Employee>

<emp:employeeid>1</emp:employeeid>

</emp:Employee>

</Employees>

Special characters

<, >, &, ", '

Use & as escape character and end with a semicolon.

Example: $lt; is equivalent to <

Another option: use XML CDATA section as <![CDATA[…]]>

The characters in the … is treated as literal character

Example:

Comment
Syntax:

Example:

</Customers>

Processing Instructions

Syntax: <?instruction?>

Instruction for application that process the XML document.

Example:

<?xml-stylesheet type="text/xsl" href="/style1.xsl"?>

XSD (XML Schema Description) Document

Describe the metadata of other XML documents

Can specify: constraints, data type, element names, # of occurrence for elements.

Schema of XSD document is predefined

Typed XML document

When have a predefined schema (from XSD document)

XML Document Validation

A process of checking if the XML document is complied with a schema (in XSD document)

Reference:

XML 1.0 document Map on MSDN: http://msdn.microsoft.com/en-us/library/ms256153(v=vs.110).aspx

70-461 Training Kit

Saturday, April 5, 2014

Fun with NULL

I hope the example below will help you understand more about NULL. This 4 letters word can be troublesome and fun. After reading about null of this document, and this blog post by Hugo Kornelis, I now conclude NULL is not unknown and update my explanation below.

Definition of NULL
NULL is not a value
NULL is a mark for a missing value - not a value itself.
NULL represent missing data
NULL is not unknown
NULL is not applicable
NULL isn't empty
NULL doesn't equal zero or an empty string
NULL is not the value of string 'NULL'
NULL doesn't equal to NULL (just like saying something is missing can't equal to another missing object. I'll explain more on this below.)
Also SQL uses 3-valued predicate logic: True, False, Unknown.

March 27, 2014 SSC question of the day: Guess what this query returns?

Select 1 Where 'hello'=Null
Union
Select 2 Where 'hello'<>Null
Union
Select 3 Where 'hello' Is Null
Union
Select 4 Where 'hello' Is Not Null
Union
Select 5 Where Not ('hello'=Null)

Below is my explanation for each SELECT statement for the query above.
Note, from this question
'hello' is known
NULL represent missing data

/* Question: Does a known object equals to a missing object?
Answer: unknown. Because NULL represents missing data (a string value, a house, a car, dollar amount).
Let's assume the missing data value could be 'hi', $200.00, 'hello'.
does 'hello'='hi'? no
does 'hello'=$200.00? no
does 'hello'='hello'? yes
So we cannot be 100% sure that a known object can be equaled to a missing object.
Therefore the 'hello'=null condition is unknown.
Thus this set is filtered out */
Select 1 Where 'hello'=Null
Union
/* Question: Does a known object doesn't equals to a missing object?
Answer: unknown. Because NULL represents missing data (a string value, a house, a car, dollar amount).
Let's assume the missing data value could be 'hi', $200.00, 'hello'.
does 'hello'<>'hi'? yes
does 'hello'<>$200.00? yes
does 'hello'<>'hello'? no
So we cannot be 100% sure that a known object doesn't equal to a missing object.
Therefore the 'hello'<>null condition is unknown.
Thus this set is filtered out */
Select 2 Where 'hello'<>Null
Union
/* Question: Does a known object is missing?
Answer: False. Because we known exactly what is the value of the string literal 'hello' is.
Therefore the 'hello' Is Null condition is false.
Thus this set is filtered out */
Select 3 Where 'hello' Is Null
Union
/* Question: Does a known object is not missing?
Answer: True. Because we known exactly what is the value of the string literal 'hello' is, so it's not unknown or missing.
Therefore the 'hello' Is Not Null condition is true.
Thus this set is returned. */
Select 4 Where 'hello' Is Not Null
Union
/* From the explanation of the first select statement above,
we can conclude that the condition 'hello'=Null is unknown.
Thus Not ('hello'=Null) is equivalent to Not(unknown).
Not(unknown) is evaluated to unknown.
Thus this set is filtered out */
Select 5 Where Not ('hello'=Null)
Union
/* I throw in this last select to make thing more clear.
From the explanation of the third select statement above,
we can conclude that the condition 'hello' Is Null is false.
Thus Not ('hello' Is Null) is equivalent to Not(false).
Not(false) is evaluated to true.
Thus this set is returned. */
Select 6 Where Not ('hello' Is Null);

Result for SSC question:
4

Result for the query above:
4
6

Notice that if a condition is false or unknown the set won't be included in the result. Please provide any comment, suggestion or correction for this post if you believe any information above is not correct.
If you want to learn more about NULL, please read 4 parts series about NULL by Hugo Kornelis here.

70-461 Training Kit Chapter 7, Lesson 1: XML Basics Part I

This post we summarize the XML terminologies. This is the foundation for working with XML; Next few posts we'll explore how to convert SQL table to XML format and vice versa.

A simple XML document:

<?xml version="1.0" encoding="UTF-8"?>

</Customers>

XML(eXtensible Markup Language) Document

Is not relational

Is Case sensitive

Is ordered (the position of elements is important)

Is well-formed when every begin tag has a matching end tag, and if tags are nested properly such as no nested element.

Example: this is not a well formed because the elements are not nested.

<?xml version="1.0" encoding="UTF-8"?>

<custid>1

<order>2

</custid>

</order>

</Customer>

</Customers>

Has a single root node

Contain both actual data & metadata

Can contain only element, only attributes or both

Is flexible; convenient for exchange data between different systems/platforms.

XML Fragment

Doesn't have a single root node

Root Node

Encloses all other elements within a xml document.

Example:

<Root>

</Root>

XML Tags

Begin/start tag. Example <Employees>

End tag. Example </Employees>

XML Element

The actual tags

Example: customer is an element name.

<contactname>Allen, Michael</contactname>

</Customer>

Note white space is not allowed in an element name. example: this is invalid

Tag may contain data within the tags, these are attributes

Element Data

Data between the tags

Example:

Attribute data

The data inside of a xml element tag.

Example:

</Customers>

Attribute data must be enclosed with single or double quotes.

Attribute names must be unique inside an element tag .

Example: this is invalid since the 2 attribute names are the same

Attributes within an element form a set with no order.

Always parsed as text, not XML

Example: the &lt is interpret as '&lt' not < symbol

Actual Data

The data itself

Metadata

Data about the actual data. Describe the data such as properties, relationship.

Reference: Microsoft 70-461 Training Kit

Friday, April 4, 2014

70-461 Training Kit Chapter 2, Lesson 2: Data Type

Data Type:

Is a constraint

Encapsulates behavior

Relational Model's Physical Data Independence

Let the storage engine take care of the internal storage format

Exact Numeric:

TINYINT (1 byte)

SMALLINT (2 bytes)

INT (4 bytes)

BIGINT (8 bytes)

NUMBERIC (size varies depend on precision)
DECIMAL (size varies depend on precision)

SMALLMONEY(4 bytes)

MONEY(8 bytes)

Approximate Numeric

REAL (4 bytes)

FLOAT

Character non-Unicode (CHAR, VARCHAR)

support only one language based on collation properties

1 byte/character

Use single quotation marks, 'xyz', to indicate regular character strings (non-Unicode)

Character Unicode (NCHAR, NVARCHAR)

Unicode, support multiple language

2 bytes/character

Use N'xyz', to indicate Unicode character string.

Binary Strings:

BINARY

VARBINARY

Image

Date and Time:

DATE (3 bytes)

TIME (3 to 5 bytes)

DATETIME2 (6 to 8 bytes)

SMALLDATETIME (4 bytes)

DATETIME (8 bytes)

DATETIMEOFFSET (8 to 10 bytes)

Other Data Types

XML

Hierarchyid

Uniqueidentifier

Timestamp

Spatial Types

Geometry

Geography

Sql_variant (can be used to store variety of data type)

Table

Cursor

BOL Data Type: http://msdn.microsoft.com/en-us/library/ms187752.aspx

Microsoft 70-461 Training Kit

Thursday, April 3, 2014

70-461 Training Kit Chapter 2, Lesson 2: Functions & Expression

CASE Expression:
Is an expression
Return values
2 types: simple and search

Expression Functions:

COALESCE (standard)

NULLIF (standard)

Syntax: NULLIF( expression, expression)

Return a NULL mark if two expressions are equal.

ISNULL (TSQL specific)

Logical Functions (available in SQL Server 2012)

CHOOSE

Syntax: CHOOSE( index, val_1, val_2, [, val_n])

Return a value at specified index

Index is based 1

IIF

Syntax: IIF( boolean_expression, true_value, false_value)

Date and Time Functions:

GETDATE (TSQL specific)

CURRENT_TIMESTAMP (standard)

DATEPART

EOMONTH

DATEFROMPARTS

DATEADD

DATEDIFF

SWITCHOFFSET

TODATETIMEOFFSET

SYSDATETIME

SYSUTCDATETIME

SYSDATETIMEOFFSET

String Functions

REVERSE

SUBSTING

STUFF

REPLACE

REPLICATE

CONCAT

CHARINDEX

PATINDEX

LEN

DATALENGTH

LOWER

UPPER

LTRIM

RTRIM

FORMAT

LEFT

RIGHT

BOL functions: http://msdn.microsoft.com/en-us/library/ms186724%28v%3DSQL.110%29.aspx
Microsoft 70-461 Training Kit