SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

John Huang’s Blog

John Huang, Microsoft Certified Master in SQL Server, SQL Server MVP, is an independent SQL Server consultant in Vancouver BC, Canada. He started using SQL Server for his projects in year 1994. He has architected and implemented many SQL and BI applications serving different industrial areas. He loves talking about SQL Server and discussing SQL Server technologies with others. He blogs at http://www.sqlnotes.info.

Run T-SQL in Parallel

Writing CLR procedures to run T-SQL concurrently is not an extremely new idea. I have seen a lot of implementations and I have written and improved it many times by myself as well. After those coding exercises, I found few important things were not (or just partially) addressed.

  • Termination of…

Read more

4 comments, 1,446 reads

Posted in John Huang’s Blog on 3 December 2015

Get All Referenced Tables and Columns

I was always challenged when my customers asked me what tables and columns are referenced by a stored procedure which was written many years ago by the guy who left the company 5 years ago. When I Google the solution, I was always told that sys.sql_dependencies and sys.sql_expression_dependencies can tell.… Read more

0 comments, 1,405 reads

Posted in John Huang’s Blog on 19 November 2015

Print Long String

Quite often, I output information through PRINT command. It works well only when message is shorter than 8000 bytes. When the message is greater than 8000 bytes, extra-characters will be removed. the simplest way to overcome this is to make a loop and print 8000 characters at a time


Read more

0 comments, 1,673 reads

Posted in John Huang’s Blog on 19 November 2015

Changed SoundEx Disables Index While Upgrading to SQL 2012

Implementation of SoundEx function is changed in SQL Server 2012, described here. For an example, the result of SOUNDEX(‘Csomorova’) in SQL Server 2012 is C561 whereas the result of it from SQL Server 2008 is C256.

It does not seem very significant to our life. But it gives you… Read more

0 comments, 895 reads

Posted in John Huang’s Blog on 17 December 2013

Create Semaphore in Sybase

It seems like I am going to write another none SQL stuff again. No. This is a real life scenario. I am writing an ETL for my customer to load data from a Sybase server running on Unix system. The database I am accessing is a standby log-shipped database which… Read more

0 comments, 1,111 reads

Posted in John Huang’s Blog on 7 November 2013

Len() and DataLength()

Both system functions, Len() and DataLength(), in SQL Server are used to measure the length of the data. The main difference of those 2 is that Len() gets the string length of the data in which DataLength measures the storage length of the data.

Len always converts the input to… Read more

1 comments, 1,236 reads

Posted in John Huang’s Blog on 16 May 2013

Protect Yourself from Using DBCC WritePage

I blogged DBCC WritePage a year ago here http://www.sqlnotes.info/2011/11/23/dbcc-writepage/. It’s an extremely dangerous command especially with the last parameter “directORbufferpool” of this command turned on. I also showed you how to fix the page checksum here http://www.sqlnotes.info/2013/05/02/fix-page-checksum/. To protect yourself, please ensure you are operating on the database… Read more

0 comments, 953 reads

Posted in John Huang’s Blog on 14 May 2013

Bring Offline File Online — Modify SQL Server Metadata

In my last post, I demonstrated how to mount a database with missing NDF files. In the end, we still have issues removing tables created over missing files, the files are taken offline. Be cautious of taking files offline since there is no (official) way to bring it back… Read more

5 comments, 1,518 reads

Posted in John Huang’s Blog on 9 May 2013

Attach Database with Missing NDF File

I wonder if you’ve had the situation that I had before where you have to attach a database with one or few missing .ndf files. It seems pretty common since I’ve just seen the same scenario at one of my clients. The reasons behind it are quite similar – They… Read more

12 comments, 2,572 reads

Posted in John Huang’s Blog on 7 May 2013

Fix Page Checksum

A checksum value is tagged with every data page in SQL Server. This is used to detect issues on the storage system to ensure what has been read from the disk is what has been written to the disk. When the checksum is detected which is not the one it’s… Read more

7 comments, 1,421 reads

Posted in John Huang’s Blog on 2 May 2013

SQL Server +0 and -0

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… Read more

2 comments, 1,538 reads

Posted in John Huang’s Blog on 30 April 2013

SQL Saturday #198 Feb 16, 2013

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…

Read more

0 comments, 809 reads

Posted in John Huang’s Blog on 9 January 2013

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

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… Read more

3 comments, 1,741 reads

Posted in John Huang’s Blog on 26 December 2012

Inserted and Deleted are One Table?

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… Read more

5 comments, 2,329 reads

Posted in John Huang’s Blog on 17 October 2012

Update with UPDLOCK

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.

First, let’s prepare our test… Read more

10 comments, 1,296 reads

Posted in John Huang’s Blog on 10 October 2012

Have You Seen This Error?

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…

Read more

2 comments, 1,134 reads

Posted in John Huang’s Blog on 10 September 2012

Get Dependent Databases, Tables, Columns…

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.… Read more

4 comments, 4,131 reads

Posted in John Huang’s Blog on 29 June 2012

Locking Behavior – Foreign Keys

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… Read more

6 comments, 2,719 reads

Posted in John Huang’s Blog on 26 June 2012

Bug? Can’t Pass TVP to SQL Function Twice

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… Read more

0 comments, 1,069 reads

Posted in John Huang’s Blog on 20 June 2012

DBCC WritePage

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… Read more

19 comments, 2,429 reads

Posted in John Huang’s Blog on 23 November 2011