Sharpen Your Basic SQL Server Skills - 20 Nov 2007

This month I’ve been hearing from new DBAs who’ve asked about the difference between FILLFACTOR = 0 and FILLFACTOR = 100 when creating or rebuilding an index and the difference between ISNULL and NULLIF (and how they each compare with CASE). You also want to know how % used as a wildcard differs from % used as an operator. I’ll answer these top questions on the basics and give you some sample scripts. If you can’t wait a month to catch up on SQL Server tips and information, check out my blog at www.sqlmag.com/go/SQLskills.

Q: What’s the difference between using FILLFACTOR = 0 and FILLFACTOR = 100 when creating or rebuilding an index?

A: FILLFACTOR = 0 and FILLFACTOR = 100 are the same in all respects. FILLFACTOR, a percentage measurement, determines how much data is filled on the leaf level of each index page when an index is created or rebuilt. The minimum value for FILLFACTOR is 0, and the maximum value is 100. The default is FILLFACTOR = 0. Once modified from 0 to any other value, the FILLFACTOR value can’t be set again. Setting FILLFACTOR to 100 has the same effect as setting it to the default of 0, because both FILLFACTOR = 0 and FILLFACTOR = 100 tell SQL Server to completely fill the leaf page. No space is unused. Using a value between 1 and 99 always leaves a percentage of the leaf page space unused. For example, FILLFACTOR = 80 leaves 20 percent of the space unused.

Q: What’s the difference between NULLIF and ISNULL?

A: The difference between NULLIF and ISNULL is that NULLIF returns NULL, whereas ISNULL replaces NULL. NULLIF checks for certain parameters and returns NULL if the parameters are equal; otherwise it returns the first parameter. Listing 1 contains a NULLIF script that returns NULL for TestEqualNULLIF and 0 for TestNotEqualNULLIF. ISNULL checks for the value of a parameter and replaces NULL with another value if the parameter’s value is NULL. Listing 2 contains an ISNULL script that returns 1 for TestNULLValue and 10 for TestNotNULLValue.

Q: What’s the typical usage of NULLIF and ISNULL?

A: Use NULLIF to compare two values, and use ISNULL to compare a value with NULL. You can use CASE instead of NULLIF or ISNULL. However, using NULLIF or ISNULL reduces the amount of T-SQL script you need to write. Only a negligible performance difference exists between using NULLIF or ISNULL, or using CASE. Listing 3 demonstrates NULLIF and ISNULL, comparing each with an equivalent CASE statement. (Notice how much more code CASE requires.) Run the script in Listing 3 two times with two different values. The first time you run the script, set @expression2 to 0, and the second time set @expression2 to 1.

Q: What’s the difference between % used as an operator and % used as a wildcard?

A: When % is used as an operator, the modulo returns the remainder of a division operation. Using % as a wildcard character matches any length of the string or number in a WHERE clause. Listing 4 contains a query that uses % as modulo and as wildcard.