Anyway, enough talk, let's take a look at this in action.
Since I don't have any tables ready to play with, I have created three in-memory SQL tables: boy, girl, and relationship.
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.
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?
If you change your View, like adding or removing a column, you will need to fetch the changes in the View into SQL Spreads.
You do that by closing and re-opening the SQL Spreads Design mode by clicking the Design mode button in the SQL Spreads ribbon in Excel.
In the second example there is no implicit type conversion as the expression defines data type of the column.
Notice also that the column in the second example has been defined as a NULLable column whereas in the first example it has not.
To resolve these issues you must explicitly set the type conversion and nullability in the CREATE TABLE [dbo].[Sales] ( [date] INT NOT NULL , [product] INT NOT NULL , [store] INT NOT NULL , [quantity] INT NOT NULL , [price] MONEY NOT NULL , [amount] MONEY NOT NULL ) WITH ( DISTRIBUTION = HASH([product]) , PARTITION ( [date] RANGE RIGHT FOR VALUES (20000101,20010101,20020101 ,20030101,20040101,20050101 ) ) ) ; CREATE TABLE [dbo].[Sales_in] WITH ( DISTRIBUTION = HASH([product]) , PARTITION ( [date] RANGE RIGHT FOR VALUES (20000101,20010101 ) ) ) AS SELECT [date] , [product] , [store] , [quantity] , [price] , [quantity]*[price] AS [amount] FROM [stg].[source] OPTION (LABEL = ' CTAS : Partition IN table : Create') ; The query would run perfectly fine.
The problem comes when you try to perform the partition switch. To make the table definitions match the CTAS needs to be modified.
It doesn't happen all that often (most of my SQL is fairly simple), but every now and then someone shows me something that just rocks my world, whether it be the power of Indexing or just something as simple as using UNION ALL instead of UNION.