title
Derived tables and common table expressions in sql server Part 48

description
In this video we will learn about derived tables and common table expressions. CTE stands for common table expressions. We will also find the differences between, views, temporary tables, table variable, derived tables and cte's Text version of the video http://csharp-video-tutorials.blogspot.com/2012/09/derived-table-and-cte-in-sql-server.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-48-derived-tables-cte.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'Derived tables and common table expressions in sql server Part 48', 'heatmap': [{'end': 435.33, 'start': 417.209, 'weight': 0.766}, {'end': 505.978, 'start': 483.001, 'weight': 0.756}, {'end': 688.211, 'start': 650.802, 'weight': 0.73}, {'end': 842.287, 'start': 810.709, 'weight': 0.753}, {'end': 898.856, 'start': 869.014, 'weight': 0.882}], 'summary': 'Covers sql server views to join tables, create a view to count employees per department, and select departments with two or more employees, benefits of temporary tables over views, types and scope of temporary tables; temporary tables, table variables, and derived tables usage, cte in sql server, syntax, usage, similarities to derived tables, temporary nature, and applicability within select, insert, update, delete, or create view statements.', 'chapters': [{'end': 409.523, 'segs': [{'end': 28.581, 'src': 'embed', 'start': 0.009, 'weight': 1, 'content': [{'end': 1.791, 'text': 'Hello! Welcome to Prefium Technologies.', 'start': 0.009, 'duration': 1.782}, {'end': 2.692, 'text': 'I am Venkat.', 'start': 2.091, 'duration': 0.601}, {'end': 3.893, 'text': 'This is part 48 of SQL Server.', 'start': 2.932, 'duration': 0.961}, {'end': 9.467, 'text': "In this session, we'll learn about derived tables and common table expressions.", 'start': 5.504, 'duration': 3.963}, {'end': 17.273, 'text': "We'll compare these with other constructs that are available in SQL Server, like views, table variables, local and global temporary tables.", 'start': 9.827, 'duration': 7.446}, {'end': 18.954, 'text': "Let's start with an example.", 'start': 17.753, 'duration': 1.201}, {'end': 23.657, 'text': 'I have TBL department table here, which has got department ID and department name columns.', 'start': 18.974, 'duration': 4.683}, {'end': 28.581, 'text': 'TBL employee table here, which has got ID name, gender, and department ID columns.', 'start': 24.057, 'duration': 4.524}], 'summary': 'Part 48 of sql server covers derived tables, common table expressions, and comparisons with other sql constructs.', 'duration': 28.572, 'max_score': 0.009, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA9.jpg'}, {'end': 68.063, 'src': 'embed', 'start': 39.908, 'weight': 0, 'content': [{'end': 47.173, 'text': 'but then your list should only include those departments which has got two or more employees.', 'start': 39.908, 'duration': 7.265}, {'end': 54.618, 'text': 'and obviously to achieve this output we have several ways, but then we will see how to do that using views.', 'start': 47.173, 'duration': 7.445}, {'end': 56.559, 'text': 'okay, so we are creating a view here.', 'start': 54.618, 'duration': 1.941}, {'end': 64.858, 'text': 'create view name as we are selecting the department name, department ID and count of star,', 'start': 56.559, 'duration': 8.299}, {'end': 68.063, 'text': 'which would count the number of employees within that department,', 'start': 64.858, 'duration': 3.205}], 'summary': 'Create a view to display departments with two or more employees using department name, id, and employee count.', 'duration': 28.155, 'max_score': 39.908, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA39908.jpg'}, {'end': 175.574, 'src': 'embed', 'start': 111.253, 'weight': 4, 'content': [{'end': 116.215, 'text': 'so obviously we have those columns in the view department name and total employees.', 'start': 111.253, 'duration': 4.962}, {'end': 126.597, 'text': 'all you have to do is select those columns from that view and then select only those departments which has got total employees count greater than or equal to 2,', 'start': 116.215, 'duration': 10.382}, {'end': 128.017, 'text': 'which will give us this output.', 'start': 126.597, 'duration': 1.42}, {'end': 139.186, 'text': "Okay, but then let's say, if we are using this view only within this query, then it doesn't really make sense to create a view for that purpose.", 'start': 129.021, 'duration': 10.165}, {'end': 143.867, 'text': "That's why we have other constructs in SQL Server, which we'll be exploring next.", 'start': 139.546, 'duration': 4.321}, {'end': 146.308, 'text': 'So views get saved in the database.', 'start': 144.247, 'duration': 2.061}, {'end': 149.682, 'text': 'and can be available to other queries and stored procedures as well.', 'start': 146.741, 'duration': 2.941}, {'end': 156.545, 'text': "So if you are using this view in other stored procedures and in other queries, then that's fine.", 'start': 149.702, 'duration': 6.843}, {'end': 166.47, 'text': "But then if you're using this just one time in this query, then it doesn't really make sense to have this view created.", 'start': 156.886, 'duration': 9.584}, {'end': 174.574, 'text': 'We can make use of other constructs that are available in SQL Server, like CTE derived tables, temp tables, table variables, et cetera,', 'start': 166.95, 'duration': 7.624}, {'end': 175.574, 'text': "which we'll be talking now.", 'start': 174.574, 'duration': 1}], 'summary': 'Select department name and total employees where count >= 2. views vs. other sql constructs.', 'duration': 64.321, 'max_score': 111.253, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA111253.jpg'}, {'end': 409.523, 'src': 'embed', 'start': 386.982, 'weight': 6, 'content': [{'end': 395.092, 'text': 'So local temporary tables are visible in the current session, only in the current session, and can be shared between nested stored procedure calls.', 'start': 386.982, 'duration': 8.11}, {'end': 404.099, 'text': 'Whereas global temporary tables are visible to all the sessions and are destroyed when the last connection referencing that table is closed.', 'start': 395.653, 'duration': 8.446}, {'end': 406.661, 'text': 'That global temporary table is basically closed.', 'start': 404.299, 'duration': 2.362}, {'end': 409.523, 'text': "Let's look at this in action.", 'start': 408.442, 'duration': 1.081}], 'summary': 'Local temp tables are session-specific, while global temp tables are visible to all sessions and are destroyed when the last connection referencing them is closed.', 'duration': 22.541, 'max_score': 386.982, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA386982.jpg'}], 'start': 0.009, 'title': 'Sql server views and temporary tables', 'summary': 'Covers the use of views to join tables, create a view to count the number of employees in each department, and select only departments with two or more employees. it also discusses the benefits of using temporary tables over views for single-use queries and explains the types and scope of temporary tables available in sql server.', 'chapters': [{'end': 128.017, 'start': 0.009, 'title': 'Sql server: derived tables and common table expressions', 'summary': 'Covers the use of views to join tables, create a view to count the number of employees in each department, and select only departments with two or more employees.', 'duration': 128.008, 'highlights': ['The chapter explains creating a view to count the number of employees in each department and selecting only departments with two or more employees, achieving the desired output.', 'The session covers comparing derived tables and common table expressions with other constructs available in SQL Server, like views, table variables, local and global temporary tables.', 'The speaker provides a detailed explanation on creating a view to count the number of employees within each department using SQL Server, specifically emphasizing the need to group by department name and department ID.', 'The chapter introduces the use of views to compute the desired result, simplifying the process of selecting the department name and total employees, and filtering departments with at least two employees.', 'The speaker highlights the ease of using the created view to compute the desired result by selecting the department name and total employees and filtering departments with at least two employees, achieving the expected output.']}, {'end': 409.523, 'start': 129.021, 'title': 'Views vs. temporary tables in sql server', 'summary': 'Discusses the use of views and temporary tables in sql server, highlighting the benefits of using temporary tables over views for single-use queries and explaining the types and scope of temporary tables available in sql server.', 'duration': 280.502, 'highlights': ['Views get physically stored in the database and can be available to other queries and stored procedures.', 'Temporary tables allow for the same logic as views but are more suitable for single-use queries, as demonstrated by creating a temporary table to achieve the same result as the view for a specific query.', 'Temporary tables in SQL Server include local and global types, each with different visibility and scope, with local temporary tables being visible only in the current session and global temporary tables being visible to all sessions.']}], 'duration': 409.514, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA9.jpg', 'highlights': ['The chapter explains creating a view to count the number of employees in each department and selecting only departments with two or more employees, achieving the desired output.', 'The session covers comparing derived tables and common table expressions with other constructs available in SQL Server, like views, table variables, local and global temporary tables.', 'The speaker provides a detailed explanation on creating a view to count the number of employees within each department using SQL Server, specifically emphasizing the need to group by department name and department ID.', 'The chapter introduces the use of views to compute the desired result, simplifying the process of selecting the department name and total employees, and filtering departments with at least two employees.', 'The speaker highlights the ease of using the created view to compute the desired result by selecting the department name and total employees and filtering departments with at least two employees, achieving the expected output.', 'Temporary tables allow for the same logic as views but are more suitable for single-use queries, as demonstrated by creating a temporary table to achieve the same result as the view for a specific query.', 'Temporary tables in SQL Server include local and global types, each with different visibility and scope, with local temporary tables being visible only in the current session and global temporary tables being visible to all sessions.', 'Views get physically stored in the database and can be available to other queries and stored procedures.']}, {'end': 820.534, 'segs': [{'end': 445.154, 'src': 'heatmap', 'start': 417.209, 'weight': 0.766, 'content': [{'end': 419.511, 'text': "So let's execute that.", 'start': 417.209, 'duration': 2.302}, {'end': 422.325, 'text': 'So we should have the temporary table created now.', 'start': 419.844, 'duration': 2.481}, {'end': 428.247, 'text': 'So obviously, when we say select star from the temporary table, you should see all the rows and columns.', 'start': 422.705, 'duration': 5.542}, {'end': 435.33, 'text': 'So select star from the temporary table, which here is temp employee count.', 'start': 428.267, 'duration': 7.063}, {'end': 438.711, 'text': "Since we have a single pound sign, it's a local temporary table.", 'start': 435.67, 'duration': 3.041}, {'end': 442.893, 'text': 'So we have spoken about temporary tables in a great detail in the previous sessions of this video series.', 'start': 438.731, 'duration': 4.162}, {'end': 445.154, 'text': "So if you're new, check those parts first.", 'start': 443.233, 'duration': 1.921}], 'summary': "Executing and viewing data from temporary table 'temp employee count' in the local context.", 'duration': 27.945, 'max_score': 417.209, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA417209.jpg'}, {'end': 507.639, 'src': 'heatmap', 'start': 483.001, 'weight': 0.756, 'content': [{'end': 489.826, 'text': "The structure is automatically inferred based on the SELECT statement that you're using here, because you're using SELECT and 2.", 'start': 483.001, 'duration': 6.825}, {'end': 494.53, 'text': "But then when you're using table variable here, we are defining the structure explicitly.", 'start': 489.826, 'duration': 4.704}, {'end': 501.554, 'text': 'So declare a variable, and this variable is of type table.', 'start': 494.87, 'duration': 6.684}, {'end': 505.978, 'text': "Obviously, if it's a table, a table will have columns and data types.", 'start': 501.935, 'duration': 4.043}, {'end': 507.639, 'text': "So you'll have to define them as well.", 'start': 505.998, 'duration': 1.641}], 'summary': 'Using select statement infers structure, while table variable defines structure explicitly.', 'duration': 24.638, 'max_score': 483.001, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA483001.jpg'}, {'end': 650.482, 'src': 'embed', 'start': 627.723, 'weight': 1, 'content': [{'end': 638.192, 'text': 'specify the column names and the data types and then insert into that table variable and then select whatever columns you want from that table variable.', 'start': 627.723, 'duration': 10.469}, {'end': 640.614, 'text': "So let's execute this.", 'start': 639.453, 'duration': 1.161}, {'end': 645.598, 'text': 'We should see the same output, except that we are using the table variable.', 'start': 641.195, 'duration': 4.403}, {'end': 650.482, 'text': "And the advantage of using table variable is that you don't have to drop it like temporary tables.", 'start': 645.979, 'duration': 4.503}], 'summary': 'Using table variables for flexibility and efficiency in sql queries.', 'duration': 22.759, 'max_score': 627.723, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA627723.jpg'}, {'end': 688.211, 'src': 'heatmap', 'start': 650.802, 'weight': 0.73, 'content': [{'end': 656.247, 'text': 'But there are several other differences as well between table variables and temporary tables.', 'start': 650.802, 'duration': 5.445}, {'end': 666.939, 'text': 'All right, so we can also achieve the same thing using derived tables.', 'start': 662.011, 'duration': 4.928}, {'end': 667.801, 'text': 'Now look at this.', 'start': 667, 'duration': 0.801}, {'end': 674.713, 'text': 'When we created a table variable, we will have to define the columns and the data types for a table variable.', 'start': 668.282, 'duration': 6.431}, {'end': 682.247, 'text': "If you are using a derived table, this table is derived, so you don't really define a table as such.", 'start': 676.522, 'duration': 5.725}, {'end': 683.708, 'text': 'You just give it an alias.', 'start': 682.267, 'duration': 1.441}, {'end': 688.211, 'text': 'So if you look at this, this is the query that we have been looking at all this while.', 'start': 684.048, 'duration': 4.163}], 'summary': 'Differences between table variables and derived tables explained', 'duration': 37.409, 'max_score': 650.802, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA650802.jpg'}, {'end': 760.833, 'src': 'embed', 'start': 728.893, 'weight': 2, 'content': [{'end': 735.682, 'text': 'okay, so now you treat this from here till here as a table.', 'start': 728.893, 'duration': 6.789}, {'end': 737.944, 'text': 'so you treat this as a table, employee count.', 'start': 735.682, 'duration': 2.262}, {'end': 747.452, 'text': 'so select department name, total employees from this employee count, where total employees is greater than or equal to 2.', 'start': 737.944, 'duration': 9.508}, {'end': 751.576, 'text': 'so here you know this is a derived table.', 'start': 747.452, 'duration': 4.124}, {'end': 760.833, 'text': 'this employee count is a derived table And derived tables are available only in the context of the current query, here the SELECT statement.', 'start': 751.576, 'duration': 9.257}], 'summary': 'Using a derived table, select department name and total employees where total employees are greater than or equal to 2.', 'duration': 31.94, 'max_score': 728.893, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA728893.jpg'}, {'end': 830.262, 'src': 'embed', 'start': 798.843, 'weight': 0, 'content': [{'end': 804.826, 'text': "And what you're doing from these three columns, you're selecting just department name and total employees columns.", 'start': 798.843, 'duration': 5.983}, {'end': 810.709, 'text': "And then you're filtering on that column, so where total employees is greater than or equal to 2.", 'start': 805.246, 'duration': 5.463}, {'end': 818.733, 'text': 'So obviously we only get those two rows where the employees count is greater than or equal to 2, which in this case is IT and HR,', 'start': 810.709, 'duration': 8.024}, {'end': 820.534, 'text': 'just like other examples that we have seen.', 'start': 818.733, 'duration': 1.801}, {'end': 830.262, 'text': 'And finally, we will see how to achieve the same thing using something called CTE, Common Table Expression.', 'start': 823.499, 'duration': 6.763}], 'summary': 'Selecting department names and total employees, filtering for count >= 2, it and hr departments, and using common table expression.', 'duration': 31.419, 'max_score': 798.843, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA798843.jpg'}], 'start': 410.904, 'title': 'Temporary tables and table variables', 'summary': 'Explains the usage of temporary tables, table variables, and derived tables in sql, demonstrating how to create and select from temporary tables, define and utilize table variables, and create and use derived tables to filter data based on specified conditions.', 'chapters': [{'end': 820.534, 'start': 410.904, 'title': 'Temporary tables and table variables', 'summary': 'Explains the usage of temporary tables, table variables, and derived tables in sql, demonstrating how to create and select from temporary tables, define and utilize table variables, and create and use derived tables to filter data based on specified conditions.', 'duration': 409.63, 'highlights': ['The chapter covers the creation and selection of data from temporary tables, demonstrating how to select specific columns and filter the results based on specified conditions, such as total employees greater than or equal to 2.', 'The explanation of table variables includes the explicit definition of the table structure, the declaration of the variable, the insertion of data into the table variable, and the selection of columns from the table variable with a focus on the advantage of not needing to drop the table variable after use.', 'The usage of derived tables is illustrated, highlighting the creation of a derived table without explicit definition of columns and data types, and the selection and filtering of data from the derived table based on specified conditions like total employees greater than or equal to 2.']}], 'duration': 409.63, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA410904.jpg', 'highlights': ['The chapter covers the creation and selection of data from temporary tables, demonstrating how to select specific columns and filter the results based on specified conditions, such as total employees greater than or equal to 2.', 'The explanation of table variables includes the explicit definition of the table structure, the declaration of the variable, the insertion of data into the table variable, and the selection of columns from the table variable with a focus on the advantage of not needing to drop the table variable after use.', 'The usage of derived tables is illustrated, highlighting the creation of a derived table without explicit definition of columns and data types, and the selection and filtering of data from the derived table based on specified conditions like total employees greater than or equal to 2.']}, {'end': 1071.535, 'segs': [{'end': 848.57, 'src': 'embed', 'start': 823.499, 'weight': 0, 'content': [{'end': 830.262, 'text': 'And finally, we will see how to achieve the same thing using something called CTE, Common Table Expression.', 'start': 823.499, 'duration': 6.763}, {'end': 834.164, 'text': 'We will be talking about CTEs in a great detail in the next session.', 'start': 830.562, 'duration': 3.602}, {'end': 836.685, 'text': 'This is just an introduction to CTE.', 'start': 834.464, 'duration': 2.221}, {'end': 842.287, 'text': 'So achieving the same output using a CTE, Common Table Expression.', 'start': 837.105, 'duration': 5.182}, {'end': 845.789, 'text': 'And CTEs are introduced in SQL Server 2005.', 'start': 842.347, 'duration': 3.442}, {'end': 848.57, 'text': 'So if you look at this one, this is, again, very simple.', 'start': 845.789, 'duration': 2.781}], 'summary': 'Introduction to achieving the same output using cte, introduced in sql server 2005.', 'duration': 25.071, 'max_score': 823.499, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA823499.jpg'}, {'end': 898.856, 'src': 'heatmap', 'start': 869.014, 'weight': 0.882, 'content': [{'end': 873.536, 'text': 'And then you can specify the columns that your CTE returns.', 'start': 869.014, 'duration': 4.522}, {'end': 875.558, 'text': 'This part is optional.', 'start': 874.297, 'duration': 1.261}, {'end': 881.902, 'text': "If this query is returning a unique column names, then you don't have to define this column list.", 'start': 876.118, 'duration': 5.784}, {'end': 886.032, 'text': 'But I have defined it here just to be clear.', 'start': 883.151, 'duration': 2.881}, {'end': 893.814, 'text': 'So this common table expression, employee count, is having three columns, department name, department ID, total employees.', 'start': 886.392, 'duration': 7.422}, {'end': 898.856, 'text': 'Where are these columns coming from? These columns are coming from the SELECT query.', 'start': 894.734, 'duration': 4.122}], 'summary': "Cte 'employee count' has 3 columns: department name, department id, total employees.", 'duration': 29.842, 'max_score': 869.014, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA869014.jpg'}, {'end': 934.582, 'src': 'embed', 'start': 902.277, 'weight': 1, 'content': [{'end': 903.417, 'text': 'We have the brackets here.', 'start': 902.277, 'duration': 1.14}, {'end': 906.358, 'text': 'So this is like a table.', 'start': 904.397, 'duration': 1.961}, {'end': 909.979, 'text': 'What is the name of this table? Employee count.', 'start': 907.158, 'duration': 2.821}, {'end': 913.652, 'text': 'And this is a CTE, so we use the with keyword.', 'start': 910.871, 'duration': 2.781}, {'end': 920.596, 'text': 'So usually the syntax for CTE is in such a way that you use the with keyword, with CTE name,', 'start': 913.672, 'duration': 6.924}, {'end': 925.318, 'text': 'the columns that your CTE returns as and your select query.', 'start': 920.596, 'duration': 4.722}, {'end': 930.961, 'text': 'Your select query actually determines what columns are being returned by the CTE.', 'start': 926.178, 'duration': 4.783}, {'end': 934.582, 'text': 'All right, so we are done.', 'start': 933.182, 'duration': 1.4}], 'summary': 'Cte named employee count used with select query in syntax.', 'duration': 32.305, 'max_score': 902.277, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA902277.jpg'}, {'end': 1015.87, 'src': 'embed', 'start': 957.988, 'weight': 2, 'content': [{'end': 964.091, 'text': 'And then what you can do is, since this is treated as a table now a common table expression.', 'start': 957.988, 'duration': 6.103}, {'end': 967.712, 'text': 'and this common table expression has got three columns, just like derived table,', 'start': 964.091, 'duration': 3.621}, {'end': 975.961, 'text': 'And out of those three columns we just want the department name and total employees columns from that CTE, which is employee count here,', 'start': 968.493, 'duration': 7.468}, {'end': 978.704, 'text': 'where total employees greater than or equal to 2..', 'start': 975.961, 'duration': 2.743}, {'end': 979.925, 'text': 'So we get the same output.', 'start': 978.704, 'duration': 1.221}, {'end': 982.147, 'text': "Let's look at that in action.", 'start': 979.985, 'duration': 2.162}, {'end': 987.112, 'text': "So that's the CTE.", 'start': 985.971, 'duration': 1.141}, {'end': 988.714, 'text': "So let's execute this.", 'start': 987.392, 'duration': 1.322}, {'end': 991.697, 'text': 'And we should get the same output.', 'start': 990.175, 'duration': 1.522}, {'end': 997.693, 'text': 'A CTE can be thought of as a temporary result set.', 'start': 994.009, 'duration': 3.684}, {'end': 998.854, 'text': "It's a temporary table.", 'start': 997.813, 'duration': 1.041}, {'end': 1007.603, 'text': 'Or you can also think of as a derived table that is defined within the execution scope of a single select, insert, update,', 'start': 999.174, 'duration': 8.429}, {'end': 1009.224, 'text': 'delete or create view statement.', 'start': 1007.603, 'duration': 1.621}, {'end': 1012.407, 'text': 'You can use the CTE even within a view.', 'start': 1010.165, 'duration': 2.242}, {'end': 1015.87, 'text': 'But this is.', 'start': 1014.97, 'duration': 0.9}], 'summary': 'Using cte to extract department names and total employees with a count of 2 or more.', 'duration': 57.882, 'max_score': 957.988, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA957988.jpg'}, {'end': 1062.692, 'src': 'embed', 'start': 1035.663, 'weight': 6, 'content': [{'end': 1044.266, 'text': 'And a CTA is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.', 'start': 1035.663, 'duration': 8.603}, {'end': 1049.828, 'text': 'So this is not being stored as a temporary table or a view.', 'start': 1044.586, 'duration': 5.242}, {'end': 1053.229, 'text': 'It only lasts for the duration of this query.', 'start': 1051.048, 'duration': 2.181}, {'end': 1057.05, 'text': "Outside the context of this query, the CTA doesn't really have any meaning.", 'start': 1053.569, 'duration': 3.481}, {'end': 1062.692, 'text': "We'll talk about CTAs in great detail in the next session of this video series.", 'start': 1058.851, 'duration': 3.841}], 'summary': 'Cta is similar to a derived table, lasts for the query duration.', 'duration': 27.029, 'max_score': 1035.663, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA1035663.jpg'}], 'start': 823.499, 'title': 'Using common table expression in sql server', 'summary': 'Explains how to use common table expression (cte) in sql server, introduced in sql server 2005, to achieve the same output as a regular query, providing an overview of its syntax and usage. it also discusses common table expressions (ctes) and their syntax, usage, and similarities to derived tables, highlighting the temporary nature and applicability within select, insert, update, delete, or create view statements, with a focus on the definition and usage of ctes.', 'chapters': [{'end': 902.097, 'start': 823.499, 'title': 'Using cte in sql server', 'summary': 'Explains how to use common table expression (cte) in sql server, introduced in sql server 2005, to achieve the same output as a regular query, providing an overview of its syntax and usage.', 'duration': 78.598, 'highlights': ['CTEs are introduced in SQL Server 2005, providing a new way to create temporary result sets for complex queries.', "Using the keyword 'with' followed by a name, CTEs can be defined to specify the columns and their data returned by the CTE.", "The CTE 'employee count' in the example has three columns: department name, department ID, and total employees, derived from the SELECT query.", 'The use of CTE is illustrated as an alternative method to achieve the same output as a regular query, providing a clear introduction to its syntax and purpose.']}, {'end': 1071.535, 'start': 902.277, 'title': 'Understanding common table expressions', 'summary': 'Discusses common table expressions (ctes) and their syntax, usage, and similarities to derived tables, highlighting the temporary nature and applicability within select, insert, update, delete, or create view statements, with a focus on the definition and usage of ctes.', 'duration': 169.258, 'highlights': ['The chapter explains the syntax for common table expressions (CTEs) and their usage, highlighting the with keyword and its role in defining CTEs, and the select query determining the columns returned by the CTE.', 'The temporary nature of CTEs is emphasized, likening them to derived tables and explaining their definition within the execution scope of a single select, insert, update, delete, or create view statement.', 'The discussion outlines the similarities between CTEs and derived tables, emphasizing their temporary nature and lack of storage as objects, clarifying their duration limited to the query execution scope.']}], 'duration': 248.036, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/FwcAkH8UyEA/pics/FwcAkH8UyEA823499.jpg', 'highlights': ['CTEs are introduced in SQL Server 2005, providing a new way to create temporary result sets for complex queries.', "Using the keyword 'with' followed by a name, CTEs can be defined to specify the columns and their data returned by the CTE.", "The CTE 'employee count' in the example has three columns: department name, department ID, and total employees, derived from the SELECT query.", 'The chapter explains the syntax for common table expressions (CTEs) and their usage, highlighting the with keyword and its role in defining CTEs, and the select query determining the columns returned by the CTE.', 'The use of CTE is illustrated as an alternative method to achieve the same output as a regular query, providing a clear introduction to its syntax and purpose.', 'The temporary nature of CTEs is emphasized, likening them to derived tables and explaining their definition within the execution scope of a single select, insert, update, delete, or create view statement.', 'The discussion outlines the similarities between CTEs and derived tables, emphasizing their temporary nature and lack of storage as objects, clarifying their duration limited to the query execution scope.']}], 'highlights': ['The chapter explains creating a view to count the number of employees in each department and selecting only departments with two or more employees, achieving the desired output.', 'The session covers comparing derived tables and common table expressions with other constructs available in SQL Server, like views, table variables, local and global temporary tables.', 'The speaker provides a detailed explanation on creating a view to count the number of employees within each department using SQL Server, specifically emphasizing the need to group by department name and department ID.', 'The chapter introduces the use of views to compute the desired result, simplifying the process of selecting the department name and total employees, and filtering departments with at least two employees.', 'The speaker highlights the ease of using the created view to compute the desired result by selecting the department name and total employees and filtering departments with at least two employees, achieving the expected output.', 'Temporary tables allow for the same logic as views but are more suitable for single-use queries, as demonstrated by creating a temporary table to achieve the same result as the view for a specific query.', 'Temporary tables in SQL Server include local and global types, each with different visibility and scope, with local temporary tables being visible only in the current session and global temporary tables being visible to all sessions.', 'CTEs are introduced in SQL Server 2005, providing a new way to create temporary result sets for complex queries.', "Using the keyword 'with' followed by a name, CTEs can be defined to specify the columns and their data returned by the CTE.", "The CTE 'employee count' in the example has three columns: department name, department ID, and total employees, derived from the SELECT query.", 'The chapter explains the syntax for common table expressions (CTEs) and their usage, highlighting the with keyword and its role in defining CTEs, and the select query determining the columns returned by the CTE.', 'The use of CTE is illustrated as an alternative method to achieve the same output as a regular query, providing a clear introduction to its syntax and purpose.', 'The temporary nature of CTEs is emphasized, likening them to derived tables and explaining their definition within the execution scope of a single select, insert, update, delete, or create view statement.', 'The discussion outlines the similarities between CTEs and derived tables, emphasizing their temporary nature and lack of storage as objects, clarifying their duration limited to the query execution scope.', 'The explanation of table variables includes the explicit definition of the table structure, the declaration of the variable, the insertion of data into the table variable, and the selection of columns from the table variable with a focus on the advantage of not needing to drop the table variable after use.', 'The usage of derived tables is illustrated, highlighting the creation of a derived table without explicit definition of columns and data types, and the selection and filtering of data from the derived table based on specified conditions like total employees greater than or equal to 2.', 'The chapter covers the creation and selection of data from temporary tables, demonstrating how to select specific columns and filter the results based on specified conditions, such as total employees greater than or equal to 2.', 'Views get physically stored in the database and can be available to other queries and stored procedures.']}