SQL Server T-SQL User-Defined Function of the Week

A Readable Data Type from System Tables Columns

Volume 1 Number 18   March 18, 2003

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

I've written a new article on Indexed Views and it will be 
published later this week at the web site 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:
/-------- Copy from below this line ----------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE    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 BEGIN

RETURN 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
\-------- Stop copying above this line -------------------------/

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:
/-------- Copy from below this line ----------------------------\
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
\-------- Stop copying above this line -------------------------/
(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.

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+