A (more) concise way to test a SQL MERGE for differences if there are nulls involved

In SQL, if you need to compare two columns that are both nullable, your compare statements can quickly get out of hand (and a lot less readable along the way). While fixing a bug in my code, I found out about a way to keep the comparison short and readable using the EXCEPT (or INTERSECT) operator. Lees meer over dit bericht

If you publish a WCF service using EF to IIS, do not forget to distribute sqlserver.dll manually

I’ve spent several hours this week debugging a WCF application using Entity Framework, that was published to an IIS server, only to have every call to the service fill with the error message “The type initializer for [My]DbContext has caused an exception”. Of course, on the dev machine everything was working fine. Short answer: as the blog title implies, the application was missing EntityFramework.SqlServer.dll in its bin folder, because Visual Studio failed to recognize the fact it is referenced by the application. Lees meer over dit bericht

You cannot use a null propagating operator in an expression tree lambda

One of the features I like about the new C# 6.0 is the null conditional operator, and I tend to use it everywhere I can (and should). Until yesterday, when at a statement I wasn’t expecting it, the compiler bit me with error CS8072, An expression tree lambda may not contain a null propagating operator. If you think about it, it makes sense, but it is easy to miss. Lees meer over dit bericht

An underrated operator: using EXCEPT in MS SQL Server to track changes

Working with a colleague who is a longtime Oracle user, I noticed he used an operator I didn’t know of, to grab hold of the differences in rows between two nearly identical tables: he used a “minus”. I couldn’t imagine MS SQL Server would not have something like it and it didn’t, it came up with the “except” operator which does exactly the same.

The trick is that you use it like you are used to with  the union operator. Lees meer over dit bericht

Update to SQL server tooling for Visual Studio

MS recently released the “March 2014” update to the SQL Server tooling for Visual Studio 2013, which was named SQL Server Data Tools for previous Visual Studio versions. Among the most notable new features are support for SQL Server 2014 and several productivity updates that simplify development and testing, also on SQL Azure. Read more about it on the SQL Server Data Tools team blog here.

Worth noting for some of us, is that Visual Studio 2010 is no longer supported with new features and updates for SSDT, although the dacpac’s and other projects remain fully compatible throughout 2010/2012/2013. The update to SSDT that was released at the time VS2013 came out, was the last one. Personally, I am only keeping my VS2010 around for its Installer support, so I am ok with that.

(Add. 10-04)

Today I noticed that the order of installation of updates is important. On another machine, I upgraded SSDT first, but was then unable to install the 1.3 update to the Microsoft Office Developer Tools (12.0.30225.00, to be precise). The latter’s Web Installer the complains about missing requirements and refuses to continue:

ssdt_required

SQL Server Data Tools not installed?

SQL Server Data Tools is installed, obviously, but not recognized, probably because of newer version numbers. I don’t really mind much since I don’t actively develop using the Office Developer Tools, so I’ll just wait for the next update. But if you do use both, I recommend you should install the ODT update first, and then SSDT!

SQL server views performing really bad when referencing a linked server

We had a view in SQL server (2008) that consisted of a simple SELECT with some fields from a table that was on another database server (an Oracle database even). Performance was not a problem, the query took less than a second to complete. Until we had to add a left join to another table on that same remote server. The view suddenly took minutes to open. Of course, we first suspected the join and looked at the indices, but they were ok. Furthermore, running the same query on the remote Oracle database directly, performed great.

The query was defined like this (pseudo):

SELECT fldA, fldB FROM tblA

It was then changed to

SELECT fldA, fldB, fldC FROM tblA LEFT JOIN tblB ON (keyA=keyB)

where fldC was from tblB. But this was really slow, despite doing nothing really complicated here.

What turned out to be the problem? Because the second table was added later and field names were distinct, we had not prefixed table names for the field names. As soon as we changed the query to

SELECT tblA.fldA, tblA.fldB, tblB.fldC FROM tblA LEFT JOIN tblB ON (tblA.keyA=tblB.keyB)

performance was back to the regular level.

It appears that a field name lookup over a linked server connection from SQL Server to Oracle is really expensive. When using linked servers, I now take extra care to prefix tablenames to my fieldnames in a SELECT-statement.