Last week I was writing a few validation scripts for some of the recent schema changes / performance tuning which we had done in our project. As part of this we had defined few indexes and most of them were on multiple columns. As a result of this, during validation process, I wanted to list all the columns of all the indexes in a particular database so that by looking at the output I can make sure that all the indexes are created as expected with the required columns in it. After doing some research  on some of the system tables (like sys.objects, sys.indexes etc.), I came up with a simple SQL script which will list all the columns contained in all the indexes in the current database.

SELECT
DB_NAME() AS DatabaseName
, SSch.name AS SchemaName
, SObj.name AS ObjectName
, DB_NAME() + '.' + SSch.name + '.' + SObj.name AS FullyQualifiedObjectName
, SObj.type AS ObjectType
, CASE
WHEN SObj.type = N'U' THEN 'Table'
WHEN SObj.type = N'V' THEN 'View'
END AS ObjectTypeDesc
, SIdx.name AS IndexName
, SIdx.type AS IndexType
, SIdx.type_desc AS IndexTypeDesc
, SIdx.is_primary_key AS IsIndexAPrimaryKey
, SIdxCol.index_column_id AS IndexColumnSequence
, SCol.name AS IndexColumnName
FROM
sys.objects SObj
INNER JOIN sys.schemas SSch
ON SObj.schema_id = SSch.schema_id
INNER JOIN sys.indexes SIdx
ON SObj.object_id = SIdx.object_id
INNER JOIN sys.index_columns SIdxCol
ON SIdx.object_id = SIdxCol.object_id
AND SIdx.index_id = SIdxCol.index_id
INNER JOIN sys.columns SCol
ON SIdxCol.object_id = SCol.object_id
AND SIdxCol.column_id = SCol.column_id
WHERE SObj.type IN (N'U', N'V')
ORDER BY DatabaseName, SchemaName, ObjectName, IndexName, IndexColumnSequence

This will produce the following output:

In one of the recent projects, there was a requirement to set the First Day of the Week as Start Date and Current Date as End Date for a Week Level Report. Similarly, First Day of Month & Current Date and First Day of Year & Current Date for Month Level and Year Level Reports respectively.

To address this requirement, one of the developers wrote a query to get the first day of the week considering the week starts on a Monday and the query seemed to work fine on his computer but when we moved that code to my computer then the results were different & were completely incorrect. When I started looking into the issue I found that he was using DATEPART function of SQL Server something like DATEPART(DW, @InputDate). After doing some analysis & referring to the properties / behavior of this function on MSDN library, I found that the return value of this function depends on the setting of DATEFIRST.

One can find out the current state of DATEFIRST property by running the following simple SELECT statement.

SELECT @@DATEFIRST 

Refer to the DATEPART() function on MSDN library for more details.

Following is the query which gives accurate results under all circumstances:

DECLARE @dttRequestedDateTime DATETIME = '2010-08-20' -- Replace '2010-08-20' with Input Date or Requested Date 
DECLARE @dtInputDate DATE = CAST(@dttRequestedDateTime AS DATE)
 
SELECT  
      @dtInputDate AS [InputDate],
      CASE
         WHEN DATENAME(DW, @dtInputDate) = 'Monday' THEN @dtInputDate
         WHEN DATENAME(DW, @dtInputDate) = 'Tuesday' THEN DATEADD(DD, -1, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Wednesday' THEN DATEADD(DD, -2, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Thursday' THEN DATEADD(DD, -3, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Friday' THEN DATEADD(DD, -4, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Saturday' THEN DATEADD(DD, -5, @dtInputDate)
         WHEN DATENAME(DW, @dtInputDate) = 'Sunday' THEN DATEADD(DD, -6, @dtInputDate)
      END AS [WeekStartDate] 
      , DATEADD(DD, 1 - DATEPART(DAY, @dtInputDate), @dtInputDate) AS [MonthStartDate] 
      , DATEADD(DD, 1 - DATEPART(DAYOFYEAR, @dtInputDate), @dtInputDate) AS [YearStartDate]

Here is the output of this query:

InputDate  WeekStartDate MonthStartDate YearStartDate
---------- ------------- -------------- -------------
2010-08-20 2010-08-16    2010-08-01     2010-01-01

(1 row(s) affected)

Week Start Date, Month Start Date & Year Start Date

Let me know if you have a better way of getting the same results by leaving a comment below.

Related Articles and Links

• T-SQL - Coding Standards & Best Practices
• T-SQL Coding Made Easy with Inbuilt SQL Server Templates
• Query to Select a Random Record from a Table in SQL Server

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 :-)

Often we take the COUNT of records in a table or many of the times all the tables in a database. We might need to get the COUNT of records in all the tables especially for validation purposes.

For instance when you are loading the data into your Staging Database in an incremental fashion, you need to do few checks to make sure that the incremental logic is working fine. As part of this, one of the most basic checks is to first get the COUNT of records from all the tables in Source & Staging Databases and compare the COUNTs.

Here is a very simple way to get the COUNT of records from all the tables in a database. Run the following query in the database in which you need to get the COUNTs of all the tables.

DECLARE @QueryString NVARCHAR(MAX)

SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','') + 'SELECT ' + '''' + TABLE_SCHEMA + '.' + TABLE_NAME + '''' + ' AS TableName, COUNT(1) AS RecordCount FROM ' + TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME 
 
EXEC Sp_executesql @QueryString

When you run the above query in AdventureWorksDW database then the results will be as shown below (Tables/Counts might slightly vary depending on the version of AdventureWorksDW).

 
Feel free to leave your comments if you like this post.

Often while working with T-SQL we create copies of tables as a backup of the original/base table so that we can make changes to the data in the original/base table for testing purposes, and revert back to the backup table if needed. SQL Server provides few Key Words or options to perform this operation.

However there are few key differences to note between the original table and the backup table while creating the copies of the tables With/Without data, and these differences could be really important to consider especially when we decide to drop the original table since you have taken the backup. Before you drop the original table, be careful and think twice before you do this! Read through this article to know more about the differences between the Original Table and the Copy of the table.

Now in this article lets take a look at how to create a copy of a table in No Time in either of the following ways:

• Copy Table Structure With Data
• Copy Only Table Structure Without Data

Create a sample table as follows and call it as ExistingEmployeeTable.

 
/* Create Table */
CREATE TABLE ExistingEmployeeTable (
  EmployeeId INT IDENTITY (1,1) NOT NULL,
  EmployeeName NVARCHAR(100) NOT NULL,
  ManagerId INT)
GO

Create a Primary Key on EmployeeId.

/* Create Primary Key */
ALTER TABLE ExistingEmployeeTable
 ADD CONSTRAINT PK_ExistingEmployeeTable_EmployeeId PRIMARY KEY (EmployeeId)
GO

Now create another table called ExistingManagerTable with ManagerId as Primary Key.
 
/* Create Table */
CREATE TABLE ExistingManagerTable (
  ManagerId INT IDENTITY (1,1) NOT NULL,
  ManagerName NVARCHAR(100) NOT NULL)
GO
 
/* Create Primary Key */
ALTER TABLE ExistingManagerTable
 ADD CONSTRAINT PK_ExistingManagerTable_ManagerId PRIMARY KEY (ManagerId)
GO

Now create a Foreign Key table in ExistingEmployeeTable on ManagerId and also create a Non Clustered Index on ManagerId.

/* Create Foreign Key */
ALTER TABLE ExistingEmployeeTable
 ADD CONSTRAINT FK_ExistingEmployeeTable_ManagerId FOREIGN KEY (ManagerId) REFERENCES ExistingManagerTable(ManagerId)
GO

/* Create Non Unique Non Clustered Index */
CREATE NONCLUSTERED INDEX IDX_NU_NCL_ExistingEmployeeTable_ManagerId ON ExistingEmployeeTable(ManagerId)
GO

Now lets take a look at the structure of the two newly created tables as below.

Figure 1.0

Now lets us insert some sample data.
 
INSERT INTO ExistingManagerTable(ManagerName)
SELECT 'John' AS ManagerName
UNION
SELECT 'James' AS ManagerName
UNION
SELECT 'Michael' AS ManagerName
GO

INSERT INTO ExistingEmployeeTable(EmployeeName, ManagerId)
SELECT 'Robert' AS EmployeeName, 1 AS ManagerId
UNION
SELECT 'Daniel' AS EmployeeName, 2 AS ManagerId
UNION
SELECT 'David' AS EmployeeName, 3 AS ManagerId
UNION
SELECT 'Steven' AS EmployeeName, 2 AS ManagerId
UNION
SELECT 'Albert' AS EmployeeName, 3 AS ManagerId
GO

Now lets create a copy of the ExistingEmployeeTable along With Data as follows. You can either create a Physical Table or a Temporary Table based on your needs. 

/* Create Copy of Table With Data */
SELECT *
INTO dbo.NewEmployeeTable
FROM dbo.ExistingEmployeeTable 

You can also create a copy of the ExistingEmployeeTable Without Data as follows. Again here you can create either a Physical Table or a Temporary Table.

/* Create Copy of Table Without Data */
SELECT *
INTO dbo.#NewEmployeeTable
FROM dbo.ExistingEmployeeTable
WHERE 1 = 2

In this query SQL Server creates a copy of the table structure, but since the condition in the WHERE clause is FALSE, the data will not be copied to the newly created table.

Now lets take a look at the structure of the backup/newly created NewEmployeeTable table.

Figure 2.0

Now compare the table structures of ExistingEmployeeTable (Old Table) & NewEmployeeTable (New Table), and here are few of the key differences to note:

1. Primary & Foreign Keys are not created in the Newly Created Table (NewEmployeeTable)
2. Indexes are not created in the Newly Created Table (NewEmployeeTable)
3. Default/Check Constraints are not created in the Newly Created Table (NewEmployeeTable)

These are few most important differences which one should note before deciding to drop the original table after taking a backup of the table using SELECT * INTO statement.

Today I was working on an SSRS report for one of our clients and came across a specific formatting requirement around formatting Minutes as HH:MM. Browsed a little bit but could not get it working. Then sat for a while & tried few tricks by playing around with the expressions and finally got it working :-)

Here is how I solved the problem.

The data available in the database is numeric and represents Total Minutes. When I initially wrote some expressions to derive Hours and Minutes from the Total Minutes I was able to get the data something like this:

10 Minutes -> 0:10

65 Minutes -> 1:5 etc

However the need was to display the data something like below:

10 Minutes -> 00:10

65 Minutes -> 01:05 etc

Now to get this formatting, first split the Minutes into Hours & Remaining Minutes as follows:

Hours part of TotalMinutes = Floor(Fields!Minutes.Value/60)

Remaining Minutes part of TotalMinutes = Fields!Minutes.Value Mod 60

Now to display the data in the intended format put the following expression in the textbox of the detailed row of the Table/Matrix of SSRS:

=Format(Floor(Fields!Minutes.Value/60),"00") + ":" + Format((Fields!Minutes.Value Mod 60),"00")

Below is a sample report with formatted minutes.

 

Hope you will find this useful. If yes feel free to leave a comment below.

Note:  This article/demonstration is based on SSRS 2008. The options should be almost the same in SSRS 2005 as well.

SQL Server Reporting Services (SSRS) is a great BI tool offering lot of powerful features. SSRS 2008 has lot more features and is much more powerful than SSRS 2005. Irrespective of whether its SSRS 2005 or SSRS 2008, there are many features common between the two versions. Functions are one such powerful options/capabilities in SSRS.

In this article I will present detailed steps for the use of Functions in SSRS.

Often in reporting/BI projects using SSRS we use lot of calculations/expressions in many fields in the reports. There are scenarios where in same formula/calculations is used across many fields in the report. Functions come in handy especially in this kind of scenarios.

Let us take a look at this sample report with simple calculations for finding Sum, Difference, Product & Percentage.

Create an SSRS report with the following query in the dataset:

SELECT 1 AS ColumnA, 2 AS ColumnB
UNION ALL
SELECT 3 AS ColumnA, 4 AS ColumnB
UNION ALL
SELECT 5 AS ColumnA, 6 AS ColumnB
UNION ALL
SELECT 7 AS ColumnA, 8 AS ColumnB
UNION ALL
SELECT 9 AS ColumnA, 10 AS ColumnB
UNION ALL
SELECT 11 AS ColumnA, 12 AS ColumnB
UNION ALL
SELECT 13 AS ColumnA, 14 AS ColumnB
UNION ALL
SELECT 15 AS ColumnA, 16 AS ColumnB
UNION ALL
SELECT 17 AS ColumnA, 18 AS ColumnB
UNION ALL
SELECT 19 AS ColumnA, 20 AS ColumnB

Now lets drop a Table object on to the report designer with the following six fields as shown in the below screenshot.

• Column A
• Column B
• Sum of A & B
• Difference of A & B
• A Multiplied by B
• A as a % of B
  

Now go to "Report > Report Properties". Report Properties dialog box will open and in this window click on "Code" in the left pane. Enter the following code with four different functions for calculating Addition, Difference, Product & Percentage in the Code window as shown in the below screenshot.

' Function for Addition
Function GetSum(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarSum AS Integer 
VarSum = A + B
Return VarSum
End Function

' Function for Difference
Function GetDiff(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarDiff AS Integer 
IF (A>=B) THEN
VarDiff = A - B
ELSE
VarDiff = B - A
END IF
Return VarDiff
End Function

' Function for Multiplication/Product
Function GetProduct(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarProduct AS Integer 
VarProduct = A * B
Return VarProduct
End Function

' Function for Percentage
Function GetPercent(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarPercent AS Integer 
VarPercent = (A/B)*100
Return VarPercent
End Function

Enter the following expressions in the detailed row of the report for the six fields as shown in the below screenshot.

Expr1: "=Fields!ColumnA.Value"
Expr2: "=Fields!ColumnB.Value"
Expr3: "=Code.GetSum(Fields!ColumnA.Value,Fields!ColumnB.Value)"
Expr4: "=Code.GetDiff(Fields!ColumnA.Value,Fields!ColumnB.Value)"
Expr5: "=Code.GetProduct(Fields!ColumnA.Value,Fields!ColumnB.Value)"
Expr6: "=CStr(Code.GetPercent(Fields!ColumnA.Value,Fields!ColumnB.Value)) + " %"" 

Now go to the Preview tab and check out the results as shown in the below screenshot.

And the results are amazing. Having functions for scenarios where the same calculation is used in many places in a report is really helpful and makes the code more modular & neat.

Note: This has been tested & presented as per SSRS 2008.

If you find this post helpful then feel free to leave a comment below :-)