A Fine Slice Of SQL Server
So what’s the problem? Well, to start with by using the SELECT TOP 0 INTO Archive method of creating the Archive table you’ve created the archive table with an identity column. Get rid of that identity property (you still want the column) and you don’t have a problem. But the really cool part of this is that it reminded me that the OUTPUT clause has a field list.
Also check out the comments for another solution.
Comments closedChris Webb shows how to use Azure Data Catalog to share queries from Power Query:
While I’m really happy to have this functionality back, and I think a lot of people will find it useful, there’s still a lot of room for improvement. Some thoughts:
This really needs to extended to work with Power BI Desktop too. In fact, it’s such an obvious thing to do it must be happening soon…?
Given how quickly the Power BI team iterates, that’s probably the case. Anyhow, read the whole thing.
Comments closedI have a post showing how to control predicate pushdown in Polybase:
As a reminder, in order to allow predicate pushdown to occur, we need to hit a Hadoop cluster; we can’t use predicate pushdown on other systems like Azure Blob Storage. Second, we need to have a resource manager link set up in our external data source. Third, we need to make sure that everything is configured correctly on the Polybase side. But once you have those items in place, it’s possible to use the FORCE EXTERNALPUSHDOWN command like so:
There’s also discussion of preventing MapReduce job creation as well as a pushdown-related error I had received in the past.
Comments closedRaul Gonzalez looks at how transaction logging works with memory-optimized tables:
Now we need double the rows, because for each row we’ve said it’s been deleted, we have to tell SQL Server that was not actually deleted (COMPENSATION due to ROLLBACK) in case of recovery (crash recovery or backup recovery). That’s so bad.
But not everything is lost yet
let’s check how the In-Memory engine deal with this problem
Memory-optimized tables are pretty neat.
Comments closedThe problem with lowering the database compatibility level is that you can’t leverage the new engine functionality available under the latest compatibility level.
This problem was solved in SQL Server 2016 with the introduction of Database Scoped Configurations which gives you the ability to make several database-level configuration changes for properties that were previously configured at the instance-level. In particular, the LEGACY_CARDINALITY_ESTIMATION database scoped configuration allows you to set the cardinality estimation model independent of the database compatibility level.This option allows you to leverage all new functionality provided with compatibility level 130 but still use the legacy CE in the odd chance that the latest CE casuses severe query regressions across your workload.
Each has its own specific use cases, so it’s good to know all three.
Comments closedErin Stellato walks through capacity planning:
Capacity planning for a new solution is really tricky. You have to come up with estimates about workload based on information you collect from the business. This means you have to ask hard questions about how much data they will expect in the first month, the first six months, and the first year. When a new solution is coming in, this is often the LAST thing the business is thinking about, so very often you’re going to get vague answers. In the case of a new solution, you really have make a best guess effort. Don’t pull your hair out trying to get exact numbers.
If the solution is from a vendor, you must ask the vendor for planning recommendations about both space needed and the resources needed. I admit, they may not have that data, but you don’t get what you don’t ask for. It never hurts to try.
Click through for Erin’s discussion points across hardware and storage requirements over time.
Comments closedDave Mason lays out how to set up Event Notifications:
When an Event Notification is created, one or more conversations is created between the SQL Server database engine and a Service. I tend to think of these as “message channels”. When the related event occurs, SQL Server calls the EVENTDATA() function, returning the event information results (as a variable of type XML) to a Service. The Service in turn writes the information to a Queue. A Queue is a FIFO data structure. Conceptually it is similar to a table of rows and columns. You can SELECT from it, but you can’t directly insert or update its rows. You “delete” rows from a Queue via the RECEIVE statement.
Dave has a full example worked out at the link.
Comments closedBefore we begin, if you are running Windows 7 or if you do not have the Active Directory module installed, please do so first by downloading and installing “Remote Server Administration Tools”. I personally cannot live without this module.
Let us call the new employee as NEW_USER and the existing employee as OLD_USER. Here is how to use PowerShell to do the comparison.
The retrieval and comparison code is pretty easy; click through to see.
Comments closedErik Darling looks at queries using table variables spilling to disk:
Boy oh boy. Boy howdy. Look at all those physical writes. We spilled everything to disk. That’s right at the 2.4 GB mark, which is the same size as the Votes table. We should probably know about that, right?
Read on for a comparison against temp tables.
Comments closed