Home >Database >Mysql Tutorial >How Can master..spt_values Efficiently Split Comma-Separated Values in SQL Server?
Master..spt_values is a system table in Microsoft SQL Server that contains various lookup and projection tables used by system stored procedures. It enables efficient data manipulation by providing pre-defined sequences of numbers, bitmasks, and other commonly required values.
Lookup tables allow for expanding numerical values to their corresponding string representations, such as converting lock types from numbers to names. On the other hand, projection tables provide a way to iterate through a range of values, which is useful for splitting columns into multiple rows.
Type "P" in master..spt_values denotes a projection table. It contains a sequence of consecutive numbers from 0 to 2047. This table enables the efficient projection of a set of values through various operations, such as counting or aggregation.
The benefit of using master..spt_values (Type "P") for splitting a CSV column lies in its performance and efficiency. By leveraging the pre-calculated sequence of numbers, SQL Server can avoid creating and inserting into a temporary table, which would be necessary if the sequence were generated dynamically. This can significantly improve the execution speed of queries that perform column splitting operations.
The code in question demonstrates how to split a CSV column into multiple rows using master..spt_values:
SELECT T.col1, RIGHT(LEFT(T.col4,Number-1), CHARINDEX(',',REVERSE(LEFT(','+T.col4,Number-1)))) FROM master..spt_values, table1 T WHERE Type = 'P' AND Number BETWEEN 1 AND LEN(T.col4)+1 AND (SUBSTRING(T.col4,Number,1) = ',' -- OR SUBSTRING(T.col4,Number,1) = '') --this does not work correctly anyway
This code leverages the Type "P" table in master..spt_values to create a sequence of numbers that соответствует each character in the CSV column (col4). It then uses these numbers to split the column into rows based on the comma delimiter.
The above is the detailed content of How Can master..spt_values Efficiently Split Comma-Separated Values in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!