Bitwise and Derived Table revisited

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.

[codesyntax lang=”tsql”]

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

[/codesyntax]

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.

[codesyntax lang=”tsql”]

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

[/codesyntax]

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.

<span style="color: #ff0000;">Msg 8155, Level 16, State 2, Line 4</span>
<span style="color: #ff0000;">No column name was specified for column 1 of 'MyTable'.</span>
<span style="color: #ff0000;">Msg 8155, Level 16, State 2, Line 4</span>
<span style="color: #ff0000;">No column name was specified for column 2 of 'MyTable'.</span>

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