How to add tailored Indexing for Microsoft PDW

Toad Data Modeler
1

Hi,

We use Microsoft PDW, which is supported by toad and we want to be able to specify our own indexing which might be something like;

WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([SurrogateKey]), PARTITION ([SourceID] RANGE LEFT FOR VALUES ('XXX, YYY)));

or

WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = REPLICATE);

as the 2 most likely scenarios.

How might I do this?

I was initially thinking extended properties but they don't seem to provide enough flexibility. Any ideas?

Regards

Sandy

2

Hi Sandy,

SQL Server Parallel Data Warehouse is not supported by Toad Data Modeler.

Regards,

Mario

3

Hi Mario,

Yes I understood that it wasn't. What I was doing was using the SQL Server 2017 and working off that. So my question was based on is there a way of tailoring the indexing to suit our purpose in anyway, perhaps using extended properties?

4

Hi Sandy,

Extended properties is feature in SQL Server, it can't be used for your problem.

You can use customization feature in TDM. Some customization example is in link: Entity custom properties

I prepared example of user package for you. Download file UserPackageMS17.txg. It is user package, which will add "More Properties" tab to Entity Properties form, where you can write some text which will be generated on the end of the CREATE TABLE command.

How to install user package:

  • Run TDM and switch on expert mode (Main Menu > Settings > Options > General > Expert Mode checkbox)
  • In Main Menu should be appeared new menu item "Expert Mode".
  • Go to "Main Menu > Expert Mode > Customization > Import Custom Package" and find and import user package “UserPackageMS17.txg”.
  • After this restart TDM.

In fact this process only copy package to location defined in TDM. By Default it is “C:\Users\USERNAMEXY\Documents\Toad Data Modeler\Standard Installation\Packages{DCB5CB9B-CF65-4350-86B5-285D246FC5AC}”.

Now you can run TDM, open or create SQL Server 2017 model, add some text to "More Properties" tab on Entity Properties form (e.g. WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = REPLICATE)) and run "Generate DDL Script", where this text will be generated on the end of the CREATE TABLE command.

You can edit script in linked user package which change generated code. In Script Explorer (Main Menu > Expert Mode > Customization > Script Explorer) you can find script "PERCodeGeneratorMS17_user" in path "Generation". Doubleclick on this script to open it, then you can edit code and save it by click on "Save" button.

For uninstall simply delete this user package in Documents path.

Regards,

Mario