Today I’d like to share two quotes I really appreciated in the comments on unit testing. Judeyak writes, “once you write the test, it lives on, manual tests are gone forever once you complete them.” How true that is. I once wrote a complicated stored procedure that required about 10 hours to complete and debug. It’s more difficult to write […]

We had some good feedback on bad data. I’d like to see if I can summarize the input from everyone into a concise package. Bad data is the result of many natural processes in an organization: Data is developed across different business units, is not comprehensive in each unit, and when brought together can result in duplication or loss. When […]

Bad data produces bad results. Who can argue with that logic? But it is something that happens frequently, for one reason or another. As data professionals, we do all we can to guard against bad data. We use techniques such as check constraints, strongly typed data, relationships, and much more. Still, there seems to be those nagging instances of bad […]

I’ve taken on the task of overseeing the development of Unit Test standards for a group in which I’m a part. So, I thought I might use this opportunity to share some thoughts, and hopefully get some feedback from others. So let me start with some philosophical things to get the conversation started. I have practiced Test Driven Development, and […]

It’s been a while since we talked about using Virtual Machines (VM) as server hosts for SQL Server. There was a time when performance on a VM was not worth the benefits provided by a VM system, and we continued to use physical systems. However, as the architecture of CPUs has changed, the reduction in performance of a VM server […]

Visual Studio Database Projects have been available for a few years. They have been used for version control, and data change migrations. One capability I have been reviewing is the ability to have automated unit testing of stored procedures and functions. Microsoft has a two layer approach to database unit tests that is quite ingenious. Their testing methodology has two […]

Do you find that the number of SQL Server instances you need to manage just continues to grow? Not everyone has that problem. But, if you do, it can get out of hand rather quickly. You need to maintain a list of all those servers, where are they located, and how do you connect to them. You may also be […]

Last Thursday I published an editorial demonstrating one use of the ROW_NUMBER function as a technique to get the last value for each group contained within a set of groups, A Little ROW_NUMBER Magic. See the example below in figure 1 as executed against my instance of AdventureWorks2012. Figure 1 – ROW_NUMBER Grouping Query WITH MyPay AS ( SELECT BusinessentityId […]

Before I leave the topic of Parallel Queries, and Connections using Multiple Result Sets, I want to share a couple things about the syntax that make this worthwhile. Just because something is written to use asynchronous methods doesn’t mean it runs concurrently. That is a big point. If you truly have two queries that are completely non-dependent, and you wish […]

I really like the ROW_NUMBER() function in SQL Server as a means of getting the first or last instance of a record for each specific group within record set containing more than one group. Ok, let me break that down a little bit. It’s pretty easy to get the last record of the line items for a single purchase order. […]