Recently I helped out on a project where a company was moving to a new CRM system. Part of the validation process was to compare identical old vs new tables, column by column, and report any discrepancies between each value.... and there were dozens of tables and hundreds of columns. So I decided to automate … Continue reading Automating Full Outer Joins →

Hello everyone! I am back with a new set of puzzles (18 to be exact). As the title suggests, these 18 puzzles revolve around permutations, combinations, sequences, and random numbers. And of course the solutions are provided as well (written in Microsoft SQL Server's T-SQL). This set of puzzles includes using recursion to create a … Continue reading Permutations, Combinations, Sequences and Random Numbers →

One of the more interesting aspects of database management systems is the concept of NULL markers. Because they are a source of great confusion and much debate (for more on this debate, I highly recommend C.J. Dates' Database in Depth: Relational Theory for Practitioners) I decided to create a summary of all the different SQL … Continue reading Behavior of NULLS →

For this article, we are going to examine the benefit of using stored procedures that simply return a result set (i.e. a stored procedure that has a SELECT statement). Before we do a shallow dive into this functionality, it is worth noting that SQL Server has 3 methods of returning data from a stored procedure: … Continue reading Stored Procedures: Returning Data Using a Result Set →

If you have always wondered what the different symbols and formatting mean when viewing syntax examples, Microsoft has the following page. Transact-SQL Syntax Conventions (Transact-SQL) - SQL Server | Microsoft Docs It's always a good exercise to go through the DDL statements like below and take stock of which options you understand or need to … Continue reading SQL Syntax Conventions →

Welcome to a new year. I haven't utilized the blog portion of my website yet, but I figured a nice year-end review would be a good topic. I'm not expecting a lot of people to read this, so if you are, hello and welcome! This site has become quite popular considering its niche subject of … Continue reading Happy New Year: 2022 →