T Sql Moving Average Berechnung

Ich muss eine Rollsumme über einen Zeitraum berechnen. Veranschaulichen Sie die AdventureWorks-Beispieldatenbank. Würde die folgende hypothetische Syntax genau das tun, was ich brauche: Leider kann die RANGE-Fensterrahmen-Ausdehnung derzeit nicht zulassen, ein Intervall in SQL Server. Ich weiß, ich kann eine Lösung mit einer Unterabfrage und eine reguläre (non-window) Aggregat schreiben: Angesichts der folgenden Index: Der Ausführungsplan ist: Während nicht schrecklich ineffizient, scheint es, wie es sollte möglich sein, diese Abfrage mit nur Fenster Aggregat auszudrücken Und analytische Funktionen, die in SQL Server 2012, 2014 oder 2016 (bisher) unterstützt werden. Zur Klarheit suche ich nach einer Lösung, die einen einzigen Durchlauf über die Daten durchführt. In T-SQL bedeutet dies, dass die OVER-Klausel die Arbeit erledigen wird, und der Ausführungsplan wird Fenster-Spools und Window Aggregate. Alle Sprachelemente, die die OVER-Klausel verwenden, sind Fairplay. Eine SQLCLR-Lösung ist akzeptabel, vorausgesetzt, es werden korrekte Ergebnisse erzielt. Für T-SQL-Lösungen gilt: Je weniger Hashes, Sorts und Window SpoolsAggregates im Ausführungsplan, desto besser. Fühlen Sie sich frei, Indizes hinzuzufügen, aber separate Strukturen sind nicht erlaubt (so dass keine vorberechneten Tabellen synchron mit Triggern gehalten werden, zum Beispiel). Referenztabellen sind erlaubt (Tabellen von Zahlen, Daten usw.) Idealerweise werden Lösungen genau die gleichen Ergebnisse in der gleichen Reihenfolge wie die Unterabfrage-Version oben produzieren, aber irgendetwas stimmt auch korrekt ist auch akzeptabel. Leistung ist immer eine Überlegung, so sollten Lösungen zumindest vernünftigerweise effizient sein. Dedizierter Chatroom: Ich habe einen öffentlichen Chatraum für Diskussionen zu dieser Frage und seinen Antworten erstellt. Jeder Benutzer mit mindestens 20 Rufpunkten kann direkt teilnehmen. Bitte ping mich in einem Kommentar unten, wenn Sie weniger als 20 Rep haben und möchte teilnehmen. Viele Grüße, Paul Ich habe ein paar verschiedene Ansätze, eine in T-SQL und eine in CLR. Der T-SQL-Ansatz kann folgendermaßen zusammengefasst werden: Nehmen Sie das Produktproduktprodukt in die Produktdatenbanken ein Merge in die beobachteten Verkaufsdaten Aggregieren Sie die Daten auf die Produktdatenebene Berechnen Sie Rollsummen in den letzten 45 Tagen auf der Grundlage dieser Aggregatdaten (die alle enthält Fehlende Tage ausgefüllt) Filtern Sie diese Ergebnisse nur zu den Produktdatepaarungen, die einen oder mehrere Verträge mit SET STATISTICS IO ON hatten. Dieser Ansatz berichtet Tabelle TransactionHistory. Scanzahl 1, logisch liest 484. was den einzelnen Durchgang über die Tabelle bestätigt. Als Referenz verwendet die ursprüngliche Schleifensuchabfrage Tabelle TransactionHistory. Scanzahl 113444, logisch liest 438366. Wie von SET STATISTICS TIME ON berichtet. Die CPU-Zeit ist 514ms. Dies ist für die ursprüngliche Abfrage günstiger als 2231 ms. Die CLR-Zusammenfassung kann wie folgt zusammengefasst werden: Lesen der Daten in den Speicher, sortiert nach Produkt und Datum Während der Verarbeitung jeder Transaktion, fügen Sie eine laufende Summe der Kosten hinzu. Wenn eine Transaktion ein anderes Produkt als die vorherige Transaktion ist, setzen Sie die laufende Summe auf 0 zurück. Pflegen Sie einen Zeiger auf die erste Transaktion, die das gleiche (Produkt, Datum) wie die aktuelle Transaktion hat. Wenn die letzte Transaktion mit dem (Produkt, Datum) angetroffen wird, berechnen Sie den Rollsumme für diese Transaktion und wenden Sie sie auf alle Transaktionen mit demselben an (Produkt, Datum). Rückgabe aller Ergebnisse an den Benutzer Verwenden von SET STATISTICS IO ON. Diese Vorgehensweise berichtet, dass keine logische IO aufgetreten ist Wow, eine perfekte Lösung (Eigentlich scheint es, dass SET STATISTICS IO nicht berichtet, IO in CLR entstanden. Aber aus dem Code ist es leicht zu sehen, dass genau ein Scan der Tabelle gemacht wird Und ruft die Daten in der Reihenfolge durch den Index Paul vorgeschlagen. Die CPU-Zeit ist jetzt 187ms. So berichtet von SET STATISTICS TIME ON. So ist dies eine ziemlich Verbesserung gegenüber dem T-SQL-Ansatz. Zwar ist die gesamte verstrichene Zeit der beiden Ansätze Sehr ähnlich bei etwa einer halben Sekunde. Allerdings hat die CLR-basierte Ansatz muss 113K Zeilen an die Konsole (vs. nur 52K für die T-SQL-Ansatz, dass Gruppen nach productdate), so dass deshalb Ive konzentrierte sich auf CPU-Zeit statt Ein weiterer großer Vorteil dieses Ansatzes ist, dass es genau die gleichen Ergebnisse wie die ursprüngliche Loopseek-Ansatz, einschließlich einer Zeile für jede Transaktion auch in Fällen, in denen ein Produkt mehrmals verkauft wird am selben Tag (AdventureWorks, ich speziell verglichen Zeile Ergebnisse und bestätigten, dass sie sich mit der ursprünglichen Pauls-Abfrage verbinden.) Ein Nachteil dieses Ansatzes, zumindest in seiner gegenwärtigen Form, ist, dass er alle Daten im Speicher liest. Jedoch benötigt der Algorithmus, der nur streng entworfen wurde, den aktuellen Fensterrahmen zu einem beliebigen Zeitpunkt im Speicher und könnte aktualisiert werden, um für Datensätze zu arbeiten, die Speicher überschreiten. Paul hat diesen Punkt in seiner Antwort illustriert, indem er eine Implementierung dieses Algorithmus erzeugt, der nur das Schiebefenster speichert. Dies kommt auf Kosten der Erteilung höherer Berechtigungen für die CLR-Baugruppe, wäre aber auf jeden Fall wert, diese Lösung bis zu beliebig großen Datensätzen zu skalieren. T-SQL - ein Scan, gruppiert nach Datum Der Ausführungsplan Aus dem Ausführungsplan sehen wir, dass der von Paul vorgeschlagene Originalindex ausreicht, um einen einzelnen geordneten Scan von Production. TransactionHistory durchzuführen. Verwenden einer Zusammenführungsverknüpfung, um die Transaktionshistorie mit jeder möglichen Produktdatenkombination zu kombinieren. Es gibt einige wesentliche Annahmen, die in diesem Ansatz gebacken werden. Ich vermute, es wird bis zu Paul zu entscheiden, ob sie akzeptabel sind :) Ich benutze die Production. Product Tabelle. Diese Tabelle ist frei verfügbar auf AdventureWorks2012 und die Beziehung wird durch einen Fremdschlüssel aus Production. TransactionHistory erzwungen. So dass ich dies als faires Spiel interpretiert. Diese Vorgehensweise basiert auf der Tatsache, dass Transaktionen keine Zeitkomponente auf AdventureWorks2012 haben, wenn sie das getan haben, die Erzeugung der vollständigen Menge von Produktdate-Kombinationen wäre nicht mehr möglich, ohne zuerst einen Pass über die Transaktionshistorie. Ich produziere ein Rowset, das nur eine Zeile pro Produktdatum-Paar enthält. Ich denke, dass dies wohl stimmt und in vielen Fällen ein wünschenswerteres Ergebnis der Rückkehr ist. Für jedes productdate habe ich eine Spalte NumOrders hinzugefügt, um anzugeben, wie viele Verkäufe aufgetreten sind. Sehen Sie im folgenden Screenshot einen Vergleich der Ergebnisse der ursprünglichen Abfrage gegenüber der vorgeschlagenen Abfrage in Fällen, in denen ein Produkt mehrmals am selben Tag verkauft wurde (zB 319 2007-09-05 00: 00: 00.000) CLR - ein Scan , Full ungrouped result set Die Hauptfunktion body Es gibt nicht eine Tonne zu sehen, hier der Hauptteil der Funktion deklariert die Eingaben (die entsprechen die entsprechende SQL-Funktion), richtet eine SQL-Verbindung und öffnet den SQLReader. Ive getrennt die Hauptlogik, so dass es einfacher zu konzentrieren: Die folgende Logik könnte inline geschrieben werden, aber es ist ein wenig leichter zu lesen, wenn sie in ihre eigenen Methoden aufgeteilt werden. Binden alles zusammen in SQL Alles bis zu diesem Punkt wurde in C, so können sehen, die eigentliche SQL beteiligt. (Alternativ können Sie dieses Deployment-Skript verwenden, um die Assembly direkt aus den Bits meiner Assembly zu erstellen, anstatt sie selbst zu kompilieren.) Der CLR-Ansatz bietet viel mehr Flexibilität, um den Algorithmus zu optimieren, und er könnte von einem Experten noch weiter optimiert werden In C. Allerdings gibt es auch Nachteile der CLR-Strategie. Ein paar Dinge im Auge zu behalten: Diese CLR-Ansatz hält eine Kopie des Datensatzes im Speicher. Es ist möglich, einen Streaming-Ansatz verwenden, aber ich begegnete Anfang Schwierigkeiten und festgestellt, dass es ein hervorragendes Connect Problem beschwert, dass Änderungen in SQL 2008 machen es schwieriger, diese Art von Ansatz zu verwenden. Es ist immer noch möglich (wie Paul zeigt), sondern erfordert eine höhere Ebene der Berechtigungen, indem Sie die Datenbank als TRUSTWORTHY und die Gewährung EXTERNALACCESS an die CLR-Montage. So gibt es einige Mühe und potenzielle Sicherheit Implikation, aber die Auszahlung ist ein Streaming-Ansatz, der besser skalieren kann, um viel größere Datenmengen als die auf AdventureWorks. CLR kann für einige DBAs weniger zugänglich sein, so dass eine solche Funktion mehr von einer Black Box, die nicht so transparent ist, nicht so leicht modifiziert, nicht so leicht implementiert, und vielleicht nicht so leicht zu debuggen. Dies ist ein ziemlich großer Nachteil im Vergleich zu einem T-SQL-Ansatz. Bonus: T-SQL 2 - die praktische Ansatz-ID tatsächlich verwenden Nach dem Versuch, über das Problem kreativ für eine Weile denken, dachte ich auch post die ziemlich einfache, praktische Art und Weise, die ich wahrscheinlich wählen würde, um dieses Problem anzugehen, wenn es kam in Meine tägliche Arbeit. Es macht Gebrauch von SQL 2012-Fenster-Funktionalität, aber nicht in Art der bahnbrechenden Art und Weise, dass die Frage erhofft wurde: Dies ergibt tatsächlich eine ziemlich einfache Gesamtabfrage-Plan, auch wenn man auf die beiden beiden relevanten Abfragepläne zusammen: Ein paar Gründe Ich mag diesen Ansatz: Es liefert die vollständige Ergebnismenge angefordert in der Problem-Anweisung (im Gegensatz zu den meisten anderen T-SQL-Lösungen, die eine gruppierte Version der Ergebnisse zurückgibt). Es ist leicht zu erklären, zu verstehen und zu debuggen Ich werde nicht wiederkommen ein Jahr später und frage mich, wie die Heck Ich kann eine kleine Änderung ohne ruinieren die Korrektheit oder Leistung Es läuft in etwa 900ms auf dem bereitgestellten Datensatz, anstatt die 2700ms von Die ursprüngliche Loop-seek Wenn die Daten viel dichter waren (mehr Transaktionen pro Tag), wächst die Rechenkomplexität nicht quadratisch mit der Anzahl der Transaktionen im Schiebefenster (wie es für die ursprüngliche Abfrage) Ich denke, das adressiert einen Teil von Pauls Sorge über wollte mehrere Scans vermeiden Es ergibt sich im Wesentlichen keine Tempdb IO in den letzten Updates von SQL 2012 aufgrund neuer tempdb faul schreiben Funktionalität Für sehr große Datensätze ist es trivial, die Arbeit in separate Chargen für jedes Produkt, wenn Speicherdruck aufgeteilt wurden Um ein Anliegen zu werden Ein paar potenzielle Einschränkungen: Während es technisch scannen Production. TransactionHistory nur einmal, seine nicht wirklich ein One-Scan-Ansatz, weil die Temp-Tabelle von ähnlicher Größe und müssen zusätzliche Logik IO auf dieser Tabelle als gut durchzuführen. Allerdings sehe ich dies nicht als zu unterschiedlich von einem Arbeitstisch, dass wir mehr manuelle Kontrolle haben, da wir seine genaue Struktur definiert haben Abhängig von Ihrer Umgebung könnte die Verwendung von tempdb als positiv angesehen werden (zB auf einem separaten Satz von SSD-Laufwerke) oder eine negative (hohe Parallelität auf dem Server, viele tempdb contention bereits) beantwortet September 8 15 am 15:41 Dies ist eine lange Antwort, so dass ich beschlossen, eine Zusammenfassung hier hinzufügen. Zuerst präsentiere ich eine Lösung, die genau das gleiche Ergebnis in der gleichen Reihenfolge wie in der Frage erzeugt. Es scannt die Haupttabelle 3 mal: um eine Liste der ProductIDs mit dem Datumsbereich für jedes Produkt zu erhalten, um die Kosten für jeden Tag zusammenzufassen (weil es mehrere Transaktionen mit demselben Datum gibt), um das Ergebnis mit den ursprünglichen Zeilen zu verbinden. Als nächstes vergleiche ich zwei Ansätze, die die Aufgabe zu vereinfachen und zu vermeiden, einen letzten Scan der Haupttabelle. Ihr Ergebnis ist eine tägliche Zusammenfassung, d. h. wenn mehrere Transaktionen auf einem Produkt das gleiche Datum haben, werden sie in eine Zeile gerollt. Mein Ansatz vom vorherigen Schritt scannt die Tabelle zweimal. Annäherung durch Geoff Patterson scannt die Tabelle einmal, weil er externes Wissen über die Strecke der Daten und der Liste der Produkte verwendet. Endlich stelle ich eine Single-Pass-Lösung, die wieder eine tägliche Zusammenfassung, aber es doesnt erfordern externe Kenntnisse über die Bandbreite der Daten oder die Liste der ProductIDs. Ich werde AdventureWorks2014-Datenbank und SQL Server Express 2014 verwenden. Änderungen an der ursprünglichen Datenbank: Geänderter Typ von Production. TransactionHistory. TransactionDate von datetime auf Datum. Die Zeitkomponente war ohnehin Null. Added calendar table dbo. Calendar Added Index to Production. TransactionHistory MSDN-Artikel über OVER-Klausel hat einen Link zu einem hervorragenden Blog-Post über Fenster-Funktionen von Itzik Ben-Gan. In diesem Beitrag erklärt er, wie OVER funktioniert, der Unterschied zwischen ROWS und RANGE Optionen und erwähnt dieses Problem der Berechnung einer rollenden Summe über einen Zeitraum. Er erwähnt, dass die aktuelle Version von SQL Server nicht implementieren RANGE in vollem Umfang und implementiert keine zeitlichen Intervall-Datentypen. Seine Erklärung für den Unterschied zwischen ROWS und RANGE gab mir eine Idee. Daten ohne Lücken und Duplikate Wenn die TransactionHistory-Tabelle Daten ohne Lücken und ohne Duplikate enthält, würde die folgende Abfrage korrekte Ergebnisse liefern: In der Tat würde ein Fenster mit 45 Zeilen genau 45 Tage umfassen. Termine mit Lücken ohne Duplikate Leider haben unsere Daten Termine. Um dieses Problem zu lösen, können wir eine Kalendertabelle verwenden, um einen Satz von Daten ohne Lücken zu erzeugen, und dann LEFT JOIN Originaldaten zu diesem Set zu verwenden und dieselbe Abfrage mit ROWS BETWEEN 45 PRECEDING und CURRENT ROW zu verwenden. Dies würde nur dann zu korrekten Ergebnissen führen, wenn sich Daten nicht wiederholen (innerhalb der gleichen ProductID). Termine mit Lücken mit Duplikaten Leider haben unsere Daten sowohl Lücken in Daten und Termine können innerhalb der gleichen ProductID wiederholen. Zur Lösung dieses Problems können wir GROUP Originaldaten von ProductID, TransactionDate, um eine Reihe von Daten ohne Duplikate zu generieren. Dann verwenden Sie Kalender-Tabelle, um eine Reihe von Daten ohne Lücken zu generieren. Dann können wir die Abfrage mit ROWS BETWEEN 45 PRECEDING und CURRENT ROW verwenden, um rollende SUM zu berechnen. Dies würde zu korrekten Ergebnissen führen. Siehe Kommentare in der folgenden Abfrage. Ich bestätigte, dass diese Abfrage dieselben Ergebnisse wie der Ansatz aus der Frage erzeugt, die Unterabfrage verwendet. Erste Abfrage verwendet Unterabfrage, Sekunde - dieser Ansatz. Sie können sehen, dass Dauer und Anzahl der Lesungen ist viel weniger in diesem Ansatz. Die Mehrheit der geschätzten Kosten in diesem Ansatz ist die endgültige ORDER BY. siehe unten. Subquery-Ansatz hat einen einfachen Plan mit verschachtelten Schleifen und O (nn) Komplexität. Plan für diesen Ansatz scannt TransactionHistory mehrere Male, aber es gibt keine Schleifen. Wie Sie sehen können mehr als 70 der geschätzten Kosten ist die Sort für die endgültige ORDER BY. Top Ergebnis - Unterabfrage. Unten - ÜBER. Vermeiden von Extra-Scans Der letzte Index-Scan, Merge Join und Sort in dem Plan oben wird durch die endgültige INNER JOIN mit der ursprünglichen Tabelle verursacht, um das endgültige Ergebnis genau das gleiche wie ein langsamer Ansatz mit Unterabfrage zu machen. Die Anzahl der zurückgegebenen Zeilen ist dieselbe wie in der TransactionHistory-Tabelle. Es gibt Zeilen in TransactionHistory, wenn mehrere Transaktionen am selben Tag für dasselbe Produkt auftraten. Wenn es OK ist, nur tägliche Zusammenfassung im Ergebnis anzuzeigen, dann kann diese letzte JOIN entfernt werden und die Abfrage wird ein bisschen einfacher und ein bisschen schneller. Der letzte Index-Scan, der Zusammenführungs-Join und die Sortierung aus dem vorherigen Plan werden durch Filter ersetzt, der die von Kalender hinzugefügten Zeilen entfernt. TransactionHistory wird noch zweimal gescannt. Ein zusätzlicher Scan ist erforderlich, um den Bereich der Daten für jedes Produkt zu erhalten. Ich war interessiert zu sehen, wie es mit einem anderen Ansatz, wo wir externe Kenntnisse über die globale Reihe von Daten in TransactionHistory. Plus zusätzliche Tabelle Produkt, das alle ProductIDs hat, um diesen zusätzlichen Scan zu vermeiden. Ich entfernte Berechnung der Anzahl von Transaktionen pro Tag von dieser Abfrage, um Vergleich gültig zu machen. Es kann in beiden Abfragen hinzugefügt werden, aber Id wie es einfach zu vergleichen. Ich musste auch andere Daten verwenden, weil ich 2014-Version der Datenbank verwenden. Beide Abfragen liefern das gleiche Ergebnis in derselben Reihenfolge. Hier sind Zeit und IO-Statistiken. Die Zwei-Scan-Variante ist ein bisschen schneller und hat weniger Lesevorgänge, da die One-Scan-Variante den Arbeitstisch viel nutzen muss. Außerdem erzeugt eine Scan-Variante mehr Zeilen als nötig, wie Sie in den Plänen sehen können. Es erzeugt Daten für jede ProductID, die sich in der Produkttabelle befindet, auch wenn eine ProductID keine Transaktionen hat. Es gibt 504 Zeilen in der Produkttabelle, aber nur 441 Produkte haben Transaktionen in TransactionHistory. Außerdem erzeugt es den gleichen Zeitraum für jedes Produkt, was mehr als erforderlich ist. Wenn die Transaktionshistorie eine längere Gesamtgeschichte aufweist, wobei jedes einzelne Produkt eine relativ kurze Historie aufweist, wäre die Anzahl der zusätzlichen nicht benötigten Zeilen noch höher. Auf der anderen Seite ist es möglich, die Zwei-Scan-Variante ein bisschen weiter zu optimieren, indem sie einen weiteren, engeren Index auf nur (ProductID, TransactionDate) erzeugt. Dieser Index würde verwendet werden, um StartEnd-Daten für jedes Produkt (CTEProducts) zu berechnen, und es würde weniger Seiten haben, als den Index zu decken und dadurch weniger Lesevorgänge verursachen. So können wir wählen, haben entweder einen extra expliziten einfachen Scan oder haben einen impliziten Arbeitstisch. BTW, wenn es in Ordnung ist, ein Ergebnis mit nur täglichen Zusammenfassungen haben, dann ist es besser, einen Index, der nicht ReferenceOrderID enthält. Es würde weniger Seiten weniger IO verwenden. Single Pass-Lösung mit CROSS APPLY Es wird eine wirklich lange Antwort, aber hier ist eine weitere Variante, die nur tägliche Zusammenfassung wieder zurückgibt, aber es nur einen Scan der Daten und es doesnt erfordern externe Kenntnisse über Bereich der Daten oder Liste der ProductIDs. Es tut nicht Zwischensortierungen als gut. Die Gesamtleistung ist ähnlich wie bei früheren Varianten, scheint aber ein bisschen schlechter zu sein. Die wichtigste Idee ist, eine Tabelle von Zahlen zu verwenden, um Zeilen zu erzeugen, die die Lücken in Daten füllen würden. Verwenden Sie für jedes vorhandene Datum LEAD, um die Größe der Lücke in Tagen zu berechnen, und verwenden Sie dann CROSS APPLY, um die erforderliche Anzahl von Zeilen in die Ergebnismenge hinzuzufügen. Zuerst versuchte ich es mit einer permanenten Tabelle der Zahlen. Der Plan zeigte eine große Anzahl von Lesungen in dieser Tabelle, obwohl die tatsächliche Dauer war ziemlich viel die gleiche, als wenn ich Zahlen auf der Fliege mit CTE generiert. Dieser Plan ist länger, da die Abfrage zwei Fensterfunktionen (LEAD und SUM) verwendet. Eine alternative SQLCLR-Lösung, die schneller ausgeführt wird und weniger Speicher benötigt: Das erfordert das EXTERNALACCESS-Berechtigungsset, da es eine Loopback-Verbindung zu dem Zielserver und der Datenbank anstelle der (langsamen) Kontextverbindung verwendet. Dies ist, wie die Funktion aufrufen: Erzeugt genau die gleichen Ergebnisse, in der gleichen Reihenfolge, wie die Frage. Profiler logische Lesevorgänge: 481 Der Hauptvorteil dieser Implementierung ist, dass sie schneller als die Kontextverbindung ist und weniger Speicher benötigt. Es hält nur zwei Dinge im Speicher zu einem beliebigen Zeitpunkt: Jede doppelte Zeilen (gleiche Produkt-und Transaktionsdatum). Dies ist erforderlich, weil entweder das Produkt oder das Datum ändert, wissen wir nicht, was die endgültige laufende Summe sein wird. In den Beispieldaten gibt es eine Kombination aus Produkt und Datum mit 64 Zeilen. Eine gleitende 45-Tage-Palette von Kosten und Transaktionsdaten nur für das aktuelle Produkt. Dies ist erforderlich, um die einfache laufende Summe für Zeilen einzustellen, die das 45-Tage-Schiebefenster verlassen. Diese minimale Zwischenspeicherung sollte sicherstellen, dass diese Methode sicherlich besser schneidet, als zu versuchen, den gesamten Eingangssatz im CLR-Speicher zu halten. Wenn Sie auf der 64-Bit-Enterprise-, Developer - oder Evaluation-Edition von SQL Server 2014 sind, können Sie In-Memory OLTP verwenden. Die Lösung wird nicht ein einziger Scan sein und wird kaum nutzen alle Fenster-Funktionen, aber es könnte etwas Wert auf diese Frage und der Algorithmus verwendet könnte möglicherweise als Inspiration für andere Lösungen verwendet werden. Zuerst müssen Sie In-Memory OLTP in der AdventureWorks-Datenbank aktivieren. Der Parameter für die Prozedur ist eine In-Memory-Tabellenvariable, die als Typ definiert werden muss. ID ist in dieser Tabelle nicht eindeutig, sie ist für jede Kombination von ProductID und TransactionDate eindeutig. Es gibt einige Kommentare in der Prozedur, die Ihnen sagen, was es tut, aber insgesamt ist es die Berechnung der laufenden Summe in einer Schleife und für jede Iteration es einen Lookup für die laufende Summe, wie es vor 45 Tagen (oder mehr) war. Der laufende Gesamtbetrag abzüglich der laufenden Summe, wie es vor 45 Tagen war, ist die rollende 45 Tage Summe, die wir suchen. Rufen Sie das Verfahren wie folgt auf. Testen Sie dies auf meinem Computer Client Statistics meldet eine Gesamtausführungszeit von etwa 750 Millisekunden. Für Vergleiche dauert die Unterabfrage-Version 3,5 Sekunden. Dieser Algorithmus könnte auch von regulären T-SQL verwendet werden. Berechnen Sie die laufende Summe unter Verwendung von Bereichszeilen und speichern Sie das Ergebnis in einer temporären Tabelle. Dann können Sie diese Tabelle mit einer Selbstverknüpfung zu der laufenden Summe abfragen, wie sie vor 45 Tagen war, und die Rollsumme berechnen. Allerdings ist die Implementierung der Bereich im Vergleich zu Zeilen ist ziemlich langsam aufgrund der Tatsache, dass muss Duplikate der Reihenfolge nach Klausel anders zu behandeln, so dass ich nicht bekommen, alle, die gute Leistung mit diesem Ansatz. Eine Problemumgehung könnte darin bestehen, eine andere Fensterfunktion wie lastvalue () über eine berechnete laufende Summe unter Verwendung von Zeilen zu verwenden, um einen Bereich, der die Summe simuliert, zu simulieren. Eine andere Möglichkeit ist die Verwendung von max () over (). Beide hatten einige Probleme. Finden Sie den entsprechenden Index zu verwenden, um zu vermeiden und Spulen mit der max () over () Version zu vermeiden. Ich gab auf, diese Dinge zu optimieren, aber wenn Sie sich für den Code interessiert sind, habe ich so weit bitte lassen Sie mich wissen. Antwort # 2 am: September 15, 2010, um 12:38 Uhr Nun, das war lustig :) Meine Lösung ist ein bisschen langsamer als GeoffPattersons aber ein Teil davon ist die Tatsache, dass Im binden an die ursprüngliche Tabelle, um eine von Geoffs Annahmen (dh eine Zeile pro eliminieren Produktdatenpaar). Ich ging mit der Annahme, das war eine vereinfachte Version einer endgültigen Abfrage und kann zusätzliche Informationen aus der ursprünglichen Tabelle. Hinweis: Im Kreditausleihe Geoffs Kalendertabelle und in der Tat am Ende mit einer sehr ähnlichen Lösung: Hier ist die Abfrage selbst: Grundsätzlich entschied ich, dass der einfachste Weg, um damit umzugehen war die Option für die ROWS-Klausel verwenden. Aber das erfordert, dass ich nur eine Zeile pro ProductID haben. TransactionDate Kombination und nicht nur das, aber ich musste eine Zeile pro ProduktID und mögliches Datum haben. Ich habe, dass die Kombination der Produkte, Kalender und TransactionHistory Tabellen in einem CTE. Dann musste ich einen weiteren CTE erstellen, um die rollenden Informationen zu erzeugen. Ich hatte dies zu tun, denn wenn ich es beigefügt die ursprüngliche Tabelle direkt habe ich Zeile Beseitigung, die meine Ergebnisse warf. Danach war es einfach, meinen zweiten CTE wieder an den ursprünglichen Tisch zu bringen. Ich fügte die TBE-Spalte (zu beseitigen), um loszuwerden, die leeren Zeilen in den CTEs erstellt. Auch habe ich ein CROSS APPLY in der ersten CTE, um Grenzen für meine Kalender-Tabelle zu generieren. Ich habe dann den empfohlenen Index hinzugefügt: Und bekam den endgültigen Ausführungsplan: EDIT: Am Ende fügte ich einen Index auf der Kalendertabelle, die Leistung durch eine angemessene Marge beschleunigt. Ich habe ein paar alternative Lösungen, die nicht verwenden Indizes oder Referenztabellen. Vielleicht könnten sie in Situationen nützlich sein, in denen Sie keinen Zugriff auf zusätzliche Tabellen haben und keine Indizes erstellen können. Es scheint, dass es möglich ist, korrekte Ergebnisse zu erhalten, wenn Gruppierung von TransactionDate mit nur einem einzigen Durchlauf der Daten und nur eine einzelne Fensterfunktion. Allerdings konnte ich nicht herausfinden, eine Möglichkeit, es zu tun, mit nur einem Fenster-Funktion, wenn Sie nicht gruppieren können durch TransactionDate. Um einen Bezugsrahmen zur Verfügung zu stellen, hat die ursprüngliche Lösung, die in der Frage geschrieben wurde, eine CPU-Zeit von 2808 ms ohne den Deckungsindex und 1950 ms mit dem Deckungsindex. Ich teste mit der AdventureWorks2014-Datenbank und SQL Server Express 2014. Beginnt mit einer Lösung für, wenn wir durch TransactionDate gruppieren können. Eine laufende Summe über die letzten X Tage kann auch folgendermaßen ausgedrückt werden: Laufende Summe für eine Zeile, die Summe aller vorherigen Zeilen ausführt - laufende Summe aller vorherigen Zeilen, für die das Datum außerhalb des Datumsfensters liegt. In SQL, eine Möglichkeit, dies auszudrücken ist, indem Sie zwei Kopien Ihrer Daten und für die zweite Kopie, multipliziert die Kosten mit -1 und Hinzufügen von X1 Tage an die Spalte Datum. Das Berechnen einer laufenden Summe über alle Daten wird die obige Formel implementieren. Ill zeigen dies für einige Beispieldaten. Unten ist ein Beispieldatum für eine einzelne ProductID. Ich vertrete Daten als Zahlen, um die Berechnungen einfacher zu machen. Startdaten: Fügen Sie eine zweite Kopie der Daten hinzu. Die zweite Kopie hat 46 Tage bis zum Datum und die Kosten multipliziert mit -1: Nehmen Sie die laufende Summe sortiert nach Datum aufsteigend und CopiedRow absteigend: Filtern Sie die kopierten Zeilen, um das gewünschte Ergebnis zu erhalten: Die folgenden SQL ist eine Möglichkeit, um die Über dem Algorithmus: Auf meiner Maschine dauerte dies 702 ms CPU-Zeit mit dem Deckungsindex und 734 ms CPU-Zeit ohne Index. Der Abfrageplan finden Sie hier: brentozarpastetheplanidSJdCsGVSl Ein Nachteil dieser Lösung ist, dass es scheint eine unvermeidbare Art bei der Bestellung durch die neue TransactionDate Spalte zu sein scheint. Ich denke nicht, dass diese Art durch das Hinzufügen von Indizes gelöst werden kann, weil wir zwei Kopien der Daten kombinieren müssen, bevor wir die Bestellung machen. Ich konnte eine Art am Ende der Abfrage, indem Sie in einer anderen Spalte zu ORDER BY loszuwerden. Wenn ich von FilterFlag bestellt habe, habe ich festgestellt, dass SQL Server diese Spalte von der Sortierung optimieren und eine explizite Sortierung durchführen würde. Lösungen, wann wir eine Ergebnismenge mit doppelten TransactionDate-Werten für die gleiche ProductId zurückgeben müssen, waren viel komplizierter. Ich würde das Problem zusammenfassen, da es gleichzeitig erforderlich ist, durch die gleiche Spalte zu teilen und zu ordnen. Die Syntax, die Paul zur Verfügung gestellt, dass Problem, so ist es nicht überraschend, dass seine so schwer zu äußern, mit den aktuellen Fenster-Funktionen in SQL Server (wenn es nicht schwer zu sagen, es gäbe keine Notwendigkeit, die Syntax zu erweitern). Wenn ich die obige Abfrage ohne Gruppierung verwende, dann bekomme ich unterschiedliche Werte für die rollende Summe, wenn es mehrere Zeilen mit dem gleichen ProductId und TransactionDate gibt. Eine Möglichkeit, dies zu beheben, besteht darin, dieselbe laufende Summenberechnung wie oben zu tun, aber auch die letzte Zeile in der Partition zu markieren. Dies kann mit LEAD geschehen (vorausgesetzt, ProductID ist nie NULL) ohne zusätzliche Sortierung. Für den letzten laufenden Summenwert verwende ich MAX als Fensterfunktion, um den Wert in der letzten Zeile der Partition auf alle Zeilen in der Partition anzuwenden. Auf meiner Maschine dauerte das 2464ms CPU-Zeit ohne den Deckungsindex. Wie vorher scheint es eine unvermeidliche Art zu geben. Der Abfrageplan finden Sie hier: brentozarpastetheplanidHyWxhGVBl Ich denke, dass es Raum für Verbesserungen in der oben genannten Abfrage. Es gibt sicherlich andere Möglichkeiten, um Windows-Funktionen verwenden, um das gewünschte Ergebnis zu bekommen. Die meisten Menschen sind vertraut mit der Phrase, quotthis tötet zwei Vögel mit einem stonequot. Wenn Sie nicht sind, bezieht sich die Phase auf einen Ansatz, der zwei Ziele in einer Handlung anspricht. (Leider sind die meisten von uns don39t wollen Steine ​​an unschuldigen Tieren zu werfen) Heute werde ich gehen, um einige Grundlagen auf zwei große Funktionen in SQL Server zu decken: der Columnstore-Index (nur in SQL Server Enterprise verfügbar) und Den SQL-Abfrage-Speicher. Microsoft tatsächlich implementiert den Columnstore-Index in SQL 2012 Enterprise, obwohl they39ve es in den letzten beiden Versionen von SQL Server verbessert. Microsoft führte den Query Store in SQL Server 2016. Also, was sind diese Funktionen und warum sind sie wichtig Nun, ich habe eine Demo, die beide Funktionen vorstellen und zeigen, wie sie uns helfen können. Bevor ich weiter gehen kann, decke ich auch diese (und andere Funktionen von SQL 2016) in meinem Artikel des CODE-Magazins über neue Funktionen SQL 2016. Als Grundeinführung kann der Columnstore-Index helfen, Abfragen zu beschleunigen, die über große Datenmengen scanaggregieren und Durchsucht der Abfragespeicher Abfrageausführungen, Ausführungspläne und Laufzeitstatistiken, die Sie normalerweise benötigen, um manuell zu sammeln. Vertrauen Sie mir, wenn ich sage, diese sind große Eigenschaften. Für diese Demo verwenden wir die Microsoft Contoso Retail Data Warehouse-Demo-Datenbank. Locker gesprochen, Contoso DW ist wie Quota wirklich großen AdventureWorksquot, mit Tabellen mit Millionen von Zeilen. (Die größte AdventureWorks-Tabelle enthält höchstens 100.000 Zeilen). Sie können die Contoso DW-Datenbank hier herunterladen: microsoften-usdownloaddetails. aspxid18279. Contoso DW funktioniert sehr gut, wenn Sie die Leistung bei Abfragen gegen größere Tabellen testen möchten. Contoso DW enthält eine Standard Data Warehouse Fact-Tabelle namens FactOnLineSales mit 12,6 Millionen Zeilen. Das ist sicher nicht die größte Data Warehouse-Tabelle der Welt, aber es spielt auch keine Rolle. Angenommen, ich möchte Produktumsatz für 2009 zusammenfassen und die Produkte einstufen. Ich könnte Abfrage der Tatsache Tabelle und beitreten, um die Product Dimension Tabelle und verwenden Sie eine RANK-Funktion, wie folgt: Here39s eine partielle Ergebnismenge der oberen 10 Zeilen, von Total Sales. Auf meinem Laptop (i7, 16 GB RAM), die Abfrage dauert überall von 3-4 Sekunden ausgeführt werden. Das scheint nicht wie das Ende der Welt zu sein, aber einige Benutzer erwarten nah-sofortige Resultate (die Weise, die Sie nahe-sofort Ergebnisse sehen konnten, wenn Sie Excel gegen einen OLAP Würfel verwenden). Der einzige Index, den ich derzeit auf dieser Tabelle haben, ist ein gruppierter Index auf einem Umsatzschlüssel. Wenn ich den Ausführungsplan betrachte, macht SQL Server einen Vorschlag, einen Deckungsindex zu der Tabelle hinzuzufügen: Jetzt, nur weil SQL Server einen Index vorschlägt, bedeutet doesn39t, dass Sie blind Indizes für jede quotmissing Indexquot-Nachricht erstellen sollten. In diesem Fall erkennt SQL Server jedoch, dass wir basierend auf Jahr filtern und den Produktschlüssel und den Verkaufsbetrag verwenden. So schlägt SQL Server einen Deckungsindex vor, mit dem DateKey als das Indexschlüsselfeld. Der Grund, den wir diesen quotcoveringquot Index nennen, ist, weil SQL Server entlang der nicht-key fieldsquot, die wir in der Abfrage verwendet haben, zitiert wird, zitiert für das ridequot. Auf diese Weise, SQL Server doesn39t müssen die Tabelle oder den gruppierten Index an allen Datenbank-Engine verwenden können einfach die Abdeckung Index für die Abfrage verwenden. Covering-Indizes sind in bestimmten Data Warehousing und Reporting-Datenbank-Szenarien beliebt, obwohl sie zu einem Preis der Datenbank-Engine, die sie erhalten kommen. Hinweis: Abdecken Indizes gibt es schon seit einer langen Zeit, so dass ich haven39t noch abgedeckt den Columnstore-Index und den Query Store. Also, ich werde den Deckungsindex hinzufügen: Wenn ich die gleiche Abfrage, die ich vor einem Moment ausgeführt habe (diejenige, die die Verkaufsmenge für jedes Produkt zusammengefasst hat) erneut ausführen, scheint die Abfrage manchmal um eine Sekunde schneller zu laufen, und ich bekomme eine Der einen Index-Suchvorgang anstelle eines Index-Scans verwendet (unter Verwendung des Datumschlüssels auf dem Deckungsindex, um den Umsatz für 2009 abzurufen). Also, vor dem Columnstore-Index, könnte dies eine Möglichkeit, diese Abfrage in viel älteren Versionen von SQL Server zu optimieren. Es läuft ein wenig schneller als das erste, und ich bekomme einen Ausführungsplan mit einem Index Seek anstelle eines Index-Scan. Allerdings gibt es einige Probleme: Die beiden Ausführungsoperatoren quotIndex Seekquot und quotHash Match (Aggregate) beide beide im Wesentlichen bedienen quotrow by rowquot. Stellen Sie sich dies in einer Tabelle mit Hunderten von Millionen von Zeilen vor. In Verbindung stehende, denken über den Inhalt einer Faktentabelle: In diesem Fall können ein einzelner Datumschlüsselwert andor oder ein einzelner Produktschlüsselwert über Hunderte von Tausenden von Zeilen wiederholt werden (denken Sie daran, die Faktentabelle hat auch Schlüssel für Geographie, Förderung, Verkäufer , Etc.) Also, wenn die quotIndex Seekquot und quotHash Matchquot Zeile für Zeile arbeiten, tun sie dies über Werte, die möglicherweise in vielen anderen Zeilen wiederholt werden. Dies ist normalerweise, wo I39d segue zum SQL Server-Columnstore-Index, die ein Szenario bietet, um die Leistung dieser Abfrage auf erstaunliche Weise zu verbessern. Aber bevor ich das mache, gehen wir zurück in die Zeit. Let39s gehen zurück auf das Jahr 2010, als Microsoft ein Add-In für Excel als PowerPivot bekannt eingeführt. Viele Menschen erinnern sich wahrscheinlich daran, Demos von PowerPivot für Excel zu sehen, wo ein Benutzer Millionen von Zeilen aus einer externen Datenquelle in Excel lesen konnte. PowerPivot would compress the data, and provide an engine to create Pivot Tables and Pivot Charts that performed at amazing speeds against the compressed data. PowerPivot used an in-memory technology that Microsoft termed quotVertiPaqquot. This in-memory technology in PowerPivot would basically take duplicate business keyforeign key values and compress them down to a single vector. The in-memory technology would also scanaggregate these values in parallel, in blocks of several hundred at a time. The bottom line is that Microsoft baked a large amount of performance enhancements into the VertiPaq in-memory feature for us to use, right out of the proverbial box. Why am I taking this little stroll down memory lane Because in SQL Server 2012, Microsoft implemented one of the most important features in the history of their database engine: the Columnstore index. The index is really an index in name only: it is a way to take a SQL Server table and create a compressed, in-memory columnstore that compresses duplicate foreign key values down to single vector values. Microsoft also created a new buffer pool to read these compressed vector values in parallel, creating the potential for huge performance gains. So, I39m going to create a columnstore index on the table, and I39ll see how much better (and more efficiently) the query runs, versus the query that runs against the covering index. So, I39ll create a duplicate copy of FactOnlineSales (I39ll call it FactOnlineSalesDetailNCCS), and I39ll create a columnstore index on the duplicated table that way I won39t interfere with the original table and the covering index in any way. Next, I39ll create a columnstore index on the new table: Note several things: I39ve specified several foreign key columns, as well as the Sales Amount. Remember that a columnstore index is not like a traditional row-store index. There is no quotkeyquot. We are simply indicating which columns SQL Server should compress and place in an in-memory columnstore. To use the analogy of PowerPivot for Excel when we create a columnstore index, we39re telling SQL Server to essentially do the same thing that PowerPivot did when we imported 20 million rows into Excel using PowerPivot So, I39ll re-run the query, this time using the duplicated FactOnlineSalesDetailNCCS table that contains the columnstore index. This query runs instantly in less than a second. And I can also say that even if the table had hundreds of millions of rows, it would still run at the proverbial quotbat of an eyelashquot. We could look at the execution plan (and in a few moments, we will), but now it39s time to cover the Query Store feature. Imagine for a moment, that we ran both queries overnight: the query that used the regular FactOnlineSales table (with the covering index) and then the query that used the duplicated table with the Columnstore index. When we log in the following morning, we39d like to see the execution plan for both queries as they took place, as well as the execution statistics. In other words, we39d like to see the same statistics that we39d be able to see if we ran both queries interactively in SQL Management Studio, turned in TIME and IO Statistics, and viewed the execution plan right after executing the query. Well, that39s what the Query Store allows us to do we can turn on (enable) Query Store for a database, which will trigger SQL Server to store query execution and plan statistics so that we can view them later. So, I39m going to enable the Query Store on the Contoso database with the following command (and I39ll also clear out any caching): Then I39ll run the two queries (and quotpretendquot that I ran them hours ago): Now let39s pretend they ran hours ago. According to what I said, the Query Store will capture the execution statistics. So how do I view them Fortunately, that39s quite easy. If I expand the Contoso DW database, I39ll see a Query Store folder. The Query Store has tremendous functionality and I39ll try to cover much of it in subsequent blog posts. But for right now, I want to view execution statistics on the two queries, and specifically examine the execution operators for the columnstore index. So I39ll right-click on the Top Resource Consuming Queries and run that option. That gives me a chart like the one below, where I can see execution duration time (in milliseconds) for all queries that have been executed. In this instance, Query 1 was the query against the original table with the covering index, and Query 2 was against the table with the columnstore index. The numbers don39t lie the columnstore index outperformed the original tablecovering index by a factor of almost 7 to 1. I can change the metric to look at memory consumption instead. In this case, note that query 2 (the columnstore index query) used far more memory. This demonstrates clearly why the columnstore index represents quotin-memoryquot technology SQL Server loads the entire columnstore index in memory, and uses a completely different buffer pool with enhanced execution operators to process the index. OK, so we have some graphs to view execution statistics can we see the execution plan (and execution operators) associated with each execution Yes, we can If you click on the vertical bar for the query that used the columnstore index, you39ll see the execution plan below. The first thing we see is that SQL Server performed a columnstore index scan, and that represented nearly 100 of the cost of the query. You might be saying, quotWait a minute, the first query used a covering index and performed an index seek so how can a columnstore index scan be fasterquot That39s a legitimate question, and fortunately there39s an answer. Even when the first query performed an index seek, it still executed quotrow by rowquot. If I put the mouse over the columnstore index scan operator, I see a tooltip (like the one below), with one important setting: the Execution Mode is BATCH (as opposed to ROW . which is what we had with the first query using the covering index). That BATCH mode tells us that SQL Server is processing the compressed vectors (for any foreign key values that are duplicated, such as the product key and date key) in batches of almost 1,000, in parallel. So SQL Server is still able to process the columnstore index much more efficiently. Additionally, if I place the mouse over the Hash Match (Aggregate) task, I also see that SQL Server is aggregating the columnstore index using Batch mode (although the operator itself represents such a tiny percent of the cost of the query) Finally, you might be asking, quotOK, so SQL Server compresses the values in the data, treats the values as vectors, and read them in blocks of almost a thousand values in parallel but my query only wanted data for 2009. So is SQL Server scanning over the entire set of dataquot Again, a good question. The answer is, quotNot reallyquot. Fortunately for us, the new columnstore index buffer pool performs another function called quotsegment eliminationquot. Basically, SQL Server will examine the vector values for the date key column in the columnstore index, and eliminate segments that are outside the scope of the year 2009. I39ll stop here. In subsequent blog posts I39ll cover both the columnstore index and Query Store in more detail. Essentially, what we39ve seen here today is that the Columnstore index can significantly speed up queries that scanaggregate over large amounts of data, and the Query Store will capture query executions and allow us to examine execution and performance statistics later. In the end, we39d like to produce a result set that shows the following. Notice three things: The columns essentially pivot all of the possible Return Reasons, after showing the sales amount The result set contains subtotals by the week ending (Sunday) date across all clients (where the Client is NULL) The result set contains a grand total row (where the Client and Date are both NULL) First, before I get into the SQL end we could use the dynamic pivotmatrix capability in SSRS. We would simply need to combine the two result sets by one column and then we could feed the results to the SSRS matrix control, which will spread the return reasons across the columns axis of the report. However, not everyone uses SSRS (though most people should). But even then, sometimes developers need to consume result sets in something other than a reporting tool. So for this example, let39s assume we want to generate the result set for a web grid page and possibly the developer wants to quotstrip outquot the subtotal rows (where I have a ResultSetNum value of 2 and 3) and place them in a summary grid. So bottom line, we need to generate the output above directly from a stored procedure. And as an added twist next week there could be Return Reason X and Y and Z. So we don39t know how many return reasons there could be. We simple want the query to pivot on the possible distinct values for Return Reason. Here is where the T-SQL PIVOT has a restriction we need to provide it the possible values. Since we won39t know that until run-time, we need to generate the query string dynamically using the dynamic SQL pattern. The dynamic SQL pattern involves generating the syntax, piece by piece, storing it in a string, and then executing the string at the end. Dynamic SQL can be tricky, as we have to embed syntax inside a string. But in this case, it our only true option if we want to handle a variable number of return reasons. I39ve always found that the best way to create a dynamic SQL solution is by figuring out what the quotidealquot generated-query would be at the end (in this case, given the Return reasons we know about).and then reverse-engineering it by piecing it together one part at a time. And so, here is the SQL we need if we knew those Return Reasons (A through D) were static and would not change. The query does the following: Combines the data from SalesData with the data from ReturnData, where we quothard-wirequot the word Sales as an Action Type form the Sales Table, and then use the Return Reason from the Return Data into the same ActionType column. That will give us a clean ActionType column on which to pivot. We are combining the two SELECT statements into a common table expression (CTE), which is basically a derived table subquery that we subsequently use in the next statement (to PIVOT) A PIVOT statement against the CTE, that sums the dollars for the Action Type being in one of the possible Action Type values. Note that this isn39t the final result set. We are placing this into a CTE that reads from the first CTE. The reason for this is because we want to do multiple groupings at the end. The final SELECT statement, that reads from the PIVOTCTE, and combines it with a subsequent query against the same PIVOTCTE, but where we also implement two groupings in the GROUPING SETS feature in SQL 2008: GROUPING by the Week End Date (dbo. WeekEndingDate) GROUPING for all rows () So if we knew with certainty that we39d never have more return reason codes, then that would be the solution. However, we need to account for other reason codes. So we need to generate that entire query above as one big string where we construct the possible return reasons as one comma separated list. I39m going to show the entire T-SQL code to generate (and execute) the desired query. And then I39ll break it out into parts and explain each step. So first, here39s the entire code to dynamically generate what I39ve got above. There are basically five steps we need to cover. Schritt 1 . we know that somewhere in the mix, we need to generate a string for this in the query: SalesAmount, Reason A, Reason B, Reason C, Reason D0160016001600160 What we can do is built a temporary common table expression that combines the hard wired quotSales Amountquot column with the unique list of possible reason codes. Once we have that in a CTE, we can use the nice little trick of FOR XML PATH(3939) to collapse those rows into a single string, put a comma in front of each row that the query reads, and then use STUFF to replace the first instance of a comma with an empty space. This is a trick that you can find in hundreds of SQL blogs. So this first part builds a string called ActionString that we can use further down. Schritt 2 . we also know that we39ll want to SUM the generatedpivoted reason columns, along with the standard sales column. So we39ll need a separate string for that, which I39ll call SUMSTRING. I39ll simply use the original ActionString, and then REPLACE the outer brackets with SUM syntax, plus the original brackets. Step 3: Now the real work begins. Using that original query as a model, we want to generate the original query (starting with the UNION of the two tables), but replacing any references to pivoted columns with the strings we dynamically generated above. Also, while not absolutely required, I39ve also created a variable to simply any carriage returnline feed combinations that we want to embed into the generated query (for readability). So we39ll construct the entire query into a variable called SQLPivotQuery. Schritt 4. We continue constructing the query again, concatenating the syntax we can quothard-wirequot with the ActionSelectString (that we generated dynamically to hold all the possible return reason values) Step 5 . Finally, we39ll generate the final part of the Pivot Query, that reads from the 2 nd common table expression (PIVOTCTE, from the model above) and generates the final SELECT to read from the PIVOTCTE and combine it with a 2 nd read against PIVOTCTE to implement the grouping sets. Finally, we can quotexecutequot the string using the SQL system stored proc spexecuteSQL So hopefully you can see that the process to following for this type of effort is Determine what the final query would be, based on your current set of data and values (i. e. built a query model) Write the necessary T-SQL code to generate that query model as a string. Arguably the most important part is determining the unique set of values on which you39ll PIVOT, and then collapsing them into one string using the STUFF function and the FOR XML PATH(3939) trick So whats on my mind today Well, at least 13 items Two summers ago, I wrote a draft BDR that focused (in part) on the role of education and the value of a good liberal arts background not just for the software industry but even for other industries as well. One of the themes of this particular BDR emphasized a pivotal and enlightened viewpoint from renowned software architect Allen Holub regarding liberal arts. Ill (faithfully) paraphrase his message: he highlighted the parallels between programming and studying history, by reminding everyone that history is reading and writing (and Ill add, identifying patterns), and software development is also reading and writing (and again, identifying patterns). And so I wrote an opinion piece that focused on this and other related topics. But until today, I never got around to either publishingposting it. Every so often Id think of revising it, and Id even sit down for a few minutes and make some adjustments to it. But then life in general would get in the way and Id never finish it. So what changed A few weeks ago, fellow CoDe Magazine columnist and industry leader Ted Neward wrote a piece in his regular column, Managed Coder , that caught my attention. The title of the article is On Liberal Arts. and I highly recommend that everyone read it. Ted discusses the value of a liberal arts background, the false dichotomy between a liberal arts background and success in software development, and the need to writecommunicate well. He talks about some of his own past encounters with HR personnel management regarding his educational background. He also emphasizes the need to accept and adapt to changes in our industry, as well as the hallmarks of a successful software professional (being reliable, planning ahead, and learning to get past initial conflict with other team members). So its a great read, as are Teds other CoDe articles and blog entries. It also got me back to thinking about my views on this (and other topics) as well, and finally motivated me to finish my own editorial. So, better late than never, here are my current Bakers Dozen of Reflections: I have a saying: Water freezes at 32 degrees . If youre in a trainingmentoring role, you might think youre doing everything in the world to help someone when in fact, theyre only feeling a temperature of 34 degrees and therefore things arent solidifying for them. Sometimes it takes just a little bit more effort or another ideachemical catalyst or a new perspective which means those with prior education can draw on different sources. Water freezes at 32 degrees . Some people can maintain high levels of concentration even with a room full of noisy people. Im not one of them occasionally I need some privacy to think through a critical issue. Some people describe this as you gotta learn to walk away from it. Stated another way, its a search for the rarefied air. This past week I spent hours in half-lit, quiet room with a whiteboard, until I fully understood a problem. It was only then that I could go talk with other developers about a solution. The message here isnt to preach how you should go about your business of solving problems but rather for everyone to know their strengths and what works, and use them to your advantage as much as possible. Some phrases are like fingernails on a chalkboard for me. Use it as a teaching moment is one. (Why is it like fingernails on a chalkboard Because if youre in a mentoring role, you should usually be in teaching moment mode anyway, however subtly). Heres another I cant really explain it in words, but I understand it. This might sound a bit cold, but if a person truly cant explain something in words, maybe they dont understand. Sure, a person can have a fuzzy sense of how something works I can bluff my way through describing how a digital camera works but the truth is that I dont really understand it all that well. There is a field of study known as epistemology (the study of knowledge). One of the fundamental bases of understanding whether its a camera or a design pattern - is the ability to establish context, to identify the chain of related events, the attributes of any components along the way, etc. Yes, understanding is sometimes very hard work, but diving into a topic and breaking it apart is worth the effort. Even those who eschew certification will acknowledge that the process of studying for certification tests will help to fill gaps in knowledge. A database manager is more likely to hire a database developer who can speak extemporaneously (and effortlessly) about transaction isolation levels and triggers, as opposed to someone who sort of knows about it but struggles to describe their usage. Theres another corollary here. Ted Neward recommends that developers take up public speaking, blogging, etc. I agree 100. The process of public speaking and blogging will practically force you to start thinking about topics and breaking down definitions that you might have otherwise taken for granted. A few years ago I thought I understood the T-SQL MERGE statement pretty well. But only after writing about it, speaking about, fielding questions from others who had perspectives that never occurred to me that my level of understanding increased exponentially. I know a story of a hiring manager who once interviewed an authordeveloper for a contract position. The hiring manager was contemptuous of publications in general, and barked at the applicant, So, if youre going to work here, would you rather be writing books or writing code Yes, Ill grant that in any industry there will be a few pure academics. But what the hiring manager missed was the opportunities for strengthening and sharpening skill sets. While cleaning out an old box of books, I came across a treasure from the 1980s: Programmers at Work. which contains interviews with a very young Bill Gates, Ray Ozzie, and other well-known names. Every interview and every insight is worth the price of the book. In my view, the most interesting interview was with Butler Lampson. who gave some powerful advice. To hell with computer literacy. Its absolutely ridiculous. Study mathematics. Learn to think. Lesen. Write. These things are of more enduring value. Learn how to prove theorems: A lot of evidence has accumulated over the centuries that suggests this skill is transferable to many other things. Butler speaks the truth . Ill add to that point learn how to play devils advocate against yourself. The more you can reality-check your own processes and work, the better off youll be. The great computer scientistauthor Allen Holub made the connection between software development and the liberal arts specifically, the subject of history. Here was his point: what is history Reading and writing. What is software development Among other things, reading and writing . I used to give my students T-SQL essay questions as practice tests. One student joked that I acted more like a law professor. Well, just like Coach Donny Haskins said in the movie Glory Road, my way is hard. I firmly believe in a strong intellectual foundation for any profession. Just like applications can benefit from frameworks, individuals and their thought processes can benefit from human frameworks as well. Thats the fundamental basis of scholarship. There is a story that back in the 1970s, IBM expanded their recruiting efforts in the major universities by focusing on the best and brightest of liberal arts graduates. Even then they recognized that the best readers and writers might someday become strong programmersystems analysts. (Feel free to use that story to any HR-type who insists that a candidate must have a computer science degree) And speaking of history: if for no other reason, its important to remember the history of product releases if Im doing work at a client site thats still using SQL Server 2008 or even (gasp) SQL Server 2005, I have to remember what features were implemented in the versions over time. Ever have a favorite doctor whom you liked because heshe explained things in plain English, gave you the straight truth, and earned your trust to operate on you Those are mad skills . and are the result of experience and HARD WORK that take years and even decades to cultivate. There are no guarantees of job success focus on the facts, take a few calculated risks when youre sure you can see your way to the finish line, let the chips fall where they may, and never lose sight of being just like that doctor who earned your trust. Even though some days I fall short, I try to treat my client and their data as a doctor would treat patients. Even though a doctor makes more money There are many clichs I detest but heres one I dont hate: There is no such thing as a bad question. As a former instructor, one thing that drew my ire was hearing someone criticize another person for asking a supposedly, stupid question. A question indicates a person acknowledges they have some gap in knowledge theyre looking to fill. Yes, some questions are better worded than others, and some questions require additional framing before they can be answered. But the journey from forming a question to an answer is likely to generate an active mental process in others. There are all GOOD things. Many good and fruitful discussions originate with a stupid question. I work across the board in SSIS, SSAS, SSRS, MDX, PPS, SharePoint, Power BI, DAX all the tools in the Microsoft BI stack. I still write some. NET code from time to time. But guess what I still spend so much time doing writing T-SQL code to profile data as part of the discovery process. All application developers should have good T-SQL chops. Ted Neward writes (correctly) about the need to adapt to technology changes. Ill add to that the need to adapt to clientemployer changes. Companies change business rules. Companies acquire other companies (or become the target of an acquisition). Companies make mistakes in communicating business requirements and specifications. Yes, we can sometimes play a role in helping to manage those changes and sometimes were the fly, not the windshield. These sometimes cause great pain for everyone, especially the I. T. people. This is why the term fact of life exists we have to deal with it. Just like no developer writes bug-free code every time, no I. T. person deals well with change every single time. One of the biggest struggles Ive had in my 28 years in this industry is showing patience and restraint when changes are flying from many different directions. Here is where my prior suggestion about searching for the rarified air can help. If you can manage to assimilate changes into your thought process, and without feeling overwhelmed, odds are youll be a significant asset. In the last 15 months Ive had to deal with a huge amount of professional change. Its been very difficult at times, but Ive resolved that change will be the norm and Ive tried to tweak my own habits as best I can to cope with frequent (and uncertain) change. Its hard, very hard. But as coach Jimmy Duggan said in the movie A League of Their Own: Of course its hard. If it wasnt hard, everyone would do it. The hard, is what makes it great . A powerful message. Theres been talk in the industry over the last few years about conduct at professional conferences (and conduct in the industry as a whole). Many respected writers have written very good editorials on the topic. Heres my input, for what its worth. Its a message to those individuals who have chosen to behave badly: Dude, it shouldnt be that hard to behave like an adult. A few years ago, CoDe Magazine Chief Editor Rod Paddock made some great points in an editorial about Codes of Conduct at conferences. Its definitely unfortunate to have to remind people of what they should expect out of themselves. But the problems go deeper. A few years ago I sat on a five-person panel (3 women, 2 men) at a community event on Women in Technology. The other male stated that men succeed in this industry because the Y chromosome gives men an advantage in areas of performance. The individual who made these remarks is a highly respected technology expert, and not some bozo making dongle remarks at a conference or sponsoring a programming contest where first prize is a date with a bikini model. Our world is becoming increasingly polarized (just watch the news for five minutes), sadly with emotion often winning over reason. Even in our industry, recently I heard someone in a position of responsibility bash software tool XYZ based on a ridiculous premise and then give false praise to a competing tool. So many opinions, so many arguments, but heres the key: before taking a stand, do your homework and get the facts . Sometimes both sides are partly rightor wrong. Theres only one way to determine: get the facts. As Robert Heinlein wrote, Facts are your single clue get the facts Of course, once you get the facts, the next step is to express them in a meaningful and even compelling way. Theres nothing wrong with using some emotion in an intellectual debate but it IS wrong to replace an intellectual debate with emotion and false agenda. A while back I faced resistance to SQL Server Analysis Services from someone who claimed the tool couldnt do feature XYZ. The specifics of XYZ dont matter here. I spent about two hours that evening working up a demo to cogently demonstrate the original claim was false. In that example, it worked. I cant swear it will always work, but to me thats the only way. Im old enough to remember life at a teen in the 1970s. Back then, when a person lost hisher job, (often) it was because the person just wasnt cutting the mustard. Fast-forward to today: a sad fact of life is that even talented people are now losing their jobs because of the changing economic conditions. Theres never a full-proof method for immunity, but now more than ever its critical to provide a high level of what I call the Three Vs (value, versatility, and velocity) for your employerclients. I might not always like working weekends or very late at night to do the proverbial work of two people but then I remember there are folks out there who would give anything to be working at 1 AM at night to feed their families and pay their bills. Always be yourselfyour BEST self. Some people need inspiration from time to time. Heres mine: the great sports movie, Glory Road. If youve never watched it, and even if youre not a sports fan I can almost guarantee youll be moved like never before. And Ill close with this. If you need some major motivation, Ill refer to a story from 2006. Jason McElwain, a high school student with autism, came off the bench to score twenty points in a high school basketball game in Rochester New York. Heres a great YouTube video. His mother said it all . This is the first moment Jason has ever succeeded and is proud of himself. I look at autism as the Berlin Wall. He cracked it. To anyone who wanted to attend my session at todays SQL Saturday event in DC I apologize that the session had to be cancelled. I hate to make excuses, but a combination of getting back late from Detroit (client trip), a car thats dead (blown head gasket), and some sudden health issues with my wife have made it impossible for me to attend. Back in August, I did the same session (ColumnStore Index) for PASS as a webinar. You can go to this link to access the video (itll be streamed, as all PASS videos are streamed) The link does require that you fill out your name and email address, but thats it. And then you can watch the video. Feel free to contact me if you have questions, at kgoffkevinsgoff. net November 15, 2013 Getting started with Windows Azure and creating SQL Databases in the cloud can be a bit daunting, especially if youve never tried out any of Microsofts cloud offerings. Fortunately, Ive created a webcast to help people get started. This is an absolute beginners guide to creating SQL Databases under Windows Azure. It assumes zero prior knowledge of Azure. You can go to the BDBI Webcasts of this website and check out my webcast (dated 11102013). Or you can just download the webcast videos right here: here is part 1 and here is part 2. You can also download the slide deck here. November 03, 2013 Topic this week: SQL Server Snapshot Isolation Levels, added in SQL Server 2005. To this day, there are still many SQL developers, many good SQL developers who either arent aware of this feature, or havent had time to look at it. Hopefully this information will help. Companion webcast will be uploaded in the next day look for it in the BDBI Webcasts section of this blog. October 26, 2013 Im going to start a weekly post of T-SQL tips, covering many different versions of SQL Server over the years Heres a challenge many developers face. Ill whittle it down to a very simple example, but one where the pattern applies to many situations. Suppose you have a stored procedure that receives a single vendor ID and updates the freight for all orders with that vendor id. create procedure dbo. UpdateVendorOrders update Purchasing. PurchaseOrderHeader set Freight Freight 1 where VendorID VendorID Now, suppose we need to run this for a set of vendor IDs. Today we might run it for three vendors, tomorrow for five vendors, the next day for 100 vendors. We want to pass in the vendor IDs. If youve worked with SQL Server, you can probably guess where Im going with this. The big question is how do we pass a variable number of Vendor IDs Or, stated more generally, how do we pass an array, or a table of keys, to a procedure Something along the lines of exec dbo. UpdateVendorOrders SomeListOfVendors Over the years, developers have come up with different methods: Going all the way back to SQL Server 2000, developers might create a comma-separated list of vendor keys, and pass the CSV list as a varchar to the procedure. The procedure would shred the CSV varchar variable into a table variable and then join the PurchaseOrderHeader table to that table variable (to update the Freight for just those vendors in the table). I wrote about this in CoDe Magazine back in early 2005 (code-magazinearticleprint. aspxquickid0503071ampprintmodetrue. Tip 3) In SQL Server 2005, you could actually create an XML string of the vendor IDs, pass the XML string to the procedure, and then use XQUERY to shred the XML as a table variable. I also wrote about this in CoDe Magazine back in 2007 (code-magazinearticleprint. aspxquickid0703041ampprintmodetrue. Tip 12)Also, some developers will populate a temp table ahead of time, and then reference the temp table inside the procedure. All of these certainly work, and developers have had to use these techniques before because for years there was NO WAY to directly pass a table to a SQL Server stored procedure. Until SQL Server 2008 when Microsoft implemented the table type. This FINALLY allowed developers to pass an actual table of rows to a stored procedure. Now, it does require a few steps. We cant just pass any old table to a procedure. It has to be a pre-defined type (a template). So lets suppose we always want to pass a set of integer keys to different procedures. One day it might be a list of vendor keys. Next day it might be a list of customer keys. So we can create a generic table type of keys, one that can be instantiated for customer keys, vendor keys, etc. CREATE TYPE IntKeysTT AS TABLE ( IntKey int NOT NULL ) So Ive created a Table Typecalled IntKeysTT . Its defined to have one column an IntKey. Nowsuppose I want to load it with Vendors who have a Credit Rating of 1..and then take that list of Vendor keys and pass it to a procedure: DECLARE VendorList IntKeysTT INSERT INTO VendorList SELECT BusinessEntityID from Purchasing. Vendor WHERE CreditRating 1 So, I now have a table type variable not just any table variable, but a table type variable (that I populated the same way I would populate a normal table variable). Its in server memory (unless it needs to spill to tempDB) and is therefore private to the connectionprocess. OK, can I pass it to the stored procedure now Well, not yet we need to modify the procedure to receive a table type. Heres the code: create procedure dbo. UpdateVendorOrdersFromTT IntKeysTT IntKeysTT READONLY update Purchasing. PurchaseOrderHeader set Freight Freight 1 FROM Purchasing. PurchaseOrderHeader JOIN IntKeysTT TempVendorList ON PurchaseOrderHeader. VendorID Te mpVendorList. IntKey Notice how the procedure receives the IntKeysTT table type as a Table Type (again, not just a regular table, but a table type). It also receives it as a READONLY parameter. You CANNOT modify the contents of this table type inside the procedure. Usually you wont want to you simply want to read from it. Well, now you can reference the table type as a parameter and then utilize it in the JOIN statement, as you would any other table variable. So dort haben Sie es. A bit of work to set up the table type, but in my view, definitely worth it. Additionally, if you pass values from. NET, youre in luck. You can pass an ADO. NET data table (with the same tablename property as the name of the Table Type) to the procedure. For. NET developers who have had to pass CSV lists, XML strings, etc. to a procedure in the past, this is a huge benefit. Finally I want to talk about another approach people have used over the years. SQL Server Cursors. At the risk of sounding dogmatic, I strongly advise against Cursors, unless there is just no other way. Cursors are expensive operations in the server, For instance, someone might use a cursor approach and implement the solution this way: DECLARE VendorID int DECLARE dbcursor CURSOR FASTFORWARD FOR SELECT BusinessEntityID from Purchasing. Vendor where CreditRating 1 FETCH NEXT FROM dbcursor INTO VendorID WHILE FETCHSTATUS 0 EXEC dbo. UpdateVendorOrders VendorID FETCH NEXT FROM dbcursor INTO VendorID The best thing Ill say about this is that it works. And yes, getting something to work is a milestone. But getting something to work and getting something to work acceptably are two different things. Even if this process only takes 5-10 seconds to run, in those 5-10 seconds the cursor utilizes SQL Server resources quite heavily. Thats not a good idea in a large production environment. Additionally, the more the of rows in the cursor to fetch and the more the number of executions of the procedure, the slower it will be. When I ran both processes (the cursor approach and then the table type approach) against a small sampling of vendors (5 vendors), the processing times where 260 ms and 60 ms, respectively. So the table type approach was roughly 4 times faster. But then when I ran the 2 scenarios against a much larger of vendors (84 vendors), the different was staggering 6701 ms versus 207 ms, respectively. So the table type approach was roughly 32 times faster. Again, the CURSOR approach is definitely the least attractive approach. Even in SQL Server 2005, it would have been better to create a CSV list or an XML string (providing the number of keys could be stored in a scalar variable). But now that there is a Table Type feature in SQL Server 2008, you can achieve the objective with a feature thats more closely modeled to the way developers are thinking specifically, how do we pass a table to a procedure Now we have an answer Hope you find this feature help. Feel free to post a comment.