Michael J. Swart

Wouldn’t it be nice to do something like this:

--split
SELECT t.id, split.value
FROM someTable t
CROSS APPLY dbo.Split(t.csv, ',') as split

or this:

--join
SELECT ManagerID, JOIN(EmployeeID + ' ')
FROM HumanResources.Employee
GROUP BY ManagerID

Well the first example can be implemented as a table-valued function and it’s possible if you’re careful. Here’s my implementation of Split:

CREATE FUNCTION [dbo].[Split]
(
	@SearchString NVARCHAR (max),
	@Separator NVARCHAR (5)
)
 
RETURNS @strtable TABLE (strval NVARCHAR (max))
AS
 
BEGIN
	DECLARE @SeparatorLength INT
	SET @SeparatorLength = DATALENGTH(@Separator) / 2
 
	IF (@SeparatorLength = 0)
		RETURN
 
	WHILE 1=1
	BEGIN
		IF (CHARINDEX(@Separator,@SearchString) = 0)
		BEGIN
			INSERT INTO @strtable
			VALUES (@SearchString);
			BREAK;
		END
 
		INSERT INTO @strtable
		VALUES ( LEFT(@SearchString, CHARINDEX(@Separator,@SearchString) - 1))
 
		SET @SearchString = RIGHT(@SearchString, DATALENGTH(@SearchString)/2 - CHARINDEX(@Separator,@SearchString) - @SeparatorLength + 1)
	END
	RETURN
END

For the second example, the join, I don’t think there there’s a simple way, But there are a couple options. There’s an example of creating the CONCATENATE aggregate with CLR code here. But it’s pretty clunky to enable clr and integrate an assembly. But here’s another alternative if you’re willing to resort to a kludge is to use select a column called data() and use the for xml path syntax:

SELECT
	M.ManagerID,
	(
		SELECT CAST(E.EmployeeID AS VARCHAR(10))
			+ ',' AS [data()]
		FROM HumanResources.Employee E
		WHERE E.ManagerID = M.ManagerID
		FOR XML PATH('')
	) AS EmployeeID
FROM
	(
		SELECT DISTINCT ManagerID
		FROM HumanResources.Employee
	) AS M

Your mileage may vary. I give no guarantees about the performance. It breaks a rule of thumb of mine where I don’t like to include subqueries in the SELECT clause of SELECT Statements.

Update! Jul 4, 2008: Shown up by a pro
In SQL Server Magazine’s “Performance Tuning and Optimization” forum,Andrew J Kelly has a much nicer split function than I do. For one thing it’s set based:
(Caveat: because of the recursion limit of 100, this function doesn’t work on lists with more than 100 elements)

CREATE FUNCTION [dbo].[fn_split_inline_cte]
    (@list NVARCHAR (MAX),
    @delimiter NCHAR(1) = ',')
RETURNS TABLE
AS
 
RETURN
WITH cte_list([BeginChar], [EndChar]) AS (
    SELECT [BeginChar] = CONVERT(BIGINT, 1), [EndChar] = CHARINDEX(@delimiter, @list + @delimiter)
    UNION ALL
    SELECT [BeginChar] = [EndChar] + 1, [EndChar] = CHARINDEX(@delimiter, @list + @delimiter, [EndChar] + 1)
    FROM cte_list
    WHERE [EndChar] > 0
)
SELECT LTRIM(RTRIM(SUBSTRING(@list, [BeginChar],
    CASE WHEN [EndChar] > 0 THEN [EndChar] - [BeginChar] ELSE 0 END))) AS [ParsedValue]
FROM cte_list
WHERE [EndChar] > 0 ;

It’s always a good idea to keep your constraints trusted. You can check to see which constraints in your database are not trusted with these commands:

select fk.name from sys.foreign_keys fk where fk.is_not_trusted = 1
select cc.name from sys.check_constraints cc where cc.is_not_trusted = 1

Constraints can become untrusted after a bulk load operation is performed without the CHECK CONSTRAINTS option. Foreign keys can be untrusted if they were defined with the NOT FOR REPLICATION option.

The obvious reason for keeping constraints trusted is that it means SQL Server is enforcing the data integrity defined by these constraints.

But there’s also a more subtle performance gain that I want to demonstrate below. If SQL Server can trust constraints, then sometimes it can decide it doesn’t need to do a join. Such queries are rare, but I’ve seen them pop up.

The following example works against the MS’s Adventureworks sample database. The example below uses two tables, Sales.Customers and Sales.SalesOrderHeader. There is a foreign key making sure that every SalesOrderHeader record references one Customer record.

use AdventureWorks
GO

-- the inner join in the following query has no effect here and sql server 
-- eliminates the join from the query plan. It's because the foreign 
-- key relationship that SQL Server can trust and simplify the query plan.
-- It only has to count orders.
select COUNT(1)
from Sales.Customer c
join Sales.SalesOrderHeader soh
on soh.CustomerID = c.CustomerID
GO

-- Drop the foreign key and sql server can no longer simplify.
-- It can't guarantee that every order has a customer.
-- Again it has to join with customers.
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID];
select COUNT(1)
from Sales.Customer c
join Sales.SalesOrderHeader soh
on soh.CustomerID = c.CustomerID;
GO

-- Create the key and try again.
ALTER TABLE [Sales].[SalesOrderHeader]  
ADD CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] 
    FOREIGN KEY([CustomerID])
    REFERENCES [Sales].[Customer] ([CustomerID])
    NOT FOR REPLICATION;
select COUNT(1)
from Sales.Customer c
join Sales.SalesOrderHeader soh
on soh.CustomerID = c.CustomerID;
-- hmmm the foreign key is back in force, but we're still joining with customers.
-- It's because the foreign key is not trusted (related to the not for replication clause)
select fk.is_not_trusted from sys.foreign_keys fk where fk.name = 'FK_SalesOrderHeader_Customer_CustomerID'
GO

-- let's try again.
ALTER TABLE [Sales].[SalesOrderHeader] DROP CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID];
ALTER TABLE [Sales].[SalesOrderHeader]  
ADD CONSTRAINT [FK_SalesOrderHeader_Customer_CustomerID] 
    FOREIGN KEY([CustomerID])
    REFERENCES [Sales].[Customer] ([CustomerID]);
select COUNT(1)
from Sales.Customer c
join Sales.SalesOrderHeader soh
on soh.CustomerID = c.CustomerID;
GO
-- much better