Sql Server Interview Question:Create Database

What does the Create Database statement do?

Creates a new database and the files used to store the database, or attaches a database from the files of a previously created database.

Database names must be unique within a server and conform to the rules for identifiers. database_name can be a maximum of 128 characters, unless no logical name is specified for the log.

If no logical log file name is specified, Microsoft® SQL Server™ generates a logical name by appending a suffix to database_name. This limits database_name to 123 characters so that the generated logical log file name is less than 128 characters.

What is LOG ON on create database definition?
Specifies that the disk files used to store the database log (log files) are explicitly defined. The keyword is followed by a comma-separated list of items defining the log files.
If LOG ON is not specified, a single log file is automatically created with a system-generated name and a size that is 25 percent of the sum of the sizes of all the data files for the database.

What is FOR ATTACH?
Specifies that a database is attached from an existing set of operating system files. There must be a entry specifying the first primary file.

The only other entries needed are those for any files that have a different path from when the database was first created or last attached. A entry must be specified for these files.

The database attached must have been created using the same code page and sort order as SQL Server. Use the sp_attach_db system stored procedure instead of using CREATE DATABASE FOR ATTACH directly. Use CREATE DATABASE FOR ATTACH only when you must specify more than 16 items.

If you attach a database to a server other than the server from which the database was detached, and the detached database was enabled for replication, you should run sp_removedbreplication to remove replication from the database.

What is primary when creating create database definition?

PRIMARY
Specifies that the associated list defines the primary file. The primary filegroup contains all of the database system tables. It also contains all objects not assigned to user filegroups.

The first entry in the primary filegroup becomes the primary file, which is the file containing the logical start of the database and its system tables. A database can have only one primary file. If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.

What is the default size when we create database?
SIZE
Specifies the size of the file defined in the . When a SIZE parameter is not supplied in the for a primary file, SQL Server uses the size of the primary file in the model database. When a SIZE parameter is not specified in the for a secondary or log file, SQL Server makes the file 1 MB.

How do you define maxsize on create database?
MAXSIZE
Specifies the maximum size to which the file defined in the can grow.
max_size

Is the maximum size to which the file defined in the can grow. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file grows until the disk is full.

What is UNLIMITED?
Specifies that the file defined in the grows until the disk is full.

What is FILEGROWTH
Specifies the growth increment of the file defined in the . The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.

What is growth_increment
Is the amount of space added to the file each time new space is needed. Specify a whole number; do not include a decimal. A value of 0 indicates no growth. The value can be specified in MB, KB, GB, TB, or percent (%).
If a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. If FILEGROWTH is not specified, the default value is 10 percent and the minimum value is 64 KB. The size specified is rounded to the nearest 64 KB.

How SQL Server implements the CREATE DATABASE statement?
SQL Server implements the CREATE DATABASE statement in two steps:
1. SQL Server uses a copy of the model database to initialize the database and its meta data.
2. SQL Server then fills the rest of the database with empty pages, except for pages that have internal data recording how the space is used in the database.
Any user-defined objects in the model database are therefore copied to all newly created databases. You can add to the model database any objects, such as tables, views, stored procedures, data types, and so on, to be included in all databases

How many number of database can be specified per sql server?
A maximum of 32,767 databases can be specified on a server.

How many of files used to store database?
There are three types of files used to store a database:

The primary file contains the startup information for the database. The primary file is also used to store data. Every database has one primary file.

Secondary files hold all of the data that does not fit in the primary data file. Databases need not have any secondary data files if the primary file is large enough to hold all of the data in the database. Other databases may be large enough to need multiple secondary data files, or they may use secondary files on separate disk drives to spread the data across multiple disks.

Transaction log files hold the log information used to recover the database. There must be at least one transaction log file for each database, although there may be more than one. The minimum size for a transaction log file is 512 KB.

Every database has at least two files, a primary file and a transaction log file.
Although 'os_file_name' can be any valid operating system file name, the name more clearly reflects the purpose of the file if you use the following recommended extensions.

File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
Note The master database should be backed up when a user database is created.


What is filegroup on create database definition?

All databases have at least a primary filegroup. All system tables are allocated in the primary filegroup.

A database can also have user-defined filegroups. If an object is created with an ON filegroup clause specifying a user-defined filegroup, then all the pages for the object are allocated from the specified filegroup.

The pages for all user objects created without an ON filegroup clause, or with an ON DEFAULT clause, are allocated from the default filegroup.

When a database is first created the primary filegroup is the default filegroup. You can specify a user-defined filegroup as the default filegroup using ALTER DATABASE:

How do you report a database?
To display a report on a database, or on all the databases for an instance of SQL Server, execute sp_helpdb.

How do you report database space used?
For a report on the space used in a database, use sp_spaceused.

How do you report filegroup, files on database?
For a report on the filegroups in a database use sp_helpfilegroup, and use sp_helpfile for a report of the files in a database.

How do you rename a database?
sp_renamedb Changes the name of a database.

How do you alter the database?
ALTER DATABASE
Adds or removes files and filegroups from a database. Can also be used to modify the attributes of files and filegroups, such as changing the name or size of a file. ALTER DATABASE provides the ability to change the database name, filegroup names, and the logical names of data files and log files.

How do you drop a database?
DROP DATABASE
Removes one or more databases from Microsoft® SQL Server™. Removing a database deletes the database and the disk files used by the database.
Syntax
DROP DATABASE database_name [ ,...n ]
To use DROP DATABASE, the database context of the connection must be in the master database.

Notes about drop database:
DROP DATABASE removes damaged databases marked as suspect and removes the specified database. Before dropping a database used in replication, first remove replication.

Any database published for transactional replication, or published or subscribed to merge replication cannot be dropped. If a database is damaged and replication cannot first be removed, in most cases you still can drop the database by marking it as an offline database.

A dropped database can be re-created only by restoring a backup. You cannot drop a database currently in use (open for reading or writing by any user).

When database is created the master database should be backed up
When a database is dropped, the master database should be backed up.
System databases (msdb, master, model, tempdb) cannot be dropped.