Browse by Tags
» Transact SQL
» Database Programming (RSS)
Showing page 1 of 2 (20 total posts)
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 committed isolation using row versioning provides statement-level read consistency''. ...
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 a burden and slow you
down rather than help you out.Joel Spolski recently wrote a ...
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 by Steve Kass. Unfortunately, all these solutions use scalar UDFs and as such are very slow, ...
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 byte of the value only. All 256 buckets had values:
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 is bypassed. Let me provide some
examples. I do not intend to provide a comprehensive list ...
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 computed
one, and a foreign key constraint to implement this rule, and I ...
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 will provide two examples, two rather common scenarios that
may potentially cause loops to ...
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
in SQL Server 2008.
However, even if you never use SET ROWCOUNT yourself, some legacy code can still use it – ...
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
Defensive programming is an approach that is designed to ensure
high quality of ...
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 posts, you can also use triggers or UDFs
wrapped in CHECK constraints, but only trusted ...