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 →

Use SQL Materialized view to eliminate DISTINCT and speed up response time

Question: Users are complaining this query is slow and is creating a bottleneck. The query includes a subquery which relies on the DISTINCT keyword. The Execution Plan indicates the highest cost is related to the DISTINCT . How can I speed up the query response time? --SQL Server parse and compile time: -- CPU time = 31 ms, elapsed time = 116 ms. --SQL Server Execution Times: --CPU time = 29500 ms, elapsed time = 36388 ms. --SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SELECT NumbersCodeId, sStatsDescription, sStatsDescriptionCode, NumbersRoutineId, NumbersTypeId FROM... Read more →