Status functions tell us something about the work environment. SQL Server has a number of these. You’re probably familiar with some of them; some of them you may not be. Here are a few that stand out from the rest in terms of rarity and usefulness:

GETANSINULL() allows you to determine default nullability for a database. Default nullability is controlled by the ANSI null default option of each database (set via sp_dboption), as well as the SET ANSI_NULL_DFLT_ON/SET ANSI_NULL_DFLT_OFF session-level commands. GETANSINULL() can optionally receive a single parameter—the database you’d like to check.

Here’s some code that uses GETANSINULL():

This code uses GETANSINULL() to determine the status of ANSI null default before changing the setting. It then creates a temporary table consisting of a single column whose NULLability is unspecified and inserts a NULL value into it. Afterwards, it restores the setting to its original value.

HOST_NAME(), GETDATE(), and USER_NAME() are also handy environmental status functions. Frequently, you’ll see them used to establish column defaults, though they can also be featured in SELECT lists, as this code illustrates:

Here, HOST_NAME() is used to return the current workstation name.

Another common use of these functions is as column default values. Here’s an example:

(Results abridged)

Note the use of INSERT...DEFAULT VALUES to add a row to the table using nothing but default values. Nullable columns without default values are inserted with NULL as their value; nonnullable columns without defaults cause an error to be generated.

Note that you could have used the ANSI SQL-92 CURRENT_TIMESTAMP and CURRENT_USER niladic functions in place of GETDATE() and USER_NAME(), respectively. USER and SESSION_USER are synonyms for CURRENT_USER. Interestingly, ANSI-92 niladic functions such as these may also be featured in SELECT lists, like so:

The SUSER_NAME() and SUSER_SNAME() functions come in handy if you prefer to default a column to the current user’s login name rather than his or her database user name (SYSTEM_USER is the ANSI SQL equivalent). If your app always logs in as ’sa’ and doesn’t use database user names, storing the current database user name in a table isn’t likely to be terribly useful. It will always be "dbo." Storing the user’s login name will permit you to track user activity without forcing you to set up separate database logins for each user.

SUSER_NAME() is included in the latest release of SQL Server for backward compatibility only—you should use SUSER_SNAME(), instead. SUSER_NAME() no longer maps directly to the SQL Server security model, so there’s a notable performance penalty for using it.

Property functions return information about objects in the database. Usually, this info is in the form of "meta-data"—data about data. There was a time when getting at even basic meta-data on SQL Server required spelunking through system tables. Fortunately, enough functions have been added that that’s no longer the case. Following are some of the more interesting ones.

COLUMNPROPERTY() returns useful info about table columns and stored procedure parameters. It takes three parameters—the object ID of the table or stored procedure (you can use OBJECT_ID() to get this), the name of the column or parameter, and a string expression indicating the exact info you’re after. You can refer to the Books Online for more information, but some of the more interesting uses of COLUMNPROPERTY() are illustrated below:

Note the USE tempdb immediately preceding the calls to COLUMNPROPERTY(). It’s necessary because the object in question resides in tempdb and COLUMNPROPERTY() can’t deal with cross-database references.

DATABASEPROPERTY() is similar to COLUMNPROPERTY() in that it returns property-level info about an object—in this case, a database. It takes two parameters—the name of the database and the property you’re after. Here are some examples that use DATABASEPROPERTY():

In the old days, you had to query master..sysdatabases and translate cryptic bit masks in order to get this information. Now, Transact-SQL makes the job much easier, completely insulating the developer from the underlying implementation details.

TYPEPROPERTY() returns property-level information about data types. It takes two parameters—the name of the data type you want to inspect and a string expression indicating the property in which you’re interested. The data type you supply can be either a system-supplied type or a user-defined data type. Here’s a query that uses TYPEPROPERTY():

IDENT_SEED() and IDENT_INCR() return the seed and increment settings for identity columns. Each function takes a single parameter—a string expression that specifies the name of the table you want to inspect (you specify a table rather than a column because each table is limited to one identity column).

IDENTITYCOL is a niladic function that returns the value of a table’s identity column. You can use it in SELECT statements to return an identity column’s value without referencing the column by name, like so:

This is handy for writing generic routines that copy data from table to table, for example. If you establish a convention of always keying your tables using an identity column, IDENTITYCOL provides a generic way of referencing each table’s primary key without having to know it in advance.

The IDENTITY() function allows you to create tables, using SELECT..INTO, that contain new identity columns. Previously, this required adding the identity column after the table was created using ALTER...TABLE. Here’s an example that features IDENTITY():

Though, technically speaking, it doesn’t have anything to do with identity columns, NEWID() is similar to IDENTITY() in that it generates unique identifiers. It returns a value of type uniqueidentifier and is most often used to supply a default value for a column. Here’s an example:

The uniqueidentifier data type corresponds to the Windows GUID type. It’s a unique value that is guaranteed to be unique across all networked computers in the world. You can use the ROWGUIDCOL keyword to designate a single uniqueidentifier column in each table as a global row identifier. Once you’ve done this, you can use ROWGUIDCOL analogously to IDENTITYCOL to return a table’s uniqueidentifier column without referencing it directly, like so:

INDEX_COL() returns the column name of a particular index key column. You can use it to iterate through a table’s indexes, displaying each set of key columns as you go. Here’s a code sample that illustrates how to use INDEX_COL():

(Results abridged)

Note that the INFORMATION_SCHEMA.KEY_COLUMN_USAGE system schema view provides the same information. Querying KEY_COLUMN_USAGE is the ANSI SQL-compliant method of accessing index column schema information. It also has the advantage of being immune to changes in the underlying system tables between releases of SQL Server.

The INDEXPROPERTY() function, like the COLUMNPROPERTY() and DATABASEPROPERTY() functions, returns schema-level information. Like COLUMNPROPERTY(), it takes three arguments—the ID of the index’s host table, the name of the index, and a string expression indicating what info you’d like. Here’s a query that uses INDEXPROPERTY():

(Results abridged)

Again, it’s preferable to use the KEY_COLUMN_USAGE system view to get this type of information rather than querying sysindexes directly.

STATS_DATE() returns the date the index statistics were last updated for a particular index. This is handy for determining when to issue UPDATE STATISTICS for the indexes in a database. You could easily write a query that checks each index’s last statistics date and issues UPDATE STATISTICS commands for those considered out of date. Here’s a query that does just that:

(Results abridged)

Of course, you could get the same basic functionality with the sp_updatestats system procedure—it updates the statistics of every index on every table in a database, but you might not want to update all of them—you might want to be a bit more selective. Updating statistics can take a long time on large tables, so you’ll want to run it only against tables it actually benefits and when system utilization is low. Also, keep in mind that SQL Server can maintain index statistics information for you automatically, alleviating much of the need for UPDATE STATISTICS. You can turn automatic statistic generation on for a given table via sp_autostats or for the entire database via sp_dboption.

ISDATE() and ISNUMERIC() return 1 or 0 based on whether a given expression evaluates to a date or numeric value, respectively. These can be handy for data scrubbing operations where you need to convert a character column to a date or numeric but need to check it first for invalid entries. Here’s a query that searches for bad dates in a character field:

This query returns 19990229 because 1999 wasn’t a leap year, making February 29 an invalid date.

DATALENGTH() returns the actual length of the data stored in a column rather than the length of the column itself. Though it can be used with any data type, it’s more commonly used with character, binary, and BLOB columns, since they can vary in length. The data length of a fixed data type (such as int) never varies, regardless of the value it contains. DATALENGTH() has more uses than you might think, most of them having to do with formatting result sets or data. Here’s some sample code from a previous chapter that makes novel use of DATALENGTH():

(Results abridged)

Using FORMATMESSAGE(), you can format strings using a printf()-like syntax. It takes a parameter specifying the ID of the message from the master..sysmessages table that you want to use, as well as a list of arguments to insert into the message. FORMATMESSAGE() works similarly to RAISERROR(), except that it doesn’t return an error. Instead, it returns the resulting message as a string, which you may then do with as you please. Unfortunately, FORMATMESSAGE() is limited to messages that exist in sysmessages—you can’t use it to format a plain character string. Here’s a technique for working around that:

This approach adds the string to sysmessages within a transaction for the express purpose of manipulating it with FORMATMESSAGE(). Once the string is formatted, it rolls the transaction back so that the message is removed from sysmessages. What you end up with is the ability to format a string without it having to be a permanent member of sysmessages first.

Admittedly, that’s a lot of code for a menial task like this. The logical thing to do here would be to generalize the technique by moving the code into a stored procedure. Unfortunately, this isn’t easily done because Transact-SQL doesn’t support variable stored procedure parameters—you can’t specify an unlimited number of variably typed parameters, which is what’s needed to exploit the capabilities of FORMATMESSAGE().

The system procedure xp_sscanf() provides a similar functionality and can handle a variable number of arguments but, unfortunately, supports only string parameters. It supports a variable number of parameters because it’s not a true stored procedure—it’s an extended procedure, and extended procedures are not written in Transact-SQL. They reside outside the server in a DLL and are usually written in C or C11. For the time being, it would probably be faster to cast nonstrings as strings and either call xp_sscanf() or use simple string concatenation to merge them with the message string.

PARSENAME() is handy for extracting the various parts of an object name. SQL Server object names have four parts:

[server.][database.][owner.]object

You can return any of these four parts using PARSENAME(), like so:

QUOTENAME() surrounds a string with either double quotation marks (""), single quotation marks (’’), or brackets ([]). It can be especially handy when building SQL to execute via EXEC(). Here’s a code sample from an earlier chapter that uses QUOTENAME() to create SQL code for execution by EXEC():

(Results abridged)

Once the duplicate rows are identified, deleting or moving them to another table is fairly straightforward. You couldn’t simply translate the main SELECT into a DELETE statement as that would delete both the duplicate and the original. It’s easy enough to do but would probably get you fired. Instead, you could use a cursor defined with the same basic SELECT as the code above to cruise through the table. You could save each pair of phone numbers off as you traversed the cursor and then iterate through the duplicates, deleting them as you go.

This would work, but there may be a better way. You may be able to use SQL Server’s ability to toss duplicate keys when it builds an index to scrub your data. Here’s an example:

The key here is CREATE INDEX’s IGNORE_DUP_KEYS option. Note that you can’t build a UNIQUE index over a table with duplicate keys regardless of the IGNORE_DUP_KEYS option—that’s why the code above creates the index, then inserts the data. As you can see, attempting to insert a duplicate key value (in this case, we defined the key as the two columns we want to scan for duplicates) generates a warning but otherwise allows the query batch to proceed.

Note that this technique works only with "simple" duplicates—ones that don’t involve delimiters and extraneous character removal (i.e., noncharacter types, for the most part). If your needs are as complex as the above technique that made use of nested REPLACE() functions, CREATE INDEX...WITH IGNORE_DUP_KEYS won’t get the job done since SQL Server can’t detect these more cleverly hidden duplicates.