Erik Darling talks about indexed views. The set of limitations is huge and painful, just as Erik describes. For every instance in which I was successfully able to build an indexed view, there were probably a couple of dozen instances in which it was a great idea until I hit one or more of the limitations that Erik describes.
Comments closedCurated SQL Posts
Osheen MacOscar builds a package:
In this blog series (this and the next blog) I am going to demonstrate how to use Poetry to create a Python package, set up testing infrastructure and install it. I am going to be creating a wrapper around the Fantasy Premier League API and creating a function which can create a weekly league table.
This is a straightforward example of how to create a new Python package and add a function call to it.
Comments closedRick Dobson inserts some data:
There are numerous use cases for multi-file imports of CSV files into a SQL Server table:
- Dynamic SQL Server bulk insert loads are especially appropriate for tasks that extract content from multiple files to a SQL Server table where the source file names change between successive import jobs.
- Static bulk insert loads target scenarios where the source file names do not change between successive import jobs.
Read on for examples of how to implement each. Admittedly, bulk insert has rarely worked all that well in my experience, whether due to permissions mishaps, poor data integrity, or sudden changes in data types between file runs. But it does tend to work a lot better if you have a specified data interchange format and a standardized process to prepare the data and make it available on disk for insertion.
Comments closedBrendan Tierney follows up on a SQL Firewall post:
In a previous post, we’ve explored some of the core functionality of SQL Firewall in Oracle 23ai, In this post I’ll explore some of the other functionality that I’ve had to use as we’ve deployed SQL Firewall over the past few weeks.
Sometimes, when querying the DBA_SQL_FIREWALL_VIOLATIONS view, you might not get the current up to-date violations, or if you are running it for the first time you might get now rows or violations being returned from the view. This is a slight timing issue, as the violations log/cacbe might not have been persisted to the data dictionary. If you end up in this kind of situation you might need to flush the logs to to data dictionary. To do this, run the following.
Click through for that command, as well as a few other scenarios and commands that may be of interest.
Comments closedTwinkle Cyril enumerates the lock types in Fabric Data Warehouse:
Fabric DW supports ACID-compliant transactions using standard
T-SQL (BEGIN TRANSACTION, COMMIT, ROLLBACK)and enforces snapshot isolation across all operations. Locks in Fabric Data Warehouse are used to manage concurrent access to metadata and data, especially during DDL operations. Here’s how locking works:
Click through for a chart. The locking policy is a lot simpler than what we see in SQL Server and you can see a description of the pros and cons of that simpler approach.
Comments closedLouis Davidson can easily get to 20:
I was reading LinkedIn posts the other day when I saw this blog about what was apparently an interview question about some forms of a COUNT aggregate function
This was apparently asked in an interview. What will each of these constructs do in a SQL statement:
COUNT(*) = ?COUNT(1) = ?COUNT(-1) = ?COUNT(column) = ?COUNT(NULL) = ?COUNT() = ?
There’s one tricky bit in this set. Louis then takes it a bit further with CASE expressions and variables, so check out the post for the answers as well as those additional examples in T-SQL.
Comments closedThe methods that I will share here allow an attacker to either conceal his identity or even evade auditing completely.
Most of these commands require sysadmin privileges. However, if your goal is to audit every access to sensitive data, this typically means “all users” – not with an exception for administrators. Because of this, it’s important to understand these methods so you can make an informed decision about whether to include them in your auditing scope.
Some of these are wildly impractical, but they do work and Andreas has mitigations for each.
Comments closedIn this video, I take you through some of the limitations of window functions in SQL Server, including the lack of intervals and oddities around the APPLY operator.
Intervals would be a really powerful addition to SQL Server’s window function capabilities, but I’m not going to hold my breath for them.
Comments closedAaron Bertrand extends a prior article:
In my previous tip, Managing Unused Indexes in SQL Server Availability Groups – Part 1, I showed how I use dynamic SQL to gather index usage statistics for a given table from all replicas in an availability group. Knowing the usage from all workloads is definitely better than focusing on only the primary or a single secondary. But what if I want to make more informed decisions, incorporating row counts, size, and index columns into this output?
Read on to see how you can incorporate this additional information.
Comments closedJovan Popovic announces that JSONL format support is now generally available in Microsoft Fabric:
The OPENROWSET function that can read JSONL format empowers you to easily read and ingest JSONL files – for example log files, social media streams, machine learning datasets, configuration files, and other semi-structured sources. With the versatile OPENROWSET T-SQL function, you can reference and query JSONL files as if they were tables, eliminating the need for manual parsing or complex transformation steps.
Read on to see examples of how to ingest and use data in the JSON Lines format.
Comments closed