How to Remove duplicate rows from result ?

Today I explain how to remove duplicate value from result.
Below I am creating one table and I am putting only one column, but this scenario will work for more than one columns also.
— Create table
create table t1(id int)
— Insert data
insert into t1
values(1)
insert into t1
values(1)
insert into t1
values(2)
insert into t1
values(2)
insert into t1
values(3)
insert into t1
values(3)
insert into t1
values(4)
insert into t1
values(4)
— Use Cte for getting data
;with cte
as
(select ROW_NUMBER() OVER(Partition by id order by id) as idno,id from t1)
select * from cte where idno = 1

I hope this post will be useful for you.

Sqlserver Query optimization Tips

Hello friends, I have learn some useful tips that i fill i need to put on blog so i am sharing with you, so below is the list of Query optimization Tips.

1. Restrict the Query result. Means return the required row and column from the query, This helps in reducing network traffic.

2. User Store procedure in place of the Queries. Store procedure is compile object so next time i will use execution plan created by the sqlserver engine.

3. Avoid Cursor that will improve your performance. Cursor fetches records row by row so it will make round trips.

4. Avoid Trigger if not needed. It will execute every time when data gets updated.

5. Use Table variable in place of the Temporary table. Temporary table will create in Tempdb it’s overhead to create table. Table variable is doesn’t need to create in the memory and it’s automatically remove when transaction complete.

6. Use “where” in place of Having clause.

7. Use “SET NOCOUNT ON” in store procedure, It’s stops sending message of rowcount and reduce traffic.

8.Use Dynamic Query with this syntax.Check below link
https://sqlmca.wordpress.com/2010/11/10/use-of-dynamic-sqlquery-and-its-optimization/

9. Use indexing where you need.(use it carefully)

10. Update your statistics in regular interval. Update Statistics will rebuild your indexes and give your good performance.

11. Check your execution plan for the No table scan.

I hope that will be useful tips for you and you can use it practically.
If you like it or any query give your valuable time by writing comments.

How to get Data from One procedure to another Procedure or Dynamic Sql?

Today I will explain the very small but very useful features which I use in the past for the procedures. When I am new to sqlserver I am facing many problems this is one of this that I will explain to you today.

That you all know that, User defines Function has limitation that we can not use #temp table or Table variable in UDF. Because of that we can not get the data from another function in the UDF. At that time I find the solution that we are now create the procedure and we get data from that procedure for further manipulation. Question is how to get data from another procedure?. You can get the answer of this question in this post..

e.g., suppose procedure ReturnStudentDetail return the table with two fields Name and City so how can you get that table in another procedure for further manipulation.

–Code

— Create Table

Create table #StudentDetail

(Name varchar (50), City varchar (100))

— Below code give you data in the procedure

Insert into #StudentDetail

Exec ReturnStudentDetail

This syntax works only if you create table like this. You also use Dynamic Sql output for inserting data in #temp table.

— Create Table

Create table #StudentDetail

(Name varchar (50), City varchar (100))

Declare @sql as varchar (500)

Set @sql = ‘Select Name, City from tbl_StudentDetail where ……..’

— Below code give you data in table

Insert into #StudentDetail

Exec (@sql)

So, this is how you can get the data from another procedure or dynamic sql in Table.

Basic thing is whatever the structure return on the basis of that you have to create the table and use that table to get data from procedure or dynamic sql.

I hope this feature is useful for in future. If any query feel free to ask me.