Author: Latheesh NK

Raga: Thodi

Introduction

Raga: Thodi

Type:

Rendering: Arohana & Avarohana

Vocal Reference

Madurai Somasundaram

Dr. M Balamuralikrishna

Instrumental References

TH Subramaniam

Film Song References

Malayalam

Sargam

Sopanam

Tamil

Gangaikari mannanadi

Raga: Vasantha

Introduction

Raga: Vasantha

Type: Janya raga of 17th melakartha Surykantham

Rendering of Arohana & Avarohana: https://www.youtube.com/watch?v=KYl2VnSKGFs

Vocal References

Dr. Vidwan UmaShankar

Neyveli R Santhanagopalan

Instrumental References

Shri Kamakshi Katakshi – Violin

Dr. L Subramaniam

Film Song References

Malayalam

Olakkuda Choodunnura

Sundari Sundari

Kanimalaray

Goruvayoor unnikannanu

Kathoram Kinnoram

Tamil

Vasantha ruthu

Andhimazhai Pozhigirathu

Minasara Poove

Idhayam

COUNT and COUNT_BIG functions in SQL Server

Todays post, we will quickly see the two important functions count and count_big in SQL Server. Both functions return the number of records in a table. There are lots of interesting facts with count function and we will try to cover most of things, if you have any more points, feel free to share your thoughts as a comment.

Return types of count and count_big

As mentioned earlier, these two functions returns the number of records, however these two differ in return datatype, COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

Let us quickly see with a system procedure (sp_describe_first_result_set) to understand the return datatype as below:

Count and column name and non column name as parameter

The parameter value makes the difference on column name and non column name expression of the table involved. The below screen shot explains you the evaluation on the column name while we provide a valid column name as parameter otherwise, irrespective of the expression value, it just do not evaluate.

As a simple example, Select 1/0 always produces an error in SQL Server, but 1/0 as a parameter to count or count_big function does not evaluate it.

Count and NULL values in table

This is another interesting point to be aware in SQL Server NULL value column in a table plays a big role with count functions. If the parameter to a count function is a column name and the column is a nullable one, it is extremely important to be noted the NULL values will not be considered to count function.

See Also:

https://blog.sqlauthority.com/2010/09/21/sql-server-count-not-allowed-but-count_big-allowed-limitation-of-the-view-5/

Hope you enjoyed this blog post, please share it with your friends!

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!

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!