How to use STRING_AGG to concatenate strings with grouping

Question: Below is table and expected result. What is the query to achieve this result Table : Test ID LOT 7065161 4 7065212 1 7065212 4 7065203 1 7065203 2 7065203 3 Expected Result of query ID LOT 7065161 4 7065212 1_4 7065203 1_2_3 Answer: Previous to SQL Server 2017 - I use to use the COALESCE function , but since SQL Server 2017 - a new function was introduced : STRING_AGG . According to MS documentation "Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string" There... Read more →

How to find computed columns on a SQL Server table

Question: I'm having a problem with an index rebuild job , which is failing on tables with computed columns. I've read ALTER INDEX failed due to QUOTED IDENTIFIER set to OFF which details a solution for situations of computed columns, xml data type and LOB data types. How can I find the computed columns on sql tables? Answer: These are two ways in which you can identify the computed columns. One method is through the sys.columns and the other through sys.computed_columns . sys.computed_columns has the benefit of not requiring you to use the "is_computed = 1" predicate. Method 1 -... Read more →

DRY or WET – SQL Antipattern #005

I was discussing with some software developers the DRY software design principle. It is an acronym for "Don’t repeat yourself". For some developers , it’ s considered one of the basic principles of software programming. But like many principles, DRY can be misunderstood and applied in a way leading to confusion. DRY or WET may be an SQL Antipattern . Read more on how to spot a SQL Antipattern The DRY principle is stated as “Every piece of knowledge must have a single, unambiguous, authoritative representation within a system.”The principle was formulated by Andy Hunt and Dave Thomas in The... Read more →

SQL Indexed View limitations

SQL Server Indexed Views remain a powerful technique for improving sql query performance , if utilised in the right context. The most effective use is to provide a pre-aggregated or pre-calculated view of the base data. In other words, users are able to query an aggregation of data without having to process the complex JOINS and aggregates when executing a request. For example, in a CRM , a daily batch job may pump data into the warehouse. A workflow may run which pre-aggregates the data into an indexed view. The pre-aggregation process may take 15 minutes, but the user does... Read more →

Difference between sys.objects and sysobjects

Question:What is the difference between sysobjects and sys.objects? Answer: SQL Server Books Online is the best source of information about system tables. In summary: 1) From SQL Server 2005 , a number of system tables were implemented as compatibility views – satisfying backward compatibility. Sysobjects is an example of a backward compatibility view. Sysobjects is not a table but a view 2) The compatibility views expose the equivalent of the SQL Server 2000 metadata but don’t expose the SQL Server 2005 features metadata 3) If using new features , such as Partitioning , use sys.objects rather than compatibility views 4)... Read more →