June 26, 2006 at 9:28 am
I've got a table with 22,000 rows. The rows consist of 4 fields, varchar(32), varchar(254), char(1), and varchar(8).
The clustered primary key is the varchar(32) field, however, this is actually an integer stored as a varchar.
A database maintenance plan is run each Sunday. Part of that plan is a reindex. Each Monday, the users report inconsistant results from a basic search. Reindexing the offending table has corrected the issues until today.
The specific query uses the NOLOCK clause. If I run the query without the clause, the query returns the rows in the expected (correct) order. If the NOLOCK clause is used, the inconsistant results are returned.
Since NOLOCK allows dirty reads, I attributed this to uncomitted transactions. However, this same issuse appears in the testing database.
If I reindex a second or third time, the same situation occurs but when using the NOLOCK clause the results returned each time are different.
Additionally, the databases all reside on an active/passive cluster.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 26, 2006 at 9:44 am
>>If I run the query without the clause, the query returns the rows in the expected (correct) order.
So, the only problem is the ORDERING of the resultset ? Are you relying on the clustered index to apply the ordering ? A query that needs to return results in a specific order *must* use ORDER BY in the SQL statement and cannot rely on the clustered idnex.
June 26, 2006 at 10:02 am
Thank You for your response.
Actually, I know the answers to these problems, but I should have explained in the initial post that I need as much "ammunition" as possible because this application was written by a third party vendor who really does not understand SQL Server, or, for the matter, relational databases in general.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 26, 2006 at 3:29 pm
I ran into a similar problem the other day. There was a varchar field used to store an integer. The program was getting the next int value by selecting the max(id) from the table. It started to error because the max varchar value in the table was 999 and the max integer value was 1000. The program wanted to select the higher number but for varchar 999 is the higher value. This is one good reason to not use varchars when storing numbers.
Hope this helps
Tom
June 27, 2006 at 4:06 am
You can get alphabetic and numeric order to coincide for strings containing positive integer literals by left-padding your strings with zeros. Better, use an indexed calculated column CASTing the values to int/bigint.
But the values should almost certainly be stored as numbers. For your ammunition: the wider varchar column is much less efficient as a join column, and indexes will be less efficient. The fact that the index is clustered is even worse, since every other index uses this to access the data pages, so performance will be degraded for all indexes.
The reliance on a clustered index betrays a misunderstanding of the distinction between physical implementation and logical query design, while use of nolock other than in a purely read-only environment is rarely defensible. There may be a misunderstanding of the different implementations of the sort operation for strings and integers, as suggested, but it's not possible to be sure on the current information.
But there is certainly a failure to use the correct datatype. There is no reason to use a varchar to store a number (if it really is a number, which I assume it is since you want to order it numerically). You risk invalid values getting into the db, and force constant CASTing of the value in order to actually use it. Requiring a CAST on an indexed column in a join or where clause also prevents SQL Server using an index, so degrades performance still more.
Make sure you use the opportunity to throw in any other changes you need while you have them on the back foot.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 27, 2006 at 2:18 pm
Storing a integer in a varchar is not a smart approach for sure.
But im most concerned with the nolock and the (in)correct order.
I know the diference between lexiografy and numeric order (and some collations issues as well).
There are (a lot of update indexes) uncomitted transactions? Why? Is the non commitement the correct behavior?
Nolock use is OK for a wide range of applications (if u use that dirt data do feed just a report and a not significant percentual error cannot hurt u)
Jean
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply