• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 77
  • Last Modified:

windows function in sql server

what is windows function.when it introduced in sql server. when we need to use window function. if we use windows function performance wise what it will happ?
0
kowsika devi
Asked:
kowsika devi
  • 7
  • 5
  • 2
  • +1
1 Solution
 
Pawan KumarDatabase ExpertCommented:
>> What is windows function??
Directly from Microsoft - Determines the partitioning and ordering of a rowset before the associated window function is applied. That is, the OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

Read more from Microsoft - https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.
Read more examples from - https://msdn.microsoft.com/en-us/library/ms189798(v=sql.100).aspx

>> When it introduced in sql server.

1. In 2005 below ranking functions were added by the SQL Team
ROW_NUMBER, RANK, DENSE_RANK, and NTILE

2. In 2012 below functions were added by the SQL TEAM

a. Offset functions (LAG, LEAD, FIRST_VALUE, and LAST_VALUE)
b. Window distribution functions (PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, and PERCENTILE_CONT)

>> When we need to use window function.
Lot of areas. Infact we can say that alomost every where people use them. They make developers life easy.

>> if we use windows function performance wise what it will happ?
Most of the time they improve the performance but it depends on the situation where you are using them. So if you have a situation we can check and provide our suggestion.

>> Example
SELECT * ,ROW_NUMBER() OVER (ORDER BY AdminFeeId) rnk
FROM Budget

Open in new window

0
 
kowsika deviAuthor Commented:
already i used that windows function in one sp jj i used like this for temp table insertion.
SELECT Row_number()
         OVER (Partition BY lnno
           ORDER BY (effdate )),lnno,docno,effdate,amount,0,Isnull(ExsAmount, 0),
       process FROM   loln_LnMisTranprocess
WHERE  Isnull(process, '') <> 'Y'
       AND mode = 'coll'
       AND Trantype IN ( 'RI', 'ARS', 'EST' )
       AND Isnull(ChqStat, '') <> 'D'
ORDER  BY lnno,
          effdate

my manager asked to remove order by clause in where condition it affect our permonce also <> should not use like that he said?
is that affect our permonce used order by clause and <> this symbol in sp?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
While insertion we should not use ORDER BY as it decreases the performances. you can use

SELECT Row_number()
         OVER (Partition BY lnno
           ORDER BY (effdate )),lnno,docno,effdate,amount,0,Isnull(ExsAmount, 0),
       process FROM   loln_LnMisTranprocess
WHERE  Isnull(process, '') <> 'Y'
       AND mode = 'coll'
       AND Trantype IN ( 'RI', 'ARS', 'EST' )
       AND Isnull(ChqStat, '') <> 'D' 

Open in new window

0
 
kowsika deviAuthor Commented:
what are the key words we should not use while insertion at the same <> (not equal symbol also should not use?)  jj can u pl share
0
 
Pawan KumarDatabase ExpertCommented:
SELECT Row_number()
         OVER (Partition BY lnno
           ORDER BY (effdate )),lnno,docno,effdate,amount,0,Isnull(ExsAmount, 0),
       process FROM   loln_LnMisTranprocess
WHERE  Isnull(process, '') <> 'Y'
       AND mode = 'coll'
       AND Trantype IN ( 'RI', 'ARS', 'EST' )
       AND Isnull(ChqStat, '') <> 'D' 

Open in new window


The above looks fine. As long as query is not taking time then we are fine as we have to look for the execution plan and see what are the time consuming operators and how we can fix them.

Also we should be SELECT * INTO #temp approach as it is very quick.

SELECT Row_number()
         OVER (Partition BY lnno
           ORDER BY (effdate )),lnno,docno,effdate,amount,0,Isnull(ExsAmount, 0),
       process
INTO #Temp

 FROM   loln_LnMisTranprocess
WHERE  Isnull(process, '') <> 'Y'
       AND mode = 'coll'
       AND Trantype IN ( 'RI', 'ARS', 'EST' )
       AND Isnull(ChqStat, '') <> 'D'
1
 
PortletPaulfreelancerCommented:
Don't use functions on table columms in the where clause if there is an alternative available without functions.. Functions usually remove the ability of the query to use indexes and slow down queries. So, for example don't use

       Isnull(process, '') <> 'Y'

       Isnull(ChqStat, '') <> 'D'

Instead use

    ( Process <> 'Y' or process IS NULL )

    ( ChqStat <> 'D' or ChqStat IS NULL )
0
 
kowsika deviAuthor Commented:
IS JSON Support 2012 JJ
0
 
Pawan KumarDatabase ExpertCommented:
>> IS JSON Support 2012 JJ
No

They have introduced the JSON in 2016.

You can read more about this from Microsoft -  https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server
1
 
kowsika deviAuthor Commented:
THANKU JJ i need to learn some complex interview senario based question.thy are giving real time senarios either queries are performance tunning which site shall i refer jj .
0
 
Pawan KumarDatabase ExpertCommented:
Below are my website link which will help you in the regard.

For SQL Server Interview Question-
https://msbiskills.com/sql-server-interview-questions/

For SQL Server Simple/Medium/Complex Puzzles-
https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/

For Performance tuning
https://msbiskills.com/sql-performance-tuning/

Good Luck
1
 
kowsika deviAuthor Commented:
thanku jj
0
 
kowsika deviAuthor Commented:
i have one doubt jj can u say pl.
Logical Binding Order or Logical Query Processing Phases
https://msbiskills.com/2015/09/15/sql-server-logical-binding-order-or-logical-query-processing-phases/
can u pl say our normal qury how it takes the execution plan

Basically, Data flows from RIGHT (TOP) to LEFT in the query plan. That is it data centric view of the query plan. Logic in the query plan flows from LEFT to RIGHT. This is logic centric view of the query plan.

what is this jj i didnt get wt ur saying.
0
 
PortletPaulfreelancerCommented:
Perhaps you should message Pawan about his blog instead
https://www.experts-exchange.com/members/pawankkmr.html?messageMe

Thanks.
0
 
kowsika deviAuthor Commented:
ok Paul jj.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now