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
» 
Comment and Contribute
