DBA

OdbcPrec() and OdbcScale() functions

I have discovered these two functions accidentally when searching for something else. I was trying to find more information about them but there is nothing in the official documentation and these function can’t be found in any metadata view like sys.all_object etc. It looks like they exist in SQL Server from 2000 edition and they are used internally in special SQL Server objects like sys.spt_columns_odbc_view.

You can review how these two functions behave on any database and compare their results with sys.columns view max_length, precision, and scale columns:

SELECT DISTINCT *
FROM (
	SELECT 
		t.[name], c.[max_length], c.[precision], 
		OdbcPrec(t.system_type_id, c.max_length, c.precision) odbc_prec,
		c.[scale],
		OdbcScale(t.system_type_id, c.[scale]) odbc_scale
	FROM sys.columns c
	    INNER JOIN sys. t ON [t].[system_type_id] = [c].[system_type_id] AND [t].[user_type_id] = [c].[user_type_id]
	) a    
WHERE ISNULL([max_length], 0) <> ISNULL([odbc_prec], 0) OR
      ISNULL([scale], 0) <> ISNULL(odbc_scale, 0)
GO

Based on this result I will say these two functions are just converting specific SQL Server-related data types length, precision, and scale to ODBC standard specification.

Leave a Reply

Your email address will not be published.