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.