JK SQL Server Blog: Datatypes

Showing posts with label Datatypes. Show all posts

Monday, 14 September 2015

In SQL server ,while table creation we can use the SQL server datatype to create columns and also possible to use our own user data type.

If you are going to use the same data type with same length for your entire project ,then we can create our own user defined datatype and use it .

Now we will use our created datatype which is created in our previous post (Refer Here).

Create Table dbo.Student

(

StudentID Int Not Null,

Name Varchar(50) Not Null,

Department UserType

)

Now we have used our created datatype in the above table. So the user datatype will be very helpful in this kind of scenario.

In SQL server ,we can create our own userdatatype and can be used while table creation.

Create Syntax :

CREATE TYPE [TYPE NAME] FROM [DATE TYPE] [NULL | NOT NULL CONSTRAINT]

Example:

CREATE TYPE UserType FROM NVARCHAR(10) NULL

Now the type named "UserType" is created as NVARCHAR datatype and it is nullable column.

Drop Syntax :

DROP TYPE [TYPE NAME]

DROP TYPE UserType

we can also add the type using System SP.

Create Syntax :

EXEC SP_ADDTYPE [TYPE NAME],[DATA TYPE(LENGTH)],[NULL | NOT NULL CONSTRAINT]

Example:

Exec Sp_AddType 'UserType','Nvarchar(10)','Null'

Its also possible to alter the type using the following syntax.

Alter Syntax :

EXEC SP_RENAME [EXISTING TYPE NAME],[NEW TYPE NAME],[USERDATETYPE]

Example:

Exec Sp_Rename 'UserType','UserTypeName','USERDATATYPE'

In SQL server all the datatypes are executed based on the precedence.

The datatype with highest precedence will be executed first and with lower precedence will be executed next.

Please find the list of datatypes used in sql server.