SQL Server Central – Page 2 – John Huang's Blog

SQL Server +0 and -0

April 30, 2013 by John H

This is a very interesting error happened in one of my clients’ databases. Their database has been running for at least 12 years without a single problem. They run DBCC CheckDB regularly to ensure the database is in healthy state until few days ago their DBA ran DBCC CHECKTABLE with data_purity and get

Msg 2570, Level 16, State 3, Line 1
Page (1:118), slot 0 in object ID 245575913, index ID 0, partition ID 72057594039042048, alloc unit ID 72057594043432960 (type "In-row data"). Column "Value" value is out of range for data type "numeric". Update column to a legal value.

Finally, they found this article, , http://support.microsoft.com/kb/923247. — Many things can cause this kind of error. In this blog post, I am going to demo how I repeat and fix this error — for fun.

SQL Saturday #198 Feb 16, 2013

January 9, 2013 by John H

SQL Saturday 198, http://www.sqlsaturday.com/198/ ; is coming to Vancouver Feb 16 this year. As of now, 56 sessions are collected from 30 speakers, http://www.sqlsaturday.com/198/schedule.aspx?sat=198. Curiously last night, I pulled all speakers from the list and concluded that it will be a great event this time. 19 SQL MVPs 7 author/co-authors 4 MCMs 4 PASS User … Read more

NULL Values Impact the Performance of MAX() and MIN()

January 3, 2013December 26, 2012 by John H

We know that we need to take special attention about NULL values while writing queries because they may lead us into writing incorrect query logics. NULLs may also affect the performance of aggregation function MAX() and MIN(). I have submitted this issue to Microsoft Connect. There are number of ways to get around it. I think the improvement can also be done at query engine level. Hope this can be fixed in next or future version of SQL Server. But for now, we need to know it and know how to get around it.

Inserted and Deleted are One Table?

October 17, 2012 by John H

Inserted and deleted tables are 2 pseudo tables preseting the before and after images of modified rows. They can only be accessed from within triggers. Those two have been around for quite long time but it’s hard to find any articles to tell how they are populated. Some rumors said they are retrieved from transaction logs in SQL Server 2000. Recently, I was asked to evaluate the performance impact of using trigger for my customer’s project. I did some investigation and got some interesting results.

Update with UPDLOCK

October 10, 2012 by John H

Do you think it’s a good idea to add UPDLOCK hint to the table which will be modified in an update query? Many people will say NO because it seems to be redundant. But the fact is that sometime it removes deadlocks in your application.

Have You Seen This Error?

September 10, 2012 by John H

location:     AppendOnlyVerStoreMgr.cpp:776
Expression:   0
SPID:         64
Process ID:   4912
Description:  Cannot locate version record and page is not allocated. Status = 3
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. 
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

This was the error a Sr. DBA at my client asked me. There error appeared after reading a table from decrypted database which has been encrypted by TDE on SQL Server 2008 R2.

Get Dependent Databases, Tables, Columns…

June 29, 2012 by John H

Quite often people need to write a new T-SQL application to replace an existing one for various reasons. For instance, Existing report is generated by a complex T-SQL procedure which pulls data from OLTP directly. The performance of the procedre is poor since everything is calculated while report is running. Company decided to pre-calculate and save the result in a denormalized table, and the report runs off that denormalized table. Obviously, the original code need to be re-written. Let’s say you are new to the company. Before starting your work, you may want to know what databases, tables, and columns are referenced by the procedure. It will not be hard if the procedure code block only has few lines of code. However, if the procedure includes few or tens thousand lines, accomplishing such work might takes days. The worth thing is you might miss some refereces and finally you might abandon your work result at the end due to the inaccuracy.

Locking Behavior – Foreign Keys

June 26, 2012 by John H

With Snapshot (read committed snapshot and snapshot isolation level) enabled, when data reading on a table takes place, if any records are being modified at the same time, the data will be read from the rows in version store rather than wait until data modification complete. Such behavior lets prgrammers think that reader process will NEVER ever be blocked by writer process. In most of the scenario, this is correct, however, when reader process is indirectly issued and running against a table, the isolation level for that reader process is always read committed lock isolation regardless the transacitoin isolation level of the parent statement.

Bug? Can’t Pass TVP to SQL Function Twice

June 20, 2012 by John H

I run into a bug recently with SQL Server 2012. I believe this exists in SQL Server 2008 as well. When a TVP is passed to a SQL function/procedure/script through an CLR function, the second execution will cause error which will be written to SQL Server log — Process x generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. It has been filed to Microsoft Connect. If you think it’s important, please visit this bug on Microsoft Connect and vote it as important. Please download the code from here.

DBCC WritePage

January 28, 2013November 23, 2011 by John H

DBCC WritePage is a dangerous undocumented DBCC command. It’s useful in your sandbox to change data, crash objects, and do some fun things you may think of. But never ever use it in your productions. Using DBCC WritePage command is at your own risk. . I will give you demos on how to use dbcc writepage to perform simple insert, delete, and update on a table.

dbcc writepage ({'dbname' | dbid}, fileid, pageid, offset, length, data [, directORbufferpool])

{‘dbname’ | dbid}: Database name or database id
fileid: File id in sys.database_files
pageid: Page number in the file
offset: Starting position of the data you want to change
length: number of bytes to be written to the page
data: data to be written. It’s binary, for example 0x13432
directORbufferpool: 2 possible values, 0 and 1. When it’s 0, which is also the default value, the data modification is done at buffer level, related data will be calculated automatically such as page hash. When it’s 1, the modification is written to disk directly, related values, such as page hash, will not be recalculated automatically. Unless you want to test your math level…