Designing and Implementing SQL Server Tables and Views

https://www.pluralsight.com/courses/sqlserver-tables-view-designing-implementing 

by Gerald Britton

Course Overview

  1. Course Overview

Introducing Tables and Views

  1. Version Check
  2. Introduction
  3. Relational Database Foundations
  4. Azure Data Studio Demo
  5. Business Problem: Bob's Shoes
  6. Course Overview

Designing and Implementing Tables
  1. Introduction
  2. Creating Identifiers
    • table names must start with a letter, underscore, @ or #
      • it cannot be longer than 128 characters
  3. Using Naming Conventions and The Mice!
  4. Using Character Data Types
    • char(n): Use it if most of your columns will have the same or mostly the same length or if the length is less than 3.
  5. Using Integer Types
  6. Using Decimal Types
  7. Using Date and Time Types
  8. Demo 1: Creating the Order Tracking Table
    • Using schemas for user tables is a good practice.
      • they're great for managing security and granting and restricting access.
    • you can force a computed column to be persisted by using the PERSISTED keyword when defining the column
  9. Using Collations
    • All character data has some collation
    • collation is used for sorting rules, case, and accent sensitivity
  10. Demo 2: Using Collations
    • shows query to get collation for the server, the database, for each field of a table
    • show how to set collation for a column
  11. Summary
Improving Table Design Through Normalization
  1. Introduction
  2. Identifying Problems with Unnormalized Data
  3. Setting Objectives for Normalization
  4. Demo 1 - Discovering Anomalies with Unnormalized Data
  5. Understanding First Normal Form
  6. Demo 2 - Transforming the Design to INF
  7. Understanding Second Normal Form
  8. Demo 3 - Transforming the Design to 2NF
  9. Understanding Third Normal Form
  10. Demo 3 - Transforming the Design to 3NF
  11. Considering Other Normal Forms
  12. Summary
Ensuring Data Integrity with Constraints
  1. Introduction and Overview
  2. Using NULL and DEFAULT Constraints
  3. Demo I - NULL and DEFAULT Constraints
    • you cannot alter a constraint, you have to drop and recreate it
  4. Implementing the PRIMARY KEY Constraint
    • SQL Server implements the PRIMARY KEY constraint with a backing index
    • There can be only one clustered index per table,
    • If a table has no clustered index, it is called a heap.
  5. Using Index Types and the UNIQUE Constraint
    • unique constrains allow for NUlls but there can only be one null
  6. Demo 2 - Implementing PRIMARY KEY and UNIQUE Constraints
  7. More About Foreign Key Constraints
  8. Demo 3 - Using FOREIGN KEY Constraints
    • shows example of UPDATE SET NULL
    • creating a backing index for a foreign key is recommended
  9. Introducing CHECK Constraints
  10. Demo 4 - Using CHECK Constraints
    • shows various example of using check constraint
      • table constraint where you don't want two columns to have the same value
      • check for specific values; CHECK (cust IN ('US','CA'))
      • you can use a scalar function that returns a Boolean with a check constraint 
  11. Options for Defining CHECK Constraints
  12. Summary
Designing View to Meet Business Requirements
  1. Introducing Views
  2. Overview and Motivation
  3. Reviewing the Bobs Shoes Order System Design
  4. Demo 1 - Creating a View to Produced a List of Customers
  5. Demo 2 - Using WITH SCHEMABINDING
    • WITH SCHEMABINDING
      • Base tables cannot be changed
      • you must use two part name (dbo....)
      • All referenced objects must be in the same database
  6. Working with Updateable Views
    • Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
  7. Demo 3 - Updating Tables using Views
  8. Summary
Implementing Indexed Views
  1. Introducing Indexed Views
    • An indexed view is a persisted object stored in the database in the same way that table indexes are stored
    • there must always be a clustered index on a indexed view
  2. Requirements for Indexed Views
    • a view must be deterministic 
  3. Demo I - Determining Determinism
    • Query to show if columns are deterministic and precise
  4. YMIVR - Yet More Indexed View Requirements
    • Forbidden T-SQL Elements
      • COUNT, ROWSETs, OUTER JOINS
      • Derived tables, self-joins, sub queries
      • DISTINCT, TOP, ORDER BY
      • UNION, EXCEPT, INTERSECT
      • MIN, MAX, PIVOT, UNPIVOT
  5. Demo 2 - Indexing the Customer List View
    • if you use EXPAND VIEWS, it tells SQL Server to ignore any indexes created for that view
  6. Demo 3 - Adding a Nonclustered Index and Views with Aggregates
    • If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*)
  7. Summary
Implementing Partitioned Views
  1. Introducing Partitioned Views
  2. Outlining a Partitioned View
  3. Reviewing the Requirements and Restrictions
  4. Demo 1 - Building a Partitioned View for the Orders Table
  5. Designing and Updating Partitioned Views
  6. Summary
Summary
  1. Wrapping Up
  2. Designing Tables
  3. Sqlserver Tables View Designing Implementing
  4. Considering Other Table Types
  5. Looking at Other Data Types
  6. Finishing Strong!

Comments

Post a Comment

Popular posts from this blog

Angular Routing and Navigation Playbook

Image
https://www.pluralsight.com/courses/angular-routing-navigation-playbook by Lara Newsom Course Overview Course Overview Course Introduction and Code Examples Course Introduction and Code Examples Basic Angular Routing Setup Introduction to Angular Routing Discussion: Angular RouterModule Exports Demo: Bootstrap Angular RouterModule Discussion: Declaring Routes angular has tow route path match strategies: prefix based or full match prefix is the default Demo: Declaring Default and Home Routes two options for declaring a default route: {path: ' ', componenet: DefaultComponent} {path: ' ', redirectTo: 'home', pathMatch: 'full'} Discussion: Child Routes and Route Order Demo: Declaring a Wildcard Route wild card routes is: path: '**' to enabled logging all navigation events to the console, enable tracing Demo: Hooking into Router Events Routing in Standalone Component Applications Introduction to Standalone Components and Routing Demo: Bootstrapping Ro...
Read more

Working with Files in C# 10

https://www.pluralsight.com/courses/c-sharp-10-working-files by Jason Roberts Course Overview Course Overview Sandbox Managing Files and Directories Introduction Creating a New Console App Checking if a File Exists you can use to check if file path is an absolute path: Path.IsPathFullyQualified Getting the Parent Directory of a Path Checking if a Directory Exists and Creating New Directories Copying a File Moving a File Getting the File Extension from a File Name Checkpoint 01- Get the Parent Directory for a Path Changing a Filename Extension Checkpoint 02 - Get the Extension Part of a Filename Deleting a Directory Enumerating All the Files in a Directory Summary Exercise 01- Copying and Deleting Files Monitoring the File System for Changes Introduction Introducing the FileSystemWatcher Class The IntemalBufferSize Property Configuring Notification Filters Additional Properties Refactoring to Use a FileSystemWatcher use FileSystemWatcher to watch for file changes in a folder Checkpo...
Read more

Mastering Git

https://app.pluralsight.com/library/courses/master-git/table-of-contents by Paolo Perrotta Module 1: Course Overview Course Overview Module 2: The Four Areas: Introduction Version Check About This Training A Note About the Command Line Introducing the Four Areas The Working Area The Repository The Index Module 3: The Four Areas: Basic Workflow Revisiting the Basics Moving Data to the Right Moving Data to the Left Removing Files Renaming Files A Quick Summary Module 4: The Four Areas: Git Reset Understanding Reset A Reset Example More Reset Examples Module 5: The Four Areas: Advanced Tools Stashing Data Solving Conflicts Working with Paths Committing Parts of a File Introducing Switch and Restore Git Is a Toolbox Module 6: History: Exploring the Past Becoming a History Surgeon Referencing Commits to show changes in a commit: git show HEAD to show changes in a commit parent: git show HEAD^ Tracking Changes in History Browsing the Log Module 7: History: Fixing Mistakes Reviewing the Golde...
Read more