The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P129: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 622 Chapter 14 Implementing Objects user-defined data type to represent people s names. This UDF can be based on nvarchar 50 and cannot contain nulls. This UDF can now be bound to any column that is to contain people s names and will be consistent throughout. Create your user-defined data types in the Model system database so that it is automatically inherited by all new databases you create. User-defined data types are created using the CREATE TYPE syntax is shown in Example . Example CREATE TYPE Statement Syntax CREATE TYPE schema_name. type_name FROM base_type precision scale NULL NOT NULL Example shows the syntax used to create a user-defined data type named PersonName and to create a table that contains two columns of type PersonName. Example Using the CREATE TYPE Statement CREATE TYPE PersonName FROM varchar 50 NOT NULL GO CREATE TABLE TeamMembers Memberld int PRIMARY KEY MemberName PersonName ManagerName PersonName GO Use the ALTER TYPE statement to change the definition of your user-defined types. The DROP TYPE statement should be used to remove the user-defined data types you no longer need in the database. You cannot remove user-defined types from the database while there are tables with columns based on these types. If you attempt to use the DROP TYPE statement to remove a data type that is in use you will get an error message similar to Msg 3732 Level 16 State 1 Line 1. Cannot drop type PersonName because it is being referenced by object TeamMembers . There may be other objects that reference this type. Implementing Objects Chapter 14 623 Working with Constraints Constraints are data validation rules that are bound to a column or a set of columns in a table. Constraints can also be used to enforce a relationship between two entities represented as two tables. The available types of constraints are as follows Check Constraints These constraints validate the integrity of data in a column by checking it against a valid .
