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.


Notes

  • Clarification/correction in regards to the sparse columns/column set limitations question (around 01:07:44):
    • A column set is an XML representation of the sparse columns, similar to a computed column. Sparse column data is not physically stored in the column set. However, since the column set is returned as an XML-typed column, the data in the sparse columns must fit within the 2 GB size limit of the XML type.
    • Adding a column set to a table converts it to a wide table which increases the allowed number sparse columns to significantly more than the normal 1,024 columns-per-table limit but also introduces performance considerations.
    • Sparse columns are not stored as large-object (LOB) columns. Using them does not increase the maximum data size allowed per row. Instead, the maximum bytes per row is slightly reduced (see wide table link, above).
  • For more about hidden result set columns, see Security Concern: Hidden Resultset Columns

If you’re in the Oklahoma City area on the 3rd Monday of the month and have free time between 6–8 pm, drop by OKCSQL’s monthly meeting.

Leave a Reply

Your email address will not be published. Required fields are marked *