One of the new Extended Event available in SQL Server 2022 is the query_antipattern. This extended event allows to identify anti-patterns on the SQL queries sent to the server. An anti-pattern in this case is some code that the SQL Server optimizer can’t do a great job optimizing the code (but cannot correct the … Read more
A student of mine sent me a T-SQL challenge involving gap-filling of missing dates and balances. I found the challenge interesting and common enough and figured that my readers would probably find it interesting to work on as well. I’ll present the challenge and three possible solutions, as well as the results of a performance … Read more
When I first saw a bullet item stating SQL Server 2022 would support instant file initialization for log file growth, I was excited. When I later learned it only applies to automatic growths, and only those of 64 MB or less, I was a little less excited. With those limitations, I was skeptical this enhancement … Read more
One of the more challenging technical details of columnstore indexes that regularly gets attention is the need for data to be ordered to allow for segment elimination. In a non-clustered columnstore index, data order is automatically applied based on the order of the underlying rowstore data. In a clustered columnstore index, though, data order is … Read more
I have come across a lot of use cases for manufacturing rows on the fly, aside from the common goal of populating a large data set such as a numbers or calendar table. A few favorites include building sample data, pivoting an unknown number of columns, data extrapolation, and filling gaps in date or time … Read more
Power BI and Azure are two related services, but with a considerable independence between them. Power BI share the Azure Tenant for security management, but in relation to networking, they are in completely different environments. Frequently Power BI needs to access to Azure resources, such as Azure SQL. If we use the easier and most … Read more
Sometimes when working with very large sets of data, you may not want to return all of the data at once. I discussed using TOP in my previous article, which allowed you to only get a number of rows from the start of the results from a query. However, if you want to see the … Read more
When you are writing T-SQL scripts to save away, for example one to create a new database, there are often optional bits of code you need to run sometimes, and not others. In the creating a database script, there are quite a few parameters you will want as part of your script. The database name, … Read more
I’ve been at this for a while now, and have a very particular set of rules and coding conventions that I follow when writing and, more importantly, reviewing T-SQL code. I often perform code reviews and thought it would be fun to frame this exercise: a completely fictitious stored procedure hits my desk, I’ll reject … Read more
There might be a time when you might want to return just a few rows of a result set, instead of the complete set. This might be useful if you want to just validate a selection criteria or a few rows of data. For whatever the reason the TOP clause can be used to return a specific number or a percentage of rows from a result set. This article will cover using the TOP clause in a SELECT statement and how it can be used to return a partial set of records.… Read more
Once data is in a table it needs to be maintained. New records will need to be added, and existing records will need to be updated, and/or deleted. To keep data maintained, the insert, update or delete actions may need to be performed. This article will show how those three data manipulation language (DML) actions can be synchronized together within a single basic MERGE statement.… Read more
In this article, I want to talk about a topic that you may never need. The only time I have really had need to look at the bits in a byte pattern. In (what is now) ancient versions of SQL Server didn't have such self-describing columns like in its metadata objects like it does now.… Read more
If only the entire world used UTC, wouldn’t life be so much easier? We can dream, can’t we? While some software applications can live in an ecosystem where all dates and times can be stored in a single time zone with no conversions needed, many cannot. As applications grow and interact more with international customers, … Read more
Over time data in SQL Server tables needs to be modified. There are two major different aspects of modifying data: updating and deleting. In my last article “Updating SQL Server Data” I discussed using the UPDATE statement to change data in existing rows of a SQL Server table. In this article I will be demonstrating … Read more
Once data is inserted into a table, data typically needs to be maintained as time goes on. To make changes to an existing row or a number of rows, in a table, the UPDATE statement is used. This article shows how to use the UPDATE statement to modify data within a SQL Server table. Syntax … Read more
Before data can be read from of a SQL Server database table, the table needs to contain rows of data. One of the most typical ways to get data into a table is to use the INSERT statement. One row or multiple rows can be inserted with a single execution of an INSERT statement. You … Read more
In order to test graph structures, I needed a large set of random data. In some ways, this data will resemble the IMDB database I will include later in this chapter, but to make it one, controllable in size and two, random, I created this random dataset. I loaded a set of values for an … Read more
During Build 2022, Microsoft announced a new feature, Datamarts for Power BI. MVP Dennes Torres explains this new feature and provides and example. … Read more
Subscribe for more articles
Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.
