Create a Basic SQL Database Objects Using the Transact-SQL Statement

The first database object that has to be created is a database itself. Database Engine manages both system and user databases. An authorized user can create user databases, while system databases are generated during the installation of the database system. System databases are: master, tempdb, model, msdb, and resource. Two basic methods are used to create a database. The first method involves using Object Explorer in SQL Server Management Studio. The second method involves using the Transact-SQL statement CREATE DATABASE. This statement has the following general form: CREATE DATABASE db_name [ON [PRIMARY] { file_spec1} ,...] [LOG ON {file_spec2} ,...] [COLLATE collation_name] [FOR {ATTACH | ATTACH_REBUILD_LOG }] db_name is the name of the database. The maximum size of a database name is 128 characters. The maximum number of databases managed by a single system is 32,767. All databases are stored in files. These files can be explicitly specified by the system administrator or implicitly provided by the system. If the ON option exists in the CREATE DATABASE statement, all files containing the data of a database are explicitly specified. file_spec1 represents a file specification, which includes further options such as the logical name of the file, the physical name, and the size. The PRIMARY option specifies the first (and most important) file that contains system tables and other important internal information concerning the database. If the PRIMARY option is omitted, the first file listed in the specification is used as the primary file. A login account of Database Engine that is used to create a database is called a database owner. A database can have one owner, who always corresponds to a login account name. The login account, which is the database owner, has the special name dbo. This name is always used in relation to a database it owns. dbo uses the LOG ON option to define one or more files as the physical destination of the transaction log of the database. If the LOG ON option is not specified, the transaction log of the database will still be created because every database must have at least one transaction log file.  With the COLLATE option, you can specify the default collation for the database. If the COLLATE option is not specified, the database is assigned the default collation of the model database, which is the same as the default collation of the database system. The FOR ATTACH option specifies that the database is created by attaching an existing set of operating system files. If this option is used, you have to explicitly specify the first primary file. The FOR ATTACH_REBUILD_LOG option specifies that the database is created by attaching an existing set of operating system files.  During the creation of a new database, Database Engine uses the model database as a template. The properties of the model database can be changed to suit the personal conception of the system administrator. Example: USE master; CREATE DATABASE PhiBui; An example above creates a database named PhiBui for master database. This concise form of the CREATE DATABASE statement is possible, because almost all options of that statement have default values. The system creates by default, two files. The logical name of the data file is PhiBui and its original size is 2MB. Similarly, the logical name of the transaction log is PhiBui_log and its original size is 1MB. Reference:


<<  January 2021  >>

View posts in large calendar