Posts

SQL Logical Processing Order Deep Dive

Image
If you're new to SQL, understanding the SQL logical processing order is the most important skill for writing efficient and bug-free code. Unlike other programming languages, SQL logically doesn't process the code top to bottom or left to right. In the video below, I provided examples of typical pitfalls/gotchas when creating SQL queries. We'll look into: Overview of SQL Logical Processing Order. Why query execution plan can be different from logical order. Why we can't use aliases defined in SELECT list in WHERE clause. Different between WHERE filters versus HAVING filters.  Using subquery to work around error when adding aggregate functions in WHERE clause. Using subquery and CTE to work around error when adding window functions in WHERE clause. Examine how expressions, aliases, and window functions are processed. Use window functions in ORDER BY clause. Tips on how to read SQL queries.
Post a Comment
Read more

XQuery: Aggregration With Multiple Levels

On the last post, the examples aggregate data with single level (group by customers). Today we'll explore how to aggregate data with multiple levels in XQuery. For the example below, we'll use the same orders.xml document. <?xml version="1.0"?> <orders> <order orderid="1"> <custid>1</custid> <orderdate>2014-05-15</orderdate> <shipaddress> <address>343 2nd Ave S.</address> <city>Seattle</city> <region>WA</region> <postalcode>98142</postalcode> </shipaddress> <items> <item itemid="a1"> <quantity>1</quantity> <unitprice>10.00</unitprice> </item> </items> <subtotal>10.00</subtotal> </order> <order orderid="2"> <custid>2</custid> <orderdate>2014-06-01...
2 comments
Read more

XQuery: Aggregation with Join

Today we'll learn about aggregation using XQuery FLWOR expressions. Assume that we have a customers and an orders XML document. The examples below demonstrate grouping, aggregation and join in XQuery. <?xml version="1.0"?> <customers> <customer custid="1"> <first-name>Bobby</first-name> <last-name>Nguyen</last-name> <phone>206-111-2222</phone> </customer> <customer custid="2"> <first-name>SonHa</first-name> <last-name>Pham</last-name> <phone>503-222-3333</phone> </customer> <customer custid="3"> <first-name>Ngan</first-name> <last-name>Le</last-name> <phone>928-464-732</phone> </customer> </customers> <?xml version="1.0"?> <orders> <order orderid="1"> <custid...
Post a Comment
Read more

Outer Join XML Data Using XQuery

Image
Today we'll learn about outer join in XQuery. -- -- assume that we have 2 XML documents, customers orders. -- load xml files to SQL Server as CLOB = Character Large Object DECLARE @customers XML = (SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\customers.xml', SINGLE_CLOB) AS customers); DECLARE @orders XML = (SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\orders.xml', SINGLE_CLOB) AS orders); -- Concatenate XML documents using XML PATH mode. DECLARE @customers_orders XML = (SELECT @customers, @orders FOR XML PATH('')); SELECT @customers_orders; Here's the XML data that we'll use for the examples below. --outer join -- all customers regardless if they have an order or not SELECT @customers_orders.query( 'for $c in //customers/customer let $o := //orders/order[custid = $c/@custid] return <order> &ltcustid>{$c/@custid}</custid> {$c/first-nam...
Post a Comment
Read more

Inner Join XML Data Using XQuery

Image
Today we'll learn how to join XML data using XQuery. -- -- assume that we have 2 XML documents, customers and orders. -- load xml files to SQL Server as CLOB = Character Large Object DECLARE @customers XML = (SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\customers.xml', SINGLE_CLOB) AS customers); DECLARE @orders XML = (SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\orders.xml', SINGLE_CLOB) AS orders); -- Concatenate XML documents using XML PATH mode. DECLARE @customers_orders XML = (SELECT @customers, @orders FOR XML PATH('')); SELECT @customers_orders; Here's the XML data that we'll use for the examples below. --cross join --When multiple for clauses are specified, the result is similar as a nested loops. --In this case, the return clause is evaluated once for each of the combination of the $c and $o variable's value. --Since there're 3 customers and 2 orders, the return clause is evaluate 3 * 2...
Post a Comment
Read more

Import & Concatenate XML Documents in SQL Server

Image
To prepare for next blog post (XML Inner Join using XQuery), today, we'll learn how to import and concatenate XML documents in SQL Server. USE tempdb; GO -- Import XML documents as CLOB = Character Large Object. -- Note: AS of SQL 2012, XML data type can hold 2GB of data max. DECLARE @customers XML = (SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\customers.xml', SINGLE_CLOB) AS customers); DECLARE @orders XML = (SELECT * FROM OPENROWSET(BULK 'C:\Self_Dev\XML\orders.xml', SINGLE_CLOB) AS orders); SELECT @customers AS customers, @orders AS orders; -- Concatenate XML document. -- This query will fails. Operand data type xml is invalid for add operator. DECLARE @customers_orders XML = @customers + @orders; -- Concatenate XML document using XML PATH mode. -- Add the default row tag as root node DECLARE @customers_orders2 XML = (SELECT @customers, @orders FOR XML PATH); SELECT @customers_orders; -- Concatenate XML documen...
1 comment
Read more

XQuery FLWOR Expression Part II

Image
In this post we will learn how to filter and sort data in XQuery when querying XML. -- let's assume that we have a table contain owners -- and all the detail about the stores that they own. USE TEMPDB; GO IF OBJECT_ID(N'#business', 'U') IS NOT NULL BEGIN DROP TABLE #business; END GO CREATE TABLE #business ( OwnerId INT NOT NULL, OwnerName VARCHAR(50) NOT NULL, stores XML --note: this is an XML data type ); GO --generate some XML data DECLARE @bookstore1 XML = N' <stores> <bookstore specialty="novel" address_city="Seattle"> <book style="autobiography"> <title>Create a Vision</title> <author> <first-name>Joe</first-name> <last-name>Smith</last-name> </author> <price>12</price> </book> <book style="autobiography"> <title>Face the Sun</title> ...
Post a Comment
Read more