Alexander Chigrik is a regular contributor to www.sswug.org. He has always caught my attention by sniffing out un-documented features in each release of SQL Server, demonstrating how they may be of use. He has scored once again with his sswug editorial ,S”ome useful undocumented SQL Server 2016 extended stored procedures”. https://sswug.org/alexanderchigrik/sql-server/some-useful-undocumented-sql-server-2016-extended-stored-procedures/ There are a few reasons Microsoft does not publish […]

How modular should your SQL programs be? Over time my answer hasn’t changed much. What has changed is my reasoning. My original position was based on the behavior of SQL Server specifically. In older versions, if you had branching in your query to do different kind of work based on parameters or data, and you included the implementation of each […]

What if you had to do everything a SQL Query Plan generator does? Well,, that’s how it was back in the dark ages. You had a lot of control over performance, but the complexity it added was quite cumbersome. In the old days, data wasn’t stored in relational tables. Depending on the data engine, the data was organized quite differently. […]

I’ve been talking with folks who are considering a major upgrade to a key application that impacts core aspects of their business. This project is big enough it is going to take years to complete the implementation. So the first thing I am thinking to myself is how to keep data moving to and from all the other systems from […]

In response to using database migrations for unit testing, Marc L writes, “I’m relatively new to the “migration first” method, so how’s this: for the unit-test environment, we use the “dynamic” method above–all data that is inserted or modified for a given test is isolated to that test. However, instead of working from a backup or anything like that, we […]

Today I wanted to finish up on the concept of test data for Unit testing. We talked about using Table Value Functions or other SQL objects that may be called from our testable database code such as stored procedures. We talked about using schemas to dynamically point to a different instance of a table created with data specific for unit […]

Today I am demonstrating one way of using the SCHEMA capability of an SQL Database to help automate unit testing. When doing unit testing you usually want the input to be static so that you can assert what the valid accurate output should be. If the input is constantly changing, then your testing can only validate if results were returned […]

Here’s a technique you may find interesting when working with SQL queries. One of the things I like to do is to create Unit Tests for my SQL procedures or functions. Sometimes this is more difficult to do in SQL because the majority of your work is based on sets. The sets come from your database. Therefore, if anything changes […]

One of the coolest things Microsoft added to SQL Server was the ability to create user defined functions. There are different types of functions you can create. You can create a scalar function returning only a single variable. You can create set functions returning a virtual table, much like a view. You can also create your own aggregate functions, although […]

Dot Net faces a number of Y2k events more frequently than ever. The number of releases of the Dot Net framework has been stepped up, sometimes as often as two in a single year. However, the number of previously released versions under active maintenance has not increased. This means that the code you write today will be un-supported in two […]