Creating and maintaining indexes
Creating views, defaults, stored procedures, and triggers
Accessing and modifying data in SQL Server and remote data sources
Working with data in an XML format
Using SQL Server utilities to import and export data in bulk
Developing a security plan for a database
Understanding locking and its impact on the server
Tuning SQL Server query performance
If we had to create a single sentence to describe the test, it would be as
follows: The exam will test your knowledge of designing, creating, and
maintaining a database on SQL Server 2000. To pass the test, you need to
fully understand these topics.
Download this Tutorial in here
SQL Server 2005 Compact Edition
Tutorial: CRUD - (Create, Read, Update, and Delete)
Download this Tutorial in here
A Tutorial on SQL Server 2005
CMPT 354 Fall 2007
Please download here PDF | 1.65 MB
Table of Content
• Copy-only backup
• Checksum
• Full-text catalogs
• article:
Partition indexes for improved SQL Server 2005 performance
• 16 article: SQL Server 2005 upgrade hurdles
Database administrators must have a reliable backup and recovery plan for their SQL Server databases.
After five years in development, see how SQL Server 2005 provides greater backup and recovery options.
Also, read about SQL Server 2005 performance tuning and upgrade hurdles.
Download this Tutorial in here
Table of Contents
Abstract
Introduction
Obtaining Information Using Error Messages
Leveraging Further Access
xp_cmdshell
xp_regread
Other Extended Stored Procedures
Linked Servers
Custom extended stored procedures
Importing text files into tables
Creating Text Files using BCP
ActiveX automation scripts in SQL Server
Stored Procedures
Advanced SQL Injection
Strings without quotes
Second-Order SQL Injection
Length Limits
Audit Evasion
Defences
Input Validation
SQL Server Lockdown
References
Appendix A - 'SQLCrack'
sqlcrack.sql
Download this Tutorial in here
Oracle/SQL Tutorial
This Oracle/SQL tutorial provides a detailed introduction to the SQL query language and the Oracle Relational Database Management System.
Further information about Oracle and SQL can be found on the web site www.db.cs.ucdavis.edu/dbs
Download this Tutorial in here
HOL SQL Server
1. Tujuan Hands on Lab
a. Memahami pembuatan database dan tabel di SQL Server.
b. Memahami pembuatan relationship antar tabel.
c. Memahami pembuatan dan fungsi stored procedure
Download Tutorial ini disini
Microsoft SQL Server 2112
A BEGINNER'S GUIDE
Part I
Basic Concepts and Installation
Download here
Microsoft SQL Server Black Book
Table of Contents
Introduction
What's on the CD-ROM
Dedication
Chapter 1—Preinstallation Considerations
What Is A Device?
What, Then, Is A Database?
What Are Character Sets And Sort Orders?
What Is The Recommended System Configuration?
Where Should The Microsoft SQL Server Be Installed?
What’s Stored In The Master Database?
The Master Database
The Pubs Database
The Model Database
Tempdb
The Msdb Database
Be Careful With Memory
What Security Model Will Be Used?
Spring Cleaning
Protocols
Services
What About The SQL Mail Client?
Should I Use The Default Location For My Devices?
What Hardware Should I Install Microsoft SQL Server On?
Finally
Summary
Practical Guide to Preinstallation
The Preinstallation Checklist
Chapter 2—Installing Microsoft SQL Server
Installing A Production Data Server
SQL Server A (The Base Level)
SQL Server B (Middle Of The Road)
SQL Server C (The High-End Beast)
Before Installing SQL Server
Creating Your SQLExec Account
One More Account To Go
Setting Up A Mail Client
One Last Time
Summary
Practical Guide to Installation
SQL Server Installation From Start To Finish
Post-Installation Issues
Chapter 3—Development Versus Production
Setting Up The Development Environment
Third-Party Tools
Data Modeling
Server-Level Parameters
User Connections
Tempdb in RAM
Sort Pages
Resource Timeout
Read-Ahead Optimization
Priority Boost
Max Worker Threads
Lock Escalation Parameters
Fill Factor
Application Parameters
DBCC PINTABLE
Registry-Type Tables
List-Type Tables
Setup Scripts
Sample Server Setup Script
Scripting Objects
Third-Party Management
Transferring Objects
Transferring Data
BCP
INSERT/SELECT
DBArtisan
Permissions
Users And Groups
Summary
Practical Guide to Transferring Objects
Registering Servers
Moving Objects From Server To Server
Warning Messages
Pitfalls
Chapter 4—Replication
Data Distribution Models
Two-Phase Commit
Replication Consistency
Terminology
Publisher
Subscriber
Distribution Server
Transaction Log
Synchronization
Horizontal Partitions
Vertical Partitions
Articles
Publications
Push
Pull
Server Roles
Publisher Server
Subscriber Server
Distribution Server
Scenarios
Considerations
Central Publisher
Central Publisher With Remote Distribution
Publishing Subscriber
Central Subscriber
Multiple Publishers Of A Single Table
Events And Processes
Log Reader Process
Synchronization Process
Replication Distribution Process
Communication Failures
Prerequisites For Replication
Memory
Working Directory
Same Character Set
Protocol
Trusts
Disk Space
SQL Executive
User Connections
Primary Key
Summary
Practical Guide to Replication
Installing The Distribution Database
Setting Publication Options
Creating Publications And Articles
Setting Subscription Options
Subscribing To A Publication
Chapter 5—Structured Query Language (SQL)
ANSI-Compliant SQL
Syntax
Comments
Pubs Database
Authors
Sales
Titleauthor
SELECT Statements
WHERE Clause
ORDER BY Clause
GROUP BY Clause
Join Conditions
Aliases
Aggregates And Functions
SUM()
MAX()
MIN()
AVG()
COUNT()
CONVERT()
GETDATE()
DATEDIFF()
DATEPART()
SOUNDEX()
SUBSTRING()
UPPER()
CHARINDEX()
RTRIM()
System Functions
ISNULL()
USER_ID()
USER_NAME()
DATALENGTH()
COL_LENGTH()
Calculated Values
Optimizer Hints
Subqueries
Union
INSERT Statements
Identity Columns
Stored Procedures
Triggers
UPDATE Statements
DELETE Statements
Batches
Cursors
Summary
Practical Guide To SQL
Schema Changes
Backing Up Data
Renaming Objects To Be Modified
Scripting Objects
Converting And Inserting Old Data
Cleaning Up The Environment
A Word On Constraints
Chapter 6—Stored Procedures
Consistent Data Manipulation
Enter Stored Procedures
Establishing Standards
Getting Data
Modifying Data
Modular Programming
Reduced Client Processing
Network Traffic
Calling A Stored Procedure
Query Optimizer
Query Plan
Parameters
Variables
NT Server Registry
Maintenance
Return Codes
Additional Rules
Nesting And Recursion
System Stored Procedures
Custom Stored Procedures
External Stored Procedures
Remote Stored Procedures
Startup Stored Procedures
Prior To Production
Summary
Practical Guide to Stored Procedures
Parsing A String
Redundant Code
Reduced Network Traffic
Calling Procedures Within Procedures
Chapter 7—Views
Syntax For Creating Views
Normalized Data
Partitioned Data
Vertical Partitions
Horizontal Partitions
Multiple Tables
Computed Values
Security
Updates
Underlying Objects
Performance
Restrictions
Summary
Practical Guide to Views
Determining Column Needs
Partitioning And Combining Data
Checking Index Coverage
Modifications
Chapter 8—Triggers
Data Integrity
Syntax
Business Rules
Permissions
Nesting
More On Triggers
Virtual Tables
Inserted Tables
Deleted Tables
Virtual Table Usage
Global Variables
INSERT Triggers
UPDATE Triggers
DELETE Triggers
Limitations
Multiple-Row Considerations
Performance
Summary
Practical Guide to Triggers
Remove The Foreign Key Constraints
Define The Business Rule
Identify The Child Records
Graphically Represent The Trigger Firing Order
Write A Test Script
Check The titleAuthor Table
Create The Trigger
Test The Trigger
Chapter 9—Rules, Defaults, Constraints, And
User-Defined Data Types
Rules
Creating Rules
Binding Rules
Changing Rules
Dropping Rules
Defaults
Creating Defaults
Binding Defaults
Changing Defaults
Dropping Defaults
Constraints
Primary Key
Unique
Foreign Key
Default
Check
User-Defined Data Types
Entity Definition
Create Table Statement
Dependency
Summary
Practical Guide to Rules, Defaults, Constraints, And
User-Defined Data Types
Creating The Scripts
Printing Out A UDT Listing
Building A Table Structure
Maintenance And Troubleshooting
Chapter 10—Error Codes
Errors In Microsoft SQL Server
Method Or Madness
Service Packs
Research
Summary
Practical Guide to Error Codes
Query/Connection-Based Errors
Server Configuration Errors
Connectivity Errors
Transaction Log Errors
Table And Index Errors
Chapter 11—Performance Tuning And
Optimization
What Is Performance?
Performance Monitor
Windows NT
Data Models
Application Design
Establish A Baseline
Keep It Simple
SQL Server Trace Flags
SQL Trace
SQL Probe
Summary
Practical Guide to Tuning And Optimization
Using The Performance Monitor
Using SQL Trace
Chapter 12—Newsgroups And The Internet
Accessing The Internet
Browsers
Functions
Search Engines
Knowledge Base
Service Packs And Patches
TechNet CD-ROM
Microsoft SQL Server Books Online
Newsgroups
Summary
Practical Guide to Free Agent Installation
Installing The Newsreader
Configuring The Source News Server
Subscribing To Newsgroups
Preferences
Newsgroup Etiquette
A Coriolis Group New Title—Bonus Chapter
Chapter 13—Using Java To Access Databases
The GuestBook Program
Initializing The Application
Handling Window Events
Opening A Database Connection
Closing The Database Connection
Executing A SQL Command
Handling Errors And Exceptions
Setting Up The ODBC Data Source
An Improved GuestBook Program
The init( ) Method
The execSQLCommand Method
The moveDataToForm( ) Method
The actionPerformed( ) Method
The destroy( ) Method
The WindowHandler Inner Class
Java And Access Data Types
Summary
Appendix A
Appendix B
Glossary
Index
Please download here
Microsoft SQL Server Black Book
Table of Contents
Introduction
What's on the CD-ROM
Dedication
Chapter 1—Preinstallation Considerations
What Is A Device?
What, Then, Is A Database?
What Are Character Sets And Sort Orders?
What Is The Recommended System Configuration?
Where Should The Microsoft SQL Server Be Installed?
What’s Stored In The Master Database?
The Master Database
The Pubs Database
The Model Database
Tempdb
The Msdb Database
Be Careful With Memory
What Security Model Will Be Used?
Spring Cleaning
Protocols
Services
What About The SQL Mail Client?
Should I Use The Default Location For My Devices?
What Hardware Should I Install Microsoft SQL Server On?
Finally
Summary
Practical Guide to Preinstallation
The Preinstallation Checklist
Chapter 2—Installing Microsoft SQL Server
Installing A Production Data Server
SQL Server A (The Base Level)
SQL Server B (Middle Of The Road)
SQL Server C (The High-End Beast)
Before Installing SQL Server
Creating Your SQLExec Account
One More Account To Go
Setting Up A Mail Client
One Last Time
Summary
Practical Guide to Installation
SQL Server Installation From Start To Finish
Post-Installation Issues
Chapter 3—Development Versus Production
Setting Up The Development Environment
Third-Party Tools
Data Modeling
Server-Level Parameters
User Connections
Tempdb in RAM
Sort Pages
Resource Timeout
Read-Ahead Optimization
Priority Boost
Max Worker Threads
Lock Escalation Parameters
Fill Factor
Application Parameters
DBCC PINTABLE
Registry-Type Tables
List-Type Tables
Setup Scripts
Sample Server Setup Script
Scripting Objects
Third-Party Management
Transferring Objects
Transferring Data
BCP
INSERT/SELECT
DBArtisan
Permissions
Users And Groups
Summary
Practical Guide to Transferring Objects
Registering Servers
Moving Objects From Server To Server
Warning Messages
Pitfalls
Chapter 4—Replication
Data Distribution Models
Two-Phase Commit
Replication Consistency
Terminology
Publisher
Subscriber
Distribution Server
Transaction Log
Synchronization
Horizontal Partitions
Vertical Partitions
Articles
Publications
Push
Pull
Server Roles
Publisher Server
Subscriber Server
Distribution Server
Scenarios
Considerations
Central Publisher
Central Publisher With Remote Distribution
Publishing Subscriber
Central Subscriber
Multiple Publishers Of A Single Table
Events And Processes
Log Reader Process
Synchronization Process
Replication Distribution Process
Communication Failures
Prerequisites For Replication
Memory
Working Directory
Same Character Set
Protocol
Trusts
Disk Space
SQL Executive
User Connections
Primary Key
Summary
Practical Guide to Replication
Installing The Distribution Database
Setting Publication Options
Creating Publications And Articles
Setting Subscription Options
Subscribing To A Publication
Chapter 5—Structured Query Language (SQL)
ANSI-Compliant SQL
Syntax
Comments
Pubs Database
Authors
Sales
Titleauthor
SELECT Statements
WHERE Clause
ORDER BY Clause
GROUP BY Clause
Join Conditions
Aliases
Aggregates And Functions
SUM()
MAX()
MIN()
AVG()
COUNT()
CONVERT()
GETDATE()
DATEDIFF()
DATEPART()
SOUNDEX()
SUBSTRING()
UPPER()
CHARINDEX()
RTRIM()
System Functions
ISNULL()
USER_ID()
USER_NAME()
DATALENGTH()
COL_LENGTH()
Calculated Values
Optimizer Hints
Subqueries
Union
INSERT Statements
Identity Columns
Stored Procedures
Triggers
UPDATE Statements
DELETE Statements
Batches
Cursors
Summary
Practical Guide To SQL
Schema Changes
Backing Up Data
Renaming Objects To Be Modified
Scripting Objects
Converting And Inserting Old Data
Cleaning Up The Environment
A Word On Constraints
Chapter 6—Stored Procedures
Consistent Data Manipulation
Enter Stored Procedures
Establishing Standards
Getting Data
Modifying Data
Modular Programming
Reduced Client Processing
Network Traffic
Calling A Stored Procedure
Query Optimizer
Query Plan
Parameters
Variables
NT Server Registry
Maintenance
Return Codes
Additional Rules
Nesting And Recursion
System Stored Procedures
Custom Stored Procedures
External Stored Procedures
Remote Stored Procedures
Startup Stored Procedures
Prior To Production
Summary
Practical Guide to Stored Procedures
Parsing A String
Redundant Code
Reduced Network Traffic
Calling Procedures Within Procedures
Chapter 7—Views
Syntax For Creating Views
Normalized Data
Partitioned Data
Vertical Partitions
Horizontal Partitions
Multiple Tables
Computed Values
Security
Updates
Underlying Objects
Performance
Restrictions
Summary
Practical Guide to Views
Determining Column Needs
Partitioning And Combining Data
Checking Index Coverage
Modifications
Chapter 8—Triggers
Data Integrity
Syntax
Business Rules
Permissions
Nesting
More On Triggers
Virtual Tables
Inserted Tables
Deleted Tables
Virtual Table Usage
Global Variables
INSERT Triggers
UPDATE Triggers
DELETE Triggers
Limitations
Multiple-Row Considerations
Performance
Summary
Practical Guide to Triggers
Remove The Foreign Key Constraints
Define The Business Rule
Identify The Child Records
Graphically Represent The Trigger Firing Order
Write A Test Script
Check The titleAuthor Table
Create The Trigger
Test The Trigger
Chapter 9—Rules, Defaults, Constraints, And
User-Defined Data Types
Rules
Creating Rules
Binding Rules
Changing Rules
Dropping Rules
Defaults
Creating Defaults
Binding Defaults
Changing Defaults
Dropping Defaults
Constraints
Primary Key
Unique
Foreign Key
Default
Check
User-Defined Data Types
Entity Definition
Create Table Statement
Dependency
Summary
Practical Guide to Rules, Defaults, Constraints, And
User-Defined Data Types
Creating The Scripts
Printing Out A UDT Listing
Building A Table Structure
Maintenance And Troubleshooting
Chapter 10—Error Codes
Errors In Microsoft SQL Server
Method Or Madness
Service Packs
Research
Summary
Practical Guide to Error Codes
Query/Connection-Based Errors
Server Configuration Errors
Connectivity Errors
Transaction Log Errors
Table And Index Errors
Chapter 11—Performance Tuning And
Optimization
What Is Performance?
Performance Monitor
Windows NT
Data Models
Application Design
Establish A Baseline
Keep It Simple
SQL Server Trace Flags
SQL Trace
SQL Probe
Summary
Practical Guide to Tuning And Optimization
Using The Performance Monitor
Using SQL Trace
Chapter 12—Newsgroups And The Internet
Accessing The Internet
Browsers
Functions
Search Engines
Knowledge Base
Service Packs And Patches
TechNet CD-ROM
Microsoft SQL Server Books Online
Newsgroups
Summary
Practical Guide to Free Agent Installation
Installing The Newsreader
Configuring The Source News Server
Subscribing To Newsgroups
Preferences
Newsgroup Etiquette
A Coriolis Group New Title—Bonus Chapter
Chapter 13—Using Java To Access Databases
The GuestBook Program
Initializing The Application
Handling Window Events
Opening A Database Connection
Closing The Database Connection
Executing A SQL Command
Handling Errors And Exceptions
Setting Up The ODBC Data Source
An Improved GuestBook Program
The init( ) Method
The execSQLCommand Method
The moveDataToForm( ) Method
The actionPerformed( ) Method
The destroy( ) Method
The WindowHandler Inner Class
Java And Access Data Types
Summary
Appendix A
Appendix B
Glossary
Index
Please download here
0 komentar:
Post a Comment