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 […]
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 ...
In my most recent adventure, I was tasked with creating a real-time push subscription to our companies distributed publication server. This effort kicked off a lot of ideas, foremost being the “Asynchronous trigger” promoted by the service broker. I used the example found in the back of the book Pro SQL Server 2008 Service Broker. While this asynchronous trigger is asynchronous in respect to “writing to an internal SQL Server object” (i.e. a table) it still takes a bit of time to execute (and thus return the initial transaction). I was disappointed to now associate the word asynchronous to “timely”. Service broker has (and will) bring about many advances in the near-term future, however the speed of submission and true asynchrony will hopefully be revisited sometime soon. The second option for performing the real-time push is to use a synchronous trigger, or an “after update” trigger. The after update trigger would push the record to a common table which would then be polled by a job. Initially I thought there was a way to insert to this common table then execute another trigger on this table to publish the record. However I soon learned that all triggers that call triggers contribute to the same transaction. When one trigger inserts into a table and that table also contains a trigger, this is a “nested trigger”. The reason that nested triggers are a concern is because the first call that performs the initial insert does not return until the last trigger in […]
If you landed on this article, then you most likely know that temp tables can cause recompilation. This happens because temp tables are treated just like regular tables by the SQL Server Engine. When the tables (in which underlying queries rely on) change significantly, SQL Server detects this change (using auto update statistics) and marks the dependent queries to be recompiled so the next execution can create an optimal execution plan. A similar sort of phenomenon also happens in temp tables. With SQL Server 2005+, recompilation now occurs at the individual statement level rather than stored procedure level. This is advantageous because when input parameters change in a stored procedure the majority of the stored procedure can still utilize the cached execution plan. This is especially beneficial to stored procedures that contain many statements. In this example, we will create an example showing recompilation, how to detect it, and how to prevent it. First, we’ll create a procedure that selects a parameterized number of rows from a table. We’re going to use a temp table for purposes of this example. [cc lang=”sql”] CREATE PROCEDURE spGetPresident ( @Rows int ) AS BEGIN SET ROWCOUNT @Rows SELECT * INTO #Presidents FROM dbo.Presidents_Tmp SELECT * FROM #Presidents SET ROWCOUNT 0 END [/cc] Next, before we execute the procedure, we will set a trace up to determine if we are recompiling. In management studio, go to tools -> SQL Server Profiler Connect to your server then choose the ‘Events Selection’ Tab. Unselect all the […]
Continue reading ...
