7
MSSQL WITH in View
Moin,
WITH ist ein undankbares Schlüsselwort, vor allem für Google
Mir sind folgende Möglichkeiten bekannt:
und
Kann ich eine View mit SCHEMABINDING erstellen und dennoch WITH in der Syntax verwenden?
Das wirft einen Syntaxfehler. Er stört sich jetzt nicht mal am Alias "t" und ich wüsste jetzt auch nicht warum das nicht gehen sollte, einzig die Syntax gibt das scheinbar nicht her. In diesem Fall mache ich kein CTE im WITH, kann meinen Select also auch anders aufbauen aber vielleicht kennt hier jemand einen Weg?
WITH ist ein undankbares Schlüsselwort, vor allem für Google
Mir sind folgende Möglichkeiten bekannt:CREATE VIEW ...
WITH SCHEMABINDING
AS
SELECT ....CREATE VIEW ...
AS
WITH t AS (
SELECT ....CREATE VIEW ...
WITH SCHEMABINDING
AS
WITH t AS (
SELECT ....
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 7259172032
Url: https://administrator.de/contentid/7259172032
Printed on: April 27, 2024 at 06:04 o'clock
7 Comments
Latest comment
https://stackoverflow.com/questions/1413516/can-you-create-nested-with-c ...
Nested WITH Komma separiert? Wie wäre es mit der Richtung?
Leider fällt mir zu SCHEMABINDING nichts gescheites ein.
Nested WITH Komma separiert? Wie wäre es mit der Richtung?
WITH x AS
(
SELECT * FROM MyTable
),
y AS
(
SELECT * FROM x
)
SELECT * FROM yLeider fällt mir zu SCHEMABINDING nichts gescheites ein.
Zitat von @ukulele-7:
Das wirft einen Syntaxfehler. Er stört sich jetzt nicht mal am Alias "t" und ich wüsste jetzt auch nicht warum das nicht gehen sollte, einzig die Syntax gibt das scheinbar nicht her. In diesem Fall mache ich kein CTE im WITH, kann meinen Select also auch anders aufbauen aber vielleicht kennt hier jemand einen Weg?
Das wirft einen Syntaxfehler. Er stört sich jetzt nicht mal am Alias "t" und ich wüsste jetzt auch nicht warum das nicht gehen sollte, einzig die Syntax gibt das scheinbar nicht her. In diesem Fall mache ich kein CTE im WITH, kann meinen Select also auch anders aufbauen aber vielleicht kennt hier jemand einen Weg?
Dass ein Syntaxfehler geworfen wird und dass dazu nichts Gescheites einfällt, liegt aus meiner Sicht in der Natur der Sache der Schemabindung und ist demgemäß zwingend. Denn bei dieser Art Abfragen handelt es sich - ich will es etwas vereinfacht ausdrücken - um materialisierte / deterministische Abfragen. Das und nur das sind auch diejenigen Abfragen, die indiziert werden können. Immerhin wird die Abfrage an die zugrundeliegende(n) Tabelle(n) gebunden. Die Tabelle kann aufgrund der Bindung auch nicht mehr geändert werden, ohne zuvor die Abfrage zu löschen. Diese Bindung führt dann vor allem zu einer extrem starken Syntaxstrenge - alles muss in der (einzigen) SELECT-Definition enthalten sein (Der Erinnerung nach sind nicht einmal die Stellvertreter-Abkürzungen (mit AS) erlaubt, sondern "FQDN" beginnend mit Datenbankname und dbo). Sogar das Einbinden von Unterabfragen ist unter diesen Vorgaben nur bedingt möglich.
In meiner Anfangszeit beim Umstieg von MS Access auf SQL Server hatte ich hinsichtlich der indizierten Views die illusorische Vorstellung, ich könnte wegen der Indizierung spürbare Leistungsverbesserungen erreichen. Wegen der Syntaxstrenge und dem Determinationszwang war es irgendwie eine Qual. Zudem war in den meisten der relevanten Fälle eine Deterministik der Abfrage ausgeschlossen und konnte auch nicht durch geschickte Veränderung der Abfragedefinition erreicht werden. Bei näherer Beschäftigung mit dieser Art Abfrage leuchtet das alles aber auch restlos ein.
Letztlich bin ich dann auf Computed Columns, Table-Functions, Proceduren etc. umgeschwenkt und dort konnte ich diejenigen Leistungspotentiale heben, die ich heben wollte und auch erhoffte.
Dort - bei den Anforderungen der Schemabindung - liegt auch der Grund, warum ich seither so gut wie gar nicht den CTE-Ansatz mit WITH genutzt habe. Hinzu kommt aber auch, dass die Fälle, in denen das WITH-Statement zur Vermeidung der mehrfachen Wiederholung der Syntax derselben Unterabfrage in einem SELECT-Konstrukt hilfreich sein könnte, äußerst selten ist. Ein weiterer Grund ist die leichtere (partielle) Prüfbarkeit eines komplexen SELECT-Konstruks (von innen nach außen) im SQL Studio, wenn auf das WITH-Statement verzichtet wird. Freilich steht dem der Nachteil gegenüber, dass ein mehrfacher Aufruf derselben Unterabfrage bei Änderungen indentisch gehalten werden muss.
Viele Grüße
HansDampf06
1
Also ich nutze jetzt die selbe Abfrage nur anders zusammen gesetzt, vereinfacht so geht es:
oder eben
Ich sehe keinen Grund warum das nicht auch in der 2ten Variante schemagebunden laufen könnte...
Ich brauche die Schemabindung übrigens für RLS, da komme ich in dem Fall nicht drum herum. Ansonsten würde ich sie auch weg lassen.
CREATE VIEW v
WITH SCHEMABINDING
AS
SELECT t.s
FROM (
SELECT s
FROM dbo.tabelle
) tCREATE VIEW v
--WITH SCHEMABINDING
AS
WITH t AS (
SELECT s
FROM dbo.tabelle
)
SELECT s
FROM tIch brauche die Schemabindung übrigens für RLS, da komme ich in dem Fall nicht drum herum. Ansonsten würde ich sie auch weg lassen.
Vielleicht noch ein Erfahrungstipp: Von MS Access kommend war ich es gewöhnt. Häufiger verwendete Abfragen als View abzulegen und diese View dann an den benötigten Stellen aufzurufen. Bei dem Wechsel auf SQL Server habe ich das selbstredend zunächst beibehalten. Bei komplexen SELECT-Konstrukten war ich aber erstaunt, dass die Abfrageleistung erkennbar bei der Verwendung von solchen (ausgelagerten Unter-)Views einbrach, während die View für sich genommen durchaus schnell war. Der Grund liegt darin, dass der Analyzer / Optimizer solche Views nur bedingt bis gar nicht optimiert in das aktuelle Konstrukt einbinden kann. Erst die direkte Erweiterung des Konstrukts um die ausgelagerte (Unter)View brachte die erwartete Leistung.
Prüfe doch noch einmal ganz genau, ob Du für Deine konkrete Abfrage tatsächlich diese Materialisierung / Determinisierung benötigst. Aus meiner Erfahrung heraus bin ich da jedenfalls eher skeptisch.
Sofern Du eine Indexierung für die View verwenden möchtest, bedenke zugleich, dass jeder Schreibvorgang in einer zugrundeliegenden Tabelle sofort auch eine Anpassung und den damit verbundenen Schreibaufwand für die View-Indexierung auslöst. Das kann Datenoperationen in der Tabelle erhebllich verlangsamen. Für mich war das ein weiterer wesentlicher Grund, letztlich von schemagebundenen Views die Finger zu lassen.
Im Übrigen: Wenn in SQL Studio Änderungen an irgendeinem Datenbankobjekt vorgenommen werden, von dem andere Datenbankobjekte abhängen, dann meckert SQL Studio, und zwar auch dann, wenn keine explizite Schemabindung definiert ist. Ich gehe davon aus, dass SQL Server selbst diese Abhängigkeiten erkennt/prüft. Wenn also die Schemabindung dem Veränderungsschutz dienen soll, könntest Du prüfen, ob dieser Veränderungsschutz auch ohne Schemabindung besteht.
Viele Grüße
HansDampf06
Ich sehe keinen Grund warum das nicht auch in der 2ten Variante schemagebunden laufen könnte...
Es ist bei diesem Konstrukt keine aus sich heraus definierte Abfrage. Das WIHT-Statement lagert nunmal eine Unterabfrage bei der Definiton aus dem eigentlichen SELECT-Konstrukt aus. Nur noch der Stellvertreter ist in der (eigentlichen) Abfragedefinition enthalten. Das ist eben die Syntaxstrenge bei schemagebundenen Views.Ich brauche die Schemabindung übrigens für RLS, da komme ich in dem Fall nicht drum herum. Ansonsten würde ich sie auch weg lassen.
Wenn Du die View unter DATENBANKNAME.dbo.Abfragename speicherst, ist sie im weitesten Sinne ans (Datenbank)Schema gebunden. Das WITH-SCHEMABINDING-Statement meint darüber hinausgehend die Materialisierung / Determinisierung der Abfrage in Bezug auf die verwendeten Tabellen und (materialisierten / deterministischen) Unterabfragen.Prüfe doch noch einmal ganz genau, ob Du für Deine konkrete Abfrage tatsächlich diese Materialisierung / Determinisierung benötigst. Aus meiner Erfahrung heraus bin ich da jedenfalls eher skeptisch.
Sofern Du eine Indexierung für die View verwenden möchtest, bedenke zugleich, dass jeder Schreibvorgang in einer zugrundeliegenden Tabelle sofort auch eine Anpassung und den damit verbundenen Schreibaufwand für die View-Indexierung auslöst. Das kann Datenoperationen in der Tabelle erhebllich verlangsamen. Für mich war das ein weiterer wesentlicher Grund, letztlich von schemagebundenen Views die Finger zu lassen.
Im Übrigen: Wenn in SQL Studio Änderungen an irgendeinem Datenbankobjekt vorgenommen werden, von dem andere Datenbankobjekte abhängen, dann meckert SQL Studio, und zwar auch dann, wenn keine explizite Schemabindung definiert ist. Ich gehe davon aus, dass SQL Server selbst diese Abhängigkeiten erkennt/prüft. Wenn also die Schemabindung dem Veränderungsschutz dienen soll, könntest Du prüfen, ob dieser Veränderungsschutz auch ohne Schemabindung besteht.
Viele Grüße
HansDampf06
Moin ukulele,
was bekommst Du denn für einen Fehler? Wenn ich Dein Skrip inkl. schemabinding ausführe, funktioniert das:
Ich habe hier den SQL Server 2017.
An den beiden with dürfte es also nicht liegen, da muß noch was anderes sein.
Gruß, Mad Max
was bekommst Du denn für einen Fehler? Wenn ich Dein Skrip inkl. schemabinding ausführe, funktioniert das:
create table tabelle (s int)
go
CREATE VIEW v
WITH SCHEMABINDING
AS
WITH t AS (
SELECT s
FROM dbo.tabelle
)
SELECT s
FROM t
goIch habe hier den SQL Server 2017.
An den beiden with dürfte es also nicht liegen, da muß noch was anderes sein.
Gruß, Mad Max
Zitat von @MadMax:
Ich habe hier den SQL Server 2017.
An den beiden with dürfte es also nicht liegen, da muß noch was anderes sein.
Ich habe hier den SQL Server 2017.
An den beiden with dürfte es also nicht liegen, da muß noch was anderes sein.
Hier ist es SQL Server für Linux 2019 in der aktuell(st)en Version.
Ich habe mir die hier vorhandene Probier-/Test-View gegriffen - sie hat das aktive WITH-SCHEMABINDING-Statement. Das damit vor langer Zeit getestete SELECT-Konstrukt verbindet drei Tabellen über ein INNER JOIN und gibt von zwei der Tabellen ausgewählte Spalten zurück.
Das CREATE-Konstrukt dieser View habe ich für eine zweite Test-View geklont und dabei die dritte Tabelle in ein WITH-Statement ausgelangert. Wie bei @MadMax sind die Ausführung des CREATE-Konstrukts als auch der View selbst erfolgreich.
Im zweiten Schritt habe ich die zweite Tabelle als zweites Alias in das WITH-Statement ausgelagert. Wieder alles erfolgreich.
Schließlich sind im dritten Schritt alle Tabellen ausgelagert. Wie zuvor alles erfolgreich.
Welche genaue Fehlermeldung bekommst Du denn? Hast Du irgendwo ein Sternchen als Platzhalter im SELECT-Konstrukt? Wird auf eine andere View zugegriffen, die selbst nicht schemagebunden ist?
Viele Grüße
HansDampf06
Zitat von @MadMax:
Ich habe hier den SQL Server 2017.
An den beiden with dürfte es also nicht liegen, da muß noch was anderes sein.
Du hast tatsächlich Recht. Habe gestern Abend nochmal alles umgebaut und es geht jetzt auch, keine Ahnung wo mein Fehler lag.create table tabelle (s int)
go
CREATE VIEW v
WITH SCHEMABINDING
AS
WITH t AS (
SELECT s
FROM dbo.tabelle
)
SELECT s
FROM t
goIch habe hier den SQL Server 2017.
An den beiden with dürfte es also nicht liegen, da muß noch was anderes sein.