SQL Server Database Design
Course
Inhouse
Description
-
Type
Course
-
Methodology
Inhouse
This course provides students with the technical skills required to program a database solution by using Microsoft SQL Server.
About this course
Logical and physical database design. Data integrity concepts. Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many). How data is stored in tables (rows and columns). Knowledge of basic SQL syntax (SELECT, UPDATE, and INSERT statements).
Reviews
Course programme
This course provides students with the technical skills required to program a database solution by using Microsoft SQL Server.
Prerequisites
- Logical and physical database design.
- Data integrity concepts.
- Relationships between tables and columns (primary key and foreign key, one-to-one, one-to-many).
- How data is stored in tables (rows and columns).
- Knowledge of basic SQL syntax (SELECT, UPDATE, and INSERT statements).
Objectives
- Delegates will be able to :
- Describe the elements of SQL Server
- Design a SQL Server enterprise application architecture
- Describe the conceptual basis of programming in Transact-SQL
- Create and manage databases and their related components
- Implement data integrity by using the IDENTITY column property, constraints, defaults, rules, and unique identifiers; plan for the use of indexes
- Create and maintain indexes; create, use, and maintain data views
- Implement user-defined functions; design, create, and use stored procedures
- Create and implement triggers
- Program across multiple servers by using distributed queries, distributed transactions, and partitioned views
- Optimize query performance
- Analyze queries
- Manage transactions and locks to ensure data concurrency and recoverability.
- Comprehensive colour course manual and exercises
- Use of a Modern Training suite with 19 inch flat screen monitors
- 1 delegate per computer
- Lunch (When at our training venue)
- Refreshments (When at our training venue)
- Relaxed refreshment area at our training room
Introduction to SQL server
- Server components
- Client server communication process
- Enterprise manager
- SQL query analyzer
- SQL server services
- MSSQL server service
- SQL server agent service
- Background to its development
- Accessing the data in SQL server databases
- Transact-SQL
- XML
- MDX
- OLE DB and ODBC APIs
- Client components
- What is a role
- Database users
- Objects in SQL server
- Retrieval of metadata
- System tables
- Databases
- How to execute T-SQL statements
- SQL server object names
- Data manipulation language statements
- Data definition language statements
- Data control language statements
- Transact-SQL
- OSQL
- Execution plans
- SQL query analyzer
- Pages and extents
- Data storage in Sql server
- Dropping/deleting a database
- Shrinking a database or file
- Managing data and log file growth
- Filegroups in SQL server
- Setting database options
- The transaction log
- Creating a database
- Database design
- Creating a unique id column (identity)
- Primary key
- Adding/dropping a column
- Deleting a table
- Data in rows
- User defined data types
- SQL server data types
- Creating a table
- Creating a database diagram
- Rules/restrictions of use of constraint types
- Creating constraints
- Constraints
- Referential integrity
- Entity integrity
- Domain integrity
- A unique index
- How SQL server stores data
- Why not to index
- Using a clustered and nonclustered index
- Maintaining the indexes
- Page splits
- Why index
- Creating indexes
- Recommended indexing practices
- A composite index
- Index options
- Index maintenance
- Statistics
- Viewing statistics
- The index tuning wizard
- Indexing guidelines
- Partitioned views
- Indexed views
- Modifying data through the views
- Hiding the structure of views
- View definition information
- Different owners of objects
- Checking dependencies
- Dropping a view
- Modifying a view
- Creating a view
- Why have views
- What is a view
- A custom error message
- Return statement
- Error messages
- Running extended stored procedures
- Using a parameter
- Modifying stored procedures.
- Stored procedure guidelines
- Viewing information on stored procedures
- Creating a stored procedure
- Advantages of stored procedures
- How a stored procedure caches
- Types of stored procedures
- Recommended practices with functions
- SCALAR functions
- Deleting a function
- Changing a function
- Some function restrictions
- Creating a function
- Considerations for the use of triggers
- Recursive triggers
- Nested triggers
- Controlling the trigger fire order
- How triggers work
- Deleting triggers
- Disabling/enabling a trigger
- Modifying triggers
- Creating a trigger
- Considerations before using triggers
- Why use them
- What are triggers
- Partitioned views
- Managing distributed transactions
- A linked server
- Distributed queries
- Turning on server trace and statistics
- Overriding the query optimizer
- Index creation guidelines
- Indexing strategies
- The bookmark lookup
- Viewing the execution plan graphically
- Data on the execution plan
- Nested Loop, Merge Join, Hash Join
- Queries that use JOINS
- Queries that use AND, OR
- Deadlocks
- SQL server locks
- Turning implicit transactions on/off
- Transaction guidelines
- Transaction recovery and checkpoint
- Restoring from backup
- Performing a backup
SQL Server Database Design