I found this interesting. If any of the following field are NULL, then the concatenated value for DirName is also NULL. You would think that if the less commonly populated fields of Prefix of Suffix were null, dirfname and dirlname would still be returned but this isn’t the case.
select prefix + ' ' + dirfname + ' ' + dirlname + ' ' + suffix As DirName from individual
To solve this issue, use the IsNull function.
select isnull(prefix, '') + ' ' + dirfname + ' ' + dirlname + ' ' + isnull(suffix, '') As DirName from individual