A Fine Slice Of SQL Server
The Hadoop in Real World team takes us through an interesting insertion problem:
When we attempt to insert in to the employee_depts table with the below INSERT, the insert operation will fail with TOK_FUNCTION not supported in insert/values SemanticException.
The solution is straightforward but not something I would have figured out intuitively.
Comments closedRayis Imayev notes a problem with Azure DevOps deployments:
Here is one thing that still doesn’t work well when you try to alter an existing temporal table and run this change through the [SqlAzureDacpacDeployment@1] DevOps task, whether this change is to add a new column or modify existing attributes within the table. Your deployment will fail with the “This deployment may encounter errors during execution because changes to … are blocked by …’s dependency in the target database” error message.
Read on to see what causes this problem and what we can do to work around it.
Comments closedSteve Jones is trying to find where he left his keys:
I’ve been working with encryption in SQL Server for a long time, and have delivered quite a few presentations on the topic. Recently I was updating some code and wanted to check if a database had a master key created in it. This post shows how to do that.
Click through to see how you can create a database master key as well as how to drop it if necessary.
Comments closedInstant File Initialization (IFI) enabled on SQL Server, for data files only, leads to faster execution on certain file operations, as it bypasses the zeroing procedure while reclaiming disk space. Instead, disk content is overwritten as new data is written to the files.
But do these benefits continue when Transparent Data Encryption (TDE) is enabled ?
Click through for the answer.
Comments closedSaveen Reddy has an update for us:
You can now easily add data quality, data validation, and schema validation to your Synapse ETL jobs by leveraging Assert transformation in Synapse data flows. Add expectations to your data streams that will execute from the pipeline data flow activity to evaluate whether each row or column in your data meets your assertion. Tag the rows as pass or fail and add row-level details about how a constraint has been breached. This is a critical new feature to an already effective ETL framework to ensure that you are loading and processing quality data for your analytical solutions.
Read on for the full list of changes.
Comments closedLee Markum does some thinking:
I was recently thinking of SQL Server temporal tables and how there is a perspective that you shouldn’t use proprietary features of a product because it locks you into that product. I want to be your guide on this matter.
I agree completely with Lee’s take on this. I can count on one hand the number of platform migrations I’ve been a part of through the years. These are painful enough experiences even if you use 100% “portable” code because optimization patterns change. And then you get to the utterly absurd: indexes in Oracle and SQL Server behave differently (e.g., clustered indexes are a practical must in SQL Server and the equivalent to clustered indexes in Oracle is so rarely used that it’s not even worth talking about to most Oracle people), so are you going to avoid indexes so you can have “truly” portable code? If the answer to that question is “yes,” you are wrong.
I do understand that there are companies which create code bases which need to be installable on multiple platforms. In that case, I do understand trying to keep things as common as possible, especially for fairly simple apps. But this is also part of why vendor databases are, as a general rule, awful.
Comments closedKellyn Pot’vin-Gorman lays out some guidance on Oracle to Azure migrations:
When migrating an Oracle database to another platform, there are the common indicators and discussion topics around PL/SQL conversions, data types, application rewrites, etc., as being roadblocks to refactoring, but being successful also has to do with the SIZE of the workload coming from Oracle. I find this is often dismissed, even though this is one of the quickest ways to identify if an ENTIRE Oracle database, (not even by schema or a subset of the Oracle database) can run on a Platform as a Service, (PaaS) solution.
Click through for more information on PaaS limits for Oracle databases in Azure.
Comments closedCem Ayberkin shows off the Azure Form Recognizer:
Shopping malls are facing strong competition and effective loyalty programs boost customer retention. The primary goal of the loyalty scheme is to promote loyalty at the mall, increase footfall whilst understanding shopping habits. With large number of stores and various receipt formats in a mall, the process of manual checking and verification of the data submitted in place did enable rewards to be issued, but proved slow, expensive, inconsistent, and non-scalable. It did not include the valuable line item/product information the mall needed to understand the shopping habits. Therefore, one of the largest shopping malls used Azure Form Recognizer automating receipt scanning and data extraction and feeding the data as rewards points into the customer’s loyalty program, which greatly improved customer shopping experience.
I was pleasantly surprised with how the Form Recognizer works. It’s not perfect but it is useful.
Comments closedColin Gillespie looks at a few new features in R version 4:
R version 4.0.0 was released almost two years ago. The change in the major version, 3.x.y to 4.0.0, represented significant and potentially breaking changes. For an organisation to start using these new features, everyone in the company must have access to that version; otherwise code isn’t shareable. This naturally slows down adoption.
We moved our internal R projects to depend on version R 4.0.0 around twelve months ago – a few months after the release date. Over the last year we’ve also assisted a number of clients in making the move; typically with Shiny applications. This post aims to highlight some of the features we’ve found useful and also some of the potential pitfalls.
It’s crazy to think that it’s been so long
Comments closedKamil Nowinski uses one of my favorite tools for diagram creation:
Documenting objects dependencies of ETL processes is a tough task. Regardless it is SSIS, ADF, pipelines in Azure Synapse or other systems. The reasons for understanding the current solution can vary either: handover to other team/member of the team, troubleshooting, refactoring, debugging, investigating dependencies due to error, performance issue or others, as well as keen to remove selected/duplicated pipelines or logic.
But there is never a good time to make documentation, and even if that has been done – no one knows how much it’s up to date. The situation is not improved by the fact that quite often there is a lack of (free or built-in) tools for generating such documentation. Sounds familiar? I bet it does.
Click through to learn more and to see how to use that tool (Mermaid).
Comments closed