Upgrade auf Azure SQL Database V12 mit dem Azure-Portal In diesem Artikel SQL Database V12 ist die neueste Version, so dass ein Upgrade auf SQL Database V12 empfohlen wird. Siehe Azure SQL Database-Funktionen für eine Feature-Matrix, die Azure SQL Database mit SQL Server vergleicht. Dieser Artikel enthält Anweisungen zum Aktualisieren vorhandener SQL Database V11-Server und - Datenbanken in SQL Database V12. Während des Vorgangs des Upgrades auf V12 aktualisieren Sie alle Web - und Business-Datenbanken auf eine neue Service-Schicht, sodass Anweisungen für die Aktualisierung von Web - und Business-Datenbanken enthalten sind. Darüber hinaus kann die Migration zu einem elastischen Pool kostengünstiger sein als ein Upgrade auf einzelne Leistungsstufen (Preisstufen) für einzelne Datenbanken. Pools vereinfachen auch die Datenbankverwaltung, da Sie nur die Performance-Einstellungen für den Pool verwalten müssen, anstatt die Performance-Ebenen einzelner Datenbanken separat zu verwalten. Wenn Sie Datenbanken auf mehreren Servern in Erwägung ziehen, sie in denselben Server zu verschieben und sie in einen Pool zu integrieren. Mithilfe von PowerShell können Sie automatisch Datenbanken von V11-Servern direkt in elastische Pools migrieren. Sie können auch das Portal verwenden, um V11-Datenbanken in einen Pool zu migrieren, aber im Portal müssen Sie bereits über einen V12-Server verfügen, um einen Pool zu erstellen. Die Anweisungen werden später in diesem Artikel bereitgestellt, um den Pool nach dem Server-Upgrade zu erstellen, wenn Sie über Datenbanken verfügen, die von einem Pool profitieren können. Beachten Sie, dass Ihre Datenbanken weiterhin online bleiben und während des gesamten Upgrades weiterarbeiten. Zum Zeitpunkt des tatsächlichen Übergangs zur neuen Leistungsstufe können temporäre Absenkungen der Verbindungen zur Datenbank für eine sehr kleine Zeitdauer erfolgen, die typischerweise etwa 90 Sekunden beträgt, aber bis zu 5 Minuten betragen kann. Wenn Ihre Anwendung vorübergehende Fehlerbehandlung für Verbindungsabschlüsse hat, dann genügt es, am Ende des Upgrades vor heruntergefallenen Verbindungen zu schützen. Das Upgrade auf SQL Database V12 kann nicht rückgängig gemacht werden. Nach einem Upgrade kann der Server nicht auf V11 zurückgesetzt werden. Nach dem Upgrade auf V12 werden Service-Tier-Empfehlungen und die Berücksichtigung von elastischen Pool-Performance nicht sofort verfügbar sein, bis der Dienst Zeit hat, Ihre Workloads auf dem neuen Server auszuwerten. Der V11-Server-Empfehlungsverlauf gilt nicht für V12-Server, sodass er nicht beibehalten wird. Vorbereiten der Aktualisierung Aktualisieren Sie alle Web - und Business-Datenbanken. Siehe Aktualisieren Sie alle unten aufgeführten Web - und Business-Datenbanken oder sehen Sie einen elastischen Pool (PowerShell) überwachen und verwalten. Überprüfung und Aussetzung der Geo-Replikation. Wenn Ihre Azure SQL-Datenbank für Geo-Replikation konfiguriert ist, sollten Sie ihre aktuelle Konfiguration dokumentieren und die Geo-Replikation beenden. Nachdem die Aktualisierung abgeschlossen ist, rekonfigurieren Sie Ihre Datenbank für Geo-Replikation. Öffnen Sie diese Ports, wenn Sie Clients auf einer Azure VM haben. Wenn Ihr Client-Programm eine Verbindung zu SQL Database V12 herstellt, während Ihr Client auf einer virtuellen VM (Virtual Machine) ausgeführt wird, müssen Sie Portbereiche 11000-11999 und 14000-14999 auf der VM öffnen. Weitere Informationen finden Sie unter Ports für SQL-Datenbank V12. Starten des Upgrades Starten Sie im Azure-Portal den Server, den Sie aktualisieren möchten, indem Sie BROWSE gt SQL-Server auswählen. Und wählen Sie den v2.0-Server, den Sie aktualisieren möchten. Wählen Sie Neueste SQL-Datenbankaktualisierung aus. Und wählen Sie dann diesen Server aktualisieren aus. Aktualisieren eines Servers auf die neueste SQL-Datenbank-Update ist dauerhaft und irreversibel. Geben Sie den Namen Ihres Servers ein, und klicken Sie auf OK, um das Upgrade zu bestätigen. Aktualisieren Sie alle Web - und Business-Datenbanken Wenn Ihr Server über Web - oder Business-Datenbanken verfügt, müssen Sie diese aktualisieren. Während des Upgrades auf SQL Database V12 aktualisieren Sie alle Web - und Business-Datenbanken auf eine neue Service-Schicht. Um Ihnen bei der Aktualisierung behilflich zu sein, empfiehlt der SQL-Datenbankdienst für jede Datenbank eine entsprechende Service-Stufe und Leistungsstufe (Preisstufe). Der Dienst empfiehlt die beste Stufe für das Ausführen Ihrer vorhandenen Datenbanken Workload durch die Analyse der historischen Verwendung für Ihre Datenbank. Wählen Sie in dem Upgrade dieses Server Blades jede Datenbank aus, die überprüft werden soll, und wählen Sie die empfohlene Preisstufe aus, auf die aktualisiert werden soll. Sie können immer die verfügbaren Preisstufen durchsuchen und die für Ihre Umgebung am besten geeignete auswählen. Nachdem Sie auf die vorgeschlagene Stufe geklickt haben, werden Sie mit dem Wählen Sie Ihr Preisklassenblatt angezeigt, in dem Sie eine Stufe auswählen können, und klicken Sie dann auf die Schaltfläche Auswählen, um diese Ebene zu ändern. Wählen Sie eine neue Stufe für jede Web - oder Unternehmensdatenbank aus. Was ist wichtig zu beachten ist, dass Ihre SQL-Datenbank ist nicht in einer bestimmten Service-Ebene oder Performance-Ebene gesperrt, so wie die Anforderungen Ihrer Datenbank ändern können Sie leicht zwischen den verfügbaren Service-Ebenen und Leistungsstufen zu ändern. In der Tat, Basic, Standard und Premium SQL-Datenbanken werden von der Stunde berechnet, und Sie haben die Möglichkeit, jede Datenbank nach oben oder unten 4 Mal innerhalb eines Zeitraums von 24 Stunden. Nachdem alle Datenbanken auf dem Server berechtigt sind, können Sie die Aktualisierung starten. Bestätigen Sie die Aktualisierung Wenn alle Datenbanken auf dem Server für ein Upgrade geeignet sind, müssen Sie den Servernamen überprüfen, um zu überprüfen, dass Sie das Upgrade durchführen möchten, und klicken Sie dann auf OK . Die Aktualisierung wird gestartet und die aktuelle Benachrichtigung wird angezeigt. Der Aktualisierungsvorgang wird gestartet. Abhängig von den Details Ihrer spezifischen Datenbanken kann ein Upgrade auf V12 einige Zeit in Anspruch nehmen. Während dieser Zeit bleiben alle Datenbanken auf dem Server online, Server - und Datenbankverwaltungsaktionen werden jedoch eingeschränkt. Zum Zeitpunkt des eigentlichen Übergangs zur neuen Leistungsstufe kann es vorkommen, dass die Verbindungen zur Datenbank temporär ablaufen (für gewöhnlich in Sekunden gemessen). Wenn eine Anwendung eine vorübergehende Fehlerbehandlung (Wiederholungslogik) für Verbindungsabschlüsse hat, dann genügt es, am Ende des Upgrades vor abgefallenen Verbindungen zu schützen. Nachdem der Upgrade-Vorgang abgeschlossen ist, wird das neueste Update-Blade aktiviert. Verschieben Sie Ihre Datenbanken in einen elastischen Pool. Blättern Sie im Azure-Portal zum V12-Server und klicken Sie auf Pool hinzufügen. Wenn Sie sehen, eine Nachricht sagen Klicken Sie hier, um die empfohlenen elastischen Pools für diesen Server. Klicken Sie darauf, um einen Pool zu erstellen, der für Ihre Server-Datenbanken optimiert ist. Details finden Sie unter Preis - und Leistungsvoraussetzungen für einen elastischen Pool. Folgen Sie den Anweisungen im Abschnitt Erstellen eines elastischen Pools, um die Erstellung Ihres Pools zu beenden. Überwachen von Datenbanken nach dem Upgrade auf SQL Database V12 Aktualisieren Sie auf die neueste Version von SQL Server Management Studio (SSMS), um die Vorteile der neuen v12-Funktionen zu nutzen. Laden Sie SQL Server Management Studio herunter. Aktivieren Sie nach dem Upgrade die Datenbank aktiv, um sicherzustellen, dass Anwendungen mit der gewünschten Leistung ausgeführt werden, und optimieren Sie die Einstellungen nach Bedarf. Ressourcenverbrauchsdaten: Für Basis-, Standard - und Premium-Datenbanken sind Ressourcenverbrauchsdaten über die sys. dm db resourcestats DMV in der Benutzerdatenbank verfügbar. Dieses DMV liefert in Echtzeit Echtzeitressourcenverbrauchsinformationen bei 15 Sekunden Granularität für die vorangegangene Betriebsstunde. Der DTU-Prozentsatzverbrauch für ein Intervall wird als der maximale Prozentsatzverbrauch der CPU-, IO - und Protokollabmessungen berechnet. Hier ist eine Abfrage zum Berechnen des durchschnittlichen DTU-Prozentsatzverbrauchs in der letzten Stunde: Zusätzliche Überwachungsinformationen: Alerts: Richten Sie 39Alerts39 im Azure-Portal ein, um Sie darüber zu informieren, wenn der DTU-Konsum für eine aktualisierte Datenbank sich einem bestimmten hohen Niveau annähert. Datenbankalarme können im Azure-Portal für verschiedene Leistungsmetriken wie DTU, CPU, IO und Log eingerichtet werden. Navigieren Sie zu Ihrer Datenbank und wählen Sie Alert-Regeln im Blade "Einstellungen" aus. Beispielsweise können Sie eine E-Mail-Benachrichtigung über DTU-Prozentsatz einrichten, wenn der durchschnittliche DTU-Prozentsatz in den letzten 5 Minuten 75 überschreitet. Weitere Informationen zum Konfigurieren von Alert-Benachrichtigungen finden Sie unter Warnmeldungen empfangen. Nächste Schritte Related LinksMost Menschen sind vertraut mit der Phrase, quotthis töten 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 aggregierte), 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 würde die Daten komprimieren und eine Engine bereitstellen, um Pivot-Tabellen und Pivot-Charts zu erstellen, die mit erstaunlichen Geschwindigkeiten gegen die komprimierten Daten durchgeführt wurden. PowerPivot benutzte eine In-Memory-Technologie, die Microsoft als "tertiPaqquot" bezeichnet. Diese In-Memory-Technologie in PowerPivot würde im Grunde genommen duplicate business keyforeign Schlüsselwerte und komprimieren sie bis zu einem einzigen Vektor. Die In-Memory-Technologie würde auch scanaggregate diese Werte parallel, in Blöcken von mehreren hundert zu einer Zeit. Die Quintessenz ist, dass Microsoft eine große Menge an Leistungsverbesserungen in die VertiPaq In-Memory-Funktion für uns zu verwenden, direkt aus der sprichwörtlichen Box gebacken. Warum bin ich unter diesem kleinen Spaziergang Speicher Lane Da in SQL Server 2012 implementiert eine der wichtigsten Funktionen in der Geschichte ihrer Datenbank-Engine: der Columnstore-Index. Der Index ist wirklich ein Index im Namen nur: es ist eine Weise, eine SQL-Server-Tabelle zu nehmen und ein komprimiertes, im Speicher befindliches columnstore zu verursachen, das doppelte Fremdschlüsselwerte bis Einzelvektorwerte komprimiert. Microsoft hat auch einen neuen Pufferpool erstellt, um diese komprimierten Vektorwerte parallel zu lesen und so das Potenzial für große Leistungssteigerungen zu schaffen. So, I39m gehen, um einen columnstore Index auf der Tabelle zu verursachen, und I39ll sehen, wie viel besser (und leistungsfähiger) die Abfrage, gegen die Abfrage, die gegen den bedeckenden Index läuft. So, I39ll erstellen Sie eine doppelte Kopie von FactOnlineSales (I39ll nennen es FactOnlineSalesDetailNCCS), und I39ll erstellen Sie einen columnstore-Index auf der duplizierten Tabelle, die Weise I won39t stören die ursprüngliche Tabelle und die Deckung Index in keiner Weise. Als nächstes erstellen wir einen Columnstore-Index auf der neuen Tabelle: Beachten Sie mehrere Dinge: I39ve hat mehrere Fremdschlüsselspalten sowie den Verkaufsbetrag angegeben. Denken Sie daran, dass ein columnstore-Index nicht wie ein herkömmlicher Zeilenspeicherindex ist. Es gibt keine Quotechart. Wir sind nur darauf hin, welche Spalten SQL Server komprimieren und platzieren sollte in einem in-memory columnstore. Um die Analogie von PowerPivot für Excel zu verwenden, wenn wir einen columnstore-Index erstellen, sagen wir SQL Server im Wesentlichen dasselbe, was PowerPivot tat, wenn wir 20 Millionen Zeilen in Excel mit PowerPivot importierten So I39ll erneut ausführen die Abfrage, diesmal mit Die doppelte FactOnlineSalesDetailNCCS-Tabelle, die den columnstore-Index enthält. Diese Abfrage läuft sofort in weniger als einer Sekunde. Und ich kann auch sagen, dass auch wenn der Tisch Hunderte von Millionen von Zeilen hatte, würde es immer noch auf das sprichwörtliche Quotbat eines Wimpernschuhs laufen. Wir könnten den Ausführungsplan (und in ein paar Momenten, wir werden), aber jetzt ist es Zeit, um die Abfrage Store-Feature zu decken. Stellen Sie sich für einen Moment vor, dass wir beide Abfragen über Nacht liefen: die Abfrage, die die reguläre FactOnlineSales-Tabelle (mit dem Deckungsindex) verwendete, und dann die Abfrage, die die doppelte Tabelle mit dem Columnstore-Index verwendete. Wenn wir uns am nächsten Morgen anmelden, sehen wir den Ausführungsplan für beide Abfragen, wie sie stattfanden, ebenso wie die Ausführungsstatistiken. Mit anderen Worten, wir sehen gerne die gleichen Statistiken, die wir in der Lage zu sehen, wenn wir beide Abfragen interaktiv in SQL Management Studio, in TIME und IO Statistiken gedreht und sah den Ausführungsplan direkt nach der Ausführung der Abfrage. Nun, that39s, was der Query Store ermöglicht es uns zu tun, können wir aktivieren (aktivieren) Abfrage-Speicher für eine Datenbank, die SQL Server auslösen wird, um Abfrage Ausführung speichern und planen Statistiken, so dass wir sie später anzeigen können. So, I39m gehen, um den Query Store auf der Contoso-Datenbank mit dem folgenden Befehl zu aktivieren (und I39ll auch löschen jedes Caching): Dann I39ll führen Sie die beiden Abfragen (und quotpretendquot, dass ich lief sie vor Stunden): Jetzt let39s vorgeben, dass sie lief Stunden vor. Nach dem, was ich sagte, wird der Query Store erfassen die Ausführungsstatistik. Also, wie kann ich sie ansehen Glücklicherweise ist das ziemlich einfach. Wenn ich die Contoso DW Datenbank erweitere, sehe ich einen Query Store Ordner. Der Abfrage-Speicher hat enorme Funktionalität und I39ll versuchen, viel von ihm in den folgenden Blogbeiträgen zu umfassen. Aber für jetzt, Ich möchte Ausführungsstatistiken auf die beiden Abfragen anzeigen, und speziell untersuchen die Ausführungsoperatoren für den columnstore-Index. So I39ll mit der rechten Maustaste auf die Top Resource Consuming Abfragen und führen Sie diese Option. Das gibt mir ein Diagramm wie das unten, wo kann ich sehen, Ausführungsdauer Zeit (in Millisekunden) für alle Abfragen, die ausgeführt wurden. In diesem Fall war Query 1 die Abfrage für die ursprüngliche Tabelle mit dem Deckungsindex und Query 2 war gegen die Tabelle mit dem columnstore-Index. Die Zahlen don39t liegen der columnstore Index übertraf den ursprünglichen Tablecovering Index um einen Faktor von fast 7 zu 1. Ich kann die Metrik ändern, um Speicherverbrauch anstatt zu betrachten. In diesem Fall beachten Sie, dass Abfrage 2 (die Spalte-Index-Abfrage) viel mehr Speicher verwendet. Dies veranschaulicht, warum der columnstore-Index die Technologie von quotin-memoryquot darstellt. SQL Server lädt den gesamten columnstore-Index im Speicher und verwendet einen vollständig anderen Pufferpool mit erweiterten Ausführungsoperatoren, um den Index zu verarbeiten. Wenn Sie auf die vertikale Leiste für die Abfrage klicken, die den columnstore-Index verwendet hat, sehen Sie die Ausführung. Wenn Sie auf die vertikale Leiste klicken, werden die Ausführungsstatistiken angezeigt Plan unten. Das erste, was wir sehen, ist, dass SQL Server eine Columnstore-Index-Scan durchgeführt, und dass fast 100 der Kosten der Abfrage dargestellt. Man könnte sagen, quotWait eine Minute, die erste Abfrage verwendet eine Deckung Index und führte eine Index-Suche, wie kann ein Columnstore-Index-Scan fasterquot That39s eine legitime Frage, und glücklicherweise there39s eine Antwort. Selbst wenn die erste Abfrage eine Indexsuche durchführte, führte sie immer noch das Byte durch rowquot aus. Wenn ich die Maus über den columnstore index scan operator setze, sehe ich eine tooltip (wie die unten unten), mit einer wichtigen Einstellung: der Execution Mode ist BATCH (im Gegensatz zu ROW, was wir mit der ersten Abfrage hatten Deckungsindex). Dieser BATCH-Modus weist darauf hin, dass SQL Server die komprimierten Vektoren (für alle Fremdschlüsselwerte, die dupliziert werden, z. B. Product Key und Datumsschlüssel) in Chargen von fast 1.000 parallel verarbeitet werden. So ist SQL Server noch in der Lage, den columnstore Index viel effizienter zu verarbeiten. Darüber hinaus, wenn ich die Maus über die Hash Match (Aggregate) Task platzieren, sehe ich auch, dass SQL Server Aggregation der columnstore Index mithilfe Batch-Modus (obwohl der Operator selbst stellt so ein winziges Prozent der Kosten der Abfrage) Schließlich, Sie SQL-Server komprimiert die Werte in den Daten, behandelt die Werte als Vektoren, und lesen Sie sie in Blöcken von fast tausend Werte parallel, aber meine Abfrage wollte nur Daten für 2009. So ist SQL Server Scannen über die Gesamte Menge von Datenquot Wieder eine gute Frage. Die Antwort lautet: Nein. Zum Glück für uns führt der neue columnstore Index Pufferpool eine weitere Funktion namens segmentsegment eliminationquot aus. Grundsätzlich untersucht SQL Server die Vektorwerte für die Datumschlüsselspalte im columnstore-Index und eliminiert Segmente, die außerhalb des Geltungsbereichs des Jahres 2009 liegen. In nachfolgenden Blog-Posts I39ll decken sowohl die columnstore Index und Query Store detaillierter. Im Wesentlichen, was wir hier gesehen haben, ist, dass der Columnstore-Index erheblich beschleunigt Abfragen, die scanaggregate über große Mengen an Daten, und der Query Store wird Abfrageausführungen erfassen und erlauben uns, Ausführungs-und Performance-Statistiken später zu untersuchen. Am Ende wollen wir eine Ergebnismenge erzeugen, die folgendes zeigt. Beachten Sie drei Dinge: Die Spalten schwenken im Wesentlichen alle möglichen Rückgabegründe nach dem Anzeigen der Verkaufsmenge Die Ergebnismenge enthält Zwischensummen für das Wochenende (Sonntag) über alle Clients (wobei der Client NULL ist). Die Ergebnismenge enthält eine Gesamtsumme Zeile (wo der Client und das Datum sind beide NULL) Zuerst, bevor ich in das SQL-Ende konnten wir die dynamische Pivotmatrix-Fähigkeit in SSRS verwenden. Wir müssten einfach die beiden Ergebnismengen um eine Spalte kombinieren und dann könnten wir die Ergebnisse an die SSRS-Matrix-Steuerung liefern, die die Rückkehrgründe über die Spaltenachse des Berichts verbreiten wird. Jedoch nicht jeder benutzt SSRS (obwohl die meisten Leute sollten). Aber selbst dann, manchmal Entwickler müssen Ergebnismengen in etwas anderes als ein Berichtswesen zu konsumieren. Also für dieses Beispiel nehmen wir an, wir wollen die Ergebnismenge für eine Web-Grid-Seite zu generieren und möglicherweise der Entwickler will outstot outquot die Zwischensumme Zeilen (wo ich habe einen ResultSetNum Wert von 2 und 3) und legen Sie sie in einem Summary Grid. So unten Zeile, müssen wir die Ausgabe oben direkt aus einer gespeicherten Prozedur zu generieren. Und als addierte Torsion nächste Woche konnte es Rückholgrund X und Y und Z geben. So wissen wir nicht, wie viele Rückholgründe es sein konnten. Wir wollen einfach, dass die Abfrage auf die möglichen unterschiedlichen Werte für Rückgabegrund verschoben wird. Hier ist, wo die T-SQL PIVOT eine Beschränkung, die wir brauchen, um es die möglichen Werte bieten. Da wir nicht wissen, dass bis zur Laufzeit, müssen wir die Abfrage-String dynamisch mit dem dynamischen SQL-Muster zu generieren. Das dynamische SQL-Muster beinhaltet die Erzeugung der Syntax, Stück für Stück, das Speichern in einem String und dann das Ausführen der Zeichenfolge am Ende. Dynamic SQL kann schwierig sein, da wir Syntax innerhalb einer Zeichenfolge einbetten müssen. Aber in diesem Fall ist es unsere einzige wahre Option, wenn wir eine variable Anzahl von Rückgabegründen behandeln wollen. Ich habe immer festgestellt, dass der beste Weg, um eine dynamische SQL-Lösung zu schaffen ist, indem Sie herauszufinden, was die quotidealquot generierte-Abfrage am Ende sein würde (in diesem Fall angesichts der Rückkehr Gründen, die wir kennen) und dann reverse-Engineering es durch Piecing Es zusammen ein Teil zu einer Zeit. Und so, hier ist die SQL, die wir brauchen, wenn wir wussten, dass diese Rückkehr Gründe (A bis D) waren statisch und würde sich nicht ändern. Die Abfrage funktioniert folgendermaßen: Kombiniert die Daten von SalesData mit den Daten von ReturnData, wobei wir das Wort Sales als Aktionstyp für die Sales-Tabelle quothard-wirequot verwenden und dann die Rückgabe-Begründung aus den Rückgabedaten in dieselbe ActionType-Spalte verwenden. Das gibt uns eine saubere ActionType-Spalte, auf der wir drehen können. Wir kombinieren die beiden SELECT-Anweisungen zu einem gemeinsamen Tabellenausdruck (CTE), der im Grunde genommen eine abgeleitete Tabellenunterabfrage ist, die wir anschließend in der nächsten Anweisung (zu PIVOT) verwenden. Eine PIVOT-Anweisung gegen den CTE, die die Dollar für den Aktionstyp summiert Der sich in einem der möglichen Aktionsart-Werte befindet. Beachten Sie, dass dies nicht die endgültige Ergebnismenge ist. Wir setzen dies in einen CTE, der aus dem ersten CTE liest. Der Grund dafür ist, dass wir am Ende mehrere Gruppierungen durchführen wollen. Die endgültige SELECT-Anweisung, die aus der PIVOTCTE liest, kombiniert sie mit einer nachfolgenden Abfrage desselben PIVOTCTE, wobei wir aber auch zwei Gruppierungen in der GROUPING SETS-Funktion in SQL 2008 implementieren: GROUPING am Wochenenddatum (dbo. WeekEndingDate) GROUPING für alle Zeilen () Wenn wir also mit Sicherheit wussten, dass wir nie mehr Rückgabecodes haben, dann wäre das die Lösung. Wir müssen aber auch andere Ursachencodes berücksichtigen. Also müssen wir diese ganze Abfrage oben als eine große Zeichenfolge generieren, wo wir die möglichen Rückkehrgründe als eine durch Kommas getrennte Liste konstruieren. I39m zeigen den gesamten T-SQL-Code zu generieren (und auszuführen) der gewünschten Abfrage. Und dann breche ich es in Teile und erkläre jeden Schritt. So zuerst, hier39s der gesamte Code, um dynamisch zu erzeugen, was I39ve oben erhielt. Es gibt im Grunde fünf Schritte, die wir abdecken müssen. Schritt 1 . Wir wissen, dass irgendwo in der Mischung, müssen wir einen String für diese in der Abfrage zu generieren: SalesAmount, Grund A, Grund B, Grund C, Grund D0160016001600160 Was wir tun können, ist ein temporärer gemeinsamer Tabellenausdruck, der die harte verdrahtete quotSales kombiniert Amountquot-Spalte mit der eindeutigen Liste der möglichen Ursachencodes. Sobald wir das in einem CTE haben, können wir den schönen kleinen Trick von FOR XML PATH (3939) verwenden, um diese Zeilen zu einem einzelnen String zusammenzubrechen, ein Komma vor jeder Zeile zu setzen, die die Abfrage liest, und dann STUFF zu ersetzen Die erste Instanz eines Kommas mit einem leeren Raum. Dies ist ein Trick, den Sie in Hunderten von SQL-Blogs finden können. Also dieser erste Teil baut eine Zeichenfolge namens ActionString, die wir weiter unten verwenden können. Schritt 2 . Wissen wir auch, dass wir39ll die generierten geneigten Grundspalten summieren wollen, zusammen mit der Standard-Verkaufsspalte. Also brauchen wir eine separate Zeichenfolge dafür, die ich SUMSTRING nennen werde. I39ll verwenden Sie einfach die ursprüngliche ActionString, und dann REPLACE die äußeren Klammern mit SUM-Syntax, plus die ursprünglichen Klammern. Schritt 3: Jetzt beginnt die eigentliche Arbeit. Mit dieser ursprünglichen Abfrage als Modell wollen wir die ursprüngliche Abfrage (beginnend mit der UNION der beiden Tabellen) generieren, aber alle Referenzen auf verschwenkte Spalten durch die dynamisch erzeugten Strings ersetzen. Auch wenn nicht unbedingt erforderlich, hat I39ve auch eine Variable erstellt, um einfach alle Wagenrücklauf-Feedkombinationen, die wir in die generierte Abfrage einbetten möchten (zur Lesbarkeit). So konstruieren wir die gesamte Abfrage in einer Variablen namens SQLPivotQuery. Schritt 4. Wir konstruieren die Abfrage erneut und verketten die Syntax, die wir mit dem ActionSelectString quothard-wirequot verknüpfen können (die wir dynamisch erzeugt haben, um alle möglichen Rückgabewerte zu halten) Schritt 5. Schließlich erzeugen wir den letzten Teil der Pivot-Abfrage, der aus dem 2. gemeinsamen Tabellenausdruck (PIVOTCTE, aus dem obigen Modell) liest und das letzte SELECT aus dem PIVOTCTE ausliest und es mit einem zweiten gegen PIVOTCTE lesen liest Implementieren die Gruppierungssätze. Schließlich können wir quotexecutequot die Zeichenfolge mit dem SQL-System gespeicherte proc spexecuteSQL So hoffentlich können Sie sehen, dass der Prozess für die folgenden für diese Art von Aufwand ist Bestimmen, was die endgültige Abfrage würde, basierend auf Ihren aktuellen Satz von Daten und Werten (dh gebaut werden Ein Abfragemodell) Schreiben Sie den erforderlichen T-SQL-Code, um das Abfragemodell als String zu generieren. Der wichtigste Teil ist die Bestimmung der einzigartigen Satz von Werten, auf denen Sie39ll PIVOT, und dann kollabieren sie in einen String mit der STUFF-Funktion und die FOR XML PATH (3939) Trick Also, was ist auf meinem Kopf heute Nun, mindestens 13 Elemente Zwei Summers, schrieb ich einen Entwurf des BDR, der sich (teilweise) auf die Rolle der Bildung und den Wert einer guten liberalen Kunst Hintergrund nicht nur für die Software-Industrie, sondern auch für andere Branchen als auch konzentriert. Eines der Themen dieser besonderen BDR betonte eine zentrale und erleuchtete Sichtweise des renommierten Softwarearchitekten Allen Holub über die freien Künste. Kranke (treu) paraphrasieren seine Botschaft: Er hebt die Parallelen zwischen Programmierung und Studium der Geschichte hervor, indem er alle daran erinnert, dass Geschichte Lesen und Schreiben ist (und Ill hinzufügen, Muster identifizieren), und Software-Entwicklung ist auch Lesen und Schreiben (und wieder Identifizierung von Mustern ). Und so schrieb ich eine Stellungnahme Stück, das auf diesem und anderen verwandten Themen konzentriert. Aber bis heute habe ich nie zu veröffentlichen. Jeder so oft Id denken der Überarbeitung, und Id sogar sitzen für ein paar Minuten und machen einige Anpassungen an sie. Aber dann würde das Leben im Allgemeinen im Weg und Id nie beenden. So was geändert Vor ein paar Wochen, CoDe Magazin Kolumnist und Branchenführer Ted Neward schrieb ein Stück in seiner regulären Spalte, Managed Coder, die meine Aufmerksamkeit erregte. Der Titel des Artikels ist On Liberal Arts. Und ich empfehle, dass alle es lesen. Ted diskutiert den Wert eines liberalen Kunsthintergrundes, die falsche Dichotomie zwischen einem liberalen Kunsthintergrund und Erfolg in der Software-Entwicklung und die Notwendigkeit, writecommunicate gut zu schreiben. Er spricht über einige seiner eigenen vergangenen Begegnungen mit HR Personalmanagement in Bezug auf seinen pädagogischen Hintergrund. Darüber hinaus unterstreicht er die Notwendigkeit, Veränderungen in unserer Branche zu akzeptieren und anzupassen, sowie die Kennzeichen eines erfolgreichen Softwareprofis (zuverlässig, vorausschauend und lernen, anfangs Konflikte mit anderen Teammitgliedern zu erlernen). Also seine eine große lesen, wie sind Teds andere CoDe Artikel und Blog-Einträge. Es hat auch mich zurück zu denken über meine Ansichten zu diesem (und anderen Themen) als auch, und schließlich motiviert mich, meine eigene Redaktion zu beenden. Also, besser spät als nie, hier sind meine aktuellen Bakers Dozen of Reflections: Ich habe ein Sprichwort: Wasser friert bei 32 Grad. Wenn youre in einer Trainingsrolle Rolle, könnten Sie denken, dass Sie alles in der Welt tun, um jemanden zu helfen, wenn in der Tat, theyre nur das Gefühl, eine Temperatur von 34 Grad und damit Dinge arent Verfestigung für sie. Manchmal dauert es nur ein wenig mehr Aufwand oder einen anderen ideachemischen Katalysator oder eine neue Perspektive, was bedeutet, dass diejenigen mit vorheriger Ausbildung auf verschiedene Quellen zurückgreifen können. Wasser friert bei 32 Grad. Einige Leute können ein hohes Maß an Konzentration auch mit einem Raum voller lärmender Menschen zu halten. Im nicht einer von ihnen gelegentlich brauche ich etwas Privatleben, zum durch ein kritisches Problem zu denken. Manche Leute beschreiben dies, wie Sie gotta lernen zu gehen weg von ihm. Anders gesagt, seine Suche nach der verdünnten Luft. In der vergangenen Woche verbrachte ich Stunden in einem halbbeleuchteten, ruhigen Raum mit einem Whiteboard, bis ich ein Problem völlig verstand. Erst dann konnte ich mit anderen Entwicklern über eine Lösung sprechen. Die Botschaft hier ist nicht zu predigen, wie Sie über Ihr Geschäft der Lösung von Problemen gehen sollten, sondern für alle, ihre Stärken kennen und was funktioniert, und verwenden Sie sie zu Ihrem Vorteil so viel wie möglich. Einige Phrasen sind wie Fingernägel auf einer Tafel für mich. Verwenden Sie es als Lehre Moment ist eins. (Warum ist es wie Fingernägel auf einer Tafel Weil, wenn youre in einer Mentoring-Rolle, sollten Sie in der Regel im Unterricht Moment-Modus sowieso, aber subtil). Heres ein anderes ich kann nicht wirklich erklären es in den Wörtern, aber ich verstehe es. Das klingt ein bisschen kalt, aber wenn eine Person wirklich kann nicht erklären, etwas in Worten, vielleicht sie nicht verstehen. 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. Read. 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. Menschen. 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.
Comments
Post a Comment