Project Description
Solution for validating South African identity numbers.

Provides SQL Server CLR bindings which allow identity numbers to be efficiently validated within T-SQL

This project makes RSA ID number validation possible by exposing a C# table valued function (TVF) to SQL server. The function takes an identity number as its input, and returns a single record with the following columns:
IsValid - bit: Whether the input identity number is valid.
IsCitizen - bit: Whether the number belongs to a South African citizen.
Gender - char: The gender of the individual - ‘M’ for male, ‘F’ for female.
DateOfBirth - date: The date of birth of the individual.

Why a table valued function when, given the 4 output fields above, it seems smarter to implement a user defined type (UDT)? Because UDTs are problematic - T-SQL Dependencies on UDTs make it very difficult to upgrade the CLR assembly if you use a UDT for a table column... you cannot upgrade the UDT’s assembly without dropping the column. Also, properties exposed by UDTs have to be aliased in SELECT statements – i.e. the name of a UDT property does not translate into a column name/alias when used in a query.

In contrast, implementing a table valued function gives us:
  • Upgradability: You can unload and upgrade a TVF’s assembly any time, since you won’t unwittingly create a schema-bound dependency on a TVF.
  • Aesthetics: Columns returned by a TVF default to having sensible names.
  • Performance: All columns for a row returned by a TVF are returned in single function call, whereas UDTs require a CLR call per property.

The solution used in this project highlights some best practices when using CLR integration with SQL server, namely:
  • The assembly is marked as safe. (no naughty pointers, no external calls, no unverifiable code blocks)
  • The assembly is stateless. SQL unloads application domains when it detects resource constraints, and assemblies must deal with this gracefully.
  • Exceptions are never thrown. This means that queries never crash, no matter what data you feed into the TVF.

Last edited Nov 2, 2012 at 10:14 AM by maranite, version 4