SQL Server DDL Discrepancy between DataGrip and SSMS

@don v nielsen Hi,

Could you provide original DDL for mentioned table?

That's as good as I got, Vasily. I don't have any change tracking or anything like that to reverse any changes. I am assuming that is the original DDL.

```

CREATE TABLE [dbo].[tbOMOrderStopDate](
    [Id] [dbo].[Id] NOT NULL,
    [OrderStopId] [dbo].[Id] NOT NULL,
    [StopDateType] [varchar](50) NOT NULL,
    [StopDate] [dbo].[Date_NONULLS] NULL,
    [StopDateFrom] [dbo].[Date_NONULLS] NULL,
    [StopDateTo] [dbo].[Date_NONULLS] NULL,
    [CreateDate] [dbo].[CreateDateTime] NOT NULL,
    [CreateUserId] [dbo].[UserId] NOT NULL,
    [ChangeDate] [dbo].[ChangedDateTime] NOT NULL,
    [ChangeUserId] [dbo].[UserId] NOT NULL,
    [RowStatus] [char](1) NULL,
    CONSTRAINT [PK_tbOMOrderStopDate] PRIMARY KEY NONCLUSTERED
    (
    [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =     ON, FILLFACTOR = 85) ON [PRIMARY]
    ) ON [PRIMARY]

```

@don v nielsen Could you provide definition for UDT Date_NONULLS to reproduce the problem?

CREATE TYPE DATE_NONULLS FROM SMALLDATETIME NOT NULL
GO

@don v nielsen So how DATE_NONULLS could be NULL? Looks like there is an issue in SSMS?

> So how DATE_NONULLS could be NULL?

Exactly!

[StopDate] [dbo].[Date_NONULLS] NULL,
[StopDateFrom] [dbo].[Date_NONULLS] NULL,
[StopDateTo] [dbo].[Date_NONULLS] NULL,

Because SqlServer allows you to apply the UDT and then constraints that follow. The UDT says NOT NULL, but the trailing constraint NULL overrides it.

So what is happening in DataGrip is that it is picking up the UDT but not the trailing constraints. Technically, DataGrip is not creating DDL that duplicates the existing table.

UDT's suck, anyway. They are a maintenance nightmare and everyone around me hates them. But it is what it is, unfortunately.

@don v nielsen
I tried to reproduce the issue. My environment:

• Microsoft SQL Server 2017 (RTM-CU3-GDR) (KB4052987) - 14.0.3015.40 (X64)   Dec 22 2017 16:13:22   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)
• SQL Server 16 Microsoft SQL Server Management Studio    13.0.15900.1
• DataGrip 2017.3.7

I created UDT:

CREATE TYPE DATE_NONULLS FROM SMALLDATETIME NOT NULL

Table:

CREATE TABLE test_table (STOPDATE DATE_NONULLS)

Then I generate create statement with  SSMS & DG.
SSMS:

CREATE TABLE [dbo].[test_table](
    [StopDate] [dbo].[DATE_NONULLS] NOT NULL
) ON [PRIMARY]
GO

DG:

CREATE TABLE dbo.test_table
(
  StopDate DATE_NONULLS NOT NULL
)
GO

So there is no difference.

I tried to insert NULL value using SSMS in two ways. I can't insert with INSERT statement. SSMS crashes if I invoke "Edit top 200 rows...".
I can't insert NULL value with DG.

Could you describe your environment in the same manner I did?

@don v nielsen

According to MS SQL documentation https://docs.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql if we define UDT and set it as NOT NULL it cannot contain NULL values.
But, according to CREATE TABLE definition https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql :

An alias type based on a SQL Server system data type. Alias data types are created with the CREATE TYPE statement before they can be used in a table definition. The NULL or NOT NULL assignment for an alias data type can be overridden during the CREATE TABLE statement. However, the length specification cannot be changed; the length for an alias data type cannot be specified in a CREATE TABLE statement.

So, everything works as intended. It looks like the idea was to guarantee NOT NULL value for a column with the mentioned type but to implement it one needs define NOT NULL for each column explicitly.

" type can be overridden during the CREATE TABLE statement "

> So, are we in agreement that DataGrip is missing something in examining the schema and creating the DDL? 

I can't understand what we're loosing.
If you do not explicitly set NOT NULL in table creation MS SQL treats it like NULL (since it's default) and overrides NOT NULL in UDT.

What I am trying to say is:

• developer creates table with column "xxx date_nonulls null"
• (udt date_nonulls translates to smalldatetime not null)
• the trailing null overrides the udt, permitting nulls
• when I recreate ddl using SSMS, SSMS generates "xxx [dbo].[Date_NONULLS] NULL,"
• (The trailing NULL is preserved. Using the DDL will override the udt, permitting nulls)
• when I recreate ddl using DG, it has "StopDateFrom Date_NONULLS"
• (The trailing NULL is lost. Using the DG DDL will not override the udt; nulls are prohibitted.)
• The end result is that DG is creating a DDL with a constraint that is the opposite of the table from which the DDL was generated.

Hello, Don. Thank you for clarification with nulls.

I've filed the related issue: https://youtrack.jetbrains.com/issue/DBE-6033 — you're welcome to watch/vote for.

If you know a way how to make it better than specifying nulls in all cases please comment the issue.

@ don v nielsen 
Also, pay attention to official documentation about nullability rules in table definition and database option is_ansi_nulls_on in sys.databases
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql#nullability-rules-within-a-table-definition