We see a lots of articles on the net and lots of documents are prepared by different project teams in the industry to present / capture the conventions, coding standards etc. However in reality there is nothing like a standard / convention as such in most cases. Conventions / Standards are defined to ensure consistency in the coding / documentation across various modules / projects.

Having said that, here are few of the conventions / standards that I use while writing T-SQL queries / functions / SPs etc.

• Always type the SQL Keywords in CAPITAL Letters.
• Use proper case while typing names of any database objects/entities. Usually database objects should be named using InitCap / Camel Case.
• Use proper indentation.
• Use Comments wherever necessary.
• Name the table aliases by concatenating the first letter of all the words in the name of the table. Example: If the table name is FactInternetSales then one of the possible alias is FIS. Define the aliases considering the conflicts if any with the other table aliases.
• While executing stored procedures using "EXEC" command use the variable names while passing input values to the Stored Procedure (SP).
  Bad Example:
  
  USE AdventureWorksDW 
  GO
  EXEC sp_spaceused 'FactInternetSales' 
  GO
  
  Good Example:
  
  USE AdventureWorksDW 
  GO
  EXEC sp_spaceused @objname = 'FactInternetSales' 
  GO

Above snapshot shows few of the most commonly used T-SQL Statements.

These conventions are purely based on my comfort level and there is no hard & fast rule that these are followed / should be following widely in the industry. One can define their own standards / conventions based on their comfort level to ensure consistency.

This blog post is updated as and when I find something interesting which I feel like adopting as part of my coding.

Often in Reporting Applications, faster rendering of reports is very essential. Especially in case of Operational Reporting Systems with large number of concurrent users, Reporting Rendering SLA is defined during the requirement phase of the project and is a very critical component of the Requirements / Reporting Application.

In these kind of solutions its good to display the total time a report takes for rendering from the moment the View Report button is clicked in the report manager. This feature can be used as a means of communicating the exact report rendering time to the users and also to identify the reports / scenarios which are exceeding / missing the rendering SLA.

Now let us see how to display the execution time in an SSRS report.

Since SSRS uses functions based on .Net framework we can use the following expression to get the report rendering time in seconds:

System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds

Similary to display the time in terms of minutes and hours we can just replace Seconds in the above expression with Minutes and Hours respectively.

Though execution time in terms of Hours is rarely / never used.

To make the display more user friendly or more presentable we can use the following expression:

="Execution Time: " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours) + " hour(s)" + " , " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes) + " minute(s)" + ", " +
CStr(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds) + " second(s)"

Here is a sample report displaying the execution time in terms of Hours, Minutes and Seconds as shown below.

As shown in the above screenshot the report takes 1 Min 3 Seconds or 63 Seconds to render.

Now let us verify how accurate this number is by querying the SSRS report tables which hold the history / log of a report execution.

I ran the following query in the ReportServer database: 

SELECT TOP 1 ReportID, TimeStart, TimeEnd, TimeDataRetrieval, TimeProcessing 

, TimeRendering, TimeDataRetrieval + TimeProcessing + TimeRendering AS TotalRenderingTime
FROM dbo.ExecutionLog
WHERE ReportID = '90ABF38F-E280-4B75-B807-343AFB5FE696'
ORDER BY TimeStart DESC

And here are the results of the above query:

From the above screenshot we can see that the total time is 63172 milliseconds or ~63 Seconds or 1 Min & 3 Seconds, which is same as the Execution Time displayed in the previous screenshot.

Please let me know your comments / opinions about this article by leaving a comment below.

Note: This demonstration is tested in SSRS 2008. The implementation should be almost the same for SSRS 2005 as well.

Often people struggle to get a RANDOM record from a table in SQL Server. There is a function in SQL Server RAND() which generates Random Numbers. However this function does not work as expected while selecting a RANDOM row from a table in SQL Server.

To address this issue, there is a workaround to select a RANDOM row from a table in SQL Server. Let us see how we can achieve this using the following demonstration.

Create a Temp Table using the following query:

CREATE TABLE #TempTable (
  IntValue INT NOT NULL,
  StrValue NVARCHAR(20) NOT NULL)
GO

Now insert some sample data into the Temp Table using the following query:

INSERT INTO #TempTable (IntValue,StrValue)
SELECT IntValue, StrValue
FROM (SELECT 1 AS IntValue, 'String Value 1' AS StrValue
      UNION ALL
      SELECT 2 AS IntValue, 'String Value 2' AS StrValue
      UNION ALL
      SELECT 3 AS IntValue, 'String Value 3' AS StrValue
      UNION ALL
      SELECT 4 AS IntValue, 'String Value 4' AS StrValue
      UNION ALL
      SELECT 5 AS IntValue, 'String Value 5' AS StrValue
      UNION ALL
      SELECT 6 AS IntValue, 'String Value 6' AS StrValue
      UNION ALL
      SELECT 7 AS IntValue, 'String Value 7' AS StrValue
      UNION ALL
      SELECT 8 AS IntValue, 'String Value 8' AS StrValue
      UNION ALL
      SELECT 9 AS IntValue, 'String Value 9' AS StrValue
      UNION ALL
      SELECT 10 AS IntValue, 'String Value 10' AS StrValue) StaticData
GO

Now run the following queries to see how 2 RANDOM rows are selected from Temp Table every time you run these queries.

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

SELECT TOP 2 *
FROM #TempTable
ORDER BY NEWID()
GO

Here is the output of the above queries. Run these queries a couple times to see the difference in the number of selected rows every time the above queries are run.

Find this post useful ? Please do let me know by leaving a comment below :-)