liquibase scripts can not represent clustered indexes

Description

CREATE TABLE dbo.testTable
(
ID nchar(10) NOT NULL,
TestLastUpdateValue timestamp NOT NULL,
TestDate datetime2(3) NULL
)
go
ALTER TABLE dbo.testTable ADD CONSTRAINT
PK_testTable PRIMARY KEY NONCLUSTERED
(
ID
)

GO
CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20140612-164512] ON dbo.testTable
(
TestLastUpdateValue
)
GO

liquibase diff this against an empty database, and you get a primary key constraint and an index.

when sql server creates a primary key, it automatically creates a clustered index (unless one already exists, or the nonclustered keyword is used)

the result of rebuilding this is that the table has a clustered index on the primary key, and a non-clustered index on the other column. This represents a physically different layout on disk, and could adversely affect performance.

The concept of clustered index is present in sql server, and mysql.
Oracle calls them " index organised tables" however there are typically better index organisation options in oracle.

recomendation: liquibase diff runs updateSQL and compares it's output to its input and have some type of tag to hold any noticed code irregularities so that code differences are noticed, rather than flaws in diff/update resulting in silent failures.; diff/update/diff should either not return anything, or emit some warnings somewhere along the line that irregular DDL was detected.

Environment

sql server

Reporter

Andrew Hill

Fix versions

Affects versions

Priority

Major
Configure