How to include Text, nText and Image datatypes in a Union query

As you probably know, in Microsoft database parlance the Text, nText and Image datatypes all hold very large amounts of data. If you've ever tried to add these types to a Union query in Visual Basic, the query probably generated errors, claiming that the Text, nText or Image data type can't be selected as DISTINCT.

When you run a UNION query, the keyword forces the database to eliminate duplicates from the results--the equivalent of adding the DISTINCT keyword to the SQL statement. Text, nText and Image fields, by their very blobular nature, are potentially too large to be compared one by one by the database. As a result, you can't select unduplicated records when displaying fields of these data types.

Fortunately, SQL offers a way around this with the UNION ALL clause. This clause works exactly like the UNION keyword, except that it selects all records, duplicates or not. The following SQL provides an example:

SELECT id1, memoText
FROM dbo.tblCatalog

UNION ALL

SELECT id3, browseImage
FROM dbo.tblImages

You might also like...

Comments

ElementK Journals

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“My definition of an expert in any field is a person who knows enough about what's really going on to be scared.” - P. J. Plauger