Filegroup / Error

torsten.strausstorsten.strauss Posts: 271 Silver 3
in SQL Prompt
Hi !

I have a database with two file groups. One is defined as default the other is named index.

-- !Change the path according to your server configuration
-- !Consider to send the index file and log files to another physical volumne
:SETVAR DataPath "E:\Microsoft SQL Server\MSSQL14.MYSQLSERVER2017A\MSSQL\DATA\"
:SETVAR LogPath "F:\Microsoft SQL Server\MSSQL14.MYSQLSERVER2017A\MSSQL\LOG\"

-- !Consider to change the size and filegrowth for the data and log files
CREATE DATABASE MDS_Master
ON PRIMARY
   (
   NAME = N'MDS_Master'
, FILENAME = N'$(DataPath)\MDS_Master.mdf'
, SIZE = 256MB
, FILEGROWTH = 0
   )
, FILEGROUP Data
  (
  NAME = N'MDS_Master_Data'
, FILENAME = N'$(DataPath)\MDS_Master_Data.ndf'
, SIZE = 2GB
, FILEGROWTH = 0
  )
, FILEGROUP [Index]
  (
  NAME = N'MDS_Master_Index'
, FILENAME = N'$(DataPath)\MDS_Master_Index.ndf'
, SIZE = 3GB
, FILEGROWTH = 0
  )
LOG ON
(
NAME = N'MDS_Master_Index_Log'
  , FILENAME = N'$(LogPath)\MDS_Master_Index_Log.ldf'
  , SIZE = 512MB
  , FILEGROWTH = 0
);
GO

-- Set data filegroup as default
ALTER DATABASE MDS_Master MODIFY FILEGROUP Data DEFAULT;
GO


If you now create an index or primary key constraint SQL Prompt will suggest the Default and the Index Filegroup which is fine.
But if you select the Default one it is not surrounded by brackets [], therefore parsing fails.
Would be nice to see a fix - thanks!

ALTER TABLE mdm.Import_Status
ADD CONSTRAINT PKCL_Import_Status_id_import_status
PRIMARY KEY CLUSTERED (id_import_status)
WITH (FILLFACTOR = 100) ON DEFAULT;  -- missing brackets
GO

Torsten
Tagged:
· Share on Twitter

Answers

Sign In or Register to comment.