Library code snippets

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

Comments

  1. 01 Jan 1999 at 00:00

Leave a comment

Sign in or Join us (it's free).

ElementK Journals

Related podcasts

  • Rocky Lhotka on Data Access Mania, LINQ and CSLA.NET

    Scott talks with developer and author Rockford Lhotka about the attack of the DALs (Data Access Layers). How can we put LINQ to SQL, LINQ to Entities and classic multi-tiered design all into a larger context? What's the right strategy for your data access needs? Scott's got questions and Rocky's ...

Want to stay in touch with what's going on? Follow us on twitter!