Using a numbers table is helpful for many things. Like finding gaps in a supposed sequence of primary keys, or generating date ranges or any numerical range. In some cases, you will be in a production system that does not already contain a numbers table and you will also be unable to add one. In this situation, the dynamic numbers table comes in handy. [cc lang=”sql”] DECLARE @MaxNumber int = 5000 ;WITH Numbers AS ( SELECT 1 AS Num UNION ALL SELECT Num+1 FROM Numbers WHERE Num
Continue reading ...
In SQL Server, boolean values can be represented using the bit datatype. Bit values differ from boolean values in that a bit can actually be one of three values 1, 0, or NULL; while booleans can only either be true or false. When assigning bits, it is best to use 1 or zero to indicate it’s on or off status. If using SQL Server 2005 or greater, you can additionally assign a bit by setting it to a string value of ‘true’ or ‘false’. This is not recommended however for two reasons. First, SQL Server has to implicitly convert the bit value prior to the assignment (or comparison). The other reason is that it is not ANSI SQL. [cc lang=”sql”] — create the bit DECLARE @testbit bit SET @testbit = 1 IF @testbit = 1 BEGIN SELECT ‘true’ END [/cc] In the above example, the string ‘true’ is displayed. Now let’s assign the bit a value of true and treat it like a boolean. [cc lang=”sql”] DECLARE @testbit bit SET @testbit = ‘true’ IF @testbit = ‘true’ BEGIN SELECT ‘true’ END [/cc] The test code works (on SQL 2005+) and we see the same result as above, true is displayed, however an implicit conversion has taken place. While this may not be a lot of overhead, it is not ideal. It is also not recommended because there is no guarantee it will be supported. Let’s take a look at two different execution plans. One for comparing the bit using a […]
Continue reading ...
This is a topic I haven’t found much information written about, however nearly every system I’ve worked with needs this exact functionality. It is important that the method for extracting data be done in a way that does not hinder performance of the source system. In this example, the goal is to extract data from a source system, into another database (or server) all while requiring as little resources as possible. This is why I choose to pull from a source system in two separate stages. First Stage – Staging Import The first step is to do a very simple select statement into a staging table. This first select statement may do some ETL — mostly in regards to lookups that are needed from the source system. There could be multiple select statements pulling data into multiple staging tables. I prefer to pull tables from the source to staging in a one to one relationship. So for every table we need, we also have a corresponding staging table. See the diagram below: The reason for pulling one to one is simple. First of all, the query is a very simple select. Second, it makes troubleshooting very simple. After importing into staging, the next step is to move the records to the history table(s). Second Stage – Historical Import In the historical import, we compare what we have in our history table with what is in staging. Each record in staging is joined with the corresponding current record in the history […]
Continue reading ...
