Formatting of filegroups on indexes

Actually two questions

My current style produces this:

USE tempdb;
GO

RAISERROR('dummy error message', 16, 1)WITH LOG;
GO

CREATE NONCLUSTERED INDEX IXF_dbo_tablename_columnname
ON dbo.tablename (columnname ASC)
WHERE columnname IS NOT NULL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON FG_Filegroupname;
GO

But I want this:

USE tempdb;
GO

RAISERROR('dummy error message', 16, 1) WITH LOG;
GO

CREATE NONCLUSTERED INDEX IXF_dbo_tablename_columnname
ON dbo.tablename (columnname ASC)
WHERE columnname IS NOT NULL
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
ON FG_Filegroupname;
GO

Question 1: How do I get a space character between the closing parenthesis of the RAISEERROR() and the WITH condition?
Question2: How do I get the "ON FG_Filegroupname" onto it's one line (this also seems to happen with tables as well.

Current style sheet is attached.

jmeyer January 23, 2018 19:20

Comments

4 comments

Hi @jmeyer,

Thank you for providing your style sheet. I've created 2 tickets for the 2 issues (ref SP-6807 and SP-6808).
We'll let you know when the issues are fixed.
Regards,
Fabiola

FabiolaB January 26, 2018 10:58

0

Fabiola:
Here's another example of where I think the style sheet doesn't handle indexes well

ALTER TABLE dbo.PHEAA_master_SD
ADD CONSTRAINT UQ_dbo_PHEAA_master_SD_CREATEDATE_BSSN_SEQ_PROGRAM_GUARANTOR_CURROWN_OWNERBOND UNIQUE NONCLUSTERED
    (CREATEDATE ASC, BSSN ASC, SEQ ASC, PROGRAM ASC, GUARANTOR ASC, CURROWN ASC, OWNERBOND ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
                                                                                                    , SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF
                                                                                                    , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
                                                                                                    , FILLFACTOR = 70) ON PS_SLServ_MR50(CREATEDATE);

I would have expected that the Schema (DDL) CREATE/ALTER section also covers for example indexes, that does not seem to be the case. I would haev expected something more like this result:

USE SLServ;
GO

ALTER TABLE dbo.PHEAA_master_SD
ADD CONSTRAINT UQ_dbo_PHEAA_master_SD_CREATEDATE_BSSN_SEQ_PROGRAM_GUARANTOR_CURROWN_OWNERBOND UNIQUE NONCLUSTERED
    (CREATEDATE ASC
    , BSSN ASC
    , SEQ ASC
    , PROGRAM ASC
    , GUARANTOR ASC
    , CURROWN ASC
    , OWNERBOND ASC) 
WITH (PAD_INDEX = OFF
    , STATISTICS_NORECOMPUTE = OFF
    , SORT_IN_TEMPDB = OFF
    , IGNORE_DUP_KEY = OFF
    , ONLINE = OFF
    , ALLOW_ROW_LOCKS = ON
    , ALLOW_PAGE_LOCKS = ON
    , FILLFACTOR = 70) 
ON PS_SLServ_MR50(CREATEDATE);
GO

jmeyer January 26, 2018 14:29

Hello @jmeyer ,

We fixed this issue in our latest version of [b]SQL Prompt <9.1.8.4871>[/b] ([url=http://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_9.1.8.4871.exe]installer here[/url]).

You can find more information about this release in [url=https://forum.red-gate.com/discussion/83138/the-latest-stable-build-of-sql-prompt-is-9-1-8-4871-18th-april]this forum post[/url].

Kind regards,

krzysztofkroczak April 18, 2018 15:18

0

Krysztof:

Looks like a step in the right direction wit the latest update. Wit the previously provided style sheet my index now renders like this:

USE tempdb;
GO

RAISERROR('dummy error message', 16, 1) WITH LOG;
GO

CREATE NONCLUSTERED INDEX IXF_dbo_tablename_columnname
ON dbo.tablename (columnname ASC)
WHERE columnname IS NOT NULL
WITH
 (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON)
ON FG_Filegroupname;
GO

However, my constraint example still looks like this:

ALTER TABLE dbo.PHEAA_master_SD
ADD CONSTRAINT UQ_dbo_PHEAA_master_SD_CREATEDATE_BSSN_SEQ_PROGRAM_GUARANTOR_CURROWN_OWNERBOND UNIQUE NONCLUSTERED
   
 (CREATEDATE ASC, BSSN ASC, SEQ ASC, PROGRAM ASC, GUARANTOR ASC, CURROWN
 ASC, OWNERBOND ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
                                                                                                   
 , SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF
                                                                                                   
 , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
                                                                                                   
 , FILLFACTOR = 70) ON PS_SLServ_MR50(CREATEDATE);

For the Schema (DDL) Parentheses I selected "Compact, simple", looks like the DDL statements do not include indexes/constraints?

jmeyer April 18, 2018 15:45

Add comment

Please sign in to leave a comment.

How can we help you today?

Formatting of filegroups on indexes

Comments

Add comment