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.

cheapest place to buy accutane online rating
5-5 stars based on 139 reviews
Various small and large RCTshave been and are being conducted, but results fromsmall RCTs have shown that, in PDD, memantine waswell tolerated; results are limited due to a sample size,however (Aarsland et al., 2009)

Various small and large RCTshave been and are being conducted, but results fromsmall RCTs have shown that, in PDD, memantine waswell tolerated; results are limited due to a sample size,however (Aarsland et al., 2009). Six months after ACT there was a sustained remissionof the disease or normal B-cells. The efficacy is lower (96–98%) compared to 98–99.9% with combined pill.Pregnancy should be suspected if amenorrhoeaof more than 2 months occurs. The optimal dailyintake is considered to be between 0.8 and 1.2 grams/kgof body weight

The optimal dailyintake is considered to be between 0.8 and 1.2 grams/kgof body weight. Delayed wound healing after TAA occurs in up to 15% of cases [2 cheapest place to buy accutane online 20].

Scandinavian Journal of Plastic and Reconstructive Surgery & Hand Surgery 40, 267–274. Recentstudies have even demonstrated that a running stitch using a barbed suture is equivalent toa running stitch using a delayed absorbable suture.18 Whether or not a second imbricatinglayer is used depends on the patient’s history and status, as well as surgeon preference

Recentstudies have even demonstrated that a running stitch using a barbed suture is equivalent toa running stitch using a delayed absorbable suture.18 Whether or not a second imbricatinglayer is used depends on the patient’s history and status, as well as surgeon preference.

Phosphodiesterase-5inhibitors have also been studied in patients with WHO group 3PH due to lung disease. Dipple KM cheapest place to buy accutane online McCabe ER (2000) Phenotypes of patients with “simple” Mendelian disordersare complex traits: thresholds, modi?ers, and systems dynamics. Donot give inaccurate information or purposely lie. (2001a) Risk fac-tors associated with age-related nuclear and cortical cataract : acase-control study in the Age-Related Eye Disease Study. Over 1,150 active compounds from the FPassay were further subjected to a cytotoxicity cell-based assay using retinoblas-toma cells with MDMX amplication. CMS has clearly stated its policy that student serv-ices under Part B are not billable cheapest place to buy accutane online and that only services provided to Medicare bene?ci-aries by the PT may be billed. Despite this cheapest place to buy accutane online observationaldata indicate preterm infants spend only half ofthe time within the intended range of SpO 2 ,nearly a third of the time with SpO 2 above, andone ?fth of the time with SpO 2 below the intendedrange (Hagadorn et al. The Anger Trap: Free Yourself from the Frustrations that SabotageYour Life. Diphtheria: For acute stage as well as forcarriers—7 day treatment is recommended.Some prefer it over penicillin. Doesthat sound familiar? If the answer is yes cheapest place to buy accutane online you may be a woman between theages of forty and fifty-five experiencing one of the common symptoms ofmenopause. These two dimen-sions of life constitute what it means to be responsible—literally to respondto the world. aureus causes one-third of septic arthritis of thesymphysis pubis

aureus causes one-third of septic arthritis of thesymphysis pubis. Lithium isadministered three times daily or as a daily slow-releasepreparation. Pentobarbital is most commonly used cheapest place to buy accutane online with a loading dose of 5 to 20 mg/kg as a bolus,followed by 1 to 4 mg/kg per hr infusion. Sotaloland dofetilide should be initiated in the hospital due to the riskof developing dose-dependent QT prolongation and torsadesde pointes. Lethality and decreased longevityappear to be due to the development of chemical pneumo-nitis. However cheapest place to buy accutane online they do notcontain the M-6-P signals and must be targeted to lysosomesby a different mechanism. Dusts resultfrom industrial processes such as sandblasting and grinding,and are identical to the compounds from which they origi-nated. Clinical evidence suggests thatcarbon tet may cause ocular toxicity cheapest place to buy accutane online although demonstra-tion of retinal or optic nerve injury is lacking. (1999) Surveil-lance for sensory impairment, activity limitation, and health-related quality of life among older adults

(1999) Surveil-lance for sensory impairment, activity limitation, and health-related quality of life among older adults.

An increase in mCV is asso-ciated with abnormally large RBCs typically seen with liverdisease, antimetabolite therapy, alcoholism, perniciousanemia, and folic acid deficiency.

Resistant VT and recurrent VF arethe most important indications. The celiacand superior mesenteric arteries are the next branches that arise anteriorly in closeproximity to each other and supply a large portion of the gastrointestinal tract. There is a region ofintense basophilia adjacent tothe nucleus. Kostis WJ, Cheng JQ, Dobrzynski JM, Cabrera J, KostisJB. The light traverses biologic tissue and its absorption at specific wavelengthscorrelates with the presence of biologic chromophores. Because the bullae are tense andintact, the skin is better protected.