A Readable Data Type from System Tables Columns

I’ve written a new article on Indexed Views that has been www.DatabaseJournal.com As part of that article I included a UDF, udf_View_ColumnsIndexableTAB, that shows which columns in a view are eligible for indexing. It’ll be next week’s UDF of the week. But before I show you that UDF, I have to show you this week’s UDF.

This week’s UDF is udf_SQL_DataTypeString. It can be used to construct a data type that a human DBA or programmer is familiar with. Types such as “int” or “varchar(283)”. The UDF is necessary because SQL Server doesn’t store data types in the form that we’d like to see.

Instead of storing a data type as a single column with values such as “numeric (18,3)”, the INFORMATION_SCHEMA views and systems tables store at the four columns shown in this table:

Column Name Data Type Descriptions
DATA_TYPE nvarchar(128) Base data type: int, char
CHARACTER_MAXIMUM_LENGTH smallint Max length in chars
NUMERIC_PRECISION tinyint Significant digits stored
NUMERIC_SCALE tinyint Digits to the right of the decimal

There are other columns with information about the data such as DATETIME_PRECISION and CHARACTER_SET_NAME but they’re not needed to reconstruct the data type that we’re familiar with.

Here’s the create function script:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GOCREATE FUNCTION dbo.udf_SQL_DataTypeString (@BaseDataType nvarchar(128) — base name like int, numeric
, @Character_Maximum_Length int
, @Numeric_Precision int
, @Numeric_Scale int
) RETURNS nvarchar(24) — Data type name like ‘numeric (15, 3)’
WITH SCHEMABINDING
/*
* Returns a data type with full length and precision information
* based on fields originally queried from
* INFORMATION_SCHEMA.ROUTINES or from SQL_VARIANT_PROPERTIES.
* This function is intended to help when reporting on functions
* and about data.
*
* Example:
SELECT ROUTINE_NAME as [Function]
, dbo.udf_SQL_DataTypeString (Data_Type
, Character_Maximum_Length, Numeric_Precision, Numeric_Scale)
as [Data Type] FROM information_schema.routines
WHERE ROUTINE_TYPE=’FUNCTION’
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain
* other unrelated database objects. You may not publish this
* UDF either in print or electronically.
* Published in T-SQL UDF of the Week Newsletter Vol 1 #18
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGINRETURN LTRIM(RTRIM(@BaseDataType))
+ CASE WHEN @BaseDataType in (‘char’, ‘varchar’
, ‘nvarchar’, ‘nchar’)
THEN ‘(‘
+ CONVERT (varchar(4)
, @Character_Maximum_Length)
+ ‘)’
WHEN @BaseDataType in (‘numeric’, ‘decimal’)
THEN ‘(‘
+ Convert(varchar(4), @Numeric_Precision)
+ ‘ ‘ + convert(varchar(4), @Numeric_scale)
+ ‘)’
ELSE ” — empty string
END
END

GRANT EXEC, REFERENCES ON dbo.udf_SQL_DataTypeString to [PUBLIC]
GO

There are several differences between this week’s CREATE FUNCTION script and those in past newsletters:

– SET statements at the beginning of scripts
– WITH SCHEMABINDING clause
– GRANT of the REFERENCES permission

All three of these are required if a computed column that invokes a UDF is to be used by an indexed view. Now I’m the first to admit that the circumstances for using indexed views are limited. But these three factors improve the UDF and I’m going to be more careful about including them when they’re appropriate.

Over the next few weeks you’ll see a lot in this space about factors that influence indexed views. And by-the-way I’ll send out a notice when the article becomes available.

This week lets discuss the SET statements at the top of the script:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

Indexed views require that a group of 7 session options be set to consistent values (The complete table is in the article). Two of them are QUOTED_IDENTIFIER and ANSI_NULLS, both of which must be ON.

These parameters can’t be set in a UDF at run time. The UDF executes with the setting what was in effect at parse time. That is, when the CREATE FUNCTION or ALTER FUNCTION script was last run.

If you ever want to use indexed views, the prudent thing to do is to set these two session options ON at the start of every UDF script.

QUOTED_IDENTIFIER is pretty common and won’t require much adjustment on your part. ANSI_NULLS is another story. It affects the way that comparisons to NULL are performed. If your code hasn’t been tested with this setting ON, don’t switch until you can retest.

To make an interesting result I created the UDF in the Northwind sample database. Lets try our UDF of the week and see what it shows:

SELECT DISTINCT TOP 15
COLUMN_NAME as [Column]
, dbo.udf_SQL_DataTypeString (Data_Type
, Character_Maximum_Length, Numeric_Precision
, Numeric_Scale) AS [Data Type]
FROM INFORMATION_SCHEMA.COLUMNS
GO

Results:

Column Data Type
OrderID int
CustomerID nchar(5)
ReorderLevel smallint
Discontinued bit
CategoryName nvarchar(15)
ProductID int
ProductName nvarchar(40)
EmployeeID int
OrderDate datetime
RequiredDate datetime
ShippedDate datetime
ShipVia int
Freight money
ShipName nvarchar(40)
ShipAddress nvarchar(60)

udf_SQL_DataTypeString is similar to the UDF from issue #12, udf_SQL_VariantToDatatypeName. In fact that function, which works on sql_variant data, could have used this week’s UDF instead of including the code inline.

I’ll let you know when the article is available. The existence of Indexed views in SQL Server 2000 influenced many of the choices the SQL Server development team made for UDFs. There will be more about these factors in the upcoming weeks.

Please don’t forget to share this issue with anyone interested in SQL Server. Thanks.