Defaults and User Defined Types Tabs in SSMS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Defaults and User Defined Types Tabs in SSMS

Author

Topic

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-14 : 08:56:57

Hello,
Just to make sure I was using all the available tools for the right purpose, I have been going through all the tabs I haven't used in SSMS. I've managed to research what they all mean but two have me a little stumped. These two tabs are:

[Database]->Programmability->Defaults
Quite hard to search for help on as the word default is used to explain a lot of different things!

[Database]->Programmability->Types->User-Defined Types
I understand what "User-Defined Table Types" and "User-Defined Date Types" are, so what goes in "User-Defined Types"?

Can anyone explain what these are? Thanks very much.

sks198117
Starting Member

46 Posts

Posted - 2010-09-14 : 10:10:38

UDTs are a way to extend the scalar type system of the database.
The scalar type system includes the columnar types that ship with SQLServer
(types like int, nvarchar, uniqueidentifier, etc.). With UDTs, you can
define your own type that can be used for column definitions, for example.
Create a UDT if your type really is an atomic value that is appropriate to
be modeled as a column.
Use UDTs if you have a need to define your own scalar type. Example
scenarios for such types include custom date/time datatypes in various
calendars, and currency datatypes. With UDTs you can create a single object
that exposes all the behaviors that are available on the type, and
encapsulate, or hide, the underlying data that is stored by the type.
Everyone that needs to access the data has to use the UDT's programmatic
interface. If you can leverage existing functionality in the .NET framework
(such as the internationalization or calendar functionality), that is
another really good reason to consider implementing your type as UDT.

More details are here-->

http://msdn.microsoft.com/en-us/library/ms131064.aspx

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-14 : 10:19:18

Isn't that just a user defined data type? What goes in the user defined type tab? Any information on the defaults tab? Thanks!

sks198117
Starting Member

46 Posts

Posted - 2010-09-14 : 10:32:39

http://www.mssqltips.com/tip.asp?tip=1628 for more explanation

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-14 : 10:45:38

That link shows SSMS for SQL Server 2005 which doesn't have the tab "User-Defined Types". I'm not referring to "User-Defined Data Types" or "User-Defined Table Types", specifically "User-Defined Types". If anyone knows what goes in this tab, please let me know! Thanks very much.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-14 : 10:56:02

Here's a screenshot to show what I mean: [url]http://awesomescreenshot.com/0e01bzs6b[/url]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-14 : 13:40:52

In SSMS, the User-Defined Types node refers to CLR data types. The User-Defined Data Types node refers to non-CLR data types based on native SQL Server data types. The terminology used in SSMS is confusing and vague and inconsistent with Books Online and most documentation.

Defaults are database objects that can be bound to user-defined types (both varieties) or columns. They're a great design choice that make it easier to design consistent behavior for your database. I blogged recently about some of the advantages:

http://weblogs.sqlteam.com/robv/archive/2010/08/17/how-to-make-your-databases-better-despite-microsofts-best-practices.aspx

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-15 : 08:56:00

Thanks for that. Don't think I'll worry about defaults then as it's being phased out. Actually, you posted that link to your blog to another question of mine, but it didn't quite answer the question I was asking then so I had forgotten it mentioned defaults.

I know about CLR data types, I just assumed they would appear under "User-Defined Data Types". Not the clearest names in SSMS! Perhaps "User-Defined Data Types" with a sub tab called "CLR" for only CLR ones would have been a lot clearer.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-15 : 11:16:25

quote:
Not the clearest names in SSMS! Perhaps "User-Defined Data Types" with a sub tab called "CLR" for only CLR ones would have been a lot clearer.

Amen! Let's hope they take that suggestion.

What was the question I didn't answer in the other post?

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-09-15 : 11:38:27

quote:
What was the question I didn't answer in the other post?

This one here:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148963[/url]

I guess you did give me a work around, although I was really after a discussion! Still think naming of indexes and constraints being unique for the whole database, rather than the table, is silly. But that's a discussion for the other thread!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources