There are many new features in SQL Server 2022. In relation to T-SQL querying, there are a few as well and they usually are left for last in face of many other new optimization features. Sample scenario These samples are built on the AdventureWorksDW2019 database installed in a SQL Server 2022 CTP 2. Date_Bucket Let’s … Read more
Or: How to make magic tricks with T-SQL Starting our magic show, let’s first set the stage: Count Distinct doesn’t work with Window Partition Preparing the example In order to reach the conclusion above and solve it, let’s first build a scenario. Using Azure SQL Database, we can create a sample database called AdventureWorksLT, a … Read more
It is easy to import Excel data into database tables via TSQL, using OLEDB, either by the OPENROWSET function or linking to the spreadsheet as a server. The problem is that there are certain things that aren't obvious that you need to know about, and you feel awkward about asking such simple questions. … Read more
SQL Server User-Defined Functions are good to use in most circumstances, but there just a few questions that rarely get asked on the forums. It is a shame, because the answers to them tend to clear up some ingrained misconceptions about functions that can lead to problems, particularly with locking and performanc… Read more
Although SQL Server supports XML well, XML's little cousin JSON gets no love. This is frustrating now that JSON is in so much demand. Maybe, Phil suggests, it is possible to leverage all that XML, and XPath, goodness in SQL Server to produce JSON in a versatile way from SQL Queries? Yes, it so happens that there are plenty of alternatives.… Read more
The calendar is inherently complex by the very nature of the astronomy that underlies the year, and the conflicting historical conventions. The handling of dates in TSQL is even more complex because, when SQL Server was Sybase, it was forced by the lack of prevailing standards in SQL to create its own ways of processing and formatting dates and times. Joe Celko looks forward to a future when it is possible to write standard SQL date-processing code with SQL Server.… Read more
To test SQL, you need test data. There are usually many reasons why you can't use production data. Although it is usually enough to use a utility to generate test data, sometimes your requirements will compel you to resort to code to supplement this. Hugo shows how he used SQL and C# to generate large volumes of test data involving related columns and complex distributions.… Read more
It isn't a problem to use deprecated TSQL features until it comes to the time to move the database to a server with a newer version of SQL Server, because The Upgrade Adviser tool will tell you what needs to be changed. An alternative is to flush out the use of archaic features via scripting during the development process so there are no surprises later. Jonathan Allen shows how… Read more
There are several frameworks for assisting with the testing of SQL Server databases, but tSQLt is popular because it is written in TSQL and is simple for a database developer to set up and use. It doesn't get in the way. Rob Sheldon shows you how to get started. … Read more
Pivoting SQL Server tables is always awkward, even with the PIVOT and UNPIVOT operators. If you want to get the job done without GROUP BY or PIVOY, here is a way to do it using only REPLACE.… Read more
It is a measure of the range of facilities that are available to the database developer nowadays that three different books on the same general topic of TSQL programming can actually complement each other. … Read more
SQL Server Management Studio (SSMS) has many features that make it particularly convenient for the database developer and DBA. Registered Servers are typical: poorly documented, but essential for the busy DBA, since they are easily scriptable via TSQL or PowerShell as well as the SSMS GUI, to access groups of servers to perform a wide range of tasks.… Read more
There are seven common antipatterns in TSQL coding that make code perform badly, and three good habits which will generally ensure that your code runs fast. If you learn nothing else from this list of great advice from Grant, just keep in mind that you should 'write for the optimizer'.… Read more
As well as running TSQL and PowerShell, the free SQL Scripts Manager tool can also run Python scripts. This allows even more sophisticated scripting possibilities for managing automated database tasks. … Read more
Despite the neglect of the basic ODBC drivers over the years, they still afford a neat way of reading from, and writing to, CSV files; and to be able to do so in SQL as if they were tables is somewhat magical. Just to prove it is possible, Phil creates a CSV version of AdventureWorks as a linked server.… Read more
When you are using SSIS, there soon comes a time when you are confronted with having to do a tricky task such as searching for particular connection strings in all your SSIS packages, or checking the execution history of scheduled SSIS jobs. You can do this type of work effectively in TSQL as Feodor explains.… Read more
In the first of a series of articles on automating the process of building, modifying and copying SQL Server databases, Phil Factor demonstrates how one can generate TSQL scripts for databases, selected database objects, or table contents from PowerShell and SMO.… Read more
DBAs are usually charged with the administration of Reporting Services, but are often short on guidance on how to go about such tasks as planning, documenting and troubleshooting those aspects that are specific to the SSRS service. Feodor gives some tips, custom SSMS reports, and useful TSQL queries, based on his experience.… Read more
SQL Server's functions are a valuable addition to TSQL when used wisely. Jeremiah provides a complete and comprehensive guide to scalar functions and table-valued functions, and shows how and where they are best used.… Read more
Are Common Language Runtime routines in SQL Server faster or slower than the equivalent TSQL code? How would you go about testing the relative performance objectively? Solomon Rutzky creates a test framework to try to answer the question and comes up with some surprising results that you can check for yourselves, and offers some good advice.… Read more