Executive Summary:
New DBAs get answers to basic Structured Query Language (SQL) questions. This month’s column explores the difference between FILLFACTOR = 0 and FILLFACTOR = 100; ISNULL and NULLIF; a clustered index and a nonclustered index; % used as a wildcard and % used as an operator; and TRUNCATE and DELETE.
|
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.
End of Article
