Introducing SQL Server

Overview of the SQL Server suite of products

• Fundamentals of the relational database engine
• Benefits of the Business Intelligence stack

SQL Server components and tools

• SQL Server Agent and Management Studio
• Integration Services
• Analysis Services
• Reporting Services
• Data Quality Services
• Master Data Services

Constructing a relational database

• Choosing optimal file size and growth parameters
• Working with database schemas

Defining tables

• Specifying appropriate data types
• Indexing tables with Management Studio
• Adding, modifying or removing columns

Safeguarding data with constraints

• Enforcing uniqueness through primary key constraints
• Validating conditions with check constraints
• Protecting relationships with foreign keys

Retrieving data with Transact-SQL

• Employing Query Designer to construct select statements
• Retrieving subsets of data from multiple tables using joins and the where clause criteria
• Analyzing data for aggregate functions

Modifying data with SQL commands

• Inserting new rows into tables
• Updating targeted data
• Deleting specific rows

Introduction of developing programming constructs

• Creating stored procedures for productivity
• Writing user-defined functions to simplify queries
• Deploying triggers to capture change events

Executing the Maintenance Plan Wizard

• Defragmenting data and checking database integrity
• Improving performance by rebuilding indexes

Automating administrative tasks

• Scheduling repeating jobs
• Implementing a backup strategy

Monitoring SQL Server resources

• Displaying current activity and expensive queries
• Running reports with Management Studio

Securing the database infrastructure

• Authenticating SQL Server and Windows logins
• Assigning users to database and server roles
• Granting or revoking permissions

Connecting to Microsoft Office

• Pulling data from SQL Server into Excel
• Creating a front-end interface with Access

Transferring business information

• Importing and exporting data from text files
• Migrating an Access database to SQL Server

Extracting data with Integration Services

• Transforming and cleaning data
• Loading data into a data warehouse

Information delivery with Reporting Services

• Designing reports quickly with the Report Builder
• Deploying reports for centralized access

Aggregating data with Analysis Services

• Building multidimensional cubes
• Predicting trends with data mining

Developing a Business Intelligence strategy

• Leveraging Excel and Power Pivot
• Transferring data into knowledge with BI functionality