Search results matching tags 'Best Practices', 'habits', and 'alias types'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Best+Practices,habits,alias+types&orTags=0Search results matching tags 'Best Practices', 'habits', and 'alias types'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Bad habits to kick : using alias typeshttp://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-alias-types.aspxThu, 15 Oct 2009 02:07:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:17697AaronBertrand<p><i>In my <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-the-visual-designers.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-the-visual-designers.aspx" target="_blank">last post in this series</a>, I talked about using the visual designers in SSMS.&nbsp; This time, I wanted to treat the use of alias types.</i> <br></p><p><br>To clarify what I am talking about here, alias types used to be called "<a href="http://msdn.microsoft.com/en-us/library/aa933121%28SQL.80%29.aspx" title="http://msdn.microsoft.com/en-us/library/aa933121%28SQL.80%29.aspx" target="_blank">user-defined data types</a>."&nbsp; Then, when CLR came around in SQL Server 2005, they gave us the ability to create our own <a href="http://msdn.microsoft.com/en-us/library/ms131120.aspx" title="http://msdn.microsoft.com/en-us/library/ms131120.aspx" target="_blank">CLR user-defined types</a>
(UDTs) - a different beast altogether.&nbsp; I won't get into the differences, but
to avoid confusion, they came up with a new name (an alias, if you
will; how ironic) for the old-style user-defined data types, which better
reflects what they actually do: alias types. <br>
</p>
<p>In general, the thinking behind <strike>user-defined data types</strike> alias types is that you can use this central definition to declare
the properties of a column type across your entire database.&nbsp; For example,
if you wanted to always store e-mail addresses in a VARCHAR(320)
column, you could do this:</p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE TYPE</font> <font color="black">[email]<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM&nbsp;VARCHAR</font><font color="gray">(</font><font color="black">320</font><font color="gray">)&nbsp;NOT&nbsp;NULL;</font></pre></td>
</tr>
</table>
</blockquote>
<p>(In SQL Server 2000, you would use sp_addtype; for this discussion, I'll assume SQL Server 2005 or better.)</p>
<p>Then you could create a table and procedure like this: <br></p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">CREATE&nbsp;TABLE&nbsp;</font><font color="black">dbo.Contacts<br></font><font color="gray">(<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">ContactID&nbsp;</font><font color="blue">INT&nbsp;PRIMARY&nbsp;KEY</font><font color="gray">,<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="black">EmailAddress [email]&nbsp;</font><font color="blue">UNIQUE<br></font><font color="gray">);<br><br></font><font><font color="blue">CREATE PROCEDURE </font></font><font><font color="black">dbo</font></font><font color="gray">.</font><font><font color="black">Contact_Create<br></font></font><font color="gray"> </font><font><font color="black">@ContactID</font></font><font color="gray"> </font><font><font><font><font color="blue">INT</font></font></font></font><font color="gray">,<br> </font><font><font color="black">@EmailAddress</font></font><font color="gray"> </font><font><font color="black">[email]<br></font></font><font color="gray"><font><font><font color="blue">AS<br>BEGIN<br> SET NOCOUNT ON</font></font></font></font><font><font color="gray">;<br></font></font><font color="gray"><font><font><font color="blue"><br> INSERT</font></font></font> </font><font><font color="black">dbo.Contacts<br></font></font><font color="gray"> (<br> </font><font><font color="black">ContactID</font></font><font color="gray">,<br> </font><font><font color="black">EmailAddress</font></font><font color="gray"> <br> )<br> </font><font><font><font><font><font color="gray"><font><font><font color="blue">SELECT<br></font></font></font></font></font></font></font></font><font color="gray"> </font><font><font color="black">@ContactID</font></font><font color="gray">,<br> </font><font><font color="black">@EmailAddress</font></font><font color="gray">;<br></font><font><font><font><font><font color="gray"><font><font><font color="blue">END<br>GO</font></font></font></font></font></font></font></font><font color="gray"><br></font></pre></td>
</tr>
</table>
</blockquote>
<p>This would ensure that your developers always use a VARCHAR(320) column for e-mail addresses, because they are instructed to use the [email] data type instead of picking their own at random.&nbsp; This provides consistency throughout your tables, and prevents you from losing data when someone accidentally declares a variable or parameter as VARCHAR(100) or VARCHAR(255).&nbsp; Of course someone can still do that, if they "forget" that they are supposed to use the [email] data type... but SQL Server can't help you solve that problem.<br></p><p>One issue with this approach is that the definition of e-mail address (or at least how you understood it at the time you created the type) can change over time.&nbsp; I know that many people use VARCHAR(128) or VARCHAR(255) for e-mail addresses, even though those are far too short (at least according to the standard).&nbsp; In this example, if you picked a big enough size, it is unlikely to affect you, as the standard hasn't really changed (apart from the inclusion of Far East languages, requiring Unicode).&nbsp; Do you want a real example of a supposedly fixed size data element that has changed?&nbsp; ISBN.&nbsp; I would bet that at one point Barnes &amp; Noble thought it might be a good idea to create an alias type called "ISBN" from CHAR(10) so that people wouldn't be using VARCHAR(10), NVARCHAR(10), VARCHAR(50), VARCHAR(MAX), and other silly ways to define the ISBN column.&nbsp; Then when ISBN-13 codes became standard, they would have regretted doing so.&nbsp;&nbsp; Why?&nbsp; Well, let's try modifying an alias type once it's in use; let's say we need to expand our email type from above to support 350 characters.&nbsp; Our instinct would be to use DDL like this:<br></p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">ALTER&nbsp;TYPE&nbsp;</font><font color="black">[email]<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM&nbsp;VARCHAR</font><font color="gray">(</font><font color="black">320</font><font color="gray">)&nbsp;NOT&nbsp;NULL;</font></pre></td>
</tr>
</table>
</blockquote>
<p>But we can't:</p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="red">Msg 102, Level 15, State 1, Line 1<br>Incorrect syntax near 'TYPE'.</font></pre></td>
</tr>
</table>
</blockquote>
<p>In fact, there is no DDL statement that supports ALTER TYPE.&nbsp; (And no, sp_rename or using synonyms won't help you here, nor is there a system procedure called sp_altertype.)&nbsp; So can't we just drop the type and re-create it?&nbsp; You would think you could do the following:<br></p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">DROP TYPE</font><font color="black"> [email]</font><font color="gray">;<br></font><font><font color="blue">GO</font></font><br><font color="blue"><br>CREATE TYPE</font><font color="black"> [email]<br>&nbsp;&nbsp;&nbsp;&nbsp;</font><font color="blue">FROM&nbsp;VARCHAR</font><font color="gray">(</font><font color="black">350</font><font color="gray">)&nbsp;NOT&nbsp;NULL;<br></font><font><font color="blue">GO</font></font><br></pre></td>
</tr>
</table>
</blockquote>
<p>But you get this error message: <br></p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="red">Msg 3732, Level 16, State 1, Line 1<br>Cannot drop type 'email' because it is being referenced by object 'Contacts'.<br>There may be other objects that reference this type.</font></pre>
</td>
</tr>
</table>
</blockquote>
<p>You need to follow this process instead:</p>
<ol>
<li>find *all* of the objects that use this alias type (this includes parameters definitions and variable declarations);</li>
<li>modify them all so that they point at the "regular" type (or a different alias type);</li>
<li>drop the alias type using DROP TYPE;</li>
<li>create the alias type with the new definition;</li>
<li>modify all of your objects to point back at the original alias type again.</li>
</ol>
<p>Now take the case with ISBN, which might also serve as the primary key and have related tables scattered throughout the database.&nbsp; In addition to the above steps, before you start step 2 above, you will also have to temporarily drop all of the constraints, and drop them in the correct order.&nbsp; </p>
<p>Once you have finished all 5 (or 6) of these steps, why not just leave them that way, and point your users at your data dictionary and/or documentation to ensure that they choose the correct data type?&nbsp; At least then when you have data type changes next time, you can phase the new type definition in gradually, instead of an all-or-nothing change.<br></p>
<p>Further to this, what if you need to use the same custom data type in multiple databases?&nbsp; Now,
in addition to the above complications, you must also synchronize the alias types common to all of your
databases, or just hope and pray that their definitions will never change.
</p>
<p>There are other complications with using alias types that you won't come across when using the built-in types.&nbsp; For example, you cannot use CONVERT() or CAST() to change a column, variable or literal to an alias type; if you do, you get:</p>
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="red">Msg 243, Level 16, State 2, Line 1<br>Type email is not a defined system type.</font></pre></td>
</tr>
</table>
</blockquote>
<p>You also don't get syntax highlighting in Management Studio, so unlike other data type names, your alias type names will appear black instead of blue in query editor windows. </p><p>You may also experience issues when you use external or 3rd party providers or drivers to access stored procedures with these alias types, or try to data-bind, or implement Entity Framework or Linq 2 SQL solutions.&nbsp; I'll admit I haven't tried it, but I envision the integration will not be 100% seamless. <br></p>
Another anomaly with alias types is that you cannot declare #temp tables that use them, unless the alias type also exists in tempdb (but you can use them in @table variables, which also use tempdb; go figure):
<blockquote>
<table cellpadding="0" cellspacing="0" bgcolor="#eeeeee">
<tr>
<td>
<pre style="padding:10px 20px;-moz-background-clip:border;-moz-background-origin:padding;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="red">Msg 2715, Level 16, State 7, Line 1<br>Column, parameter, or variable #1: Cannot find data type email.</font></pre></td>
</tr>
</table>
</blockquote>
<p>
For further clarification, you can select *into* a new #temp table from a source that includes a column with an alias type, however the #temp table inherits the properties of the base data type (so in the above example, the column would be defined as VARCHAR(320), not email). </p>
<p>I hope I have demonstrated that, while tempting, the benefits of alias types are far outweighed by the potential problems they can cause down the road.&nbsp; In fact, I am hard-pressed to think of a single implementation of an alias type that would provide any tangible benefits at all.&nbsp; CLR UDTs are a different story, as these can be much more complex and can include additional features like properties and methods.&nbsp; And since the CLR UDTs are based in .NET, you can implement validation rules using real regular expressions.&nbsp; Of course, as with any complex feature, they come with a set of restrictions and prerequisites as well, so I will leave it as an exercise to the reader to determine how much of an advantage these provide *for you* over alias types.<br><br><br><i>I am working on a series of "Bad habits to kick" articles, in an
effort to motivate people to drop some of the things that I hate to see
when I inherit code.&nbsp; Up next: <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx" title="http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx" target="_blank">mis-handling date / range queries</a>.</i><br></p>