Create or Replace

Blog series: SQL Server Connect Items

How many times have you been forced to change a “CREATE” statement to “ALTER” because “There is already an object…”:

Msg 2714, Level 16, State 3, Procedure XXX, Line 1 There is already an object named XXX in the database.
Msg 2714, Level 16, State 3, Procedure XXX, Line 1 There is already an object named XXX in the database.

And then you need to change it back to “CREATE” in the script, or else it won’t work when deploying. This will happen if you try to issue a “ALTER” statement on a object that does not exist:

(more…)

Continue ReadingCreate or Replace

Rowlock can block more than a row

To avoid the blocking of other users, rowlocks are sometimes used to create locks on the most granular locking level in a table. In theory, when user A makes changes to a row, user B can read, change or delete any other row than that. But in practice, user B can still become blocked by user A altough they are not accessing the same row. It all depends on how data is accessed.

(more…)

Continue ReadingRowlock can block more than a row

Missing comma between columns in select statement

Hopefully, when you make an typing error while writing a SQL statement, you get the red curly lines under the offending part of the query showing you where the error is. If you execute the following statement, you’ll get an error message:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'FROMTABLE'.
Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘FROMTABLE’.

However, some typos doesn’t create an syntax error. Instead it alters the meaning of the t-sql statement in an perhaps unwanted way. Check out the following example.

(more…)

Continue ReadingMissing comma between columns in select statement