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
AddThis

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 ...

Events coming up

  • Jul 22

    IT Job Seekers Greater Boston (Java, .NET, SQL, LAMP, etc.) July Meetup

    Boston, United States

    6:00pm to 6:30pm: Refreshments and networking6:30pm to 7:00pm: Individual introductions to the group7:00pm to 7:30pm: Job Hunting Tips- interviewing tips- what is hot, what is not- job hunting resources, places to look7:30 - 8:00pm: More networking!If you are a hiring manager, recruiter, or entrepreneur you are welcome to bring with you handouts about your company and opened positions.

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