× Heads up!

Aqua Data Studio / nhilam

Follow
IDE for Relational Databases
×
samjacinto reported 2019-09-30T16:34:16Z  · nhilam last modified 2019-09-30T18:20:43Z

Case #00756988 - Enhance script generation


customer request
Priority Major
Complexity Unknown
Component App - AquaProjects
Version Future

Reference Aquaclusters case #170

Enhance the script generation to the same level as SQLServer, which includes the following:

* Index options should include compression (and possibly fill factor)

* Include columnstore

* Include filegroup/partition schema

* Include partition scheme/function

So it seems there are some issues when scripting from Azure. These have always existed for Azure and I'm finally got annoyed enough to email you guys.

Here is some basic SQL to create partition function, schema, tables, PK constraint, nonclustered indexes, and columnstore index. All of which are scripted incorrectly.


CREATE PARTITION FUNCTION [PARTFUNC_AQUA_DATA_TEST] (varchar(50)) AS RANGE LEFT FOR VALUES(N'[ALL]')
GO
CREATE PARTITION SCHEME [PARTSCHEME_AQUA_DATA_TEST] AS PARTITION [PARTFUNC_AQUA_DATA_TEST] ALL TO ( [PRIMARY] )
GO
CREATE TABLE [dbo].[AQUA_DATA_TEST] (
[COLUMN1] int NOT NULL,
[COLUMN2] int NOT NULL,
[COLUMN3] int NOT NULL,
[COLUMN4] int NOT NULL,
[PARTITION_COL] varchar(50) NOT NULL
)
GO
ALTER TABLE [dbo].[AQUA_DATA_TEST]
ADD CONSTRAINT [PK_AQUA_DATA_TEST_CLUS]
PRIMARY KEY CLUSTERED ([PARTITION_COL])
on PARTSCHEME_AQUA_DATA_TEST([PARTITION_COL])
GO
CREATE NONCLUSTERED INDEX [VFIDX_TEST]
ON [dbo].[AQUA_DATA_TEST]([COLUMN1])
on PARTSCHEME_AQUA_DATA_TEST([PARTITION_COL])
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [CSI_TEST]
ON [dbo].[AQUA_DATA_TEST]([COLUMN1],[COLUMN2],[COLUMN3])
on PARTSCHEME_AQUA_DATA_TEST([PARTITION_COL])
GO


Below is what is scripted from Azure with Aqua Data.

* It does not handle index options such as compression i suspect fill factor as well
* columnstore has columns coming out in include syntax
* no filegroup/partition schema
* It does not script the partition scheme/function as it would in the normal SQL Server connection

CREATE TABLE [dbo].[AQUA_DATA_TEST] (
[COLUMN1] int NOT NULL,
[COLUMN2] int NOT NULL,
[COLUMN3] int NOT NULL,
[COLUMN4] int NOT NULL,
[PARTITION_COL] varchar(50) NOT NULL,
CONSTRAINT [PK_AQUA_DATA_TEST_CLUS] PRIMARY KEY CLUSTERED([PARTITION_COL])
)
GO
CREATE NONCLUSTERED INDEX [IDX_TEST_COMPRESSION]
ON [dbo].[AQUA_DATA_TEST]([COLUMN1])
INCLUDE ([PARTITION_COL])
GO
CREATE NONCLUSTERED INDEX [CSI_TEST]
ON [dbo].[AQUA_DATA_TEST]()
INCLUDE ([COLUMN1], [COLUMN2], [COLUMN3], [PARTITION_COL])
GO
CREATE NONCLUSTERED INDEX [IDX_TEST]
ON [dbo].[AQUA_DATA_TEST]([COLUMN1])
INCLUDE ([PARTITION_COL])
GO

Running the same script in SQL Server and scripting from there works fine, see below.


IF NOT EXISTS (select 'exists' from sys.partition_functions where name = 'PARTFUNC_AQUA_DATA_TEST')
CREATE PARTITION FUNCTION [PARTFUNC_AQUA_DATA_TEST] (varchar(50)) AS RANGE LEFT FOR VALUES (N'[ALL]')
GO
IF NOT EXISTS (select 'exists' from sys.partition_schemes where name = 'PARTSCHEME_AQUA_DATA_TEST')
CREATE PARTITION SCHEME [PARTSCHEME_AQUA_DATA_TEST] AS PARTITION [PARTFUNC_AQUA_DATA_TEST] TO ([PRIMARY],[PRIMARY])
GO
CREATE TABLE [dbo].[AQUA_DATA_TEST] (
[COLUMN1] int NOT NULL,
[COLUMN2] int NOT NULL,
[COLUMN3] int NOT NULL,
[COLUMN4] int NOT NULL,
[PARTITION_COL] varchar(50) NOT NULL,
CONSTRAINT [PK_AQUA_DATA_TEST_CLUS] PRIMARY KEY CLUSTERED([PARTITION_COL])
ON [PARTSCHEME_AQUA_DATA_TEST] ([PARTITION_COL])
)
ON [PARTSCHEME_AQUA_DATA_TEST] ([PARTITION_COL])
WITH (
DATA_COMPRESSION = NONE
)
GO
IF NOT EXISTS (select 'exists' from sys.partition_functions where name = 'PARTFUNC_AQUA_DATA_TEST')
CREATE PARTITION FUNCTION [PARTFUNC_AQUA_DATA_TEST] (varchar(50)) AS RANGE LEFT FOR VALUES (N'[ALL]')
GO
IF NOT EXISTS (select 'exists' from sys.partition_schemes where name = 'PARTSCHEME_AQUA_DATA_TEST')
CREATE PARTITION SCHEME [PARTSCHEME_AQUA_DATA_TEST] AS PARTITION [PARTFUNC_AQUA_DATA_TEST] TO ([PRIMARY],[PRIMARY])
GO
CREATE NONCLUSTERED INDEX [IDX_TEST_COMPRESSION]
ON [dbo].[AQUA_DATA_TEST]([COLUMN1])
WITH (
DATA_COMPRESSION = PAGE
)
ON [PARTSCHEME_AQUA_DATA_TEST] (PARTITION_COL)
GO
IF NOT EXISTS (select 'exists' from sys.partition_functions where name = 'PARTFUNC_AQUA_DATA_TEST')
CREATE PARTITION FUNCTION [PARTFUNC_AQUA_DATA_TEST] (varchar(50)) AS RANGE LEFT FOR VALUES (N'[ALL]')
GO
IF NOT EXISTS (select 'exists' from sys.partition_schemes where name = 'PARTSCHEME_AQUA_DATA_TEST')
CREATE PARTITION SCHEME [PARTSCHEME_AQUA_DATA_TEST] AS PARTITION [PARTFUNC_AQUA_DATA_TEST] TO ([PRIMARY],[PRIMARY])
GO
CREATE NONCLUSTERED INDEX [IDX_TEST]
ON [dbo].[AQUA_DATA_TEST]([COLUMN1])
WITH (
DATA_COMPRESSION = NONE
)
ON [PARTSCHEME_AQUA_DATA_TEST] (PARTITION_COL)
GO
IF NOT EXISTS (select 'exists' from sys.partition_functions where name = 'PARTFUNC_AQUA_DATA_TEST')
CREATE PARTITION FUNCTION [PARTFUNC_AQUA_DATA_TEST] (varchar(50)) AS RANGE LEFT FOR VALUES (N'[ALL]')
GO
IF NOT EXISTS (select 'exists' from sys.partition_schemes where name = 'PARTSCHEME_AQUA_DATA_TEST')
CREATE PARTITION SCHEME [PARTSCHEME_AQUA_DATA_TEST] AS PARTITION [PARTFUNC_AQUA_DATA_TEST] TO ([PRIMARY],[PRIMARY])
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX [CSI_TEST]
ON [dbo].[AQUA_DATA_TEST]([COLUMN1], [COLUMN2], [COLUMN3], [PARTITION_COL])
WITH (
DATA_COMPRESSION = COLUMNSTORE
)
ON [PARTSCHEME_AQUA_DATA_TEST] (PARTITION_COL)
GO

Product: Aqua Data Studio
Version: 20.0.0
Build #: 56860
Build Date: 2019-May-10 12:12:58 PM

Operating Environment: Linux (5.0.0-27-generic, amd64) / UTF-8 / en / US / Oracle Corporation 1.8.0_171-b11
Memory: Max=4,153,409,536; Total=963,117,056; Free=600,051,064; CPUs=12

In-Window Graphics Capabilities
Graphics Vendor: X.Org
OpenGL Renderer: AMD Radeon (TM) Pro WX Series (POLARIS11, DRM 3.27.0, 5.0.0-27-generic, LLVM 8.0.0)
OpenGL Version: 4.5 (Compatibility Profile) Mesa 19.0.8
Double-Buffering: Enabled
Anti-Aliasing: Enabled
Anti-Aliasing Sample Count: 8
Hardware Acceleration: Enabled
Color Bits: Red: 8 Green: 8 Blue: 8 Alpha: 8
Depth Bits: 16
Accumulation Buffer Bits: Red: 0 Green: 0 Blue: 0
Initialization Time: 661 ms


Offscreen Graphics Capabilities
Graphics Vendor: Brian Paul
OpenGL Renderer: Mesa OffScreen
OpenGL Version: 2.1 Mesa 7.8.2
Double-Buffering: Disabled
Anti-Aliasing: Disabled
Anti-Aliasing Sample Count: 0
Hardware Acceleration: Disabled
Color Bits: Red: 8 Green: 8 Blue: 8 Alpha: 8
Depth Bits: 16
Accumulation Buffer Bits: Red: 16 Green: 16 Blue: 16
Initialization Time: 67 ms
Hardware PBuffer Available: No
Using PBuffer: No
Using Ram Buffer: Yes
Offscreen Rendering: Enabled
Offscreen Buffer Size: 800x600

 

Issue #15736

New
 
 
Completion
No due date
No fixed build
No time estimate

About AquaClusters Privacy Policy Support Version - 19.0.2-4 AquaFold, Inc Copyright © 2007-2017