T-SQL: Splitting up portions of a string

I have a piped list of identifiers that work as pairs, like this:

“g|1455,i|34,i|43444, ….”

Splitting up the comma delimited list is easy enough, but the length of the number (an integer) is variable.  The first element preceding the pipe will always the same length. If I split each element of the list into a variable called @Me, my first entry would look like this:

@Me = “g|1455″

This little routine will now split them up into two elements, with the second element being cast as an integer:

SELECT LEFT(@Me, 1) AS MyType,
CAST(RIGHT(@Me, (LEN(@ME) – 2)) AS int) AS MyID

This returns:

MyType = g, MyID = 1455

 

 

An SQL Azure Indexing Strategy

After digesting some of the lessons learned and revisiting my schema to make sure I have primary keys on every single table (ie. no heaps!), I need to develop an indexing strategy that everyone who touches this database can follow.

My table structure for the main “objects” in my application comprise

• Object Table which has just one of whatever the object is, with a unique GUID (ObjectID) as the primary key.  Each unique row can be modified many times, and is queried many times; which means …
• Object History Table is a companion table that contains every historical row of data that ever was in the Object Table.  The history table mimics the object table but without the unique requirement for the object GUID; and with 3 x modification values (who, what, when) added.  To make this arrangement work in SQL Azure, I’ve added an ObjectINT as the primary key.  This table is queried occasionally, but usually only for the modification data, not the object data.

My indexing strategy really needs to reflect how the data is used (queries).  All my queries have order by clauses, so I am not too concerned about that.  So, for each Object Table, these indexes:

1. Object Cover Index …. an index of the unique GUID (as ObjectID) and includes every item in the table
2. Object Specific Index … an index of the ObjectID and any other parameters commonly used in table joins (such as additional guids and types).  There may be more than one of these.  As a naming convention, I’m calling this index (say) IXObjectTable_ID_SomeOtherID_TypeValue … stringing out the values in the name of the index.  This helps avoid naming collisions AND makes the index itself readable, always good when monitoring performance.

For each simple Link Table (and matching Link_History Table):

1. For now, a single index with all the ObjectIDs

Some Link Tables with additional content (and matching History tables) will need to be treated more like Object tables.

Lookup tables don’t really need any indexes on their history component as they are never queried (unless extreme duress).  Base lookup tables really should have a cover index, and then additional indexes for how they are usually used – in my case with ID and parentID, as most of my lookup tables are two-tier hierarchies.

Once I have all these in place, I’ll need to do some data entry and application testing to figure which ones of all these indexes SQL Azure decides it wants to use.  I also want to explore caching some of these queries to reduce the number of round trips.

Starting from the Ground Up with SQL Azure

Clean slate!  An SQL database for the Azure platform.  The first thing I did was purchase the book “Pro SQL Azure (Expert’s Voice in .NET)” by Scott Klein and Herve Roggero (Kindle edition).  If you are even vaguely considering the Azure platform, this is as good a place as any to start.

First lesson:  Every table MUST have a primary key.  In traditional databases, this isn’t always necessary, particularly if you use history tables.  So back to the schema scripts and adding primary keys to everything.

This means I really should invoke a strategy for primary keys.  For several years now we have used GUIDs as primary keys.  I will continue to do so for this Azure database, but will only use GUIDs for the main object tables; a simple INT will suffice for  the history tables as these items are never updated; and rarely queried.

Second lesson:  This is more like a marker for more investigation.  I DO think my scenario is going to fit into horizontal partitions, as I’m hoping for a mufti-tenanted scenario (one version of the code, many separate databases).

Third lesson:  If you aren’t using SQL 2008 R2 on your desktop, get a brain transplant.

More on this subject as I progress.

 

 

 

 

Indexing Views

I want to remove the mysteries contained in the schema, and present some views that can be easily queried.  To make the views work quickly and efficiently, I need to make at least one index, maybe more.  Not as easy as it sounds. There are some rules I’ve discovered that are worth noting when it comes to creating indices on views:

• schema qualify every object with (ie. dbo.TableName); this is particularly important for nested select statements, and it doesn’t hurt to do some consistent aliasing as well;
• avoid using functions

The syntax looks like this:

CREATE VIEW dbo.vwViewName WITH SCHEMABINDING
AS
SELECT  .....


Once the view is established, proceed to:

1. Create a unique, clustered index on the most appropriate “thing” in your view; THEN
2. Proceed with non-clustered indexes that include the additional elements, THEN
3. Create Full Text Catalogs if that is part of your plan.

These tips should help you avoid error messages like:

“Names must be in two-part format and an object cannot reference itself”

or

“Cannot create index on view vwViewName because the view is not schema bound”

and variations on that theme.

The full list of what is and is not allowed in indexed views is available here: http://msdn.microsoft.com/en-us/library/ms191432.aspx

Balsamic … love, love, love

I’ll make no secret about having a long-term love affair with balsamic vinegar.  It is my magic ingredient with so many different foods.

Balsamic Strawberries with Ice-cream

My latest “favorite thing” is Balsamic Strawberries.  It all started when the MOTH (man of the house) brought home a huge punnet of strawberries.  They were the first of the season but there was almost a kilo (2 pounds) of strawberries.  I wondered what I could do with them as they don’t keep very well.

In honor of The MOTH’s love of ice cream AND my love of balsamic, it wasn’t a quantum leap to the outcome.  It’s been a resounding success at our place – I’m sure it will be at yours too.

Ingredients

• 2 lbs (1 kg) fresh strawberries, washed, hulled and chopped up into even size bits
• 1/3 cup raw sugar (granular, relatively unprocessed – I prefer organic)
• 2 teaspoons balsamic vinegar
• 1 teaspoon pure vanilla essence
• good hand full of fresh mint leaves, sliced or shredded very finely.

Method

Put half the strawberries in a small saucepan with the balsamic, vanilla and raw sugar.

Bring to a gentle boil for a couple of minutes.  As the strawberries begin to soften, start to mash them with a potato masher.  The idea is to break them up without having to cook them down too hard. I wouldn’t recommend using anything too violent, as you don’t want them totally pureed.

Balsamic Strawberries … cooling down

When you have the strawberries in a consistency you like, add the other half of the strawberries, and the mint.  Let cook for just a minute. Set aside to cool.

Pour liberally over anything you like.  Ice cream is the first choice.  I warmed up a Choc-Orange Brownie in the microwave, added ice cream and poured over the strawberries.  Yum!

An unforeseen consequence – the MOTH no longer buys me flowers – he brings home punnets of strawberries instead!

 

Porcini Risotto with Asparagus

I just drool in the produce section of the supermarket in spring.  All those beautiful greens.  And living on the West Coast we get fooled into thinking spring is here, only to be quickly reminded that the weather dictates everything here.  Nothing like some comfort food on a grey and miserable day that SHOULD be sunny.  This risotto warms my soul.

Ingredients

• 2 cups Arborio rice
• 3 cloves garlic, chopped finely
• 1 large yellow/brown/white onion, chopped finely
• good splash of olive oil
• 100g dried porcini mushrooms (if you are on a budget, 50g will give you the same taste)
• boiling water
• half red capsicum (bell pepper) chopped finely
• 1 bunch spring onions, chopped including almost all the green part
• 1 litre chicken stock (or vegetable stock for vegetarian)
• 150gm grated Parmesan cheese
• 1 bunch (about 500gm/1lb) asparagus, chopped into bite size pieces
• 1 bunch of thyme (grab a handful from the garden if you are so lucky) – strip out the wood and retain the leaves.  Use as much or little as you like, to your taste.  I love lots of it!
• Cracked pepper
• Optional – 2 x bacon rashers crisped up in the oven for garnish.

Method

Place porcini mushrooms in a small glass bowl and cover with about 2 cups of boiling water.  Cover bowl with small plate to keep in the steam, and leave the mushrooms sit for a while – 30 minutes is good.

Place the chicken stock in another saucepan and turn the heat onto low.  The basic principal of a risotto is to add warm liquid to the hot rice pan – but there is no need to boil the stock, just warm it up.

Ready a third small saucepan with water and salt, sufficient to cook the asparagus at the last minute.

Heat the splash of oil in a heavy based pan – I use my Le Creuset french oven.  Add the brown onion and garlic, saute over medium heat for a couple of minutes until soft.

Add the rice and stir well for at least 3 minutes.  The idea is to get the rice well coated with the oil and the garlic/onion flavor.

Skim about 3/4 cup of the porcini mushroom liquid (it’s really dark, like tea) and add to the rice pan.  It should all hiss and bubble – reduce the heat so you can get a gentle simmer.  Stir often until the liquid is gone.

Now add to the rice pan about 3/4 cup of the chicken stock you also have heating on the stove top.  Stir often until the liquid is gone.  The rice should start to go quite creamy, and become opaque as it begins to cook through.

Keep repeating this process until there is just a small bit of chicken stock left in the warming pan.  Taste the rice as you go to make sure the cooking process is at the right stage.

Add the last portion of stock AND the chopped spring onions, bell peppers, thyme and porcini mushrooms to the rice pan.  Depending on how the mushrooms are presented, you may also want to chop them up a little before you add them to the rice pan.  Stir well and often.  I often increase the heat a little at this point, to help cook all the additions.

NOW is a good time to turn up the heat on the pot you will cook the asparagus in and bring it to a rolling boil. Add the asparagus pieces and cook for exactly 2 minutes and 30 seconds.  Yes – exactly that time, no more, no less.  Drain immediately and set aside.

Once the last of the liquid in the rice pan is basically gone, stir through the asparagus, then stir through the Parmesan cheese.  Remove immediately from the heat.  Add a little cracked pepper.

Porcini Risotto with Asparagus

Serve right out of the pot.  If you subscribe to the theory that bacon is the garnish of the gods, add some.  I do!

If you wish to make a more substantive meal, try cooking a chicken breast or pork filet separately, slicing it and adding it to the pot just before serving.

The porcini mushrooms have quite a strong flavor, which can easily survive a pairing with a full bodied red.

Enjoy.

Southern Style Pea and Ham Soup

What does an expat Aussie living in Canada know about Southern Style cooking? Not much, but I’m learning all the time. We’ve all but lived on this soup this winter – it has evolved into a firm family favorite.  The spices, peas and ham help to warm your soul on those cold, gray, miserable West Coast days.

If you try it, do let me know how you enjoy it, or how you modified it for your taste.

Black Eyes Peas

Ingredients

1 cup dried black eyed peas, soaked overnight (or at least for 4 – 6 hours)

Splash of olive oil

2 x leeks

1 x large onion

1 x parsnip

Fresh vegies

2 x carrots

1 x large potato

2 x tomatoes

2 x celery stalks

1/2 bunch spring onions

1 x 800ml can diced tomatoes

2 x tablespoons tomato paste

1 x litre good quality chicken stock

Wood-fire Smoke Bacon - thick cut

3 x rashers good bacon

1 x ham hock (use a big one if you can, or 2 x small ones)

2 x heaped teaspoons dried basil

2 x heaped teaspoons cumin powder

1 x teaspoon chilli flakes

Garlic – to your taste

Dash of Tabasco sauce

Extra water – depending on how big your pot is.

Method

Bring the peas to a rolling boil in a medium size saucepan, reduce heat, cover and simmer for 30 minutes.  You don’t want them to be mush. Drain then set aside.  I usually start cooking these about 10 minutes before I start preparing the soup.

Using your biggest, heavy-base pot (I use a Le Creuset French Oven), splash in the olive oil and put the heat to just below medium – not too hot.

Halve the leeks lengthwise and clean them thoroughly, then slice thinly.  Add to the pot.

If you like garlic, add enough for your taste.  Stir.

Peel the onion, then chop finely, add to the pot.  Stir.

I’m blessed to have access to some fantastic bacon – wood-fire smoked bacon, cut thick, with the most amazing aroma.  If you can only get ordinary bacon – it will do.  Cut bacon up into bits or strips, add to the pot.  Stir.

Gently saute leeks, onion and bacon

Peel and chop the parsnip, carrots and potato into small pieces.  Add to pot.  Stir.

Chop the celery, spring onions and fresh tomatoes into small pieces.  Add to pot.  Stir.

Clockwise from top: dried basil, chilli flakes, cumin

Stir through the dried basil, cumin and chilli.  Feel free to adjust the proportions to your taste.  Cumin in this quantity may be a little overwhelming if you are not used to it, but trust me, you’ll acquire the taste!

Add the black-eyed peas and stir through.

Add chicken stock, can of diced tomatoes (juice and tomatoes) and tomato paste.  I usually turn the heat up a point or so now, to get everything going.  Stir fairly regularly for a few minutes.

Meanwhile (between stirs), peel the skin off the ham hock by cutting the skin on the hock, grabbing an end and peeling it off.  You might need to use a knife to separate the meat from the skin in places. Discard the skin.

Ham Hock - pull off the skin, chop the meat

Once the skin is removed, just hack up the meat. Nothing elegant is required – the idea is to get as much meat off the bone as you can into small pieces – saves you having chunks in the soup that are too big to fit in your mouth!

Put the meat AND the bone into the pot. Stir some more. The meat still attached to the bone will cook off.

Top up the pot with some water.  Don’t fill right to the top, you need a little bit of “bubble” room.

Add a dash of Tabasco sauce – it’s strong, so don’t get carried away.  Tabasco seems to add a lovely base note to spicy dishes, balancing out a little of the heat from the chilli.

Simmer for several hours, until all the meat is off the bone.  I usually cover the pot, but leave the lid on a slight angle, to let the steam escape. Stir every 30 minutes or so.

Southern Style Pea & Ham Soup

There are more than enough carbs in this dish, so I don’t find it necessary to serve it with bread – although something crusty and Italian would go nicely.  I always garnish with grated fresh parmesan or romano cheese, and fresh basil.

Bon Appetit!

Choc-Orange Brownies

I never used to really “get” brownies until I lived in a cold climate – you can make them lickety-split even if the butter is rock hard.  So I’ve been playing around with them, having some fun and experimenting with different combinations.  One of my favourite combinations from childhood is orange and chocolate (remember Jaffas – a cultural icon from antipodes??).

So if you’re up for some zingy brownies that are an absolute taste sensation, try these.

Ingredients:

• 1 large navel orange.  Zest off the skin and keep it.  Cut the zest-less orange in half and squeeze the juice from one half (about 2 tablespoons worth) and keep.  Eat the other half (frugal).
• 125 gm butter
• 200 gm good quality chocolate (I used semi-sweet chocolate – anything that is 70% cocoa or better will do just fine)
• 1 cup sugar
• 2 eggs
• 1 cup self raising flour
• 1 tablespoon cocoa powder
• 100 gm citrus peel (the stuff you would put into a fruit cake or Xmas mince pies)
• 100 gm walnuts (whole or chopped; or any other nuts that take your fancy – don’t recommend salted ones though)

Method:

Preheat oven to 345F (about 175C).

Put butter and chocolate into a glass bowl and sit on top of simmering hot water until all melted.  Stir it to make it nice and smooth.  Probably best to not allow the hot water to actually touch the bottom of the bowl – makes for nicer chocolate.

When the choc/butter is completely melted, remove the bowl from the simmering water (and don’t forget to turn the stove off!).  Stir in the sugar.  This has the effect of somewhat dissolving the sugar and cooling down the mix.  You don’t want it too hot – just barely warm.

Stir in zest and orange juice.  Mix well.  Takes a little bit of effort to get the orange juice to mix in.

Stir in the eggs one at a time, mixing thoroughly.  If the mix is too hot, wait for it to cool down BEFORE you put the eggs in – you don’t want them hard boiled!

Fold in sifted flour and coca.  Don’t beat it, gently mix it.  Then stir in peel and nuts.

Put mix into slice-size baking tray that is lined with baking paper.  I don’t grease anything these days – I just use good quality baking paper.  So much less mess!

Cook for 40 – 45 minutes or until cooked through.  Allow to cool in the pan before cutting into slices.

Enjoy!

Fav Blueberry Muffins

I haven’t met a person yet who doesn’t love blueberry muffins … my husband is particularly partial to them.  Just as well they are so easy to make ….

BlueBerry Muffins

Heat the oven to 395-400 degrees F (200-210 C).

Melt 5oz (140 gms) butter in the microwave, and set aside to cool down.

Whisk 2 eggs in a small bowl, then whisk in:

• 1 1/2 cups of milk
• 1 teaspoon pure vanilla

Put egg/milk mixture aside right next to the cooling melted butter (so you don’t forget to add the butter at the end!!)

In a blender, blitz 1/4 cup rolled oats until they are reasonably fine, like breadcrumbs.  Put oats into a big bowl, then add (by sifting together):

• 2 1/4 cups self-raising flour
• 1 teaspoon ground ginger
• nutmeg to taste

Then add 1/2 cup sugar.

Mix the dry ingredients in the big bowl together – I use a whisk to just combine them and make sure there are no lumps in the sugar.

Make a well in the middle of the dry ingredients in the big bowl.  Add BOTH the cooled, melted butter AND the egg/milk mixture (how many times have I almost forgotten one or the other of these two??).

Mix all this up until it is just combined – don’t beat it – it needs to be a bit lumpy.  Do check, though, that there isn’t a lump of flour at the bottom of the bowl.

Stir through a punnet of fresh blueberries.

Load up your muffin pan – don’t forget to grease it!  Cook for 25 – 30 minutes.  Check after about 25 minutes as you don’t want to overcook them … it makes them tough.  Let the muffins cool in the pan for a few minutes – this might require you to hold a sharp object and standing protectively over the muffin pan!  The muffins don’t fall apart quite so easily once they have cooled a few degrees.

Enjoy.

Crown Prince Shawl Variation – Finished!

Completed Faroese Shawl, Crown Prince Variation

I’m delighted with my Estonian idea using the Crown Prince motifs in my Faroese shawl, but the knitted off edge in the Estonian style just didn’t want to co-operate. I think it is the beautiful silk yarn, it has no loft to speak of and doesn’t do nupps very well.

So back to the drawing board and found Berna’s Torchon Lace on http://knitting-and.com and also referenced in Ravelry.

I’ve made some modifications to the border by adding an extra stitch, changing the connection panel (the bit close to the shawl), removing the centre eyelet, doing most of the main border in stocking stitch, and adding beads. The border is knit on to the live border stitches of the main shawl in the VLT style. The border chart can be downloaded in PDF.

Torchon Lace Variation

By picking up the little star bead pattern that naturally comes out of the Estonian pattern in the body -here beads have replaced the nupps, I’ve managed to connected all the elements together.

I was a little concerned that the beads would be too heavy for the silk yarn, but that has not been the case. In fact the beads give the whole shawl a very nice “feel” and enhances the natural fall that all silk objects have. The shoulder dart also helps keep the slippery silk in place. The beads used are 8/0 Miyuki Triangles, probably about 35gms worth – thank you Kelly at Beadweaver.

Body of shawl with Torchon Border

Now I have to pack it up and send it to my friend in Australia. I just know she is going to love it.