Scenarios that can be worked around with CTAS include: CREATE TABLE [dbo].[Annual Category Sales] ( [English Product Category Name] NVARCHAR(50) NOT NULL , [Calendar Year] SMALLINT NOT NULL , [Total Sales Amount] MONEY NOT NULL ) WITH ( DISTRIBUTION = ROUND_ROBIN ) ; UPDATE acs SET [Total Sales Amount] = [fis].[Total Sales Amount] FROM [dbo].[Annual Category Sales] AS acs JOIN ( SELECT [English Product Category Name] , [Calendar Year] , SUM([Sales Amount]) AS [Total Sales Amount] FROM [dbo].[Fact Internet Sales] AS s JOIN [dbo].[Dim Date] AS d ON s.[Order Date Key] = d.[Date Key] JOIN [dbo].[Dim Product] AS p ON s.[Product Key] = p.[Product Key] JOIN [dbo].[Dim Product Sub Category] AS u ON p.[Product Subcategory Key] = u.[Product Subcategory Key] JOIN [dbo].[Dim Product Category] AS c ON u.[Product Category Key] = c.[Product Category Key] WHERE [Calendar Year] = 2004 GROUP BY [English Product Category Name] , [Calendar Year] ) AS fis ON [acs].[English Product Category Name] = [fis].[English Product Category Name] AND [acs].[Calendar Year] = [fis].[Calendar Year] ; -- Create an interim table CREATE TABLE CTAS_acs WITH (DISTRIBUTION = ROUND_ROBIN) AS SELECT ISNULL(CAST([English Product Category Name] AS NVARCHAR(50)),0) AS [English Product Category Name] , ISNULL(CAST([Calendar Year] AS SMALLINT),0) AS [Calendar Year] , ISNULL(CAST(SUM([Sales Amount]) AS MONEY),0) AS [Total Sales Amount] FROM [dbo].[Fact Internet Sales] AS s JOIN [dbo].[Dim Date] AS d ON s.[Order Date Key] = d.[Date Key] JOIN [dbo].[Dim Product] AS p ON s.[Product Key] = p.[Product Key] JOIN [dbo].[Dim Product Sub Category] AS u ON p.[Product Subcategory Key] = u.[Product Subcategory Key] JOIN [dbo].[Dim Product Category] AS c ON u.[Product Category Key] = c.[Product Category Key] WHERE [Calendar Year] = 2004 GROUP BY [English Product Category Name] , [Calendar Year] ; -- Use an implicit join to perform the update UPDATE Annual Category Sales SET Annual Category Sales. Total Sales Amount FROM CTAS_acs WHERE CTAS_acs.[English Product Category Name] = Annual Category Sales.[English Product Category Name] AND CTAS_acs.[Calendar Year] = Annual Category Sales.[Calendar Year] ; --Drop the interim table DROP TABLE CTAS_acs ; CREATE TABLE dbo. As the persisted value in the result column is used in other expressions the error becomes even more significant.
Dim Product_upsert WITH ( Distribution=HASH(Product Key) , CLUSTERED INDEX (Product Key) ) AS -- Select Data you wish to keep SELECT p. This is particularly important for data migrations.
Even though the second query is arguably more accurate there is a problem.
The data would be different compared to the source system and that leads to questions of integrity in the migration.
This can often prove to be a win/win situation as not only will your code be compliant but it will often execute faster on SQL Data Warehouse. This can lead to subtle variances in values if you aren't careful.
This is as a result of its fully parallelized design. Dim Product p RIGHT JOIN dbo.stg_Dim Product s ON p. Try the following as an example: The value stored for result is different.
In the first example the table defines the column definition.
When the row is inserted an implicit type conversion occurs.
CREATE TABLE [dbo].[Sales_in] WITH ( DISTRIBUTION = HASH([product]) , PARTITION ( [date] RANGE RIGHT FOR VALUES (20000101,20010101 ) ) ) AS SELECT [date] , [product] , [store] , [quantity] , [price] , ISNULL(CAST([quantity]*[price] AS MONEY),0) AS [amount] FROM [stg].[source] OPTION (LABEL = ' CTAS : Partition IN table : Create'); You can see therefore that type consistency and maintaining nullability properties on a CTAS is a good engineering best practice.
It helps to maintain integrity in your calculations and also ensures that partition switching is possible.
Notice how the SQL UPDATE statement is JOINing the @boy, @girl, and @relationship table using INNER JOINs and limiting it to boys who have dated Winona Ryder. That was before I knew you could do a FROM clause in the UPDATE statement. I don't think one is necessarily better than the other - they do different things. The trick is to know when to leverage the powers of each tool. Although, I'll admit upfront that my measurement process consists of running the app and saying "Well, that seemed a lot faster".
The update is made to the result of that JOIN and then we are selecting all the rows from that updated @boy table (to see that it works). This should make things much easier, especially when dealing with an UPDATE that uses a sub-query. :) @Matt, If you think about applications from a "user experience" standpoint, it's the "that runs faster" moment much more important than any numeric reading?
The boy table lists boys, the girl table lists girls, and the relationship table lists out romantic relationships between the two (what can I say, I am a romantic fool at heart). FROM to persist an array of objects in one SQL statement.