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?
kowsika deviAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
PortletPaulEE Topic AdvisorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.