Today I am going to revisit two posts from the past couple of weeks.  I want to revisit them just to make some minor updates and clarifications.  This is nothing earth-shattering but is good info to have.

The two posts to revisit are:

1. Bitwise Operations
2. Derived Table Column Alias

Bitwise Operations

In this particular post, I shared a simple example of how to perform bitwise operations.  The example involved the bit comparison of up to three values.  I made the query overly complicated.  Here is a less complicated method to get to the same results.

Code block

DECLARE @ColorType INT = 3
 
SELECT cp1.*
FROM ColorPlate cp1
WHERE cp1.colortype & @ColorType <> 0 
 
ORDER BY ColorID;

Can you see the simplicity in that?  Both methods work.  Looking at this code, it is a little easier to follow and understand.

Derived Table Column Alias

In the post about subqueries and derived tables, there was an important piece of information that I neglected.  In the first example I posted there is a good example of what was neglected.  The first example was a derived table based on values rather than a query.  Here is that example again.

Code block

SELECT *
FROM (
VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10)
) AS MyTable(a, b);

If you were to try to write that query without the external column alias naming convention, you would get an error.  The error message(s) would be like the following.

Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 1 of 'MyTable'.
Msg 8155, Level 16, State 2, Line 4
No column name was specified for column 2 of 'MyTable'.

Knowing this information could save you a bit of headache and time.  When using a value set rather than query, the column alias is required after the table alias.

Like I said, nothing big or fancy today – just a quick revisit to clarify some previous posts.  Oh, and I have some more good stuff coming down the pipe (like another bitwise related post).