September 16, 2008

Sure looks like a bug to me.

Create the following:

CREATE TYPE dbo.LocationPreferences AS TABLE (
	Latitude decimal(9,6), 
	Longitude decimal(9,6),
	MinPref int,
	MaxPref int,
	Importance int
)
GO

Then try to pull it back in a query from sys.objects listing everything that isn't shipped by MS (i.e. a list of what you would expect to be nothing but user-created objects):

SELECT name,object_id, type_desc 
FROM sys.objects
WHERE is_ms_shipped = 0

And.... your UDTT doesn't come back in the result set.

But fire off the following:

SELECT name,object_id, type_desc 
FROM sys.objects
WHERE name NOT LIKE 'sys%'

And you get results like this:

Which would seem to indicate:
a) That there's a bug/issue with is_ms_shipped when it comes to UDTTs
b) That there's some weirdness going on as well (notice the names of my UDTT objects compared to normal object names).