A Fine Slice Of SQL Server
Andy Mallon answers a question:
For example, I had a database and I want to see which physical backup file was used to restore into it. The problem is that the database was deleted. How can I see the record?
Read on for that answer, which will depend on how, exactly, you dropped the database.
Comments closedJoe Celko talks about the lesser-known language in SQL (compared to DML and DDL):
But the truth is that the most important sub-language is the one that needs fixing. You wonder why a three-legged stool works? All three legs have to be coordinated together; the same principle holds an SQL schema.
The third sub language in SQL is the DCL (data control language). This is where you get those database privileges I just mentioned. SQL classes don’t spend a lot of time on DCL for several reasons. The first of all is that you’re a mere USER and you’re probably not allowed to pass out privileges. The original ANSI/ISO security model was pretty simple. The universe was divided into USER and USER. An important concept in that you do not create a privilege, but the ADMIN grants it to a user and it is separate from the DDL.
Click through to learn more.
Comments closedThe French philosopher Montesquieu wrote, “Le mieux est le mortel ennemi du bien”, which means, “The best is the mortal enemy of the good.” Montesquieu was an interesting man who lived in the 18th century, and influenced many of the political structures around the world. His book “The Spirit of Law” from 1748 described ways to avoid absolute power, established concepts like “innocent until proven guilty”, and campaigned against slavery. I guess some countries adopted those ideas more than others.
I mentioned it because Josephine Bush has invited us to write about the topic of “Good enough is perfect“. And while I realise that the two statements are not identical, I consider that they are essentially the same. If we always strive for perfect then we can all too easily miss the mark of being able to deliver anything worthwhile. As a consultant I have to understand that the ideal solution for my customers is not the one where everything is completely perfect if achieving that means that the cost is too high. Instead, I need to find the point where good enough really is good enough.
Understanding value at the margin is a vital skill.
Comments closedAndy Leonard continues a series on design patterns:
In this post, I clone and modify the dynamic parent pipeline from the previous post to retrieve metadata from an Azure SQL database table for several child pipelines, and then call each child pipeline from a parent pipeline.
When we’re done, this pipeline will:
- Read pipeline metadata from a table in an Azure SQL database
- Store some of the metadata (a collection of pipelineID values) in the (existing) pipelineIdArray variable
- Iterate the pipelineIdArray variable’s collection of pipelineID values
- Execute each child pipeline represented by each pipelineID value stored in the pipelineIdArray variable
Read on to learn how.
Comments closedSandeep Pawar has a script for us:
I have been sitting on this code for a long time. I shared the first version in one of my blogs on Direct Lake last year. I have been making updates to it since then as needed. I waited for the lakehouse schema to become available and then forgot to blog about it. Yesterday, someone reached out asking if the above could be used for warehouse delta tables in Fabric, so here you go. It’s 250+ lines so let me just explain what’s going on here:
Read on for the explanation, the script itself, a demonstration, and several additional notes.
Comments closedDeepak Mahto shares some advice:
The PL/pgSQL language, available as a default extension in PostgreSQL, provides powerful tools and flexibility for application developers to build complex, enterprise-scale functionality within the database. Through PL/pgSQL’s functions and procedures, developers can choose different volatility categories—
IMMUTABLE, STABLE, or VOLATILE—that offer varying performance benefits, especially in terms of result caching and data state awareness. For a deeper dive, refer to the official documentation. Choosing the appropriate volatility for a function is crucial; as they say, “With great power comes great responsibility.“
Read on to learn more. It seems like it would be quite easy to mess this up.
Comments closedAdam Bertram differentiates the two:
Whenever you execute or run anything in PowerShell, you’re running a “command.” A command is an informal term for executable code, which can be a built-in PowerShell cmdlet, a custom function, or even an object method.
Cmdlets are compiled executable code provided by Microsoft, created in a language like C#, compiled, and added to a PowerShell module by software developers. Functions, on the other hand, are created directly in PowerShell by users. Although functions and cmdlets are technically different, they can be thought of as similar when you’re starting out.
Read on for more information, including a demo. I normally call them all cmdlets, but apparently I’ve been doing it wrong all this time.
Comments closedBravin Wasike completes a series on continuous integration and continuous deployment:
Continuous Deployment (CD) is an advanced CI/CD practice that takes automation to the next level. It automatically deploys every code change that passes the automated testing phase to production. Unlike Continuous Delivery, which requires manual approval before deploying to production, Continuous Deployment eliminates manual intervention. This allows for seamless and frequent software updates.
Click through for the full article.
Comments closedTeo Lachev describes some benefits and properties of temporal tables:
At the same time, temporal tables are somewhat more difficult to work with. For example, you must disable system versioning before you alter the table. Here is the recommended approach for altering the schema by the documentation:
I think the main drawback to using temporal tables in this way is that we can only use system time as the separator, unless you manually load data into the history table. It’d be great to have a user time capability to open up temporal tables to these sort of warehousing scenarios, such as using them for type-2 slowly-changing dimensions.
Comments closedSemab Tariq covers autovacuum:
However, one side effect of MVCC is the creation of dead tuples—old versions of data rows that are no longer needed but still occupy space.
Dead tuples also lead to a phenomenon known as table bloat, which refers to the excessive unused space in a table caused by dead tuples that haven’t been cleaned up, resulting in inefficient storage and reduced performance
To address the issues of dead tuples and table bloat, autovacuum comes into play. It’s an automatic process designed to clean up these dead tuples and maintain optimal database performance.
Read on for a list of scenarios that can trigger autovacuum.
Comments closed