In some situations, deadlocks may need to be dealt with not by changing the source of the deadlock, but by changing handling the deadlock gracefully. An example of this may be an external subscription that runs on a schedule deadlocking with another process. If the subscription deadlocks then it would be ok to just kill it and not allow it to interfere with the source process. However if it raises an error, then the source of publication pull will fail. First to make sure one of the deadlocking procedures becomes a victim over the other, a deadlock priority can be set in order to tell SQL Server which process it should kill first. If the priority is set to “high”, then the procedure will not be the victim of the deadlock, (that is, if the other process does not have a priority). Now to handle the deadlock gracefully, wrap it in a try / catch. If the process is the victim of the deadlock, can simply be rolled back without the error bubbling up to the end-user. To give an example of this, let’s simulate a deadlock by creating two tables and write to them from separate query windows. First we’ll setup the tables. [cc lang=”sql”] ——— setup ———- CREATE TABLE ##t1 ( i int ) CREATE TABLE ##t2 ( i int ) INSERT INTO ##t1 SELECT 4 INSERT INTO ##t2 SELECT 5 ——— /setup ———- [/cc] Now copy the two code snippets into separate windows and see the results. […]
Continue reading ...
The substring function takes 3 arguments. The first argument is the actual string in which you want to extract from. The second argument is the start position, and the third argument is the number of characters to extract. [cc lang=”sql”] DECLARE @BaseString varchar(max) SET @BaseString = ‘Quick Brown Fox’ SELECT SUBSTRING ( @BaseString — The base string to extract from ,7 — Start Position ,5 — Length of Characters ) [/cc] The above query returns the value ‘Brown’. Note: This is equivalent to the Oracle function: substr
Continue reading ...
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 ...
Generating a date table on the fly is nice to have for many reasons. The most recent of my adventures required determining when employees did not enter their time in their timesheets. I know I had a tattle tale job, the ironic part is that I ended up getting in trouble the next day for not inputting my hours. So, as for determining the dates when no one entered their time, the only way to determine this, was to cross join against a table of complete sequential dates. Yes it is an expensive query, however it’s the only way I think it can be done. In this exercise, I needed to create a date table containing all dates for the last month. I needed to do this in order to cross join against all the days the employee had logged in order to retrieve a summary of hours. If an employee did not enter any hours for a particular day, then the day itself would not be represented on the report in order to show zero hours. [cc lang=”sql”] — Create our date table using a — Common Table Expression (CTE) DECLARE @EndDate datetime DECLARE @StartDate datetime — 30 days ago SET @StartDate = GETDATE() – 30 — today SET @EndDate = GETDATE() ;WITH Dates(DATEPARAM) AS ( SELECT @StartDate AS datetime UNION ALL SELECT DATEADD(DAY, 1, DATEPARAM) FROM Dates WHERE DATEPARAM < @EndDate ) SELECT * FROM Dates OPTION (MAXRECURSION 10000) [/cc] Now we join this table on the actual […]
Continue reading ...
