create database

torsten.strauss

HI!

Formatting the following statement will cause some issues:

:SETVAR DataPath "C:Program FilesMicrosoft SQL ServerMSSQL13.MYSQLSERVER2016MSSQLDATA"
:SETVAR LogPath "C:Program FilesMicrosoft SQL ServerMSSQL13.MYSQLSERVER2016MSSQLDATA"
CREATE DATABASE MemoryOptimized
ON PRIMARY
--disk based objects
	(
	NAME = MemoryOptimized_data
,	FILENAME = '$(DataPath)MemoryOptimized_data.mdf'
,	SIZE = 256MB
	)
--in memory objects
,	FILEGROUP MemoryOptimized CONTAINS MEMORY_OPTIMIZED_DATA
	(
	NAME = MemoryOptimized_folder
,	FILENAME = '$(DataPath)MemoryOptimized_folder'
	)
--disk based objects
LOG ON
	(
	NAME = MemoryOptimized_log
,	FILENAME = '$(LogPath)MemoryOptimized_log.ldf'
,	SIZE = 128MB
	);
ALTER DATABASE MemoryOptimized SET RECOVERY FULL;
GO

This above statement is formatted manually.
SQL prompt will do it like this:

:SETVAR DataPath "C:Program FilesMicrosoft SQL ServerMSSQL13.MYSQLSERVER2016MSSQLDATA"
:SETVAR LogPath "C:Program FilesMicrosoft SQL ServerMSSQL13.MYSQLSERVER2016MSSQLDATA"
CREATE DATABASE MemoryOptimized
ON PRIMARY
--REDGATE issue
	--disk based objects
	(
	NAME = MemoryOptimized_data
,	FILENAME = '$(DataPath)MemoryOptimized_data.mdf'
,	SIZE = 256MB
	)
--in memory objects
--REDGATE issue
, FILEGROUP MemoryOptimized CONTAINS MEMORY_OPTIMIZED_DATA
	(
	NAME = MemoryOptimized_folder
--REDGATE issue
	, FILENAME = '$(DataPath)MemoryOptimized_folder'
	)
--disk based objects
LOG ON
--REDGATE issue
(
	NAME = MemoryOptimized_log
,	FILENAME = '$(LogPath)MemoryOptimized_log.ldf'
,	SIZE = 128MB
--REDGATE issue
	);
GO

It would be nice to get this sorted - thanks!

Torsten

Harry Frankish

Hi Torsten

Thanks for reporting these issues. It looks like our list and parentheses options aren't working properly.

We'll try and get this sorted for you!

Cheers
Harry

torsten.strauss

Thanks Harry,

another example of strange formatting can be observed using this statement.
Just try to format this and I am pretty sure you will see the issues.

-- create a database for demonstration purposes
USE master;
GO

IF DB_ID('InternalStorageFormat') IS NOT NULL
	BEGIN
		ALTER DATABASE InternalStorageFormat SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE InternalStorageFormat;
	END;
GO

CREATE DATABASE InternalStorageFormat
ON PRIMARY
	(
	NAME = N'InternalStorageFormat'
,	FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MYSQLSERVER2016MSSQLDATAInternalStorageFormat.mdf'
,	SIZE = 50MB
,	FILEGROWTH = 0
	)
,FILEGROUP SECONDARY
	(
	NAME = N'InternalStorageFormat_02'
,	FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MYSQLSERVER2016MSSQLDATAInternalStorageFormat_02.ndf'
,	SIZE = 50MB
,	FILEGROWTH = 0
	)
,
	(
	NAME = N'InternalStorageFormat_03'
,	FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MYSQLSERVER2016MSSQLDATAInternalStorageFormat_03.ndf'
,	SIZE = 50MB
,	FILEGROWTH = 0
	)
LOG ON
(
	NAME = N'InternalStorageFormat_log'
,	FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL13.MYSQLSERVER2016MSSQLDATAInternalStorageFormat_log.ldf'
,	SIZE = 25MB
,	FILEGROWTH = 0
	);
GO

Michael Clark

Thanks for reporting this beta formatting issue Torsten. We've got this logged as SP-6092 and will get back to you when we have an update.

James R

Hi Torsten,

I've been looking into this issue more, and we’re considering an implementation change related to comma alignment inside indented parenthesis.
We think it makes more sense that when you align commas 'To Statement', the 'Statement' refers to the containing parenthesis, instead of the overall statement.

To use your example, this would be the formatted code :

CREATE DATABASE MemoryOptimized
ON PRIMARY
    (
    NAME = MemoryOptimized_data
    , FILENAME = '$(DataPath)MemoryOptimized_data.mdf'
    , SIZE = 256MB
    )
,   FILEGROUP MemoryOptimized CONTAINS MEMORY_OPTIMIZED_DATA
    (
    NAME = MemoryOptimized_folder
    , FILENAME = '$(DataPath)MemoryOptimized_folder'
    )
LOG ON
    (
    NAME = MemoryOptimized_log
    , FILENAME = '$(LogPath)MemoryOptimized_log.ldf'
    , SIZE = 128MB
    );
ALTER DATABASE MemoryOptimized
SET RECOVERY FULL;
GO

We would be interested in your opinion of this, compared to your original example.

Any other suggestions would be greatly appreciated!

Thanks,
James

torsten.strauss

Hi James,

good suggestion but it would be nice to align the first line with the second one if there is more than one line.

    CREATE DATABASE MemoryOptimized
    ON PRIMARY
        (
          NAME = MemoryOptimized_data
        , FILENAME = '$(DataPath)MemoryOptimized_data.mdf'
        , SIZE = 256MB
        )
    ,   FILEGROUP MemoryOptimized CONTAINS MEMORY_OPTIMIZED_DATA
        (
          NAME = MemoryOptimized_folder
        , FILENAME = '$(DataPath)MemoryOptimized_folder'
        )
    LOG ON
        (
          NAME = MemoryOptimized_log
        , FILENAME = '$(LogPath)MemoryOptimized_log.ldf'
        , SIZE = 128MB
        );
    ALTER DATABASE MemoryOptimized
    SET RECOVERY FULL;
    GO

This is the same problem with the value formatting:

INSERT Person.Address
	(
	AddressLine1
,	AddressLine2
	)
VALUES
	(
-- not aligned
	N'' -- AddressLine1 - nvarchar(60)
	, N'' -- AddressLine2 - nvarchar(60)
	);

What do you think?

Thanks for looking into this!