azuredatastudio
azuredatastudio copied to clipboard
microsoft
Add ability to specify filegroups to Tables
Is your feature request related to a problem? Please describe. Data files can be grouped together in filegroups for allocation and administration purposes. Datafiles can contain Tables, indexes, stored procedures, views. A user may need to allocate a Table (Data file) to a specific file group and this functionality currently does not exist.
Describe the solution or feature you'd like Provide user ability to specify file groups for their Tables.
Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.
Additional context See document https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver16
@erinstellato-ms can provide any additional context I may be missing here.
Clarification: objects (tables, indexes) exist in a filegroup. If not specifically assigned during object creation, they will exist in the DEFAULT filegroup (usually PRIMARY). For folks that create additional filegroups (which have one or more data files within them), they can specify the filegroup during object creation. Within table designer, there is no option to specify a filegroup (sample syntax below, ON PRIMARY is FG specification).
CREATE TABLE dbo.TableName ( col1 INT NOT NULL ) ON PRIMARY;
For tables with NVARCHAR(MAX), the TEXTIMAGE_ON is also an option:
CREATE TABLE [dbo].[Orders]( [OrderID] [int] NOT NULL, [CustomerID] [int] NOT NULL, [SalespersonPersonID] [int] NOT NULL, [PickedByPersonID] [int] NULL, [ContactPersonID] [int] NOT NULL, [BackorderOrderID] [int] NULL, [OrderDate] [date] NOT NULL, [ExpectedDeliveryDate] [date] NOT NULL, [CustomerPurchaseOrderNumber] nvarchar NULL, [IsUndersupplyBackordered] [bit] NOT NULL, [Comments] nvarchar NULL, [DeliveryInstructions] nvarchar NULL, [InternalComments] nvarchar NULL, [PickingCompletedWhen] datetime2 NULL, [LastEditedBy] [int] NOT NULL, [LastEditedWhen] datetime2 NOT NULL, CONSTRAINT [PK_Sales_Orders] PRIMARY KEY CLUSTERED ( [OrderID] ASC ) WITH (ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
Ideally, FILEGROUP is also an option for INDEX creation.
Is there an ETA for filegroup specification in ADS Server - database - New Table ( and index ) ?
@ALZDBA We don't have an ETA at this time, addressing some other issues we determined were a higher priority. While many folks do use non-default FGs, there is a vast majority that uses the default of PRIMARY. As a workaround, the CREATE TABLE statement can be scripted and then users can manually change the filegroup from PRIMARY in the script.
However, if you can get folks who also find value in this request to upvote the item, that would help us understand importance.
Will this be available in ADS v2023-03 ? ( I fear it missing stuff modified in the script , but not available in the UI, rendering another result as I would expect )
Will this be available in ADS v2023-03 ? ( I fear it missing stuff modified in the script , but not available in the UI, rendering another result as I would expect )
@ALZDBA Can you clarify what you mean here? Specifically, what is it "it"? Table designer?
Clarifying/confirming: the request is to add this functionality to the Table Designer UX.