Geeks With Blogs
Evan Koch Musings on BizTalk Server and SQL Server August 2007 Entries
Index Maintenance
In SQL Server there are generally two types of fragmentation that can plague a database: internal fragmentation and external fragmentation. Both of these types of fragmentation can cause the hard drive to spend more time searching for data on the disk, and since the hard drive is the slowest component in a system, this is something we’d like to speed up if possible. Internal fragmentation comes about as records are added, deleted, or moved within a database file. As records are deleted from a table, ......

Posted On Sunday, August 26, 2007 8:10 AM

n the previous post we saw how indexes can speed up data access; now we’ll discuss the index itself. Some of the information used in this post comes from SQL 2005 Books Online that’s installed with the SQL Server 2005 installation, so check it out if you want more information. There are two main types of indexes within SQL: clustered and nonclustered indexes. You can only define one clustered index per table which must reside in the same file group as the table it references. You’re allowed to create ......

Posted On Monday, August 20, 2007 9:49 AM

Basic Execution Plan Analysis
For the purposes of this post I'llbe using hte following Continent/Country tables. We’ll analyze a few different queries that, in terms of the end result, all do the same thing: they list out countries and their respective continent and order them by the country name. So here’s our base case. If you look at the steps, you can see that the Sort accounts for a little over half of the entire time that the query takes to run. This seems a bit extraneous, so we’ll see what else can be done. Below is our ......

Posted On Tuesday, August 14, 2007 9:22 PM

Manipulating XML in the Business Rules Engine
One of the components provided with BizTalk Server 2004 and 2006 is the Business Rules Engine. It's touted as one of the big benefits of going with BizTalk as a business user can administer the rules and affect changes without needing to understand the intricacies of BizTalk. The BRE can also be used outside of BizTalk which is something that might merit investigation later. For now, I'll cover three methods of manipulating XML data I often use. I'll also take a moment to mention that this post isn't ......

Posted On Friday, August 10, 2007 4:33 PM

External Functions and the BRE
It’s useful from time to time to be able to call external functions within business rules. Instead of something like this: We can make calls to external functions in business rules like this: The IsOpenEnd function is below. The return type is already a Boolean so it’s unnecessary to check to see if the returned value is equal to true or not. public static Boolean IsOpenEnd(int loanTypeCd) { if ((loanTypeCd == LoanTypeCode.HomeEquityLine... || (loanTypeCd == LoanTypeCode.HomeEquityLine... ......

Posted On Wednesday, August 8, 2007 8:35 PM

BizTalk Maps and the Table Looping/Table Extractor Functoids
When dealing with existing systems, sometimes a challenge presents itself in the form of a flat file. Trying to impose structure upon a flat file can be achieved, however, using the Table Looping and Table Extractor functoids. Consider the following schemas: Source schema: Target schema: One’s first attempt at a map to transform the source to destination might look something like this: We’ll use the following input file to test the map. The output isn’t quite what we were hoping for. The Table Looping ......

Posted On Tuesday, August 7, 2007 6:06 PM

Copyright © Evan Koch | Powered by: