Jaka jest różnica między CTE a tabelą Temp?



Jaka jest różnica między Common Table Expression (CTE) a tabelą tymczasową?A kiedy powinienem używać jednego nad drugim?

**CTE**


```
WITH cte (Column1, Column2, Column3)
AS
(
     SELECT Column1, Column2, Column3
     FROM SomeTable
)

SELECT * FROM cte

```
**Tabela temp**


```
SELECT Column1, Column2, Column3
INTO #tmpTable
FROM SomeTable

SELECT * FROM #tmpTable

```
Jest to dość szerokie, ale dam ci tak ogólną odpowiedź, jak tylko mogę.

**CTE ...**

* Są niezindeksowalne (ale mogą używać istniejących indeksów na obiektach, do których istnieją odwołania)
* Nie można mieć ograniczeń
* Są zasadniczo jednorazowe `VIEW` s
* Trwać tylko do momentu uruchomienia następnego zapytania
* Może być rekurencyjny
* Nie masz dedykowanych statystyk (polegaj na statystykach dotyczących podstawowych obiektów)

**#Temp Tables ...**

* Są prawdziwe zmaterializowane tabele, które istnieją w tempdb
* Może być indeksowany
* Może mieć ograniczenia
* Trwaj w życiu obecnego POŁĄCZENIA
* Może odwoływać się do innych zapytań lub podprocedur
* Masz dedykowane statystyki generowane przez silnik

Jeśli chodzi o użycie każdego z nich, mają bardzo różne przypadki użycia.Jeśli masz bardzo duży zestaw wyników lub musisz odwołać się do niego więcej niż raz, umieść go w tabeli `#temp` .Jeśli ma być rekurencyjny, jednorazowy lub po prostu dla uproszczenia logicznego, preferowany jest `CTE` .

Ponadto należy `CTE`**nigdy nie używaj do wykonania**.Prawie nigdy nie przyspieszysz przy użyciu CTE, ponieważ znowu jest to widok jednorazowy.Możesz zrobić z nimi kilka fajnych rzeczy, ale przyspieszenie zapytania nie jest jednym z nich.
<p>ma więcej kolumn niż pokaż status tabeli; ale robi sztuczkę:</p><pre><code>select * from information\_schema.tables where table\_schema = database(); </code></pre><p>aktualizacja 2011-06-07 19:02</p><pre><code>select table\_name,engine,version,row\_format,table\_rows,avg\_row\_length, data\_length,max\_data\_length,index\_length,data\_free,auto\_increment, create\_time,update\_time,check\_time,table\_collation,checksum, create\_options,table\_comment from information\_schema.tables where table\_schema = database(); </code></pre><p>te zapytania działają, jeśli ustawisz bieżącą bazę danych.</p><p>możesz także kodować konkretną bazę danych:</p><pre><code>select table\_name,engine,version,row\_format,table\_rows,avg\_row\_length, data\_length,max\_data\_length,index\_length,data\_free,auto\_increment, create\_time,update\_time,check\_time,table\_collation,checksum, create\_options,table\_comment from information\_schema.tables where table\_schema = 'mysql'; </code></pre> druga pewnie jest dla sprawdzenia poprwanosci odpowiedzi, kucze nie pamietam dokladnie tych komend ale sprobuj skopjowac je z tej gotowej tabelki i ewentualnie cos pozmieniaj- a dokladniej t wspolrzedne pol zeby sie zgadzaly (wstaw tylko w pierwszych ramkach a reszte zlap za jeden rog i przeciagnij to ci sie skopiuje dla innych wynikow) ło panie. dobra. jeśli używasz entity framework, to wybierz wszystko, co jest w danej tabelce do zmiennej (i już masz dane 2 cache). sprawdzasz ile masz tam danych metodą count dostępną dla list. robisz tablicę dynamiczną i wrzucasz tam dane podzielone z listy. zwykły for od 0 do tego, ile zwróciło … <p>od <a href="http://msdn.microsoft.com/en-us/library/ms188029.aspx">books online</a> </p><blockquote> <p>format nowej\_tabeli określa się, oceniając wyrażenia na liście wyboru. kolumny w nowej\_tabeli są tworzone w kolejności określonej przez listę wyboru. każda kolumna w tabeli nowa\_ ma taką samą nazwę, typ danych, dopuszczalność nullab i wartość jak odpowiednie wyrażenie na liście wyboru. <strong> właściwość tożsamość kolumny jest przenoszona </strong>, z wyjątkiem warunków określonych w „praca z kolumnami tożsamości” w sekcji uwagi.</p> </blockquote><p>w dół strony:</p><blockquote> <p>po wybraniu istniejącej kolumny tożsamości do nowej tabeli nowa kolumna dziedziczy właściwość tożsamość, chyba że spełniony jest jeden z następujących warunków:</p> <ul> <li>instrukcja select zawiera funkcję join, group by lub funkcję agregującą.</li> <li>wiele instrukcji select jest łączonych za pomocą union.</li> <li>kolumna tożsamości znajduje się na liście wyboru więcej niż jeden raz.</li> <li>kolumna tożsamości jest częścią wyrażenia.</li> <li>kolumna tożsamości pochodzi ze zdalnego źródła danych.</li> </ul> <p>jeśli którykolwiek z tych warunków jest spełniony, kolumna jest tworzona nie null zamiast dziedziczenia właściwości tożsamość. jeśli w nowej tabeli wymagana jest kolumna tożsamości, ale taka kolumna nie jest dostępna lub chcesz mieć wartość początkową lub przyrostową inną niż kolumna tożsamości źródłowej, zdefiniuj kolumnę na liście wyboru za pomocą funkcji tożsamość. zobacz „tworzenie kolumny tożsamości za pomocą funkcji tożsamość” w sekcji przykłady poniżej.</p> </blockquote><p>więc ... teoretycznie można uciec:</p><pre><code>select id, val into copy\_from\_with\_id\_2 from with\_id union all select 0, 'test\_row' where 1 = 0; </code></pre><p>ważne byłoby skomentowanie tego kodu, aby go wyjaśnić, aby nie został usunięty następnym razem, gdy ktoś na niego spojrzy.</p> <p>kolejność kolumn ma znaczenie, więc jeśli (i tylko jeśli) kolejność kolumn jest zgodna, możesz na przykład:</p><pre><code>insert into items\_ver select * from items where item\_id=2; </code></pre><p>lub jeśli nie pasują, możesz na przykład:</p><pre><code>insert into items\_ver(item\_id, item\_group, name) select * from items where item\_id=2; </code></pre><p>ale poleganie na kolejności kolumn jest błędem, który może się zdarzyć (może się zmienić, podobnie jak liczba kolumn) - utrudnia także czytanie sql</p><p>nie ma dobrego „skrótu” - należy wyraźnie wymienić kolumny zarówno dla tabeli, do której wstawiasz, jak i zapytania, którego używasz dla danych źródłowych, np .:</p><pre><code>insert into items\_ver (item\_id, name, item\_group) select item\_id, name, item\_group from items where item\_id=2; </code></pre><p><em> dbfiddle <a href="http://dbfiddle.uk/?rdbms=postgres\_9.6&amp;fiddle=cec35746a8728841974621f20cc6aba0">tutaj</a> </em></p> <p>następujące czynności powinny załatwić sprawę:</p><pre><code>create table mytable (col1 float null, col2 nvarchar(30) null, col3 datetime null); go alter table mytable add constraint checkonlyonecolumnisnull check ( ( case when col1 is null then 0 else 1 end + case when col2 is null then 0 else 1 end + case when col3 is null then 0 else 1 end ) = 1 ) go </code></pre> <pre><code>postgres=&gt; \l list of databases name | owner | encoding | collate | ctype | access privileges +++++ postgres | postgres | utf8 | en\_us.utf-8 | en\_us.utf-8 | template0 | postgres | utf8 | en\_us.utf-8 | en\_us.utf-8 | =c/postgres + | | | | | postgres=ctc/postgres template1 | postgres | utf8 | en\_us.utf-8 | en\_us.utf-8 | =c/postgres + | | | | | postgres=ctc/postgres </code></pre><p> <a href="http://www.postgresql.org/docs/current/static/sql-grant.html">dokumenty dotyczące <code>grant</code></a> zawierają wyjaśnienie, jak interpretować dane wyjściowe. aby uzyskać określone uprawnienia w tabeli bieżącej bazy danych, użyj <code>\z mytable</code>.</p> <p>oto wyniki zapytania tabeli w <strong>druga kolumna indeksu wielokolumnowego</strong>.<br> efekty są łatwe do odtworzenia dla każdego. po prostu spróbuj w domu.</p><p>testowałem z postgresql <strong>9.0.5</strong> na debianie, używając średniej wielkości tabeli bazy danych z 23322 wierszami. implementuje relację n: m między tabelami <code>adr</code> (adres) i <code>att</code> (atrybut), ale tutaj nie ma to znaczenia. uproszczony schemat:</p><pre><code>create table adratt ( adratt\_id serial primary key , adr\_id integer not null , att\_id integer not null , log\_up timestamp(0) not null default (now())::timestamp(0) , constraint adratt\_uni unique (adr\_id, att\_id) ); </code></pre><p>ograniczenie <code>unique</code> skutecznie implementuje unikalny indeks. dla pewności powtórzyłem test z prostym indeksem i uzyskałem identyczne wyniki, jak oczekiwano.</p><pre><code>create index adratt\_idx on adratt(adr\_id, att\_id) </code></pre><p>tabela jest skupiona w <code>adratt\_uni</code> indeks i przed testem uruchomiłem:</p><pre><code>cluster adratt; analyze adratt; </code></pre><p>skanowanie sekwencyjne w poszukiwaniu zapytań na <code>(adr\_id, att\_id)</code> są tak szybkie, jak to tylko możliwe. indeks wielokolumnowy będzie nadal używany jako warunek zapytania w samej drugiej kolumnie indeksu.</p><p>uruchomiłem zapytania kilka razy, aby zapełnić pamięć podręczną i wybrałem najlepszy z dziesięciu przebiegów, aby uzyskać porównywalne wyniki.</p><h2>1. zapytanie za pomocą obu kolumn</h2><pre><code>select * from adratt where att\_id = 90 and adr\_id = 10; adratt\_id | adr\_id | att\_id | log\_up +++ 123 | 10 | 90 | 2008-07-29 09:35:54 (1 row) </code></pre><p>wyjście <code>explain analyze</code>:</p><blockquote> <pre><code>index scan using adratt\_uni on adratt (cost=0.00..3.48 rows=1 width=20) (actual time=0.022..0.025 rows=1 loops=1) index cond: ((adr\_id = 10) and (att\_id = 90)) total runtime: 0.067 ms </code></pre></blockquote><h2>2. zapytanie za pomocą pierwszej kolumny</h2><pre><code>select * from adratt where adr\_id = 10 adratt\_id | adr\_id | att\_id | log\_up +++ 126 | 10 | 10 | 2008-07-29 09:35:54 125 | 10 | 13 | 2008-07-29 09:35:54 4711 | 10 | 21 | 2008-07-29 09:35:54 29322 | 10 | 22 | 2011-06-06 15:50:38 29321 | 10 | 30 | 2011-06-06 15:47:17 124 | 10 | 62 | 2008-07-29 09:35:54 21913 | 10 | 78 | 2008-07-29 09:35:54 123 | 10 | 90 | 2008-07-29 09:35:54 28352 | 10 | 106 | 2010-11-22 12:37:50 (9 rows) </code></pre><p>wyjście <code>explain analyze</code>:</p><blockquote> <pre><code>index scan using adratt\_uni on adratt (cost=0.00..8.23 rows=9 width=20) (actual time=0.007..0.023 rows=9 loops=1) index cond: (adr\_id = 10) total runtime: 0.058 ms </code></pre></blockquote><h2>3. zapytanie za pomocą drugiej kolumny</h2><pre><code>select * from adratt where att\_id = 90 adratt\_id | adr\_id | att\_id | log\_up +++ 123 | 10 | 90 | 2008-07-29 09:35:54 180 | 39 | 90 | 2008-08-29 15:46:07 ... (83 rows) </code></pre><p>wyjście <code>explain analyze</code>:</p><blockquote> <pre><code>index scan using adratt\_uni on adratt (cost=0.00..818.51 rows=83 width=20) (actual time=0.014..0.694 rows=83 loops=1) index cond: (att\_id = 90) total runtime: 0.849 ms </code></pre></blockquote><h2>4. wyłącz indeksowanie i skanowanie bitmaps</h2><pre><code>set enable\_indexscan = off; select * from adratt where att\_id = 90 </code></pre><p>wynik analizy explain analyze:</p><blockquote> <pre><code>bitmap heap scan on adratt (cost=779.94..854.74 rows=83 width=20) (actual time=0.558..0.743 rows=83 loops=1) recheck cond: (att\_id = 90) -&gt; bitmap index scan on adratt\_uni (cost=0.00..779.86 rows=83 width=0) (actual time=0.544..0.544 rows=83 loops=1) index cond: (att\_id = 90) total runtime: 0.894 ms </code></pre></blockquote><pre><code>set enable\_bitmapscan = off select * from adratt where att\_id = 90 </code></pre><p>wyjście <code>explain analyze</code>:</p><blockquote> <pre><code>seq scan on adratt (cost=0.00..1323.10 rows=83 width=20) (actual time=0.009..2.429 rows=83 loops=1) filter: (att\_id = 90) total runtime: 2.680 ms </code></pre></blockquote><h2>wniosek</h2><p>zgodnie z oczekiwaniami indeks wielokolumnowy służy do zapytania tylko w drugiej kolumnie.<br> zgodnie z oczekiwaniami jest mniej skuteczny, ale zapytanie jest nadal <strong>x szybsze</strong> niż bez indeksu.<br> po wyłączeniu skanowania indeksów planista zapytań wybiera skanowanie sterty map bitowych, które wykonuje prawie tak samo szybko. dopiero po wyłączeniu tego wraca do skanowania sekwencyjnego.</p> <p>najlepszym rozwiązaniem, które sam odkryłem, było <code>desc table\_name</code> komenda. więcej informacji znajduje się na <em> <a href="http://www.hscripts.com/tutorials/mysql/listing-tables.php">wyświetl tabele mysql</a> </em>. to polecenie podaje opis jednej tabeli bazy danych, a dokładnie tego próbowałem się dowiedzieć.</p> <p>postgres ma bardzo elastyczną obsługę <a href="http://www.postgresql.org/docs/current/static/arrays.html">tablice</a> i <a href="http://www.postgresql.org/docs/current/static/rowtypes.html">typy złożone</a> . może to być coś, co próbujesz zrobić:</p><blockquote> <pre><code>create type my\_type as (val1 integer, val2 integer); </code></pre></blockquote><blockquote> <pre><code>create function my\_function(arr my\_type[]) returns text language plpgsql as $$ begin return arr::text; end;$$; </code></pre></blockquote><blockquote> <pre><code>select my\_function(array[row(1,2),row(3,4)]::my\_type[]); </code></pre> <pre> | moja\_funkcja | | :  <a href="http://dbfiddle.uk/?rdbms=postgres\_9.4&amp;fiddle=207971f904ff986920e88a9cd76bcab6">| {„(1,2)”, „(3,4)”} | </pre></blockquote><p><em> dbfiddle  (tutaj</a> </em></p>