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.