Refreshing Views After DDL Changes

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.

Order of Execution on SELECT Expressions

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.”

Nested Window Functions in ANSI SQL

Itzik Ben-Gan covers a hypothetical feature in the ANSI standard:

The standard nested window functions seem like a very powerful concept that enables a lot of flexibility in interacting with different points in windowing elements. I’m quite surprised that I cannot find any coverage of the concept other than in the standard itself, and that I don’t see many platforms implementing it. Hopefully this article will increase awareness for this feature. If you feel that it could be useful for you to have it available in T-SQL, make sure to cast your vote!

Check it out and upvote if you’re interested in seeing this functionality in SQL Server.

Identity Inserts: One Table at a Time

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.

Case-Insensitive Searches in Snowflake

Koen Verbeeck shows how you can perform case-insensitive searches in Snowflake DB:

I’m doing a little series on some of the nice features/capabilities in Snowflake (the cloud data warehouse). In each part, I’ll highlight something that I think it’s interesting enough to share. It might be some SQL function that I’d really like to be in SQL Server, it might be something else.

Today I have a small blog post about a neat little function I discovered last week – with thanks to my German colleague, who wants to remain anonymous. The function is called ILIKE and it is syntactic sugar for the combination of UPPER and LIKE.

I’m personally not a fan of case-sensitive collations for data; it’s hard for me to understand the meaningful differences between “dog,” “Dog,” and “DOG.”

Making Dynamic SQL Safe

Erik Darling explains patiently that if you use sp_executesql wrong, you don’t get the benefits of using it right:

The gripes I hear about fully fixing dynamic SQL are:

– The syntax is hard to remember (setting up and calling parameters)
– It might lead to parameter sniffing issues

I can sympathize with both. Trading one problem for another problem generally isn’t something people get excited about.

But there are good reasons fully to fix it, so read on.

CAST and CONVERT Make Expressions Nullable

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.

Using CHOOSE() in SQL Server

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.

Deleting From OPENQUERY

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.

Debugging a Pivot

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.

Categories

July 2019
MTWTFSS
« Jun  
1234567
891011121314
15161718192021
22232425262728
293031