Tag Archives: SQL

Equals Is Not Always Equivalent: Power Query Joins vs. SQL Joins

, , , ,

Take the following M expression:

Table.Join(A, "ID", B, "ID", JoinKind.Left)

Does it behave like the below SQL (which is how a join between two tables on column ID would typically be coded in the database world)?


Perhaps surprisingly, no—at least, not when the simple, innocent null is involved.

Continue reading

Lightning Talk: SELECT Doesn’t Always Return the Expected Number of Columns

, , ,

Last night, I had the privilege of sharing a lightning talk at the OKC SQL Server User Group (OKCSQL). This 17-minute presentation touches on four scenarios where the way columns are returned by SELECT might not match what you expect.

When you write SELECT statements, you probably have expectations like:

  • SELECT * returns all columns in the referenced object(s).
  • Each column is returned in a separate column.
  • Each column returned in visible in the result set.

However—at least in the world of Microsoft SQL Server/Transact SQL (T-SQL)—these assumptions aren’t always true.

Continue reading

T-SQL on the Wire: What takes place between client and server?

, , ,

The answer to this question might shed light on some Microsoft® SQL Server® mysteries, might answer—or raise—security concerns and might help you administer, develop and debug better.

In this presentation, learn about Tabular Data Stream (TDS)—the protocol used for client-to-SQL Server interactions. In addition to a  high-level understanding of how TDS works, you’ll (hopefully!) come away with practical applications of this knowledge that should benefit you as a database administrator or developer.

Make It Easier for the DBA: Give SQL Connections the Application’s Name!

, , , , ,

Imagine you’re a database administrator tracing query activity on a server. You’d want all the clues you can get to help you figure out what’s going on. One clue is the name of the client program associated with each database session. This name, passed from client to Microsoft SQL Server when a connection is established, can be used by the DBA to differentiate between multiple applications running on a single client and to focus on (or filter out) activity from the same application across multiple clients.

Does your application pass a meaningful program name to the database server?

Continue reading


, ,

Can you rewrite WHERE Col1 = Col2 OR (Col1 IS NULL AND Col2 IS NULL) so that each column name is only used once without using the substitution method (that is, without doing something like WHERE ISNULL(Col1, '-2,147,483,648') = ISNULL(Col2, '-2,147,483,648'))?


What…what?! That can’t be possible! I thought INTERSECT compares two result sets, not two columns from the same row of data. Continue reading

T-SQL Ordering Myth #2: ORDER BY in Views

, ,

Since a view is a saved query and queries can specify ordering, adding ORDER BY a view definition might seem a reasonable proposition. Try it and Microsoft SQL Server chokes. Then you learn the trick: include a TOP clause and SQL Server will be a-ok with ordering clauses in views. You want all rows returned so you add “TOP 100 PERCENT” to your definition and SQL Server is happy!

Yet you notice something strange. Sometimes the rows returned aren’t sorted according to the view’s ORDER BY clause. As you ponder this puzzlement, your mind wonders back to the roundabout syntax required to finagle ORDER BY into the view definition. Is something funny going on? But the answer eludes you. Continue reading

Quick Tip: Decrementing IDENTITY Column


Did you know that IDENTITY columns can count downward?! According to MSDN, IDENTITY’s second argument, named increment, “is the incremental value that is added to the identity value of the previous row that was loaded.” No constraint is given that increment must be positive (remember from Algebra that both positive and negative numbers can be added). Set increment to a negative number and IDENTITY will generate a descending sequence of numbers. Continue reading

T-SQL Ordering Myth #1: Clustered Indexes Determine Default Sort Order

, ,

If I query a table that has a clustered index without specifying an ORDER BY clause, the resultset will be sorted according to the clustered index” may sound reasonable. After all, since “a clustered index determines the physical order of data in a table” (per TechNet), isn’t it logical to assume that a query with no ORDER BY clause returns data sorted this way? Continue reading

Object IDs: Unique Instance-Wide or Database-Wide?

, ,

The versatility of T-SQL’s object_id() function, which accepts one-part (object_name), two-part (schema.object_name) and three-part (database.schema.object_name) object names as its first argument, could seem to imply that object identification numbers (object IDs) are global across a Microsoft SQL Server instance. After all, if object_id() accepts a multi-part object name which uniquely identifies the specified object relative to the server instance, wouldn’t the object ID returned by that function also uniquely identify the same object across the server instance? Continue reading