How to Create, Alter, Drop Databases in SQL Server?
#DecodeITeS
SQL Server Databases - Create, Alter, Drop
SQL Server Database is group SQL Server database objects like tables, view, store procedures, DB users, etc. SQL Server Databases can be divided into 2 layers Physical & Logical.
In the Logical layer, Database objects reside like tables, view, store procedures, DB users, etc..
Whereas in the Physical layer, How database resides at a disk-level comes into the picture. Database resides on files at the OS files system. Database files are divided into data and log files. Data files consist of data\ index\ views and log file consist of transaction-level details for recovery perspective.
Types of Database files
The Primary Data File contains the startup information for the database and points to the other files in the database. User data and objects can be stored in this file or secondary data files. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.
Secondary Data File are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow. The recommended file name extension for secondary data files is .ndf.
The Transaction Log File hold the log information that is used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf.
Types of Database
Microsoft SQL Server had 2 types of databases.
System Databases: System databases come with SQL Server setup with defined purpose and objects. Each system database has its usage and gives very limited options for modification. System Databases in SQL Server are:-
Master
Model
MSDB
TempDB
ResourceDB
Distribution
User Databases: User Databases are one which is created by the user as per their requirements. When you create any database, it’s a blank container and you can create DB objects based on your application or needs. Sample Databases like AdventureWorks and WorlWideImports are also kind of user databases.
One SQL Server instance supports maximum 32767 databases including system DBs.
Database Actions
Action Command Syntax
Create Database CREATE DATABASE database_name
[ CONTAINMENT = { NONE | PARTIAL } ]
[ ON [ PRIMARY ] filespec [ ,...n ]
[ , filegroup [ ,...n ] ]
[ LOG ON filespec [ ,...n ] ] ] [ COLLATE collation_name ]
[ WITH option [,...n ] ] [;]
Alter Database ALTER DATABASE { database_name | CURRENT } { MODIFY NAME = new_database_name | COLLATE collation_name |
file_and_filegroup_options |
SET option_spec [ ,...n ]
[ WITH termination ] } [;]
Rename Database ALTER DATABASE old_database_name MODIFY NAME = New_database_name ;
Drop Database DROP DATABASE database_name
Watch video How to Create, Alter, Drop Databases in SQL Server? online without registration, duration hours minute second in high quality. This video was added by user Decode ITES 07 July 2020, don't forget to share it with your friends and acquaintances, it has been viewed on our site 503 once and liked it 3 people.