Featured Post

Introduction To Silverlight with Brian Sullivan at HubCityNUG Nov. 18t

  Brian Sullivan will be at HubCityNUG for the month of November giving a presentation on “Introduction to Silverlight”.  If you are in any of the following cities located near Hattiesburg, MS we hope you’ll come out on Thursday November 18th, 2010 for Hub City .NET User Group meeting:Hattiesburg,...

Read More

SqlDependency and SQL Service Broker Permissions

Posted by Keith Elder | Posted in .Net, SQL Server | Posted on 20-01-2009

2

Today I spent the better part of the day working with one our DBAs at work to setup SqlDependency so it would work with SQL Server.  In order to use SqlDependency it requires Service Broker.  Thus we enabled Service Broker on the database.   The rest of the day we kept getting the following error each time SqlDependency would get called on start up.

Error: Message: Cannot find the procedure ‘SqlQueryNotificationStoredProcedure-42d557f5-071f-456d-8d4c-a1bafc4cbece’, because it does not exist or you do not have permission.
Cannot find the queue ‘SqlQueryNotificationService-42d557f5-071f-456d-8d4c-a1bafc4cbece’, because it does not exist or you do not have permission.
Invalid object name ‘SqlQueryNotificationService-42d557f5-071f-456d-8d4c-a1bafc4cbece’.

After much Kung Fu searching and trial and error we finally found something.  We had to enable the following permissions to get this to work.  Thanks to Remus Rusanu for the information. 

GRANT CREATE PROCEDURE TO [SqlUser];
GRANT CREATE SERVICE TO [SqlUser];
GRANT CREATE QUEUE TO [SqlUser];
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [SqlUser];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SqlUser];
GRANT CONTROL
ON SCHEMA::[dbo] TO [SqlUser];
GRANT IMPERSONATE ON USER::DBO TO [SqlUser];

A security red flag with these permissions is the “GRANT CONTROL ON SCHEMA::[dbo] to [SqlUser];”.  This opens up the permissions extensively on the database for that user to do all sorts of things.  I’m not a DBA, but reading the documentation on what GRANT CONTROL scared me enough to know we can’t leave this in a production environment.  Jim, the DBA at work, has an idea to tune this down. 

Better Permissions

Jim, our Kung Fu DBA found an article that outlined some finer grained permissions here.  The script on that page assumes you have two different users or services.  One that creates the dependency, one that is the listener.  Jim’s script assumes you have only one account doing both functions.  This is how most setups will be more than likely.  Thus use this script instead of the one above, unless you like security holes in your database. :)

--Script to give user:[User] rights to use the SQLDependency functionality in .Net 2.0
CREATE ROLE [SQLDependency] AUTHORIZATION [dbo]
GO
 
CREATE SCHEMA [SQLDependency] AUTHORIZATION [SQLDependency]
GO
 
--Database level permissions
GRANT CREATE PROCEDURE to [SQLDependency];
GRANT CREATE QUEUE to [SQLDependency];
GRANT CREATE SERVICE to [SQLDependency];
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [SQLDependency];
GRANT VIEW DEFINITION TO [SQLDependency];
 
--Service Broker permissions
GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO [SQLDependency];
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [SQLDependency];
GO
 
EXEC sp_addrolemember N'SQLDependency', N'[User]'
GO
 
ALTER USER [User] WITH DEFAULT_SCHEMA=[SQLDependency]
GO

Thanks to Jim the Kung Fu DBA for finding a better solution.

DreamSpark – Fantastic Idea But Only The First Step

Posted by Keith Elder | Posted in .Net, Smart Clients, SQL Server, Windows, XBOX 360 | Posted on 27-02-2008

5

When I was in high school one of my math teachers took it upon himself to teach a few students about computer programming.  It wasn’t a real class it was just something he put together during our free period.  Instead of attending study hall we’d go to this computer programming class.  Basically other students in the school called the few of us taking the class the “Star Trek” club.  Honestly I didn’t watch Star Trek and never have.  It just never interested me.  I know that I just lost thousands of geek points by stating I am not a Star Trek fan publicly but when you grow up on a small farm in Mississippi things need fed, watered, and slopped.  Coming home from school and sitting down to vegetate to watch TV wasn’t in the cards of our household.  Anyway, I digress.  Even though Mr. Foley didn’t have a lot of support from the school in putting together this class, it was the one thing that really got me hooked on computers.  Mr. Foley was only one man though.  It takes a much larger effort to get students interested in technology.  To hook the next generation of students on programming Bill Gates recently announced DreamSpark.  It is a new project that will provide thousands of dollars of free software to students.  Students like me who didn’t have access to the tools companies were using while they learned can now install and leverage the same products for free.   As an educator I’m really excited to see this announcement.   Here are some thoughts as to why I think this is important and what should spark within the community.

What is DreamSpark?

DreamSpark is simple, it’s all about giving students Microsoft professional-level developer and design tools at no charge so you can chase your dreams and create the next big breakthrough in technology – or just get a head start on your career.

DreamSpark makes the following software available to students who register on the site.   The program is available in 11 countries giving millions of students access to professional developer tools.

image

All a student needs is a computer to get started.  They can load Windows Server 2003 onto the machine along with Visual Studio 2008, SQL Server 2005 and Expression Studio and have the same tools we are using today in enterprises across America.  If the student wants to build games for Windows or the XBox then they can install XNA Game Studio.  The amount of things that can be built with these technologies is amazing, especially when you think about WPF Windows Applications and Silverlight.

Ok students, Bill has done his part to give you the software.  What are you going to do with it now?  Someone has to replace a lot of the Elder generation as we age.  Even right now there is a huge demand for developers that know these technologies.  Here are some numbers from the popular online job engines.

Searching for .Net

  • Dice.Com – 11,131 jobs available
  • Monster.Com – Over 5,000 (max return value)
  • CareerBuilder.Com – 3,376

Searching for SQL Server

  • Dice.Com – 14,588
  • Monster.Com – Over 5,000 (max return value)
  • CareerBuilder.Com – 8357

Searching for Windows

  • Dice.Com – 15,480
  • Monster.Com – Over 5,000 (max return value)
  • CareerBuilder.Com – 16,933

Positives About DreamSpark

I Couldn’t Even Buy A Vowel

As I said earlier, the DreamSpark program is a great idea I just wish it was around when I was younger.  When I was in college I was still doing off to the side programming while pursuing my music degree.  I knew there were tools, IDEs, databases and other things that people in large companies used but I was broke.  I was financially embarrassed as my father used to say.  I was so broke I couldn’t even afford to buy a vowel on Wheel of Fortune.  The only option I really had was to learn open source tools.  Back then the open source tools were, well, hard.  There weren’t web pages full of documentation, books, and fancy editors.  You basically first had to learn the VI editor and then try to dig through the man pages and the source code.  To say the experience was painful would honestly be an understatement.  It was slow going.  The sad fact today is that a lot of students are in the same boat I was back then.  DreamSpark fills this gap and puts the latest technology in the student’s reach.  This is wonderful, I can’t express that enough. 

Challenges DreamSpark Faces

It is important that students be offered the opportunity to learn the tools we are using today but the sad fact is only a few will take advantage of it without the community getting in behind this effort.  If 1% of the students downloaded these tools, I would consider that a success.  A perfect benchmark to go against is every student enrolled in a Computer Science program and every student in high school taking computer programing.  If 100% of those students download and use the software then that is a perfect world. 

In order to achieve this goal, it is going to take a lot of effort from those of us in the community.  The following is an open letter to Microsoft, the community and educators who can effect change and help create the next generation of programmers.

Give It Away And They’ll Come

The first thing we must overcome is thinking free equals a lot of use.  Some have heard the line before “Build it, and they will come.”.  The reality is just because something is free doesn’t mean anyone will use it.  If this were the case, we wouldn’t need ads, marketing departments, or ad agencies.  This is the first thing we need to do, spread the word.

Ask yourself this question.  How many students will seriously go download this software?  How many of them will actually learn about it?  There are numbers in the industry I’ve heard repeated of the 5% geek rule.  The premise is take 100 computer science students and only 5% of them are truly dedicated to their profession.   This top 5% are the ones that will more than likely take advantage of DreamSpark, the rest will never know.

We (Microsoft, the community, and educators) need to make a concerted effort to spread the word about DreamSpark.   We need to establish DreamSpark Day events at schools and Universities in our local areas and get this information out.  By information I am not just talking about the bits, that isn’t enough. 

How Long Will It Take?

Here is a pondering question.  How long will it take the education institutions to realize they have thousands of dollars of software available they can build an entire curriculum around?  Unless a concerted effort is made the answer is years.  Why?  Well for starters very few of the professors know the technology.  Sad but true.  When I say “few”, I mean a very small percent. 

There are a few professors out there that are teaching .Net.  Those educators should have no problem, but the majority of professors I’ve encountered love to tell you how their first computer was the size of a house and how they punched cards to program it.  What really saddens me is the “dinosaur” professors as I like to call them are still doing things the way they have for years.  Thankfully this isn’t every professor out there, but there are professors at colleges that still rest on their laurels and just learn enough to teach out of a book.  The majority of these professors have no real world experience in technology except in academia because they have spent their entire lives in academia.  I know these types of professors exist because I had several of these “dinosaur” teachers in college.  I used to call them the “Punchcardasaurus”. 

“Punchcardasaurus – A professor who loves to tell you about the stone age of computers but doesn’t know anything about today’s current technology.”

I can’t begin to tell you how much of a waste of time it is for someone like myself to have to sit through a lecture from a Punchcardasaurus only to correct him or her about how the Internet really works and the Mosaic browser is not really the Internet (true story).  I’m starting to digress but hopefully the point is driven home.  A concerted effort needs to be made at the institutions of learning.

Educational Institutions Already Have Free Software – Open Source

I firmly believe that higher educational institutions should focus more of their efforts putting students in a position to get a job and succeed.  Sadly the curriculum of colleges don’t.  Someone graduating from college today should have experience with all sorts of technologies and languages.  Colleges that just teach C++ are doing their students a grave injustice.  Students should learn C, C++, C#, Obj-C, Java, and a variety of other languages including dynamic languages.  Each of these should expose students to the various types of platforms such as Windows, Unix and OS X. 

The majority of colleges use a lot of open source software to teach their students today because, well, the software doesn’t cost the college nor the students anything.  If a college is just focusing on open source software, they are really doing their students a major disservice.  Students should be educated on both sides of the fence and taught multiple platforms.  In the end it is the students who should decide which platform they feel will be the most valuable to their career or interest, not the educational institution.  The more this is done, the more the students  know and the better chance they have at getting a job.  DreamSpark of course helps to fight the cost factor argument with open source software but it is going to take time for the value to sink in.  Especially in institutions where open source software has a very strong hold with a lot of zealots to play devils advocate.  Yes, these zealots exist, to ignore this fact would not prove wise.

Train The Trainer

The Punchcardasaurus and other professors need help.  It has to start with those teaching others.  Here is another sad but true fact.  Even if a school wanted to build a curriculum around DreamSpark who’d teach it?  If it was offered the odds of it being taught with real world business experience on the platform is very small.  We need to do something to train the current educators how to use these new tools.  There are a variety of ways this could be done.

  1. Use the current Microsoft training curriculum and certification courses to offer classes to faculty and staff.  Schools are off in the summer and it would be a great time for re-educating.
  2. Help the teachers by putting together a standardized curriculum around the DreamSpark project so there is consistency to how the platform is taught.

I personally would be more than willing to spend my time during the summer to put together a train the trainer workshop for those educators willing to participate.  This could be run at night so not to interfere with work duties. 

Already Lagging Behind

Another sad but true fact is educational institutions are years behind in terms of the technology being taught.  I know when I was in college this was true.  For example the language called Java was started in 1991 and released in 1994/1995.   While this was a newly released technology it wouldn’t be taught in the classroom until many years later.  Back then they were teaching us Pascal.  The progression worked something like this.  Once you mastered Pascal, which at the time was already considered a dead language, you could learn C, and then after that C++ and if you stayed around to get your PhD they’d eventually mention Java as an experimental language.  Basic skills are an absolute must have but what chance does a student have at actually getting a job today in a company that uses a technology like .Net where the student has never used it?  Very few companies have a need for engineers that are highly skilled in assembly and compilers.  However, a company would be willing to hire someone who knows how to write applications using Windows Presentation Foundation, Silverlight, Windows Communication Foundation and Workflow Foundation.  All of which have been released in the last year.  I truly feel sorry for students graduating today because the industry changes so quickly.  They need our help.

What Can We Do To Help

There are a lot of challenges that need to be overcome no doubt.  All of them are challenges, not road blocks though.  They can be fixed.  To restate some of the ideas above here are the things we (Microsoft, the community, educators) need to start with.

  1. The Community / Microsoft – Organize some type of event at educational institutions to help spread the word.
  2. The Community / Microsoft – Help train the current educators.
  3. Microsoft – Help the educators standardize a curriculum using existing training information, labs, etc.
  4. Microsoft – Put together a program whereby students build something using the tools and award them with free trips to TechEd or Mix or other conferences.

 

Creating Custom SQL CLR User-Defined Types

Posted by Keith Elder | Posted in .Net, SQL Server | Posted on 29-10-2007

6

Since SQL Server 2005 .Net developers and DBAs have had the ability to create custom SQL CLR types.  It wasn’t until recent I found a few uses for them.  Not only did I find a use for them but a DBA who is a team member did as well.  If you would like more information about SQL CLR types or are new to them start by reading the introduction here.   What follows is a walk through from start to finish creating a user-defined type (UDT).   The project source code is available at the end of the article.

Maintaining Data Integrity

One of the advantages of SQL CLR User-Defined Types is the ability to store complex information in SQL Server and guarantee the integrity of the data.  For example, let’s say a database is fed information from an SSIS package at night that has to move email address information from one system to another.  How would one guarantee the email address field is valid as it is moved from system A to system B?  Obviously that would have to be build that into the SSIS package somehow.  But does that “really” guarantee another SSIS package a Jr. Database Engineer wrote does the same validation?  No it doesn’t.  Removing SSIS packages from the equation we still have to worry about end-user input and hope our developers from various teams or platforms follow the same validation rules.  It is a tough balancing act to say the least.  Having data integrity close to the data store has its advantages since everything coming into the database must pass the data integrity spec.  This is where a SQL CLR User-Defined Type can come into play.  Instead of creating a column to store email address in a type of varchar(50) or whatever your company uses as a design standard, we could create a SQL CLR User-Defined Type of “EmailAddress”.  Within the .Net runtime we could easily validate the integrity of the email address and throw an exception if it doesn’t match.  We could also split apart the domain and prefix values if we wanted to pull out all email addresses for the domain keithelder.net.  Let’s look at an example.

Creating Your Project

To get started creating a SQL CLR type we first need to create a project that understands how to build SQL Server types.  To do this open up Visual Studio and point to Database Projects (assuming you installed the database components) and select SQL-CLR.  Then chose the language for your project.  In our case we are going to select C#.

image

After the project is created the first thing to do is to create a folder to store the various types.  It isn’t wise to just start adding different CLR types to the project, things will get messy.  Right click the project and add a folder called “Types”.

After you’ve added the folder, right click the folder and click “New Item”.  Select User-Defined Type from the menu and give your type the name of the type you want displayed in SQL Server (important).

image

In this example I am calling the type ”EmailAddress”.   Once entered click add.

Project Cleanup

After the custom user-defined type is added to Visual Studio the code should look like this.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct EmailAddress : INullable
{
public override string ToString()
{
// Replace the following code with your code
return "";
}

public bool IsNull
{
get
{
// Put your code here
return m_Null;
}
}

public static EmailAddress Null
{
get
{
EmailAddress h = new EmailAddress();
h.m_Null = true;
return h;
}
}

public static EmailAddress Parse(SqlString s)
{
if (s.IsNull)
return Null;
EmailAddress u = new EmailAddress();
// Put your code here
return u;
}

// This is a place-holder method
public string Method1()
{
//Insert method code here
return "Hello";
}

// This is a place-holder static method
public static SqlString Method2()
{
//Insert method code here
return new SqlString("Hello");
}

// This is a place-holder field member
public int var1;
// Private member
private bool m_Null;
}


There are several things we do not need here as well as some refactoring we need to do.  Let’s clean things up a bit. 

First thing I suggest is to remove the Method*() methods.  They aren’t needed, they are just place holders.  The next thing is to refactor the m_Null variable.  Personal choice here but I can’t stand the naming convention of m_.  It irks me to no end.  It must die. 

The next thing we need to do is create a variable to hold our email address value.  Since our type is called EmailAddress I am going to call this new property Value.  After the cleanup is done we should have something similar to this.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct EmailAddress : INullable
{
private string _value;
/// <summary>
/// The email address value.
/// </summary>
public string Value
{
get { return _value; }
set { _value = value; }
}

public override string ToString()
{
return _value;
}

private bool _isNull;
public bool IsNull
{
get
{
return _isNull;
}
}

public static EmailAddress Null
{
get
{
EmailAddress h = new EmailAddress();
h._isNull = true;
return h;
}
}

public static EmailAddress Parse(SqlString s)
{
if (s.IsNull)
return Null;
EmailAddress u = new EmailAddress();
// Put your code here
return u;
}
}

Coding Our Logic

We are now ready to put in our own logic to validate our email address along with some other features.

To start with let’s add a method to the code to validate the email address.  In this example I am going to use a regular expression that simply validates the integrity of the data.  Notice I didn’t use the word “validates” because validation of an email and and data integrity are two different things.  Integrity just checks to make sure my data is in the proper format.  It handles finding problem email addresses liked @asdf.com or blah@ and so on.  Whether the email address is actually valid and it works is a totally different story that requires the use of business services.  This is not the point here.  We just want to make sure that if someone is going to store an email address in our database it adheres to some type of data integrity and that our database will not except any text a developer throws at it.  Think of it as “gate keeper logic”.

After we add our validation logic we are going to add a call in the Parse(SqlString s) method.  This is where we are going to call our validation.   The new code will look something like this:

public static EmailAddress Parse(SqlString s)
{
if (s.IsNull)
return Null;

EmailAddress email = new EmailAddress();
if (email.ValidateEmail(s))
{
email.Value = s.ToString();
return email;
}
else
{
throw new SqlTypeException("The email " + s.ToString() + " was not in the proper format.");
}
}

/// <summary>
/// Validates the email being passed in.
/// </summary>
/// <param name="email"></param>
/// <returns></returns>
public bool ValidateEmail(SqlString email)
{
bool isValid = false;
if (this.IsNull)
{
return true;
}

isValid = Regex.IsMatch(email.ToString(), @"^[\w-]+(?:\.[\w-]+)*@(?:[\w-]+\.)+[a-zA-Z]{2,7}$");
if (isValid)
{
return true;
}
else
{
return false;
}
}

Fixing Serialization

At this point if build our solution it will work.  However, if we right click our project and click “Deploy” it will fail with the following error.

image

The reason is we have the email address as a string and we also have the object attributed with the serialization of Format.Native.

[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]

The native format can only store value types, not referenced types.  This means it is limited as to what we can use (bool, int, etc).  String is not one of these so we need to create our own user-defined format.  Don’t worry, it isn’t as hard as it sounds.  The first step is to change the attribute to this:

[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,IsByteOrdered=true,MaxByteSize=8000)]

Set the format to UserDefined, the IsByteOrder to true and the MaxByteSize to 8000.  Setting the format to UserDefined allows us to then put our own logic in place.  We do this by implementing IBinarySerialize in our custom type as we’ll see later on.  The IsByteOrdered option set to true guarantees the serialized binary data can be used for semantic ordering of the information.  This allows us to use the same operations in SQL Server as in managed code.  See this page for all of the options this enables like creating indexes, group by and more.  MaxByteSize can only be as large as 8000 bytes so we essentially set it the largest value.  If you know the value is going to require less information, set it to that value.  Yes in this case 8000 is more than an overkill for email addresses but I put it there so I could explain that 8000 is the largest the value can be.  Important to know.

Once we have our attribute setup properly we need to implement the interface IBinarySerialize as I mentioned.  There are only two methods to this interface: read and write.   Since we only have one property we are going to be storing this is pretty simple.  Here are the two methods implemented.

   public void Write(BinaryWriter w)
{
w.Write(_value);
}

public void Read(BinaryReader r)
{
_value = r.ReadString();
}

Ready To Deploy

As you see creating our own user-defined format wasn’t that bad.  Only a few lines of code.   At this point the project should build and should be deployable to the database.   To deploy the EmailAddress type to the database right click the project in Visual Studio and chose “Deploy”.  After it is deployed it should appear in the User-defined types under the Programmability section within SQL Server Studio as follows.

image

Using Our UDT in A Column

Once the UDT is deployed into the database we can associate our new type as the “Data Type” with a column in the database.  If we create a new table we’ll see the new type in the drop down.

image

Cool!  Yes and now we start thinking about other UDT’s such as phone numbers, zip codes, and more.  Where do we stop!?

Using Our New EmailAddress User-Defined Type

Now that we have a table to put our emails in.  Let’s add some email addresses to it and start playing around.  I added four email addresses to mine to initially play with.  After the “fake data” is entered, do a “select * from Email” T-SQL query to see the results of the data.  It should look like this:

image

Whoa!  Yes, this is scary.  The reason has to do with the format the database is storing our data.  Since we were not able to use the native format we can’t see the data as expected.  If you reference the code after we cleaned up you’ll remember there was a “Value” property in our object.  The good news is we can use this property to get at and see our data.  Let’s rewrite our query using that property like this:

select EmailId, EmailAddress.Value as Email from Email

The result of running this will be:

image

This is why I chose the naming convention of “Value” for this field since it seems a little weird to type EmailAddress.EmailAddress or EmailAddress.Email.  It made sense to just ask for the value stored.  I hope that makes sense.

Checking Our Validation

Now that we are able to select data properly from our table let’s look at how our validation works.   Here is the SQL query we are going to issue to SQL Server.

INSERT INTO [CARI2].[dbo].[Email]
([EmailAddress])
VALUES
('fakeaddress')

Of course the email address of “fakeaddress” shouldn’t pass our data integrity.  If we run this command on our table the following error is generated.

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "EmailAddress":
System.Data.SqlTypes.SqlTypeException: The email fakeaddress was not in the proper format.
System.Data.SqlTypes.SqlTypeException:
at EmailAddress.Parse(SqlString s)
.
The statement has been terminated.

This is exactly what we wanted since this stops anyone from entering data into our database that doesn’t pass our integrity check.  This means that neither an SSIS package running in the middle of the night nor an end-user is able to insert erroneous information into our data store.  Business problem solved.

Advance Queries and Extension Properties

It is important to remember that our custom type is merely an object.  As with any object we can add methods or properties to that object.  Here are some examples to get your brain turning. 

Let’s start with the example looking up all users that are in the domain “keith.com”.   How would we do that?  The trick is to remember we have to reference the .Value of the column to get to the data (this was the public property we created).  If you keep this in mind then it is like any other query.  Here’s an example:

select EmailId, EmailAddress.Value as Email from Email where EmailAddress.Value like '%keith.com'

A side note, SQL Server Studio does not give you intellisense when typing so it is important whatever names you come up with you standardize on to not confuse your team members. 

What if we wanted to do a distinct lookup of domains or to print the domain or the prefix values of the emails for data mining purposes?  In these cases you can chose to add extension properties to your object.  Below is the code for two examples using the prefix and the domain.

    /// <summary>
/// The part of the email address before the @ sign.
/// </summary>
public string Prefix
{
get
{
string[] x = Value.Split('@');
return x[0];
}
}

/// <summary>
/// The domain of the email address.
/// </summary>
public string Domain
{
get
{
string[] x = Value.Split('@');
return x[1];
}

}

As you see we are using the Split method to split our email address string into two parts using @ sign.  Instead of doing the like clause the way did it above that can now be expressed like the following:

select EmailId, EmailAddress.Value as Email from Email where EmailAddress.Domain='keith.com'

We can also do this now:

select EmailId, EmailAddress.Domain as Domain from Email

Which would yield us this:

image

Or we can do a distinct lookup like this which provides us with a much cleaner T-SQL syntax.

select distinct(EmailAddress.Domain) as Domain from Email

While this is all neat and good let me point out that I haven’t done any extensive performance tests to see which method performs better so be sure you do that before using one over the other.

Updating Your User-Defined Type

So far it has all been shiny pennies up until this point.  Watch out though here comes a rusty washer.  

When you update your UDT you are going to have to drop the UDT from any columns that use it, and then redeploy and then re-add.  Obviously this can be scripted but it is a pain, especially if you are dealing with tables that have millions of rows so be careful.  If your only goal is to get data integrity you may be better off using a user-defined trigger instead.  The bottom line is to make sure your UDT doesn’t change or need to change once you deploy it.

Conclusion

That’s it folks, a start to finish nuts and bolts of building user defined-types in SQL CLR.  Wasn’t it exciting?  I hope this helps those that are investigating SQL CLR User-Defined Types.