SQL 2005 70-431 Prüfungsvorbereitung

Andres Bohren

Hallo zusammen,

Ich bereite mich gerade auf die SQL 2005 Prüfung 70-431 vor.

TS: Microsoft SQL Server 2005 - Implementation and Maintenance
http://www.microsoft.com/learning/en/us/exams/70-431.aspx

Hardware Requirements

Prozessor Intel Pentium III 600MHz, 1GHz  empfohlen
Arbeitsspeicher Mindestens 512 MB, 1GB empfohlen
Festplattenspeicher 8 GB freier Speicherplatz
SVGA Monitor (1024*768)
DVD-ROM

Software Requirements

Windows 2000 SP4
Windows 2003 SP1
Windows XP SP2
Windows XP x64
IE 6.0 SP1
IIS 5.0 (Reporting Services
TCP/IP

Versionen

SQL Server 2005 Enterprise Edition (32/64 Bit) / Unlimited CPU / RAM OS
SQL Server 2005 Standard Edition (32/64 Bit) / 4 CPU, RAM OS / Cluster 2 Nodes
SQL Server 2005 Workgroup Edition (32 Bit) / 2 CPU, 3GB RAM
SQL Server 2005 Developer Edition (32/64 Bit)
SQL Server 2005 Express Edition (32/64 Bit) / 1 CPU, 1GB RAM / Max DB 4GB / No Full Text / No Agent


Service Packs

Hier sind die Versionen der Service Packs aufgelistet, welche mit "select @@version" abgefragt werden können.

9.00.4035 SQL Server 2005 SP3
9.00.3042 SQL Server 2005 SP2
9.00.2047 SQL Server 2005 SP1
9.00.1399 SQL Server 2005 RTM

Beispiel Datenbank

In meinem Beispiel erzeuge ich eine Datenbank mit dem Namen db_test und den untenstehenden Tabellen

 

/****************************************************************************
* SQL 2005
* T-SQL Befehle
****************************************************************************/

/* Server Infos */
USE master
SELECT @@version
SELECT @@language
SELECT @@servername
SELECT @@SERVICENAME

USE db_test
EXEC sp_helpdb --zeigt alle db's an
EXEC sp_helpdb db_test
EXEC sp_helpindex tusers

/* Information Schema*/
SELECT * FROM information_schema.tables
SELECT * FROM information_schema.columns ORDER BY table_name
SELECT * FROM information_schema.domains
SELECT * FROM information_schema.parameters
SELECT * FROM information_schema.views
SELECT * FROM information_schema.routines

/*Logins*/
USE MASTER
GO
CREATE LOGIN [DOMAIN\username] FROM WINDOWS WITH DEFAULT_DATABASE = db_test
CREATE LOGIN sqlusername WITH PASSWORD = 'password', CHECK_POLICY = OFF, DEFAULT_DATABASE = db_test
ALTER LOGIN sqlusername WITH PASSWORD = 'newpassword'
ALTER LOGIN sqlusername DISABLE
ALTER LOGIN [DOMAIN\username] DISABLE
DROP LOGIN sqlusername
DROP LOGIN [DOMAIN\username]

/*Server Rollen*/
sysadmin
securityadmin
serveradmin
setupadmin
processadmin
diskadmin
dbcreator
bulkadmin

EXEC sp_addsrvrolemember 'sqlusername', 'sysadmin'
EXEC sp_addsrvrolemember 'DOMAIN\username', 'sysadmin'
 
EXEC sp_addsrvrolemember 'sqlusername', 'sysadmin'
EXEC sp_dropsrvrolemember 'DOMAIN\username', 'sysadmin'

/* DB anlegen */
CREATE DATABASE db_test

CREATE DATABASE db_test
ON
( NAME = db_test_dat,
    FILENAME = 'D:\SQLDATA\db_test.mdf', SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )
LOG ON
( NAME = db_test_log,
    FILENAME = 'E:\SQLLOG\db_test.ldf', SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )

/*DB löschen*/
USE master
EXEC sp_dboption 'db_test', 'single user', 'true'
DROP DATABASE db_test

/*Recovery Model*/
ALTER DATABASE db_test SET RECOVERY FULL
ALTER DATABASE db_test SET RECOVERY BULK_LOGGED
ALTER DATABASE db_test SET RECOVERY SIMPLE

/*DB Rollen*/
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin

USE db_test
EXEC sp_changedbowner 'sa'
EXEC sp_adduser 'sqlusername'
EXEC sp_adduser 'corp\m.bohren'

EXEC sp_addrolemember 'db_datareader', 'sqlusername'
EXEC sp_addrolemember 'db_datawriter', 'sqlusername'
EXEC sp_addrolemember 'db_datareader', 'DOMAIN\username'
EXEC sp_addrolemember 'db_datawriter', 'DOMAIN\username'
 
EXEC sp_droprolemember 'db_datareader', 'sqlusername'
EXEC sp_droprolemember 'db_datawriter', 'sqlusername'
EXEC sp_droprolemember 'db_datareader', 'DOMAIN\username'
EXEC sp_droprolemember 'db_datawriter', 'DOMAIN\username'

EXEC sp_dropuser 'sqlusername'
EXEC sp_dropuser  'DOMAIN\username'


/* Tablle anlegen */
USE db_test
CREATE TABLE tKunden
 (
 fID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
 fVorname varchar(50) NOT NULL,
 fNachname varchar(50) NOT NULL,
 fAdresse varchar(50)NOT NULL,
 fOrtID INT NULL, 
 fCountryID INT NULL,
 )

CREATE TABLE tOrt
 (
 fID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
 fPLZ SMALLINT NOT NULL,
 fORT varchar(50) NOT NULL,
 )

CREATE TABLE tCountry
 (
 fID INT PRIMARY KEY NOT NULL IDENTITY(1,1),
 fCountry varchar(50) NOT NULL,
 )

-- Temporary Table
CREATE TABLE #tTemp
 (
 fID INT NOT NULL IDENTITY(1,1),
 fUsername varchar(50) NOT NULL,
 fNachname varchar(50) NOT NULL
 )

-- Global Temporary Table
CREATE TABLE ##tTemp
 (
 fID INT NOT NULL IDENTITY(1,1),
 fUsername varchar(50) NOT NULL,
 fNachname varchar(50) NOT NULL
 )

/*Tabelle löschen*/
DROP TABLE tKunden

/*Foreign Key*/
ALTER TABLE [dbo].[tKunden]  WITH CHECK ADD  CONSTRAINT [FK_tKunde_tOrt] FOREIGN KEY([fOrtID])
REFERENCES [dbo].[tOrt] ([fID])

ALTER TABLE [dbo].[tKunden]  WITH CHECK ADD  CONSTRAINT [FK_tKunde_tCountry] FOREIGN KEY([fCountryID])
REFERENCES [dbo].[tCountry] ([fID])