# Friday, February 29, 2008

Yesterday we discussed the changes to XQuery by allowing LET statements. Today the last XML enhancement is an "enhancement" to XML DML.

XML Data Manipulation Language (DML) is specific to MS SQL Server 2005 and 2008. It is a very useful feature that allows you to insert nodes to an existing XML instance, delete an element, or replace the value of (sort of an edit.) It works like this:

First let's create a table with an XML data type and insert a piece of XML into it:

Create table XMLTest1
    (
    Speaker_ID int primary key,
    SpeakerXML xml not null
    )

GO

Insert into XMLTest1
Values
(
    1,
    '
    <classes>
        <class name="Writing Secure Code for ASP .NET " />
        <class name="Using XQuery in SQL Server 2008" />
        <class name="SQL Server and Oracle Working Together" />
    </classes>
    '
    )

Now to insert an element into this table it is pretty easy, just use the XML modify method of the XML data type. Since our XML instance is in a SQL Server table we have to use the standard SQL UPDATE and SET syntax with a WHERE clause to get to the piece of XML in our table we are concerned with. You can see how easy it is to add an element to the table here:

Update XMLTest1
Set SpeakerXML.modify(
'insert
<class name="Using Linq to SQL" />
into /classes[1]'
)
Where Speaker_ID=1

If we select * from XMLTest1 WHERE Speaker_ID=1 the XML column looks like this now:

<classes>
  <class name="Writing Secure Code for ASP .NET " />
  <class name="Using XQuery in SQL Server 2008" />
  <class name="SQL Server and Oracle Working Together" />
  <class name="Using Linq to SQL" />
</classes>

SQL Server 2008 has an enhancement to this. You can substitute the XML element with a variable. For example let's create a variable here:

DECLARE @newElement xml
Set @newElement='<class name="ASP.NET Scalability"/>'

Now let's do the same insert as before, however, we will use the @newElement variable. This will enable you to select XML from other areas and stick it into a variable and then insert it into another XML instance. Here is the final piece:

Update XMLTest1
Set SpeakerXML.modify(
'insert
sql:variable("@newElement") 
into /classes[1]'
)
Where Speaker_ID=1

If we select * from XMLTest1 WHERE Speaker_ID=1 the XML column looks like this now:

<classes>
  <class name="Writing Secure Code for ASP .NET " />
  <class name="Using XQuery in SQL Server 2008" />
  <class name="SQL Server and Oracle Working Together" />
  <class name="Using Linq to SQL" />
  <class name="ASP.NET Scalability" />
</classes>

Not the greatest of new features, but a handy dandy feature to say the least.

posted on Friday, February 29, 2008 12:00:54 PM (Eastern Standard Time, UTC-05:00)  #    Comments [1] Trackback
# Thursday, February 28, 2008

Continuing our discussion of XML in SQL Server 2008, SQL Server 2005 alowed us to use XQuery to query XML data in an XML data type.  You can use the FLWOR statements of for, where, order by, and retun, but not LET. SQL Server 2008 now has support for LET, which is used to assign values to variables in an XQuery expression. For example we are going to take the XML below and count the number of sessions per speaker using the count XQuery expression and assign it to a variable via LET.

declare @xml xml
set @xml=
'
<Speakers>
    <Speaker name="Stephen Forte">
        <classes>
            <class name="Writing Secure Code for ASP .NET "/>
            <class name="Using XQuery in SQL Server 2008"/>
            <class name="SQL Server and Oracle Working Together"/>
        </classes>
    </Speaker>
    <Speaker name="Richard Campbell">
        <classes>
            <class name="SQL Server Profiler"/>
            <class name="Advanced SQL Querying Techniques"/>
            <class name="SQL Server and Oracle Working Together"/>
            <class name="From 1 Server to 2"/>
        </classes>
    </Speaker>
</Speakers>
'
SELECT @xml.query(
'<Speakers>
{
for $Speaker in /Speakers/Speaker
let $count :=count($Speaker/classes/class)
order by $count descending
return
<Speaker>
{$Speaker/@name}
{$Speaker/count}
<SessionCount>{$count}</SessionCount>
</Speaker>
}
</Speakers>')

The result is shown here, notice that Richard sorts on top since we used the descending order to our order by and ordered by the expression we created via the LET statement. This is very helpful if you are manipulating simple XML and need to do a mini-transform via XQuery. I used to use the RETURN statement all the time to make slight manipulations to my XML, so I was waiting for LET. While this is the only addition to XQuery, it is a much needed and welcome one.

<Speakers>
  <Speaker name="Richard Campbell">
    <SessionCount>4</SessionCount>
  </Speaker>
  <Speaker name="Stephen Forte">
    <SessionCount>3</SessionCount>
  </Speaker>
</Speakers>

posted on Thursday, February 28, 2008 12:00:35 PM (Eastern Standard Time, UTC-05:00)  #    Comments [1] Trackback
# Wednesday, February 27, 2008

Continuing our discussion from yesterday on SQL Server 2008 XML Enhancements, let's take a look at the XSD enhancement of Union and List types. In SQL Server 2005 you could define a simple type of xsd:list with an enumeration to require items in a list for any element that inherits that type. For example our XSD below will allow FedEx, DHL, and UPS for a shipping.

create XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:simpleType name="shiptypeList">
    <xsd:list>
        <xsd:simpleType>
          <xsd:restriction base="xsd:string">
            <xsd:enumeration value="FexEx"/>
            <xsd:enumeration value="DHL"/>
            <xsd:enumeration value="UPS"/>
          </xsd:restriction>
        </xsd:simpleType>
    </xsd:list>
    </xsd:simpleType>
   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>
        <xsd:element name="ShipType" type="shiptypeList"/>   
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>'
GO

DECLARE @xml XML(dbo.order_xsd)
SET @xml =
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>100</OrderAmt>
    <ShipType>DHL</ShipType>
</Order>'
GO

So this is cool, gives you some database lookup functionality, kind of third normal form Clemens Vasters style. With the new support for union of lists in SQL Server 2008 with xsd:union, you can combine multiple lists to one simple type. For example in our XSD below we allow the same strings as FedEx, etc, but also numerical values.

My example below is pretty basic, but this is useful if you have more than one way to describe something and need two lists to do so. One thing that comes to mind is units of measurement, metric and English measurements, so shoe sizes, US and Italian, etc.  This is very useful when you need to restrict items and are writing them from a database, etc.

Take a look at the demo for SQL 2008 below, notice that I changed the data to 1 from FedEx:

create XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
    <xsd:simpleType name="shiptypeList">
        <xsd:union>
            <xsd:simpleType>
              <xsd:list>
                <xsd:simpleType>
                  <xsd:restriction base="xsd:integer">
                    <xsd:enumeration value="1"/>
                    <xsd:enumeration value="2"/>
                    <xsd:enumeration value="3"/>
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:list>
            </xsd:simpleType>
            <xsd:simpleType>
              <xsd:list>
                <xsd:simpleType>
                  <xsd:restriction base="xsd:string">
                    <xsd:enumeration value="FedEx"/>
                    <xsd:enumeration value="DHL"/>
                    <xsd:enumeration value="UPS"/>
                  </xsd:restriction>
                </xsd:simpleType>
              </xsd:list>
            </xsd:simpleType>
        </xsd:union>
    </xsd:simpleType>

   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>   
        <xsd:element name="ShipType" type="shiptypeList"/>
        <xsd:any namespace="##other" processContents="lax"/>   
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>'
GO

DECLARE @xml XML(dbo.order_xsd)
SET @xml =
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>100</OrderAmt>
    <ShipType>1</ShipType>
    <Notes xmlns="sf">Steve Test 1</Notes>
</Order>'
GO

posted on Wednesday, February 27, 2008 12:00:16 PM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Tuesday, February 26, 2008

The SQL Server 2008 February CTP is now installed. I did it on my daily machine to force me to use it in a more realistic environment. It installed a-ok with my default SQL 2005 instance. As you can see below you can use the SQL 2008 IDE to manage and work with both SQL Server instances on your computer at the same time.

image

I am playing with the new XML features of SQL Server. For those of you that know my old company, Corzen, used the XML data type to do some really cool stuff. I'm now investigating the XML features for the update to the Programming SQL Server 2008 book from MS Press.

I guess that XLINQ is pretty cool and well accepted Microsoft did not change all that much in the XML world in SQL Server 2008. The new XML features of SQL Server 2008 are:

  • Lax validation support in XSD-giving you some more flexibility with your required areas of the XSD
  • Full support of the XSD: xs:dateTime, basically the preservation of time zones
  • Union and List types in XSDs (I have not played with this yet so can't speak to how useful it will be yet, give me a day)
  • Support of the LET FLOWR expression in XQuery (yay!)
  • XML DML "enhancements." I put that in quotes for two reasons, there is only one true enhancement, allowing you to insert data from a SQL column or a SQL variable as part of your insert. This is a very minor enhancement, I was hoping for a new syntax for specifying position, etc. Also XML DML is SQL Server specific, so it is hard to get very excited about this feature.

Now let's take a look at some of the XSD enhancements. SQL Server 2005 added the ability put XML data into an intrinsic XML data type. This was a vast improvement over SQL Server 2000 where we had to put data in text fields. In addition, SQL Server 2005 allowed us to restrict this column with an XSD. Consider this XSD:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>

We have defined a few elements, given them a sequence and data types. We also say that you can only have an order amount between 1 and 5000 (work with me, I like simple demos) since the OrderAmt element inherits the simple type orderAmt. This works well, for example if we create an XSD and a table with an XML column bound to that XSD in SQL 2005:

--Valid SQL Server 2005 Schema
CREATE XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>'
GO

CREATE TABLE xml_schematest (
   Order_ID int primary key,
   Order_XML XML(order_xsd) --XML Schema Name
)

Now let's insert some XML in there, the XML will be like this:

Insert into xml_schematest
VALUES
(1,
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>100</OrderAmt>
</Order>
')

Here is the result, notice the Z for UTC as well as the new time (more on that below):

<Order>
  <CustomerName>Bill Gates</CustomerName>
  <OrderDate>2008-10-10T19:22:27.250Z</OrderDate>
  <OrderAmt>100</OrderAmt>
</Order>

This XML will fail since it has an order over 5000.

Insert into xml_schematest
VALUES
(2,
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>10000</OrderAmt>
</Order>
')

So life is good. We have a nice XSD and it restricts our content. SQL Server 2008 has added two new features of an XSD that we will look at today. The first is something called Lax validation. Let's say that you wanted to add an additional element after <OrderAmt> that is not part of the same schema. In SQL Server 2005, schemas can use processContents values of skip and strict for any and anyAttribute declarations as a wildcard. If it was set to skip, SQL will skip completely the validation of the additional element, if it is set to strict, SQL will require that it has an element or namespace defined in the current schema. SQL Server 2008 adds support for an additional validation option: lax. By setting the processContents attribute for this wildcard section to lax, you can enforce validation for any elements that have a schema associated with them, but ignore any elements that are not defined in the schema. Pretty cool, no?

Also the xsd:dateTime XML data type is now timezone aware. SQL Server 2005 you had to provide a time zone for dateTime, time and date data (did you really ever do that? If you needed the flexibility, trust me, this was a pain). What was not cool was that SQL Server 2005 did not preserve the time zone information for your data for dateTime or time,  it normalizes it to UTC (so for example 2008-10-10T08:00:00:000-5:00 is normalized to 2008-10-10T13:00:00:000Z. Notice the Z for UTC. Quite annoying.) In SQL Server 2008, this has been removed! Take a look at this code:

CREATE XML SCHEMA COLLECTION dbo.order_xsd
AS
'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:simpleType name="orderAmt" >
     <xsd:restriction base="xsd:int" >
       <xsd:maxInclusive value="5000" />
       <xsd:minExclusive value="1" />
     </xsd:restriction>
   </xsd:simpleType>
   <xsd:element name="Order">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CustomerName" type="xsd:string" />
        <xsd:element name="OrderDate" type="xsd:dateTime"/>
        <xsd:element name="OrderAmt" type="orderAmt"/>   
        <xsd:any namespace="##other" processContents="lax"/>   
      </xsd:sequence>
    </xsd:complexType>
   </xsd:element> 
</xsd:schema>'
GO
CREATE TABLE xml_schematest (
   Order_ID int primary key,
   Order_XML XML(order_xsd) --XML Schema Name
)
GO

Now this will work and even preserve the time zone of -5 (New York) and notice the random <Notes> element due to lax validation. Here is how to insert the XML:

Insert into xml_schematest
VALUES
(1,
'<Order>  
    <CustomerName>Bill Gates</CustomerName>
    <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
    <OrderAmt>100</OrderAmt>
    <Notes xmlns="sf">Steve Test 1</Notes>
</Order>
')

Here are the results in the database with the original time zone:

<Order>
  <CustomerName>Bill Gates</CustomerName>
  <OrderDate>2008-10-10T14:22:27.25-05:00</OrderDate>
  <OrderAmt>100</OrderAmt>
  <Notes xmlns="sf">Steve Test 1</Notes>
</Order>

Tomorrow I will look at Union and List types.

posted on Tuesday, February 26, 2008 12:00:36 PM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Monday, February 14, 2005

I know this. (Ask me if I care.) Anyway, all of those folks who dislike me usually say "is there a way to shut him up." Well there is a way!!!! Have me train for a marathon. Ran 18 miles on Saturday and then a 9.3 mile race on Sunday inside the “gates“ in Central Park. I was very quiet all weekend. (Marathon in 12 days)

 

Another thing people thought was impossible was referencing a RANK()ing function in a WHERE clause in SQL Server 2005 or using an aggregate with the Ranking and Windowing functions. A simple solution that I did not see in any of the docs, use a Common Table Expression. Take this simple aggregate, rounding to the nearest 100th the sales figure from AdventureWorks;

 

Select CustomerID,round(convert(int, sum(totaldue))/100,8) *100 as totalamt

From Sales.SalesOrderHeader

Group by CustomerID

 

Gives you results like:

 

CustomerID  totalamt

----------- -----------

22814       0

11407       0

28387       600

15675       7900

18546       0

 

(and so on)

 

What if you want to rank them? Easy, make the aggregate a CTE and rank over the new field:

--rank by totaldue, summed and rounded (nearest 100)

--need a CTE to do a sum & rounding

--so this example will have a

--customerID summed with all of

--their orders

With CustomerSum

As

(

Select CustomerID,round(convert(int, sum(totaldue))/100,8) *100 as totalamt

From Sales.SalesOrderHeader

Group by CustomerID

)

Select *,

            Rank() Over (Order By totalamt Desc) as Rank

From CustomerSum

 

Results:

CustomerID  totalamt    Rank

----------- ----------- --------------------

678         1179800     1

697         1179400     2

170         1134700     3

328         1084400     4

514         1074100     5

(and so on)

 

Ditto if you want to filter a query by the results of a ranking function. Just move the ranking function up to the CTE:

 

--use a common table expression if you want

--to filter by one of the rows that contain a

--ranking function since ranking functions

--are not allowed in where or having clauses

With NumberRows

As

(

Select SalesOrderID, CustomerID,

            Row_Number() Over (Order By SalesOrderID) as RowNumber

From Sales.SalesOrderHeader

)

 

Select * from NumberRows

where RowNumber between 100 and 200

Order By SalesOrderID

 

Resutls:

SalesOrderID CustomerID  RowNumber

------------ ----------- --------------------

43758        27646       100

43759        13257       101

43760        16352       102

43761        16493       103

43762        27578       104

(and so on)

 

 

 

 

posted on Monday, February 14, 2005 5:49:05 PM (Eastern Standard Time, UTC-05:00)  #    Comments [4] Trackback
# Monday, January 17, 2005

Yukon has enhanced the SQL Server 2000 FOR XML function. If you want to create element based XML (yay!), FOR XML PATH allows you to specify column aliases that contain valid XPath expressions that will shape your XML output.

--XML FOR PATH

Select

ContactID as [@Contact_ID],

FirstName as [ContactName/First],

LastName as [ContactName/Last],

Phone as [ContactPhone/Phone1]

From Person.Contact For XML PATH

Produces output like:

<Customer>

  <CustomerID>1</CustomerID>

  <OrderDetail>

    <SalesOrderID>43860</SalesOrderID>

    <OrderDate>2001-08-01T00:00:00</OrderDate>

  </OrderDetail>

  <OrderDetail>

    <SalesOrderID>44501</SalesOrderID>

    <OrderDate>2001-11-01T00:00:00</OrderDate>

  </OrderDetail>

</Customer>

<Customer>

  <CustomerID>2</CustomerID>

  <OrderDetail>

    <SalesOrderID>46976</SalesOrderID>

    <OrderDate>2002-08-01T00:00:00</OrderDate>

  </OrderDetail>

  <OrderDetail>

    <SalesOrderID>49054</SalesOrderID>

    <OrderDate>2003-02-01T00:00:00</OrderDate>

  </OrderDetail>

  <OrderDetail>

</Customer>

If you are familiar and comfortable with XPath, there are some additional features to XML PATH you may like. You can use the following XPath node test functions to further control the shape of your XML output:

·        node()

·        text()

·        data()

·        comment()

·        processing-instruction()

 

posted on Monday, January 17, 2005 1:31:13 PM (Eastern Standard Time, UTC-05:00)  #    Comments [6] Trackback
# Tuesday, January 11, 2005

The XML Data Type can, just like other data types, conform to Nullability, Defaults and Constraints. If you want to make the field required (NOT NULL) and provide a default value on your XML column, just specify like you would any other column.

CREATE TABLE OrdersXML

   (OrderDocID INT PRIMARY KEY,

   xOrders XML NOT NULL Default '</Orders>'

This insert will work because it is relying on the default:

Insert Into OrdersXML (OrderDocID, xOrders) Values (2, DEFAULT)

The following insert will work, even if we add non <Orders> in our table because we have not declared a constraint on the column:

Insert Into OrdersXML (OrderDocID, xOrders) Values (3, 'steve')

SQL Server 2005 gives you the opportunity to add a constraint. An interesting feature of an XML column is that the CHECK constraint is based on the XML Infoset model using the methods of the XML column. You can use the exist method, which is specific to the XML type, as part of our CHECK constraint to force a particular type of XML element to exist.  In the following example we will drop and recreate the table from scratch (you can use ALTER TABLE as well) adding a default and CHECK CONSTRAINT.

Drop Table OrdersXML

--create the table with the XML Datatype

--using a default value

--also has the check constraints

CREATE TABLE OrdersXML

   (OrderDocID INT PRIMARY KEY,

   xOrders XML NOT NULL Default '<Orders>'

 CONSTRAINT xml_orderconstraint

  CHECK(xOrders.exist('/Orders')=1))

 

Now this example from before will fail.

Insert Into OrdersXML (OrderDocID, xOrders) Values (3, 'steve')

When you attempt to insert data that will violate the xml_orderconstraint you get the following error message from SQL Server:

Msg 547, Level 16, State 0, Line 1

INSERT statement conflicted with CHECK constraint

'xml_orderconstraint'. The conflict occurred in database

'AdventureWorks', table 'OrdersXML', column 'xOrders'.

The statement has been terminated.

 

posted on Tuesday, January 11, 2005 10:25:06 AM (Eastern Standard Time, UTC-05:00)  #    Comments [23] Trackback
# Wednesday, December 22, 2004

SQL Server 2005 introduces an enhancement to the OPENROWSET function, the new BULK rowset OLE DB provider. Via OPENROWSET, it lets you access data in files in a relational fashion. In the past, you used BULK INSERT to pull data from the file into a temp table and then ran your relational operations, but with SQL Server 2005, you can use the data contained in the file as part of your SQL statement rowset results. Keep in mind that you must specify a format file, which is the same format file you use with bcp.exe or the BULK INSERT statement. The following code shows how to access a file named c:\bulk.txt using the format file c:\bulk.fmt:

SELECT customerid, customername, totalorders

FROM OPENROWSET(BULK 'c:\bulk.txt',

       FORMATFILE = 'c:\bulk.fmt') AS (customerid, customername, totalorders)

posted on Wednesday, December 22, 2004 8:27:10 PM (Eastern Standard Time, UTC-05:00)  #    Comments [16] Trackback
# Tuesday, December 21, 2004

SQL Server 2005 adds a new feature called Common Table Expressions (CTE). The true power of CTEs emerges when you use them recursively to perform hierarchical queries on tree structured data. In fact, besides SQL-92 compliance, this was the main reason Microsoft built CTEs. A recursive CTE is constructed from a minimum of two queries, the first, or anchor member (AM), is a nonrecursive query, and the second, or recursive member (RM), is the recursive query. Within your CTE’s parentheses (after the AS clause), you define queries that are either independent or refer back to the same CTE. The AM and RM are separated by a UNION ALL statement. Anchor members and are invoked only once and are invoked repeatedly until the query returns no rows. Multiple AMs can be appended to each other using either a UNION or a UNION ALL operator, depending on whether you want to eliminate duplicates. (You must append recursive members using a UNION ALL operator.)  Here is the syntax:

With SimpleRecursive( field names)

As

(

     <Select Statement for the Anchor Member>

 

     Union All

    

     <Select Statement for the Recursive Member>

)

 

Select * From SimpleRecursive

 

To demonstrate this feature, I will create an example here. We create a table with employees and a self referencing  field back to Employee_ID called ReportsTo (I call this a Domestic Key in lue of a Foreign Key). We are going to write a query that returns all the employees who report to Stephen (Employee_ID=2) and all the employees who report to Stephen’s subordinates: 

--create a table with tree data

--Reportsto is a "domestic key" back to Employee_id

create table Employee_Tree (Employee_NM nvarchar(50), Employee_ID int Primary Key, ReportsTo int)

--insert some data, build a reporting tree

insert into employee_tree values('Richard', 1, 1)

insert into employee_tree values('Stephen', 2, 1)

insert into employee_tree values('Clemens', 3, 2)

insert into employee_tree values('Malek', 4, 2)

insert into employee_tree values('Goksin', 5, 4)

insert into employee_tree values('Kimberly', 6, 1)

insert into employee_tree values('Ramesh', 7, 5)

 

Our table looks like this:

Employee_NM

Employee_ID

ReportsTo

Richard

1

null

Stephen

2

1

Clemens

3

2

Malek

4

2

Goksin

5

4

Kimberly

6

1

Ramesh

7

5

 

Now the recursive query to determine all the employees who will report to Stephen:

 

--Recursive Query

WITH SimpleRecurvice(Employee_NM, Employee_ID, ReportsTO)

            AS

(SELECT Employee_NM, Employee_ID, ReportsTO

  FROM Employee_Tree WHERE Employee_ID = 2

UNION ALL

SELECT p.Employee_NM, p.Employee_ID, p.ReportsTO

 FROM Employee_Tree  P  INNER JOIN

 SimpleRecurvice A ON A.Employee_ID = P.ReportsTO

)

SELECT Employee_NM FROM SimpleRecurvice

 

Employee_NM

--------------------------------------------------

Stephen

Clemens

Malek

Goksin

Ramesh

 

(5 row(s) affected)

This recursion starts where Employee_ID= 2 (the ANCHOR MEMBER or the first SELECT). It picks up that record and then, via the RECURSIVE MEMBER (the SELECT after the UNION ALL), picks up all of the records that report to Stephen and that record’s children (Goksin reports to Malek and Malek reports to Stephen). Each subsequent recursion tries to find more children that have as parents the employees found by the previous recursion. Eventually the recursion returns no results and that is what causes the recursion to stop (the reason why Kimberly is not returned).

posted on Tuesday, December 21, 2004 8:51:18 AM (Eastern Standard Time, UTC-05:00)  #    Comments [1] Trackback
# Friday, December 10, 2004

 

The ranking functions can also combine with windowing functions. A windowing function will divide a resultset into equal partitions based on the values of your PARTITION BY statement in conjunction with the OVER clause in your ranking function. It is like applying a GROUP BY to your ranking function-you get a separate ranking for each partition. The example below uses ROW_NUMBER with PARTITION BY to count the number of orders by order date by salesperson. We do this with a PARTITION BY SalesPersonID OVER OrderDate. This can be used with any of the four ranking functions.

Select SalesOrderID, SalesPersonID, OrderDate,

Row_NUMBER() Over (Partition By SalesPersonID Order By OrderDate) as OrderRank

From Sales.SalesOrderHeader

Where SalesPersonID is not null

 

SalesOrderID SalesPersonID OrderDate               OrderRank

------------ ------------- ----------------------- ---

43659        279           2001-07-01 00:00:00.000 1

43660        279           2001-07-01 00:00:00.000 2

43681        279           2001-07-01 00:00:00.000 3

43684        279           2001-07-01 00:00:00.000 4

43685        279           2001-07-01 00:00:00.000 5

43694        279           2001-07-01 00:00:00.000 6

43695        279           2001-07-01 00:00:00.000 7

43696        279           2001-07-01 00:00:00.000 8

43845        279           2001-08-01 00:00:00.000 9

43861        279           2001-08-01 00:00:00.000 10

. . . More

48079        287           2002-11-01 00:00:00.000 1

48064        287           2002-11-01 00:00:00.000 2

48057        287           2002-11-01 00:00:00.000 3

47998        287           2002-11-01 00:00:00.000 4

48001        287           2002-11-01 00:00:00.000 5

48014        287           2002-11-01 00:00:00.000 6

47982        287           2002-11-01 00:00:00.000 7

47992        287           2002-11-01 00:00:00.000 8

48390        287           2002-12-01 00:00:00.000 9

48308        287           2002-12-01 00:00:00.000 10

. . . More

 

PARTITION BY supports other SQL Server aggregate functions including MIN and MAX.

 

posted on Friday, December 10, 2004 8:47:01 AM (Eastern Standard Time, UTC-05:00)  #    Comments [9] Trackback
# Thursday, December 09, 2004

 

            DENSE_RANK works exactly like RANK() but will remove the skipping of numbers in the tie.  

 

Select SalesOrderID, CustomerID,

            DENSE_RANK() Over (Order By CustomerID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

 

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           1

44501        1           1

45283        1           1

46042        1           1

46976        2           2

47997        2           2

49054        2           2

50216        2           2

51728        2           2

57044        2           2

63198        2           2

69488        2           2

44124        3           3

. . . More

 

NTile(n) will evenly divide all the results into approximately even pieces and assign each piece the same number in the resultset. A perfect example is the percent of 100 (like for an examination in University)  or a percentile of runners in a road race.

 

Select SalesOrderID, CustomerID,

            NTILE(10000) Over (Order By CustomerID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

 

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           1

44501        1           1

45283        1           1

46042        1           1

46976        2           2

47997        2           2

49054        2           2

50216        2           2

51728        2           3

57044        2           3

63198        2           3

69488        2           3

44124        3           4

. . . More

 

One last example will bring these all together in one SQL Statement and show the difference between all four ranking functions.

 

--Ranking All

use adventureworks

Select SalesOrderID as OrderID, CustomerID,

            Row_Number() Over (Order By CustomerID) as RowNum,

            RANK() Over (Order By CustomerID) as Rank,

            DENSE_RANK() Over (Order By CustomerID) as DRank,

            NTILE(10000) Over (Order By CustomerID) as NTile

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

 

 

OrderID     CustomerID  RowNum               Rank                 DRank             NTile  

----------- ----------- -------------------- -------------------- -------------------- --------------------

43860       1           1                    1                    1                    1

44501       1           2                    1                    1                    1

45283       1           3                    1                    1                    1

46042       1           4                    1                    1                    1

46976       2           5                    5                    2                    2

47997       2           6                    5                    2                    2

49054       2           7                    5                    2                    2

50216       2           8                    5                    2                    2

51728       2           9                    5                    2                    3

57044       2           10                   5                    2                    3

63198       2           11                   5                    2                    3

69488       2           12                   5                    2                    3

44124       3           13                   13                   3                    4

44791       3           14                   13                   3                    4

 

. . . More

posted on Thursday, December 09, 2004 1:34:28 PM (Eastern Standard Time, UTC-05:00)  #    Comments [2] Trackback
# Wednesday, December 08, 2004

            RANK() works a lot like ROW_NUMBER() except that it will not break ties, you will not get a unique value for ties.

Select SalesOrderID, CustomerID,

            RANK() Over (Order By CustomerID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

 

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           1

44501        1           1

45283        1           1

46042        1           1

46976        2           5

47997        2           5

49054        2           5

50216        2           5

51728        2           5

57044        2           5

63198        2           5

69488        2           5

44124        3           13

. . . More

Dense_Rank tomorrow...

posted on Wednesday, December 08, 2004 12:39:51 AM (Eastern Standard Time, UTC-05:00)  #    Comments [12] Trackback
# Tuesday, December 07, 2004

SQL Server 2005 adds the functionality of a Ranking expression that can be added to your resultset that is based on a ranking algorithm being applied to a column that you specify. This will come in handy in .NET applications for paging and sorting in a grid as well as many other scenarios.

 The most basic new ranking function is ROW_NUMBER(). ROW_NUMBER() returns a column as an expression that contains the row’s number in the result set. This is only a number used in the context of the resultset, if the result changes, the ROW_NUMBER() will change. The ROW_NUMBER() expression takes an ORDER BY statement with the column you want to use for the row count with an OVER operator as shown here:

Select SalesOrderID, CustomerID,

            Row_Number() Over (Order By SalesOrderID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By SalesOrderID

 

Results are shown here:

 

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43659        676         1

43660        117         2

43661        442         3

43662        227         4

43663        510         5

43664        397         6

43665        146         7

43666        511         8

43667        646         9

...More

Alternatively if you have an ORDER BY clause in your result set different than your ORDER BY in your ROW_NUMBER() expression

--Row_Number using a unique value, different order by

Select SalesOrderID, CustomerID,

            Row_Number() Over (Order By SalesOrderID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID --Different ORDER BY than in Row_NUMBER

 

The result is shown here:

 

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           202

44501        1           843

45283        1           1625

46042        1           2384

46976        2           3318

47997        2           4339

49054        2           5396

...More

If you choose the ROW_NUMBER() function to run against a non-unique column, it will break the tie and still produce a running count so no rows will have the same number. For example, CUSTOMERID can repeat in this example and there will be several ties, SQL Server will just produce a monotonically increasing number, which means nothing other than the number in the result set as shown here:

Select SalesOrderID, CustomerID,

            Row_Number() Over (Order By CustomerID) as RunningCount

From Sales.SalesOrderHeader

Where SalesOrderID>10000

Order By CustomerID

The result are shown here:

SalesOrderID CustomerID  RunningCount

------------ ----------- --------------------

43860        1           1

44501        1           2

45283        1           3

46042        1           4

46976        2           5

47997        2           6

49054        2           7

50216        2           8

51728        2           9

57044        2           10

63198        2           11

69488        2           12

44124        3           13

. . . More

Tomorrow RANK()...

posted on Tuesday, December 07, 2004 4:19:45 PM (Eastern Standard Time, UTC-05:00)  #    Comments [10] Trackback
# Wednesday, August 25, 2004

Northwind is oficially dead, it will not ship with Yukon. This is good, Northwind was bad, very bad.

posted on Wednesday, August 25, 2004 5:59:29 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [14] Trackback
# Monday, May 10, 2004

Today I gave software developers in the Netherlands a preview of the XML Features of SQL Server 2005 or better know by its code name “Yukon” (the US National Park, not the Canadian frozen land). I show off the very cool and advanced XML to Relational mapping. (Clemens is a big fan). I then talk all about the deep XML integration via the XML Data type. This is a native SQL Server type so we have the option to store XML in its native format. For example you can do just about anything a standard datatype can do, excluding Foreign Keys and PKs. Here is the XML Data Type at work in a variable:

 

DECLARE @xmlData AS XML

SET @xmlData = (SELECT * From Customers where CustomerID='ALFKI'  FOR XML AUTO, TYPE)

SELECT @xmlData

 

The results look like this:

<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545"/>

 

(1 row(s) affected)

 

Then it is fun to create tables with XML fields in them and do things like create an index on them, etc.

--create the table with the XML Datatype

CREATE TABLE OrdersXML

     (OrderDocID INT PRIMARY KEY, xOrders XML NOT NULL)

 

Then you can insert some XML into the table, you can do this manually, from an XML Raw statement or via a XML file.

--Get some XML Data, can also use a file

DECLARE @xmlData AS XML

SET @xmlData = (SELECT * From Orders FOR XML AUTO, TYPE)

 

--insert into the table

Insert Into OrdersXML (OrderDocID, xOrders) Values (1, @xmlData)

 

Then let’s add an index on that column:

CREATE XML INDEX idx_1 ON OrdersXML (xOrders)

 

It is more fun to create an XML Schema as data validation for data validation. This is my favorite thing about the XML integration, it gives you the best of both worlds. This shows off the deep integration of the XML model and the relational model, the XML Schema works just like a constraint, allowing us to validate the XML that we put into our XML data type fields in the database.

 

--xml schema as validation

use northwind

CREATE XMLSCHEMA

'<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"

            xmlns:tns="http://corzen.com/customer"

            targetNamespace="http://corzen.com/customer" >

   <xsd:simpleType name="personAge" >

     <xsd:restriction base="xsd:float" >

       <xsd:maxInclusive value="65" />

       <xsd:minExclusive value="18" />

     </xsd:restriction>

   </xsd:simpleType>

   <xsd:element name="age" type="tns:personAge" />

</xsd:schema>'

GO

 

-- create table with xml column and use schema as a 'constraint'

CREATE TABLE xml_schematest (

   the_id INTEGER,

   xml_col XML('http://corzen.com/customer')

)

GO

 

-- works

INSERT INTO xml_schematest VALUES(1,

 '<p:age xmlns:p="http://corzen.com/customer">55</p:age>')

GO

 

-- fails, age > 65

INSERT INTO xml_schematest VALUES(2,

 '<p:age xmlns:p="http://corzen.com/customer">100</p:age>')

GO

 

-- fails, column is validated on update also

UPDATE xml_schematest

  SET xml_col = '<p:age xmlns:p="http://corzen.com/customer">100</p:age>'

  WHERE the_id = 1

 

DROP TABLE xml_schematest

GO

 

DROP XMLSCHEMA NAMESPACE 'http://corzen.com/customer'

GO

 

 

But wait, there’s more! I show off a little bit of XQuery. What is cool is that XQuery is integrated fully with TSQL.

 

--XQuery

use adventureworks

--take a look at an XML column

select * from ProductModel where productmodelid=19

 

--query out a piece of data from the xml field

SELECT Name, CatalogDescription::query('

namespace PD="http://www.adventure-works.com/schemas/products/description"

<Product ProductModelID="{ /PD:ProductDescription[1]/@ProductModelID }" />

') as Result

 

FROM ProductModel

 

where Name like 'Mo%' and CatalogDescription::exist('

namespace PD="http://www.adventure-works.com/schemas/products/description"

namespace wm="http://www.adventure-works.com/schemas/products/WarrAndMain"

     /PD:ProductDescription/PD:Features/wm:Warranty ') = 1

 

 

posted on Monday, May 10, 2004 3:45:37 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Friday, February 27, 2004

Yukon Lays Down the Security Gauntlet

 

Microsoft is taking its “Secure by Default” motto very seriously in the next version of SQL Server code named Yukon. SQL Server PM Tom Rizzo said recently while it is too early to tell, Yukon will ship with a lot of features off by default and secure by default.

 

Here is my advice to the team, after setup, FORCE the installation to not work until you change the SA password.

posted on Friday, February 27, 2004 4:53:53 PM (Eastern Standard Time, UTC-05:00)  #    Comments [2] Trackback
# Monday, December 29, 2003

.NET Stored Procedures in Yukon

You have heard about .NET Stored Procedues in Yukon. You have also heard about the power of TSQL. So which to use, TSQL or .NET? Here is the best rule of thumb. TSQL is for data access. Just about 99% of the time you will want to use TSQL to return data. When you want to do heavy CPU computational "things" you will want to use a CLR Language like C# or VB .NET. Here are some scenarios where a CLR language is much better:

1. Your queries have very complex procedural logic and require intensive computation
2. You need to access external resources like a text file or event log
3. You want to take advantage of .NET Base class libraries (like RegEx for example)
4. You want to leverage you database code across multiple projects

How does this work? You create a .NET assembly in Visual Studio and use what is called the In-Process Service Provider, or ADO in-proc for short. To use the In-Process Service Provider you can use the System.Data.SqlServer namespace to get access to all the classes in ADO In-proc. Then using your .NET language and ADO In-Proc you write some stored procedures, etc and then compile the assembly and bind it to your database. Now you can call your new database objects. Let's take a look at how to do that. Let's get started. Open up Visual Studio (you will need a beta of the next version of Visual Studio) and select "SQL Server Project" from the New Project dialog box. After you set your project type to SQL Server Project, Visual Studio asks you to set a "Add Database Reference" or basically set an active connection. Lastly, add a new item to your Visual Studio project like normal and choose a "stored procedure" from the dialog.

Ok, we are now ready to start coding. We are going to return a single value back to the calling application by querying the database using the SqlServer namespace classes and returning a single value. The first thing that you will notice is the namespaces used, by default when Visual Studio loads up a Stored Procedure project it adds the three new namespaces for you:

sing System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

These are the namespaces that you will use in your .NET Stored Procedure. The most important one is the SqlServer namespace. Here is a simple SP.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
using System.Data.SqlTypes;

public class CSingleValue
//My first .NET Stored Procedure! :)
{
[SqlProcedure]
public static int SingleValue()
{
//Use a SqlCommand object and the SqlContext
 //SqlContext will always return you an active
 //command (or what you ask for) from the current
 //database that exists for your project
 //notice a lot like SqlClient, but no connection
 SqlCommand cmd = SqlContext.GetCommand();

 //now that we have an active command we are going
 //to set the command text to return a single value
 //here we are going to send back the total sales
 //figure in the Northwind database
 cmd.CommandText = "select Sum((UnitPrice*Quantity)-Discount) From [Order Details] As TotalSales";

 //now we have to execute a datareader to get the single row
 SqlDataReader dr = cmd.ExecuteReader();

 //our datareader has the value in it
 //now do something with it, like return it to
 //as the result of the procedure
 dr.Read();

 int intTotalSales = (int)dr["TotalSales"];

 //return the  value
 return intTotalSales;
}
};

Let's dissect the code. First you use a SqlCommand from the SqlServer namespace and create it via the SqlContext object. SqlContext is your friend. For starters it will always give you the current connection of the calling function and in this case set all of the command's behaviors due to the scope of the connection. The next line of code sets the CommandText property of the Command object to a valid SQL statement. Then we create and execute a SqlDataReader and read the results into a variable and return that value.
Easy no? Well we are not there just yet.  You have to bind your stored procedure to your database. Visual Studio should do this for you, but here is how to manually bind it.

Create Assembly SqlServerProject1
From 'C:\SqlServerProject1.dll'
With Permission_Set=SAFE

You have three options with the permissions:
1. Safe
2. External_Access
3. UnSafe

Safe will only permit you access inside the database objects, unable to access the outside world. The assembly must be type-safe. External_Access gives you all of safe, but allows you to access the outside world, files, system resources, etc. UnSafe does not have to be type-safe and can call unmanaged code. Now you have to bind your procedure to the database, once again Visual Studio should do this for you, but here is how to manually do it:

Create PROCEDURE SingleValue
As
External Name SqlServerProject1:[SqlServerProject1.CSingleValue]::SingleValue

Calling your stored procedure is quite simple, just call it like any other stored procedure in SQL Workbench or in client side ADO .NET code:

Exec SingleValue

 

posted on Monday, December 29, 2003 5:37:21 PM (Eastern Standard Time, UTC-05:00)  #    Comments [1] Trackback
# Friday, December 19, 2003

Exception Handling in Yukon Transactions

 

Ok, it is Friday, so an easy one to digest, but quite powerful. Yukon has made a major improvement in Error Handling inside of TSQL transactions. You can now catch Transaction Abort Errors using the TRY / CATCH model without any loss of the transaction context. This makes me very happy. The syntax is here:

 

BEGIN TRY

sql statement

END TRY

 

BEGIN CATCH TRAN_ABORT

    sql statement

END CATCH

See my article on SQLJunkies.com.

posted on Friday, December 19, 2003 1:29:41 PM (Eastern Standard Time, UTC-05:00)  #    Comments [16] Trackback
# Thursday, December 18, 2003

New Data Types in Yukon

 

There are a few very cool new types. The most noticeable are categorized as: DateTime types, varchar(max), and XML. There are a few datetime datatypes, but the most interesting new DateTime datatypes are the UtcDateTime, Date and Time types The UtcDateTime datatype is time zone aware and is used in scenarios where the server and clients span multiple time zones. It uses an expanded range (from 1 AD for 9999 AD) and precision (100 nanoseconds.) The Date datatype is useful when you just want to store the simple date and has the same expanded range. The Time is a new datatype for Time only and has expanded precision (100 nanoseconds.) Varchar(Max) is an extension to varchar, nvarchar, varbinary that will store data up to 2GB and is an alternative to text/ntext/image. It uses the Uses MAX size specifier, like so:

 

CREATE TABLE TablewithMaxColumn

(Customer_Id int, CustomerLifeStrory varbinary(max))

 

All string functions operate on varchar(max), you can use the TSQL SUBSTRING to read chunks and the UPDATE statement has been enhanced to support update of CHUNKS.

 

To accommodate the growing importance of XML Yukon includes an XML datatype. In the past developers would store XML in a varchar or text/ntext field. That was not optimal since the XML was not in its native format and you could not query its contents efficiently. The new datatype allows developers to store XML in its native format (well actually it is stored in a BLOB field) and gives you the ability to via XQuery to query parts of the XML field.

 

The XML datatype is a full blown TSQL citizen, able to have table columns and participate in a CAST or CONVERT statement. You can even use the FOR XML statement to convert tabular data into XML from a SQL query:

 

DECLARE @xmlData AS XML

SET @xmlData = (SELECT * From Customers FOR XML AUTO, TYPE)

SELECT @xmlData

 

As expected the results of a row look like this:

 

<Customers CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" ContactName="Maria Anders" ContactTitle="Sales Representative" Address="Obere Str. 57" City="Berlin" PostalCode="12209" Country="Germany" Phone="030-0074321" Fax="030-0076545"/>

 

You can also insert this XML data into a new XML field in a table and have data validations based on XSD be enforced by Yukon. Pretty cool stuff.

See my article on SQLJunkies.com.

posted on Thursday, December 18, 2003 3:14:36 PM (Eastern Standard Time, UTC-05:00)  #    Comments [16] Trackback
# Wednesday, December 17, 2003

Common Table Expressions in Yukon

 

A Common Table Expression closely resembles a non-persistent view. A CTE is a temporary named result set that you define in your query that will be used by the FROM clause of the query. Each CTE is only defined once (but can be referred to as many time as it is in scope) and lives for as long as the query lives. CTEs can also be used to perform recursive operations. Here is the syntax to create a CTE:

 

With <name of your CTE>(<column names>)

As

(

<actual query>

)

 

Select * from <name of your CTE>

 

An example of a simple CTE is shown here:

 

With LondonCustomersCTE

As

(Select * From Customers Where City='London')

 

Select * From LondonCustomersCTE

 

Here is an example where we get a list of all the count of Customers’ orders in the orders table as a CTE and then a simple inner join with the Customer’s table to return the full name of the customer. (Sure this can be done without a CTE, but think about the ease of aggregating in the CTE only, it makes your code much easier to deal with.)

The code is here:

 

With CustomerOrderCountCTE(CustomerID, OrderCount)

As

(

     select CustomerID, Count(*)

     from orders

     group by CustomerID

)

 

Select CU.CompanyName, City, COC.OrderCount

From Customers As CU Inner Join CustomerOrderCountCTE As COC

     on CU.CustomerID = COC.CustomerID

Order By COC.OrderCount Desc

 

The true power of CTEs are when you use then recursively. A recursive CTE is constructed from a minimum of two queries, the first or Anchor Member (AM) is a non-recursive query and the send is the recursive query or the Recursive Member (RM). The AM and RM are separated by a UNION ALL statement. Here is the syntax:

 

With SimpleRecursive( field names)

As

(

     <Select Statement for Anchor Member>

 

     Union All

    

     <Select Statement for the Recursive Memember>

)

 

Select * From SimpleRecursive

See my article on SQLJunkies.com.

posted on Wednesday, December 17, 2003 12:25:22 PM (Eastern Standard Time, UTC-05:00)  #    Comments [4] Trackback
# Tuesday, December 16, 2003

TOP Enhancements in Yukon

 

In previous versions of SQL Server TOP allowed you to limit the number of rows returned as a number or percentage in Select Queries. I use this all the time. It has gotten more flexible in Yukon where TOP can be used in Delete, Update and Insert queries. What is even cooler is that Yukon will allow you to specify the number of rows (or percent) by using variables or subqueries. I love the ability to dynamically set the TOP, so much that I have started writing Stored Procedures that accept a NumberofRows parameter like so: 

 

Create Procedure usp_SEL_ReturnTopOrders

@NumberofRows int

As

Select TOP (@NumberofRows) OrderID

From Orders

Order By OrderID

 

Executing the stored procedure is easy, just pass in how many records that you want (in this case it is 100):

 

usp_SEL_ReturnTopOrders 100

 

Using a subquery can be very powerful when you are doing things on the fly. A real simple example to demonstrate the concept is show here, we are getting the TOP n customers based on how many records we have in our Employees table:

 

Select TOP (Select Count(*) from Employees) *

From Orders

See my article on SQLJunkies.com.

posted on Tuesday, December 16, 2003 4:31:23 PM (Eastern Standard Time, UTC-05:00)  #    Comments [8] Trackback
# Monday, December 15, 2003

PIVOT Data in Yukon (RIP Case and Rozenshtein)

 

Let’s face it, users usually want to see data in tabular format, which is a bit of a challenge when we usually store data in a highly relational form. PIVOT is a new TSQL operator that you can specify in your FROM clause to rotate rows into columns and create a traditional “Crosstab” query without using CASE, Rozenshtein or subqueries. You have always been able to do this easily in Access with Pivot and Transform, but SQL Server was always a step behind.

 

Using PIVOT is easy. First in your select statement you need to specify the values you want to pivot on, in the following example, we will use the Year of the Order. Our FROM clause looks normal except for the PIVOT part. The PIVOT statement creates the value we plan on showing in the rows of the newly created columns, in this case I am using the aggregate SUM of the TotalAmount (a calculated field in our FROM clause). Then we have to use the FOR operator to list the values we are going to Pivot on in the OrdersYear column.  The example is shown here:

 

SELECT CustomerID, [1996] AS 'Y1996', [1997] AS 'Y1997', [1998] AS 'Y1998', [1999] as 'Y1999'

FROM

     (SELECT CustomerID,OD.UnitPrice * OD.Quantity - OD.Discount AS TotalAmt,

          Year(dbo.Orders.OrderDate) as OrdersYear

          FROM dbo.Orders INNER JOIN dbo.[Order Details] As OD

              ON dbo.Orders.OrderID = OD.OrderID)

          As Orders

     PIVOT

     (

          SUM(TotalAmt)

          FOR OrdersYear IN([1996], [1997], [1998], [1999])

     ) AS XTabData

Order BY CustomerID

 

The results look like this:

CustomerID

Y1996

Y1997

Y1998

ALFKI

NULL

2293.25

2301.9

ANATR

88.8

799.75

514.4

ANTON

403.2

6451.15

660

AROUT

1379

6588.4

5838.4

BERGS

4324.4

14532.25

8108.5

BLAUS

NULL

1079.8

2160

BLONP

9986.2

8371.05

730

BOLID

982

4035.3

279.8

BONAP

4202.35

12460.7

7184.7

 

That is all there is to it. Of course this is a real simple example to show you the new concept, you can then of course get more sophisticated aggregates and even use Common Table Expressions in the FROM clause. Also you can use the UNPIVOT operator to normalize data that is already pivoted.

 

See my article on SQLJunkies.com.

posted on Monday, December 15, 2003 4:44:32 PM (Eastern Standard Time, UTC-05:00)  #    Comments [3] Trackback
# Monday, November 10, 2003

Gentlemen, Start your Word Processors

 

I am one of three authors of the Microsoft Press book on Developing/Programming for SQL Server “Yukon” due out when the product ships. Last night I met with fellow authors Andrew Brust and Bill Zack at Andrew’s house and discussed the final outline, chapter assignment and writing schedule. Bill has written an article on the new SQL Server Service Broker for SQL Server magazine, so look for that in the March issue and I have written and article for MSDN magazine for the February issue on the new TSQL improvements, IDE and CLR Stored Procedures. These of course are excepts from chapters, so the writing process has officially begun.

 

This is going to be a great book since we will target .NET developers more than the DBA, while covering all the administrative features and tools. Since performance is as much of a developer concern as DBA concern, expect lots on performance tuning and optimization. I can’t post the outline and sample chapters here just yet (stay tuned, one day I can), but I can tell you we cover everything from soup to nuts. I was assigned (actually asked) for the chapter on XQuery, XML and the XML Datatype. Clemens must find that so funny!

posted on Monday, November 10, 2003 4:10:13 PM (Eastern Standard Time, UTC-05:00)  #    Comments [2] Trackback