Thursday, February 02, 2006

SQL 2K: User Defined Types are such a pain!

The promise of User Defined Types is huge, but the headaches may not be worth the effort. With one of my clients we need to be able to easily change UDT definitions and get It Just Works (IJW). For example, we create a StringType_50 that can either be varchar(50) or nvarchar(50). Works fine for table definitions and for sproc params. But if you need to create a temp table in a sproc, you can't use the UDTs. Well, you can create the UDT in the tempdb. We have very restrictive production security issues, and I don't know what the issues are re: ability to create types in tempdb. I will probably mess around with that option later. For now, however, IJW just isn't there w/UDTs.