Azure DocumentDB Preview at Sydney Alt.Net, Aug 2014 from Richard Banks on Vimeo.
Enjoy!
For those of you who don’t know what I’m talking about, Neo4j is a graph DB, which is great for dealing with certain types of data such as relationships between people (I find it ironic that relational databases aren’t great for this) and it’s perfect for the project I’m working on at the moment.
However this project also needs to be deployable on Azure, which means if I want a graph DB for storing data, I need something running on Azure. I’d originally looked at Sones for doing this since it was the only one around that I knew could run on Azure, but my preference was to run Neo4j instead, because I know it a little better, and because I know that the community around it is quite helpful.
The great news is that as of just recently (a week ago at time of writing) Neo4j can now run on Azure! Happy days!! and “Perfect Timing”™ for my project. I just had to try it out. P.S. Feel free to go ahead and read the Neo4j blog post because it has a basic explanation of how to set it up, has some interesting notes on the implementation and also talks about the roadmap ahead.
Here’s a simple step-by-step on how to get Neo4j running on you local Azure development environment:
1. Download the Neo4j software from http://neo4j.org/download/. I grabbed the 1.3.M02 (windows) version.
2. You’ll also need to download the Neo4j Azure project, which is linked to from the Neo4j blog post.
3. I’m assuming you have an up to date Java runtime locally, so go to your program files folder and zip up the jre6 folder. Call it something creative like jre6.zip :-)
4. If you haven’t already done so I’d also suggest you get yourself a utility for managing Azure blob storage. I’m using the Azure Blob Studio 2011 Visual Studio Extension.
5. Spin up Visual Studio and load the Neo4j Azure solution. Make sure the Azure project is the startup project
6. Next, upload both the Ne04j and JRE zip files you have to Azure blob storage. Note that by default the Azure project looks for a neo4j container, so it’s best to create that container first:
7. Now it’s time to spin this thing up! Press the infamous F5 and wait for a while as Azure gets it act in order, deploys the app and starts everything up for you. You’ll know things are up and running when a neo4j console app appears on your desktop. You may also get asked about allowing a windows firewall exception, depending on your machine configuration.
Behind the scenes, the Neo4j Azure app will pull both the java runtime and neo4j zip files down from blob storage, unzip them and then call java to start things up. As you can imagine, this can take some time.
If you have the local Compute Emulator running, then you should be able to see logging information indicating what’s happening, for example:
8. Once everything is up and running you should be able to start a browser up and point it to the administration interface. On my machine that was http://localhost:5100. You can check the port number to use by having a look at the top of the log for this:
You should then see something like this:
That chart is showing data being added to the database. Just what we want to see!
So that’s it. The server is up and running and it wasn’t too hard at all! The Neo team is looking to make the whole process much simpler, but I think this is a great start and it is very welcome indeed!
If you do have problems, check the locations in the settings of the Neo4jServerHost in the Azure project to make sure they match your local values. You might just need to adjust them to get it working.
P.S. If you have questions, then ask in the Neo4j forums. I’m not an expert in any of this ;-) I’m just really pleased to be able to see it running on Azure.
For those who don’t follow me on twitter (and why not!) you might have missed me having a whinge about a SQL database I was looking at recently from a performance perspective.
Anyway, I found this little INSERT INTO statement in one of the extremelyy slow stored procedures they were having problems with (table names changed to protect the innocent)
INSERT INTO #TEMPTable
SELECT p.PID,EH.EID, SUM(EH.Amount)
FROM ps INNER JOIN
xx ON ps.xxID = xx.xxID INNER JOIN
p ON xx.PID = p.PID INNER JOIN
EH ON ps.psID = EH.psID
WHERE p.EID = @ParamID AND (p.Processedflag = 1)
GROUP BY EH.EID, p.PID
It executed in about 25 seconds. After a look at the indexing I decided to help SQL out a little by making the join to EH a little more selective, as follows:
INSERT INTO #TEMPTable
SELECT p.PID,EH.EID, SUM(EH.Amount)
FROM ps INNER JOIN
xx ON ps.xxID = xx.xxID INNER JOIN
p ON xx.PID = p.PID INNER JOIN
E1 on E1.EID = p.EID INNER JOIN
E2 on E2.EID = E1.EID INNER JOIN
EH ON ps.psID = EH.psID
and EH.EID = E2.EID
WHERE p.EID = @ParamID AND (p.Processedflag = 1)
GROUP BY EH.EID, p.PID
That little change reduced the query to under a second, even though I’m now joining to two extra tables.
If only all performance improvements were as simple. And if looking like a hero was always so easy! :-) And the lesson for you? Remember to keep your joins as selective as possible, even if it means taking the long way round on joins at times.
I spent a decent amount of time working with PostgreSQL some years ago and thought at the time that the concept of databases that could store objects was fantastic. None of that object relational impedance mismatch hoo-hah – just save an object, retrieve an object, etc. Unfortunately reality wasn’t so great - the tooling around reporting and support by other applications was lacking, and that plus various other influences at the time meant I didn’t really get to explore it much further and it eventually dropped off my radar.
This morning I listened to the Alt.Net podcast where Rob Conery is talkng about Object Databases (or post-relational databases as they’re sometimes called). It’s an interesting podcast and well worth the 30 minutes you’ll need to listen to the whole thing, and it was great to hear that Rob’s sentiments reflected most of mine, though I’m sure he expressed them in a lots gooder way.
It sparked me to have a quick look at where things are at these days in the ODBMS world and to see if the critiques the SQL fans have of them are still valid. So let me present a very paraphrased version of the wikipedia entry on the Pro’s and Con’s of ODBMS’s
Pros:
Cons:
OK, so speed can be an issue as well as interoperability. It’s the second one that is the biggest barrier to adoption and is after all the main reason I didn’t do more with it in the past – however Rob made a comment on the podcast that really stuck; why not run both an ODBMS and and RDBMS side by side? The two types of database engines really serve different purposes, and people already run multiple databases today with different purposes – e.g. transactional vs reporting databases – so why not do the same with ODBMS and RDBMS engines?
Here’s what the ODBMS.org web site has to say on this idea:
“Thus object databases are increasingly established as a complement to (not a replacement for) relational databases for efficient resolution of the OR mismatch. ODBMSs are flourishing as embeddable persistence solutions in devices, on clients, in packaged software, in real-time control systems, and to power websites.”
Interesting stuff and it certainly gets the mind thinking of possibilities. I think it might be time to have a revisit of the whole ODBMS scene and see where things are at now. I’ll probably start with the db4o database engine as it’s an open source native .NET engine with a strong developer community, it supports linq for querying (codeproject sample) and weighs in at a very light 13.5 MB. That’s good enough for me :-)
DataDude, or as it’s more formally known – Visual Studio Team System 2008 Database Edition GDR (*phew*) has been released for a few weeks now and I spent part of today showing a number of people how it all fits together.
By the way, if you already have the 2005 edition installed, go get this version right now. The improvements it makes over the 2005 version are massive – this product is now a useable and worthwhile addition to your toolbox, plus it supports SQL2008 databases.
The project system is now quite different. The biggest (and I think the most needed) change was the separation of the build process from the deployment process. You can now build the project to make sure that your SQL is correct without creating a deployment script. When you build you get a DBSCHEMA file with you database definition in it. Deployment is also improved with the option to just create the deploy script to target a database or you can create the script and run it against your target database in one operation:
The project file itself is now structured in a simpler manner with specific configuration files for various aspects of the configuration and deployment:
Oh – I should also mention this one : no more design time database! Yay!! DataDude now uses the SqlServerCE data engine for checking syntax and no longer creates those temporary databases with the GUIDs in the name. Of course it does mean you need SqlServerCe (3.5 SP1) installed otherwise you might run into errors opening database projects with “Could not load assembly…” errors.
Another very cool addition to DataDude is the inclusion of FxCop style code analysis rules. While you can’t ensure that your SQL code is brilliant, you can at least be sure it doesn’t completely suck and break common rules. As you can see it includes checks for things like the use of @@IDENTITY and SELECT * … etc.
The Schema View window
One of the other painful things about the 2005 edition of data dude was the schema management. With 2005 by far the easiest way to edit your schema was to make changes via management studio and then do a schema comparison to bring the changes you wanted into your project. While you can still do this it’s now pretty easy to manage the schema inside visual studio since there is a Schema View window with all the various things you might expect.
You still edit the table definitions using SQL (no designers) but it’s pretty simple to manage this stuff now.
What’s also nice is the improved things you can do in the schema view window as seen here:
Apart from much improved database refactoring support, there is now also the ability to view the dependencies of an object – both the things depending on it, and the things it depends on. This really helps in understanding what you’ll be breaking when you make those crazy schema changes your thinking of :-).
Woo-hoo!! Unit testing for stored procedures is very, very nice now. To get started with a unit test hit the Create Unit Tests… button
Run through the dialogs and you’ll end up with a generated test that looks something like this:
See all that SQL code calling the proc? What’s it doing inside a *.cs file? Well, DataDude has added a designer experience to the database unit tests. You can now write some SQL and add your test conditions from a list. Test conditions are adjusted through the properties pages for each condition.
You can also do pre & post test scripts as well which can be very handy.
Behind the scenes you have normal code so if you really wanted to you can always go and add further code to assert and test conditions to suit whatever the specific needs are that you have
There’s a whole slew of other changes in the new version of DataDude and all of them are valuable and probably deserver a blog post in their own right, but these three areas of improvement are to me the changes that make the most difference. If you want to know what the other changes are, go and check Gert’s blog where he gives a run through of each of them or go and grab the documentation and have a read of it for yourself – even better, go download it and have a play with it yourself and discover the improvements.
You may remember a few posts back that I was working with a team trying to use a pure stored procedure approach to access a database, and trying to do so using an OR/M. One of the commenters on the post mentioned iBATIS. Now I always thought iBATIS was a competitor to NHibernate, Linq2SQL, et al. but it's actually a different approach where instead of being a fully blown OR/M it just does simple data mapping of business objects to and from SQL statements and nothing more than that.
So I decided to have a bit of a play with it and I must say it looks pretty good. Here's a quick bit of sample code I knocked up to see how it works.
Assume I have the following sproc:
CREATE PROCEDURE InsertSaleHeader
@Tax money,
@TotalValue money,
@SaleNumber int output
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].[SaleHeader]
([Tax]
,[TotalValue]
,[ModifiedDate])
VALUES
(@Tax,@TotalValue,GETDATE())
SET @SaleNumber = scope_identity()
END
It's just a basic insert sproc that updates a modified date on insert and also returns a new identity value for the record we just added using an out parameter. This is the sort of thing that was a real pain to so with many of the full featured OR/M's.
I then wrote an integration test (p.s. please forgive my incorrect casing in places):
using IBatisNet.DataMapper;
using SalesDAL.ibatis;
namespace SalesIntegrationTests
{
[TestClass]
public class ibatisSaleRepositoryTests
{
[TestMethod]
public void CreateSaleAndGetSalesUsingIBatis()
{
ISqlMapper mapper = Mapper.Instance();
ISaleRepository repository = new ibatisSaleRepository(mapper);
ISale sale = new SalesTax.Sale();
sale.Add(new SalesTax.SaleLine(1, "imported box of chocolates", 10.00m, true));
bool result = repository.CreateSale(sale);
Assert.IsTrue(result);
}
}
}
This just creates an instance of a sale object and sends it to a sale repository class. The code for the ibatisSaleRepository is shown below.
namespace SalesDAL.ibatis
{
public class ibatisSaleRepository : ISaleRepository
{
private ISqlMapper mapper;
public ibatisSaleRepository(ISqlMapper mapper)
{
this.mapper = mapper;
}
public bool CreateSale(ISale sale)
{
mapper.Insert("InsertSaleHeader", sale);
LastSaleId = sale.SaleNumber;
return true;
}
public int LastSaleId
{
get;
private set;
}
As you can see the only actual work that happens is in the CreateSale() method where the IBatis Insert() method is called, passing in the object to be saved (note that I’m only saving the sale header here – we could extend this to save the sale lines easily enough).
And that’s it for the code. Nothing too complex at all. The rest of the work is done through the config files that IBatis loads during the Mapper.Instance() method call in the unit test. When it's initialised iBATIS loads up the SqlMap.config file and then processes it any other config files that are reference by it to create the mapping behaviours it needs to know.
The IBatis SqlMap.config file I used is shown here:
<?xml version="1.0" encoding="utf-8" ?>
<sqlMapConfig xmlns="http://ibatis.apache.org/dataMapper" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<settings>
<setting useStatementNamespaces="false"/>
</settings>
<providers embedded="providers.config, SalesIntegrationTests" />
<database>
<provider name="sqlServer2.0"/>
<dataSource name="SalesData" connectionString="Data Source=.;Initial Catalog=SalesDatabase;Integrated Security=True"/>
</database>
<sqlMaps>
<sqlMap embedded="ibatis.SalesMap.xml, SalesDAL"/>
</sqlMaps>
</sqlMapConfig>
And finally the SalesMap.xml file is as follows:
<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="SalesDAL" xmlns="http://ibatis.apache.org/mapping" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >
<alias>
<typeAlias alias="SaleHeader" type="SalesInterfaces.ISale, SalesInterfaces" />
</alias>
<statements>
<procedure id="InsertSaleHeader" parameterMap="InsertSaleHeader-Params">
dbo.InsertSaleHeader
</procedure>
</statements>
<parameterMaps>
<parameterMap id="InsertSaleHeader-Params" class="SaleHeader">
<parameter property="Tax" />
<parameter property="TotalValue" />
<parameter property="SaleNumber" direction="Output" column="SaleNumber" />
</parameterMap>
</parameterMaps>
</sqlMap>
I won't go into all the details of the configuration, but as you can see it's not onerous in any way. In fact I found that overall iBATIS it’s a lot simpler to use than NHibernate for this type of operation and it's much easier and faster using it than doing the alternative and writing ADO.NET by hand.
Oh, I should also mention that there is a Castle Windsor facility that loads iBatis up for you so you don’t have to maintain dependencies throughout your application.
So when is using IBatis a good choice? I'll let the IBatis team say it in their own words (taken from the documentation):
So, how do you decide whether to OR/M or to DataMap? As always, the best advice is to implement a representative part of your project using either approach, and then decide. But, in general, OR/M is a good thing when youLikewise, the best time to use a Data Mapper, like iBATIS, is when:
- Have complete control over your database implementation
- Do not have a Database Administrator or SQL guru on the team
- Need to model the problem domain outside the database as an object graph.
In the end, you have to decide what's best for your project. If a OR/M tool works better for you, that's great! If your next project has different needs, then we hope you give iBATIS another look. If iBATIS works for you now: Excellent!
- You do not have complete control over the database implementation, or want to continue to access a legacy database as it is being refactored.
- You have database administrators or SQL gurus on the team.
- The database is being used to model the problem domain, and the application's primary role is to help the client use the database model.
I've been working through some architectural choices with a team starting a new project recently and the ever running debate over database access philosophies came up (as it was bound to).
Personally I'm in favour of letting an ORM manage all the CRUD operations as I see very little benefit in writing CRUD stored procedures (as do others). However I'm also pragmatic and can see some value in the sprocs argument, though the purported benefits of security and performance don't wash with me (but that's for a blog post).
Anyhoo, this particular team decided they wanted stored procedures for ALL data access. That means CRUD procedures for every table and no direct table access whatsoever. Yet at the same time they wanted to use an ORM for the .NET side of the equation as coding ADO.NET can be painful at the best of times.
Now, as part of the architecture we had decided early on to use the Repository pattern for data access and ensure we had persistence ignorance for all our data classes. This lets us isolate the data access plumbing from the rest of the application which helps with the persistence ignorance in the rest of the app, and lets us work with POCO objects everywhere except in the repositories. However it doesn't solve what to actually use in the repositories themselves.
So what to do? Some of the team wanted to use Linq To Sql, others wanted to use Microsoft's new Entity Framework and others suggested NHibernate. Too many opinions meant there was only one real way to decide. We decided to have an ORM to SProcs smackdown - may the best working code win!
The rules were simple... Take a very simple, 3-property, POCO business class and persist it to/from a database using nothing but stored procedures and an implementation of the repository pattern in the ORM of your choice. Get it done in 4 hours or less. The team would then vote for their preference.
Here's the results:
The entity framework has support for stored procedures for data access. Surely it would be simple to match things up, right? Wrong!! The developer who got the EF mission tried his best to get the tool to do what we wanted but mapping the classes was a problem.
The stored procedure support is best described as simplistic, at it's worst it's probably described using words not suitable for children to hear.
Even when the developer got something almost working, it still required creating a class that duplicated the POCO class and then manually mapping fields from one class to another. Gah! This is pretty much the same as using ADO.NET and mapping to parameters.
Also, the steps required to get the Entity Framework code "working" were so convoluted and confusing it would be a maintenance nightmare, even if it did work.
No vote was required for this. Everyone hated it.
There was some hope here - a working implementation was shown, however there are some major limitations.
First, Linq to Sql wants access to tables. Without table access you don't get any change tracking in your data context negating many of it's benefits.
Secondly, the designer support for stored procedures without is limited. Sure you can drag the procs onto the designer surface, but it only helps a little as the select stored procedures don't actually return classes you can easily use, but rather ISingleResult<T> objects. To get these into our business layer POCO's we needed to manually map them using either a linq projection or through standard property assignments in code.
Updates via stored procedures were also a problem. Not having table access means that you won't get any value from implementing the partial UpdateXXX() methods that the linq data context provides as they'll never be called. Instead you have to call the update stored procedures directly and you have to manually map your objects properties to the stored procedure's parameters.
Further, if you use an insert procedure and database generated primary key fields then you have to remember to get the new value of the key from the database and update the POCO's properties with the new value.
It's workable, but no one in the team was that impressed with it. It received zero votes.
The implementation using NHibernate was also shown to be workable.
A hibernate mapping file was created that had a class definition with specific overrides for the update, insert and delete methods so they called out to stored procedures. There was also an override for the object loader to use a select stored procedure.
The only change to the POCO was that the properties needed to be changed so that they were virtuals. We need to investigate this a little more but I'm going to assume it's something to do with NHibernate creating proxy versions of the classes for internal use.
Other than that the actual repository implementation was very simple. Pass an object to NHibernate's session and call Flush at the appropriate times.
When a vote was called for everyone went for this method. If I get some time I'll try and explain how it all works (or you could just do a Google search for it!).
So NHibernate method wins, but the scenario was simplistic. There may be some further issues that we need to deal with such as complex procs, optimistic concurrency and so forth, but even so there is more confidence that NHibernate will deliver the goods. It has a large user base, it's been around for quite some time now and we're pretty sure other people have tried this before.
On the other side of the equation, Linq to Sql and the Entity Framework are surprisingly and embarrassingly inept at this. If you don't have direct table access then you might as well forget about using these tools and stick with ADO.NET or code generation. The simple fact that data access via just stored procedures is such a common requirement in enterprise databases and that these two "modern" offerings from Microsoft just don't work is downright perplexing. Seriously, Microsoft recommends for years that people use stored procs and the release tools that are only partially functional in a pure stored procedure environment. What gives?!
Well, I think that's enough of a rant for now - time to get some back to doing some real work now!
I had a situation recently where I was building a solution that contained a Database project (.dbproj) built using Visual Studio 2005 Team Edition for Database Professionals (aka DataDude).
In Team Build there were two different build types. One for Continuous Integration and one for the Nightly Build. The CI build simply compiled the solution and ran some tests locally, whereas the nightly build also deployed the database to a test server and refreshed the application on the test server.
As part of the build process I had something like this in the project file:
<PropertyGroup>
<TargetConnection_A>DataSource=ServerA%3BIntegrated Security=True%3BPooling=False%3B</TargetConnection_A>
<TargetConnection_B>DataSource=ServerB%3BIntegrated Security=True%3BPooling=False%3B</TargetConnection_B>
</PropertyGroup>
<Target Name="xxx">
<Message Text="Deploying Database Locally" />
<MSBuild Projects="db1.dbproj" Targets="Build;Deploy" Properties="TargetConnectionString=$(TargetConnection_A)" />
<Message Text="Deploying Database Locally" />
<MSBuild Projects="db1.dbproj" Targets="Deploy" Properties="TargetConnectionString=$(TargetConnection_B)" />
</Target>
Simple enough, really. To do the second deployment you just change the connection string for the database project and re-deploy, right? Well, actually it's not. If you try this you'll get an error in your build log that looks something like this:
Deploy error TSD151: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line xx The server name in the build script ServerB does not match the name of the target server ServerA. Verify whether your database project settings are correct and whether your build script is up to date.
This is a bit surprising, especially as the build & deploy to Server A works correctly.
Well, as it turns out, when a database project is built it creates one huge uber-script and embeds within it the knowledge of the server it should be deployed to. It also adds a check that the server in the connection string matches the server it thinks it should be deploying to, so even though you change the connection string before calling the script you'll get an error occurring based on the fact that the servers don't match.
If you look at the MSDN documentation on this topic they'll tell you to modify the database project settings directly in order for the deploy to work in Team Build, however this is a flawed approach as changing the project properties will affect everyone in the team (and you don't want them all deploying to the one database by default, do you?) and it also doesn't solve the problem of deploying the database twice.
Now you could try changing the targets in the second MSBuild step to use "Clean;Build;Deploy" as the targets but what happens in this case is that the compiler determines that the source hasn't changed and it therefore has nothing to do, and the uber-script isn't changed. As such you'll still received the same error as before.
The solution then is to force a rebuild. This is easily done by changing the targets for the second MSBuild to use "Rebuild;Deploy" as shown here.
<MSBuild Projects="db1.dbproj" Targets="Rebuild;Deploy" Properties="TargetConnectionString=$(TargetConnection_B)" />
Once you've done that, everything should work and your builds will then deploy the database to multiple servers as expected.
Today saw the start of this weeks RDN sessions. In Sydney this morning Wendy Richard's provided a good overview of ASP.NET AJAX and Greg Low talked through what's coming up for SQL Server 2008.
The SQL 2008 session in particular was really informative and provided a good insight into what the SQL Server team is doing with the product, including query enhancements, storage improvements, the upcoming Spatial data type and the Date and Time data types (yay!), how the release cycle will work and a whole lot more. Invaluable stuff for anyone who works with an SQL database (that's most of us!).
If you haven't yet booked into an RDN session then I strongly suggest you do. The value in these sessions is huge and the only cost is your time. What a great return on investment :-)
insert into humanresources.employeepayhistory
values
(1, getdate(), 100, 1, getdate()),
(2, getdate(), 100, 1, getdate()),
(3, getdate(), 100, 1, getdate()),
(4, getdate(), 100, 1, getdate())
insert into humanresources.employeepayhistory values (1, getdate(), 100, 1, getdate())
insert into humanresources.employeepayhistory values (2, getdate(), 100, 1, getdate())
insert into humanresources.employeepayhistory values (3, getdate(), 100, 1, getdate())
insert into humanresources.employeepayhistory values (4, getdate(), 100, 1, getdate())


| Run | Single Statement | Separate Statements |
| Query Profile Statistics | ||
| Number of INSERT, DELETE and UPDATE statements | 2 | 8 |
| Rows affected by INSERT, DELETE, or UPDATE statements | 4 | 4 |
| Number of SELECT statements | 1 | 4 |
| Rows returned by SELECT statements | 1 | 4 |
| Number of transactions | 1 | 1 |
| Network Statistics | ||
| Number of server roundtrips | 3 | 3 |
| TDS packets sent from client | 3 | 3 |
| TDS packets received from server | 11 | 24 |
| Bytes sent from client | 626 | 934 |
| Bytes received from server | 33338 | 88924 |
| Time Statistics | ||
| Client processing time | 30 | 50 |
| Total execution time | 30 | 50 |
| Wait time on server replies | 0 | 0 |



I recently grabbed the new July CTP (CTP4) for SQL 2008 - Katmai.
I'm still looking through things, for instance Notification Services has been dropped, and Reporting Services has undergone some serious surgery.
One thing that did intrigue me though was a new data type for supporting hierarchical data: hierarchyid.
This looks really, really cool. Basically it lets you store tree structured data in SQL and includes methods to make working with that data really simple. You get methods like IsDescendant, GetAncestor, GetLevel, ReParent (for moving nodes/subtrees) and so forth.
You can index either breadth-first or depth-first, so you can index based on how you typically traverse your tree.
Here’s an example table with breadth first indexing:
CREATE TABLE Organization (
EmployeeID hierarchyid,
OrgLevel as EmployeeID.GetLevel(),
EmployeeName nvarchar(50) NOT NULL
);
CREATE CLUSTERED INDEX Org_Breadth_First
ON Organization(OrgLevel,EmployeeID) ;
The GetLevel() on the OrgLevel column is so you know what depth a record (node) is at in the tree.
Use the GetRoot() function to get the root of a tree (note the double colon syntax). For example, this inserts a new root node and then selects it.
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES (hierarchyid::GetRoot(), 1, 'Me', 'CEO') ;
SELECT * FROM HumanResources.EmployeeOrg WHERE OrgNode = hierarchyid::GetRoot() ;
The other interesting thing is that hierarchyid’s have a ToString() function that spits out hierarchy paths similar to the following examples:
Nodes can be inserted in any location. Nodes inserted after /1/2/ but before /1/3/ can be represented as /1/2.5/. Nodes inserted before 0 have the logical representation as a negative number. For example, a node that comes before /1/1/ can be represented as /1/-1/.
It’s a little weird to look at but it makes sense.
Performance is optimised for selecting data, so anything that does large amounts of inserts/deletes or reparenting (think sales order header/order lines for example) may not be a good candidate for hierarchical data.
This came up during a discussion about meta data for table rows on the Readify internal mailing list.
Wouldn't it be nice to have a way to logically delete rows in a table, i.e. make it so that they were available for looking up information like descriptions and so forth but in general did not come as results in normal select statements. It would be even better if obsolete rows could be excluded when trying to establish new foreign key relationships.
Here's the feedback that has been placed on the SQL 2008 CTP site:
SQL Server could have:
OBSOLETE FROM sometable WHERE someclauseand those rows would no longer be returned by
SELECT columns FROM sometable unless you also added: SELECT columns FROM sometable WITH OBSOLETE Rows could be reinstated by: REINSTATE FROM sometable WHERE someclause Foreign key relationships could then be defined as not permitting new references to obsolete rows. No doubt performance gains could also be obtained where the engine knows which rows won't be updated. Part of this could be done by partitioning today but it's not the full story.
What do you think?
P.S. If you are on the SQL 2008 CTP program then go to the Microsoft Connect site and vote for the Obsolete/Reinstate feature via this link.
I've been looking at some of the new features in the SQL 2008 (Katmai) June CTP today. Here's some of the interesting things I has a look at
1. Multiple Value Inserts
In one statement, it's now possible to insert multiple records, and not by batching changes.
As an example:
insert into factbuyinghabits values (707, 11794, getdate()), (708, 11795, getdate())
will insert 2 records into the factbuyinghabits table (and tell you that 2 records were added).
What if you are inserting data into a table with an identity column? What is the value of @@identity for this statement?
insert into table_2 ([value]) values ('val1'), ('val2')
select @@identityNo surprises - it's the value of the last record inserted.
2 Change Data Capture (Logging)
This is a really nice new feature, but it needs some work. What you can do is track changes at the column level for tables in a database. There's a few steps to follow to set it up:
The database itself needs to be configured by running sys.sp_cdc_enable_db_change_data_capture. This creates a number of tracking tables in the database, adds a role (cdc_admin) and turns on the is_cdc_enabled flag in sys.tables
Tables then need to be specifically marked for tracking using the sys.sp_cdc_enable_table_change_data_capture stored procedure. For example the following starts tracking on all columns for the dbo.FactBuyingHabits table
EXECUTE sys.sp_cdc_enable_table_change_data_capture
@source_schema = N'dbo'
, @source_name = N'FactBuyingHabits'
, @role_name = N'cdc_Admin';
This creates a tracking table specifically for the FactBuyingHabits table (dbo_factbuying_habits_CT) and turns on the is_tracked_by_cdc flag in sys.tables.
So now we're tacking data, how do we see it. We'll, it's a little involved but I'm sure this will improve (or you could convert it to a stored proc). The following SQL returns the changes in the table.
DECLARE @from_lsn binary(10), @to_lsn binary(10);
SELECT @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', dateadd(d,-1,getdate()));
SELECT @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', getdate());
DECLARE @customeridCol int;
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_factbuyinghabits(@from_lsn, @to_lsn, 'all update old');
The 'all update old' ensures that not only do all changes get returned, but that updates also show the previous value of the column or row being changed.
One thing that's missing though is determining who made the change. Currently you only get the data, not the login or other tracking information that would really make this handy for auditing DB changes.
Also, in order for this to work, the SQL Server Agent process MUST be running. Failure to do so will queue changes for the agent to process, but won't actually cause any of the data tracking tables to be updated.
3. The Merge Statement
This is COOL! In the scenario where you have a record you want to put in the database but you aren't sure wether the record exists or not, you usually have to do a read of the database (1 round trip) and then either execute an insert or an update statement as appropriate (another round trip).
With the merge statement, you can now do it all in one go. This statement
merge factbuyinghabits fbh
using (select 1175 as customer, 707 as product) as src
on fbh.customerid=src.customer and fbh.productid=src.product
when matched then
update set fbh.lastpurchasedate=getdate()
when not matched then
insert values (707, 1175, getdate());
ensures that if a record for the product and customerid exists then the last purchase date is updated, otherwise a new record is created.
Nice ;-)