Articles tagged SQL

04 July 2022
04 July 2022

What’s new in T-SQL in SQL Server 2022

0
20
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
07 October 2020
07 October 2020

What is database continuous integration?

Have you ever longed for a way of making the delivery of databases more visible, predictable and measurable? Do you ever wish that they would be of better quality, quicker to change, and cost less? Grant Fritchey explains some of the secrets of doing Continuous Integration for Databases to relieve some of the pain-points of the Database Delivery process.… Read more
01 August 2020
01 August 2020

Temporary Tables in SQL Server

Temporary tables are used by every DB developer, but they're not likely to be too adventurous with their use, or exploit all their advantages. They can improve your code's performance and maintainability, but can be the source of grief to both developer and DBA if things go wrong and a process grinds away inexorably slowly. We asked Phil for advice, thinking that it would be a simple explanation.… Read more
07 December 2017
07 December 2017

SQL Server Internals: In-Memory OLTP

In this book Kalen Delaney introduces and explains how the 2016 In-Memory OLTP engine works. The Hekaton internals knowledge offered in this book will help you migrate existing tables or databases to Hekaton, and get faster performance from your SQL Server applications than you ever thought possible.… Read more
17 March 2016
17 March 2016

The Comeback of Migrations-Based Deployments

With database deployments, not all script-based processes are equal. Some use change scripts in a free-and-easy way, and some, which are normally called 'migrations-based approaches', have more discipline around them. In this article, Redgate Product Manager Elizabeth Ayer covers 'migrations', and shows some of the benefits that have come with new tooling which is specifically designed to assist the change script processes.… Read more
16 March 2016
16 March 2016

T-SQL Window Function Speed Phreakery: The FIFO Stock Inventory Problem

Sometimes, in the quest for raw SQL performance, you are forced to sacrifice legibility and maintainability of your code, unless you then document your code lavishly. Phil Factor's SQL Speed Phreak challenge produced some memorable code, but can SQL features introduced since then help to produce code that performs as well and is also easy to understand? Aunty Kathi investigates.… Read more
16 March 2016
16 March 2016

Database Lifecycle Management for ETL Systems

Few databases are self-contained. They take data from other sources, and publish them to downstream consumers of data. These ETL processes tend to grow in an unplanned organic way and so tand to cause trouble both in production and in deployment. Database Lifecycle Management systems allow all the teams to come together to ensure that ETL systems meet all requirements.… Read more
16 March 2016
16 March 2016

The SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store

The SQL Server 2016 Query Store can give you valuable performance insights by providing several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. However, it can also directly affect the performance of queries by forcing Execution Plans for specific queries.… Read more
15 March 2016
15 March 2016

Creating a Date Dimension in a Tabular Model

0
10
As well as its multidimensional model, SQL Server Analysis Services (SSAS) now has a tabular model of database that either runs in-memory or in DirectQuery mode. The in-memory analytics engine allows the users of Excel or Power View to base reports on tabular model objects. Having shown how to handle date-based information using the Multi-dimensional model, Dennes now turns his attention on the in-memory tabular model.… Read more
09 March 2016
09 March 2016

Database Migrations: Modifying Existing Databases

It should be simple to upgrade a database to a new version. It certainly can be, but if you need to preserve the existing data and you have made changes to the design to the tables then it can get complicated. If you are deploying changes to a heavily-used OLTP system on which an organization depends, then you need to understand, and be familiar with, the issues that can effect a database migration. Matthew Skelton explains the basic approaches.… Read more
07 March 2016
07 March 2016

The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters

Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is able to introduce extra common parameters that can be used by any Cmdlet or advanced function. When -PipelineVariable was introduced in V4, we all wondered what it could be used for. Laerte experimented and discovered that it could be a real convenience in every-day scripting with the pipeline.… Read more
07 March 2016
07 March 2016

When AUTO_UPDATE_STATISTICS Doesn’t Happen

When your SQL Server database is set to have its statistics automatically updated, you will probably conclude that, whenever the distribution statistics are out-of-date, they will be updated before the next query is executed against that index or table. Curiously, this isn't always the case. What actually happens is that the statistics only gets updated if needed by the query optimiser to determine an effective query plan.… Read more
25 February 2016
25 February 2016

Issue Tracking for Databases

Any database development project will be hard to manage without a system for reporting bugs in the code, anomalies and incidents from live environments, and for keeping track of new features. Matthew Skelton discusses the critical role of an integrated issue tracking system in database lifecycle management.… Read more
19 February 2016
19 February 2016

Better Ways to Build a Database

The purpose of a database build is simple: prove that what you have in version control can successfully create a working database. And yet many teams struggle with unreliable and untested database build processes that slow down deployments and prevent the delivery of new functionality. Grant Fritchey explains how to achieve an automated and reliable database build that is only as complex as the database system it needs to create.… Read more