Sergiy (6/30/2016)
ScottPletcher (6/30/2016)
Jeff Moden (6/24/2016)
Shifting gears a bit and depending on how that's used, it could become a performance problem due to the implicit conversions of the string literals. Since @database2 has been declared as NVARCHAR, the literals should also be made to be NVARCHAR. Like this...
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES (N'A'),(N'B'),(N'C')
;
SELECT @database2 = COALESCE(@database2 + N',', N' ') + DBname
FROM @dbs
;
SELECT @database2
;
I strongly recommend against explicitly making literals nvarchar. The resulting implicit conversion is truly negligible overhead anyway. The real issue is if the column type changes to varchar, because then you explicitly forcing an nvarchar string will force an implicit column conversion, which will be many, many orders of magnitude worse than the implicit conversions of literal strings.
You should always remember that your would is limited by your knowledge about the world.
And apparently your knowledge is far from being universal.
And the rules which work in your world may be not applicable for others.
This is an international forum.
People who read it use to have literal constants not only in English.
Here how your rule "works" for them:
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES ('?'),('?'),('?')
;
SELECT @database2 = ISNULL(@database2 + ',', ' ') + DBname
FROM @dbs
;
SELECT @database2
Output:
?,?,?
Wowwww. It does that even with the original Coalesce code. Amazing. Today I learned two really valuable lessons... 1) my "universe" is a whole lot smaller than I thought and 2) there are "black holes" that I'd never seen before.
Thanks, Sergiy. Really good stuff.
--Jeff Moden
Change is inevitable... Change for the better is not.