System Databases

System Databases:--


When you install SQL Server, four system databases are created: the master, tempdb, model, and msdb databases. These databases are described in the following list:
• master Records the system level information, SQL Server initialization information, and configuration settings for SQL Server. This database also records all login accounts, the existence of all other databases, and the location of the primary file for all user databases.
• tempdb Holds temporary tables and temporary stored procedures. This database is also used for other temporary storage needs of SQL Server, such as for sorting data. A clean copy of the tempdb database is re-created at its default size every time SQL Server is started. It then grows automatically, as necessary.
• model Serves as a template for all other databases created on the system, including tempdb. When a database is created, the first part of it is created as a copy of the contents of the model database. The rest of the database is filled with empty pages. The model database must exist on the system because it is used to re-create tempdb every time SQL Server is started. You can alter the model database to include user-defined data types, tables, and so on. If you alter the model database, each database you create will have the modified attributes.
• msdb Holds tables that SQL Server Agent uses for scheduling jobs and alerts and for recording operators. (Operators are individuals who are assigned responsibility for jobs and alerts.) This database also holds tables used for replication.


REAL WORLD A Simple Database
________________________________________
In the following example, we'll create a database named MyDB that contains a primary data file (MyDB_root); one secondary data file (MyDB_data1), which remains in the primary filegroup by default; and one transaction log file (Log_data1). The SQL statements for creating the MyDB database are shown here:
CREATE DATABASE MyDB
ON
(NAME = MyDB_root, --Primary data file
FILENAME = 'c:\mssql2k\MSSQL\data\mydbroot.mdf',
SIZE = 8MB,
MAXSIZE = 9MB,
FILEGROWTH = 100KB),
(NAME = MyDB_data1, --Secondary data file
FILENAME = 'c:\mssql2k\MSSQL\data\mydbdata1.ndf',
SIZE = 1000MB,
MAXSIZE = 1500MB,
FILEGROWTH = 100MB)
LOG ON
(NAME = Log_data1, --Log file
FILENAME = 'e:\log_files\logdata1.ldf',
SIZE = 1000MB,
MAXSIZE = 1500MB,
FILEGROWTH = 100MB)





Database Deletion



USE master You must use the master database to run the DROP
GO -- database command.
DROP DATABASE MyDB -- The only parameter is the name of
-- the database to be removed.
GO










Creating Tables
As you begin to design your database tables, you must make several decisions regarding their structure. These decisions include determining what pieces of data need to be stored in your tables and how your tables should relate to each other. This process will help you envision the big picture before you get into the details of creating the tables. The following list provides an overview of these design decisions:
• What data will each table contain?
• What columns should be created to hold the data, and what should they be named?
• What are the requirements for the range of data that a column should be allowed to hold, and what Microsoft SQL Server 2000 data type should be used for each column?
• Are there any columns that must be allowed to contain null values, or can defaults be used instead? (Allowing null values requires more processing overhead than does using defaults.)
• Which columns will be primary keys, and which will be foreign keys?
• What kinds of constraints should be used?
• What type of index or indexes (clustered or nonclustered) should the table have, and on which column or columns should these indexes be defined?
• Which users should have access to which tables?
Try to resolve as many of these design issues as possible, and track them on paper or with online diagrams to understand the overall design of your database tables before you create them. You should also find out from your users how the data will be accessed. For example, find out if a particular table data will be read-only or if inserts, deletes, and updates will be performed. Determine which queries will be performed most often and which columns will need to be retrieved. Establish what information is really needed in the database and what is not necessary to store. This information will help you decide how to build tables and indexes, what constraints might be needed, where defaults might be useful, and more.


System data types in SQL Server 2000
Data type Description Storage size
bigint An 8-byte integer (whole number). 8 bytes
binary[(n)] Fixed-length binary data of n bytes, where n is a value from 1 through 8000. Use binary when data entries in a column are expected to be close to the same size. n + 4 bytes
bit Integer data type that can be a value of 1, 0, or NULL. Bit columns cannot have indexes on them 1 byte for a table with up to 8-bit columns, 2 bytes for a table with 9-bit through 16-bit columns, and so on
char[(n)] Fixed-length non-Unicode character data with length of n characters, where n is a value from 1 through 8000 n bytes
cursor A reference to a cursor. Can be used only for variables and stored procedure parameters Not applicable
datetime Date and time data from January 1, 1753 through December 31, 9999, with accuracy to 3.33 milliseconds 8 bytes
decimal[(p,[s])] or numeric[(p,[s])] Fixed-precision and fixed-scale numbers. (The data type numeric is a synonym for decimal.)Precision (p) specifies the total number of digits that can be stored, both to the left and to the right of the decimal point. Scale (s) specifies the maximum number of digits that can be stored to the right of the decimal point. Scale must be less than or equal to precision. The minimum precision is 1, and the maximum precision is 28 unless SQL Server is started with the -p parameter, in which case, precision can be up to 38. 5 through 17 bytes, depending on precision
float[(n)] Floating-precision numerical data that can range from -1.79E +308 through 1.79E +308. The value n is the number of bits used to store the mantissa of the float number and can range from 1 to 53 4 through 8 bytes, depending on precision
image Used for variable-length binary data longer than 8000 bytes, with a maximum of 2^31 - 1 bytes. An image column entry is a pointer to the location of the image data value. The data is stored separately from the table data 16 bytes for the pointer
integer or int Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647) 4 bytes
money Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (922,337,203,685,477.5807), with accuracy to one ten-thousandth of a monetary unit 8 bytes
nchar[(n)] Fixed-length Unicode character data of n characters, where n is a value from 1 through 4000.Unicode characters use 2 bytes per character and can support all international characters- 2 bytes * the number of characters entered
ntext Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.The column entry for ntext is a pointer to the location of the data. The data is stored separately from the table data 16 bytes for the pointer and 2 bytes * the number of characters entered for the data
nvarchar Variable-length Unicode data of n characters, where n is a value from 1 through 4000. Recall that Unicode characters use 2 bytes per character and can support all international characters. 2 bytes * the number of characters entered
real Floating-precision numerical data that can range from ?3.40E+38 through 3.40E+38. The synonym for real is float(24) 4 bytes
smalldatetime Date and time data from January 1, 1900 through June 6, 2079, with accuracy to the minute (less precise than the datetime data type) 4 bytes
smallint Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767) 2 bytes
smallmoney Monetary data values from -214,748.3648 through 214,748.3647, with accuracy to one ten-thousandth of a monetary unit 4 bytes
sql_variant Allows values of different data types. The data value and data describing that value—its base data type, scale, precision, maximum size, and collation are stored in this column Size varies
sysname A special, system-supplied, SQL Server user-defined data type. The sysname data type is defined by SQL Server as nvarchar(128), which means that it can contain 128 Unicode characters (or 256 bytes). Use sysname to refer to columns that store object names 256 bytes
table Similar to using a temporary table—the declaration includes a column list and data types. Can be used to define a local variable or for the return value of a user-defined function. Varies with table definition
text Used for variable-length non-Unicode character data longer than 8000 bytes. A text column entry can hold up to 2^31 - 1 characters. It is a pointer to the location of the data value. The data is stored separately from the table data 16 bytes for the pointer
timestamp A timestamp column is automatically updated every time a row is inserted or updated. Each table can have only one timestamp column. 8 bytes
tinyint Integer data from 0 through 255. 1 byte
unique-identifier Stores a 16-byte binary value that is a globally unique identifier (GUID) 16 bytes
varbinary Variable-length binary data of n bytes, where n is a value from 1 through 8000. Use varbinary when data entries in a column are expected to vary considerably in size. Actual length of data entered + 4 bytes
varchar[(n)] Variable-length non-Unicode character data with a length of n characters, where n is a value from 1 through 8000. Actual length of data entered


Sample code for creating a table
CREATE TABLE Product_Info
(
Product_ID smallint,
Product_Name char(20),
Description char(30),
Price smallmoney,
Brand_ID brand_type
)

Using Null Values
DROP TABLE Product_Info
GO
CREATE TABLE Product_Info
(
Product_ID smallint NOT NULL,
Product_Name char(20) NOT NULL,
Description char(30) NULL,
Price smallmoney NOT NULL,
Brand_ID brand_type

)
GO

Another example:

Use MyDB
CREATE TABLE Customer_Data
(customer_id smallint,
first_name char(20),
last_name char(20),
phone char(10))
GO

The ALTER TABLE Statement
ALTER TABLE Customer_Data
ADD middle_initial char(1)
GO

The DROP TABLE Statement
DROP TABLE Customer_Data
GO

The INSERT Statement
INSERT INTO Customer_Data
(customer_id, first_name, last_name, phone)
VALUES (777, "Frankie", "Stein", "4895873900")
Notice the list of column names in the second line of the preceding SQL statement. Listing these column names specifies in which column the data values will be placed, in corresponding order. For example, the first data value will be placed into the first column listed, customer_id, the second value will go into the second column listed, and so on. Because we have listed the values to be inserted in the same order in which the columns were defined when the table was created, we do not have to specify the column names at all. We could use the following INSERT statement instead:

INSERT INTO Customer_Data
VALUES (777, "Frankie", "Stein", "4895873900")


The SELECT Statement

SELECT customer_id, first_name FROM Customer_Data
WHERE first_name = "Frankie"

The UPDATE Statement
UPDATE Customer_Data
SET first_name = "Franklin"
WHERE last_name = "Stein" and customer_id=777



The DELETE Statement
DELETE FROM Customer_Data or

DELETE Customer_Data
To delete the rows from the Customer_Data table in which the value in the customer_id column is less than 100, use the following statement:

DELETE FROM Customer_Data
WHERE customer_id < 100