Browse by Tags
» Transact SQL
» Database Programming (RSS)
Showing page 2 of 2 (20 total posts)
Well I have just read a post by
Jonathan Kehayias named SQL Tip: Keep your Presentation Logic in your Presentation LayerI cannot say I completely agree with it. Of course if all the following assumptions are true:You already have a presentation layerYou are using only one presentation layerYour client is good at formatting dataThe ...
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 here. However, if you have ambiguities,
SET will raise an error, but SELECT will not detect ...
Suppose that you need to implement the following logic:
Update the row
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 using UPDATE and INSERT commands. I will stress test several ...
It is well known that UPDATE ... FROM command does not
detect ambiguities. Also it well known that ANSI standard UPDATE may perform very
poorly and may be difficult to maintain, because it does not adhere to the
fundamental DRY (Don’t Repeat Yourself) principle. I will tweak UPDATE ... FROM
in three different ways so that you can detect or ...
The following pattern is quite common in database
IF EXISTS(some query) BEGIN
When such code runs in high concurrency situations, it may
not work as expected. I will provide a repro when such logic fails 40% of the
time. The following script provides a test table and attempts to ...
In Transact SQL you can assign a 10-character value to a
VARCHAR(5) variable, as follows:
DECLARE @c VARCHAR(5); SET @c='1234567890'; PRINT @c
The value will be silently truncated without raising an
error. Because of this behavior it may be very easy to make mistakes. For
example, consider the following table ...
Stored procedures using old-style error handling and
savepoints may not work as intended when they are used together with TRY …
CATCH blocks. I will provide some examples. This post continues the series on
defensive database programming.
Avoid calling old-style stored
procedures from TRY blocks.
Stored procedures ...
In most cases LIKE conditions should by followed by ESCAPE clauses. Let me give you an example. Consider the following table, sample data, and stored procedure:
CREATE TABLE Data.Messages(Subject VARCHAR(30), Body VARCHAR(100));
INSERT INTO Data.Messages(Subject, Body)
SELECT 'Next release delayed',
'Still fixing bugs' ...
If you do not qualify columns in your query, which means that you do not specify from which tables your columns come, you may have problems if the database schema changes. For example, consider the following sample tables and a select query: CREATE TABLE Data.Shipments(Barcode VARCHAR(30), SomeOtherData VARCHAR(100))GOINSERT INTO ...
The behavior of NOT IN clause may be confusing and as such
it needs some explanations. Consider the following query:
SELECT LastName, FirstName FROM
LastName NOT IN('Hedlund', 'Holloway', NULL)
Although there are more than a thousand distinct last names
in AdventureWorks.Person.Contact, the query returns ...