Using PRINT within a function (SQL Server)

Have you ever tried using PRINT function inside another user defined function in SQL Server?
Well i recently tried doing this and to my surprise i found that we can not use PRINT function inside another user defined function.
I tried a lot to find the reason for this, but no luck. I am not sure why SQL Server team always comes with such shortcomings.

We often use PRINT function as a mean for debugging our Database scripts or procedures. so now question is how can we debug user defined functions without PRINT function.
Well guys if you are expecting any other alternate solution from me then i wont disappoint you. The only option to make sure that your user defined function is working properly is to test the script before making it a function. Meaning you test your script first with all possible scenarios and inputs, then you can make it as function.

Thats it guys, this is the only solution to this for now.
If any of you have any other better solution then do let everyone know here. Also don't forget to write in your valuable comments.

Numeric DataType & Storage, Choose wisely..!!!

SQL Server provides two datatypes decimal and numeric that have fixed precision and scale. numeric datatype is primitive datatype which is equivalent to decimal and it is kept merely for backward compatibility. Most of the time developer use these datatypes without keeping in mind the storage aspect, which i think is very important and affects performance of the reports.

Lets briefly discuss about this datatype and its storage aspect.

decimal[ (p[ , s] )] and numeric[ (p[ , s] )]
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.

p (precision)
The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)
The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

Table below specifies storage structure that SQL Server uses based on the precision selected by user.

Precision	Storage bytes
1 - 9	5
10-19	9
20-28	13
29-38	17

By default SQL Server uses 18,3 as precision and scale for these datatypes. Most of the time developers just these default precision and dont really think of what actual is the requirement for them. As a good practice always choose precision manually as per your need.

Hope you find this post useful. Feel free to provide your comments.

How to Hide Rendering Extensions in SQL Reporting Services ?

When we generate reports using SQL Reporting Services, the report viewer by default gives some report export options such as WORD, EXCEL, etc. A good mechanism for preventing users from accidently exporting reports to a format that you don’t want (for example, you might know that the report doesn’t render quite right in a particular format) is to mark the extension as “invisible” in the Report Server config file.

The rsreportserver.config file, located in C:\Program Files\Microsoft SQL Server\MSRS10.\Reporting Services\ReportServer\Bin folder, if the default installation location was selected during installation.
The parent element is . Under the Render element is an Extension element for each rendering extension. The Extension element contains two attributes, Name and Type. The important one here is the Visible attribute:

Visible

A value of false indicates that the rendering extension should not be visible in user interfaces. If the attribute is not included, the default value is true.


By setting this attribute to false for a given extension, it will hide the extension from being visible in the Report Viewer control as well as in the delivery configuration pages.

This MSDN documentation explains how to configure a rendering extension: