sql nerchukundam randiii... Ms sql server technology discussions...

[mtkr]

40 minutes ago, k2s said:

@loveindia @mtkr help ??

@k2s helped

[ronitreddy]

1 minute ago, sanbk said:

Why can't you build non clustered indexes on all the columns provided you have good CPU performance (wipro interveiw question)

SSIS vi post chestha next... 

ee columns avi?

[mtkr]

Just now, ronitreddy said:

daddy..pillola mida ala satires veyocha?

daddy telisi aduguthunnadoo.. telusukovaalani aduguthunnado ardam kaavatle..

[ronitreddy]

43 minutes ago, k2s said:

chaduvu ki 

prema / love / ishq  ki vayasu tho emi sambandam man ?

chaduvu love goes hand in hand daddy

[ronitreddy]

Just now, mtkr said:

daddy telisi aduguthunnadoo.. telusukovaalani aduguthunnado ardam kaavatle..

ardam avvaleda inka..daddy thana pravinyam antha mana mida chupisthunadu

[ronitreddy]

8 minutes ago, sri_india said:

indhulo disco chusthuntee urgent gaa SQL technologies ki shift ayipovali ani undi... chala support undi ee DB lo SQL tech ki 

support unte chaladu man..Job undali

[ronitreddy]

16 minutes ago, mtkr said:

1) Index scan is looking all data pages from first to last... index seek is looking specific data pages...

scan preferred whn tbl is small..

seek preferred whn tbl is large...

orders table having no index on it
select * from orders --- table scan

orders table having clustered index on it
select * from orders --- index scan

orders table having clustered index on it
select * from orders where id = 2 --- index seek

https://blogs.msdn.microsoft.com/craigfr/2006/06/26/scans-vs-seeks/

 

2) create index index_name on table_name (column_name)

 

3) correlated sub query -  its a subquery wch uses values from outer query for execution 

(frm ggle)

A correlated subquery is one which uses values from the parent query. This kind of query tends to run row-by-row, once for each row returned by the outer query, and thus decreases SQL query performance. New developers are often caught structuring their queries in this way—because it’s usually the easy route.

Here’s an example of a correlated subquery:

SELECT c.Name, 
       c.City,
       (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName 
FROM Customer c
In particular, the problem is that the inner query (SELECT CompanyName…) is run for each row returned by the outer query (SELECT c.Name…). But why go over the Company again and again for every row processed by the outer query?

A more efficient performance tuning technique would be to refactor the correlated subquery as a join:

SELECT c.Name, 
       c.City, 
       co.CompanyName 
FROM Customer c 
    LEFT JOIN Company co
        ON c.CompanyID = co.CompanyID
In this case, we go over the Company table just once, at the start, and JOIN it with the Customer table. From then on, we can select the values we need (co.CompanyName) more efficiently.

shabash samara

[sanbk]

2 minutes ago, ronitreddy said:

ee columns avi?

eh columns ane specification em ledu oka table lo 100 columns unnayi oka column meeda clustered create chesi migatha 99 columns meeda nonclustered create cheyachaa ela chestharu chesthe em avuthadi nenu 16 column limit gurinchi cheppa inka emaina untadaa reason enduku cheyalemo apart from 16 limit and 900 bytes...

[mtkr]

6 minutes ago, sanbk said:

Why can't you build non clustered indexes on all the columns provided you have good CPU performance (wipro interveiw question)

SSIS vi post chestha next... 

multiple inserts updates deletes on that table decreases performance!!!??

[mtkr]

2 minutes ago, sanbk said:

eh columns ane specification em ledu oka table lo 100 columns unnayi oka column meeda clustered create chesi migatha 99 columns meeda nonclustered create cheyachaa ela chestharu chesthe em avuthadi nenu 16 column limit gurinchi cheppa inka emaina untadaa reason enduku cheyalemo apart from 16 limit and 900 bytes...

my ans: if multiple inserts and deletes - indexes hav 2 be modified n reorganized 

[mtkr]

6 minutes ago, ronitreddy said:

ardam avvaleda inka..daddy thana pravinyam antha mana mida chupisthunadu

chaadastamm...

[sanbk]

1 minute ago, mtkr said:

my ans: if multiple inserts and deletes - indexes hav 2 be modified n reorganized 

performance ane cheppa google lo choosi adu satisfy kaledu tarvatha telsukunna index fragmentation gurinchi index rebuild eppudu cheyyalo index reorganize eppudu cheyyalo paul randal gadiki dandam but inka doubt eh ah answer correct aah kaadaa anii

[MeetFriendz]

gp

[loveindia]

21 minutes ago, sanbk said:

eh columns ane specification em ledu oka table lo 100 columns unnayi oka column meeda clustered create chesi migatha 99 columns meeda nonclustered create cheyachaa ela chestharu chesthe em avuthadi nenu 16 column limit gurinchi cheppa inka emaina untadaa reason enduku cheyalemo apart from 16 limit and 900 bytes...

table mottam meeda index chestey adi malli heap ey avutundi man... 16 column limit is for unique index man, and its turn around is very simple noo man...

[k2s]

1 hour ago, mtkr said:

1) Index scan is looking all data pages from first to last... index seek is looking specific data pages...

scan preferred whn tbl is small..

seek preferred whn tbl is large...

orders table having no index on it
select * from orders --- table scan

orders table having clustered index on it
select * from orders --- index scan

orders table having clustered index on it
select * from orders where id = 2 --- index seek

https://blogs.msdn.microsoft.com/craigfr/2006/06/26/scans-vs-seeks/

 

2) create index index_name on table_name (column_name)

 

3) correlated sub query -  its a subquery wch uses values from outer query for execution 

(frm ggle)

A correlated subquery is one which uses values from the parent query. This kind of query tends to run row-by-row, once for each row returned by the outer query, and thus decreases SQL query performance. New developers are often caught structuring their queries in this way—because it’s usually the easy route.

Here’s an example of a correlated subquery:

SELECT c.Name, 
       c.City,
       (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName 
FROM Customer c
In particular, the problem is that the inner query (SELECT CompanyName…) is run for each row returned by the outer query (SELECT c.Name…). But why go over the Company again and again for every row processed by the outer query?

A more efficient performance tuning technique would be to refactor the correlated subquery as a join:

SELECT c.Name, 
       c.City, 
       co.CompanyName 
FROM Customer c 
    LEFT JOIN Company co
        ON c.CompanyID = co.CompanyID
In this case, we go over the Company table just once, at the start, and JOIN it with the Customer table. From then on, we can select the values we need (co.CompanyName) more efficiently.

 thnx bro... got it 

another QQ:

1) scan & seek syntax same undi. seek works only on cluster index e na ?

3) ala tables join chesthey adi just cache mem lo pettukoni query run chesthunda ? or else a new table temp created ?