-
Jan 10, 2025 | 
red-gate.com | Edward Pollack
Previously, we introduced and discussed the Parquet file format and SQL Server and why it is an ideal format for storing analytic data when it does not already reside in a native analytic data store, such as a data lake, data warehouse, or an Azure managed service. Both Python and the Parquet file format are quite flexible, allowing for significant customization to ensure that file-related tasks are as optimal as possible.
-
Nov 19, 2024 | 
red-gate.com | Edward Pollack
When analytic data is stored directly in SQL Server, a clustered columnstore index is an ideal place for that data to reside. It is column-based, highly compressed, and supports a wide variety of optimizations for both reads and writes. When analytic data is stored in files, such as for use in Azure Data Factory, Synapse, or Fabric, the Parquet file format will often be used.
-
Aug 22, 2024 | 
red-gate.com | Edward Pollack
Like XML, JSON is an open standard storage format for data, metadata, parameters, or other unstructured or semi-structured data. Because of its heavy usage in applications today, it inevitably will make its way into databases where it will need to be stored, compressed, modified, searched, and retrieved. Even though a relational database is not the ideal place to store and manage less structured data, application requirements can oftentimes override an “optimal” database design.
-
Jul 14, 2024 | 
red-gate.com | Edward Pollack
SQL Server provides a variety of ways to tune XML so that it provides consistent performance, consumes less space, all while ensuring efficient access to critical data. At its core, the metadata-styled XML format runs counter to the data that SQL Server is optimized to manage. Therefore, additional features were added to SQL Server over time that allowed for XML data to be indexed and compressed.
-
May 27, 2024 | 
red-gate.com | Aaron Bertrand |Ben Johnston |Edward Pollack
Part of a series: [ Part 1 | Part 2 | Part 3 | Part 4 ]In the previous posts in this series, I described how I have optimized a long-running set of routines by processing databases, tables, and even subsets of tables in parallel.
-
May 15, 2024 | 
red-gate.com | Aaron Bertrand |Ben Johnston |Edward Pollack
Part of a series: [ Part 1 | Part 2 | Part 3 | Part 4 ]In part 2 of this series, I showed an example implementation of distributing a long-running workload in parallel, in order to finish faster. In reality, though, this involves more than just restoring databases. And I have significant skew to deal with: one database that is many times larger than all the rest and has a higher growth rate.
-
Feb 28, 2024 | 
red-gate.com | Edward Pollack
String manipulation is an inevitable task for developers and data professionals alike. Despite all the best efforts to normalize databases, eventually we are faced with some sort of text-based data stored within a relational database and need to extract detailed information from it. Those of us who have tackled these challenges fully understand how code can quickly go from simple to absurdly complex, one function at a time.
-
Jan 7, 2024 | 
red-gate.com | Aaron Bertrand |Robert S. Sheldon |Julian Daizan Skinner |Edward Pollack
I don’t think I’ve ever had a great impression of Azure SQL Database as a place for production applications. In its early days, it was missing several important features (like data compression). While that hole has been plugged, there are still other limitations and differences you should be aware of, like T-SQL differences, lack of SQL Server Agent, and some observability gaps.
-
Oct 23, 2023 | 
red-gate.com | Edward Pollack |Aaron Bertrand
There are plenty of applications and tools available that allow for the movement of data in and out of SQL Server. Some tools are built by Microsoft, such as SSIS or Azure Data Factory. Others are created by third parties, such as Databricks or Snowflake. Still other available options make use of SQL Server features and rely on our own ability to write efficient code to get data from point A to point B.
-
Oct 15, 2023 | 
red-gate.com | Aaron Bertrand |Edward Pollack
54 60599 views 60599 views 54 I’ve quietly resolved performance issues by re-writing slow queries to avoid DISTINCT. Often, the DISTINCT is there only to serve as a “join-fixer,” and I can explain what that means using an example. Let’s say we have the following grossly simplified schema, representing customers, products, and product categories: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 Namenvarchar(255)NOTNULL, CONSTRAINTPK_CustomersPRIMARYKEY(CustomerID)...
