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

.Net Core .csproj – Automatically Packaging README.txt

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

Introducing DataReaderAdapter: Adds AsDataReader()/AsDataReaderOfObjects() to IEnumerable<T>

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

Equating NULLs with EXISTS + INTERSECT

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

Haskell’s Infinite Lists = Fascinating + Mind-Stretching

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

For a Future Version: Re-Engineer Microsoft Access to Use a SQL Server Backend

At the core, both Microsoft Access and Microsoft SQL Server are relational database management systems (RDBMS), yet each uses a separate file format and code base. These differences add friction to the process of integrating the two technologies. Developers interacting with both back ends need to learn the idiosyncrasies of two systems. For administrators, the fact that each uses a different driver requires maintaining multiple driver configurations on systems that need to communicate with both. Power users are faced with the pain of figuring out how to cross this technology divide when growth dictates switching a home-grown Access application to a SQL Server backend. 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