Indexing on Index Cards This is part 1 of a series to help people better understand storage, caching, and ultimately indexing in SQL Server. The concepts are applicable to any modern relational database, and most non-relational databases. This video covers caching, logical and physical IO, dirty pages, and checkpointing.
Views: 3612 Database by Doug
Second in the series of "Indexes on Index Cards", this video covers clustered index read operations and how the cache works on these operations in a relational database. Various queries and cache situations are covered, pointing out the logical and physical for each query and situation.
Views: 1576 Database by Doug
Short videos showing the difference in the RANK(), DENSE_RANK(), and ROW_NUMBER() ranking functions.
Views: 295 Database by Doug
Get the code here: https://douglaskline.blogspot.com/2018/10/time-zones-and-datetimeoffset-data-type.html
Views: 189 Database by Doug
Making sure that group by specifies a unique grouping, and how to specify multi-column groups.
Views: 636 Database by Doug
Basic use of the ANY and ALL comparison modifiers in Structured Query Language. Comparison operators (equals, less than, etc.) which compare one single value with another single value can be modified with ANY or ALL to compare one single value with all the values in a single-column table. Get the code here: https://douglaskline.blogspot.com/2018/10/using-any-and-all-in-sql.html
Views: 175 Database by Doug
Intermediate level SQL on Relational Division using GROUP BY, COUNT, HAVING, and a correlated subquery or common table expression (CTE). A simple example, stripped-down example of Job Applicants with certain Skills, and Jobs that require certain skills is provided to teach the concept of relational division. Here is the script to create the example JobSkills database, with data. https://douglaskline.blogspot.com/2018/11/jobskills-database-schema-and-data-for.html Here is the code used in the example. https://douglaskline.blogspot.com/2018/11/understanding-relational-division-in-sql.html
Views: 289 Database by Doug
The default results grid font in SSMS is 8 point, which makes it hard to read for demonstrations on a projector (and for youtube videos). Here's how to make it bigger.
Views: 269 Database by Doug
How to use the ranking functions ROW_NUMBER(), RANK(), and DENSE_RANK().
Views: 317 Database by Doug
Beginner video on Foreign Keys in relational databases. Foreign keys allow for "lookups" between two tables. To ensure that these "lookups" are valid, we can enforce referential integrity, which makes sure that all foreign key values actually exist in the primary key table. Although referential integrity is specified as a constraint on the foreign key table, both tables involved check data changing operations to ensure all foreign key values are always valid. Primary Keys are always UNIQUE and NOT NULL, but Foreign Keys are generally not UNIQUE, and can be NULL. See the full code here: http://douglaskline.blogspot.com/2018/10/foreign-keys-in-relational-databases.html
Views: 135 Database by Doug
Beginner video - an introduction to primary keys in relational databases. Primary Keys need to be UNIQUE and NOT NULL, but not really anything else. This makes sure that all records are accessible by at least one field (the primary key). See the code here: http://douglaskline.blogspot.com/2018/10/primary-keys-in-relational-databases.html
Views: 139 Database by Doug
A brief beginner's introduction to the DISTINCT operator in SQL. DISTINCT creates a set of unique records, based on field values. In other words, duplicates records, based on field values, are removed. DISTINCT is often used with COUNT, to achieve a count of distinct values. Be aware that NULL is treated as a distinct value in terms of records, but COUNT ignores NULLs. See the full code here: https://douglaskline.blogspot.com/2018/10/using-distinct-in-sql.html
Views: 90 Database by Doug
A big picture look at the model building process for data analytics, statistics, data science, etc. In honor of a great professor I had in PhD school! Table of Contents: 00:00 - Introduction 00:00 - Marker 1 00:10 - Slide 2 00:28 - Slide 3 00:57 - Slide 4 01:17 - Slide 5 01:41 - Slide 6 02:03 - Slide 7 02:28 - Slide 8 02:58 - Slide 9 03:21 - Slide 8 03:27 - Slide 9 03:29 - Slide 10 03:44 - Slide 11 03:46 - Slide 12 03:55 - Slide 13 04:27 - Slide 14 05:54 - Slide 15 06:16 - Slide 16 06:41 - Slide 17 07:04 - Slide 18 08:13 - Slide 19 08:34 - Slide 20 09:26 - Slide 21
Views: 27 Database by Doug
Intermediate level video on Structured Query Language. The IN and EXISTS operators are sometimes viewed as "equivalent". They serve a similar purpose and are sometimes used to produce the same records. However, their underlying mechanisms are quite different. This example using the Northwind database demonstrates how NULLs are treated differently when using NOT IN versus NOT NULL.
Views: 76 Database by Doug
SwitchOffset is a function that will shift a time in one time zone to the correct time in another time zone. It takes a DATETIMEOFFSET data type, and produces a DATETIMEOFFSET data type. Get the code here: https://douglaskline.blogspot.com/2019/01/the-switchoffset-function.html
Views: 15 Database by Doug