Press "Enter" to skip to content

Category: Uncategorized

Data Paging using Common Table Expressions

Steve Stedman takes us through one method of generating pages of data:

I can remember the first time that I worked on data paging code. I had to page through web site search results 20 at a time on a given web page. My task was to understand how it had been written and to do some bug fixing. After reviewing about 2,000 lines of code, and reviewing the seven different variables that were being used to know the current page, the next page, the previous page, the page size, the row at the top of the page, the row at the bottom of the page, and the number of pages, I finally understood what the code was intended to do. What a mess that was, but beyond the mess, the performance was horrible. The way the page worked was that based on the page you were on, all of the rows would be queried, then a loop would read through all of the rows before the current page, then loop through the rows on the current page displaying them on the page, and finally it would ignore the results after the current page. So page 1 was slow, page 2 was slower than page 1, page 3 was slower than page 2 and on and on.

Since that point I have implemented several different data paging algorithms myself, all better than the original implementation but none as elegant as the CTE way of doing data paging. I used to look at data paging as a painful task, but thanks to the SQL Server implementation of CTEs there is no more pain.

Steve also covers OFFSET and FETCH. This technique won’t be great with enormous data sets, but for moderate-sized data sets which query quickly, it works. This is one area which is quite painful, and the best (and wackiest) solution I’ve come up with in SQL Server when the initial query is quite expensive is to create tables with random names to store results and row numbers, populate a table the first time a query is run, and query that table on subsequent runs, using the RETURN value in a stored procedure to pass along the name of the table to access. Granted, that solution works best with static data and you’d want to have a method to clean up those tables after they’re no longer in use (like storing a list of those tables and their last access dates and times). So it’s a mess.

Comments closed

Memory-Optimized Table Variables and tempdb Contention

Erik Darling notes that memory-optimized table variables can be useful in specific circumstances:

First, yes, they do help relieve tempdb contention if you have code that executes under both high concurrency and frequency. And by high, I mean REALLY HIGH.

Like, Snoop Dogg high.

Because you can’t get rid of in memory stuff, I’m creating a separate database to test in.

Been there. When tempdb object creation causes massive contention, this certainly alleviates the stress.

As Erik notes, there are some tradeoffs to this, meaning that you have a real decision to make rather than simply using memory-optimized user-defined table types as a starting point.

Comments closed

Generating Alerts from Power Automate

Ed Hansberry shows how to create a Power Automate alert off of SQL Server data:

I’m going to show you how to do this in Power Automate with just a few steps. Let’s get started. In my example, I am going to return a table when a customer has placed an order where the order quantity will not divide evenly into the case pack. So if they order 100 units and the cases contain 24 each, I want to alert the order entry person to tell them the customer has effectively ordered 4.1667 cases, which isn’t allowed. They will need to order either 96 units or 120 units to get 4 or 5 cases.

Read on to see how.

Comments closed

Contrasting Data Warehouses with Power BI Dataflows

Reza Rad makes a comparison:

Dataflow is the data transformation service in Power BI, and also some other Power Platform services. Data Warehouse is the cloud storage and also compute engine for data. I often get this question that: “Now that we have dataflow in Power BI, should we not use the Data warehouse? What are the differences? which is better? When to use what?” This article and video, explains answer to these questions.

I’m probably a bit lower on self-service BI compared to others. When I see something like Dataflows, it reminds me too much of a mess of Excel spreadsheets on shared drives. There’s a lot of relevant business knowledge embedded in those disbursed locations, and bringing it together becomes as much a forensic exercise as it is architectural.

Comments closed

Changing SQL Server Settings with dbatools

Mikey Bronowski walks through a slew of dbatools cmdlets which replicate behavior from SQL Server Management Studio:

The reasons to alter the database might be as many as different users. Some want to rename the old database, or maybe change the owner or recovery model after restoring the database. Enabling features like Query Store is also common. Most of this can be done from the Database Properties screen and as well with dbatools.

This is part of a whole series on dbatools which Mikey has been putting together, and to which you can find a link as you click through.

Comments closed

SQL Server Assessment Extension for Azure Data Studio

Ebru Ersan announces a new extension for Azure Data Studio:

SQL Server Assessment Extension for Azure Data Studio provides a user interface for evaluating your SQL Server instances and databases for best practices. It uses SQL Assessment API to achieve this. In this preview version, you can:

– Assess a SQL Server or Azure SQL Managed Instance and its databases with built-in rules (Invoke Assessment)

– Get a list of all built-in rules applicable to an instance and its databases (View applicable rules)

– Export assessment results and list of applicable rules as script to further store it in a SQL table

It’s in preview status and requires version 1.19.0 (this month’s version) of Azure Data Studio at a minimum.

Comments closed

Generating Scripts to a Notebook with SSMS

Taiob Ali tries out a new feature in SQL Server Management Studio:

SQL Server Management Studio (SSMS) was released on April 7th, 2020. You can download this latest version from this link. 18.5 is an update to 18.4 with these new items and bug fixes.

One of the features added in this release is to select ‘Azure Data Studio‘ Notebook as a destination for Generate Scripts wizard.

Now you can send the objects definition of Table, View, Stored Procedure, Function along with sample call, sample data, and my comments all packaged in one Azure Data Studio Notebook. I can see scope for better communication between business partners, developers, and database engineers.

Click through for an example of the process.

Comments closed

When to Index Temp Tables

Erik Darling thinks about what phase in a procedure one should index a temp table:

You already know that your temp table needs an index. Let’s say there’s some query plan ouchie from not adding one. You’ve already realized that you should probably use a clustered index rather than a nonclustered index. Adding a nonclustered index leaves you with a heap and an index, and there are a lot of times when nonclustered indexes won’t be used because they don’t cover the query columns enough.

Good. We’ve fixed you.

But, like, when should you create the index?

I try to do as many inline operations as I can with temp tables because doing so means you might be able to take advantage of temp table reuse, and on a frequently-running procedure, that can make a difference.

Comments closed

SSMS Regular Expressions

Tim Mitchell looks at regular expressions in SQL Server Management Studio:

Regular expressions (or simply regex for short) have long been used by system administrators and data professionals for searching and manipulating text. Regular expressions allow the user to find, replace, and manipulate text based on the pattern they define in the expression. While every text editor allows simple search-and-replace capabilities, regex allows for searching for partial matches, using wildcards, and even integrating special characters (such as newlines and tabs) into the search or replacement text.

Regular expressions have been a part of SSMS for as long as I can remember, and make the process of pattern-based SQL code search much easier. In this tip, I’ll show you a couple of brief examples of the use of regular expressions for working with SQL code in Management Studio.

Regular expressions have been in the product for a long time, but the set of available regular expressions changed when SSMS moved over to the Visual Studio shell. And in some ways (particularly around capture groups), that was a change for the worse.

Comments closed

Power BI and Azure Synapse Analytics

James Serra gives us some insights on the future of Power BI and how it relates with Azure Synapse Analytics today:

As an example of the speed of each layer, during an Ignite session (view here), there was a Power BI query run against 26 billion rows that was returning a sum of store sales by year. The same query was run three times using a different layer:

1. Using a DirectQuery against tables in SQL DW took 8 seconds
2. Using a DirectQuery against a materialized view in SQL DW took 2.4 seconds.  Note you don’t have to specify that you are using a materialized view in the query, as the SQL DW optimizer will know if it can use it or not
3. Using a Aggregation table that is Imported into Power BI took 0 milliseconds

Keep in mind this is all hidden from user – they just create the report.  If they do a query against a table not in memory in Power BI, it will do a DirectQuery against the data source which could take a while.  However, due to SQL DW result-set caching, repeat DirectQuery’s can be very fast (in the Ignite session they demo’d a DirectQuery that took 42 seconds the first time the query was run, and just 154 milliseconds the second time the query was run that used result-set caching).

There’s some interesting information in here, especially around Power BI eventually taking over Azure Analysis Services’ space in the market.

Comments closed