How To Create A Database In Sql Server 2017

This article briefly explains creating a new database with the New Database wizard of SQL Server Management Studio (SSMS).

The SQL Server Management Studio is a graphical interface tool for creating, configuring, and administrating SQL Server engines and databases. You can download it from here.

To create a database, connect to the SQL Server instance -> Right-click on Databases -> New Database.

Picture 1. Launching a New Database wizard

A New Database dialog box opens. There, you will see three different sections:

1. General.
2. Options.
3. Filegroups.

The General Screen of New Database dialog box.

In the general screen, you can specify the database name in the respective textbox. The owner textbox serves to specify the database owner. By default, it is the user who created the database.

If you want to add another database file during the database creation process, click Add, and it will include a new row in the database files grid view.

The Database files grid allows you to specify the following database files details:

1. Logical Names of the database files.
2. File Types of the database files. This option helps when you add secondary database files. The valid values are:
   1. ROWS data
   2. Log
   3. FILESTREAM.
3. Filegroups – choose the filegroup to keep the database files. The valid options are:
   1. Primary
   2. Secondary / Name of the filegroup.
4. Initial Size – specify the Initial Size of the database file and log file.
5. Autogrowth / Maxsize – specify the Autogrowth and maximum file size values. To change the value, click on eclipse – it opens a new dialog window:

Picture 2. Autogrowth settings

Here, you can enable or disable the F ile Growth parameter and specify the File Growth value. In the Maximum File Size section, you can limit the data file's size – set the maximum value in the Limited to (MB) field. If you do not want to limit the data file/log file size, select Unlimited.

6. Path – specify the database file's physical location. If you want to save the database files to other than the default location, click on eclipse. A dialog box opens. There, choose the desired folder and click OK.

7. File Name – specify the actual name of the database file.

In the demo, we are going to create the codingsite_demo database:

Picture 3. The General screen

The Options Screen of the New Database dialog box.

On the Options page of the New Database dialog box, you can configure advanced options:

1. Collation. Specify the database collation.
2. Recovery model. Choose the desired database recovery model:
   1. Full
   2. Simple
   3. Bulk-logged.
3. Compatibility level. The SQL server's latest compatibility level is 150 (SQL Server 2019), and the oldest compatibility level is 100 (SQL Server 2008).
4. Containment type. If you want to designate your database as a contained database, choose Partial . The default selection is None .

Other advanced options belong to the following categories:

1. Automatic.
   • Enable or disable the Auto Close parameter.
   • Enable or disable the Auto Create Incremental statistics option.
   • Enable or disable the Auto Create and Auto Update Statistics option.
   • Enable or disable the behavior of updating the outdated statistics during query compilation.
2. Cursor
   • Close the cursor when the transaction is committed. The valid values are True and False.
   • The default behavior of the cursor. The valid values are True and False. When the selected value is True, the default cursor declaration is LOCAL. If the selected value is False, the default is GLOBAL.
3. Database Scoped configuration
   • Enable or disable the Legacy Cardinality Estimator.
   • Enable or disable the Legacy Cardinality Estimator for secondary.
   • Specify the maximum degree of parallelism (Max DOP).
   • Specify the maximum degree of parallelism (Max DOP) for secondary.
   • Enable or disable parameter sniffing.
   • Enable or disable parameter sniffing for secondary.
   • Enable or disable the query optimizer hotfixes.
   • Enable or disable the query optimizer hotfixes for secondary.
4. FILESTREAM
   1. Specify the FILESTREAM directory.
   2. Specify the non-transactional access to the FILSTREAM:
      • None
      • READONLY
      • FULL
5. Recovery.
   1. Choose the page verification method:
      • None
      • CHECKSUM
      • TORN_PAGE_DETECTION
   2. Specify the Target Recovery time. The unit is second.
6. Service broker. Enable or disable the service broker of the SQL Server database.
7. State
   1. Mark the SQL Database as Read-only.
   2. Enable or disable encryption in the database.
   3. Restrict user access to the database:
      • MULTI_USER
      • SINGLE_USER
      • RESTRICTED_USER

Picture 4. The Options screen

Note: you can read this article to get more information about the database options.

The Filegroups Screen of the New Database dialog box.

On the Filegroups screen, you can specify the following filegroups:

1. Secondary database filegroup.
2. Filegroup for FILESTREAM.
3. Filegroup for In-Memory OLTP.

The screen looks like the following image:

Picture 5. The Filegroups screen

Generate Script to Create a database

Once the database is created, you can generate a Create database script.

To do that, click on the Script button on top of the New Database dialog box, and click Script action to the new query window :

Picture 6. Generate the script to create a new database

The generated script is the following:

          CREATE DATABASE [CodingSight_Demo]  CONTAINMENT = NONE  ON  PRIMARY  ( NAME = N'CodingSight_Demo', FILENAME = N'C:\MSSQL\SQLData\CodingSight_Demo.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )  LOG ON  ( NAME = N'CodingSight_Demo_log', FILENAME = N'C:\MSSQL\SQLLog\CodingSight_Demo_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) GO ALTER DATABASE [CodingSight_Demo] SET COMPATIBILITY_LEVEL = 140 GO ALTER DATABASE [CodingSight_Demo] SET ANSI_NULL_DEFAULT OFF  GO ALTER DATABASE [CodingSight_Demo] SET ANSI_NULLS OFF  GO ALTER DATABASE [CodingSight_Demo] SET ANSI_PADDING OFF  GO ALTER DATABASE [CodingSight_Demo] SET ANSI_WARNINGS OFF  GO ALTER DATABASE [CodingSight_Demo] SET ARITHABORT OFF  GO ALTER DATABASE [CodingSight_Demo] SET AUTO_CLOSE OFF  GO ALTER DATABASE [CodingSight_Demo] SET AUTO_SHRINK OFF  GO ALTER DATABASE [CodingSight_Demo] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF) GO ALTER DATABASE [CodingSight_Demo] SET AUTO_UPDATE_STATISTICS ON  GO ALTER DATABASE [CodingSight_Demo] SET CURSOR_CLOSE_ON_COMMIT OFF  GO ALTER DATABASE [CodingSight_Demo] SET CURSOR_DEFAULT  GLOBAL  GO ALTER DATABASE [CodingSight_Demo] SET CONCAT_NULL_YIELDS_NULL OFF  GO ALTER DATABASE [CodingSight_Demo] SET NUMERIC_ROUNDABORT OFF  GO ALTER DATABASE [CodingSight_Demo] SET QUOTED_IDENTIFIER OFF  GO ALTER DATABASE [CodingSight_Demo] SET RECURSIVE_TRIGGERS OFF  GO ALTER DATABASE [CodingSight_Demo] SET  DISABLE_BROKER  GO ALTER DATABASE [CodingSight_Demo] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  GO ALTER DATABASE [CodingSight_Demo] SET DATE_CORRELATION_OPTIMIZATION OFF  GO ALTER DATABASE [CodingSight_Demo] SET PARAMETERIZATION SIMPLE  GO ALTER DATABASE [CodingSight_Demo] SET READ_COMMITTED_SNAPSHOT OFF  GO ALTER DATABASE [CodingSight_Demo] SET  READ_WRITE  GO ALTER DATABASE [CodingSight_Demo] SET RECOVERY FULL  GO ALTER DATABASE [CodingSight_Demo] SET  MULTI_USER  GO ALTER DATABASE [CodingSight_Demo] SET PAGE_VERIFY CHECKSUM   GO ALTER DATABASE [CodingSight_Demo] SET TARGET_RECOVERY_TIME = 60 SECONDS  GO ALTER DATABASE [CodingSight_Demo] SET DELAYED_DURABILITY = DISABLED  GO USE [CodingSight_Demo] GO ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off; GO ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary; GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0; GO ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY; GO ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On; GO ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary; GO ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off; GO ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary; GO USE [CodingSight_Demo] GO IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [CodingSight_Demo] MODIFY FILEGROUP [PRIMARY] DEFAULT GO                  

You will see the new database created under the Databases folder of the SQL Server Management Studio:

Picture 7. The newly created database

The database has been created successfully on the SQL Server.

Summary

This article explained creating and configuring a new database using the New Database wizard of SQL Server Management Studio.

Read also

Database Design Concepts with SQL Server Management Studio (SSMS) Part 1

Learn Database Design with SQL Server Management Studio (SSMS) – Part 2

(Visited 144 times, 1 visits today)

About the Author / Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.

How To Create A Database In Sql Server 2017

Source: https://codingsight.com/create-sql-server-database-using-sql-server-management-studio-ssms/

Posted by: govanloded1954.blogspot.com

Share this post