未加星标

T-SQL Tuesday #87 : Shiny New Toys

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

T-SQL Tuesday #87 : Shiny New Toys
This month's T-SQL Tuesday hosted by Matt Gordon ( @sqlatspeed ) is called "Fixing Old Problems with Shiny New Toys." Matt wants us to write about how we've solved old problems in new ways afforded by enhancements to SQL Server (or new features anywhere in the stack, I would guess).

Over the years I have helped a lot of people solve problems with either splitting strings apart or putting them back together. Both require an inordinate amount of code in older versions of SQL Server, but modern versions make this a cinch with new built-in functions. As an added bonus, these new built-ins perform better than the older kludges, at least in all cases I've tested.

Splitting strings

The function STRING_SPLIT() , introduced in SQL Server 2016, allows you to pass in a string and a delimiter, and outputs a row for each string in the set.


T-SQL Tuesday #87 : Shiny New Toys
There are a few limitations right now: you can only use single-character delimiters, and you can't control the output order. While in practice you will generally see the values come out in the order they appear in the original string, this is not guaranteed.

Concatenating strings

The function STRING_AGG() , which just missed the cut for SQL Server 2016, will be available in SQL Server v.Next hopefully later this year. This allows the opposite of STRING_SPLIT() : you can "join" values from multiple rows into a single string, using whatever delimiter you like.

This function is a little more fully-baked: The one-character restriction for the delimiter is not present, and you can dictate the order of concatenation using the WITHIN GROUP clause.

A Quick Example

In a post last year, I talked aboutFour Practical Use Cases for Grouped Concatenation. One of the examples where I show how to remove duplicates from a comma-separated string, and then re-assemble the string without the duplicates was quite messy. It used an expensive string-splitting function (and most are quite expensive ), along with the quirky FOR XML PATH syntax many of us use to join strings together:

DECLARE @names nvarchar(4000) = N'bob,sandy,tracy,bob,tracy,frank,tracy'; ;WITH x AS ( SELECT Item -- rather expensive UDF call FROM dbo.SplitStrings(@names, N',') GROUP BY Item ) SELECT STUFF((SELECT N',' + Item FROM x ORDER BY Item FOR XML PATH(''), TYPE ).value(N'.[1]',N'nvarchar(max)'),1,1,'');

This would produce:

bob,frank,sandy,tracy

With the new built-in functions, I can now simplify this substantially, and get the exact same results:

DECLARE @names nvarchar(4000) = N'bob,sandy,tracy,bob,tracy,frank,tracy'; SELECT STRING_AGG(Value, N',') FROM ( SELECT Value FROM STRING_SPLIT(@names, N',') GROUP BY Value ) AS x;

That may not look like a massive simplification, but don't forget about all the logic buried behind the table-valued function in the first example. And if you're like several shops I know, if you look across your codebase and see all the messy uses you have for either of these methods, the benefits should be even more clear and testing should bear that the performance savings compared to traditional, expensive methods are the sweetest part of the deal.

Other Resources

I've written about both of these functions and the kludges they finally replace in a lot more detail. Here are a few posts:

String Splitting

New Way Performance Surprises and Assumptions : STRING_SPLIT() STRING_SPLIT() in SQL Server 2016 : Follow-Up #1 STRING_SPLIT() in SQL Server 2016 : Follow-Up #2 Old Way Split strings the right way or the next best way Splitting Strings : A Follow-Up Splitting Strings : Now with less T-SQL

String Concatenation

New Way SQL Server v.Next : STRING_AGG() performance SQL Server v.Next : STRING_AGG Performance, Part 2 Old Way Grouped Concatenation in SQL Server Grouped Concatenation : Ordering and Removing Duplicates Four Practical Use Cases for Grouped Concatenation Comparing string splitting / concatenation methods

Don't worry, I have no intentions of going back and re-writing the "Old Way" posts to take advantage of the newer functions. But if you have a use case where you're splitting or concatenating now, and would like some help converting your code to work with the new functions, let me know in the comments below or at [email protected] I'll be happy to help, if I can.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLXMLSQL ServerUF
分页:12
转载请注明
本文标题:T-SQL Tuesday #87 : Shiny New Toys
本站链接:http://www.codesec.net/view/534764.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(55)