A Fine Slice Of SQL Server
Steven Sanderson saves some changes:
When working with data, exporting your results to an Excel file can be very handy. Today, I’ll show you how to write the iris dataset to an Excel file using R and Python. We will explore three R packages:
writexl,openxlsx, andxlsx, and theopenpyxllibrary in Python. Let’s dive in!
Click through for these four examples.
Comments closedJeff Iannucci talks about OLE Automation:
It’s rare to see OLE Automation procedures enabled on a SQL Server instance. Most folks aren’t using these specially system procedure because they didn’t have a need to use them, have a compliance requirement that prohibits using them, or they tried using them and had adverse results. As the Microsoft documentation on OLE Automation stored procedures notes:
“Don’t directly or indirectly call Automation procedures from any SQL Server common language runtime (CLR) objects. Doing so can cause SQL Server to crash unexpectedly.”
But if this setting is enabled in any of your SQL Server instances, you need to consider it similarly to the xp_cmdshell setting. By that I mean: this is probably not a problem, but you should try to figure out why the setting is enabled, and how its associated system stored procedures are being used.
I find this perfectly reasonable. There’s a lot of fear around xp_cmdshell, when in practice, it doesn’t affect security at all unless you completely mess things up and start granting rights to non-sysadmins.
Comments closedEd Pollack does a bit of tuning:
SQL Server provides a variety of ways to tune XML so that it provides consistent performance, consumes less space, all while ensuring efficient access to critical data.
At its core, the metadata-styled XML format runs counter to the data that SQL Server is optimized to manage. Therefore, additional features were added to SQL Server over time that allowed for XML data to be indexed and compressed.
While these features are critical for managing XML data as it becomes large, it is important to remember what XML is intended for and why it is (loosely) structured as it is. Many data professionals have used shortcuts when XML was small, such as storing and analyzing it in string format, only to be forced to reckon with performance challenges when scanning large strings become agonizingly slow.
Read on for the full article.
Comments closedChristoph Schiessl sorts things out:
Custom
ENUMtypes in PostgreSQL are an excellent tool for enforcing certain database constraints, but you must be careful if you useSELECTqueries and want toORDER BYthese columns. Recently, I had to fix a bug whose root cause was a misunderstanding of this behavior. It’s just a contrived example, but imagine a table of people with their marital status, which is implemented as a customENUMtype.
Read on to learn more about the misunderstanding and some of the unexpected trickiness involved in getting a good query plan.
Comments closedPaul White delves into history:
SQL Server doesn’t support parallel modifications to a b-tree index.
That might sound surprising. After all, you can certainly write to the same b-tree index from multiple sessions concurrently. For example, two sessions can happily write alternating odd and even numbers to the same integer b-tree index. So long as both sessions execute on different schedulers and take row locks, there will be no blocking and you’ll get true concurrency.
No, what I mean is: A single session can’t write to a b-tree index using more than one thread. No parallel plan modifications of a b-tree index, in other words. It’s a bit like the lack of parallel backward ordered scans. There’s no reason it couldn’t be implemented, but it hasn’t been so far.
Click through for a link to the full article. Or click the link I just added, your choice.
Comments closedMeagan Longoria explains some of what you get with themes in Power BI:
Power BI reports have a theme that specifies the default colors, fonts, and visual styles. In Power BI Desktop, you can choose to use a built-in theme, start with a built-in theme and customize it, or create your own theme.
Creating your own theme involves specifying formatting options in a JSON file and importing it into your report. This post will focus on the theme colors, but there are lots of other options that can be specified in a theme, including structural colors, fonts, and page and visual formatting options.
Read on to learn more about the three primary sets of colors you can specify.
Comments closedSemab Tariq notes some syntax to assist with performing updates in a MVCC world:
In critical environments like banking, healthcare, and online retail, ensuring safe data modifications is crucial to prevent data corruption and maintain system integrity. PostgreSQL offers a robust solution for this with its row-level locking mechanism, which ensures that the data being modified is protected from concurrent changes. One key feature of PostgreSQL is the SELECT FOR UPDATE clause, which locks the selected rows against concurrent updates. In this blog, we will explore how to implement the SELECT FOR UPDATE clause in PostgreSQL and discuss its real-world use cases.
Read on to learn more about how it works.
Comments closed