SQL Things

We recently found a view in our warehouse which had only been created for demonstration purposes but the underlying tables had changed over time.  The view was broken and, of course, someone found it.  This necessitated a remediation which would identify broken views and create a record of the issue.  Ideally, there would never be broken views if this process was in place so we could also create a record showing how few issues we have had.   At first, I thought of creating a SQL Agent job which would query the view and fail when the view was not working. But this seemed like a lot of work as views got added over time.  So, something more flexible was called for. I wanted to create a procedure that would test all of the views in a database and record the relevant data if the view broke.  I created a procedure with a try-catch block and selected the top(1) record from the views and when there was a failure the catch block would insert the record...

STRING_AGG is a function released in SQL Server 2017.   The Oracle equivalent LIST_AGG has been available since 11gR2.  In the meantime, SQL Server users have resorted to black magic in order to get string values from separate rows. STRING_AGG is very welcome but there are some gotchas to be aware of.  Below is a simple example that works just like you think it would. SELECT STRING_AGG(FIrst_Name, ', ') WITHIN GROUP (ORDER BY First_Name) LIST FROM             (SELECT DISTINCT   First_name FROM [dbo].[Employee]     WHERE First_Name like 'A%' OR First_Name like 'B%') A ORDER BY LIST; You can run the query on any database you have string values and you will get something like this: A David, Aaren, Aaron, Abdul, Bob, Brian... If you want to split by the first letter of the name, do not make the mistake of using an over clause.  You will get an error stating STRING_AGG is not a window function....