Reverse Engineering Alias Data Yypes in SQL Server 2000

  • Comments posted here are about the content posted at

  • Are ADT's compatible with the current release of Visual Studio .NET?

    A couple years ago I had done some C# programming with .NET 2003 and a SS2000 db that used UDT's but .NET did not recognize the UDT's. That actually made programming harder, since you had to go look up the definition for the UDT and use a native .NET data type.

    Does Visual Studio .NET 2005 allow you to use ADT's, and how is that done?

  • Other drawbacks of the ADT:

    * When you need to "change" the type the effort is more than with native

    * When dealing with temporary tables you have to have them in model by the time you restart SQL Server or use a startup procedure to add them to tempdb. This is harder than native datatypes

    * replication has issues with some of these types

    * Because it hides implemetation details it forces you to find out the definition.

    * Like posted above not many providers deal with this successfully

    * Linked servers can be trashed because of this

    Because of all the above (and possibly more that I may be missing) I would vote for the use of ADT as a BAD parctice!


    * Noel

  • I had to deal with ADT's in a previous nightmare job. Oh what a total pain it was! Just use the system types. The biggest pain was having to script removal of the use of the types by every table that used one when needing to change the definition of the type. There were some benefits to using the ADTs, but the drawbacks were much larger.

  • I prefer to use C++ approach - I define macros in a *.h file and have them replaced by actual definition by the preprocessor. For instance, I used to have BIGGEST_VARCHAR defined as VARCHAR(8000). WHen I migrated to 2005, I just replaced that with VARCHAR(MAX) in only one place in my ConstantDefinitions.h file. Next time I generated my deployment script, it had VARCHAR(MAX) wherever I had BIGGEST_VARCHAR in my source.

  • If the ADT is also used in stored procedures, is there a way to drop the ADT without changing the stored procedure first?

  • Hi pcwc66,

    you have a very intresting question.

    Could you please provide a sample of you stored procedure,

    what do you want to accomplish and I will be glad to answer.


  • Hi Yakov,

    The idea to replace the old ADT with a new ADT is good.  However, for maintenance purpose, I'm thinking whether I can replace the old ADT with a new ADT and then replace the new ADT with another ADT which has the same name as the old ADT but has the definition of the new ADT.

    Replacing ADT for columns can follow the article and write a not too complicated script to go thru all tables in a database.  Writing a script to replace ADT in stored procedures may be complicated.  If there is a way to suppress SQL Server to check ADT existence in other SQL Server objects, then othe SQL Server objects may not need to be replaced.



  • Hi pcwc66,

    you can replace the old ADT with a new ADT and then replace the new ADT with another ADT which has the same name as the old ADT but has the definition of the new ADT. The procedure provided in the article allows you to do it.

    There is no way to suppress SQL Server from checking ADT existence in other SQL Server objects.

    Please read more on ADT in coming parts of this article.


Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply