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.
The resultset grid shows all columns sent by the server, right? Not always! Under certain circumstances, Microsoft® SQL Server® transmits columns that weren’t referenced in the
SELECT statement and that don’t show up in tools like SSMS.
How many columns does the below view return? Four seems like the obvious answer but it’s only sometimes correct. This view sometimes returns four columns—and other times returns nine!
CREATE VIEW dbo.EmployeeSummary AS
dl1.MonthName + ' ' + dl1.DayString AS Birthday,
dl2.YearString AS EmployedSince
FROM Employee e
JOIN UserAccount u ON e.UserName = u.UserName
JOIN EmploymentHistory h ON e.SSN = h.SSN
JOIN DateLookup dl1 ON h.HireDate = dl1.Date
JOIN DateLookup dl2 ON u.Birthdate = dl2.Date;
The extra five columns (shaded in yellow, below) are returned as hidden columns. They won’t appear in most query tools, yet the data they contain still crosses the wire and can be accessed programmatically on the client-side.
It depends. 🙂
Tabular Data Stream (TDS)—the protocol used for client-to-Microsoft® SQL Server® communications—gives both client and server three options in regards to encryption: require, support or doesn’t support. Whether SQL Authentication credentials are encrypted depends on which encryption options are in use.