T-SQL SELECT Random Rows in UNION

To Select random rows from a Table, use the ORDER BY NEWID()

SELECT * FROM Table ORDER BY NEWID();

SELECT TOP 10 PERCENT * FROM Table ORDER BY NEWID();

SELECT TOP 10 * FROM Table ORDER BY NEWID();

To Select random rows from a Table UNION Table, then do:

SELECT * FROM (SELECT TOP 10 Column1, Column2, Column 3 FROM Table1 ORDER BY NEWID()) TableA
UNION
SELECT * FROM (SELECT TOP 10 Column1, Column2, Column3 FROM Table2 ORDER BY NEWID()) TableB;

Dynamic MIME Type

Storing and retrieving images from the database is now made pretty easy.

But what about displaying images in Reports? We are required to provide an image type if we are to choose Database as our Image source.

This can be easily done if you an additional column of ImageName in your database alongside your Picture column. The ImageName should contain the proper extension of the image that was loaded into the database. A proper ImageName input, for example, is "megan-fox-is-a-dude.jpg".

On your Dataset query statement, include the following code:

SELECT Table.Column1, Table.Column2, Table.Column3, Table.Picture,
ISNULL(
CASE SUBSTRING(Table.ImageName,(LEN(Table.ImageName)-2),3)
WHEN 'jpg'
THEN 'image/jpeg'
ELSE 'image/' + SUBSTRING(Table.ImageName,LEN(Table.ImageName)-2,3)
END,'image/jpeg') as 'ImageExtension',
FROM Table

The CASE statement will give you the proper Image Extension based on the name of the Picture. If the Image column is NULL then it will provide a default MIMEType of 'image/jpeg' to avoid raising Errors - this is an important.

1.) Drag and drop Image to the Report Body.
2.) On the Image properties:
- set Value: =Fields!EmployeePicture.Value
- set MIMEType: =Fields!ImageExtenion.Value