|
|
|
|
Browse by Tags
All Tags » Database Programming (RSS)
-
When a select uses scalar or multi-statement UDFs under READ_COMMITTED_SNAPSHOT, we might not get consistent results as of the time our select began - I will provide simple repro scripts. At the time of this writing MSDN clearly states the following: Read More...
|
-
I have been practicing database unit testing for two years, and I am a big fan of it. My test harness is a great help to me - it lets me change my modules quickly and with confidence. However, having too many unit tests is counterproductive - they become Read More...
|
-
Problems such as calculating third Wednesday of the month or the last day of the month are very common. Recently Peter Larsson posted a scalar UDF that solves problems such as "third Wednesday of the month" , and Uri Dimant added a comment with a solution Read More...
|
-
I ran a quick check, and it looks like a reasonably even distribution to me. I genenerated 2M NewId values, as follows: SELECT NewID() AS RandomColumn INTO #t FROM Data.TableWith2Mrows I looked at the distribution into 256 buckets, considering the first Read More...
|
-
Some T-SQL code is written under the assumption that either a TRY block successfully completes or a CATCH block is invoked. Most likely, this is the case. However, there is a third, although rare, possibility – the TRY block may fail, and the CATCH one Read More...
|
-
Suppose that you need to enforce the following business rule: contracts cannot be changed after you have started working on them (let us assume that that particular business operates in the perfect world). You can use a ROWVERSION column, a persisted Read More...
|
-
Although there are many discussions about which kind of cursor or loop performs the best, there is no doubt which loops perform the worst – the infinite ones of course. Whenever you write a loop, you need to make sure that it never runs infinitely. I Read More...
|
-
I have written up two examples when a SET ROWCOUNT command breaks a seemingly working stored procedure or trigger. Note that currently the best practice is to use TOP clause instead of SET ROWCOUNT, which is deprecated in SQL Server 2008. However, even Read More...
|
-
I have been posting examples of defensive database programming for some time now. I am by no means done with this topic, there is much more to it. Yet this time I would like to skip concrete examples and write up a long overdue introduction. Defensive Read More...
|
-
I will demonstrate how use constraints to make sure that intervals of time have no overlaps. Also you may ensure that there are no gaps either, but if you choose to allow gaps, then the query to retrieve them is very easy. As discussed in my previous Read More...
|
-
Well I have just read a post by Jonathan Kehayias named SQL Tip: Keep your Presentation Logic in your Presentation Layer I cannot say I completely agree with it. Of course if all the following assumptions are true: You already have a presentation layer Read More...
|
-
Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before. Assigning multiple values via SELECT performs better , and you don’t have to repeat your code several times, as described by Tony Rogerson here and Read More...
|
-
Calculating running totals is notoriously slow, whether you do it with a cursor or with a triangular join. It is very tempting to denormalize, to store running totals in a column, especially if you select it frequently. However, as usual when you denormalize, Read More...
|
-
You cannot use CHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECK constraint. What if you actually need to compare columns in different rows or in different Read More...
|
-
Suppose that you need to implement the following logic: IF(row exists) Update the row ELSE Insert a new row. If you already are on 2008, you should use MERGE command, and you don’t need to read this post. Prior to 2008 this logic has to be implemented Read More...
|
|
|
|
|
|