CODEWITHSUNDEEP
sql-server
Suvasish
Suvasish

Reputation: 11

User-defined data type issue in SQL Server 2014

I have created a not nullable user defined data type using the below script

CREATE TYPE ssn
FROM varchar(11) NOT NULL ;

But the user-defined data type is allowing NULL value. I am using SQL Server 2014 Developer edition. Please let me know if I am missing something.

Proof of concept:

DECLARE @a ssn = NULL; 
SELECT @a

Upvotes: 1

Views: 408

Answers (3)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

To expand on what @a_horse_with_no_name was saying with regards to NOT NULL working only on columns and not variables:

A variable is of a particular datatype (i.e. type and, when applicable, max size or precision/scale), but will not take on any other properties or constraints, such as:

  • NOT NULL
  • IDENTITY
  • Collation
  • DEFAULT CONSTRAINT
  • ROWGUIDCOL
  • SPARSE

Even though you are able to declare a variable as the User-Defined Data Type (UDDT) that you created (which includes NOT NULL as part of its definition), you could not declare a variable using NOT NULL:

DECLARE @Bob INT NOT NULL;

Returns:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'NOT'.

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

I guess it is due to ANSI_WARNINGS.

When set to ON, the divide-by-zero and arithmetic overflow errors cause the statement to be rolled back and an error message is generated

But in case of SET/SELECT,

ANSI_WARNINGS is not honored when passing parameters in a stored procedure, user-defined function, or when declaring and setting variables in a batch statement.

For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.

DECLARE @var char(3)
SET @var = 'ABCDEF'
SELECT @var --ABC

The above query works even though we are inserting more than 3 character's

When you try to do the same thing in Table

CREATE table test(colu CHAR(3))
INSERT INTO test VALUES('ABCDEF')

Will fail, saying

Msg 8152, Level 16, State 14, Line 9 String or binary data would be truncated.

Upvotes: 0

user330315
user330315

Reputation:

The NOT NULL constraint only applies to values when the type is used as a data type for a column in a table, not for variables. The following would not work:

CREATE TYPE ssn
FROM varchar(11) NOT NULL;
create table foo (id integer primary key, foo_ssn ssn);

insert into foo values (1, null);

The insert results in

Cannot insert the value NULL into column 'foo_ssn',

Upvotes: 3

Related Questions