 Thursday, April 23, 2009
TechEd Session: Solve problems without spending any money
As we all know by now, I hate twitter. Why, why, why do you think I care about when you are picking your nose? Here is a really funny (and accurate!) list of reasons why twitter sucks. With that backdrop, my good friend Mary Chipman is trying to trying to convince me that some people are twittering useful stuff. She is correct, she twitters here, mostly on SQL Server and other data related stuff. That said, I still won’t use twitter since there is still way too much noise. Mary and I are doing a session at TechEd next month called “Access and SQL Server: Solve problems without spending money.” In this session we look at a few use cases where it makes sense from both a technological and business perspective to use the wiz-bang features of Access to augment your .NET and SQL Server solutions. We are not advocating using Access as a development platform, just as an augment to your solution. You do this with Excel all the time, why not use a relational engine with build in reporting as well? Let me give you an example. Let’s say you have an enterprise application written in .NET with a SQL Server back end. You are a PR firm and the system takes inputs from some other systems of press and PR items. Then the data is transformed and put into data warehouse tables and viewed on the web via an ASP.NET app and SQL Server Reporting Services. Now the boss tells you that you have to track twitter. You protest! But the boss insists. The problem is that twitter has so much junk in it and you can’t accept a raw feed into your enterprise application like you do for press releases, etc. You ask your developers to build an app that will pull in the twitter feeds via its RESTful API and store the tweets locally to give you the ability to rate the tweets relevant or irrelevant and then upload to the enterprise database to flow into the data warehouse and .NET app. They say, sure, but it will take a little while to build the app but they are busy on higher priority stuff, so they can’t get started. So why not as a stop gap, just build a simple little Access app that uses VBA to call the twitter API and allow you to download the tweets into a local Access table, and then you can scroll through the data and click a “relevant” field as true/false. You can build this mini-solution in about 15 minutes. We’ll show you how. Now just to be uber geeks, we also will want to get that data back into the enterprise system. The enterprise system has a locked down table structure (good!) so the only way to get data in is via a stored procedure. This stored procedure will only accept a table-valued parameter. Based on Mary’s MSDN white paper, we’ll show you how to do that too. Hope to see you there, Tuesday May 12th after lunch. We have a few other scenarios to show you, some with Sharepoint (which are really cool), some with agile prototyping, and some using Access reporting for some solutions for annoying power users.
Wednesday, April 08, 2009
Thursday, January 29, 2009
Tuesday, January 20, 2009
RESTify your Data Driven Applications: User Group Meeting on Thursday
I will speaking at the SQL Server User Group on Thursday at 6pm. You must register to attend: http://www.clicktoattend.com/?id=134822 Location: Microsoft , 1290 Avenue of the Americas (the AXA building - bet. 51st/52nd Sts.) , 6th floor Directions: B/D/F/V to 47th-50th Sts./Rockefeller Ctr 1 to 50th St./Bway N/R/W to 49th St./7th Ave. Session Info:
Applications today are expected to expose their data and consume data-centric services via REST. In this session we discuss ADO .NET Data Services or “Project Astoria” and see how we can REST enable your data. Then you will learn how to leverage existing skills related to LINQ and data access to customize the behavior, control-flow, security model and experience of your data service. We will then see how to enable data-binding to traditional ASP.NET controls as well as Silverlight Then switching gears we will look quickly at consuming of REST services from any platform (including Ruby on Rails) using Visual Studio and the WCF REST Starter kit. We will conclude with a discussion on developing offline applications with the ability to sync back to the online data service. This is a very demo intensive session.
Tuesday, November 25, 2008
TSQL Enhancements in SQL Server 2008
They filmed my talk at TechEd last week and made if available on the internet. You can watch here. Unfortunately the video of the code (about 85% of the session) is not as clear as I would like but you should be able to see it.
Saturday, November 08, 2008
Using a TSQL Common Table Expression to Find Dupe Records
Here is a cool TSQL tip I will be showing off next week at TechEd Europe at one of my sessions (Rock Star Common Table Expressions). Let’s say you have a product table that looks like this: 
As you can see in the image, we have some duplicate data. You can use a Common Table Expression in SQL Server 2005 or 2008 to look up the “parents” using an aggregate HAVING clause. Then you join to the CTE to the Product table on the Product_Name field as well as the Product_ID field, but using the > indicator, so we only return the “children” or products with a higher product ID. Here is the CTE and code: --Find the Dupes with a CTE WITH CTEMinProductRecords AS ( SELECT MIN(Product_ID) AS Product_ID, Product_Name FROM Products GROUP BY Product_Name HAVING COUNT(*) > 1 ) SELECT cte.Product_ID as DupeProductID, cte.Product_Name as DupeProduct, p.Product_ID as ParentID, p.Product_Name as ParentProduct, p.Price as ParentPrice FROM Products p JOIN CTEMinProductRecords cte ON p.Product_Name = cte.Product_Name AND p.Product_ID > cte.Product_ID Here are the results: 
Let’s say you want to automatically delete the children. While the business case for this may exist (my old business did have a rule, the higher product id was the dupe), you will want to update all of the “many” tables to the lower product ID first. To then do the delete using the CTE all you have to do is convert the above select statement to a delete statement: WITH CTEMinProductRecords AS ( SELECT MIN(Product_ID) AS Product_ID, Product_Name FROM Products GROUP BY Product_Name HAVING COUNT(*) > 1 ) DELETE Products FROM Products p JOIN CTEMinProductRecords cte ON p.Product_Name = cte.Product_Name AND p.Product_ID > cte.Product_ID Enjoy!
Wednesday, November 05, 2008
Table Value Parameters Make Life Easier for C# Developers
Yesterday I showed how there are some new language constructs in TSQL that will be very familiar to C# developers. I will be doing three talks on TSQL next week at TechED Developers in Barcelona and the talks are designed for application developers. (But database geeks will enjoy these sessions as well!) If you write any TSQL code in your applications today, or interact with it, these sessions will give you some advice and help change the way you approach particular problems. Today I want to show a new feature about using Table Valued Parameters (TVP) in a stored procedure and then calling it from an ASP .NET page or any other .NET client using ADO.NET. A TVP is new in SQL Server 2008, so you can now declare a type of TABLE, insert data into it, and then pass it to a stored procedure. Have you ever passed in a CSV string to a DAL or stored procedure or even an XML data type, then parsed it and executed some SQL? TVP will eliminate that. Here is an example: First create a new table for us to play with: Create Table TVP_TEST (Field_ID int primary key, Field_DESC varchar(10))
Next create the TVP by declaring a new type. Notice that I have created the type to be exactly the same shape as the underlying table, this is not a requirement, however, it is important to do so if you are going to be doing inserts as we do below. CREATE TYPE TVP_TESTType AS TABLE( Field_ID int, Field_DESC varchar(10)) Next create a variable as type TVP_TESTType and insert some data into it, insert is done the same way we would add data to a normal table. Notice that this only puts data into the variable, NOT the underlying table: --put data into the type DECLARE @MyTable as TVP_TESTType --put data into the type INSERT INTO @MyTable VALUES (1, 'Test'), (2, 'Next Test') Now you can insert data into a table very easily (remember @MyTable has to be in scope): INSERT INTO TVP_TEST SELECT * FROM @Mytable To get really useful, let’s create a stored procedure that will accept as a parameter the TVP_TESTType data type and insert data into the TVP_TEST table using the parameter, notice no parsing of CSV strings, etc: CREATE PROCEDURE usp_ins_MyTable (@MyTVP as TVP_TESTType READONLY) As INSERT INTO TVP_TEST SELECT * FROM @MyTVP
You can call this stored procedure as so, as long as @MyTable is in scope and is filled with data: exec usp_ins_MyTable @MyTVP=@MyTable
But what is really exciting is calling this data from a .NET client. If you have an ADO .NET dataset that is the same shape of the TVP you can pass it in from the client to the server, greatly enhancing data access and batch operations.You can pass in a dataset as a parameter using the SqlDbType.Structured as its data type. Here is some code from C#, just remember that ds is our active dataset filled with data and shaped the same way as TVP_TESTType: SqlCommand cmd = new SqlCommand("usp_ins_Portfolio", conn); cmd.CommandType = CommandType.StoredProcedure; //add the ds here as a tvp SqlParameter sp = cmd.Parameters.AddWithValue("@Portfolio", ds.Tables[0]); //notice structured sp.SqlDbType = SqlDbType.Structured; cmd.ExecuteNonQuery(); Pretty easy! Now you can pass data sets in as parameters to a stored procedure, eliminating some funky parsing on either the client or server.
Tuesday, November 04, 2008
TSQL 2008-Closer to C#
I am doing a talk next week at TechED Developers in Barcelona about new TSQL enhancements for developers. The session is: DAT313 T-SQL Enhancements in SQL Server 2008 : Getting Down and Dirty. it is Wednesday at 1:30 in room 115. The goal of this talk is to show you some cool new stuff in TSQL but also to show C# and VB developers that TSQL is not all that bad. I get the impression that developers hate TSQL. :) So the TSQL designers decided to add a few “delighters” to the syntax to make C# and VB developers feel more at home. The first is variable assignment at declaration time. For example:
DECLARE @MSFT char(4) = 'MSFT' Print 'Using: ' + @MSFT The next is using shortcut syntax like += or –+, etc: UPDATE Portfolio SET Qty += 10 WHERE Symbol='MSFT' The addition of Table Value Parameters (TVPs) allows you to pass in datasets via ADO.NET in C#/VB as a parameter to a stored procedure, etc, will really make C# developers life easier (demo soon on the blog). Also cool integration with Google Maps or Virtual Earth and the geospacial data types will make developing apps with location much easier. There are lots of other cool new features in there for developers, I will post some more on the blog soon.
Wednesday, October 29, 2008
Monday, June 30, 2008
SQL Server 2008 RC0-Lost a Few Hours
Yesterday was my last day of MBA classes and needless to say I was out partying hard last night. I woke up early for some strange reason and decided to get a jump on my book chapters for the upcoming Programming SQL Server 2008 from Microsoft Press. My VPC died on me so I decided to give SQL Server 2008 RC0 an install on a spare machine (what better to do on a Sunday morning with a hangover). I remember reading that if you have SQL Server 2005 Express edition installed you will have trouble installing RC0. So I went in and uninstalled everything on a spare machine, too hungover to reformat. I figured I am an MBA now, I should make myself more efficient. Well, even with nothing left of SQL Server 2005, I still got a very mean error message (well after I entered in all the account, mixed mode, information): "The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools." I was not happy since I had already removed it. So I started removing Visual Studio 2005 too. That did not work. So I decided the next thing to do was Google. Someone else had to have had this pain already. Found this post from Jason Follas. He said that if you have Red Gate SQL Prompt software installed, as I did, it leaves a bad registry key and showed how to fix it. Read the blog post here. I can't live without Red Gate SQL Compare and SQL Data Compare so I did not mind losing some time. They save me day and days of time over the year. The good news is that Red Gate left a note on Jason's blog that the issue has been resolved. I guess the MBA was lost on me. Oh well, at least I can write TSQL and google for install problems...
Friday, June 27, 2008
Impedance Mismatch
Unless you have been living under a rock, you should already know about the controversy over the nHibernate mafia's Microsoft Entity Framework's Vote of No Confidence. The manifesto says that the upcoming Entity Framework (to be released soon via .NET 3.5 SP1) is just not usable. As far as ORMs go, there are some valid points working against EF- in order of (my) importance: - Lack of Lazy Loading (The EF team said that they had "explicit" lazy loading, but that is just playing with words and the team has since retracted from that.)
- Lack of persistence ignorance (at best this is inelegant design, at worst it will cripple the performance of your application and make it a burden to maintain)
- Too many merge conflicts when using a model in a source control environment
- Bad design for TDD development (and in my opinion, CI as well)
The manifesto also brings up some Domain Driven Design issues that I feel are less important than the above four bullets. To their credit the normally reserved EF team has responded quickly and nicely to the manifesto. On the heels of last week's transparent-design process announcement, Tim Mallalieu gave a detailed reply which has started a good discussion and wiki. They are listening and that is good. Since the Entity Framework is starting work this week on v.next, they have also put together an advisory council including Eric Evans, Martin Fowler, Paul Hruby, Jimmy Nilsson, and myself. To some degree the vote of no confidence worked, they got Tim's attention. I think that the manifesto has some very valid points, but I also think it lacks professionalism. Anyone can say something sucks, it is more important to give valid feedback, suggest solutions, and engage in a dialog. (The vote of no confidence was just too in your face-and a dialog was stated only because of the professionalism of the EF team.) In addition there are some mafia style attacks on anyone who does not agree with them, most recently against the always honest and open Julie Lerman. So this blog post sounds like an endorsement of the Entity Framework and ORMs in general, right? Wrong. My first problem with ORMs in general is that they force you into a "objects first" box. Design your application and then click a button and magically all the data modeling and data access code will work itself out. This is wrong because it makes you very application centric and a lot of times a database model is going to support far more than your application. In addition an SOA environment will also conflict with ORM. I prefer to build the application's object model and the data model at about the same time, with a "whiteboarding" approach that outlines the flows of data and functionality across the business process and problem set. Maybe it is the MBA talking but I tend to be "business and customers first" when I design a system. (Those of you that know me know that I have designed some very large and scalable systems in my day.) I usually like to "follow the money" as Ron Rose of Priceline taught me 10 years ago. In addition, I prefer a very agile design process where you are constantly making changes to your design in the early sprints, the database and object model are both part of this process. My second major problem with ORMs is that ORMs are a solution to a problem that should not be solved. Developers who write object oriented and procedural code like C# and Java have trouble learning the set-based mathematics theory that govern the SQL language. Developers are just plain old lazy and don't want to code SQL since it is too "hard." That is why you see bad T-SQL: developers try to solve it their way, not in a set-based way. The premise of EF, LINQ, nHibernate, and Luca Bolongese’s original premise with ObjectSpaces, is that set-based theory causes an “impedance mismatch” between data access and all the other (more procedural) coding we do. And it’s ORMs to the rescue to resolve the impedance mismatch. So ORMs are trying to solve the issue of data access in a way that C# and VB developers can understand: objects, procedural, etc. That is why they are doomed to fail. The further you abstract the developer from thinking in a set-based way and have them write in a procedural way and have the computer (ORM) convert it to a set-based way, the worse we will be off over time. What I am saying (and have been saying for a long time) is that we should accept, no, embrace the impedance mismatch! While others are saying we should eradicate it, I say embrace it. ORM tools should evolve to get closer to the database, not further away. One of the biggest hassles I see with LINQ to SQL is the typical many-to-many problem. If I have a table of Ocean Liners, vessels, and ports, I’ll typically have a relational linking table to connect the vessels and ports via a sailing. (Can you tell I am working with Ocean Freight at the moment?) The last thing I want at the object layer is three tables! (And then another table to look up the Ocean Liner that operates the vessel.) Unfortunately, this is what most tools give me. Actually I don't even want one table, I want to hook object functionality to underlying stored procedures. I really want a port object with a vessel collection that also contains the ocean liner information. At least the Entity Framework does this however I have major concerns about the performance of the code it produces. The ironic thing I’m now seeing is developers who are lazy and don't want to learn SQL using tools that will produce SQL for them. The SQL is bad, and now those same anti-SQL lazy developers are struggling to read pages of generated and near-unreadable SQL trying to solve performance problems. They’re dealing with SQL that’s more verbose and orders of magnitude harder to understand than what was needed in the first place! So where do we go from here? We can't just keep pretending that this mismatch problem can be solved and keep throwing good money after bad. As Ted Neward said that is the Vietnam of Computer Science. I say that developers should start embracing the impedance mismatch. I also say that Microsoft and other ORM vendors need to realize that embracing the mismatch is a good thing and design ORM tools that allow this. (This is the advice I will give at the EF council.) I am not anti-ORM (even tho I did trash LINQ on stage at TechEd last year), but I am pro using the right tool/technology for the right job. ORM is real good for CRUD and real bad at other things. Let's start from there. To quote Ted, "Developers [should] simply accept that there is no way to efficiently and easily close the loop on the O/R mismatch, and use an O/R-M to solve 80% (or 50% or 95%, or whatever percentage seems appropriate) of the problem and make use of SQL and relational-based access (such as "raw" JDBC or ADO.NET) to carry them past those areas where an O/R-M would create problems." Hopefully "cooler heads will prevail." Update 6/29/08 My fellow RD Greg Low brought to my attention that my discussion on Linq to SQL above is very similar to an email he sent not to long ago. At Triton Works (the ocean containers) we used his example in our own entity model debate and I realized after he pointed it out to me that the words above are just too close to his, so I am giving him the credit above. (Though he did not call programmers lazy and anti-SQL, I did.) FYI, we went with POAN or Plain Old ADO.NET, ocean containers are old school. The inspiration for this post came from not only the public debate on the EF vote of no confidence but also a private email discussion on nHybernate, EF and Linq to SQL between: Greg, Andrew Brust, Rocky Lhotka, Sten Sundbland, Joel Semeniuk, Barry Gervin, and myself. I won't tell you who was on what side. If you want to know, you will have to ask them. Hint, hint, Canadians are lazy and anti-SQL. :)
Friday, February 29, 2008
SQL Server 2008 XML: XML DML Enhancements
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.
Thursday, February 28, 2008
SQL Server 2008 XML: XQuery Enhancements
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>
Wednesday, February 27, 2008
SQL Server 2008 XML: XSD Enhancements-Union and List Types
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
Tuesday, February 26, 2008
SQL Server 2008 February CTP Installed: New XSD Features for XML Data
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. 
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.
Wednesday, May 16, 2007
Tuesday, February 06, 2007
Thursday, January 25, 2007
Tuesday, December 12, 2006
Corzen's DBA Test
We are hiring a DBA at my company Corzen and instead of looking at resumes/cvs we are looking at how people solve particular SQL problems-not just the solution but the approach they take. (Do you use temp tables, (nolock), CTEs, table alias, etc.)
Here are the five test questions we ask. (If you want to come work here, just email me the answers.)
Here are three TSQL problems that can judge how you approach a SQL problem. There are no real wrong answers, but the more efficient and bulletproof the query is the better. No cheating and no cursors! Don't cheat and don't google the answers (we'll know.)
Step 1: run SetupTSQLProblems.sql in any database.
Problem 1.
Finding Duplicate Products in the Product table. Write a query to list only the duplicated products in the Products table. Assume that the dupe is the product with the highest Product_ID. The results of your SQL statement should be:
|
Dupe_Product_ID |
Dupe_Product_Name |
Dupe_Price |
Parent_Product_ID |
Parent_Product_Name |
|
7 |
Widgets |
88 |
1 |
Widgets |
Problem 2.
Assign rooms to classes based on capacity using the Classes and Rooms tables. Rules: each class should have a room (and NULL if a room is not available). No class can be in a room where there are more students than capacity. No room can be used twice.
The results of your SQL statement should be:
|
class |
students |
room |
capacity |
|
Advanced SQL Queries |
65 |
225 |
70 |
|
Introduction to C# |
52 |
224 |
60 |
|
XQuery Deep Dive |
35 |
222 |
40 |
Problem 3.
Make the following static pivot dynamic in AdventureWorks, assume that the orderyear can increment to 2005 and 2006. Assume that order years can skip.
SELECT *
FROM (SELECT CustomerID, YEAR(OrderDate) AS orderyear, TotalDue
FROM Sales.SalesOrderHeader) as Header
PIVOT(SUM(TotalDue) FOR orderyear IN([2002],[2003],[2004])) as Piv
Problem 4.
You have a piece of XML:
<office id="NYC">
<Employees>
<employee num="1">Steveemployee>
<employee num="2">Alemployee>
<employee num="3">Mikeemployee>
<employee num="4">Malishaemployee>
Employees>
office>
<office id="Cairo">
<Employees>
<employee num="1">Remonemployee>
<employee num="2">Lameesemployee>
<employee num="3">Bassmaemployee>
Employees>
office>
<office id="Pune">
<Employees>
<employee num="1">Vinayemployee>
<employee num="2">Pradeepemployee>
<employee num="3">Rashmiemployee>
<employee num="4">Sujataemployee>
<employee num="5">Ajitemployee>
Employees>
office>
<office id="Karachi">
<Employees>
<employee num="1">Amratemployee>
Employees>
office>
A. Using the XML datatype, write an XQuery to return the 3rd employee in the NYC office. Your results should look like:
B. Using the XML datatype, write an XQuery to return the 3rd employee in each office. Your results should look like:
|
EmployeeName |
|
Mike |
|
Bassma |
|
Rashmi |
Problem 5.
Using adventureworks, rank each salesperson by total sales in 2004 with the rank starting over for each country. Do not use temp tables, self joins, or cursors.
|
CountryName |
SalesPersonID |
TotalSales |
SalesRank |
|
Australia |
290 |
955311.5851 |
1 |
|
Australia |
288 |
35006.4385 |
2 |
|
Canada |
285 |
2411105.152 |
1 |
|
Canada |
278 |
755593.2997 |
2 |
|
Canada |
268 |
15655.2241 |
3 |
|
France |
286 |
1832662.467 |
1 |
|
France |
284 |
2278.3269 |
2 |
|
Germany |
289 |
1121896.781 |
1 |
|
Germany |
284 |
74363.4408 |
2 |
|
United Kingdom |
282 |
1648152.541 |
1 |
|
United Kingdom |
284 |
54036.9765 |
2 |
|
United States |
276 |
2534645.3 |
1 |
|
United States |
275 |
2053782.757 |
2 |
|
United States |
277 |
1824701.882 |
3 |
|
United States |
279 |
1446386.797 |
4 |
|
United States |
281 |
1429353.893 |
5 |
|
United States |
287 |
1114278.877 |
6 |
|
United States |
283 |
901142.2512 |
7 |
|
United States |
280 |
871513.6294 |
8 |
|
United States |
268 |
318200.2683 |
9 |
Enjoy!
Tuesday, November 07, 2006
SQL Server 2005 Service Pack 2 CTP
Microsoft is releasing the SQL Server 2005 Service Pack 2 Community Technology Preview November 2006. The CTP release is scheduled for Tuesday, November 7th.
Some bullets:
§ Support for the upcoming Windows Vista.
§ Data Mining Add-Ins for Office 2007, which enables data mining functionality from SSAS to be used directly within Excel 2007 and Visio 2007.
§ SSRS integration with MOSS 2007, which allows integration with the Report Center in SharePoint providing seamless consumption and management of SSRS reports within SharePoint.
§ SSAS improvements for Excel 2007 and Excel Services relating to performance and functionality.
Heterogeneous Environments (Interoperability):
§ Oracle Support in Report Builder. Users will now be able to use Report Builder on top of Oracle data sources.
§ Hyperion support with SSRS. Organizations will now be able to use SSRS to build reports on top of Hyperion Essbase cubes.
Performance/Enterprise:
§ Data compression (varDecimal), which is important for data warehouse scenarios and is specifically important for SAP BW scenarios. This requires less disk storage of decimal data which increases overall performance.
§ Manageability enhancements. Based on customer feedback, SQL Server provides enhanced management capabilities for DBAs such as improvements in database maintenance plans, enhanced management reports and a new copy database wizard.
SQL Server Express Edition
§ Management reports have been added to SQL Server Express Edition enabling customers to get insights into the performance of their Express Edition and SQL Server Compact Edition databases.
§ SQL Server Management Studio Express Edition now enables management of SQL Server Compact Edition databases.
SQL Server 2005 SP2 will ship shortly after the launch of Windows Vista and Office. You can find more details about the features in SP2 at http://go.microsoft.com/fwlink/?LinkId=71711.
Tuesday, October 24, 2006
SQL Server 2005 XQuery-User Group Talk on Thursday
I will be speaking at the NY Metro SQL Server Users Group on Thursday at 6pm on XQuery in SQL Server. Hope to see you all there.
|
Topic: |
Using XQuery to Retrieve and Manipulate XML Data with SQL Server 2005 |
|
Speaker: |
Stephen Forte, Microsoft Regional Director |
|
Date: |
6:00 PM on October 26, 2006 |
|
Place: |
Microsoft Office in Manhattan |
|
|
The Axa Financial Building |
|
|
1290 6th Avenue, NY, NY |
Due to new security guidelines at the building, you will have an easier time getting in if you confirm your attendance via email to joelax@dbdirections.com. Otherwise you'll have to wait till someone comes downstairs to sign you in. Also remember to have a photo id with you.
Blogs, Web Services and general interoperability have proliferated the use of XML in recent years. With all of that XML out there, there needs to be an easy way to incorporate XML data with SQL Server relational data.
This session will look at how to use XQuery to retrieve and manipulate XML data inside the database. We'll start with a look at the new XML datatype in SQL Server 2005, then the ability to validate with XML Schema (XSD) and then creating XML indexes for use with XQuery statements. After a brief look at the W3C XQuery specification we quickly move to SQL Server’s implementation of XQuery 1.0. We'll incorporate XQuery in SELECT and WHERE clauses to retrieve information as well as see how to manipulate XML data with XQuery DML.
Pizza and refreshment will be served at the meeting, and there will be a drawing for several giveaways.
Sunday, May 28, 2006
Wednesday, May 24, 2006
Red Gate Releases SQL Prompt-Intellisense for SQL Server
Red Gate has released SQL Prompt 2.0, a pretty cool auto-complete and Intellisense for SQL Server Management Studio and Query Analyzer. The best part: t is FREE! Download it here.
When you load it up and connect to a database, it will ask you to connect.
Now when you are typing code in SQL you have real auto-complete and Intellisense!
Tuesday, October 11, 2005
Firehose Security: SQL Server Surface Area Tool-Merge Replication
When I installed the latest beta of SQL Server 2005 and ran the Merge Replication wizards to create a Publication, I got a strange new error not present in previous betas. The error said that the “XP Agents are not enabled and you must run sp_configure to enable them.”
I figured that MS did the right thing and turned this feature off by default. I ran the new SQL Server Surface Area Configuration Tool and could not find anything for enabling XP Agent stored procedures, etc. I did find a lot of good stuff disabled by default that will make SQL Server more secure. This is good since by default out of the box an installation of SQL Server will leave some of the advanced, yet powerful tools disabled to deny a hacker the ability to guarantee that it will be turned on.
So in my case, MS turned it (XP Agents) off but do not give a way in the tool to enable it. After some trial and error and help from help I got this to make it all work and my publication agent ran smoothly:
You can't just run sp_configure, you first have to turn on advanced options so SQL Server knows it exits:
use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
go
RECONFIGURE
GO
It is good that this stuff is turned off by default, it will just mean a little more time for us developers to setup and deploy. Worth the tradeoff.
Saturday, October 08, 2005
SQL Server 2005 XQuery: xml.exist()
SQL Server 2005 allows you to store XML in a native data type. That is cool, however, having XML in the database is almost useless unless you can query the elements and attributes of the XML data natively. XQuery becomes very useful when you can use it to search based on the values of a particular element or attribute. The xml.exist() function accepts an XQuery as input and returns 0, 1, or NULL, depending on the result of the query; 0 is returned if no elements match, 1 is returned if there is a match, and NULL is returned if there is no data to query on. For example we will verify if a node exists in this particular XML string of classes.
DECLARE @XML xml
Set @XML='
<classes>
<class name="SQL Server Index"/>
<class name="SQL Precon"/>
</classes>
'
Select @XML.exist('/classes')
The code above returns a 1 since the “classes” element exists in the XML variable. If you change the XQuery expression to search for an XML node that does not exist like, Select @XML.exist('/dogs), then it will return 0.
You can see this in action as part of a CHECK CONSTRAINT. SQL Server will not allow you to use an xml.exist as part of a CHECK CONSTRAINT. So you have to first create a user defined function (UDF) to perform the action. This UDF accepts an XML field and retunes the value of an xml.exist() method looking for an instance of < Orders'>:
USE AdventureWorks
GO
CREATE FUNCTION dbo.DoesOrderXMLDataExist
(@XML XML)
RETURNS bit
AS
BEGIN
RETURN @XML.exist('/Orders')
END;
GO
To use this UDF as a CHECK CONSTRAINT, just create a table and pass the column you want to apply the constraint to the UDF you just created.
--create the table using the function
CREATE TABLE OrdersXMLCheck
(OrderDocID INT PRIMARY KEY,
xOrders XML NOT NULL Default '<Orders/>'
CONSTRAINT xml_orderconstraint
CHECK(dbo.DoesOrderXMLDataExist(xOrders)=1))
Its that simple, now you have a rule enforced on that column making sure that an <Order> element is added to this table.
Tuesday, August 09, 2005
Components of SQL Server 2005 Notification Services
SQL Server 2005 Notification Services is what you would traditionally call “middle ware” or part of an application server. SQL Server is traditionally a “back end” or an infrastructure component to your architecture. SSNS is middleware provided by Microsoft as part of the SQL Server 2005 package. Microsoft has not traditionally provided a lot of middleware, but ever since the success of “Viper” or Microsoft Transaction Server (MTS), Microsoft has been providing more and more reliable and scalable middleware. Since SSNS is middleware and not part of the core database engine, SSNS is a separate setup option that is turned off by default when you initially set up your SQL Server 2005 server. You will need to select SSNS to install it at initial setup.
If you choose to install SQL Server 2005 Notification Services, all of its components and support files install in a separate subdirectory (typically the \Notification Services subdirectory of your default SQL Server installation.). If you are like me and need to know what everything is, you can see by inspecting the subdirectory that SSNS is made up of a few components, the important ones are explained here:
microsoft.sqlserver.notificationservices.dll-this is the actual guts of Notification Services. A managed .NET assembly that contains the core code for SSNS and the built-in providers you use to retrieve data and create notifications and subscriptions.
NSService.exe-the executable shell of microsoft.sqlserver.notificationservices.dll used for running as a Windows Service. Each instance of NSService.exe runs as a Windows Service and can be managed by SQL Management Studio. Instances of NSService.exe are independent of SQL Server 2005 instances.
Providers-is the extensible framework that SSNS is built around: event providers, formatters and delivery protocol providers.
Nscontrol.exe-this is a utility program that is used when you compile your application and generates the SQL Server Database and objects that the Windows Service application uses to retrieve data and create notifications.
XML Schemas-When you generate a SSNS application, you use configuration files that are XML based. The schemas provided as part of the framework validate those documents.
Sample applications-more than most other tools, the community has used the sample applications to fully show the power of Notification Services. We will explore them as well.
This is good stuff. More later..
Tuesday, June 07, 2005
Tuesday, May 31, 2005
SDC Day 2-The Damn Progress Meter
So last night during the geek night session at the SDC, the Dutch, inspired by Richard Campbell called me on my SMO Backup and Restore GUI that had a progress meter. They thought I was hacking it, not that I was actually providing a true representation of the progress made by the status of the backup. Here is the progress meter in action, as the database backup makes progress we update the progress meter:
To do a backup programmatically you can to use SMO (see yesterday). Begin by setting the variables to get started.
Server svr = new Server();//assuming local server
Backup bkp = new Backup();
Cursor = Cursors.WaitCursor;
Then you have to set the device to backup to and what database to backup. Notice in the comments the code to the progress meter
try
{
string strFileName = txtFileName.Text.ToString();
string strDatabaseName = txtDatabase.Text.ToString();
bkp.Action = BackupActionType.Database;
bkp.Database = strDatabaseName;
//set the device: File, Tape, etc
bkp.Devices.AddDevice(strFileName, DeviceType.File);
//set this when you want to do Incremental
bkp.Incremental = chkIncremental.Checked;
//progress meter stuff
progressBar1.Value = 0;
progressBar1.Maximum = 100;
progressBar1.Value = 10;
//this gives us the % complete by handling the event
//provided by SMO on the percent complete, we will
//update the progress meter in the event handler
//set the progress meter to 10% by default
bkp.PercentCompleteNotification = 10;
//call to the event handler to incriment the progress meter
bkp.PercentComplete += new PercentCompleteEventHandler(ProgressEventHandler);
//this does the backup
bkp.SqlBackup(svr);
//alert the user when it is all done
MessageBox.Show("Database Backed Up To: " + strFileName, "SMO Demos");
}
catch (SmoException exSMO)
{
MessageBox.Show(exSMO.ToString());
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
Cursor = Cursors.Default;
progressBar1.Value = 0;
}
Here is the ProgressEventHandler, notice that I made it generic enough that I can call it from both the backup and restore methods!
public void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
{
//increase the progress bar up by the percent
progressBar1.Value = e.Percent;
}
Monday, May 30, 2005
SDC Day 1-Sneak peak at SQL Server Management Objects (SMO)
The Software Developers Conference in the Netherlands has begun. Today I show how to prevent SQL Injection Attacks in ASP .NET as well as other cool tricks. Lots of RDs here and lots of happy attendees. As usual at SDC I will show something very new and cool. I will show off some of the new SQL Server Management Objects (SMO) in the keynote tonight. Ill give you a preview here.
The SMO object model is a logical continuation of the work done in SQL-DMO. SMO is feature-compatible with SQL-DMO, containing many of the same objects. . To achieve maximum data definition language (DDL) and administrative coverage for SQL Server 2005, SMO adds more than 150 new classes. The primary advantages of SMO are in its performance and scalability. SMO has a cached object model, which allows you to change several properties of an object before effecting the changes to SQL Server. As a result, SMO makes fewer round trips to the server, and makes its objects more flexible. SMO also has optimized instantiation, meaning that you can partially or fully instantiate objects. You can load many objects quickly by not instantiating all the properties of the objects. To get started you have to set a reference to it and pull in the namespace:
using Microsoft.SqlServer.Management.Smo;
Now I will show you how to programitically do a database restore. You start with getting the SMO objects: Server and Restore.
Server svr = new Server();
Restore res = new Restore();
Now take a look at how easy you can do a restore, just a few lines of code:
res.Database = “AdventureWorks“;
res.Action = RestoreActionType.Database;
res.Devices.AddDevice(“c:\mybackup.bak“, DeviceType.File);
res.ReplaceDatabase = true;
res.SqlRestore(svr);
There is a lot more that you can do with SMO, but this shows you how easy it is to manage your server from code. A very cool thing to do it put some of the server monitor stuff into an ASP .NET page for viewing your server stats from a remote location.
More on SMO to come...
Saturday, May 28, 2005
Friday, April 29, 2005
Come see me at TechEd USA!
DBA304 Advanced Querying Techniques, Tips & Tricks Using Transact-SQL
Speaker(s): Richard Campbell, Stephen Forte
Session Type(s): Breakout
Track(s): Database Administration
Day/Time: Monday, June 6 3:15 PM - 4:30 PM Room: S 310 A
Take your querying to the next level! This session gets away from the fundamentals of SQL queries and into the hard stuff. See two experts in SQL Server compare and contrast querying techniques between SQL Server 2000 and SQL Server 2005. This session has a series of real world examples to show how creative SQL queries can generate solutions in record time. Some techniques you'll learn include how to do crosstab queries that take seconds to execute instead of hours, exploiting sub-queries and taking advantage of self-joining. Along the way, get some insight into how SQL servers work, as well as how SQL Server 2005 is going to make advanced querying even easier.
Friday, April 15, 2005
Back from India, Off to Africa...
Back from India, boy was it hot. Sanjay (RD Bombay) and I had a blast. Off to speak at the North Africa Developers Conference in Algeria this weekend. Seven RDs (including the ones who speak French ) will be there in full force. I plan on making Clemens see the light while I am there. I am doing these 4 sessions:
Writing Secure Code for ASP .NET
Data Controls and Advanced Cache Techniques with ASP .NET
Ranking and Windowing Functions in SQL Server 2005
Using XQuery to Query and Manipulate Data in SQL Server 2005
And: The New York Mets and the New York Yankees have the exact same record. I am enjoying it while it lasts. It seems that the Mets are 1-5 when I am in the country and 3-0 when I am not.
Tuesday, April 05, 2005
Tuesday, March 29, 2005
Monday, February 14, 2005
A Lot of People Don't Like Me (oh yea something about Ranking Functions too)
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)
Monday, January 17, 2005
SQL Server 2005-For XML Enhancements-FOR XML PATH
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()
Tuesday, January 11, 2005
SQL Server 2005-XML Defaults and XQuery Constraints on an XML Column
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.
Wednesday, December 22, 2004
SQL Server 2005 (Yukon) Bulk Rowset Provider (OPENROWSET Enhancement)
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)
Tuesday, December 21, 2004
SQL Server 2005 (Yukon)-Recursive Query
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).
Monday, December 13, 2004
SQL Server 2005 Preview, 10K in the park and a German in the house
Come one, come all, this Thursday at the NYC .NET Developers Group, Andrew, Bill and I will give a real in-depth sneak peak on SQL Server 2005 including a tools overview, TSQL, XQuery,XML, Service Broker, OLAP and the Unified Demensional Model. Too bad Clemens will not be there, he is arriving on Friday and spending the weekend at my place for some year end partying. Maybe we will rewrite dasBlog to use SQL Server 2005. (Most likely we will just drink a lot and fall down.)
Did another race this weekend, 10K in Central Park. 8:39 pace, no way I can hold that up for an entire marathon.
|
Last Name
|
First Name
|
Sex/
Age
|
Bib
|
Team
|
City
|
State
|
Net
Time
|
Pace/
Mile
|
|
GURBISZ |
KATHLEEN |
F27 |
5484 |
|
NY |
NY |
51:48 |
8:21 |
|
FORTE |
STEPHEN |
M32 |
5448 |
|
NEW YORK |
NY |
53:40 |
8:39 |
Friday, December 10, 2004
SQL Server 2005-PARTITION BY
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.
Thursday, December 09, 2004
SQL Server 2005-DENSE_RANK() and NTILE(n)
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
Wednesday, December 08, 2004
SQL Server 2005 (Yukon) Ranking Functions-RANK()
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...
Tuesday, December 07, 2004
SQL Server 2005 (Yukon) Ranking Functions-ROW_NUMBER()
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()...
Thursday, August 26, 2004
Force Feed
Microsoft has gathered 300 of its top internal and 3rd party (RDs) Evangelists in one place and have given us a brain dump on their 7 major categories over the next few years. I am sitting in on the Yukon/SQL Server category.
Many RDs are here and we of course are causing lots of mischief. Lots of info overload here today…
Friday, July 09, 2004
Welcome to the Real World Clemens
My buddy Clemens Vasters wrote a few days ago that you don't need inheritance or full data encapsulation for business objects. Some other dude wrote a passionate response to him saying that he was all wrong.
You see out here in the real world, in the coding trenches, the “full data encapsulation” in your business objects just doesn’t hold any water. Who reuses a customer object between applications? At my company we don’t even have a customer object! But we do have customers. :) The data logic all should be handled by the database. Way too much effort, code and BS to deal with when you do it otherwise.
Academic conference speakers who don't write code in the real world always argue to over complex-ify the application. I'm sorry, anyone paid to write code in a production environment has no desire to throw all this logic into the objects and have lots of levels of inheritance so they are “reusing code”. Sure sure we all read the Gang of Four's book and spend a year using all the design patterns but then come back after that year only using the few that make sense to our application. Academic conference speakers who don't write production code should take a year off, write some production code and then come back and see what they have to say.
Lastly at the PDC Clemens said publicly that rows and columns suck and all you need in a database is a PK field and an XML field. Looks like he changed his tune, at least on storage, we all can learn buddy.
“What you end up with are elements and attributes (infoset) for the data that flows across, services that deal with the data that flows, and rows and columns that efficiently store data and let you retrieve it flexibly and quickly.”
Friday, May 14, 2004
Dry Run Complete
Another successful CTTM (or now called SDC) in the Netherlands. What a great show and 7 RDs where speaking. Richard and I did a dry run of From Interoperability to Migration: SQL Server and Linux Databases Working Together and it went very well. Hopefully we can get Oracle to work and play well on the VPC image before our TechED session in 13 days. This session is quite cool, hope to see you all there.
DATC02 From Interoperability to Migration: SQL Server and Linux Databases Working Together
Monday, May 24 1:30 PM- 2:45 PM, Cabana 08
Speaker(s): Richard Campbell, Stephen Forte
Track(s): Data Management
"They" say it can be done, now see it in action! This session demonstrates how SQL Server can acts as the gateway to interoperability with Linux databases such as DB2 and Oracle! You'll see a fully functioning Linux-based web application using Red Hat Linux, Apache, PHP and Oracle sharing data with an identically implemented ASP.NET application using SQL Server. This session shows not only how to interoperate, but to use these interoperate capabilities to facilitate a seamless migration from the Linux based system to SQL Server and Windows . This is how migration was meant to be!
Monday, May 10, 2004
Are there any National Parks in the Netherlands?
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.
|