A Fine Slice Of SQL Server
When I mentioned to the developer that it’s probably not the best idea, he turned around and asked me, ‘why not?’
It was a genuine question. Why shouldn’t we just spam VARCHAR(MAX) over all of our columns? On the upside, it would get rid of all those annoying issues that crop up when we try to insert a value that overflows the datatype.
Click through for a video as well as a blog post laying out some of the problem with using VARCHAR(MAX) all willy-nilly.
Copy job has been a go-to tool for simplified data ingestion in Microsoft Fabric, offering a seamless data movement experience from any source to any destination. Whether you need batch or incremental copying, it provides the flexibility to meet diverse data needs while maintaining a simple and intuitive workflow.
We continuously refine Copy job based on customer feedback, enhancing both functionality and user experience. In this update, we’re introducing several key improvements designed to streamline your workflow and boost efficiency.
Click through to see what’s new.
Comments closedThe performance impact of pg_tde by Percona for encrypted data pages is measurable across all tests, but not very large. The performance impact of encrypting WAL pages is about 20% for write-heavy tests. The tests were run with an extension RC (Release Candidate), however the WAL encryption feature is still in Beta stage.
Andreas also has a post on the testing specifics:
Comments closedThis test was run on a dedicated physical server, to avoid external influences and fluctuations from virtualization.
The server has a Intel(R) Xeon(R) Gold 5412U CPU with 48 cores, 256 GB RAM, and a 2 TB SAMSUNG MZQL21T9HCJR NVram disk dedicated for the tests (OS was running on a different disk).
Andy Brownsword doesn’t want to keep hitting the database:
Lookup transformations provide us a way to access related values from another source, such as retrieving surrogate keys in data warehousing. When we need multiple lookups to the same reference data we can improve performance through the use of a Cache.
If we consider data warehousing, a prime example of this would be an order table which has values for Order Date, Dispatch Date, Delivery Date, etc. All of these would require a lookup to a calendar dimension.
This is a perfect use case for a cache.
Read on to see how the cache connector works.
Comments closedKendra Little does a bit of index management:
Dear Postgres, Why won’t you use my covering index?
Lately I’ve been learning to tune queries running against PostgreSQL, and it’s pretty delightful. One fun question that I worked through struck me something that most Postgres users probably encounter one way or another: sometimes you may create the perfect index that covers a given query, but the query planner will choose to ignore it and scan the base table.
Why in the world would it do that?
Read on to learn why.
Comments closedAjay Dwivedi runs into an error:
I work with developers quite a lot. There are scenarios where data is received from various sources in an application, and asynchronously pushed to database in multiple sessions/connections.
There are situations where concurrency and transaction speed is high enough to cause below UPSERT code blocks to fail with error message like Cannot insert duplicate key row in object dbo.person with unique index ‘pk_person’
Click through for one way to do things. I will note that Ajay has concerns about the MERGE operator, but Hugo Kornelis took a deep dive into all of the known problems in MERGE and found that most of them were fixed. Hugo’s post does make it clear when using MERGE is a bad idea, as there are still some situations in which it won’t work effectively, but for something like this, it would be fine.
I’ll say that I’m generally not a fan of app locking. There are specific circumstances in which it’s the best answer, but those are rare. Here, I’d rather just use a ROWLOCK table hint or change the serialization level.
Miquella de Boer has an announcement:
Shortcut transformations is a new capability in Microsoft Fabric that simplifies the process of converting raw files, starting with .CSV files, into Delta tables. This feature eliminates the need for traditional ETL pipelines, enabling users to transform data directly on top of files with minimal setup.
Click through to see how it all works.
Comments closedIf you want to copy huge data from one SQL server to another, try using dbatools which has powershell module underneath.
In the demo here, I’ve compared 2 dbatools commands to move data from one SQL server to another:
Write-DbaDbTableData vs Copy-DbaDbTableData
Click through to see which one wins the speed challenge.
Comments closedBoniface Muchendu looks at some changes:
Power BI includes a powerful feature called Publish to Web, which allows users to share interactive reports publicly without requiring viewers to sign in. While this tool simplifies access, it can also create security risks if misused. In this guide, you’ll learn what “Publish to Web” does, how Microsoft updated it for better governance, and how to manage access responsibly.
Click through to see what’s new.
Comments closedAndy Brownsword changes things around:
Implicit conversion happens in SQL Server when the engine detects a mismatch in data types and automatically converts from one type to another. This can be helpful as it makes different types interchangeable and is generally transparent to the client, but it can come with issues.
Usually the downside from implicit conversion is seen through performance impact. But we’ve got something a little different today – an inconsistent result set.
Read on for Andy’s example.
Comments closed