|
|
|
|
Browse by Tags
All Tags » SQL Server (RSS)
-
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...
|
-
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...
|
-
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...
|
-
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) Read More...
|
-
The following pattern is quite common in database programming: IF EXISTS(some query) BEGIN DO SOMETHING; END 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. Read More...
|
-
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 12345 The value will be silently truncated without raising an error. Because of this behavior it may be Read More...
|
-
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 Read More...
|
-
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 )); GO INSERT INTO Read More...
|
-
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 Read More...
|
-
If your data has a small percentage of duplicates, then IGNORE_DUP_KEY may speed up your inserts. For larger amounts of duplicates, IGNORE_DUP_KEY may slow them down significantly. I set up two tables, stripping down all the irrelevant details, as follows: Read More...
|
-
<Denis Gobo’s mode on> Suppose you have an empty table: SELECT COUNT (*) FROM SampleTable --- 0 What would be the result of the following query: DBCC CHECKIDENT ( 'SampleTable' , RESEED , 1 ) INSERT SampleTable ( j ) SELECT 1 SELECT SCOPE_IDENTITY Read More...
|
-
When you run NUnit/C# unit tests against your local instance, you are a dbo. As such, you are not getting any errors caused by missing permissions. However, you can impersonate another user, and run your unit tests in the context of that other user. The Read More...
|
-
Well, I have just finished a series of articles about unit testing on simple-talk.com, and some techniques were not included, just to keep the articles short. Still problems such as reproducing a lock timeout and unit testing the retry after it (lock Read More...
|
-
There is a popular misconception that UDFs have adverse effect on performance. As a blanket statement, this is simply not true. In fact, inline table-valued UDFs are actually macros – the optimizer is very well capable rewriting queries involving them Read More...
|
|
|
|
|
|