Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reverse Engineering Alias Data Yypes in SQL Server 2000


Reverse Engineering Alias Data Yypes in SQL Server 2000

Author
Message
Yakov Shlafman
Yakov Shlafman
SSC-Enthusiastic
SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)SSC-Enthusiastic (119 reputation)

Group: General Forum Members
Points: 119 Visits: 516
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/yShlafman/3038.asp
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1770 Visits: 3041

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?


noeld
noeld
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7050 Visits: 2048
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!

Cheers!


* Noel
Vic Kirkpatrick-173212
Vic Kirkpatrick-173212
Mr or Mrs. 500
Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)Mr or Mrs. 500 (513 reputation)

Group: General Forum Members
Points: 513 Visits: 76
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.
Alexander Kuznetsov
Alexander Kuznetsov
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 824
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.
pcwc66
pcwc66
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 174

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





yakov shlafman-228008
yakov shlafman-228008
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 281

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.

Regards


pcwc66
pcwc66
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 174

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.





yakov shlafman-228008
yakov shlafman-228008
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 281

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.

Regards


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search