BUG : Script Table / Partition scheme syntax error and missing in index definition
torsten.strauss
Posts: 270 Silver 3
in SQL Prompt
Hi Redgate Team,
Hovering over a partitioned table, SQL Prompt does not add the partition column to the partition scheme which causes a syntax error. Also, no aligned index includes the partition scheme which is wrong as well.
Would be great to see a fix in the next release - thanks.
Example /result of a partitioned table and aligned index
Hovering over a partitioned table, SQL Prompt does not add the partition column to the partition scheme which causes a syntax error. Also, no aligned index includes the partition scheme which is wrong as well.
Would be great to see a fix in the next release - thanks.
Example /result of a partitioned table and aligned index
CREATE TABLE dbo.Transactions
(
col1 int NOT NULL
, col2 int NOT NULL
, DateTime datetime2(2) NOT NULL
) ON ps_DateTime; --- missing the partition column
CREATE NONCLUSTERED INDEX NCL_Transactions_Ccol2
ON dbo.Transactions (
col2
, DateTime
); --- missing the partition scheme and column
GO
Torsten
MVP
Torsten
MVP
Tagged:
Answers
May I ask which version of SQL Prompt you are currently using?
I've done a bit of testing myself on 10.8.4.24201 and I'm not able to replicate exactly what you're encountering.
Using the code in the following guide to create a table with a partition, I can see that SQL Prompt is showing everything related to the partition.
(https://www.mssqltips.com/sqlservertip/2888/how-to-partition-an-existing-sql-server-table/)
Below is the example:
You can see it has everything related to the partition included in the hover over create script.
Running this script works, so long as the partition scheme has been created/exists first.
Kind regards,
Kurt McCormick
Product Support Engineer, Redgate
Need help? Take a look at our Help Center
You are right! I just figured out that this is a problem with an Azure SQL Database only.
Can you please create the table on a SQL Database and try again - thanks!
Torsten
Thanks for the clarification.
I've tested this on an Azure SQL Database and have managed to replicate your initial post.
SQL Server Database
Azure SQL Database
I'll go ahead and get this logged with the development team.
I'll let you know as soon as we have any updates.
Kind regards,
Kurt McCormick
Product Support Engineer, Redgate
Need help? Take a look at our Help Center
Your issue should now be fixed in the most recent release of SQL Prompt:
You can update from the following link:
https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.10.2.25539.exe
Kind regards,
Kurt McCormick
Product Support Engineer, Redgate
Need help? Take a look at our Help Center