When you’re developing on an instance you might want to change something in a database where the change might require to re-create the table. By default, the SQL Server Management Studio (SSMS) will prevent saving changes that require the table to be recreated.
Examples of changes that require table re-creation:
Change a column to no longer allow NULL values
Adding columns in the before another column
Moving a column
I agree with Sander: this is a useful feature, but not something you want to abuse. If you don’t understand the magnitude of your change, it could cause production problems. And if you do understand the magnitude of your change, typically you’ll want to script it out for later.
Now wait, why are they getting a read error when trying to UPDATE or DELETE? Because of the WHERE clause. The WHERE requires reading the data to see if a row meets the required conditions.
It turns out that write-only permissions don’t really work the way you’d want, as typically you want to read data even if your final goal is to update or delete rows.
For example, when I run it with 10,000 joins:
- Msg 8631, Level 17, State 1, Line 1
- Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.
When I drop it down to a much more realistic 5,000 joins:
- Msg 4074, Level 16, State 1, Line 1
- Client drivers do not accept result sets that have more than 65,535 columns.
Ah! Okay, that’s fair. (That’s also two error messages I’ve never seen before. Probably a good thing.) Alright, let’s take out the SELECT * and replace it with SELECT p1.* and see what happens:
This is a fun read.
When you think of technical debt, you may think only of classic shortcuts like making assumptions about the data, not using a TRY-CATCH block or perhaps hard-coding a manual correction into a stored procedure or view.
But I would argue that not paying attention to performance is just as much a technical debt. And rather than just crashing with an error message, performance issues are not always easy to just fix in production when your business users are working late to meet their deadlines, or when your web request are timing out. Start thinking of performance as an important part of your development process – half the job is getting the right data in the right place, the other half is making sure that your solution will handle double or triple the workload, preferably under memory pressure conditions with other workloads running at the same time.
Read the whole thing.
There are a handful of activities that have broken my SQL Server backup routines. Sometimes a DIFFERENTIAL (or LOG) backup would fail because there was no FULL backup. Other times, a LOG backup would fail because the log chain was broken. Some of those activities include:
Creating a new database.
Changing the recovery model to SIMPLE.
Restoring/reverting a database from a snapshot.
Any other activity that breaks the transaction log chain.
Dave discusses an easy way of figuring out if you’ve just performed an activity which breaks the log chain.
Start writing queries that demonstrate TSQL anti patterns – and make them slow
You know how people say that the best way to learn something is to teach it?
The best way to learn to speed up queries is to write slow ones.
The best way to get a job speeding up queries is to write a blog about the queries you’ve sped up.
This is a long-term learning process, but is absolutely a worthwhile skill for any database professional.
focus()works similarly to
select()from the dplyr package (which is loaded along with the corrr package). You add the names of the columns you wish to keep in your correlation data frame. Extending
focus()will then remove the remaining column variables from the rows. This is why
mpgdoes not appear in the rows above. Here’s another example with two variables:
Click through for the entire article.
In this article I will not be looking at Power BI as a data visualization tool. Indeed, I will not be discussing in anything more than a cursory way the data transformation and modelling capabilities of this product. Instead, I would like to focus on using Power BI Desktop to connect directly to certain data sources. So although you need no previous knowledge of Power BI Desktop to understand this article, I will be providing only an extremely succinct glance at the extensive array of the data mashup and modelling techniques that are available. Nor will I be explaining any of the approaches that can be taken to create visuals and dashboards. I will, however, try out a few classic techniques using the Power BI Query Editor and Data Model to see if there are any limits to using DirectQuery when compared to data loaded into the in-memory model. The aim here is to see just how, and in what circumstances, DirectQuery can help you to analyze your data faster and more easily.
This article is lengthy but good. Check it out.
This was an in-depth Extension method but as with any good recursive algorithm it was precious few lines of code. Why I care about it is twofold: execution dependencies and as I type that, I realize lineage tracing would also fall under this, and foreign key traversal. For the former, in my world, I find I have the best success when my SSIS packages are tightly focused on a task and I use a master/parent package to handle the coordination and scheduling of sub-package execution. One could use an extension method to discover all the packages that implement an Execute Package Task and then figure out the ordering of dependent tasks. That could save me some documentation headaches.
Sounds like a fun training.
The house edge is 2.70%. On average a gambler would lose 2.7% of his stake per game. Of course, on any one game he would either win or lose, but this is the long term expectation. Another way of looking at this is to say that the Return To Player (RTP) is 97.3%, which means that on average a gambler would get back 97.3% of his stake on every game.
Below are the results of a simulation of 100 gamblers betting on even numbers. Each starts with an initial capital of 100. The red line represents the average for the cohort. After 1000 games two gamblers have lost all of their money. Of the remaining 98 players, only 24 have made money while the rest have lost some portion of their initial capital.
This is a very interesting article if you’re interested in basic statistics. 13-year-old Onion article of note.