There are three major “engines” in Fabric that I have seen that use T-SQL. There is the SQL Azure Fabric engine, the Lakehouse, and the Data Warehouse. Having this capability to manipulate data is wonderful, but there are some things you need to understand before you start writing code (unless you want to learn them the hard way like I have. I also will not profess to have found all of these differences. Changing my mindset when using these engines was the subject of this editorial

I don’t want to get into it in this series, but T-SQL has different intricacies in the other engines that I will write about some day. This posts is going to cover the Data Warehouse component’s implementation primarily.

The biggest thing that is of interest here is that it feels a lot like a regular SQL Server database, to a point. And some of those points are frustrating to find as you go. None of them are that big, but they will make you just a wee bit annoyed at times until you understand them. This blog will talk about creating tables, and a bit about altering tables as well.

In part 1, I am going to talk about two of the first things I noticed as I started developing my databases in Fabric Data Warehouse. This may not be all I cover in this series about creating a table, but I am starting here with the basic stuff that will trip you up (it did me) first.

Fabric?

Ok, this is a bit of a change for me it might seem, but only slightly. I am covering Fabric because I have been working in Fabric for the past few months building a new data warehouse platform for my company. I am creating tools to move data around, and this series will give you a way to understand some of the stuff to expect.

I will also note I am almost treating Fabric like I would have treated SQL Server. All the work I am doing goes into T-SQL if it can go into T-SQL. I have used Pipelines and Copy Jobs when a limitation has arisen that I have come across.

Datatype differences and inconsistencies

One of the first things you may run into when adding data to a data warehouse is that there are some datatypes that you don’t see. And some you just instinctively try to use. For example, what about sysname? All object names are in this system defined alias for nvarchar(128). But if you try to use that datatype:

CREATE TABLE test
(
    name  sysname
);

You will get this error:

Msg 24574, Level 16, State 1, Line 1
The data type 'sys.sysname' in column 'name' is not supported in
this edition of SQL Server.

Ok…fine, I will just enumerate it as nvarchar(128):

CREATE TABLE test
(
    name nvarchar(128)
);

But even this won’t work:

Msg 24574, Level 16, State 1, Line 14
The data type 'nvarchar(128)' in column 'name' is not supported in
this edition of SQL Server.

At this point, you are starting to realize you aren’t in Kansas anymore, but you have to realize that the datatypes “in the version of SQL Server” (still hoping they parameterize SQL Server so it can say Fabric Data Warehouse!) are different. Character sets are all UTF8 compatible, so varchar will work fine.

You can get the collations and see that they are UTF* using the following queries (and set it when creating a Workspace, which we have chosen case insensitive for own sanity!):

-- Get database collation
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation;

This returns:

DatabaseCollation
--------------------------------------
Latin1_General_100_CI_AS_KS_WS_SC_UTF8

But the default is Latin1_General_100_BIN2_UTF8. You can ask for the server collation:

-- Get server collation
SELECT SERVERPROPERTY('Collation') AS ServerCollation

This returned: SQL_Latin1_General_CP1_CI_AS. It isn’t really relevant that I can see, but it is interesting because I am pretty sure this is for the metadata so it is all case insensitive.

If you want the collation of any column, you can use the system objects or the INFORMATION_SCHEMA views just like on your typical SQL Server db:

-- Get column collations from a specific column
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       COLUMN_NAME,
       COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLLATION_NAME IS NOT NULL; 

And most of these will be the database’s collation, but you can use COLLATE on a definition of a column or variable to set the collation to case sensitive or insensitive if you need to. I have to say it is kind of nice that there are just 2 collations for compatibility reasons, but I wonder if that is causing anyone grief when sorting data?

The minor inconsistency

This idea of not using nvarchar wasn’t immediately obvious to me for one big reason that you can see in the next snippet:

DECLARE @value NVARCHAR(100) = 'Hello'
SELECT  @value;

The output of this is not an error, but it is

----------
Hello

But what datatype is the expression really? when I tried my tricks to see what the type would be (first using sys.dm_exec_describe_first_result_set):

SELECT * 
FROM   sys.dm_exec_describe_first_result_set
            ('select cast(''value'' as nvarchar(10))',NULL,0)

That didn’t work:

Msg 15871, Level 16, State 9, Line 97
DMV (Dynamic Management View) 'dm_exec_describe_first_result_set' is not supported.

And my other trick, to put the value into a sql_variant doesn’t work (and that type isn’t supported for creating objects either):

DECLARE @value sql_variant
SELECT @value,
       SQL_VARIANT_PROPERTY(@value, 'BaseType') AS DataType; 

This caused the sad trombone to play again:

Msg 15871, Level 16, State 6, Line 108
FUNCTION 'SQL_VARIANT_PROPERTY' is not supported.

Lastly, the old faithful way has got to work, right? This at least gave me datatypes!

DECLARE @value sql_variant;
set @value = 'Hello there';
SELECT @value as checkItOut
INTO   testTypes; 

Nope:

Msg 24574, Level 16, State 1, Line 126 The data type ‘sql_variant’ in column ‘checkItOut’ is not supported in this edition of SQL Server.`

You can read more about datatype support in the documentation, but the big point here is that most of what you want to do is going to be slightly different and there will be limitations that make your mind shift a bit interesting. For one last example, datetime2(7) isn’t supported, but datetime2(6) is.

No real constraints

As a relational programmer, and especially a programmer that cares about data quality, keeping the mess out of your database is a large part of creating a data warehouse. So you use constraints (PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK, DEFAULT) and sometimes TRIGGER objects to enforce integrity. None of these are supported in any enforceable fashion.

The only constraint that is supported is the non-named, built in NOT NULL.

PRIMARY KEY and UNIQUE constraints

There are three named constraint types supported, and these are two of them. But, one thing to not is that they won’t do what you almost certainly want them to. I will now take you through the progression of grief I had when I first tried to create a table.

CREATE TABLE PKTest
(
    PKTestId INT CONSTRAINT PKTest PRIMARY KEY,
    OtherValue INT NOT NULL
);

Gives you this error message:

Msg 24584, Level 16, State 3, Line 16
The PRIMARY KEY keyword is not supported in the CREATE TABLE statement
in this edition of SQL Server.

That error message leads you to believe you can’t create a PK, but maybe you can add one:

CREATE TABLE PKTest
(
    PKTestId INT NOT NULL,
    OtherValue INT NOT NULL
);
ALTER TABLE PKTest 
  ADD CONSTRAINT PK_PKTest PRIMARY KEY (PKtestId);

Well sort of:

Msg 24583, Level 16, State 1, Line 34
Enforced constraints are not supported. To create an unenforced constraint you must include the NOT ENFORCED syntax as part of your statement.

By this point, I was getting a bit cranky.

ALTER TABLE PKTest 
   ADD CONSTRAINT PK_PKTest PRIMARY KEY (PKtestId) NOT ENFORCED;

Come on:

Msg 24847, Level 16, State 1, Line 41
Clustered constraints are not supported. To create a nonclustered constraint you must include the NONCLUSTERED clause as part of your statement.

The engine doesn’t quite realize that it is in a Data Warehouse container and not try to make a clustered constraint, so you can (finally) execute:

ALTER TABLE PKTest 
   ADD CONSTRAINT PK_PKTest PRIMARY KEY NONCLUSTERED (PKtestId) NOT ENFORCED;

And this succeeds. It doesn’t do anything except signal to tools this column is there to be the primary way that rows will be accessed and is expected to be unique. You need to check and manage uniqueness yourself. I will pontificate on how this is all kind of counter-productive when building a high data quality reporting platform later, but it is what it is.

I will cover metadata in a later entry, this is al ot of the same “similar but different” feeling that makes you feel like you are at home, but something is different. Like a Disney World person at Disneyland. There is my theme park reference for the day covered too.

You can also add a UNIQUE constraint (also not enforced):

ALTER TABLE PKTest 
  ADD CONSTRAINT AK_PKTest UNIQUE (OtherValue) NOT ENFORCED;

But this too thinks it is clustered?

Msg 24847, Level 16, State 1, Line 53 Clustered constraints are not supported. To create a nonclustered constraint you must include the NONCLUSTERED clause as part of your statement.`

It doesn’t know that this isn’t NONCLUSTERED by default either, so:

ALTER TABLE PKTest
     ADD CONSTRAINT AK_PKTest UNIQUE NONCLUSTERED 
             (OtherValue) NOT ENFORCED;

Is this worth it? I don’t know yet. It does signal to tools both vendor and in-house that this should be unique, and it should be checked when you are doing transformations. But it doesn’t do anything that I have seen…yet.

FOREIGN KEY constraints

Just like PRIMARY KEY and UNIQUE constraints, FOREIGN KEY constraints are just informational. But again, just as I noted they do tell you about your schema and if you want to use data modeling tools to reverse engineer a database, well, they are pretty useful.

For this I will create an FKTest object with a reference to the PKTest object. I won’t go through the same progression of trying to add an FK in the table declaration, but this one is kind of interesting:

CREATE TABLE FKTest
(
    FKTestId INT NOT NULL,
    PKTestId INT NOT NULL
); 

This produces an error:

Msg 24584, Level 16, State 5, Line 232
The FOREIGN KEY keyword is not supported in the CREATE TABLE statement
in this edition of SQL Server.

Um, I didn’t use the FOREIGN KEY keyword! But I knew what it meant at least.

ALTER TABLE FKTest
  ADD CONSTRAINT FKTest$References$PKTest 
    FOREIGN KEY (PKTestId) REFERENCES PKTest(PKTestId) 
       NOT ENFORCED;

Beauty. But like I said, insert a 1000 different values for PKTestId:

INSERT INTO FKTest(FKTestId, PKTestId)
SELECT value, value
FROM   GENERATE_SERIES(1,1000); 

And then check the data and PKTest is empty, so nothing other than NULL should be able to be in FKTest.PKTestId, but there will be:

SELECT *
FROM   PKTest;
SELECT *
FROM   FKTest; 

And the rest

The other two types of constraints, the ones that do NOT require indexes to work, just plain aren’t implemented. PRIMARY KEY and UNIQUE constraints in SQL Server are built on having a unique index to stop data from being entered, and FOREIGN KEY constraints require a quick answer to the question “Does that PK value exist”. So their absence, though pretty painful for data integrity checking, isn’t a big deal.

Their lack does make loading tables amazingly fast (and some recommendations are to drop and recreate tables instead of syncing data as it is faster). But CHECK and (especially) DEFAULT constraints are row based operations. You can’t really leave the context of your row with these (other than the ability to but a function reference in a CHECK constraint.

But they are just plain not available.

ALTER TABLE PKTest 
     ADD CHECK (OtherValue > 100); --NOT ENFORCED

Causes the following error:

Msg 24585, Level 16, State 6, Line 64
The specified ALTER TABLE statement is not supported
in this edition of SQL Server.

Adding NOT ENFORCED just gives a syntax error near ‘NOT’. Same for DEFAULTs:

ALTER TABLE PKTest ADD DEFAULT (100) FOR OtherValue

Causes:

Msg 24585, Level 16, State 6, Line 71
The specified ALTER TABLE statement is not supported in this edition of SQL Server.

Conclusion Part 1

In this post I showed you some of the first differences you will notice when you start writing T-SQL in a Fabric Data Warehouse and creating tables. The fact that I can treat the DW pretty much like I did a SQL Server database is an amazing boost to my productivity since I am quite skilled at SQL Server, but far less so in any of the other stuff you need to do with lake houses and such.

But there are differences that show up, and just because you are working in a platform that looks the same, it may not always act the same. Honestly, it doesn’t help that I am sitting here working on this code using SSMS and Redgate SQL Prompt. I sometimes forget this is Fabric at all, until I hit a limitation.

In future parts, there are plenty of other differences to understand, but the bulk of the differences for most of us are going to lie in datatypes and constraints because they are central to creating a database on a relational engine. Which is something to remember.