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.
Bulk loading seems shrouded in mystery. You can’t directly bulk insert values using a query. However, you can bulk load using a tool like BCP, an API like SqlBulkCopy or via a query that tells SQL Server to read the rows out of a file and insert them (
BULK INSERT or
OPENROWSET(BULK…)). Then, if you use one of these means to do a bulk load and watch server activity using a Profiler trace or extended events, you’ll see an insert query but no row data. What’s going on?! Continue reading
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.
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.
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
Microsoft Visual Studio gives special treatment to a README.txt file found in a NuGet package’s root directory. If README.txt is present in this folder, Visual Studio displays its contents when the package is directly installed (vs. when it is installed as a dependency).
This sounds like a great way to display notes and examples to the developer getting ready to use your package—but how do you get a readme file copied from your project into the package during the packaging process? Continue reading
Yesterday, we released BenGribaudoLLC.IEnumerableHelpers.DataReaderAdapter as a NuGet package!
What does it to?
Adapts IEnumerable<T> to the IDataReader interface. Enables enumerable sequences to be used where a data reader is expected.
Great for loading data from a List<T>, LINQ expression or CSV parser into a database using SqlBulkCopy! Streams data to SqlBulkCopy, bypassing the need to first materialize the entire sequence in memory and load it into a DataTable. 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'))?
WHERE EXISTS(SELECT Col1 INTERSECT SELECT Col2)
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
Manipulating data directly in a table isn’t always practical. On occasion, performance requirements may dictate that the revised or replacement data set first be assembled in a separate table (a staging table) then switched in to replace the currently live data. Continue reading
I like to expose myself to programming languages rooted in different paradigms so that I can expand my skill set, broaden my thinking and learn new ways to solve problems. Lately, I’ve been exploring the world of pure functional programming by studying Haskell.
My object-oriented mind finds the following fascinating:
naturalNumbers = [1..] -- [1,2,3,4,5,…]
This simple statement defines a list starting with 1 and increasing by 1 each step all the way to infinity. The result is not a range that can be enumerated to infinity (for example,
1..1.0/0 using Ruby’s Range class); it’s an actual list that goes to infinity. Continue reading