A Fine Slice Of SQL Server
This post hows how you can capture and store the number of records inserted, updated or deleted from a T-SQL Merge statement.
This is in response to a question on an earlier post about using Merge to load SCDs in a Data Warehouse.
You can achieve this by using the OUTPUT clause of a merge statement, including the $Action column that OUTPUT returns.
Read on for the answer. If only MERGE weren’t so riddled with problems.
Leave a CommentHasan Savran takes us through the OFFSET and LIMIT clauses in Cosmos DB:
OFFSET LIMIT clause one of the latest additions to the Azure Cosmos DB. Skip/Take function was a big request from users and Cosmos DB team listened users and deliver this functionality. If you think Cosmos DB is missing a feature and if you have a new idea, you can use Feedback Forums to give feedback to Cosmos Db team.
OFFSET LIMIT clause let you skip x number of results then take y numbers of values from the query. Count for OFFSET and Limit are integer and both are required. In other words, You must use LIMIT if you use OFFSET.
A common use for this is paging. I’d be interested to see if this shares the issues that the SQL Server version has: you may only return back 20 rows, but you’re potentially scanning N + 20 each time.
Leave a CommentThe problem was that the plan was doing a table scan on the underlying table, and not using the indexes I had carefully crafted.
I had even created a second non-clustered index to try and make sure it was a proper covering index for the query.
Read on to see how Randolph solved the problem.
Comments closedKoen Verbeeck shows how you can use the RATIO_TO_REPORT() function in Snowflake to determine the current row’s percentage of the total:
This episode talks about a new window function Snowflake recently introduced: RATIO_TO_REPORT. The function returns the ratio of the value of the current row to the sum of the values within the set. Or in other words, some sort of “percentage of total”. Nothing we couldn’t calculate before, but a bit of syntactic sugar so we don’t have to write two expressions.
Click through to see how to use it and a contrast with the ANSI SQL approach.
Comments closedAnvesh Patel shows how to remove find duplicate rows using ROW_NUMBER() and then delete them in a statement:
Recently, I got one request for one script to delete duplicate records in PostgreSQL.
Most of the Database Developers have such a requirement to delete duplicate records from the Database.
Like SQL Server, ROW_NUMBER() PARTITION BY is also available in PostgreSQL.
Click through for separate solutions for MySQL, SQL Server, and Postgres.
Comments closedItzik Ben-Gan takes us through two issues you might run into when using window functions:
There are two common pitfalls involving window functions, both of which are the result of counterintuitive implicit defaults that are imposed by the SQL standard. One pitfall has to do with calculations of running totals where you get a window frame with the implicit RANGE option. Another pitfall is somewhat related, but has more severe consequences, involving an implicit frame definition for the FIRST_VALUE and LAST_VALUE functions.
There’s a lot going on in these two examples, so read on.
Comments closedKevin Marquette explains what is possible with if in Powershell:
The
-notoperator flips an expression from$falseto$trueor from$trueto$false. Here is an example where we want to perform an action whenTest-Pathis$false.if ( -not ( Test-Path -Path $path ) )
There’s plenty of good stuff here, so check it out.
Comments closedKenneth Fisher takes us through procedure parameters:
If you are executing a stored procedure with a bunch of parameters it can be a bit of a pain if you have to pass a value in for each of them. Fortunately, it’s pretty easy to make some parameters required and others optional. You simply give them a default value.
Kenneth also points out that functions don’t behave this way, and shows how to handle parameters where you don’t want to accept NULL under any circumstances. This is useful when NULL is just a placeholder for “I don’t really want to use this parameter” but the application doesn’t know how to avoid sending the parameter in the first place.
Comments closedNisarg Upadhyay shows three ways to calculate the median in T-SQL:
To calculate the median of any dataset, we first need to arrange all values from the dataset in a specific order. After arranging the data, we must determine the middle value of the specified dataset. If the dataset contains an odd number of values, than the middle value of the entire dataset will be considered as a median. Following is the example:
Median (M) = value of ((X + 1)/2) th item. (x is the number of values in the dataset)
Honestly, CLR’s probably the best approach here if you want a fast calculation for a reasonably large number of rows. Using ML Services and R/Python is another alternative, though the launchpad spinup time will probably make it slower than CLR.
Comments closedSo we proceed to execute an alter view over the first view:
ALTERVIEWdbo.[vi_invoices_received_by]ASSELECT
ConfirmedReceivedByas[Receivedby],
COUNT(InvoiceID)as[#ofInvoices],
CustomerIDFROMSales.InvoicesGROUPBYConfirmedReceivedBy, CustomerID;GOSo 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.
Comments closed