Database Designer

Database Designer in dbForge Studio for SQL Server provides a visual interface for creating, analyzing, and maintaining database structures. You can use it to design database objects, define relationships, and manage keys and constraints.

Open Database Designer

To open the Designer, on the menu bar, select Database > New Database.

New Database option

You can also open the Designer in one of these ways:

  • In Database Explorer, right-click the connection and select New Database.
  • In Database Explorer, right-click the database and select New Database or Edit Database.

The Designer opens, displaying:

  • Tabs – Provides access to the following tabs: Files, Filegroups, Options, and T-SQL.
  • Header panel – Lets you specify the database name, owner, and description.
  • T-SQL Editor – Provides a feature-rich environment for writing, editing, validating, formatting, and executing T-SQL scripts.
  • Bottom panel – Lets you refresh the object, apply changes, or generate a script for review before execution.

Database designer

Header panel

You can use the header panel to specify the basic database details:

  • Name (required) – Specifies a database name. The default database name is database1. If a database with this name already exists, dbForge Studio assigns the next available name, such as database2, database3, and so on. To use a different name, enter it in this box.
  • Owner (required) – Specifies the database owner. You can select the owner from the list. If you leave Default, the username you used to connect to the server is applied. To refresh the list of owners, click Refresh icon or press Ctrl+R.
  • Description (optional) – Specifies a description for the database. To add a description, enter it in the box. To save the changes, click OK. To discard the changes, click Cancel.

Note

If you’re editing an existing database, you can’t update the database name in the header panel. Instead, you can change the database name from the Database Explorer > Rename shortcut menu.

Files tab

On the Files tab, you can configure database file properties in the database files grid, including the logical file name, file type, filegroup, initial size (MB), autogrowth settings, maximum size, full file path, and file name.

Files tab

Configure database files

By default, the database files grid displays two files that inherit the database name in their names:

  • database_name.mdf (Master Database File) – The primary data file of a SQL Server database that contains the schema (tables, views, stored procedures) and actual data stored in the database.
  • database_name_log.ldf (Log Database File) – The transaction log file that records all transactions and database modifications. It is used for data recovery and rollback operations.

A database can also have one or more secondary data files:

  • database_name.ndf (Secondary Database File) – An optional secondary data file that stores data and database objects that do not fit in the primary data file.

Note

Each SQL Server database must have at least one .mdf file and one .ldf file.

To add a file to a database, click Add file above the grid. The file appears in the grid.

To delete the selected file from the grid, click Remove above the grid.

In the grid, you can configure the following details:

  • Logical name – Specifies the logical file name.
  • File type – Specifies the file type for a new file. To add the file type, select it from the list.

Note

You can’t change the file types or create filegroups for the default .mdf and .ldf files. When you add a file, you can select Filestream from the File type column. Filestream lets you store and manage unstructured data, such as images, videos, and documents, directly in the file system while maintaining database consistency.

  • Filegroup – Specifies a filegroup for the file. Select the filegroup from the list. To create a new group, in the Filegroup column, click Add icon. You can’t create filegroups for default files.

  • Initial Size (MB) – Specifies the initial size of the file. To set the maximum size, use the up and down arrows in the column.

  • AutoGrowth/MaxSize – Specifies the maximum size and growth rate for the file. You can change the maximum size for the file.

  • Path – Specifies the full path to the file. The default path is
    C:\Program Files\Microsoft SQL Server\server_version\MSSQL\DATA\.

    To change the path, click the ellipsis (…) and select the folder to store the file, then click OK.

  • File name – Specifies the file name.

Filter files by columns

In the grid, you can filter files by columns.

To filter files, hover over the column header and click the filter icon in the upper-right corner of the column.

Database Editor - Filter files

Logical name column

On the Values tab, select the checkboxes next to the logical names you want to display.

Alternatively, select a condition from the list and enter a value on the Text Filters tab.

Filter files - Logical Name

File type column

On the Values tab, select the checkboxes next to the file types you want to show.

Filter files - File Type

Filegroup column

On the Values tab, select the checkboxes next to the filegroups you want to show.

Alternatively, select a condition from the list and specify a value on the Text Filters tab.

Filter files - Filegroup

Initial size (MB) column

On the Values tab, use the slider to set the minimum and maximum values or enter them manually in From and To.

Alternatively, select a condition from the list and specify a value on the Numeric Filters tab.

Filter files - Initial size (MB)

AutoGrowth/MaxSize column

On the Values tab, select the checkboxes next to the sizes you want to show.

Alternatively, select a condition from the list and specify a value on the Text Filters tab.

Filter files - AutoGrowth

Path column

On the Values tab, select the checkboxes next to the path you want to show.

Alternatively, select a condition from the list and specify a value on the Text Filters tab.

Filter files - Path

File name column

On the Values tab, select the checkboxes next to the file names you want to show.

Alternatively, select a condition from the list and specify a value on the Text Filters tab.

Filter files - File name

Search for items

Some filters support searching. In the filter dialog, enter a search string in the search box. The grid displays only the items that match the search criteria, and the matching text is highlighted in yellow.

Filter files - Search

To delete the text, in the search box, click Clear Filter.

To remove the filter, in the filter dialog, click Clear Filter.

You can add, edit, or delete filters using the Filter panel or the Filter Editor dialog.

Add a new filegroup

1. In the file list, select the filegroup where you want to add a new filegroup, and then click Add a filegroup next to it.

2. In the New Filegroup dialog that opens, configure the following settings:

  • Name – Enter a name for the filegroup.

  • Read-Only – Select to make the filegroup read-only, preventing data modifications to any files within the filegroup.

  • Default – Select to set the filegroup as the default.

3. Click OK. To discard the changes, click Cancel.

Use the filter panel

The filter panel appears at the bottom of the database files grid after you apply a filter. You can use it to:

  • Clear the checkbox to turn off the filter temporarily.
  • Click Delete icon to remove the filter.

Filter files - Filter panel

Add a condition or a group

1. In Filter Editor, hover over the root operator (for example, And) and click Add a condition next to it.

2. Choose the required option: Add Condition or Add Group. Each filter condition consists of a logical operator, column selector, an operator selector, and a value field.

Filter Editor components

3. Specify a filtering condition:

  • Click the logical operator placeholder, then select the operator:
    And – Displays the records when both conditions are True;
    Or – Displays the records when at least one of the conditions is True;
    Not And/Not Or – Reverses the logic of the specified conditions.

  • Click the column selector placeholder and select the column by which you want to filter the data.

  • Click the operator selector placeholder and select the operator that defines how the filter value is matched against the column.

  • Click the value field and enter a value by which you want to filter the data.

4. Click Apply. To discard the changes, click Cancel. To apply the filter and close the Filter Editor dialog, click OK.

Remove a condition or a group

In Filter Editor, hover over any filtering condition and click Remove a condition next to the condition or group you want to remove.

Change the maximum size

You can use the Change Autogrowth dialog to configure automatic growth settings for a database file when the file runs out of available space.

To change the maximum size:

1. In the AutoGrowth/MaxSize column, click the ellipsis (…) for the required file.

2. In the Change Autogrowth dialog that opens, configure the following settings:

  • Enable Autogrowth – Select the checkbox to allow SQL Server to automatically increase the file size when additional space is needed. Clear the checkbox to turn off automatic file growth (the file doesn’t expand automatically and might cause errors when it reaches its size limit).

  • In Percent – Select to increase the file size by a specified percentage of its current size. The default value is 10.

  • In Megabytes – Select to increase the file size by a fixed amount in megabytes. The default value is 1.

  • Limited to (MB) – Select to restrict the file to a specified maximum size in megabytes. The default value is 10.

  • Unlimited – Select to allow unlimited file growth, subject only to available disk space.

3. Click OK. To discard the changes, click Cancel.

Change the path to a database file

1. Click the ellipsis (…) for the path you want to change.

2. In the Browse For Folder dialog that opens, select the folder where you want to store the database file.

Change the path to the database file

Browse the folder tree to navigate to the desired location.

The Selected path field displays the full path to the selected folder.

3. Click OK. To discard the changes, click Cancel.

Filegroups tab

You can use the Filegroups tab to add filegroups, filestream groups, and memory-optimized filegroups, or remove them.

The Filegroups tab includes the grid where you can collapse or expand filegroups to view the files they contain.

  • Add Filegroup – Adds a new standard filegroup to the database for storing regular data files.
  • Add Filestream Filegroup – Adds a FILESTREAM filegroup for storing unstructured data, such as documents, images, and other binary files, on the file system.
  • Add Memory-Optimized Filegroup – Adds a memory-optimized filegroup for use with In-Memory OLTP tables.
  • Remove – Removes the selected filegroup from the database.
  • Name – Specifies the name of the filegroup.
  • Files – Displays the number of files in the filegroup.
  • Default – Indicates whether the filegroup is used as the default location for creating new database objects. The checkbox appears in the Default column if the filegroup is set as default.
  • Read-Only – Specifies whether the filegroup is read-only. The checkbox appears in the Read-Only column if the filegroup is read-only.

Change the maximum size

Options tab

On the Options tab, you can customize the default behavior of databases by including or excluding specific settings.

To include the setting, select the checkbox next to it. To exclude the setting, clear the corresponding checkbox. The description of the selected setting appears below the grid.

Options tab

View database options

The following table describes database settings available in the grid.

Name Group Default State Description
Collation General Server Default Specifies the default collation for the database.
Recovery model General Full Controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.
Compatibility level General Server Default Sets certain database behaviors to be compatible with the specified version of SQL Server.
Containment type General None Specifies the database containment status.
Database Read-Only State Turned off Controls whether updates are allowed on the database.
Restrict Access State MULTI_USER Controls which users may access the database. When SINGLE_USER is specified, only one user can access the database at a time. When RESTRICTED_USER is specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database. When MULTI_USER is specified, it returns the database to its normal operating state.
Auto Close Automatic Turned off Shuts down the database cleanly and frees its resources after the last user exits.
Auto Create Statistics Automatic Turned on Specifies that the query optimizer creates statistics on single columns in query predicates, as necessary, to improve query plans and query performance.
Auto Shrink Automatic Turned off Specifies that the database files are candidates for periodic shrinking.
Auto Update Statistics Automatic Turned on Specifies that the query optimizer updates statistics when they are used by a query and when they might be out-of-date.
Auto Update Statistics Asynchronously Automatic Turned off Specifies that statistics updates for the AUTO_UPDATE_STATISTICS option are asynchronous.
Close Cursor on Commit Enabled Cursor Turned off Specifies that any cursors that are open when a transaction is committed or rolled back are closed.
Use LOCAL Cursor Cursor Turned off Specifies that the scope of the cursor is local to the batch, stored procedure, or trigger where the cursor was created.
Filestream Directory Name Filestream   Specifies the directory name for the FILESTREAM data associated with the selected database.
Non-Transacted Access Filestream OFF Determines non-transactional access to files stored in SQL Server. The available options are OFF, READ_ONLY, and FULL.
Page Verify Recovery CHECKSUM Discovers damaged database pages caused by disk I/O path errors.
Broker Enabled Service Broker Turned off Specifies that Service Broker is enabled for the specified database.
Change Tracking Change Tracking Turned off Indicates whether change tracking is enabled for the database.
Allow Snapshot Isolation Miscellaneous Turned off Specifies that snapshot isolation is allowed.
ANSI NULL Default Miscellaneous Turned off Determines the default value, NULL or NOT NULL, of a column or CLR user-defined type for which the nullability is not explicitly defined in the CREATE TABLE or ALTER TABLE statements.
ANSI NULLS Enabled Miscellaneous Turned off Determines that all comparisons to a NULL value evaluate to UNKNOWN.
ANSI Padding Enabled Miscellaneous Turned off Determines that strings are padded to the same length before conversion or inserting to a varchar or nvarchar data type.
ANSI Warnings Enabled Miscellaneous Turned off Specifies that errors or warnings are issued when conditions, such as divide-by-zero, occur or NULL values appear in aggregate functions.
Arithmetic Abort Enabled Miscellaneous Turned off Specifies that a query is ended when an overflow or divide-by-zero error occurs during query execution.
Concatenate Null Yields Null Miscellaneous Turned off Specifies that the result of a concatenation operation is NULL when either operand is NULL.
Date Correlation Optimization Enabled Miscellaneous Turned off Specifies that SQL Server maintains correlation statistics between two tables in the database that are linked by a FOREIGN KEY constraint and have datetime columns.
Is ReadCommitted Snapshot On Miscellaneous Turned off Enables the Read-Committed Snapshot option at the database level. When it is turned on, DML statements start generating row versions even when no transaction uses Snapshot Isolation.
Numeric Round-Abort Miscellaneous Turned off Generates an error when an expression loses precision.
Forced parametrization Miscellaneous Turned off Specifies that SQL Server parameterizes all queries in the database.
Quoted Identifiers Enabled Miscellaneous Turned off Specifies that double quotation marks can be used to enclose delimited identifiers.
Recursive Triggers Enabled Miscellaneous Turned off Specifies whether recursive firing of AFTER triggers is allowed.
Legacy Cardinality Estimation Database Scoped Configurations OFF Indicates Legacy Cardinality Estimation.
Legacy Cardinality Estimation For Secondary Database Scoped Configurations PRIMARY Indicates Legacy Cardinality Estimation For Secondary.
Max DOP Database Scoped Configurations 0 Indicates Max DOP (degree of parallelism).
Max DOP For Secondary Database Scoped Configurations   Indicates Max DOP (degree of parallelism) For Secondary.
Parameter Sniffing Database Scoped Configurations ON Indicates Parameter Sniffing.
Parameter Sniffing For Secondary Database Scoped Configurations PRIMARY Indicates Parameter Sniffing For Secondary.
Query Optimizer Fixes Database Scoped Configurations OFF Indicates Query Optimizer Fixes.
Query Optimizer Fixes For Secondary Database Scoped Configurations PRIMARY Indicates Query Optimizer Fixes For Secondary.
Operation Mode (Requested) Query Store OFF Indicates the requested query store operation mode.

Search for options

On the Options tab, you can search for specific options by entering the search text or symbol, such as an underscore, in the search box.

To search for options, enter a search query in the search box.

Options tab - Search

Search behavior:

  • Matching text is highlighted in yellow.
  • The number of matches appears in the search box.
  • The search is case-insensitive. For example, searching for Tab matches Tab and tab.
  • If the query contains multiple words, each word is matched separately, and all matches are highlighted.

To clear the search results, click Clear filter in the search box.

Keyboard shortcuts in the search box

The table provides keyboard shortcuts you can use in the search box.

Keyboard shortcut Description
Ctrl+A Selects all.
Ctrl+Left arrow key Moves the caret to the beginning of the word.
Ctrl+Right arrow key Moves the caret to the end of the word.
Ctrl+Shift+Left arrow key Selects text from the current caret position to the beginning of the current word and moves the caret to the beginning of the word.
Ctrl+Shift+Right arrow key Selects text from the current caret position to the end of the current word and moves the caret to the end of the word.
Ctrl+Backspace Deletes the word to the left of the caret.

T-SQL tab

On the T-SQL tab, you can view the CREATE DATABASE and ALTER DATABASE scripts, which automatically update based on the properties you configure in the visual designer. To work with the SQL text, right-click anywhere on the T-SQL tab and select the required option, such as code refactoring or AI Assistant.

T-SQL tab

Bottom panel

You can use the bottom panel to:

  • Refresh Object (Shift+Alt+R) – Reloads an object state from a database.
  • Apply Changes (Shift+Alt+A) – Runs a SQL script and saves changes to a database.

  • Script Changes – Generates the script in a new SQL document that includes all your changes without applying them to the database.

    Click the arrow next to Script Changes and select:

    • To New SQL Window (Shift+Alt+C) – Opens the script in a new SQL document.
    • To Clipboard – Copies the script to the clipboard.

    Script Changes options