A Fine Slice Of 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.
Comments closedErik Darling takes us through an interesting scenario:
Like most tricks, this has a specific use case, but can be quite effective when you spot it.
I’m going to assume you have a vague understanding of parameter sniffing with stored procedures going into this. If you don’t, the post may not make a lot of sense.
Or, heck, maybe it’ll give you a vague understanding of parameter sniffing in stored procedures.
This was new to me.
Comments closedKenneth Fisher shows how to use the VALUES clause to construct a virtual table:
This has come up a few times recently, I find it rather fascinating and I can never seem to remember how to do it properly .. so in other words, it’s a perfect subject for a blog post.
Basically, you can use VALUES to create a table within a query. I’ve seen it done in a number of places. Mostly when demoing something and you want some data, but don’t want to actually create a table. I’ve also seen it used to create a numbers table, create test data, etc. Really, any case where you want a short list of values but don’t want to create an actual (or even temp) table to store them.
Click through for examples on how to construct and use this virtual table as a quick replacement for creating a temporary table or table variable.
Comments closedEd 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.
Comments closedEmanuele Meazzo shows one reason why loops can be so much slower in T-SQL:
Not everybody knows that SQL Server sends a DONE Token to the client each time that a SQL statement completes (so, everything except variable declarations); For the query above you can basically track it with extended events by tracking the “SQL Statement Completed” event.
What happens in a loop? For each statement that’s completed, a token is sent, which means that for this loop that contains 3 statements (the WHILE loop itself it’s a statement) 15 Million tokens are sent to the client.
There can be a drastic difference, as Emanuele shows.
Comments closedMax Vernon takes us through the .modify function:
Determining the property syntax when modifying XML values in SQL Server can be time consuming if you don’t work with XML regularly. SQL Server includes a very flexible XML subsystem, called XML_DML, or XML Data Manipulation Language. XML_DML can be used to easily and effectively update XML values in an xml-typed column or variable. This question on dba.stackexchange.comasked about using the
.modifyfunction to change the value of an element, which in turn prompted this post.
Read on for a number of examples.
Comments closedEd Elliott takes us through things to think about before deleting a few million rows from a table:
Fragmentation
Fragmentation occurs when we delete from pages, and there is still data surrounding our data. If we have 100 rows and delete every odd row, we would have perfect fragmentation in that we have doubled the size of the data that we need. If we delete rows 1-49, even though we remove the same number of rows we don’t have any fragmentation as the data is in a continuous block. Knowing how the data is stored on disk and how the data will be deleted, is it the first x records or every x record is vital so that we know whether, after the delete, we should also reorganise the indexes to remove the deleted records.
Ed has quality insights here, so check it out.
Comments closedKenneth Fisher shares a couple of interview puzzles:
The year is 2004. You’re taking a tech test as an interview for a SQL development job. They have a page in their application that displays up to 20 rows of information. They need a piece of code that will return the rows from a given page. Oh, and it may not always be 20 rows per page. You need to write a piece of code where they can pass in a page number and page size and get back results. So for example, if the page size is 20 and the page is 3 then you need to return back rows 41 to 60.
The answers aren’t on the page, but then again, that’s the point of a puzzle.
Comments closedSolomon Rutzky dives into how big a table value constructor can be in terms of rows:
On 2019-05-08, a helpful individual, Michael B, commented on my answer saying that the 1000-row limit only existed when using a TVC as the
VALUESclause of anINSERTstatement. And, that there was no limit when using a TVC as a derived table.Could this be true?
Solomon finds out. Click through and so can you.
Comments closedItzik Ben-Gan provides quality information on working with subqueries in SQL Server:
In this plan you see a Nested Loops (Left Semi Join) operator, with a scan of the clustered index on Customers as the outer input and a seek in the index on the customerid column in the Orders as the inner input. You also see an outer reference (correlated parameter) based on the custid column in Customers, and the seek predicate Orders.customerid = Customers.custid.
So why are you getting the plan in Figure 1 and not the one in Figure 2? If you haven’t figured it out yet, look closely at the definitions of both tables—specifically the column names—and at the column names used in the query. You will notice that the Customers table holds customer IDs in a column called custid, and that the Orders table holds customer IDs in a column called customerid. However, the code uses custid in both the outer and inner queries.
Itzik covers three specific scenarios, all of which can cause trouble to database developers who haven’t been burned yet. And sometimes even those who have.
Comments closed