Creating a Database
You might already
have a database in which you can create some temporary tables for the purpose
of
working through the
examples. If you don't, creating one is easy enough. In Transact-SQL, you
create databases
using the CREATE DATABASE command. The complete syntax can be quite complex,
but
here's the simplest
form:
CREATE DATABASE
SQLSERVERQUEST
Run this command in
Query Analyzer to create a scratch database for working through the examples in
This book. Behind
the scenes, SQL Server creates two operating system files to house the new
database:
SQLSERVERQUEST.MDF
and SQLSERVERQUEST.LDF. Data resides in the first file; transaction log
Information lives in
the second. A database's transaction log is the area where the server first
carries out
changes made to the
data. Once those changes succeed, they're applied atomically—in one piece—to
the
actual data. It's advantageous
for both recoverability and performance to separate user data from
transaction log
data, so SQL Server defaults to working this way. If you don't specifically
indicate
transaction log
location (as in the
Example above), SQL
Server selects one for you (the default location is the data directory
that was
Selected During installation).
Notice that we
didn't specify a size for the database or for either of the les. Our new
database is set up so
That it
automatically expands as data is inserted into it. Again, this is SQL Server's
default mode of
operation.
This one feature
alone—database files that automatically expand as needed—greatly reduces the
database
administrator's
(DBA's) workload by alleviating the need to monitor databases constantly to
ensure that
they don't run out
of space. A full transaction log prevents additional changes to the database,
and a full
data segment
prevents additional data from being inserted.
TSQL Tutorials..........
No comments:
Post a Comment