Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts

Tuesday, June 5, 2012

Using APPLY Operator

The Apply operator available in SQL Server 2005 onwards is used to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

The Apply operator is similar to that of Joins but has no conditions in the query.

There are two forms of Apply operator:
  • Cross Apply
    The CROSS APPLY operator returns rows from the primary (outer) table only if the table-value function produces a result set. It's similar to that of Inner Join or Cross Join.
  • Outer Apply
    The OUTER APPLY, on the other hand, returns all rows from the outer table, even if the function produces no results. Thus, it generates NULL values for those records whose result set is not returned. It's similar to that of Outer Join.

Consider an example below to demonstrate Apply operator:

GO

CREATE TABLE [dbo].[Departments](
 [DepartmentId] [int] NOT NULL PRIMARY KEY,
 [DeparttmentName] [varchar](50) NULL,
);

GO

INSERT INTO [dbo].[Departments] VALUES(1, 'HR')
INSERT INTO [dbo].[Departments] VALUES(2, 'Marketing')
INSERT INTO [dbo].[Departments] VALUES(3, 'Finance')
INSERT INTO [dbo].[Departments] VALUES(4, 'R&D')
INSERT INTO [dbo].[Departments] VALUES(5, 'Training')
INSERT INTO [dbo].[Departments] VALUES(6, 'Gardening')


GO

CREATE TABLE [dbo].[Employees](
 [EmployeeId] [int] NOT NULL PRIMARY KEY,
 [EmployeeName] [varchar](50) NULL,
 [DepartmentId] [int] NULL REFERENCES [dbo].[Departments],
);

GO

INSERT INTO [dbo].[Employees] VALUES(1, 'Jeet', 1)
INSERT INTO [dbo].[Employees] VALUES(2, 'Sagar', 2)
INSERT INTO [dbo].[Employees] VALUES(3, 'Chirag', 2)
INSERT INTO [dbo].[Employees] VALUES(4, 'Jayesh', 3)
INSERT INTO [dbo].[Employees] VALUES(5, 'Ketan', 4)
INSERT INTO [dbo].[Employees] VALUES(6, 'Rikin', 4)
INSERT INTO [dbo].[Employees] VALUES(7, 'Pratik', 6)

GO

In above example we can see list of Departments and Employees related to departments (if any) in appropriate tables respectively.

As mentioned above, Apply operator works with table-values function (also known as TVF), consider the below example of it.
GO
CREATE FUNCTION [dbo].[GetEmployees]
(
 @DepartmentId int 
)
RETURNS 
@ResultSet TABLE 
(
 EmployeeId int,
 EmployeeName varchar(20)
)
AS
BEGIN
 INSERT INTO @ResultSet 
  SELECT EmployeeId,EmployeeName 
  FROM Employees 
  WHERE DepartmentId = @DepartmentId
 
 RETURN 
END

GO


Using CROSS APPLY
SELECT * FROM Departments
 CROSS APPLY GetEmployees(Departments.DepartmentId)

The above query returns the rows with employees assigned to departments, whereas the department wherein the employees are not assigned does not appear. Thus we can see the department "Training" is missing.


Using OUTER APPLY
SELECT * FROM Departments 
 OUTER APPLY GetEmployees(Departments.DepartmentId)

The above query returns all the rows with the outer table, whether or not the function returns data for a specific row. Thus we can see that employee value of "Training" department has NULL values.

Learn by diving in Programming Ocean...
Happy Programming!!!

Tuesday, November 8, 2011

SSRS - Report Viewer - Hide PDF Export option

SQL Server Reporting Services eases designing of reports, whereas Microsoft Report Viewer control is used to display .rdl/.rdlc report into ASP.Net website.

Moreover report viewer control provides the various options in its toolbar. Of the different options, one option provides functionality of Exporting the report in different formats (like XML, CSV, Word, Excel, PDF ...)

Now what if one wants to exclude any/all of the above options?

Say if I want to hide PDF export option from Report Viewer toolbar. Below are the steps to do so.

Step - 1
Locate the file name "RSReportDesigner.config" at
"Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\RSReportDesigner.config".

Note: Location of the file varies in different version of SQL Server.

Step - 2
Disable PDF export option - add the visible attribute with value false
<Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering" Visible="false" />


Enable PDF export option - remove the visible attribute
<Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PDFRenderer,Microsoft.ReportingServices.ImageRendering" />


Step - 3
After this you need RESTART your Report Server.

This can be applied to other exporting options too.



Learn by diving in Programming Ocean...
Happy Programming!!!

Friday, October 21, 2011

SQL Query – How to get data in Hierarchical Structure?

Download sample data with Schema
Prepare an SQL query to display the given data in “Hierarchical Structure” based on the level of the parent node. In a way that tree structure can be prepared.

I have one table:
  • Location – It contains the data with Parent – Child relationship.

The structure of the table and result set is as follows:

Location
Resultant Data

Please provide your SQL query in comment of this article.

Click here to download the sample data including schema.

Following are the solutions provided by:
Me (Chirag Vidani)
WITH LocationList (LocationId,LocationName,ParentLocationId,LocationLevel)
AS
(
    SELECT loc.Id, loc.Name,loc.ParentId, 1 AS LocationLevel
    FROM Location AS loc
    WHERE loc.ParentId IS NULL
    UNION ALL
    SELECT l.Id,l.Name,l.ParentId,list.LocationLevel + 1
    FROM Location AS l
    INNER JOIN LocationList AS list
        ON l.ParentId = list.LocationId
)
SELECT LocationId,LocationName,isnull(ParentLocationId,0) 'ParentLocationId',LocationLevel FROM LocationList


Mr. Kaushik Patel
WITH LocationLevelReports (LocationID, Locationname , ParentLocationId, LocationLevel)
AS
(
-- Anchor member definition
SELECT L.ID, L.name, ISNULL(L.ParentId,0) ParentId, 
1 AS LocationLevel
FROM Location AS L
WHERE ParentId IS NULL
UNION ALL
-- Recursive member definition
SELECT L.ID, L.name, L.ParentId, 
LocationLevel + 1 as LocationLevel
FROM Location AS L
INNER JOIN LocationLevelReports AS R
ON L.ParentId = R.LocationID

)
select * from LocationLevelReports



Learn by diving in Programming Ocean...
Happy Programming!!!

Thursday, October 13, 2011

SQL Query - How to Select top 2 highest salaried employees from each department?

Download sample data with Schema
Let us share some knowledge of SQL Query.

I have three tables mentioned below:
  • Employee – Master table
  • SalaryDetails – Reference table with salary information
  • Department – Master table

The sample data into these tables is show below:

Employee
Department
Salary Details
Now the SQL Query to be prepared should contain resultant data as ‘top 2 highest salaried employees from each department’ as below :

Resultant Data
Please provide your queries in comment of this article.

Schema with sample data is provided on download link, which will save your time for preparing sample tables and data. Click here to download.

Following are the solutions provided by:
Me (Chirag Vidani)
select 
 data.DepartmentName 'Department Name', data.EmployeeName 'Employee Name', data.Salary 'Salary/Day' 
from
 (select 
  d.Name 'DepartmentName',e.Name 'EmployeeName',sd.Salary 'Salary',dense_rank() over(partition by sd.DepartmentId order by sd.Salary desc)'SalaryRank' 
 from 
  Salarydetails sd
 join Department d 
  on sd.DepartmentId = d.Id
 join Employee e 
   on sd.EmployeeID =e.Id) as data
where 
 data.SalaryRank <= 2
order by 
 data.DepartmentName,data.Salary desc


Mr. Kaushik Patel
SELECT 
 d.* 
FROM
 (select 
  S.*,D.NAME DepartmentName,E.NAME EmployeeName ,RANK() OVER (PARTITION BY DeptID ORDER BY SALARY DESC) AS Raw_Rank 
 from 
  Salary s 
 inner join Employee e 
  on s.EmployeeId = e.id 
 inner join Department d 
  on s.DepartmentId = d.id ) d 
WHERE 
 Raw_Rank <=2



Learn by diving in Programming Ocean...
Happy Programming!!!

Wednesday, October 5, 2011

How to RESET identity column in SQL Server

During the development of any application we input dummy data and which is stored in database. But frequently we come to the point where we want all records of the table to be deleted and also want to start the identity columns values from 0.

For this we delete the data using truncate command.
It will delete the data from table and also reset the identity column’s value to 0.
truncate table <table_name>

truncate table product


But the truncate command fails to delete the data if there is relationship given to the table and the identity column is not reset.

For this after firing the delete command execute below command.
It will reset the identity column of product table to 0.
DBCC CHECKIDENT('<table_name>', RESEED, <reset from number>)

DBCC CHECKIDENT('product', RESEED, 0)


Learn by diving in Programming Ocean...
Happy Programming!!!

Monday, September 26, 2011

Preparing “Data Dictionary” using SQL Query

Data dictionary is the required while documenting any project. So here is the query for preparing the data dictionary of any database.

SELECT [Table Name] = OBJECT_NAME(c.object_id)
,[Column Name] = c.name
,[Data Type] = t.name
,[Size] = c.max_length
,[Description] = isnull(ex.value,'') 
FROM sys.columns c 
LEFT OUTER JOIN sys.extended_properties ex ON (ex.major_id = c.object_id AND ex.minor_id = c.column_id AND ex.name = 'MS_Description') 
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id 
WHERE OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0 
AND OBJECT_NAME(c.object_id) in (select name from sys.tables where type= 'U') 
ORDER BY OBJECT_NAME(c.object_id), c.column_id

Just include the column of the table definition (if any other required - i.e. if you want to display whether the column is null/not null, just include the field from sys.objects table and so on).


Learn by diving in Programming Ocean...
Happy Programming!!!