Alignment incorrect in CREATE TABLE statement

TheRightAmount

I've set up a formatting style in which I've asked for data types and constraints to be aligned in my DML statements. However, I'm finding that SQL Prompt doesn't always get this right. It seems to be something to do with tab stops or alignments lining up with the end of the longest column name in the table - that's when things don't quite work. It's like there's a "+1" missing in whatever works out what space needs to be left after a column name.

I've attached an example but it's not actually a very good one. I'm doing a lot of reformatting at the moment so if I hit a better one I'll add that.

TheRightAmount

This is maybe a better example. Those are spaces rather than tabs after the AddressLine columns. There are tabs after all the other column names. To sort out the formatting I have to delete those spaces, replace them with tabs, and add an extra tab in front of all the other datatypes. If those AddressLine columns had had shorter datatypes I'd also have had to fix the tabs before the NULLs as well.

FabiolaB

Hi @TheRightAmount,

Thank you for reporting this issue. Which SQL Prompt version are you using? Unfortunately, we are unable to reproduce it with the latest version of SQL Prompt.

We have used the Default style settings + "Align data types and constraints" checkbox checked.

CREATE TABLE Persons
(
    Code                                                                   CHAR(6)       NOT NULL,
    CountryCode                                                            CHAR(3)       NULL,
    Name                                                                   NVARCHAR(255) NULL,
    AddressLine1                                                           NVARCHAR(255) NULL,
    AddressLine2                                                           NVARCHAR(255) NULL,
    AddressLine3                                                           NVARCHAR(255) NULL,
    AddressLineAddressAddressLineAddressAddressLineAddressLineAddressLine3 NVARCHAR(255) NULL,
    City                                                                   NVARCHAR(50)  NULL,
);

Best wishes,
Fabiola

TheRightAmount

Hi Fabiola

I'm on version 8.0.11.2296.

I've just tried the table definition you posted and realised why you might not be seeing my issue. In the Whitespace section I have Tab behaviour set to "Only tabs", and Number of spaces in tabs set to 4. When I reformat the code you've posted above it pushes all of the datatypes to the right by one character, to the next tab stop. (If I switch to "Only spaces" it puts it back the way you have it formatted.)

If I stick with "Only tabs" and delete the last two characters from your long column name, my problem appears, as you can see in the image below. So it appears to happen when the end of that long column name aligns with a tab stop.

Cheers

Matt

Jessica R

Hi @TheRightAmount!

I'm strangely still not able to reproduce this even when using tabs only with 4 spaces, and deleting the last two characters from the long column name.

Can you kindly first try updating to the latest release, v8.2.2.2657, to see if the problem still occurs for you (Help>Check for updates)?

If it does, could you please post here with an attachment of your style file which will hopefully get the problem to occur for us?

Thank you!

TheRightAmount

Hi, yes I updated to the latest release just after my last post. The problem still occurs. Style file attached - I've renamed to .txt because it wouldn't allow me to add it with its original name.

Jessica R

Thanks @TheRightAmount!

I can reproduce this now and it seems that the comma settings can affect it as well.

If you go to Lists>Commas, switch to "Place commas before items", set it to align commas "To list" , and disable "add a space after comma", then all the data types line up. Any other comma setting combination though appears to cause problems with the data type alignment.

I'm going to check back with our developers on this- please bear with us!

Jessica R

Just a quick note that we've logged this bug as SP-6662. We'll post here once we have an update.

TheRightAmount

That's great, thanks.

FabiolaB

Hello @TheRightAmount,

This issue should be fixed in our latest version of SQL Prompt 8.2.5.2924 (installer here).

You can find more information about this release in this forum post.

Kind regards,
Fabiola

TheRightAmount

Looks good so far! Thanks very much for the speedy turnaround.