What are the challenges you face when working across database platforms? Take the survey
Options

create database

edited February 24, 2017 8:21AM in SQL Prompt
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

Comments

  • Options
    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
  • Options
    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
    
  • Options
    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.
    Michael Clark
    Technical Lead, SQL Monitor
  • Options
    James RJames R Posts: 104 Silver 4
    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
    Software Engineer
    Redgate Software
  • Options
    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!
Sign In or Register to comment.