Introduction
Raga: Thodi
Type:
Rendering: Arohana & Avarohana
Vocal Reference
Instrumental References
Film Song References
Malayalam
Tamil
Author: Latheesh NK
Introduction
Raga: Vasantha
Type: Janya raga of 17th melakartha Surykantham
Rendering of Arohana & Avarohana: https://www.youtube.com/watch?v=KYl2VnSKGFs
Vocal References
Instrumental References
Shri Kamakshi Katakshi – Violin
Film Song References
Malayalam
Tamil
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:
Hope you enjoyed this blog post, please share it with your friends!
Error Message:
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.
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.
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
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)
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.
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!
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!
