![]() ![]() One name with or & and the function will blow up. nodes ( 'i' ) AS y (i ) ) GOĪ very strong caveat has to ride along with the XML approach: it can only be used if you can guarantee that your input string does not contain any illegal XML characters. SplitStrings_XML ( NVARCHAR ( MAX ), NVARCHAR ( 255 ) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT Item = y. Then I added the following assembly and function to my database:ĬREATE FUNCTION dbo. So I took Adam's function, compiled the code to a DLL ( using csc), and deployed just that file to the server. If you are currently using a CLR function and this is not it, I strongly recommend you deploy it and compare – I tested it against a much simpler, VB-based CLR routine that was functionally equivalent, but the VB approach performed about three times worse than Adam's. He spent a lot of time fine-tuning this CLR function to efficiently parse a string. If you wish to use CLR, you should definitely borrow code from fellow MVP Adam Machanic before thinking about writing your own (I've blogged before about re-inventing the wheel, and it also applies to free code snippets like this). Here are the contenders I am going to compare (using SQL Server 2012,, on a Windows 7 VM with 4 CPUs and 8 GB of RAM): CLR And I may re-visit this in the future to examine the impact on other metrics, but for now I'm just going to focus on duration. I'm going to compare the performance of a few solutions – and focus on the question everyone always asks: "Which is fastest?" I'm not going to belabor the discussion around *all* of the potential methods, because several have already been eliminated due to the fact that they simply don't scale. If you can't use CLR – and I know there are many of you out there who can't, due to corporate policy, the pointy-haired boss, or stubbornness – then you use one of the many workarounds that exist. The answer seems to be, almost invariably, that you should use CLR. Questionable schema design aside, in this case the comma-separated list needs to be "split" into individual values – and this is the question that frequently spurs a lot of "new" debate and commentary about the best solution to achieve just that. Teams WHERE TeamName IN ( N'Patriots', N'Red Sox', N'Bruins' ) īut since there is no array type in SQL Server, this is not how the variable is interpreted at all – it's still a simple, single string that happens to contain some commas. ![]() UserTeams (UserID, TeamID ) SELECT, TeamIDįROM dbo. This is the problem where people want to pass in a string like this: ![]() I know many people are bored of the "split strings" problem, but it still seems to come up almost daily on forum and Q & A sites like Stack Overflow.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |