SQL Server 2016 deprecation of DML keyword ROWGUIDCOL

Question

I just found out that the DML keyword ROWGUIDCOL is deprecated, replacement is $rowguid; and that DML IDENTITYCOL keyword is deprecated, new name is $identity.

I don't understand why a non-variable (no dollar sign symbol) DML can be replaced with what is essentially an alias, which if I understand it, will sometimes evaluate back to ROWGUIDCOL and sometimes to something else?. Can anyone explain what is going on here? What does this deprecation mean?

In AdventureWorks sample, for example, I don't think it means I should do this:

CREATE TABLE [Sales].[Customer]
(
    [CustomerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    ...
    [rowguid] [uniqueidentifier] $rowguid  NOT NULL,
    ...
);
GO

The above doesn't work obviously. So what is it saying is deprecated? Clearly not the keyword ROWGUIDCOL above?

I thought when something is deprecated that a new syntax must already exist and this would give me time to transition to a form that won't break in "SQL 2017+".

Origin:

Deprecated Database Engine Features in SQL Server 2016

Answer 1

I think you are misreading the deprecation notice. Specifically, you seem to be confusing DML (Data Modification Language) with DDL (Data Definition Language). What is being deprecated is the ability to use ROWGUIDCOL in INSERT / UPDATE / DELETE / MERGE statements:

-- DDL statement
CREATE TABLE #Test
(
  ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY DEFAULT (NEWID()) ROWGUIDCOL,
  SomeColumn INT NULL
);

INSERT INTO #Test ([SomeColumn]) VALUES (1), (100);

SELECT * FROM #Test;

DECLARE @RowID UNIQUEIDENTIFIER;
SELECT TOP 1 @RowID = tbl.[ID]
FROM   #Test tbl;

SELECT @RowID AS [RowToUpdate];

-- DML statement
UPDATE tmp
SET    tmp.[SomeColumn] += 2
FROM   #Test tmp
WHERE  ROWGUIDCOL = @RowID;

SELECT * FROM #Test;

If you run the above, only one row will be updated. This example shows that ROWGUIDCOL when used in a DML statement (not in a DDL statement) is dynamically replaced with the actual column marked as ROWGUIDCOL. And this is, in fact, why you are able (or required?) to mark a column as ROWGUIDCOL in a DDL statement: so that you (and other processes) can find it later :-).

Answer 2

The document you pointed to is actually abundantly clear about what is deprecated:

ROWGUIDCOL as a column name in DML statements.

The recommendation is to use $rowguid.

So for instance:

SELECT ROWGUIDCOL
FROM dbo.SomeTable;

is deprecated. Use this instead:

SELECT $rowguid
FROM dbo.SomeTable;

The key is "DML" in the definition of what is being deprecated. This doesn't affect DDL operations such as CREATE TABLE.