Azure Data Studio Notebooks and SQL Prompt

One of the coolest and most useful features of Azure Data Studio (ADS) is Notebooks. If you haven’t seen them, notebooks allow you to combine text with markdown and runnable code in the same document, and they are shareable. Notebooks might be used for teaching, documentation, or runbooks among other uses.

I recently heard about a public preview for Redgate’s SQL Prompt for ADS, and I wondered if the features would work only in a query window or if the features would also work in a notebook. After testing, I found that yes, SQL Prompt features do work in notebooks. It doesn’t matter if you are working in a query window or a notebook, you can take advantage of SQL Prompt!

To try it out, I installed the most recent ADS version and the public preview for SQL Prompt for ADS. The first feature I tested was formatting.

Formatting

There is some built-in formatting in ADS, but there is just one style. SQL Prompt has several built-in styles, plus it imports styles from SSMS if you have it installed and allows you to create your own custom styles.

You must tell ADS that you want to use SQL Prompt for formatting either for the current operation or as a default. To set it up as the default, right-click in a notebook code cell or in the query window. Then select Format Document With as shown in Figure 1.

ads1

 

Figure 1: Choose a formatter

A dialog will pop up. Click Configure Default Formatter as shown in Figure 2.

Figure 2: Configure the default

Then click Redgate SQL Prompt as shown in Figure 3.

 

Figure 3: Select SQL Prompt

From there on, SQL Prompt will be the default formatting tool for both the notebooks and query window.

One advantage of SQL Prompt is that you have several formats from which to choose. There are five built-in formatting styles and any created in SSMS will automatically import. To see the styles, first bring up the command pallet by typing CTRL+SHIFT+P. You can search for everything related to Prompt as shown in Figure 4. Select SQL Prompt: Change Active Formatting Style.

 

Figure 4: Find Change Active Formatting Style

Here you’ll see a list of built-in styles plus any of your custom styles shown in Figure 5. You can select any of them to be your default style going forward.

 

Figure 5: Select a formatting style

Notice in Figure 4 that you can also delete, create, or edit formatting styles. Now, formatting the way you like it is just a right-click away in both notebooks and the query window.

Another feature available in this preview is the well-loved snippets. Learn about that next!

Snippets

Snippets save SQL Prompt users tons of time. They are as simple as typing in three or four characters to get anything from a SELECT statement to a function definition. Just like formatting, this feature can be seen in the notebooks. Figure 6 shows the famous SSF (select star from) snippet.

 

Figure 6: Select star from

Snippets are great for things that you type often, like the team’s official comment section for procs. To see the available snippets, go to the command pallet and search for Preferences: Configure User Snippets as shown in Figure 7.

 

Figure 7: Configure user snippets

You’ll be able to see your custom snippet list and create your own.

Conclusion

SQL Prompt is a popular tool that has worked in SSMS and Visual Studio to save you time writing T-SQL code. Now, there is a preview of SQL Prompt that runs in Azure Data Studio, and you get these features in both query windows and notebook

Posted in Uncategorized | Leave a comment

Free SQL Server Reporting Services Class

As part of the Redgate Community Circle project, I’m teaching a seven-week class online class, “Introduction to SSRS”.  This class is perfect for people who need to learn SSRS from the beginning. I just completed week three, but you can jump in any time, and the class will continue to be available on Redgate’s University. The class consists of recorded weekly sessions, homework, and a forum where you can ask questions.

Here’s the agenda:

  • Introduction, tools, setup
  • Data sources, datasets, and your first report
  • Parameters
  • Grouping
  • Interactive report features
  • Visualizations
  • Deploying and managing reports

Be sure to check out my class or any of the other Redgate Advocate’s classes. These include:

Kendra Little’s T-SQL for Beginners 

Steve Jones’ DAX and Power BI

Grant Fritchey’s SQL Server Query Performance Tuning

There is definitely something for everyone!

Posted in SSRS | Leave a comment

Slides from Data Platform Discovery Day and finding the limits of a WHERE clause

Yesterday, I was privileged to speak at the first Data Platform Discovery Day. This event was virtual and the brainchild of Data Platform MVP Matt Gordon (@sqlatspeed) with one day in US time zones and one for Europe. He realized that many folks will be looking to grow their marketable skills due to the uncertain economic times. The topics were all geared for beginners, and my session was an introduction to T-SQL (slides and code). Redgate also sponsored the event.

My session covered SELECT, FROM, WHERE, and ORDER BY. I learned something new during my session, too! Someone asked how many predicates can you have in a WHERE clause? My answer was that there probably is a limit, but that you should never reach it. Someone in the audience did give an answer, I think it was 4096. That turned out to be false, at least in the case of SQL Server. However, that is the limit on the number of expressions in the SELECT list.

The documentation says:

“There is no limit to the number of predicates that can be included in a search condition.”

So, the audience member probably found some information about a different database system.

After playing around with this, I did manage to reach a limit, at least where a query would no longer run. I created a script to run a query with a large number of predicates:

DECLARE @statement VARCHAR(MAX) = 'select name
from sys.databases
where 1 = 1 ';

SET @statement = ' select name from sys.databases where 1 = 1 ';
SET @statement = @statement +
   REPLICATE(CAST(' and 1=1' AS VARCHAR(MAX)), 9000);
SELECT LEN(@statement);

EXEC (@statement);

Somewhere between 8000 and 9000 predicates (depending on the server), I got this error:

Msg 8631, Level 17, State 1, Line 3

Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

I was hoping that I could increase the max memory settings and get more predicates to work but going from 4 GB to 32 GB didn’t make that much difference. I also wondered if it was the length of the statement or actually the number of predicates. When I switched to this query which has a much larger number of characters, it behaved the same:

DECLARE @statement VARCHAR(MAX) = 'select name
from sys.databases
where 1 = 1 ';

SET @statement = ' select name from sys.databases where 1000 = 1000 ';
SET @statement = @statement 
   + REPLICATE(CAST(' and 1000 = 1000' AS VARCHAR(MAX)), 8000);
SELECT LEN(@statement);
EXEC (@statement);

Ok, comparing a number to itself is quite simple. What if the expression were more complex? Nope, I got the same behavior when I ran this one:

DECLARE @statement VARCHAR(MAX) = 'select name
from sys.databases
where 1 = 1 ';

SET @statement = ' select name from sys.databases where 1000 = 1000 ';
SET @statement = @statement 
   + REPLICATE(CAST(' and 1 = case 
     when name = ''master'' then 1 else 0 end' AS VARCHAR(MAX)), 9000);

SELECT LEN(@statement);

EXEC (@statement);

What’s the moral of this story? Well, there are two: Matt and crew put on a first-class event for beginners, and I like breaking things.

Posted in T-SQL | Leave a comment