union vs or clause

I bet many of SQL developers have attempted or have written SQL code like this:

select    a.*
from    tblA a
join    tblB b
on        a.DiscoverySerialNo = b.SerialNo
or        a.DiscoverySerialNo = b.DiscSerialNo

You must have noticed that code like this takes longer than usual, or even worse, never returns result.

I haven’t analyze this so cannot tell exactly what the query plan looks like. There are a couple of other ways to achieve the same results without using OR in JOIN ON clause, and they are life savers in terms of improved performance.

One way is to use UNION:

select    a.*
from    tblA a
join    tblB b
on        a.DiscoverySerialNo = b.SerialNo
select    a.*
from    tblA a
join    tblB b
on        a.DiscoverySerialNo = b.DiscSerialNo

Make sure that UNION does not introduce duplicate matching records because of different values in the selected columns. In another word, using getdate() function in SELECT is not a good idea.


create view syntax(column1, column2, column3)

I’ll be the first person to admit- I’ve ALWAYS been a huge fan of views (instead of sprocs) because they really make things EASIER.

I’ve originally read about the importance of using views 12 years ago when I was playing around with Analysis Services.

I’m starting to finally use this notation- I wish I had start doing this 12 years ago

I just think that naming the columns in the create view statement is more elegant than giving each column an alias.


create view dbo.DWF_INVENTORY_ON_HAND_RealTime




Power View for Cubes


Power View for Cubes


By Ivan Paniagua, 2013/05/21


In this article I will evaluate the latest functionality provided by Microsoft SQL Server 2012 – Power View for Multidimensional Models. This SQL Server 2012 Community Technology Preview (CTP) release allows connections between Power View and Multidimensional Models (cubes),  in addition to the Tabular Models which have been supported since the release of SQL Server 2012 RTM/SP1.


LightSwitch HTML Client Runtime Updated

Microsoft has updated the HTML client runtime in LightSwitch, its lightweight Web development tool. “Runtime Update 1”, as it’s called, is a bug and compatibility fix for the latest versions of jQuery, jQueryMobile and datajs.

The update adds support for jQueryMobile 1.3 and jQuery 1.9, according to this blog posting from the LightSwitch team. “Embracing the mobile-first, instead of mobile-only, approach was the main focus of this release,” the team states.

The key upgrade to the runtime, it appears from the blog, is the implementation of responsive design. Responsive design automatically resizes an interface according to the screen size of a particular device. (It’s something we did on VisualStudioMagazine.com; we’ve been writing about some of the challenges.)

The update is available through NuGet. The first step is to update the “Microsoft.LightSwitch.Client.JavaScript.Runtime” package, which also grabs the latest supported dependencies. The next step is to increment the version numbers of the JavaScript and CSS files in the default.htm file (the blog specifies which files). Note that you’ll need to do that for every LightSwitch project you have.




Was Dijkstra wrong? Is GOTO good for novice programmers?

.. programming education .. may by chance have thrown the baby out with the bathwater, just as the reform maths educators did with calculators in schools.

In the old days, before calculators, children had to learn to do long multiplication, for example 431 × 27, using pencil and paper. This was a bit of a chore, now largely dropped in favour of tapping the problem into a calculator, which gets the answer more reliably. However, it turns out that the children were learning a lot more by accident than how to multiply long numbers. They were learning to set out their working in an error-displaying form, essentially a twentieth century proof that their answer was correct. They had to be absolutely precise in their working, but if they made a mistake, they could check their working, find the mistake and correct it. Their teachers could periodically take in this work and confirm that their pupils were working accurately and in a proper error-displaying way. Not only all that, but children were intuitively learning something about the mathematical structure of numbers by working with them, so that when they came to polynomials they found that working out (4x² + 3x + 1) × (2x + 7) was not much different to working out 431 × 27. (In fact it’s a bit simpler, because there are fewer carries.) To someone with a calculator, they are entirely different.

I wonder if, in the way we try to teach programming nowadays, we may have fallen into some similar traps, by not realising what students accidentally learned in the past.

For example — and here’s a heretical thought — are languages with GOTO really as bad as we imagine for noviceprogrammers? Dijkstra claimed that “It is practically impossible to teach good programming to students that have had a prior exposure to BASIC: as potential programmers they are mentally mutilated beyond hope of regeneration”. But don’t forget this was just a claim: where’s the evidence? Dijkstra himself obviously first learned to program in a language with only GOTOs, as I did, and I’m fairly happy that it did us no lasting damage. In fact I think it forced us to think about our code and to work with it in a particular detailed way, and this earlier practice may have served us well later, even when we programed in structured languages.