A Fine Slice Of SQL Server
Sandeep Pawar drinks your milkshake:
In Fabric, if you want to query a delta table from a lakehouse in another workspace, you create a shortcut to that table. Similarly, in Eventhouse, you can also create shortcuts to Eventhouses in other workspaces, but the option might not be immediately obvious in the GUI. If you click on New > OneLake shortcut, it creates a shortcut to a delta table, not an Eventhouse.
Click through to see how to do this via UI and programmatically.
Comments closedKenneth Omorodion reminds us that the Kimball model is still quite valuable:
In modern data warehousing, how we handle updates to dimension tables is crucial. There are several approaches; but the decision often comes down to two primary strategies: Slowly Changing Dimensions (SCD) Type 2 and overwriting tables. Each has its own benefits, use cases, and trade-offs. This tip will explore the two methods and why SCD Type 2 is often a better option in many data warehouse scenarios.
Read on for this overview of the benefits of type-2 slowly changing dimensions, as well as a little bit of coverage of several other types of slowly changing dimensions.
Comments closedDennes Torres is looking for the top dog:
When learning something new, we always compare it with what we know. In this case, we end up comparing KQL with SQL.
In SQL, when we need to get the top rows based on a grouping, the process is not easy. For example, let’s say that in a table containing taxi rides, we want to retrieve the record of the ride with the highest fare on each day.
There are multiple ways to do this, none is too easy. One of the methods is to create a row_number based on the day. This can be achieved using what’s called in SQL as window function. In this case row_number with a stablished window in the result based on the date.
Click through to see how you can use arg_max() in KQL to do this.
Steve Jones asks, what’s in a name?:
I had someone ask me how to rename a SQL Server database recently. They were doing some development work and wanted to rename databases to test an application. I thought I remembered, but in this post, I show I learned something.
Read on for the answer, as well as some notes about it. One additional thing I’d point out is that renaming the database doesn’t rename the underlying files.
Comments closedWell…..I just learned about these and thought that it would be good to understand them a little more and have some links to read more about them. I honestly don’t have a lot of SQL 2022 servers in our customers SQL estate, so this has flown under the radar for me. This will be an attempt to put some spread-out information in a one-stop shop.
Click through for a table with information on roles, as well as lists for permissions.
Comments closedBrent Ozar builds a view but a special one:
That query gets the top 100 users who have the most accepted answers. On my server, that takes about a minute to run – unacceptably long for a public-facing web site, for example.
Does that data need to be up to the second? Of course not. The leaderboard of the top 100 answerers isn’t going to change all that quickly. People who have successfully answered hundreds of questions aren’t going to suddenly disappear, nor is someone else suddenly going to rocket to the top.
For report queries like this, Postgres offers materialized views: a view that’s written to disk, and then updated only when you want to update it.
Read on to see how you can create one in PostgreSQL. Brent does touch on one of the differences between indexed views in SQL Server and materialized views in PostgreSQL while covering the process of creating, querying, and updating materialized views. In discussing how to update them, Brent covers en passant a second difference between indexed views in SQL Server and materialized views in PostgreSQL. Whether the “keep it up to date at all times” approach beats the “update it when you want but let data go stale in the meantime” approach is better, that’s something worth debating.
Comments closedReitse Eskens shares some thoughts:
It’s been a long time coming, but all of a sudden there were a number of posts from Microsoft announcing the first preview of SQL Server Management Studio 21. This is big as it’s quite the overhaul from the SQL Server Management Studio we’re used to.
To give you some idea, here are my first impressions.
Vlad Drumea also takes a peek:
SQL Server Management Studio 21 Preview 1 was released 2 days ago, so I took it out for a spin and here are my first impressions so far.
Read on to see what’s in the preview today, some of the new functionality, and whether SSMS 21 is faster than SSMS 20.
2 Comments