Search This Blog

Space Used by Tables in a Database in SQL

Space Used by Tables in a Database in SQL

In order to know which table is using maximum space or How much space each table is using in a Database 
so for this SQL Procedures are there

In order to check space used by a single table

--Space Used by Single Table in Database

USE Database_Name
EXEC sp_spaceused 'Employee'; 

Output


name       rows           reserved                data                index_size               unused

Employee 7           16 KB               8 KB                   8 KB                  0 KB

--Space Used by All Table in Database


USE Database_Name;


EXEC sp_msforeachtable 'EXEC sp_spaceused [?]'

Output

All tables of Database with there size 


name       rows           reserved                data                index_size               unused

Employee 7           16 KB               8 KB                   8 KB                  0 KB


name       rows           reserved                data                index_size               unused

Employee1 7           16 KB               8 KB                   8 KB                  0 KB


name       rows           reserved                data                index_size               unused

Employee2 7           16 KB               8 KB                   8 KB                  0 KB

How to Rename a column,Table or Database in SQL

How to Rename a columnName,Table or a Database in SQL

To Rename a columnName or TableName creating table is to use SP_Rename System Procedure

To Rename a DBName SP_Renamedb System Procedure

Below we will create a table then we will apply SP_Rename to change ColumnName Table name

create table Employee_rename
(Empid int ,EmpName nvarchar(50),EmpSalary float)
insert into Employee_rename values(1,'Amit','5000'),(2,'Sumit','6000'),(3,'Raj','8000'),(4,'vijay','9000'),(5,'suresh','10000')

select * from Employee_rename


--Rename a ColumnName 1parameter tablename.oldcolumnname 2 parameter NewcolumnName

sp_rename 'Employee_rename.EmpName','EmployeeName'

select * from Employee_rename


--Rename a table name 1parameter old tablename 2 parameter Newtable Name

sp_rename 'Employee_rename','Emp_rename'

select * from Emp_rename


--Rename a Database name 1parameter old dbname 2 parameter NewDB Name

sp_renamedb 'Testing','Testingnew'


Alternative way to Rename DBName-Rightclick DBName from Object Explorer -Rename

Alternative way to Rename Table-Rightclick TableName from Object Explorer -Rename

Alternative way to Rename Column -Rightclick TableName-Design-Change ColumnName-Save the Table Name


Note: For Table and column Name we have used SP_Rename Procedure, but to Rename DB
we have Used Sp_Renamedb Stored Procedure
After testing change your db ,TableName and columnName back to PreviousName

Almost All Strings function of sql 2008

Almost all Strings Manipulation function of sql 2008 like Substring, Left, Right , Stuff, Replace, Replicate...........

This post is to show all string functions of SQLwith examples  and How to use them in TSQL.

We will take a string 'Bangalore' and Apply all function in that string.

--Stuff Replacing value from 'ang' to 'abc' position 2-4 to 'abc' output babclore
select 'bangalore',STUFF('bangalore',2,4,'abc')


--Return value from Position 2-4  output ang
select 'bangalore',substring('bangalore',2,4)


--Replace values b to z output Zangalore
select 'bangalore',REPLACE('bangalore','B','Z')


--Replicate 'bang' word 5 time Output 'bangbangbangbangbang'
select 'bangalore', Replicate('bang',5)


--Reverse the value of bangalore output erolagnab
select 'bangalore', reverse('bangalore')


--Take three values from left Output 'Ban'
select 'bangalore',LEFT('bangalore',3)

--Take three values from right Output 'ore'
select 'bangalore',right('bangalore',3)


--convert values into upper case Output 'BANGALORE'
select 'bangalore',UPPER('bangalore')


--convert values into lower case Output 'bangalore'
select 'bangalore',lower('bangalore')

--Search the pattern and return starting position of  pattern  value if pattern not found returns zero
--output 2
select 'bangalore',PATINDEX('%ang%','bangalore')


--Search the value and return  position of  value if value not found returns zero
--output 2
select 'bangalore',charindex('n','bangalore')


--Count no of Character and  return no. of characters  it include space also Output -13
select 'bangalore' ,LEN(' bangalore is')


--returns no. of byte in a string
select 'bangalore' ,DATALENGTH('bangalore is')


--Trim white space from right side of string
select 'bangalore',RTRIM(' bangalore ')


--Trim left space from right side of string
select ' bangalore',lTRIM(' bangalore')

Note: In above function we have taken the example of a string 'bangalore' in order to use it with tables replace 'bangalore' with your column names'


Update column values with other column values using Select query in SQL

Update column values with other column values using Select query  in SQL

You have a scenario where you want to update your table column value with other table column values for eg  all values of a column to be updated with all values of a column of other table.
for this we will create two tables
The first table will have old salaries of Employees and the second table will have updated salaries of employees
so what we have to do update oldsalary of table1 with newsalary of table 2

Below is the script with Example

--table with old salaries
create table Employee_Update_oldsalary
(Empid int ,EmpName nvarchar(50),EmpSalary float)
insert into Employee_Update_oldsalary values(1,'Amit','5000'),(2,'Sumit','6000'),(3,'Raj','8000'),(4,'vijay','9000'),(5,'suresh','10000')

select * from Employee_Update_oldsalary

create one more table with new salary of the employee

--table first with new salaries
create table Employee_Update_newsalary
(Empid int ,EmpName nvarchar(50),EmpSalary float)
insert into Employee_Update_newsalary values(1,'Amit','15000'),(2,'Sumit','16000'),(3,'Raj','18000'),(4,'vijay','19000'),(5,'suresh','110000')

select * from Employee_Update_newsalary 

Now we want to Update old salary table  with new salary 

--Test this before runnig update query in table what you are updating

 select m.EmpSalary as oldsalary,mu.EmpSalary as newsalary
 from Employee_Update_oldsalary M  join  Employee_Update_newsalary MU
 on m.Empid=mu.Empid and m.Empid=mu.Empid
 --Update query that will update old salary with new salaries


 update Employee_Update_oldsalary  set EmpSalary=mu.EmpSalary 
  from Employee_Update_oldsalary m   join  Employee_Update_newsalary MU
 on m.Empid =mu.Empid and m.Empid =mu.Empid

old values will be updated with new values

Note: In scenario like this test query before running in production server and Secondaly write query inside transactions blocks ie commit rollback so that if any value will updating  values will fail it will rollback all the updated values.



Elements of Select Statement and how they are logically processed in SQL

Elements of Select Statement and how they are logically processed in SQL

This is my 50th blog post so i thought to write something interesting so here I am writing how a select query logically processed along with its elements  and  what's the order of elements of select query when logically processed..

The Select Clause are Logically Processed in Below Order.

  1-FROM
 2-WHERE 
 3-GROUP BY
4-HAVING
5-SELECT
6-DISTINCT
7-ORDER BY
8-TOP

Even though select comes first in the query but it is logically processed almost last

Lets take a example

Select empid,max(salary) as Empsalary
from Employee
where Empid between 1 and 100
group by empid
having max(salary)>10000
order by Empsalary desc

How this query logically Processed
--First Processing
From TableName
--next
Where Condition
--next
Grouping By 
--next
Having condition
--next
Order by 

Unfortunately, we cannot write the query in correct logical order. We have to start with the SELECT clause.

If you will see the above logical processing and query, Did you noticed that i have used aliasing for Max salary and same alias name i have given in Order by condition but not in Having Condition.
The Reason behind this is Having Processed before Select Clause and Order by after select clause, so after getting value in Select query  you are using alias name for order by but having Processed before select so if you will use alias name in Having Clause it will thrown an error.

--To check error run this query
Select empid,max(salary) as Empsalary
from Employee
where Empid between 1 and 100
group by empid
having Empsalary>10000
order by Empsalary desc

Error.
Invalid Column Name Empsalary .

I found it basic but yet interesting and less known Fact for developers.




Fetch Last four Records without using Order by in SQL

Fetch Last four Records without using Order by in SQL

Yesterday one of my Friend asked me How to fetch last four Record without using Order by and second thing was the table doesnt have any primary key or AutoIncreament Column .
I like the question so thought to have blog on this.
Even though with order by the solution is easy but  little diffcult without Order by.
Here is the actual question

Column1 column2 5 s 4 d 8 f 9 r 6 t 8 y 7 h 3 u 6 i OUTPUT: Column1 Column2 8 y 7 h 3 u 6 i

Fetch last 4 records with out using ORDER BY

In order to get last four records without using order by we will create a table Test with above data

CREATE TABLE [dbo].[test]( [column1] [nvarchar](50) NULL, [column2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[test] ([column1], [column2]) VALUES (N'5', N's') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'4', N'd') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'8', N'f') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'9', N'r') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'6', N't') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'8', N'y') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'7', N'h') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'3', N'u') INSERT [dbo].[test] ([column1], [column2]) VALUES (N'6', N'i')


Output Required
Column1 Column2 8 y 7 h 3 u 6 i

First Solution using cursor
--Declare 8 variable in order to get data in same order as output
DECLARE @var1 varchar(50),@var2 varchar(50),@var3 varchar(50),@var4 varchar(50),@var5 varchar(50),@var6 varchar(50),@var7 varchar(50),@var8 varchar(50)
declare @test table (column1 nvarchar(50), column2 nvarchar(50) ) DECLARE MYCURSOR CURSOR
DYNAMIC FOR
SELECT column1,column2 FROM test OPEN MYCURSOR
FETCH LAST FROM MYCURSOR INTO @var1,@var2 -- Fetch the row immediately prior to the current row in the cursor. FETCH PRIOR FROM MYCURSOR INTO @var3,@var4 FETCH PRIOR FROM MYCURSOR INTO @var5,@var6 FETCH PRIOR FROM MYCURSOR INTO @var7,@var8
insert into @test values(@var7,@var8),(@var5,@var6),(@var3,@var4),(@var1,@var2) select * from @test
CLOSE MYCURSOR DEALLOCATE MYCURSOR

Column1 Column2 8 y 7 h 3 u 6 i

Solution with Order by inside a function

with test1 as ( select row_number()over( order by @@rowcount)rownum,* from test ) select column1,column2 from test1 where rownum>5


Another Solution with Order by inside a function

with test1 as ( select row_number()over( order by (select 0))rownum,* from test ) select column1,column2 from test1 where rownum>5

Another Solution without Order by -Fetching last four rows but only if you dont have any duplicate value in column2 like we have in our table

select * from test where column2 not in ( select top ((select COUNT(*) from test)-(4)) column2 from test )

All solutions will give you last four Records.




Selecting Maximum Value with other column of other tables in SQL

Selecting Maximum Value with other column of other tables  in SQL

you might have faces a situation where you want maximum value a column with other columns from other tables also.
you can use group by in order to get Maximum value but if you are displaying all fields in the select query and joining with other tables  then you have to alter your query
.
Let me explain you through  a table

Suppose in a table you have empid name and salary the salary is revised again and new rows were added in the table with new salary you have one more table Projects with empid, projectid and projectname.
Now you have to display Empid,Name Porjectid,ProjectName and New Updated salary in a single query
Like below screen shot.





we will create to tables


create table Employee1_error3
(Empid int ,EmpName nvarchar(50),EmpSalary float)
insert into Employee1_error3 values(1,'Amit','5000'),(2,'Sumit','6000'),(3,'Raj','8000'),(4,'vijay','9000'),(5,'suresh','10000')

--salary revised or updated

insert into Employee1_error3 values(1,'Amit','15000'),(2,'Sumit','16000'),(3,'Raj','18000'),(4,'vijay','19000'),(5,'suresh','110000')

select * from Employee1_error3

now you have another table 

Create table Projects1
(Empid int,Projectid int ,ProjectName varchar(50))

insert into Projects1 values(1,100,'Test1'),(2,101,'Test12'),(3,102,'Test13'),(4,103,'Test14'),(5,104,'Test15')


Now in your query you want new empid ,empname, salary with projectid and projectnames 



i will tell the simple and easy way




select a.*,b.projectid,projectname from 
(select empid,empname, max(EmpSalary) as salary  from Employee1_error3
group by empid , empname)A
join 
(select empid, Projectid,ProjectName from Projects1)B 
on B.empid=A.empid


Another way using cte and row num

with cte as 
(
select A.empid,empname,Projectid,ProjectName ,empsalary,row_number() over(partition by A.empid order by empsalary desc) as Rownum from Employee1_error3 A 
join Projects1 B on B.empid=A.empid
)
select * from cte where rownum=1

 In the Second query rownum is just for reference you can remove * and write columns name to get exact result in outer part of CTE.

Note : There are other ways also to achieve this results but i found the above ways easy and simpler.




Conversion failed when converting the varchar value to data type int or any other Datatype in SQL

Conversion failed when converting the varchar value to data type int or any other Datatype in SQL.

The Reason for this error is you are converting a varchar value to Int or Numeric or any Other datatype let us take a simple example 

select CAST('abc' as int)

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'abc' to data type int.

what we are doing in above query is changing the nvarchar value to int which is not possible that why it throw the error.

While working with TSQL Coding if you are adding some string value with alphanumeric field  you could face the above problem if you have not written your query properly.
Take an Example where you want to show empid\Empsalary from employee table
for this we will create a table below 

create table Employee1_error1
(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)
insert into Employee1_error1  values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000')

select * from Employee1_error1 

now we want to display employee empid\Empsalary in same format \we are concatenating the slash in between empid\Empsalary 

like  we want our result like 

EmpId\Empsalary
 1\5000
 2\6000 and so on ..........
             
now run the below query 

select cast(empid+'\'+empsalary as varchar) from Employee1_error1

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '\' to data type int.

we are adding backslash sign between empid and empsalary and coverting the whole into varchar but why it's throwing error
The  Reason is empid is a int field empsalary is a float field and we are doing a complete conversion of all values to varchar that's why it is throwing error 
In order to fix this error Cast Empid Seperately to varchar and same with Empsalary as varchar than added them to backslash.

select cast(empid as varchar)+'\'+ cast(empsalary as varchar) as 'Empid\Empsalary' from Employee1_error1

Output

Empid\Empsalary
1\5000
2\6000
3\8000
4\9000
5\10000


Cannot Perform an Aggregate function on an expression containing an aggregate or a query Error in SQL

Cannot Perform an Aggregate function on an expression containing an aggregate or a query Error in SQL

While working with sql aggregate functions , if you are using an aggregate function for a column and again over that you are using one more Agg function then sql will throw above .error
If you read this error properly it is saying you cannot perform an aggregate function o on expression 
containinga an aggregate This mean that in that field u had already applied a agg function and again ur applying aggregate function over the same Expression.

for eg

Select Min(sum(Empsalary) from employee

In above query u noticed that first function summing the value then applying Min function on that so this will throw the above error the reason for throwing error that sql cannot evaluate minimum value from a sum field.

How i got this error on of my collleague told that he want sum and Count of two column  as total
below is the eg of syntax what he was doing i will explain by a test table

create table Employee1_error
(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)
insert into Employee1_error values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000')

select * from Employee1_error


Now he wants Total of (count of empid  and sum of salary)  and his query was like

--This query will throw an error 
select  sum(COUNT(empid)+ SUM(empsalary)) as Total  from Employee1_error

Msg 130, Level 15, State 1, Line 1
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I checked the error and explain him about the error 
Below is the correct syntax

select  COUNT(empid)+ SUM(empsalary) from Employee1_error


Handling Divide BY Zero Exception\ Error in SQL by NULLIF and ISNULL function

Handling Divide BY Zero Exception\ Error in SQL by NULLIF and ISNULL function 

Some times while doing Calculation in your query you got an error or exception "Divide by Zero" and some time you will have  Output value Like NULL So how to handle these Issue and Exception.

Use NULLIF -To Handle Divide by zero Exception
Use ISNULL -To Show some value instead of NULL in your output

Below is the complete explanation
select 10/0

if you run the above query it will throw an error

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

Now in order to Solve this we will use Nullif function

select nullif (10/ nullif (0,0),0)

Output NULL

Now it will not throw an error and your output will be null

Explanation:NULLIF function takes two arguments check wheather they are equal or not if they are equal it will return NULL as output 
IF  both expressions are not equal it will return the first expression with same data type.
now in the denominator it checked wheather 0 is equal to 0 
The conditon was true it returned Null in denominator 
now the expression was like 

select Nullif(10/null,0)

now again it will check wheather 10/Null is equal to zero condition fails 

and you will get

 Output  NULL 

Note : AnyNumber divide multiplied,added subtracted with NULL will Resultant to NULL only.

Now how to use NULLIF and ISNULL in your Code or in TSQL to avoid exception and NULL Values .
In the below code i have used nullif with ISNULL function .

For this we will  Create a table

CREATE TABLE Test_NULLIF1
(
   col1            int   IDENTITY,
   col2      int   NULL,
   col3   int   NULL
);
INSERT Test_NULLIF1  VALUES(10, 20);
INSERT Test_NULLIF1 VALUES(NULL, 23);
INSERT Test_NULLIF1 VALUES(0, 18);
INSERT Test_NULLIF1 VALUES(NULL,75);
INSERT Test_NULLIF1 VALUES(300000, 21);
Go

If you will divide column 3 by column 2 for Row 3 it will throw Divide By zero Exception 

Run this query to generate error..

select col3/col2  from Test_NULLIF1  where col1 =3

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

In order to handle this Use nullif function as explained in above example 

select nullif(col3/nullif(col2,0),0)  from Test_NULLIF1  where col1 =3

Output NULL

Now You can Use ISNULL function after handling divide by zero exception to give a value when you have output like NULL
for eg like instead of NULL you want to show value like 0 or 1 or anything

select ISNULL(col3/nullif(col2,0),1)  from Test_NULLIF1  where col1 =3

Output 1

Explanation: ISNULL Function takes two argument check wheather first expression is NULL
 if null provide a replacement for that NULL in second expression it can be any value.

IN query ISNULL checked the first expression it was NULL so it replaced the NULL Value with 1,
and return output  as 1.

IN case if first expression is not null it willl return the first expression value only. .

Test IsNULL function with below queries 

eg 
Select ISNULL(null,2)

output 2

select isnull(3,1)--as first expression is not null so it returns first value i.e. 3

output 3






The report parameter has a default value forward dependencies are not valid Error in SSRS.

The report parameter has a default value forward dependencies are not valid Error in SSRS.

A comman problem while working with SSRS Reports when you have created Multiple Parameters and 
Created Cascading Parameters then there are chances you will face this problem due to order of Parameters.
In order to know about cascading of Parameters Check my earlier blog on Cascading of Parameter.

The Reason for this Error.
1-When working with Multiple Cascading Parameter if you have added one more parameter in your report
then the Parameter position will be last and lets us suppose that parameter is taking filter value from first parameter so it Should be your second Parameter but you have added it newly so it's comming at last position In this case if you will preview the Report .
The Report will throw an Error saying.

The report parameter has a default value forward dependencies are not valid Error in SSRS.

Solution-In order to Solve this Problem Select your last Parameter change its position to second place 
In order to do this go to view -Last Click Report Data
Screen will come like this.


Now in order to remove error you have to  move Last Parameter ResourceUID to 2nd Place
Select ResourceUID click on arrow key Upward Direction move it to place 2nd
Now the Resource UID in Second Place 


Preview your Report the error will not be there.


Minimum or Maximum Values from Entire Table or from Multiple Tables in SQL

Minimum or Maximum Values from Entire Table or from Multiple Tables in SQL

This is the comman question by interviewer when you are going for interview yesterday one of my college time junior called me and asked that he went to interview and the interview has given him a table with 5 columns and told him to retirve the max and minimum value So i told him the answer and thought to write a blog on this.

So as per the above question you have a table having 5 column and you want to retrive max and min value from the table
Let me create a table with 5 columns

create table table1
(col1 int ,col2 int, col3 int ,col4 int,col5 int)
insert into table1 values(1,3,5,6,7),(10,11,24,44,12),(12,44,55,64,54),(11,22,44,55,100)
select * from table1 

Output


col1 col2 col3 col4 col5

1 3 5 6 7
10 11 24 44 12
12 44 55 64 54
11 22 44 55 100

Now if you will see that the minimum value in entire table is 1 and maximum is 100 
so how to retrieve this there are many ways but i will tell u the simpler and easier


Select MIN(d.col1)as Minimum,MAX(d.col1 )as Maximum from
(
select col1 from table1 
union --Union to take only distinct values we can use union all also
select col2  from table1 
union 
select col3  from table1 
union 
select col4  from table1 
union 
select col5  from table1 
)D

Output
Minimum Maximum
1           100

Same thing you can apply where you have more than 1 table and you are asked to calculate min or max or both from 2 or 3 tables

Simply add more union in the inner query .



Select MIN(d.col1)as Minimum,MAX(d.col1 )as Maximum from

(
select col1 from table1 
union --Union to take only distinct values we can use union all also
select col2  from table1 
union 
select col3  from table1 
union 
select col4  from table1 
union 
select col5  from table1 
union


select col1 from table2--Table 2 Column Names

union
select col2  from table2 
union 
select col3  from table2 
)D

Isn't its Simple if you want to try Another way you can use by using Derived table or CTE like taking minimum and maximum value 

of each column putting into derived table and CTE and From those min and max values picking the most minimum and most maximum Value.



Displaying selected Values of multiple selected Parameter and Displaying Column names in Text Box with Comma Separating the Values in SSRS

Displaying all Values of multiple selected Parameter in Text Box with Comma Separating the Values in SSRS

Today while Creating a Report the client requirement was  to show all ResourceName  in the Report Header so i thought to have a blog on this 
It's like displaying multiples ResourceName in a text box .

EG -Vijay,Suresh,Amit,Ankit -In a Single TextBox

In order to acheive this result 

We will consider a table Resources with column ResourceName

CASE 1- To show all column values in TextBox

create a Dataset 

Select top 20 ResourceName from Resources

Drag a Text Box in your Report Header 

In order to display these ResourceNames values in a Text Box
Right click -Text Box Expression

=Join(LookupSet(1,1,Fields!ResourceName.Value, "DataSetSummary")," , ")

Output-Vijay,Suresh,Amit,Ankit

Explanation: The Lookup function will check the source value which is 1 match with Destination value 1 
giving the Resultant as All ResourceName as 1=1 will always true
The outer Join Function will take two Parameter ResourceNames and Join it with Comma to Get Comma Separated Value

Case 2 Where you are Displaying your Multiple Selected ResourceName from ResourceName Parameter into the Text Box

=Join(Parameters!resourcename.Label, ",")

Explanation: Resourcename is the name of Parameter Join function to make it as Comma Separeted Value in Textbox .Label because we are displaying Label field in the Text Box if you have same value and Label filed you can write .Value also.

=Join(Parameters!resourcename.Value, ",")

Output-Vijay,Suresh,Amit,Ankit

Note: In above examples i have skipped simple parts like  creating Dataset part creating Parameter in order to know how to create Dataset and Parameter check my previous Blogs.







Deleting Duplicates Records in SQL

Deleting Duplicates Records in SQL

There are Many ways to delete duplicate records from table but i will tell you the most easy and efficient way.
Condition 1
When your table have a AutoIncreament or PrimaryKey Column

Condition 2 
When table doesn't have a AutoIncreament or PrimaryKey Column

we will create a table to delete duplicates records.

For Condition 1

create table Employee_Duplicate
(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)
insert into Employee_Duplicate values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000')

--Inserting Duplicate Records
insert into Employee_Duplicate values('Raj','8000'),('vijay','9000'),('suresh','10000')

Now we have to delete duplicates records and from Duplicates records we will keep the records with maximum EmpId i.e. record which are inserted later to make records Duplicate

--query to delete duplicate records for condition 1

delete  from Employee_Duplicate where empid not in  (select MAX(empid) from Employee_Duplicate 
group by EmpName,EmpSalary )

Explanation-The inner query will take the max value of Empid group by EmpName and Salary 
and Delete those records which are not in inner query.

For Condition 2

where you dont have any AutoIncreament or PrimaryKey Column

we will create a table to duplicate records without primary key column

create table Employee_Duplicate1
(EmpName nvarchar(50),EmpSalary float)
insert into Employee_Duplicate1  values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000')

--Inserting Duplicate Records
insert into Employee_Duplicate1  values('Raj','8000'),('vijay','9000'),('suresh','10000')

select * from Employee_Duplicate1 


with cte as
(select empname,empsalary,ROW_NUMBER()over(partition by empname,empsalary order by empname) as rownum from Employee_Duplicate1
)
select * from cte where rownum>1 --this will give the Duplicate Records list to test

--In order to Delete duplicate

with cte as
(select empname,empsalary,ROW_NUMBER()over(partition by empname,empsalary order by empname) as rownum from Employee_Duplicate1
)
delete from cte where rownum>1

--The above query will delete the Duplicate Records

Explanation -Here we have used Row_Number function which gives rownumber and  partition by empname and salary mean if those name and salary appears second time in the records it will give those records with row num 2
The reason we have used CTE i.e comman table expression  as we cannot apply rownumber function
directly in where clause

 run this query to check

select empname,empsalary,ROW_NUMBER()over(partition by empname,empsalary order by empname) as rownum from Employee_Duplicate1
where ROW_NUMBER()over(partition by empname,empsalary order by empname)>2

Msg 4108, Level 15, State 1, Line 3
Windowed functions can only appear in the SELECT or ORDER BY clauses.

NOTE: when you want to apply Row_Number function or other Ranking function  in where clause always use CTE.








Creating SubReport and filtering SubReport using MainReport Parameters in SSRS.

Creating SubReport and filtering SubReport using MainReport Parameters in SSRS

Many times you have a requirement where you want to display a Sub Report inside the Main Report.
for Eg Suppose we have a main Report as Product Category inside Product Category Report we want to Display Products Sub Category also as a Sub Report for these type of Scenario's we used sub Report.

Now you have one More Requirement like you have to filter both Main Report and Sub Report Based
on Main Report filter 
For Eg we have to filter  Product category and Product Sub Category  report based on Parameter Product Name in your main Report.

We will cosider above examples to create Reports

step how to create Sub Report.

we will create two Report Separetely .

1-Create your Main Report name it as  Product category.

2-Create two Dataset one for Report Dataset and one for Report Parameter.

3-Add a Report Parameter for the Main Report name it as ProductcategoryName allow multiple values..

4-Select your Tablix add filter where productCategoryName in Parameter Name which is Product CategoryName.

5- Now Create your SubReport name it as Product Subcategory.

6-Create two Dataset one for Report Dataset and one for Report Parameter.

7-Add a Report Parameter for the Sub Report name it as ProductcategoryName allow multiple values.

8-Select your Tablix add filter where productCategoryName in Parameter Name which is Product CategoryName.

9-Drop a Sub Report from Tool Box to Your Main Report.

10-Select subReport Control .

Right click-In General Tab- Select Produect SubCategory-In Use this Report as Sub Report Column.
In Parameter tab-Name your Parameter Name =ProductCategoryName Value @ProductCategoryName
or You can click fx in Parameter value Select Parameter from there also.

Preview the Main Report-The Report Parameter will filter both Reports in your  Main Report based on ProductCategoryName

Note:In order to avoid Error Sub Report Cannot be seen always create ReportParameter with same name in  Main Report and Sub Report .
Secondly we have created Dataset for Report Parameters in both Reports  because we are allowing Multiple values to Report Parameters i.e a user can select multiple Products category fro m Main Report Parameter.












IIF or IF Else in SSRS with Examples

IIF or IF Else in SSRS with Examples

Many times in your Report you have to Show Data Based on Some Condition which is like IF ELSE in other Programming Language .
In SSRS you don't have to write else condition within  IFF only you have to define what you want to do if condition satisfy and what if condition doesn't Satisfy.

Syntax eg

=iif(Fields!TaskIsCritical.Value like "True","Y", "N")

Field is the value of ur Data field

In the above iif condition  it will check wheather the TaskIsCritical which is a bit field is True or Not 
If it's true it will Display Y in the text Box else N.

if you want to check two condition like taskiscritical is true and Sumofcost >10000 then Y else N

you can write expression like below

=iif(Fields!TaskIsCritical.Value like "True" and  Sum(Fields!Cost.Value)>10000 ,"Y", "N")

In above IiF condition you are checking two conditon it will show Y in your TextBox only when 
Taskiscritical field value is true and Sum of cost is greater than 10,000.

In order to apply conditional formating in your Text Box like if  Cost is greater than 1000 then Tex box text color should be green else black.


Select your TextBox-Go to Text Box Properties-Right Hand in Solution Explorer

Select Color Properties-Expression write

 =Iif(ReportItems!Cost.Value > .1, "Green", "Black")

Note:In order to appply multiple if condition you can use switch Statement in SSRS.




How to Repair Suspected Database in SQL

 Repair Suspected Database in SQL

Some times your database goes to Suspect mode in that you cannot work on Database anymore

The Possible Reason of Suspected Database.
1-Database files has been corrupted
2-Insufficient Memory or Space in your Drive.
3-Hardware Failure
4-Unexpectdly  Shut Down of SQl Server or Sql Services.
5 Sometimes Moving Database files to other Location.

In order to Recover the Database from Suspected Mode

First way

ALTER DATABASE db_name SET EMERGENCY

This query will set ur Database to Emergency Mode 

Restore the Latest Backup

Then your Database will work

2-If you don't have Recent Back UP

You can Run this Query

EXEC sp_resetstatus [DatabaseName];

ALTER DATABASE [DatabaseName] SET EMERGENCY

DBCC checkdb([DatabaseName])

ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ([DatabaseName], REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE [DatabaseName] SET MULTI_USER

It will bring your DB back to Normal state but there are possibility that while conversion some of your data can be lost.

Note : In the 2nd Solutions there Might be chances that some of Data can be loss while Repairing so make sure ur not running this in Production server or when  you can afford data loss then only run the second option.

Filtering Dataset or Tabix using Report Parameter in SSRS

Filtering Dataset or Tabix using Report Parameter in SSRS

A very comman requirement in your Report where you want to filter your Dataset or Records using Some parameter so there are two ways to achieve this in SSRS.

In below example we  want to filter our Report Based on  ResourceId to check which resourceId is working on which project

for this i will create a table to Explain you in a more better way


--Creating a test table for the dataset

create table Projects
(
ProjectName nvarchar(50),
ResourceID nvarchar(50),
Hours int,
Cost float
)

insert into Projects values('ProjA',1,8,3000),('ProjA',2,7,7000),('ProjA',1,3,3000),('ProjB',4,8,3700),('ProjB',1,5,3800),('Projc',1,5,3800)


Creating Dataset for ResourceID

Now For Resource Name
create a Dataset-DSTResourceID
Query-

select distinct resourcename from projects 
click ok


Creating Parameters for ResourceID

-Go to Parameters
-Right Click
-Add parameter
-ResourceName
-Allow Multiple Values Checked
-Available Values
-Check Radio Button -Get Values from a Query
-Dataset-DSTResourceName
-Value field-ResourceName
-Display field-ResourceName
click ok


Now in your Main Dataset
You want to filter your Report Based on ResourceName
-Create Dataset for Main Report

Select projectname,resourceID,cost, hours from projectname
where resourceID in (@ResourceID)

This @ResourceID is your parameter that will filter your Report Based on ResourceID
Click ok

Now preview the report 

Select ResourceId from ur ResourceID parameter it will fiter the report

The Second Method 

where you dont want to add a where clause in your query

Go to  your Main Dataset reomove where Clause

Select projectname,resourceID,cost, hours from projectname

Go to filter properties of Your MainDataset

ADD FIlter
Expression - ResourceID

Operator-In

Values-Click-Fx and Select your Parameter ResourceID

click ok

Now preview the report 

Select ResourceId from ur ResourceID parameter it will fiter the report,

NOTE: In our Parameter we are using filter based on multiselection i.e more than 1 resourceID that's why we are using IN operator in case you want to filter with only one resourceId Remove Multiselection from Parameter Property and Change Operator to equal in Second Method.