Serverless Pool: Msg 15872, Level 16, State 1, Line 13 NOT NULL is not allowed for external table columns.

[jasonhorner]

• SqlPackage or DacFx Version: 162.2.111.2
• .NET Framework (Windows-only) or .NET Core:
• Environment (local platform and source/target platforms):

Steps to Reproduce:

1. Create an External Table in serverless pool

   CREATE EXTERNAL TABLE [dbo].[test2]
                       WITH (
                           LOCATION = '/stage/test',
                           DATA_SOURCE =[dls] , 
                           FILE_FORMAT = ParquetFormat
                       )  
                       AS
                       SELECT 1 as Id

2. Note that in SSMS it shows the column as not null

4 now try to recreate this table specifying NOT NULL

DROP EXTERNAL TABLE [dbo].[test]

CREATE EXTERNAL TABLE [dbo].[test]
(
    [Id] [int] NOT NULL
)
WITH (DATA_SOURCE = [dls],LOCATION = N'/stage/test',FILE_FORMAT = [ParquetFormat])
GO

this error occurs Msg 15872, Level 16, State 1, Line 13 NOT NULL is not allowed for external table columns.

it is also worth noting that SMSS 19.3 will correctly script this out:

CREATE EXTERNAL TABLE [dbo].[test]
(
    [Id] [int] NULL
)
WITH (DATA_SOURCE = [dls],LOCATION = N'/stage/test',FILE_FORMAT = [ParquetFormat])
GO

However ADS Schema compare scripts it out as NOT NULL which it successfully builds in database project / sqlpackage however the resulting script fails when trying to deploy.

to be honest I'm not entirely sure where the bug is here. There seems to be a disagreement between SSMS and DACFX as to what is allowed. In any case this isn't desired or expected behavior.

I would assert we should be able to define a column as not null in the table definition in serverlesss pool or if not the behavior across all tools should be consistent.

(DacFx/SqlPackage/SSMS/Azure Data Studio)

[dzsquared]

Serverless SQL pools don't support null/not null in the external table column definition, so we need to fix in DacFx:

1. failing the SQL project build if the project targets SQL serverless and the columns specify null/not null
2. generating the table definition for external tables in SQL serverless should not contain the specifier

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azure-sqldw-latest&preserve-view=true&tabs=serverless#syntax-2

SSMS scripting it out with the null included is also not correct, that would throw an error that NULL is not allowed for external table columns.