Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jun 7, 2002

SQL Server 2000 Administration in 15 Minutes a Week: More Database Creation Topics - Page 2

By Michael Aubert


To create a database we use the CREATE DATABASE statement. Let's look at the syntax for this statement:

CREATE DATABASE database_name
[ ON
    [ < filespec > [ ,...n ] ]
    [ , < filegroup > [ ,...n ] ]
]
[ LOG ON { < filespec > [ ,...n ] } ]
[ COLLATE collation_name ]
[ FOR LOAD | FOR ATTACH ]

< filespec > ::=

[ PRIMARY ]
(
[ NAME = logical_file_name , ]
    FILENAME = 'os_file_name'
    [ , SIZE = size ]
    [ , MAXSIZE = { max_size | UNLIMITED } ]
    [ , FILEGROWTH = growth_increment ] ) [ ,...n ]

< filegroup > ::=

FILEGROUP filegroup_name < filespec > [ ,...n ]

Confusing isn't it? To brake it down line by line have a look at the following link: Transact-SQL Reference: CREATE DATABASE You can also find this information in the  SQL Server Books Online under "CREATE DATABASE".

For example we are going to create a database called DBbySQL with a 5 MB data file and 1 MB log file. Enter the following statement into the query window:




There are a few things you should note here. First, the USE statement changes the Database context (the database we are working with). In this case, we are adding a new database so we use the master database. Next, the GO command tells SQL Query Analyzer to execute the current batch of Transact-SQL statements. A batch is simply a set of Transact-SQL statements from the last GO command or from the start of the script. It is important to know that GO is not a SQL statement, rather it is a command that you can use to tell SQL Query Analyzer or OSQL (and ISQL for that madder) to send the current batch of Transact-SQL statements to SQL Server.

One last thing to notice about this script is the CREATE DATABASE statement itself. More specifically, we don't have to specify every option available. For the options we did not specify, SQL server will use default values. Also when we set the SIZE of the files, we don't have to specify MB because MB is the default for the SIZE value (although for readability you may want to add MB anyway).

Once you have entered the statement, select Execute from the Query menu. The output from Query Analyzer should look like the following:



That's it! We now have a new database created on our SQL Server.



You can also use the ALTER DATABASE statement to modify an existing database. For example, the following statement would add a new data file to the DBbySQL database with a size of 5MB and a Max size of 50MB:

USE master
GO
ALTER DATABASE DBbySQL
ADD FILE
(
  NAME = DBbySQL_data2,
  FILENAME = 'c:\DBbySQL_data2.ndf',
  SIZE = 5MB,
  MAXSIZE = 50MB
)
GO

For more information on the ALTER DATABASE statement see "ALTER DATABASE" in the SQL Server Books Online. This information can also be found on the MSDN website at: Transact-SQL Reference: ALTER DATABASE

Note: I plan to cover the Query Analyzer in more detail in later articles.


Page 3: Understanding Transaction Logs


 » See All Articles by Columnist Michael Aubert



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM