SQL alias data types

Question

Hi.

I am starting a new project, and I am considering using alias data types in my SQL Server 2005 database, for common table columns. For e.g. I would define an alias data type to hold the name of an object, as follows:

CREATE TYPE adt_Name FROM varchar(100) not null

Then use it for the definition of table columns, ensuring all my columns share the exact same definition (length, nullability, precision, etc).

1. What are the advantages of using alias data types?
2. What are the disadvantages of using alias data types?
3. What naming conventions would you recommend? I was thinking adt_Xxx (adt = alias data type).
4. Why does SQL Server 2005 Management Studio not allow me to work with alias data types from the GUI. I can only use them through SQL Scripts. The GUI doesn't list them in any of the drop-down boxes - its frustrating.
5. How will the use of alias data types affect my Linq to SQL model?

Answer 1

Types in general are good, but our project uses it limited, so I can answer #2 The problem is"altering". We developed type TUrl as varchar(255), but after some time we have tried to change to varchar(800). For working database it is impossible.

I don't know any other disadvantages of using alias data types.

Answer 2

Advantages:

• Reusable and Sharable, the ADT is reusable within the database it's created in, if you want it across all your DBs then create it in the model database

• Enforcement, the ADT will enforce the characteristics, length and nullability of it's base type, and can be used to enforce development standards

• Disallows implicit conversions, ADTs cannot be CAST or CONVERTed

• Simplicity, as with other languages, ADTs lend simplicity to development and maintenance

• Data Hiding

Disadvantages:

• Unmodifiable, ADTs cannot be directly modified, you must DROP and reCREATE them. Note that you can still ALTER the tables they're in to another base type or ADT, then DROP and reCREATE them, then ALTER the tables back (or just CREATE a new one to alter them to).

• No Tools, ADTs must be created with a direct query using the CREATE statement (sp_addtype is deprecated and shouldn't be used).

• Table variables are not supported

Naming Conventions:

• 'adt_' looks like it will work just fine

Linq to SQL:

• Treat these types as either the base type in your querys, or create the corresponding type to use

Answer 3

I would advise against it. I support some customers who have products that use them and they are a constant PITA (pain-in-the-ankle).

In particular, you cannot use them in #temp tables unless you also define them in TempDB. And as TempDB gets reset every time you restart SQL Server, that means that you also have to redefine them every time that you restart SQL Server. But that means a startup procedure which must be in Master and has certain privs. And since the Alias definitions (really UDT's in Ms terminology) might change, that means that the DBA has to give someone else the rights to edit that procedure which could be a security issue.

Oh, and lest I forget, if you have to upgrade, migrate or reinstall your server, you'll need an external copy of that proc to re-add to Master and you'll need to remember to do that.

And then there's the limitations: in my experience developers want to use these "Alias's" because they think that it will give them the flexibility to change the definition later on if they need to. It won't. Persistent data is NOT like persistent code, it doesn't have that kind of flexibility and SQL Server isn't going to help you much at all here. After you try to do this once, you'll quickly conclude that you never should have used the darn things in the first place.

Answer 4

IMO using built in types is a huge pain in the neck. Others have already given enough reasons why. I am using C-style macros. For example, in my SQL 2000 code I had the following line in a separate file macros.h

#define WIDEST_CHAR VARCHAR(8000)

I would use it as follows:

#include "macros.h"

(snip)

CREATE TABLE dbo.Comments(CommentID INT NOT NULL,
Comment WIDEST_CHAR,
...

and the macro WIDEST_CHAR will be replaced with VARCHAR(8000) by the preprocessor. When I migrated my system to 2005, I simply replaced the macro definition with

#define WIDEST_CHAR VARCHAR(MAX)

and I was all set.