Browse by Tags
» sql server
» Transact SQL (RSS)
Showing page 1 of 2 (19 total posts)
Sounds trivial? Right, but different flavors of this myth still persist. Yesterday I noticed a thread on stackoverflow discussing SELECT TOP without a where clause, where it was suggested that ''The order will be defined based on the clustered key in that table.'' ...
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 ...
We can tuck any logical expressions in CHECK constraints,
but all foreign keys are currently capable of right now is to verify that one
or more column values are equal. In many cases it would be very useful to have
foreign keys use more complex logical expressions. Also in some cases the
ability to create constraints on indexed views would ...
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 ...
When you process your rows one by one in a loop, you need to
make sure that your loop never runs infinitely. I will describe three scenarios
with possible infinite loops. There have been many articles discussing feasibility
of loops vs. set-based solutions – I will not repeat any of these arguments. Let
us assume that you need to use some ...
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 ...
Selects under READ COMMITTED may return incorrect results if
the data they select is being modified at the same time. I will provide a repro
script which on my laptop returns incorrect results in more than 90% cases.
Also I will provide a similar repro script which runs under REPEATABLE READ but
still returns incorrect results in more than ...
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 – ...
Apparently for high isolation levels some heap tables may be
more prone to deadlocks than identical tables with clustered indexes. I have a
simple repro script which successfully completes if a table has a clustered
index but embraces in a deadlock if it runs against a heap. Here is the table:
CREATE TABLE dbo.NarrowTable(ID INT ...
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 ...