SQL Server Certification: Developing SQL Databases

Discover how SQL Server evolved from a relational database to a full data platform, covering data stores, data models, integration, and visualization for business intelligence.

Learn the fundamentals of databases as collections of related data, how computer-based systems improve access, storage, and retrieval, and the design and indexing concepts that shape SQL Server databases.

Explore OLTP and OLAP structures, from fast transactional inserts to efficient aggregated queries, and how data warehouses copy OLTP data for scalable business intelligence.

Explore Transact-SQL, the proprietary extension of the SQL standard used to create, manage, and query Microsoft SQL Server databases. Understand its role among SQL flavors and cross-database relevance.

Learn how to obtain SQL Server 2016 for database development by choosing developer or evaluation editions, downloading the ISO, mounting it, and installing via the setup wizard.

Learn to install SQL Server 2016 step by step, mounting the ISO, running setup, and selecting database engine with Windows authentication. Includes prerequisites, updates, instance naming, and progress to completion.

Install SQL Server Management Studio (SSMS) separately from SQL Server, download from Microsoft, run as administrator, and complete a quick 5–10 minute setup.

Attach the Adventureworks 2012 MDF to your SQL Server 2016 instance via SQL Server Management Studio after copying the MDF into the data folder.

Identify server wide management tools installed with SQL Server 2016 and use the Installation Center to run the Features Discovery report, verifying the database engine, SSMS, and integration services.

Create and configure a new SQL Server database using Transact-SQL or the graphical tools in SQL Server Management Studio; explore options such as file groups, initial size, and recovery model.

Explore the four core database options in SQL Server—data file locations, database size and growth, and file groups—and learn how separating the transaction log from data files boosts performance.

Discover how database tables organize related data with rows and columns, enforce data integrity through data types, and prevent duplicate rows and excessive data as tables grow.

Explore the seven data type categories in SQL Server and learn to choose exact numerics, date and time, character and Unicode strings, and binary types to optimize memory usage.

Choose data types for each table column carefully to balance value range, memory efficiency, and supported operations. Use visuals to understand storage, avoid overflow, and minimize wasted space.

Explore normalization in sql databases to organize data, reduce redundancy, and improve integrity by applying normal forms, splitting data into related tables, and using joins.

Explore how normalized relational databases, including first through third normal forms, use tables and foreign key constraints to enforce referential integrity, prevent orphaned data, and manage relationships.

Explore how SQL Server schemas organize tables, stored procedures, and views into namespaces and simplify security via schema-level permissions.

Explore table design basics in sql server by examining tables as securable objects of related columns, defining attributes, row composition, data types, nullability, and creating tables with t-sql.

Learn to create tables with Transact-SQL in SQL Server Management Studio using the basic create table syntax, defining columns, data types, and nullability.

Learn to create tables graphically in SQL Server Management Studio without writing Transact-SQL code, using object explorer, diagram tools, and properties, with options like memory-optimized, temporal, external, and file tables.

Explore identity columns in SQL Server, which generate unique sequential values using seed and increment for each new row, supporting primary keys, foreign keys, and global unique identifiers.

Implement identity columns using a globally unique identifier with NEWID(), including default values and not null constraints, and recognize GUIDs as surrogate keys for cross-database joins.

Use alter table to adapt relational OLTP databases by adding or dropping columns and managing constraints, informed by transact-sql and schema concepts to avoid costly production changes.

Explore how altering tables by adding a column can reorder columns behind the scenes, creating a new table, copying data, and risking production performance.

Explore data integrity in SQL Server by examining domain, entity, and referential integrity, and learn how data types, constraints, and keys guard data accuracy and table relationships.

Explore SQL Server constraints—primary key, foreign key, unique, check, and default—and how to define or alter them to enforce data integrity, including table-level and column-level checks.

Understand how primary keys uniquely identify rows and how foreign keys enforce referential integrity between tables. Grasp normalization's role in data division, composite primary keys, and unique constraints.

Explore adding constraints using create and alter statements for primary, unique, and foreign keys in sql server. See how composite primary keys, clustered indexes, and constraint behaviors work across tables.

Build two related tables with a primary key and a foreign key, using an identity column and defaults, then illustrate the relationship with a database diagram.

Explore how file groups in SQL Server manage data placement across multiple physical disks to reduce contention, enable concurrent reads and writes, and spread large tables.

Learn how table partitions in SQL Server divide large tables by rows using a partition column, enabling partition elimination and targeted maintenance for faster queries and backups.

Explore SQL Server 2016 temporal tables, a system versioned design that automatically preserves history via a history table for auditing, forensics, and recovery.

Explore temporal tables in SQL Server 2016, creating a temporal table with a history table and system versioning to automatically track changes.

Modify a temporal table, verify updates, and review the history table for start and end times, then use as-of queries to view the table state at a specific moment.

Explore memory optimized tables in SQL Server, learn about in-memory OLTP, optimistic locking, native compiled stored procedures, and cross-container transactions to boost performance up to 5–20x.

Learn to implement in-memory tables by configuring a memory optimized file group, creating memory optimized tables, and using natively compiled stored procedures for fast inserts, updates, and deletes.

Learn to generate transact-sql scripts for database objects and entire databases in SQL Server Management Studio, including schema and optional data, using the Generate Scripts wizard.

Explore how SQL Server uses indexes to speed data retrieval, compare clustered and nonclustered indexes, and see how indexing a name column improves searches like 'chain ring bolts'.

Explore how clustered and non-clustered indexes shape data storage and performance in SQL Server, including heaps, composite keys, primary keys, and max index sizes.

Explore clustered vs non-clustered indexes, how non-clustered index structures enable fast searches but complicate updates, and guidelines for using them on columns with high distinct values.

Learn how covered indexes, a composite non-clustered index, speed up critical queries by including all columns from the where clause in the index, avoiding extra disk reads and pointer lookups.

Explore creating and managing standard row-based indexes in SQL Server using Transact-SQL or SQL Server Management Studio. Build clustered and non-clustered indexes, including composite options, in AdventureWorks 2012.

Discover columnstore indexes in SQL Server, a column-based, highly compressible alternative to row-based indexes that accelerates analytics and real time operational analytics on large data.

Explore how SQL Server manages and defragments indexes, contrasting automatic maintenance with manual maintenance using T-SQL or SSMS, and apply reorganize or rebuild based on index fragmentation metrics from sys.dm_db_index_physical_stats.

Rebuild or reorganize indexes to defragment databases using SSMS or T-SQL, guided by fragmentation levels (10–30% reorganize, over 30% rebuild), with maintenance plans and SQL Server Agent for automation.

Create and use views to present only the needed data from complex, normalized tables, improving data access, security, and abstraction for end users and report writers.

Create a sql server view by joining production.product and purchasing.product_vendor in Adventureworks 2012, selecting product name, color, min order quantity, and average lead time.

Discover how updatable views in SQL Server let you modify underlying tables, under conditions like referencing a single base table and avoiding aggregates, with instead of triggers as a workaround.

Explore indexed views in SQL Server, where a view gains a unique clustered index, stores data on disk, and can speed queries via the optimizer, especially with joins and aggregations.

Explore how transactions group multiple database actions into a single unit of work, ensuring atomicity, consistency, isolation, and durability, with commit and rollback governing changes.

Compare implicit and explicit transactions in SQL Server, showing how implicit mode auto-commits each statement and affects the transaction log. Learn how explicit transactions require manual commit or rollback.

Learn how the SQL Server transaction log records every data modification, enabling rollback and point-in-time recovery, and compare full, bulk logged, and simple recovery models.

Learn to use explicit transactions in SQL Server with begin tran, commit, and rollback, including safe updates and transaction log awareness on the AdventureWorks person table.

Explore savepoints in transactions to rollback only portions of work, not the whole transaction, using a simple save transaction and rollback example with a tran test table.

Learn to use save points in SQL Server to selectively rollback nested transactions with save tran and save point one. See how rolling back a subtransaction leaves other changes intact.

Understand stored procedures, a secure, persisted database object in SQL Server that encapsulates T-SQL logic for reusable, efficient code and controlled execution with granted permissions.

Create a basic stored procedure in SQL Server Management Studio using AdventureWorks 2012, then extend to a get orders procedure joining header and detail and summing quantities and line totals.

Add input parameters to stored procedures to restrict results. Alter the get orders procedure to accept a product ID with a default value, yielding targeted data.

Learn to create and use output parameters in sql server stored procedures to return an inventory total by summing current inventory.

Discover how table valued parameters pass multiple rows to a stored procedure, reducing locks and round trips, with a five-step setup and hands-on code example.

Create and use a table-valued parameter with a user-defined type and a stored procedure, pass data, and retrieve two result sets showing inventory above and below 950.

Learn part 1 of SQL Server return status in stored procedures. Zero means success; negative values indicate errors, and the difference between return and output parameters is discussed.

Learn how to manage return status in a stored procedure by updating the person table, capturing and printing the status, and returning the row count to calling code.

Explore how natively compiled stored procedures differ from standard stored procedures, their use with memory optimized tables, and how the native compilation advisor in SQL Server Management Studio aids evaluation.