How to Create Database in SQL Server 2014 - SQL Server DBA Tutorial

In this video you will learn how to create database using SQL Server Management Studio as well as using T-SQL script. You will learn some of the best practices of creating SQL Server Databases and learn some of the important database options to consider while creating SQL Server Databases.


Scripts used in this video
USE [master]
GO

/****** Object: Database [SalesOrders] ******/
CREATE DATABASE [SalesOrders]
CONTAINMENT 
= NONE
ON  PRIMARY 
( NAME = N'SalesOrders', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\DATA\SalesOrders.mdf' , 
SIZE = 2048KB , MAXSIZE =UNLIMITED, FILEGROWTH = 1024KB )

LOG ON ( NAME = N'SalesOrders_log',
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLPROD\MSSQL\DATA\SalesOrders.ldf' , 
SIZE = 1024KB , MAXSIZE =2048MB , 
FILEGROWTH = 10%)
GO


 
How to Create Database in SQL Server 2014

9 comments:

  1. Hi Sir,

    CreateDB_Options query is not mentioned here as you have shown in tutorial video. Can you please make it available for the same?

    And your each tutorial awesome sir.

    ReplyDelete
    Replies
    1. CreateDB_Options query is not mentioned here

      Delete
    2. /*
      ALTER DATABASE [MyDatabase] SET ANSI_NULL_DEFAULT OFF
      GO
      ALTER DATABASE [MyDatabase] SET ANSI_NULLS OFF
      GO
      ALTER DATABASE [MyDatabase] SET ANSI_PADDING OFF
      GO
      ALTER DATABASE [MyDatabase] SET ANSI_WARNINGS OFF
      GO
      ALTER DATABASE [MyDatabase] SET ARITHABORT OFF
      GO
      ALTER DATABASE [MyDatabase] SET AUTO_CLOSE OFF
      GO
      ALTER DATABASE [MyDatabase] SET AUTO_SHRINK OFF
      GO
      ALTER DATABASE [MyDatabase] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
      GO
      ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS ON
      GO
      ALTER DATABASE [MyDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF
      GO
      ALTER DATABASE [MyDatabase] SET CURSOR_DEFAULT GLOBAL
      GO
      ALTER DATABASE [MyDatabase] SET CONCAT_NULL_YIELDS_NULL OFF
      GO
      ALTER DATABASE [MyDatabase] SET NUMERIC_ROUNDABORT OFF
      GO
      ALTER DATABASE [MyDatabase] SET QUOTED_IDENTIFIER OFF
      GO
      ALTER DATABASE [MyDatabase] SET RECURSIVE_TRIGGERS OFF
      GO
      ALTER DATABASE [MyDatabase] SET DISABLE_BROKER
      GO
      ALTER DATABASE [MyDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
      GO
      ALTER DATABASE [MyDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF
      GO
      ALTER DATABASE [MyDatabase] SET PARAMETERIZATION SIMPLE
      GO
      ALTER DATABASE [MyDatabase] SET READ_COMMITTED_SNAPSHOT OFF
      GO
      ALTER DATABASE [MyDatabase] SET READ_WRITE
      GO
      ALTER DATABASE [MyDatabase] SET RECOVERY FULL
      GO
      ALTER DATABASE [MyDatabase] SET MULTI_USER
      GO
      ALTER DATABASE [MyDatabase] SET PAGE_VERIFY CHECKSUM
      GO
      ALTER DATABASE [MyDatabase] SET TARGET_RECOVERY_TIME = 60 SECONDS
      GO
      ALTER DATABASE [MyDatabase] SET DELAYED_DURABILITY = DISABLED
      GO
      USE [MyDatabase]
      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
      */

      Delete
  2. CreateDB_Options query is not mentioned here

    ReplyDelete
  3. Nice article. Agree, thanks for sharing. I'm sure others will benefit from it too 789betting vip

    ReplyDelete
  4. we are proud of you
    you did great job
    we wish you best of life

    ReplyDelete
  5. very interesting, good job, and thanks for sharing such a good blog. 123bet vip

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. I found a lot of useful information about SQL Server SELF JOIN

    ReplyDelete