Andrea Benedetti http://blogs.ugidotnet.org/ab/Default.aspx SQL Server and more... it-IT Andrea Benedetti Subtext Version 2.6.0.0 Andrea Benedetti http://blogs.ugidotnet.org/images/RSS2Image.gif http://blogs.ugidotnet.org/ab/Default.aspx 77 60 E' tempo di saluti... Varie http://blogs.ugidotnet.org/ab/archive/2007/06/12/81433.aspx <P><FONT face=Verdana size=2>Oggi è una giornata importante, almeno per un paio di motivi.</FONT></P> <P><FONT face=Verdana size=2>Il primo è il saluto che faccio alla Community di UgiDotNet per lo spazio che mi ha dedicato, gli amici ed i colleghi con cui ho condiviso il muro.</FONT></P> <P><FONT face=Verdana size=2><A title="" href="community.ugiss.org/blogs/dmauri" target="" name=""><FONT face=Verdana size=2>Davide</FONT></A>, dopo uno splendido lavoro, ha terminato la messa in linea di strumenti che possono chiamarsi tali per una <A title="" href="http://community.ugiss.org/" target="" name="">community</A>.</FONT></P> <P><FONT face=Verdana size=2>E' quindi con molto piacere che porto (sposto) il <A title="" href="http://community.ugiss.org/blogs/abenedetti/default.aspx" target="" name="">mio blog</A> all'interno del posto più naturale che possa avere: la community di <A title="" href="http://community.ugiss.org/blogs/abenedetti/default.aspx" target="" name="">UGISS</A>.</FONT></P> <P><FONT face=Verdana size=2>Il secondo motivo è la partenza della <A title="" href="http://www.sqlconference.it/" target="" name="">SQL Conference 2007</A>.</FONT></P> <P><FONT face=Verdana size=2>In questo momento, infatti, blog in tempo reale dalla prima sessione della conferenza.</FONT></P> <P><FONT face=Verdana size=2>Quindi il nuovo blog dovrebbe (ri)partire al meglio proprio con qualche post da Segrattle!</FONT></P> <P><FONT face=Verdana size=2><STRONG>Grazie UGI!</STRONG></FONT></P><!-- Powered by IMHO 1.3 (IT) Instant Blogger Copyright (c) 2005 A.Boschin - http://www.imhoproject.org --> <img src="http://blogs.ugidotnet.org/ab/aggbug/81433.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/06/12/81433.aspx Tue, 12 Jun 2007 11:27:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/06/12/81433.aspx#feedback 6 http://blogs.ugidotnet.org/ab/comments/commentRss/81433.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/81433.aspx [SQL] Tabella &quot;visite&quot; e recuperare tragitto fatto - Ovvero: fare distinct su localit&#224; visitate di seguito SQL Server http://blogs.ugidotnet.org/ab/archive/2007/05/31/79817.aspx <P><FONT face=Verdana size=2>Ok, il titolo è un pò strano, ma non sapevo come metterlo meglio ;-)</FONT></P> <P><FONT face=Verdana size=2>Ieri un cliente mi ha prospettato una domanda interessante...<BR>Supponiamo di avere una tabella [spostamenti] dove l'utente memorizza i clienti che visita, quindi la data (quando) e la citta (dove).</FONT></P> <P><FONT face=Verdana size=2>Supponiamo quindi che io, oggi, memorizzi visite come:<BR>cliente A - mattina - brescia<BR>cliente B - mattina - brescia<BR>cliente C - pomeriggio - milano<BR>cliente D - sera - brescia<BR>...</FONT></P> <P><FONT face=Verdana size=2>La domanda è: <EM> come faccio a visualizzare le città visitate in modo tale che se faccio più incontri nello stesso luogo la città mi appaia una volta sola?<BR></EM>Ovvero: non mi interessa quanti clienti ho visitato nella città "A", quanto avere sotto mano il percorso (tragitto) fatto...</FONT></P> <P><FONT face=Verdana size=2>Interessante... mi sono divertito.<BR>Ecco come risolverei:</FONT></P> <DIV style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: black 1px solid; PADDING-LEFT: 5px; PADDING-BOTTOM: 5px; BORDER-LEFT: black 1px solid; PADDING-TOP: 5px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: gainsboro"><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">USE&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">tempdb<BR>GO<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">create&nbsp;table&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">int&nbsp;primary&nbsp;key&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">identity</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">),<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">data&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">datetime</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">citta&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">varchar</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">30</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR>)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">go<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;9:00:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'brescia'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;10:00:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'brescia'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;10:30:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'brescia'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;12:00:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'milano'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;14:00:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'brescia'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;14:10:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'desenzano'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;14:30:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'brescia'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;15:00:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'brescia'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;16:00:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'brescia'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;17:00:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'verona'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'20070531&nbsp;19:00:00'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'brescia'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;Questa&nbsp;la&nbsp;mia&nbsp;tabella&nbsp;di&nbsp;spostamenti<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">*&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">order&nbsp;by&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">data<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">/*<BR>Risultato:<BR><BR>idRecord&nbsp;&nbsp;&nbsp;&nbsp;data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;citta<BR>-----------&nbsp;-----------------------&nbsp;------------------------------<BR>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;09:00:00.000&nbsp;brescia<BR>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;10:00:00.000&nbsp;brescia<BR>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;10:30:00.000&nbsp;brescia<BR>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;12:00:00.000&nbsp;milano<BR>5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;14:00:00.000&nbsp;brescia<BR>6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;14:10:00.000&nbsp;desenzano<BR>7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;14:30:00.000&nbsp;brescia<BR>8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;15:00:00.000&nbsp;brescia<BR>9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;16:00:00.000&nbsp;brescia<BR>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;17:00:00.000&nbsp;verona<BR>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;19:00:00.000&nbsp;brescia<BR><BR>(11&nbsp;row(s)&nbsp;affected)<BR>*/<BR><BR>--&nbsp;Questa&nbsp;la&nbsp;tabella&nbsp;con&nbsp;un&nbsp;check&nbsp;per&nbsp;verificare&nbsp;se&nbsp;sono&nbsp;stato&nbsp;più&nbsp;volte&nbsp;o&nbsp;meno<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">*&nbsp;,&nbsp;(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff00ff; FONT-FAMILY: Courier New">count</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;S2&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">where&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">S2</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">citta&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">S1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">citta&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">and&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">S2</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">S1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">+</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">as&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">N&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;S1<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">/*<BR>Risultato:<BR><BR>idRecord&nbsp;&nbsp;&nbsp;&nbsp;data&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;citta&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;N<BR>-----------&nbsp;-----------------------&nbsp;------------------------------&nbsp;-----------<BR>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;09:00:00.000&nbsp;brescia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<BR>2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;10:00:00.000&nbsp;brescia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<BR>3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;10:30:00.000&nbsp;brescia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<BR>4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;12:00:00.000&nbsp;milano&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<BR>5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;14:00:00.000&nbsp;brescia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<BR>6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;14:10:00.000&nbsp;desenzano&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<BR>7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;14:30:00.000&nbsp;brescia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<BR>8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;15:00:00.000&nbsp;brescia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1<BR>9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;16:00:00.000&nbsp;brescia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<BR>10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;17:00:00.000&nbsp;verona&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0<BR>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2007-05-31&nbsp;19:00:00.000&nbsp;brescia&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<BR><BR>(11&nbsp;row(s)&nbsp;affected)<BR>*/<BR><BR>--&nbsp;&nbsp;Questo&nbsp;il&nbsp;risultato&nbsp;finale<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">citta&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">*&nbsp;,&nbsp;(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff00ff; FONT-FAMILY: Courier New">count</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;S2&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">where&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">S2</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">citta&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">S1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">citta&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">and&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">S2</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">S1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">+</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">as&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">N&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti&nbsp;S1<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">T<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">where&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">N&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">0<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">order&nbsp;by&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">T</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">data<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">drop&nbsp;table&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">spostamenti<BR>go<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">/*<BR>Risultato:<BR><BR>citta<BR>------------------------------<BR>brescia<BR>milano<BR>brescia<BR>desenzano<BR>brescia<BR>verona<BR>brescia<BR><BR>(7&nbsp;row(s)&nbsp;affected)<BR>*/<BR></DIV></SPAN> <P><FONT face=Verdana size=2>Praticamente con la seconda query siamo in grado di avere le città che hanno la successiva identica con un valore = 1.<BR>In questo modo, filtrando per quel campo = 0, abbiamo solo le città che vogliamo visualizzare.</FONT></P> <P><FONT face=Verdana size=2>Diciamo che è un pò il "classico" problema del: <EM>"... voglio resettare un contatore al cambio di articolo / fornitore / cliente / ..."</EM></FONT></P> <P><FONT face=Verdana size=2>Commenti?</FONT></P><!-- Powered by IMHO 1.3 (IT) Instant Blogger Copyright (c) 2005 A.Boschin - http://www.imhoproject.org --> <img src="http://blogs.ugidotnet.org/ab/aggbug/79817.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/05/31/79817.aspx Thu, 31 May 2007 12:17:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/05/31/79817.aspx#feedback 7 http://blogs.ugidotnet.org/ab/comments/commentRss/79817.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/79817.aspx [Reporting] Colorare a righe alternate una matrice Reporting Services http://blogs.ugidotnet.org/ab/archive/2007/05/10/77893.aspx <P><FONT face=Verdana size=2>Per colorare a righe alternate un oggetto tabella è sufficiente utilizzare la funzione RowNumber scrivendo, ad esempio:<BR><EM>=iif(RowNumber(nothing) mod 2 = 0, "red", "green")</EM></FONT></P> <P><FONT face=Verdana size=2>Nelle matrici la RowNumber non può funzionare... quindi dobbiamo realizzarla "a mano".</FONT></P> <P><FONT face=Verdana size=2>Per colorare le righe quindi:</FONT></P> <P><FONT face=Verdana size=2>1) ci serve una colonna che, tramite la funzione RunningValue ed il CountDistinct possa farmi contare le righe in maniera univoca e quindi, tramite la classica "... mod 2 = 0 ..." distinguere le righe pari e le righe dispari.</FONT></P> <P><FONT face=Verdana size=2>Ad esempio, aggiungo al report una colonna in cui visualizzare (proprietà text):<BR><EM>= iif( RunningValue(Fields!XXXXXX.Value,CountDistinct,Nothing) mod 2 = 0, "#dedede", "#ededed")</EM></FONT></P> <P><FONT face=Verdana size=2>Dove XXXXX è il campo che mi da l'univocità di riga.</FONT></P><FONT face=Verdana size=2> <P>Se l'univocità di riga la ottengo da più colonne (supponete di fare avere colonne "cognome" e "nome"), il parametro di RunningValue deve essere la concatenazione delle colonne:</P> <P><EM>= iif( RunningValue( (Fields!cognome.Value &amp; Fields!nome.Value) ,CountDistinct,Nothing) mod 2 = 0,&nbsp; "red", "green")</EM></P> <P>2) rendiamo invisibile questa nuova colonna</P> <P>3) nella riga della matrice impostiamo la proprietà BackgroundColor come:</P> <P><EM>= ReportItems!YYYYYY.Value</EM></P> <P>Dove YYYYYY è il nome che abbiamo dato alla nuova cella che abbiamo creato tramite il punto 1</P> <P>N.B.: se la matrice ha attivi i subtotali e se il background di queste celle è imposato come trasparente, il colore sarà identico a quello dell'ultima riga dati precedente</FONT></P> <P><FONT face=Verdana size=2></FONT>&nbsp;</P><!-- Powered by IMHO 1.3 (IT) Instant Blogger Copyright (c) 2005 A.Boschin - http://www.imhoproject.org --> <img src="http://blogs.ugidotnet.org/ab/aggbug/77893.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/05/10/77893.aspx Thu, 10 May 2007 16:14:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/05/10/77893.aspx#feedback http://blogs.ugidotnet.org/ab/comments/commentRss/77893.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/77893.aspx [SQL] Katmai... SQL Server http://blogs.ugidotnet.org/ab/archive/2007/05/10/77857.aspx <P><FONT face=Verdana size=2>Sembra che a Redmond i motori siano parecchio caldi....</FONT></P> <P><FONT face=Verdana size=2>Dopo quello che abbiamo visto al summit MVP qualcosa comincia ad uscire.</FONT></P> <P><FONT face=Verdana size=2><FONT face=Verdana size=2><A title="" href="http://www.microsoft.com/presspass/press/2007/may07/05-09KatmaiPR.mspx" target="" name=""><FONT face=Verdana size=2><FONT face=Verdana size=2>Qui</FONT></FONT></A> </FONT>e <A title="" href="http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx" target="" name="">qui</A>...</FONT></P><!-- Powered by IMHO 1.3 (IT) Instant Blogger Copyright (c) 2005 A.Boschin - http://www.imhoproject.org --> <img src="http://blogs.ugidotnet.org/ab/aggbug/77857.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/05/10/77857.aspx Thu, 10 May 2007 10:34:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/05/10/77857.aspx#feedback 5 http://blogs.ugidotnet.org/ab/comments/commentRss/77857.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/77857.aspx [SQL] Query, condizioni negative e optimizer SQL Server http://blogs.ugidotnet.org/ab/archive/2007/04/28/76844.aspx <P><FONT face=Verdana size=2>Il Query Optimizer di SQL Server è un gioiellino (sicuramente migliorabile come tutte le cose).<BR>E' il componente in grado di esaminare le nostre query e le condizioni delle interrogazioni e di valutare la strada migliore da percorrere per arrivare al risultato richiesto.</FONT></P> <P><FONT face=Verdana size=2>Alcune volte però il Query Optimizer deve essere aiutato.<BR>Uno di questi casi riguarda tutte quelle condizioni in cui chiediamo la negazione di qualcosa, ovvero quando utilizziamo operatori come:<BR>&lt;&gt;, NOT EXISTS, NOT IN, NOT LIKE, ...</FONT></P> <P><FONT face=Verdana size=2>Perchè?<BR>Pechè SQL Server lavora meglio se gli chiediamo l'esistenza di una riga, e non la sua "non esistenza".</FONT></P> <P><FONT face=Verdana size=2>Se possibile, quindi, potrebbe essere conveniente trasformare la negazione in affermazione ;-)</FONT></P> <P><FONT face=Verdana size=2>Partire da una negazione simile a:<BR>... where P.idCategoria NOT IN (1,3,5,12,13)<BR>Per arrivare ad un'affermazione come:<BR>... where P.idCategoria IN (select idRecord from categorie where idRecord NOT IN (1,3,5,12,13))</FONT></P> <P><FONT face=Verdana size=2>La valutazione poi, come sempre, dipende dalla mole di dati che andremmo ad interrogare.<BR>Sicuramente tenete sempre presente (ed analizzate) statistiche e piani escuzione.<BR>Sono i nostri migliori amici!</FONT></P><FONT face=Verdana size=2> <P>Vediamo un esempio:</P> <DIV style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: black 1px solid; PADDING-LEFT: 5px; PADDING-BOTTOM: 5px; BORDER-LEFT: black 1px solid; PADDING-TOP: 5px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: gainsboro"><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;utilizzo&nbsp;il&nbsp;db&nbsp;temporaneo<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">use&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">tempdb<BR>go<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;creo&nbsp;una&nbsp;tabella&nbsp;categorie<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">create&nbsp;table&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">tinyint&nbsp;primary&nbsp;key&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">identity</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">),<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">codice&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">char</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">),<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">varchar</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">20</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR>)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">go<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;creo&nbsp;una&nbsp;tabella&nbsp;prodotti<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">create&nbsp;table&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">prodotti<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">int&nbsp;primary&nbsp;key&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">identity</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">),<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idCategoria&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">tinyint&nbsp;foreign&nbsp;key&nbsp;references&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">),<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">varchar</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">50</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR>)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">go<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;inserisco&nbsp;categorie<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;nocount&nbsp;on<BR>insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'A'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;A'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'B'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;B'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'C'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;C'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'D'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;D'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'E'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;E'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'F'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;F'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'G'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;G'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'H'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;H'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'I'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;I'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'L'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;L'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'M'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;M'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'N'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;N'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'O'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;O'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'P'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;P'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Q'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;Q'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'R'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;R'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'S'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;S'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'T'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;T'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'U'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;U'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'V'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;V'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Z'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'Categoria&nbsp;Z'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR><BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;Inserisco&nbsp;420.000&nbsp;righe<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">declare&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">int<BR>set&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">0<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">while&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">&lt;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">20000<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">begin<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">prodotti&nbsp;<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">C</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">P</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;C<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">cross&nbsp;join&nbsp;(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'P&nbsp;'&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">+&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff00ff; FONT-FAMILY: Courier New">convert</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">varchar</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">25</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">),&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff00ff; FONT-FAMILY: Courier New">getdate</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(),&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">121</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">as&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">p<BR><BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">+&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">end<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">go<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">create&nbsp;index&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idx_categoria&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">on&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">Prodotti&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idCategoria</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&gt;&nbsp;abilitare&nbsp;la&nbsp;visualizzazione&nbsp;del&nbsp;piano&nbsp;di&nbsp;esecuzione<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;statistics&nbsp;time&nbsp;on<BR>set&nbsp;statistics&nbsp;io&nbsp;on<BR>&nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff00ff; FONT-FAMILY: Courier New">count</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">C</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">),&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">C</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">prodotti&nbsp;P<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">join&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;C&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">on&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">P</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idCategoria&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">C</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">where&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">P</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idCategoria&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">NOT&nbsp;IN&nbsp;(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">3</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">5</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">12</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">13</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">group&nbsp;by&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">C</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;statistics&nbsp;io&nbsp;off<BR>set&nbsp;statistics&nbsp;time&nbsp;off<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">/*<BR>Table&nbsp;'prodotti'.&nbsp;Scan&nbsp;count&nbsp;21,&nbsp;logical&nbsp;reads&nbsp;655<BR>Table&nbsp;'categorie'.&nbsp;Scan&nbsp;count&nbsp;1,&nbsp;logical&nbsp;reads&nbsp;2<BR><BR>SQL&nbsp;Server&nbsp;Execution&nbsp;Times:&nbsp;&nbsp;&nbsp;CPU&nbsp;time&nbsp;=&nbsp;521&nbsp;ms,&nbsp;&nbsp;elapsed&nbsp;time&nbsp;=&nbsp;1765&nbsp;ms.<BR>*/<BR><BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;statistics&nbsp;time&nbsp;on<BR>set&nbsp;statistics&nbsp;io&nbsp;on<BR>&nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff00ff; FONT-FAMILY: Courier New">count</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">C</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">),&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">C</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">prodotti&nbsp;P<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">join&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;C&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">on&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">P</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idCategoria&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">C</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">where&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">P</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idCategoria&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">IN&nbsp;(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">where&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">NOT&nbsp;IN&nbsp;(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">3</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">5</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">12</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">13</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">))<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">group&nbsp;by&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">C</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">.</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">descrizione<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;statistics&nbsp;io&nbsp;off<BR>set&nbsp;statistics&nbsp;time&nbsp;off<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">/*<BR>Table&nbsp;'prodotti'.&nbsp;Scan&nbsp;count&nbsp;16,&nbsp;logical&nbsp;reads&nbsp;499<BR>Table&nbsp;'categorie'.&nbsp;Scan&nbsp;count&nbsp;1,&nbsp;logical&nbsp;reads&nbsp;34<BR><BR>SQL&nbsp;Server&nbsp;Execution&nbsp;Times:&nbsp;&nbsp;&nbsp;CPU&nbsp;time&nbsp;=&nbsp;271&nbsp;ms,&nbsp;&nbsp;elapsed&nbsp;time&nbsp;=&nbsp;1168&nbsp;ms.<BR>*/<BR><BR><BR>--&nbsp;faccio&nbsp;pulizia:<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">drop&nbsp;table&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">prodotti<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">drop&nbsp;table&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">categorie<BR>go<BR></DIV></SPAN></FONT><!-- Powered by IMHO 1.3 (IT) Instant Blogger Copyright (c) 2005 A.Boschin - http://www.imhoproject.org --> <img src="http://blogs.ugidotnet.org/ab/aggbug/76844.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/04/28/76844.aspx Sat, 28 Apr 2007 20:20:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/04/28/76844.aspx#feedback 3 http://blogs.ugidotnet.org/ab/comments/commentRss/76844.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/76844.aspx [SQL] Indici, selettivit&#224; e densit&#224; SQL Server http://blogs.ugidotnet.org/ab/archive/2007/04/27/76744.aspx <P><FONT face=Verdana size=2>Il tema dell'indicizzazione dei dati è un tema interessantissimo, molto vasto, molto richiesto e, spesso, poco conosciuto.</FONT></P> <P><FONT face=Verdana size=2>SQL Server utilizza, per definire il miglior piano di esecuzione per le nostre interrogazioni, delle <STRONG>statistiche</STRONG> in grado di fornirgli informazioni sulla <STRONG>selettività degli indici</STRONG>.<BR>A cosa serve la selettività? A capire se quel determinato indice è utile o no.</FONT></P> <P><FONT face=Verdana size=2>La selettività viene valutata tramite un <STRONG>valore di densità</STRONG> e, tramite questo, il query optimizer decide se prendere in considerazione o meno un indice.</FONT></P> <P><FONT face=Verdana size=2>Un'alta densità indica che l'indice è poco selettivo (una chiave primaria o una colonna UNIQUE hanno il massimo livello di densità) e quindi usabile.<BR>Una colonna che ha solo pochi valori (pensiamo ad una colonna bit), per questo motivo, ha una altissima selettività e quindi una piccola (piccolissima) utilità.</FONT></P> <P><FONT face=Verdana size=2>Come dire: sarebbe utile fare un indice sulla colonna sesso di una tabella anagrafica?</FONT></P> <P><FONT face=Verdana size=2>SQL Server fornisce uno strumento in grado di visualizzare la densità di un indice, il comando:</FONT></P> <P><FONT face=Verdana size=2><EM>DBCC Show_Statistics (miaTabella, mioIndice)</EM></FONT></P> <P><FONT face=Verdana size=2>Vediamo un esempio...</FONT></P><FONT face=Verdana size=2><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New"> <DIV style="BORDER-RIGHT: black 1px solid; PADDING-RIGHT: 5px; BORDER-TOP: black 1px solid; PADDING-LEFT: 5px; PADDING-BOTTOM: 5px; BORDER-LEFT: black 1px solid; PADDING-TOP: 5px; BORDER-BOTTOM: black 1px solid; BACKGROUND-COLOR: gainsboro"><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;utilizzo&nbsp;il&nbsp;tempdb&nbsp;per&nbsp;test<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">use&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">tempdb<BR>go<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;creo&nbsp;tabella&nbsp;di&nbsp;analisi<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">create&nbsp;table&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">test<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">int&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">identity</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">),<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">valore1&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">int</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">valore2&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">int</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">constraint&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">pk&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">primary&nbsp;key&nbsp;clustered&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idRecord</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR>)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">go<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;nocount&nbsp;on<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;inserisco&nbsp;5000&nbsp;righe&nbsp;di&nbsp;prova<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">declare&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">int<BR>set&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">0<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">while&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">&lt;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">5000<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">begin<BR>&nbsp;&nbsp;&nbsp;&nbsp;insert&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">test&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">values&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff00ff; FONT-FAMILY: Courier New">case&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">when&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">%</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">2&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">0</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">then&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">0&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">else&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">end</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR>&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">@i&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">+&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">end<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">go<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;creo&nbsp;un&nbsp;indice&nbsp;sulla&nbsp;prima&nbsp;colonna<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">create&nbsp;index&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idx_pocoSelettivo&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">on&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">test&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">valore1</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;creo&nbsp;un&nbsp;indice&nbsp;sulla&nbsp;seconda&nbsp;colonna<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">create&nbsp;index&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idx_moltoSelettivo&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">on&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">test&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">valore2</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">go<BR><BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;questa&nbsp;è&nbsp;la&nbsp;mia&nbsp;tabella<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">*&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">test<BR><BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;visualizzo&nbsp;le&nbsp;statistiche&nbsp;associato&nbsp;al&nbsp;primo&nbsp;indice&nbsp;(quello&nbsp;poco&nbsp;selettivo)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">DBCC&nbsp;Show_Statistics&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'test'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'idx_pocoSelettivo'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&nbsp;visualizzo&nbsp;le&nbsp;statistiche&nbsp;associato&nbsp;al&nbsp;secondo&nbsp;indice&nbsp;(quello&nbsp;molto&nbsp;selettivo)<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">DBCC&nbsp;Show_Statistics&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'test'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">,&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #ff0000; FONT-FAMILY: Courier New">'idx_moltoSelettivo'</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">)<BR><BR><BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">--&gt;&nbsp;Abilitare&nbsp;la&nbsp;visualizzazione&nbsp;del&nbsp;piano&nbsp;di&nbsp;esecuzione<BR><BR>--&nbsp;Eseguo&nbsp;una&nbsp;query&nbsp;sulla&nbsp;colonna&nbsp;valore1<BR>/*<BR>Il&nbsp;piano&nbsp;di&nbsp;esecuzione&nbsp;mi&nbsp;mostra&nbsp;un&nbsp;clusterd&nbsp;index&nbsp;scan&nbsp;(l'indice,&nbsp;talmente&nbsp;poco&nbsp;selettivo,&nbsp;non&nbsp;gli&nbsp;serve&nbsp;a&nbsp;nulla!)<BR>*/<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;statistics&nbsp;io&nbsp;on<BR>select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">*&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">test&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">where&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">valore1&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;statistics&nbsp;io&nbsp;off<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">/*<BR>Info&nbsp;statistiche&nbsp;di&nbsp;I/O:<BR>Table&nbsp;'test'.&nbsp;Scan&nbsp;count&nbsp;1,&nbsp;logical&nbsp;reads&nbsp;15<BR>*/<BR><BR><BR>--&nbsp;Eseguo&nbsp;la&nbsp;stessa&nbsp;query&nbsp;con&nbsp;la&nbsp;stessa&nbsp;clausola&nbsp;where<BR>--&nbsp;sulla&nbsp;stessa&nbsp;colonna&nbsp;valore1&nbsp;FORZANDO&nbsp;l'utilizzo&nbsp;<BR>--&nbsp;del&nbsp;mio&nbsp;indice&nbsp;poco&nbsp;selettivo:<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;statistics&nbsp;io&nbsp;on<BR>select&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">*&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">from&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">test&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">with&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">index&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">(</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">idx_pocoSelettivo</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">))&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">where&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">valore1&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #808080; FONT-FAMILY: Courier New">=&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">1<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">set&nbsp;statistics&nbsp;io&nbsp;off<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #008000; FONT-FAMILY: Courier New">/*<BR>Info&nbsp;statistiche&nbsp;di&nbsp;I/O:<BR>Table&nbsp;'test'.&nbsp;Scan&nbsp;count&nbsp;1,&nbsp;logical&nbsp;reads&nbsp;5007&nbsp;(!!!)<BR>*/<BR><BR><BR>--&nbsp;faccio&nbsp;pulizia<BR></SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #0000ff; FONT-FAMILY: Courier New">drop&nbsp;table&nbsp;</SPAN><SPAN style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: Courier New">test<BR>go<BR></DIV></SPAN></SPAN></FONT> <P><FONT face=Verdana size=2>Anche <A title="" href="https://mvp.support.microsoft.com/profile=E8E5E4C3-F736-43EB-88BD-647CC5FBFB48" target="" name="">Luca</A>,&nbsp;MVP di SQL Server oltre che grande amico, ha scritto un altro post sull'argomento (che consiglio di leggere): <A title="" href="http://blogs.aspitalia.com/lucabianchi/post2027/Misurare-Utilita-Indice.aspx" target="" name="">Misurare l'utilità di un indice</A>.</FONT></P><!-- Powered by IMHO 1.3 (IT) Instant Blogger Copyright (c) 2005 A.Boschin - http://www.imhoproject.org --> <img src="http://blogs.ugidotnet.org/ab/aggbug/76744.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/04/27/76744.aspx Fri, 27 Apr 2007 12:42:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/04/27/76744.aspx#feedback http://blogs.ugidotnet.org/ab/comments/commentRss/76744.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/76744.aspx [SQL] Seek sempre meglio di Scan? SQL Server http://blogs.ugidotnet.org/ab/archive/2007/04/26/76625.aspx <P><FONT face=Verdana size=2>Ho sentito spesso dire: "<EM>Per recuperare dati, le operazioni di seek sono sempre più veloci delle operazioni di scan</EM>".<BR>Questo è sempre vero? Fate attenzione: no.</FONT></P> <P><FONT face=Verdana size=2>Perchè?<BR>Perchè se la mia interrogazione riguarda una consitente porzione di dati, diciamo una <U>percentuale rilevante</U>, le operazioni di scan sono nettamente da preferire (e ben più veloci) di operazioni di seek che, invece, sono il massimo nel momento in cui devo recuperare poche ed isolate righe.</FONT></P> <P><FONT face=Verdana size=2></FONT>&nbsp;</P><!-- Powered by IMHO 1.3 (IT) Instant Blogger Copyright (c) 2005 A.Boschin - http://www.imhoproject.org --> <img src="http://blogs.ugidotnet.org/ab/aggbug/76625.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/04/26/76625.aspx Thu, 26 Apr 2007 15:58:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/04/26/76625.aspx#feedback http://blogs.ugidotnet.org/ab/comments/commentRss/76625.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/76625.aspx [SQL] Database mirroring, wizard e certificati digitali SQL Server http://blogs.ugidotnet.org/ab/archive/2007/04/26/76553.aspx <P><FONT face=Verdana size=2>Il mirroring di database è una bellissima (nuova) funzionalità (qualcuno la chiama "<EM>il cluster dei poveri</EM>" :-)).<BR>SQL Server 2005 fornisce un wizard per poter configurare correttamente la macchina Principal, il Mirror e, se servisse, il Witness (che può anche essere inserito in un secondo momento).</FONT></P> <P><FONT face=Verdana size=2>Se qualcuno è passato dal newsgroup microsoft.public.it.sql avrà forse letto del mio "amore" per queste procedure guidate che cerco immancabilmente di evitare...<BR>Ecco un altro motivo...</FONT></P> <P><FONT face=Verdana size=2>Il wizard del mirroring parte dal presupposto (e non vi segnala nulla in caso contrario) che tutte le istanze stiano girando sotto lo stesso domain user account.</FONT></P> <P><FONT face=Verdana size=2>Se così non fosse l'utilizzo di <STRONG>ENDPOINT</STRONG> con certificato digitale diventa indispensabile!</FONT></P> <P><FONT face=Verdana size=2>Quindi, diventa indispensabile configurare il mirroring via script T-SQL (Vedere l'istruzione <STRONG>CREATE CERTIFICATE</STRONG> e, quindi, <STRONG>CREATE ENDPOINT</STRONG>).</FONT></P> <P><FONT face=Verdana size=2>Personalmente preferisco comunque <U>configurare sempre tramite script</U>... so cosa faccio, so come lo faccio, so perchè lo faccio ;-)</FONT></P> <P><FONT face=Verdana size=2></FONT>&nbsp;</P><!-- Powered by IMHO 1.3 (IT) Instant Blogger Copyright (c) 2005 A.Boschin - http://www.imhoproject.org --> <img src="http://blogs.ugidotnet.org/ab/aggbug/76553.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/04/26/76553.aspx Thu, 26 Apr 2007 00:45:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/04/26/76553.aspx#feedback 5 http://blogs.ugidotnet.org/ab/comments/commentRss/76553.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/76553.aspx [SQL] Posso disabilitare un indice clustered? SQL Server http://blogs.ugidotnet.org/ab/archive/2007/04/20/76098.aspx <P>Un'interessante novit&#224; di SQL Server 2005 &#232; quella di poter <STRONG>disabilitare</STRONG> un indice.</P> <P>Pu&#242; essere utile nei casi in cui si voglia mettere offline un indice mantenendo i metadati all'interno del database.</P> <P>La domanda del post &#232;: posso&nbsp;disabilitare un indice clustered?</P> <P>La risposta: si! Ma&nbsp;<FONT color=red><STRONG>a-t-t-e-n-z-i-o-n-e</STRONG></FONT>: equivale a disabilitare la tabella stessa e quindi a renderla irraggiungibile!</P> <P><FONT color=#008000 size=2><BR>-- eseguo test sul db temp</FONT><FONT color=#0000ff size=2> <BR>use</FONT><FONT size=2> tempdb <BR>GO</FONT><FONT color=#008000 size=2> <BR>-- creazione di una tabella di test</FONT><FONT color=#0000ff size=2> <BR>create</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>table</FONT><FONT size=2> test</FONT><FONT color=#808080 size=2> <BR>(</FONT><FONT size=2> <BR>idRecord </FONT><FONT color=#0000ff size=2>int</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>identity</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>1</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>1</FONT><FONT color=#808080 size=2>),</FONT><FONT size=2> <BR>valore </FONT><FONT color=#0000ff size=2>varchar</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>20</FONT><FONT color=#808080 size=2>),</FONT><FONT color=#0000ff size=2> <BR>CONSTRAINT</FONT><FONT size=2> PK_test </FONT><FONT color=#0000ff size=2>PRIMARY</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>KEY</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>NONCLUSTERED</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>idRecord</FONT><FONT color=#808080 size=2>) <BR>)</FONT><FONT size=2> <BR>GO</FONT><FONT color=#008000 size=2> <BR>-- creazione indice clustered sulla tabella</FONT><FONT color=#0000ff size=2> <BR>create</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>clustered</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>index</FONT><FONT size=2> idx_valore </FONT><FONT color=#0000ff size=2>on</FONT><FONT size=2> test</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>valore</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> <BR>GO</FONT><FONT color=#008000 size=2> <BR>-- inserimento dati di prova</FONT><FONT color=#0000ff size=2> <BR>insert</FONT><FONT size=2> test </FONT><FONT color=#0000ff size=2>values</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'a'</FONT><FONT color=#808080 size=2>)</FONT><FONT color=#0000ff size=2> <BR>insert</FONT><FONT size=2> test </FONT><FONT color=#0000ff size=2>values</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'b'</FONT><FONT color=#808080 size=2>)</FONT><FONT color=#0000ff size=2> <BR>insert</FONT><FONT size=2> test </FONT><FONT color=#0000ff size=2>values</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'c'</FONT><FONT color=#808080 size=2>)</FONT><FONT color=#0000ff size=2> <BR>insert</FONT><FONT size=2> test </FONT><FONT color=#0000ff size=2>values</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>(</FONT><FONT color=#ff0000 size=2>'d'</FONT><FONT color=#808080 size=2>)</FONT><FONT color=#008000 size=2> <BR>-- vedo la tabella</FONT><FONT color=#0000ff size=2> <BR>select</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>*</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>from</FONT><FONT size=2> test</FONT><FONT color=#008000 size=2> <BR>-- ATTENZIONE: disabilito l'indice clustered! <BR>-- (attenzione al warning che ci da SQL Server)</FONT><FONT color=#0000ff size=2> <BR>ALTER</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>INDEX</FONT><FONT size=2> idx_valore </FONT><FONT color=#0000ff size=2>ON</FONT><FONT size=2> test </FONT><FONT color=#0000ff size=2>DISABLE</FONT><FONT size=2> <BR>GO</FONT><FONT color=#008000 size=2> <BR>-- vedo la tabella (NON funziona!!!)</FONT><FONT color=#0000ff size=2> <BR>select</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>*</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>from</FONT><FONT size=2> test</FONT><FONT color=#008000 size=2> <BR>/* <BR>Questo l'errore: <BR>Msg 8655, Level 16, State 1, Line 3 <BR>The query processor is unable to produce a plan because the index 'idx_valore' on table or view 'test' is disabled. <BR>*/ <BR>-- ricostruisco l'indice (il contrario di DISABLE &#232; REBUILD!)</FONT><FONT color=#0000ff size=2> <BR>ALTER</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>INDEX</FONT><FONT size=2> idx_valore </FONT><FONT color=#0000ff size=2>ON</FONT><FONT size=2> test </FONT><FONT color=#0000ff size=2>REBUILD</FONT><FONT size=2> <BR>GO</FONT><FONT color=#008000 size=2> <BR>-- vedo la tabella (ok)</FONT><FONT color=#0000ff size=2> <BR>select</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>*</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>from</FONT><FONT size=2> test</FONT><FONT color=#008000 size=2> <BR>-- faccio pulizia</FONT><FONT color=#0000ff size=2> <BR>drop</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>table</FONT><FONT size=2> test <BR>GO</FONT></P><img src="http://blogs.ugidotnet.org/ab/aggbug/76098.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/04/20/76098.aspx Fri, 20 Apr 2007 18:32:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/04/20/76098.aspx#feedback 2 http://blogs.ugidotnet.org/ab/comments/commentRss/76098.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/76098.aspx [Reporting] Report ed informazioni di esecuzione Reporting Services http://blogs.ugidotnet.org/ab/archive/2007/04/20/76096.aspx <P><FONT face=Verdana size=2>Spesso non si ricorda che un report, con qualsiasi strumento esso venga costruito, rappresenta dati in un particolare e preciso momento.</FONT></P> <P><FONT face=Verdana size=2>Ovvero uno stato che può, eventualmente, cambiare un attimo dopo.</FONT></P> <P><FONT face=Verdana size=2>Per questo motivo penso sia importante fornire sempre informazioni circa la data di escuzione e l'utente che ha eseguito il report.</FONT></P> <P><FONT face=Verdana size=2>Personalmente risolvo questa esigenza grazie al footer del report, alcune variabili globali e tre oggetti textBox che valorizzo così:</FONT></P> <P><FONT face=Verdana size=2>= "Data esecuzione: " &amp; Globals!ExecutionTime.ToLongDateString &amp; " : " &amp; Globals!ExecutionTime.ToLongTimeString<BR>= "Utente: " &amp; User!UserID<BR>= "Pag. " &amp; Globals!PageNumber &amp; " di " &amp; Globals!TotalPages</FONT></P> <P><FONT face=Verdana size=2>... oltre questo imposto sempre il Language del report in maniera opportuna (così che le date vengano formattate nelle corrette impostazioni internazionali) ;-)</FONT></P><!-- Powered by IMHO 1.3 (IT) Instant Blogger Copyright (c) 2005 A.Boschin - http://www.imhoproject.org --> <img src="http://blogs.ugidotnet.org/ab/aggbug/76096.aspx" width="1" height="1" /> Andrea Benedetti http://blogs.ugidotnet.org/ab/archive/2007/04/20/76096.aspx Fri, 20 Apr 2007 18:00:00 GMT http://blogs.ugidotnet.org/ab/archive/2007/04/20/76096.aspx#feedback 6 http://blogs.ugidotnet.org/ab/comments/commentRss/76096.aspx http://blogs.ugidotnet.org/ab/services/trackbacks/76096.aspx