SQL Server Database Services (How Do I)
The SQL Server 2005 Database Engine is the core service for storing, processing, and securing data. The Database Engine provides controlled access and rapid transaction processing to meet the requirements of the most demanding data consuming applications within your enterprise. The Database Engine also provides rich support for sustaining high availability.
The following list of key topics will help you learn more about how to install, understand, and use the Database Engine. For other topics, use the search feature of Books Online or MSDN.
Orientation and Setup
- Database Snapshots
New in SQL Server 2005, a database snapshot is a read-only, database view to which you can revert later in the event of an error. This section describes how snapshots work and the typical uses for them.
- International Considerations for Databases and Database Engine Applications
Storing data in multiple languages within one database is difficult to manage when using only character data and code pages. This section describes how to use collations and Unicode to easily manage issues like sort order and character data in a multilanguage environment.
Designing and Creating
- Estimating the Size of a Database
Obtaining the best performance from your applications depends to a great extent on knowing how large your database will be when filled with data. The answer to that question can affect deployment decisions such as whether to normalize. Learn more here.
- Creating Indexes (Database Engine)
This section tells you how to create indexes to significantly improve the performance of database queries and applications, as well as to enforce uniqueness on the rows in a table, ensuring the data integrity of the table data.
- Partitioned Tables and Indexes
Make your large tables and indexes more manageable and scalable. This section provides the information necessary to understand, design, implement, and work with partitioned tables and indexes.
- DDL Triggers
DDL triggers are useful when you want to respond to changes or events in the database schema, as opposed to changes in a table or view. Learn more about these useful features here.
- Creating a Database (Database Engine)
This section covers the issues to consider when performing this basic SQL Server task, as well as various methods for accomplishing it.
Administering
- Shrinking a Database
Over time, SQL Server databases and transaction logs can become larger than necessary as tables accumulate unused pages, decreasing performance and wasting disk space. This topic describes methods for reclaiming this unused disk space.
- Using the sqlcmd Utility
A command-line alternative to SQL Server Management Studio, the sqlcmd utility is used for ad hoc, interactive execution of Transact-SQL statements and scripts.
- Introducing SQL Server Management Studio
SQL Server Management Studio combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager, included in previous releases of SQL Server, into a single environment.
- Registering Servers
Administrators will learn here how to register a local or remote server, which stores the server connection information for future connections.
- SQL Server Management Studio and Business Intelligence Development Studio
This section provides a comprehensive understanding of the basic containers you use when working in SQL Server Management Studio and Business Intelligence Development Studio.
- Automated Administration How-to Topics (SQL Server Management Studio)
Learn how to use automated administration to handle routine tasks and events, to free time to perform other administrative functions.
- Monitoring Events
Learn how to use powerful tools such as SQL Server Profiler to troubleshoot performance, audit database activity, gather sample data for a test environment, debug Transact-SQL statements and stored procedures, or gather data for performance analysis tools.
- Managing the Database Engine Services
Learn how to use SQL Server Configuration Manager to manage SQL Server applications that run in the system background.
- Securables
Administrators often need to regulate access to SQL Server resources. This topic covers scopes, which allow hierarchical nesting of securables.
Programming
- Data Type Conversion (Database Engine)
Data types may be converted when data from one object is moved to, compared with, or combined with data from another object, or when data from a Transact-SQL result column, return code, or output parameter is moved into a program variable. This topic lays out when such conversions occur implicitly and when they can take place explicitly, through use of the CAST and CONVERT functions.
- Date Functions
One of the most common database programming tasks is calculating, retrieving, and manipulating dates. This section introduces the most common functions for this purpose.
- Recursive Queries Using Common Table Expressions
A common table expression (CTE) can reference itself, creating a recursive CTE. A recursive CTE can greatly simplify the code needed to execute a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
- Using FOR XML and OPENXML to Publish and Process XML Data
XML support is deeply integrated into all SQL Server 2005 components. Specifically, you can execute Transact-SQL queries to return results as XML rather than standard rowsets. These queries can be executed directly or from within stored procedures.
- SQL Injection
SQL injection is an attack in which malicious code is inserted into strings that are later passed to SQL Server for parsing and execution. This topic provides examples of injection attacks, and specific steps you can take to prevent them.