Eduardo Pivaral shows how you can refresh the metadata for a view in SQL Server after one of its underlying tables or functions changes:

So we proceed to execute an alter view over the first view:

ALTER VIEW dbo.[vi_invoices_received_by]
AS
SELECT
ConfirmedReceivedBy as [Received by],
COUNT(InvoiceID) as [# of Invoices],
CustomerID
FROM Sales.Invoices
GROUP BY ConfirmedReceivedBy, CustomerID;
GO

So we should now be able to see the updated column names on the second view, right? 

but if you execute the view again, you will obtain the same results as before:

Read on to see what’s gone wrong and how you can fix it.

Bert Wagner digs into an interesting topic:

Success! But as I was celebrating my dynamic SQL victory, I realized I was making an assumption about SQL Server that I had never thought about before:

The above query only works because SQL Server is executing the variables in the SELECT list sequentially. I’m incrementing @CurrentRow only after processing my @RowQuery variable, and this logic only works correctly if SQL Server executes the variable expressions in the order they appear in the SELECT list. If SQL Server was executing items in the SELECT list in reverse or random order, @CurrentRow could potentially get set BEFORE @RowQuery was evaluated, causing the logic of adding “UNION ALL SELECT” in the right location to fail.

I have a vague recollection that you couldn’t always count on this, though I admit to never having seen a counter-example. I don’t believe expression execution order is defined in the ANSI SQL standard—the idea is that everything is typically resolved “at once.”

Bert Wagner shows that you can only insert with IDENTITY_INSERT = ON for one table at a time:

Ok, simple enough to fix: we just need to do what the error message says and SET IDENTITY_INSERT ON for both tables:

SET IDENTITY_INSERT dbo.User_DEV ON; SET IDENTITY_INSERT dbo.StupidQuestions_DEV ON;

And… it still didn’t work:

IDENTITY_INSERT is already ON for table 'IdentityTest.dbo.User_DEV'. Cannot perform SET operation for table 'dbo.StupidQuestions_DEV'.

Click through for the ramifications and your alternative.

Daniel Hutmacher points out a side effect of using CAST() and CONVERT():

Suppose we want to set up a view in the new solution that mirrors the names and definitions of the old table, so the legacy integration can use that view going forward:

CREATE OR ALTER VIEW new.the_table_like_before
AS
SELECT CAST(id AS varchar(32)) AS id,
CAST([row] AS int) AS [row],
CAST(date_loaded AS datetime) AS dt
FROM new.the_table;

Now, if you check out the resulting datatypes of the view, you’ll notice that all the columns are marked nullable, even though they’re all based on non-nullable columns, so the values in the view could never be null.

Read on for a couple possible solutions.

Bert Wagner explains the CHOOSE() function:

While I know I don’t utilize most of the features available in SQL Server, I like to think I’m at least aware that those features exist.

This week I found a blind-spot in my assumption however. Even though it shipped in SQL Server 2012, the SQL Server CHOOSE function is a feature that I think I’m seeing for the first time this past week.

CHOOSE() and IIF() were functions ported over to make it easier for Access and Excel users to write code. I tend to avoid them because there are typically better idiomatic constructs (like CASE) in SQL Server.

Jack Vamvas shows how you can delete data from a remote server using OPENQUERY:

How can I DELETE using OPENQUERY?   Normally for a SELECT from OPENQUERY , I’ll do something like :

SELECT col1 ,col2  FROM OPENQUERY (MY_LINKED_SERVER,'SELECT col1,col2  FROM MY_LINKED_TABLE')

Can a similar method be used but for DELETE?

Click through for the answer.

Ed Elliott takes us through problems with the PIVOT statement:

If you have a PIVOT query and it isn’t returning the data you expect, what can you do to troubleshoot it? The thing to do is to break it down into the constituent parts. First, lets take a look at a query and see what we can do to help.

Click through for potential problems and their solutions.