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
• Building the Database

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
• Leveraging Server Programmability

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
• Administering SQL Server Databases

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
• Integrating External Applications

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
• Implementing Business Intelligence Solutions

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