Local variables and batch scope

Jul 142009

I was recently debugging a problem in a Stored Procedure where the wrong values where inserted into a table, in the middle of a long running cursor operation.
The culprit turned out to be the scope of a variable in SQL versus the scope most (object orientated) developers are used to in the language they usually code in.

The problem can be illustrated with this syntax:
DECLARE @Counter INT = 1

WHILE @Counter < 5 BEGIN
DECLARE @Var VARCHAR(MAX)
IF @Counter = 2
SET @Var = ‘SomeValue’
SELECT @Var
SET @Counter += 1
END

The way scope works for variables in T-SQL is “batch scope”, whereas most OO developers are used to the scope being limited within the innermost context.

The way it functions in SQL is that in the first iteration of the while loop the variable “@Var” gets declared but not assigned a value.
Thus, when selecting it out, the result will be NULL.

However in the second iteration, the variable is already declared once, so SQL Server will not “recreate” it, because the scope is “batch scope”.
So here it’ll just be assigned the value “SomeValue”.

The third iteration is like the second. The variable is already declared, and because it in the previous iteration had the value set to “SomeValue”, that value will persist in this iteration, and all subsequent iterations until another value is set.

This is a source of errors if not careful, because the object orientated developer will – if not aware of this – most likely read the above as only iteration 2 will contain the value “SomeValue”, whereas all other iterations will contain NULL.
But because of the batch scope, it is not so.

As the result from the query shows:

Sorting integers in a string using XQuery

Jun 292009

I stumbled across the task of sorting numbers present in a comma separated string, based on their numerical value in SQL.
Curious as always I thought it would be possible to solve such a problem using XML and XQuery in SQL and indeed it was.

Given a numerical string, then in SQL Server 2005 and up, you can do something like this:
DECLARE @X XML = ''

DECLARE @STR VARCHAR(255) = CAST(@X.query('for $i in (11, 9, 10)
                  order by $i
                  return fn:concat(xs:string($i), ",")') AS VARCHAR(255))
                  
SELECT LEFT(@STR, LEN(@STR) -1)  
(Remember, in SQL 2005, you need to put assignment on another line, this is 2008 syntax)

The output from this query will be the value: '9, 10, 11'

Now, it might not be special or terrible useful as such – but the point of the post is also much more that untraditional methods for solving problems exists, and one can think outside the box and solve many problems by doing so.

A problem with this is that if you want the input created dynamically – you either need to build the entire segment within a string and use EXEC, or perhaps you can use the sql:column and sql:variable to help you along.

Using INNER JOIN/SELF JOIN to allow for smaller indexes.

May 152009

In databases, I often need foreign keys in my tables, because I’ll want to use them to select content out from my tables. However this can often result in either bad index utilization in the selection or making additional indexes based on the foreign key(s) and the content I need to select out.

This in turn can result in ending up with “many” indexes and sometimes many big indexes.

However a method can be to make smaller indexes and use an INNER JOIN to join into the table an extra time.

I’m going to show the pattern with a relative simple example to illustrate, because it is about the pattern more so then the specifics layout, content and size of the table.

It is just a pattern/technique to keep in mind and have in the SQL toolbox.

Suppose you have two tables of a similar pattern to this:

Tables for self join example

(click for larger size)

I’ve let the SQL Server create my clustered indexes based on the primary key, which means I have a clustered index over my primary key(s).

When having to look up rows in JoinOne based on the foreign key, it’ll often look like this:

SELECT *

FROM JoinOne T1

WHERE FKOne = <VALUE>

Because I’ve added no other indexes to the tables, I’ll get an Index Scan or Table Scan when running the above query, which – as we – know is not a good thing most of the time.

This usually leads to the creating of a second index which indexes over my foreign key.

However if the table is large, and if I’ll need to extract many/most of the columns (like in my example with SELECT *), it can mean I’ll have to make either a complete index or one with many included columns, just ordered by my foreign key first.

If I make a small index with my foreign key and my primary key second, I can use this to join into my table again, and then after that use my primary key.

This is an index to illustrate:

Index for self join example

(click for larger size)

Then I can make the following query:

SELECT T2.*

FROM JoinOne T1

INNER JOIN JoinOne T2 ON

T1.PKOne = T2.PKOne

AND T1.PKTwo = T2.PKTwo

AND T1.PKThree = T2.PKThree

WHERE T1.FKOne = <VALUE>

When looking at its execution plan, it’ll show two index seeks instead of my previous index scan. This way I have a small index as possible, but maintain seeks in my execution.

Of course there are some considerations one need to take with this pattern. Firstly – if the tables are “small”, then the scan in itself might be alright, or the overhead of keeping a complete index for the foreign key is neglible.

However the advantage of the pattern is that I can have smaller indexes on (very) large tables, which means less overhead when inserting/updating – but still have only index seeks in my selections execution plans.

It is a useful technique in my opinion – when used at the right times, which of course is on a case by case evaluation as always.

Rollback the transaction

Apr 212009

Last night I wrote a bit about an experience I had where the transaction blocked access.

So I decided to quickly write about how to rollback a transaction.

Suppose we have two tables:

Tables for Transaction example

I run the following SQL to illustrate wraping some INSERTs in a transaction.

BEGIN TRAN

BEGIN try

INSERT INTO TableOne VALUES ('aa')

INSERT INTO TableTwo VALUES (SCOPE_IDENTITY(), 'bb')

INSERT INTO TableTwo VALUES (-1, 'cc')

INSERT INTO TableOne VALUES ('aa')

END try

BEGIN catch

ROLLBACK TRAN

END catch

IF @@TRANCOUNT = 1

COMMIT TRAN

What happens here is the first insert will insert ‘aa’ into TableOne.

Then I use the identity value from TableOne and inserts into TableTwo in the subsequent insert. This will succeed. The third insert will try to insert a value -1 which will throw a foreign key restraint error.

Because I have wrapped in a transaction, and utilize the Try/Catch functionality (like in most other programming languages) – I can within rollback nicely within the “catch”, and make sure nothing gets committed to the database.

The end of the query tests the transaction counter (@@TRANCOUNT) and whether or not the transaction should be committed. This way I avoid having open transactions, and I avoid trying to close a transaction which have already been handled. Each BEGIN TRAN(saction) will increment @@TRANCOUNT, whereas ROLLBACK and COMMIT will decrement it.

Transaction and connection

Apr 202009

I had an experience at work today when I started playing around with transactions.

Basically – unless you play around with the isolation levels (a large topic on its own, for another time), if you open a transaction it’ll lock access to the table(s) you are modifying within said transaction.
Nothing strange about that.

However I experienced a transaction timing out when called from my data-access code layer. That meant the transaction was still “live” and blocked access to the table.
The lock could only be solved in one of 3 ways.
Either I had to manually kill the connection (for example via activity monitor) in SQL Server.
Or I had to wait until the application pool (aspnet process) got recycled (or I could kill it manually).
Or I had to wait until SQL Server killed the transaction (which in my test examples took around 5 minutes).

The lesson learned here is that one needs to watch the transactions and their running because you can end up with a long period of locking.

The way I tested this was I made an asp.net webform which called a Stored Procedure.
In this procedure I started a transaction, and then performed some actions which I knew would result in a time out when called from my webform. Even after the call from my webform had failed with a timeout, access to the affected table was still locked until I did as mentioned.

Transactions are a good thing, but you need to be aware of them. And remember to use rollback, cause otherwise transactions are pretty useless :)

Dynamic inequality checks

Mar 052009

One of the problems I often encounter in SQL when reading forums, solving problems for myself and so on, are equality and inequality checks and the need to make them dynamic.
A rather nifty (in my view) mathematical trick can be used combining the checks > with < and >= with <= in one query without the use of IF or CASE.

Note that logically when A is greater than B, then –A will be less than –B, and that is the core of the logic which is behind this.

Suppose I have a table (MyTable) containing numerical values in a Value1 field, which I need to check against an arbitrary number – I’ll use 25 in my example. Then if I write the following code:

DECLARE @modifier INT = 1

SELECT *
FROM MyTable
WHERE Value1 * @modifier > 25 * @modifier
ORDER BY Value1 DESC
(Note this is 2008 syntax, if using 2005 or ealier, just move the assignment of the modifier variable to another line)

In this case when the modifier is 1 the check will be Value1 > 25, but if I change the modifier value to -1, the logical check will now effectively be Value1 < 25. So just to recap – with a modifier of 1, I’d get all rows where Value1 is greater then 25, and with a modifier of -1 I would get the rows where Value1 is less then 25.

And if I needed to check for <= or >= all I’d have to do is apply a second check in an OR clause with a second modifier.

DECLARE @modifier INT = -1
DECLARE @modifier2 INT = 1

SELECT *
FROM MyTable
WHERE Value1 * @modifyer > 25 * @modifyer
OR Value1 * @modifier2 = 25
ORDER BY value1 DESC

Now this time around, if my @modifier2 is 1 the check now includes the equal check (so 25 will also be selected out), but if I change it to 0, the equal check is no longer present, and it’ll function as the earlier one without the second check.
So this means I can now check for > and < and >= and <= simply by changing @modifier between 1 and -1 and @modifier2 between 0 and 1. And by applying another modifier to value1 in the ORDER BY I could change between ASC and DESC on the fly as mentioned in my blog post about Dynamic ASC and DESC and Order by DateTime.

The main problem here is of course the difficulty in using possible indexes.
So if you have a large table it might hurt performance too much, and multiple stored procedures are the better approach.
But as always it is a case by case judgment call.  In my small testing for this piece it was about a 62/38 ratio meaning the mathematical dynamic approach took 62% and a specialized optimized query took 38%.
However I would need to maintain 4 different functions either via different queries, IF-constructs or CASE-constructs to get the same dynamic.
And of course the specialized and optimize will perform better, but at times the performance hit is negligible and at times a more dynamic approach is more useful.

Filter parameters in a Stored Procedure

Mar 022009

Something I see often is difficulty in using “filters” in a stored procedure used to select – for example – products based on various different restrictions. Often I see people using dynamically written SQL in the code-layer and then sent to the SQL Server and EXEC.

However a different method of doing this is to utilize the logic of the OR functionality in the Stored Procedure.

Suppose you have a table containing products. These products have – for example – a weight column, height, length and other such physical attributes. Now suppose you want to select all products from this table, but also give the option to filtering the select further – say below a specific weight and/or below a specific length and so on.
This is doable the following way:

DECLARE @Weight INT = NULL
DECLARE @Length INT = NULL
DECLARE @Width INT = NULL

SELECT *
FROM Product
WHERE (@Weight IS NULL OR (ProductWeight <= @Weight))
AND (@Length IS NULL OR (ProductLength <= @Length))
AND (@Width IS NULL OR (ProductWidth <= @Width))

Note this is 2008 syntax, if using 2000 or 2005 remember you can’t assign value to a declaration in the same line.

Also if using it in a Stored Procedure, the declarations are usually input parameters – but just default them to (for example) NULL.

When running this, with the null assignment, you’ll get all rows out, because all rows will fulfill the first part of the OR logic.

However if changing one, or more, of the variables to an actual value, the SQL will filter based on those values, because – as the variables no longer hold the value “null”, the second part of the OR statement comes into play.
So suppose you have the following values instead:

DECLARE @Weight INT = 10
DECLARE @Length INT = 15
DECLARE @Width INT = 20

The select will only select out those product rows which have a Weight less than or equal to 10, a Length equal to or less than 15 and likewise with Width and 20.
Omit one value and you’ll not filter for that one either, so

DECLARE @Weight INT = NULL
DECLARE @Length INT = 10
DECLARE @Width INT = NULL

Will select all products which have a length less than or equal to 10 – regardless of Weight and Width.

Of course, me using <= is basically irrelevant, as any logical operation can be performed within the second part of the OR statement.

An inherit disadvantage to this type of procedure over the more specialized ones are the difficulty in utilizing indexes. Due to the OR nature of the procedure, you’ll be unable to utilize the indexes optimal, so it is naturally a concern which you must consider when using such approach.

Using @@IDENTITY and SCOPE_IDENTITY()

Feb 162009

Often I see people just using @@IDENTITY when needing the latest used identity value inserted into a table.
However this is dangerous behavior seeing as a potential bug resides there.

As mentioned in the documentation for the functionalities @@IDENTITY returns the last used identity value within the given session, but the globally used identity. This means that if something else, within the same session (not to be mistaken with a HTTP session ;) ) uses identity insert, you risk getting the wrong value back.
SCOPE_IDENTITY() only returns within the same scope.
One exists called IDENT_CURRENT which can return the identity for any session for a given table. However, I’ll only focus on @@IDENTITY and SCOPE_IDENTITY() as they are most common.

An easy way to see the difference is to make two tables with an identity column and a dummy value field like this.

CREATE TABLE TableOne(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Value] [nvarchar](50) NULL
)
CREATE TABLE TableTwo(
[ID] [int] IDENTITY(10,1) NOT NULL,
[Value] [nvarchar](50) NULL
)

Note that the second table has a different initial identity value then TableOne. This will help with the illustration.
Now create a trigger on TableOne to run after Insert which inserts another row into TableTwo. Just some dummy data like this:

CREATE TRIGGER [dbo].[TriggerInsert]
ON [dbo].[TableOne]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO TableTwo VALUES (‘ss’)
END
GO

Now try running the following query to illustrate the difference between the two IDENTITY functionalities:
INSERT INTO TableOne VALUES (‘test’)
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

You’ll notice that the first SELECT @@IDENTITY returns a different identity value then just inserted into TableOne, and that is because it takes the last used identity value for the given session. SCOPE_IDENTITY() returns the expected value.

This is a potential problem if not aware of the differences and for example triggers are used. It can often provide in foreign key integrity problems and can be difficult to debug if multiple developers work on the same database.

Drop User: The database principal owns a schema in the database, and cannot be dropped

Feb 052009

Dropping a user which owns a schema will result in the error message “The database principal owns a schema in the database, and cannot be dropped”.

To solve this you can assign the schema back to its base principal with the ALTER AUTHORIZATION message, or it can be moved to another user/principal.
If for example user Test1 owns the db_owner schema and you want to drop Test1 but don’t have another user to move the schema to you can type:

ALTER AUTHORIZATION ON SCHEMA::db_owner TO db_owner
(or for example db_datawriter if that was the case and so on)

If you wanted to move the schema to another user the syntax would be:

ALTER AUTHORIZATION ON SCHEMA::<theSchema> TO userName

Select/Edit rows and Saving Changes is not permitted.

Feb 032009

So I’ve just started using MS SQL Server 2008 (yes, I’m that far behind) cause we received it on our Microsoft subscription now, so it was time to test and play :)

My first impression was that it was annoying they changed “Open” into both an Open Top 1000 rows and Edit Top 200 rows. Mostly because when I open a table, I like to open the entire table – just a preference of mine. Of course this is made to avoid having to wait a long time to open and edit a table if it contains a lot of rows, but in my work it was a constraint.

Luckily – this could be changed easily, by going into Tools -> SQL Server Object Explorer -> Commands and you can enter a 0 for all rows, if you want that – or another number to keep it at Open Top <n> rows.
Glad I didn’t have to mess with the registry for this.

The second problem I ran into was the error message: “Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created” and so on…… Luckily Microsoft thought of us again and this was also easily changeable.
So once more into the Tools menu: Tools -> Options -> Designer -> Prevent saving changes that require table re-creation” and uncheck that.

Older Entries Newer Entries