Question:

Why use the undocumented master..spt_values table for splitting a column, and what are the benefits?

Answer:

Purpose of master..spt_values:

master..spt_values is a system table in Microsoft SQL Server that contains information used by system stored procedures within the database engine. It is commonly used for:

• Lookup Tables: It provides normalized data tables containing specific values, such as lock types, device types, and more.
• Projection Tables: It contains a table (Type = 'P') with consecutive numbers, allowing for projection operations.

Use in Column Splitting:

The "Type = 'P'" table in master..spt_values provides a convenient way to iterate through a column's values, especially in cases where the values are comma-separated or otherwise have a repeating structure. It enables:

• Extraction of individual values by using the consecutive numbers as a vector to navigate the data.
• Efficient processing of comma-separated or otherwise structured data in a single column.

Benefits:

The benefits of using master..spt_values for column splitting include:

• Convenient Projection: The table provides a readily available set of consecutive numbers, eliminating the need to create a temporary table for projection operations.
• Performance Improvement: System stored procedures that rely on projection operations benefit from the presence of master..spt_values, as it reduces the overhead of creating and managing temporary tables.
• Simplicity of Implementation: The use of master..spt_values simplifies the code necessary for column splitting and projection operations.