Author: Latheesh NK

Errors and Exceptions, SQL

Error Message : The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query.
Error Message:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

As specified in the error message, its a rare event or exception. The important message from the exception is that “Please simplify the query”. When the query is complex, the relational engine would not be able to create a query plan due to various reasons like the environment resource limitation, reduced capacity etc. and end up with the above exception.

The case I observed is with a dynamic query execution. I do not exactly going to give the query, however tries to provide a more like scenario. I wanted to create a dynamic query with an IN condition where the list of items are separated with a comma like below. 

declare @sql varchar(max)
Declare @values varchar(max)='val1','val2','val3'......'valhugenumber'
Select @sql = 'Select * from ' + @tablename + ' where ' + @columnname + ' in ('+@values+')'
Exec(@sql)


When executing this in one of environments (lower), the error is produced, however, the other interesting thing is its not happening in few other environments(eg. Production). As mentioned above, that is because it depends on the resources involved in various environment matters in producing the query plan.

Resolution:

From the analysis, the issue is associated with long (really long) list of values and when its parsing, the relational engine is not able to create a plan for the query execution. To resolve the issue, the query has been modified not to use the value list variable, instead populated those values into a temp table and then use the temp table as below. 

declare @sql varchar(max)
create table #temp (valuelist varchar(100))
Insert into #temp values('val1'),('val2'),('val3'),......('valhugenumber')
Select @sql = 'Select * from ' + @tablename + ' where ' + @columnname + ' in (Select valuelist from #temp)'
Exec(@sql)

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

UPPER and LOWER functions in SQL Server
UPPER and LOWER are strings functions in SQL Server. Both function accepts character expression to be converted. UPPER converts all characters in the character expression to upper case and LOWER converts to lower case. If the character expression is not able to convert to varchar implicitly, an explicit conversion methods to be used. 
Select UPPER('SQL Server'),lower('SQL Server')


See Also:
Change first character of table names with capital character including intermediate words using sp_rename
Generate Random characters in SQL Server

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

STR() function in SQL Server
Today, we are going to see the function – STR in SQL Server.

Syntax:
STR ( float_expression [ , length [ , decimal ] ] )
Microsoft defines the function as “Returns character data converted from numeric data. The character data is right-justified, with a specified length and decimal precision.” The key words are “right -justified” and “specified length and decimal precision”. Let us go through an example to understand these two terms. 
Select '*'+ str(1) + '*' Value,Len(str(1)) Value_Len
The output is clear that the value “1” is right justified to 10 positions by default.


Few more interesting observations:
    1. By default, if the value is more than 10, then the value of the result is masked to “*” to 10 times.
    2. If the length is specified, and the value is more than specified length, then again it masks to specified length.


Round up funda:
    1. If length is not specified, then depending the decimal part, the number is rounded to next.
    2. Adds more 0 to match the decimal part, if in short the number for decimal.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

sp_settriggerorder in SQL Server
Today, we are going to see a very interesting system procedure in SQL Server – sp_settriggerorder. At times, we will have multiple triggers on the table for same operations. If there is a significance in the order of execution, how do we ensure the ordering? The answer is sp_settriggerorder.

Let us look at the syntax quickly:
sp_settriggerorder [ @triggername = ] ‘[ triggerschema. ] triggername’ , [ @order = ] ‘value’ , [ @stmttype = ] ‘statement_type’ [ , [ @namespace = ] { ‘DATABASE’ | ‘SERVER’ | NULL } ]
triggername -> Name of the trigger to be ordered.
order -> Order of the trigger. It can be (First/Last/None)
stmttype -> INSERT, UPDATE, DELETE, LOGON, or any Transact-SQL statement DDL event
namespace -> ‘DATABASE’ | ‘SERVER’ | NULL

Sample Execution 
DROP  Table  if exists TestTriggerOrder, TestTriggerResult
GO

Create Table TestTriggerOrder (Id bigint, Value varchar(100))
Create Table TestTriggerResult ( OrderValue varchar(100), EventDateTime Datetime default(Getdate()) )
Insert into TestTriggerOrder Values(1,'SQL')

GO
create trigger tr_upd_TesttriggerOrder On TestTriggerOrder
FOR UPDATE
AS
	Insert into TestTriggerResult(OrderValue) Values ('FirstTrigger')

GO
create trigger tr_upd_TesttriggerOrder0 On TestTriggerOrder
FOR UPDATE
AS

	Insert into TestTriggerResult(OrderValue) Values ('SecondTrigger')

GO
Select * From sys.trigger_events


While executing the above code, we can see the objects are created for our testing and the type of order is not specified in the trigger_events (is_first/is_last values). This is the normal scenario for any triggers, by default, there would not be any specific order defined for the execution.

Now, let us quickly try to set an order and see how the results are changing. 
--First step -> We are trying to update a value in the table
update TestTriggerOrder set Value=value+' Server'

Select * from TestTriggerResult

GO
sp_settriggerorder @triggername= 'tr_upd_TesttriggerOrder0', @order='First', @stmttype = 'UPDATE';

Select * From sys.trigger_events

--Second step -> Again, We are trying to update a value in the table post setting the order
update TestTriggerOrder set Value=value+' Server'

Select * from TestTriggerResult


We can clearly see that the order of values inserted in the TestTriggerResult table has changed as per the order given in the above code.Also, this can be confirmed in the sys.trigger_events, the value of is_first has been changed to 1.

Caveats:

    1. If there is a change in definition of the trigger, then the order has to set again.
    2. Replication always needs its triggers to be the first in order.


I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

SQL

SOUNDEX() and DIFFERENCE() in SQL Server
SOUNDEX() and DIFFERENCE() functions are string functions in SQL Server. Honestly, I have not seen these functions used in development except rare cases. But, its good to understand about these two functions as part of our learning purpose so that in case you see any requirement in future, it would help you.

SOUNDEX() in SQL Server

SOUNDEX() function returns a 4 character code based on how the string sounds on speak. There are definite rules to generate the 4 character code.

1. Every soundex code consists of a letter and three numbers. The letter is always the first letter of the string. The numbers are assigned to the remaining letters of the string according to the soundex guide shown below.

Number Represents the Letters

1-> B, F, P, V

2-> C, G, J, K, Q, S, X, Z

3-> D, T

4-> L

5-> M, N

6-> R

Disregard the letters A, E, I, O, U, H, W, and Y. 

SELECT SOUNDEX('CRAFT')
/*
    First Letter is C
    6 for R
    A is ignored letter
    1 for F
    3 for T
*/

2. Zeroes are added at the end if necessary to produce a four-character code.

3. Additional letters are disregarded.

4. If the word has any double letters, they should be treated as one letter.

5. If the surname has different letters side-by-side that have the same number in the soundex coding guide, they should be treated as one letter. 

SELECT SOUNDEX('SQL')
/*
    S - the first character
    Q is coming in the same soundex group as S, hence both are treated as one letter.
    L - has soundex digit 4

So, the value is S400 (00 is added to produce four character code as per rule 2.) 
*/

6. If a vowel (A, E, I, O, U) separates two consonants that have the same soundex code, the consonant to the right of the vowel is coded. Example:

7. If “H” or “W” separate two consonants that have the same soundex code, the consonant to the right of the vowel is not coded.

DIFFERENCE() in SQL Server

DIFFERENCE function is used to compare and return an integer (0-4) that represents the similarity of the two words. 0 indicates weak or no similarity between the SOUNDEX values. 4 indicates strong similarity or identically SOUNDEX values. 

SELECT SOUNDEX('Juice'), SOUNDEX('Jucy'), difference('Juice','Jucy')

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!