Search code examples
t-sqlazureazure-sql-databasetruncatedatabase-partitioning

AZURE SQL TRUNCATE TABLE PARTITIONS

I get stuck trying to 

TRUNCATE TABLE PartitionTable1 WITH (PARTITIONS (2, 4, 6 TO 8));

That syntax is exactly the same as in MSDN here

In VS 2012 with latest SSDT and Target platform: "Microsoft Azure SQL Database V12" I get this error :

"SQL46010: Incorrect syntax near (. " and its mark bracket between "WITH ( PARTITIONS"

I´ve got same error in AZURE Management Portal - SQL Database.

In Microsoft SQL Server Management Studio i get this error message:

"Incorrect syntax near 'WITH'."

Any thought would be appreciated...

Solution

  • Disclaimer: I work on the SQL Server tooling team.

    There are two separate issues here, one in SSDT and one related to the SSMS failure.

    • For the SSMS failure, the problem is that you are using an Azure v1 Server/Database and this syntax isn't supported against V1 DBs. Try upgrading the server or running against a different one to verify that this works as expected for you. There is information on upgrading or creating Azure V12 databases here.
    • The main SSDT bug is because this syntax is new to Azure V12 (not currently available in any other SQL Server version). SSDT uses a component called SQLDOM to parse scripts, and the current release still uses the SQL Server 2014 RTM version of this component. This will be fixed in an upcoming preview release that will support new syntax such as this. Unfortunately until then this is an issue that will cause this statement to fail in the project system. The workaround would be to avoid using the new syntax until SSDT is updated.