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 →

Use cases for NULLIF

Question: I understand the theory behind NULLIF – based on the MS documentation “Returns a null value if the two specified expressions are equal”. But I’ve rarely had the opportunity to apply this function in real word scenarios . Can you expand on some real world scenarios for NULLIF ? Answer: You are right – it’s not a widely used function , I think mainly because the more widely used expression CASE is used Basically NULLIF(value1,value2) Is the equivalent to CASE WHEN value1 = value2 THEN NULL ELSE value1 END Using CASE gives you the advantage of utilising other expressions... Read more →

Msg 16955 – Could not create an acceptable cursor

Question: I’m getting an error when checking some new application installed with a SQL Server backend. How can I fix? The sql code and the associated error are below: declare @p1 int set @p1=0 declare @p3 int set @p3=98305 declare @p4 int set @p4=311300 declare @p5 int set @p5=0 exec sp_cursoropen @p1 output,N'SELECT GetDate()',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 Msg 16955, Level 16, State 2, Line 10 Could not create an acceptable cursor. Msg 16945, Level 16, State 2, Procedure sp_cursoropen, Line 1 [Batch Start Line 9] The cursor was not declared. Answer: This error is occurring... Read more →

How to find SQL Server Instance Default Path and Instance Default Log Path

Finding the SQL Server Instance Default Path and the Instance Default Log Path in the old days (pre SQL Server 2012) was generally limited to either checking SSMS for the Instance Default Path or Log Path , and you could also use xp_regread to extract the information from the Registry. To read more about these options read SQL Server - Find Default Data location and Default Log Location .... For the last few versions of SQL Server similar information can be extracted using the SERVERPROPERTY functionality. This has introduced the benefit of being able to programmatically extract the details and... Read more →

How to write a conditional substring in sql

Question: I have a SELECT statement returning a recordset . Before presenting the recordset , I want to strip out some of the substrings within certain records. This is an example of the recordset Brazil Brazil.com Argentina.net Bolivia The requirement is to strip out any part of the string from the dot and after. For example, “brazil.com” will become “brazil”. The problem I have currently is by running the SUBSTRING function across all the rows, if there is no dot in the row , the rown will return as blank. The only rows returning are the ones with a dot!... Read more →