Kategorie: "SQLServer"

Update für SQL Server 2017 (März 2019) - CU14

Hi,

new Update CU14 fpr SQL Server 2017 available

Ganze Geschichte »

After Update Trigger - Execute any Sp by inserting the command to a table

Hi,

habe meine T-SQL Script Sammlung um ein Teil erweitert:

2019_03_11_Excerpt_Batch_Trigger.sql.txt

zu finden hier:

Dieses Script erzeugt zwei Tabellen
ExecTable und ExecTable_History
Zudem erzeugt es einen einfachen After_Insert Trigger auf die Tabelle ExecTable.

Dies ermöglicht einem durch einen einfachen Insert eines Stored Procedure Textes in die Tabelle ExecTable, diesen Aufruf sofort auszuführen.
Der eingefügte Text wird in die Tabelle ExecTable_History übertragen, so dass die ExecTable Tabelle immer leer ist.

Hintergrund dieser einfachen Stored Proc ist PowerApps.

In PowerApps kann man ohne das Zusatzprogramm Flow keine Stored Procedure aufrufen. Diese Einschränkung kann man hierdurch umgehen.
Um PowerApps zusammen mit dem SQL Server zu verwenden, sind m.E. die einfache Verwendung von Stored Procedures eine sinnvolle (zwingende) Voraussetzung.

---------------------------- english text ------------------

I've enhanced my T-SQL Scripting samples

2019_03_11_Excerpt_Batch_Trigger.sql.txt

you find it here:

This simple script creates two tables and a simple After Update Trigger.

Usage: For being able to directly call any SP simply by inserting the command to the ExecTable
The AfterInsert trigger executes that command and moves the command to the ExecTable_History table,
so that the ExecTable table itself always is empty.

Reason: Powerapps does not allow to call a SP without FLOW integration.
I want to circumvent this limitation.

TABLES
------

ExecTable - To support Create table pattern (execute SP through table trigger) - contains after insert trigger
ExecTable_History " "

Experiment FileTable aborted unsuccessfully

english text at the end as usual

Hallo zusammen,

Um mit FileTable besser vertraut zu werden, wolle ich meine Ideen- und Beispielsammlung sowie meine MP4 / MP3 Sammlung in FileTable auf meinem SQL Server 2017 Express meines PCs einstellen.
Bevor ich richtig loslegen konnte - wurden alle Datenbanken, auf denen ich FileTable aktiviert hatte, ohne erfindlichen Grund mehrere Male in den Status "pending" versetzt, was zur Folge hatte, dass ich diese Datenbankenbanken vorerst nicht öffnen konnte. Warum FileTable sich mehrfach auf den PCs "aufgehängt" hat, weiss ich nicht. Wahrscheinlich hat eines der automatischen Updates dafür gesorgt. Es passierte innerhalb von wenigen Wochen 4 oder 5 mal, ohne dass ich bewusst etwas an den Einstellungen geändert hätte.

Ich will mir lieber nicht ausmalen, was für ein Chaos beim Kunden passiert, wenn dieser seine SQL Server Datenbank plötzlich nicht mehr öffnen kann.

Damit ist für mich das Thema FileTable gestorben.

Daher habe ich alles bzgl. FileTables deinstalliert.

Vorgehensweise:

Nach allg. Backup aller DBs und separater Sicherung der FileTable-Dateien - froh noch kein FileTable beim Kunden ausgeliefert zu haben, habe ich - in allen betroffenen Datenbanken
zuerst die FileTable-Tabelle gelöscht
In den Properties unter Filegroups den Filestream gelöscht und dann unter Options Filestream Non Transacted-Access auf Off gesetzt.

Danach mittels "SQL Server 2017 Configuration Manager" im SQL_Server_Express - Rechte Maustaste, Tab: Eigenschaften "Filestream" - die drei Häkchen entfernt und den SQL Server Express neu gestartet und gleich noch ein Backup der Datenbanken gemacht <und vorsichtshalber mit Restore ohne FileTable erfolgreich getestet>

Zudem ist es im Access-Umfeld nur bedingt nutzbar. Die Nachteile bei der Nutzung sind:
a) Ich konnte bei mir unter Windows 10 keine Ordner "per Hand" via Explorer anlegen, da - zumindest bei mir - immer zuerst der Ordner als "Neuer Ordner" angelegt wird, den der Explorer danach umbenennt. Dieses Umbenennen klappt bei FIleTable bei mir nicht.
b) In VBA: Der Kill-Befehl funktioniert nicht
c) In VBA: Der Dir() Befehl funktioniert nicht
d) Missing Feature (Doku): Doppelklick - Eine Datei die mittels Notpad oder Paint geöffnet wird kann man nicht per Doppeklick ansprechen.
e) Das Anlegen der Files: Wichtig - nie den vollen Pfad sondern "nur" den relativen Pfad verwenden - ist, da man mit den Werten aus der FileTable-View arbeiten muss um den Pfad zu bestimmen SEHR gewöhnungsbedürftig.

PS:

a) Da ich das Programm "SQL Server 2017 Configuration Manager" auf meinem Rechner nicht gefunden habe, habe ich mir eine
Verknüpfung unter dem Namen "SQL Server 2017 Configuration Manager" angelegt.
Ziel: C:\Windows\SysWOW64\SQLServerManager14.msc

b) So bekam ich den Pending Status wieder entfernt:

Alle User aus dem System entfernt
Mittels "SQL Server 2017 Configuration Manager" im SQL_Server_Express - Rechte Maustaste, Tab: Eigenschaften "Filestream" - die drei Häkchen entfernt und den SQL Server Express neu gestartet.
Mittels "SQL Server 2017 Configuration Manager" im SQL_Server_Express - Rechte Maustaste, Tab: Eigenschaften "Filestream" - die drei Häkchen wieder gesetzt und den SQL Server Express neu gestartet.
Rechner neu gebootet.
Danach waren im SSMS alle Datenbanken wieder "normal benutzbar".

Fazit:

Ich bin mittlerweile davon überzeugt, dass das FileStream-Feature <MS entwicklungsseitig> langfristig bei MS "wenig Überlebens-Chancen" hat, da es ein Grund wäre, die Datenbanken NICHT in die Cloud umzuziehen. Zudem ist es - zumindest bisher - nicht mal unter Linux erhältlich.

Wenn man denn unbedingt FileStream nutzen möchte, unbedingt nur und ausschliesslich in einer speziell dafür angelegten FileTable-Datenbank nutzen, wenn diese in den Status "pending" versetzt wird, dann sind die "Haupt-Datenbanken" nicht davon betroffen.


-------------------------------------- english text starts here ----------------------------------

Experiment - FileTable with SQL Server 2017 Express - Unsuccessfully aborted

Hello everybody,

In order to become more familiar with FileTable, I want to set up my ideas and sample collection as well as my MP4 / MP3 collection in FileTable on my SQL Server 2017 Express on my PC.
Before I got it right, all the databases where I had FileTable turned on, were stalled (Status: pending) several times for no good reason, which meant I could not open those databases for the time being. Why FileTable has "hung up" several times on the PCs, I do not know. Probably one of the automatic updates has ensured this. It happened within a few weeks 4 or 5 times without me consciously changing anything about the settings.

I would rather not imagine what a mess at the customer happens when he suddenly can no longer open its SQL Server databases.

That's why FileTable has died for me.

Therefore, I have uninstalled everything regarding FileTables.

Method:
After general backup of all DBs and separate backup of the FileTable files - glad I have not yet delivered a FileTable to the customer, I have - in all affected databases
first deleted the FileTable table
In the properties under Filegroups, delete the filestream and then set it to Off under Options Filestream "Non Transacted-Access".

Then with "SQL Server 2017 Configuration Manager" in the SQL_Server_Express - right mouse button, Tab: Properties "Filestream" - the three checkmarks removed and restarted the SQL Server Express and immediately made a backup of the databases <and, as a precaution, successfully tested with Restore without FileTable >


In addition, it is only partially usable in the Access environment. The disadvantages of using are:
a) I could create under Windows 10 no folder "by hand" via Explorer, because - at least for me - always first the folder as a "new folder" is created, which renames the Explorer thereafter. This renaming does not work for FIleTable with me.
b) In VBA: The kill command does not work
c) In VBA: The dir () command does not work
d) Missing Feature (Doku): Double-click - A file that is opened using Notpad or Paint can not be addressed by double-clicking.
e) Creating the files: Important - never use the full path but "only" the relative path - is VERY uncomfortable  because you have to work with the values ​​from the FileTable view to determine the path.

PS:

a) Since I did not find the program "SQL Server 2017 Configuration Manager" on my computer, I have one
link created under the name "SQL Server 2017 Configuration Manager".
Target: C: \ Windows \ SysWOW64 \ SQLServerManager14.msc

b) So I got the pending status removed again:

All users removed from the system
With "SQL Server 2017 Configuration Manager" in the SQL_Server_Express - right mouse button, Tab: Properties "Filestream" - the three check marks removed and the SQL Server Express restarted.
With "SQL Server 2017 Configuration Manager" in the SQL_Server_Express - right mouse button, tab: Properties "Filestream" - the three check marks are set again and the SQL Server Express is restarted.
Computer rebooted.
After that all databases were again "normally usable" in the SSMS.

Conclusion:

I'm now convinced that the FileStream <MS development-side> feature has little chance of surviving on MS in the long term, as it would be a good reason NOT to move the databases to the cloud. In addition, it is - at least so far - not even available under Linux.

If you absolutely want to use FileStream, necessarily use only and exclusively in a specially created FileTable database, if this is put in the status "pending", then the "main databases" are not affected.

mfg Klaus Oberdalhoff

Update für SQL Server 2017 (December 2018) - CU13

Hi,

Update für SQL Server 2017 (December 2018) - CU13  - 18-12-2018

https://support.microsoft.com/en-us/help/4466404/cumulative-update-13-for-sql-server-2017

Nachtrag für das Update für SQL Server 2017 (December 2018) - CU13  - 18-12-2018

 

On-demand hotfix update package for SQL Server 2017 CU13

https://download.microsoft.com/download/4/3/9/439277DD-1041-48F8-A5E5-FA6493E44BC5/SQLServer2017-KB4483666-x64.exe

 

Offizieller Microsoft-Update-Katalog  (fast sämtlicher MS Produkte) 

(noch ohne SQL Server Hotfixes)

http://www.catalog.update.microsoft.com/Home.aspx

 

Inoffizieller (aber aktueller) SQL Server Blog mit Hotfixes

https://sqlserverbuilds.blogspot.com/

T-SQL - RANKING Windowing Functions for Beginners

as usual: english part at the end

Hi,

Der Grund dieses Blog-Eintrages ist schlicht der, dass ich mit dem Grundverständnis der verschienen "Window RANKING" Funktionen im SQL Server so meine Probleme hatte, da ich die diversen Erklärungen nicht gleich verstanden habe. Und ich dachte mir, vielleicht geht es auch Anderen so ...

RANKING Windowing Functionen

- DENSE_RANK
- RANK
- ROW_NUMBER
- NTILE

Alle mit der OVER Klausel

es gibt noch weitere Windowing Functions mit der OVER Klausel, diese sind jedoch mehrheitlich eher statistischer Natur.

OVER-Klausel
- ORDER BY bestimmt die Reihenfolge der erzeugten Nummern

- PARTITION BY - Optional - definiert die Anzahl der verwendeten Sets / Partitions.
wenn PARTITION BY angegeben wird, startet die Reihenfolge bei jeder neuen Partition wieder bei 1
wenn kein PARTITION BY angegeben wird, werden alle ausgewählten Datensätze als ein einziges Set / Partition betrachtet.

- - - - - - - -

- ROW_NUMBER
Wie der Name besagt, Zeilennummern - ORDER BY bestimmt die Reihenfolge, wenn PARTITION BY,
dann startet die Nr bei einer neuen Partition wieder bei 1

- DENSE_RANK
Erzeugt pro Partition und Sortierung eine eigene - fortlaufende - Nummer ohne Lücken in der Numerierung

- RANK
Erzeugt pro Partition und Sortierung eine eigene - fortlaufende - Nummer mit Lücken in der Numerierung
wobei die Lückengröße immer der Anzahl der gleichen Daten entspricht. 
Geeignet für die Erzeugung von Siegplätzen bei Wettkämpfen, wenn es zwei 1. Plätze gibt, ist das Ranking
1 1 3 und es gibt somit keinen 2. Platz

 

- NTILE
teilt eine Tabelle / Abfrage in exakt n gleichgroße Stücke (plus Rest)

NTILE is wichtig zum Laden von "Junks" von Datensätzen
(z.B. kann man bei PowerApps max. 500 Datensätze auf einmal downloaden / bearbeiten)

Wenn also nicht die Anzahl der Teilstücke interessieren, sondern nur die max. Anzahl pro Datensätze pro Teilstück:
Einfach die Gesamtanzahl der Datensätze durch die gewünschte Anzahl dividieren und diesen Wert als @ntile_value verwenden ...

Wenn man ein bestehendes Set von ungefähr dieser Größe hat, z.b. alle Tage eines Jahres, dann ist Dense_Rank besser geeignet
(da in diesem Fall die unterschiedliche Set-Größe wg. eines Schaltjahres automatisch berücksichtigt wird)

Beispiel:

Für dieses Beispiel würde eine Tabelle [_tblAlleTage] benötigt,
aber das Beispiel sollte auch so eingängig sein.

extrem stark gekürzt

Tabelle [_tblAlleTage]

JJJJMMTT dtDatum JahrNr Quartal MonatNr TagNr Wochentag ID
19900101 1990-01-01 00:00:00.000 1990 1 1 1 1 0
19900102 1990-01-02 00:00:00.000 1990 1 1 2 2 1
19900103 1990-01-03 00:00:00.000 1990 1 1 3 3 2
19900104 1990-01-04 00:00:00.000 1990 1 1 4 4 3
19900105 1990-01-05 00:00:00.000 1990 1 1 5 5 4
19900106 1990-01-06 00:00:00.000 1990 1 1 6 6 5
19900107 1990-01-07 00:00:00.000 1990 1 1 7 7 6
19900108 1990-01-08 00:00:00.000 1990 1 1 8 1 7
19900109 1990-01-09 00:00:00.000 1990 1 1 9 2 8
19900110 1990-01-10 00:00:00.000 1990 1 1 10 3 9
....

use nuetzliche_Tabellen
go

Declare @ntile_value as int

select @ntile_value = Count( Distinct JahrNr) from dbo.[_tblAlleTage]
print @ntile_value -- 110

--select @ntile_value = Count(*) from dbo.[_tblAlleTage]
--set @ntile_value = @ntile_value / 480

SELECT *
FROM
(
SELECT JJJJMMTT,
[ID],
jahrnr,
NTILE(@ntile_value) OVER(ORDER BY [JJJJMMTT]) AS NTILE_X,
DENSE_RANK() OVER(ORDER BY JahrNr) AS Dense_Rank_X,
RANK() OVER(ORDER BY JahrNr) AS Rank_X,
ROW_NUMBER() OVER(ORDER BY JJJJMMTT) AS Row_Number_X,
ROW_NUMBER() OVER(PARTITION BY JahrNr ORDER BY JJJJMMTT) AS Tag_desJahres_X
FROM dbo.[_tblAlleTage]
) a
WHERE ....

Resultset:

JJJJMMTT ID jahrnr NTILE_X Dense_Rank_X Rank_X Row_Number_X Tag_desJahres_X
19900101 0 1990 1 1 1 1 1
19900102 1 1990 1 1 1 2 2
19900103 2 1990 1 1 1 3 3
19900104 3 1990 1 1 1 4 4
19900105 4 1990 1 1 1 5 5
....
19901230 363 1990 1 1 1 364 364
19901231 364 1990 1 1 1 365 365
19910101 365 1991 1 2 366 366 1
19910102 366 1991 2 2 366 367 2
19910103 367 1991 2 2 366 368 3
19910104 368 1991 2 2 366 369 4

Man kann auch Row_Numbers pro NTILE vergeben, ist aber etwas "tricky":

DECLARE @ntile_value AS INT;

select @ntile_value = Count(*) from dbo.[_tblAlleTage]
set @ntile_value = @ntile_value / 480

SELECT * FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY a.[NTILE_X] ORDER BY a.Row_Number_X) AS NTILE_ROW_X

FROM
(
SELECT JJJJMMTT,
[ID],
jahrnr,
DENSE_RANK() OVER(ORDER BY JahrNr) AS Dense_Rank_X,
RANK() OVER(ORDER BY JahrNr) AS Rank_X,
ROW_NUMBER() OVER(ORDER BY JJJJMMTT) AS Row_Number_X,
NTILE(@ntile_value) OVER(ORDER BY [JJJJMMTT]) AS NTILE_X
FROM dbo.[_tblAlleTage]
) a
) b
WHERE ...

------------------------------------------ english part ----------------------------------


The reason of this blog entry is simply that I had my problems with the basic understanding of the different "Window RANKING" functions in the SQL Server, because I did not understand the required explanations immediately. And I thought to myself, maybe others are doing the same ...

RANKING windowing functions

- DENSE_RANK
- RANK
- ROW_NUMBER
- NTILE

All with the OVER clause

There are other windowing functions with the OVER clause, but these are mostly statistical in nature.

OVER clause
- ORDER BY determines the order of the generated numbers

- PARTITION BY - Optional - defines the number of used sets / partitions.
if PARTITION BY is specified, the order will start at 1 for each new partition,
if no PARTITION BY is specified, all selected records will be considered as a single set / partition.

- - - - - - - -

- ROW_NUMBER
As the name implies, row numbers - ORDER BY determines the order
when PARTITION BY, then the number restarts again at 1 at each new partition

- DENSE_RANK
Creates a separate - consecutive - number without gaps for each partition and sorting

- RANK
Creates a separate - consecutive - number with gaps for each partition and sorting
where the gap size always equals the number of the same data.
Suitable for generating victory places in competitions, if there are two 1st places, the ranking is
1 1 3 and there is no 2nd place

- NTILE
divide a table / query into exactly n equal pieces (plus remainder)

Important if loading a complete "junk" of records
(For example, with PowerApps, you can download / edit a maximum of 500 records at once)

So if not the number of cuts interested, but only the max. Number per data set per section:
Simply divide the total number of records by the desired number and use that value as @ntile_value ...

If one has an existing set of about this size, e.g. every day of the year, then Dense_Rank is better suited
(since it automatically takes leap years into account - in this sample).

Example:

For this example, a table would be needed [_tblAlleTage]
but the example should also be so catchy.

extremely shortened

Tabelle [_tblAlleTage]

JJJJMMTT dtDatum JahrNr Quartal MonatNr TagNr Wochentag ID
19900101 1990-01-01 00:00:00.000 1990 1 1 1 1 0
19900102 1990-01-02 00:00:00.000 1990 1 1 2 2 1
19900103 1990-01-03 00:00:00.000 1990 1 1 3 3 2
19900104 1990-01-04 00:00:00.000 1990 1 1 4 4 3
19900105 1990-01-05 00:00:00.000 1990 1 1 5 5 4
19900106 1990-01-06 00:00:00.000 1990 1 1 6 6 5
19900107 1990-01-07 00:00:00.000 1990 1 1 7 7 6
19900108 1990-01-08 00:00:00.000 1990 1 1 8 1 7
19900109 1990-01-09 00:00:00.000 1990 1 1 9 2 8
19900110 1990-01-10 00:00:00.000 1990 1 1 10 3 9
....

use nuetzliche_Tabellen
go

Declare @ntile_value as int

select @ntile_value = Count( Distinct JahrNr) from dbo.[_tblAlleTage]
print @ntile_value -- 110

--select @ntile_value = Count(*) from dbo.[_tblAlleTage]
--set @ntile_value = @ntile_value / 480

SELECT *
FROM
(
SELECT JJJJMMTT,
[ID],
jahrnr,
NTILE(@ntile_value) OVER(ORDER BY [JJJJMMTT]) AS NTILE_X,
DENSE_RANK() OVER(ORDER BY JahrNr) AS Dense_Rank_X,
RANK() OVER(ORDER BY JahrNr) AS Rank_X,
ROW_NUMBER() OVER(ORDER BY JJJJMMTT) AS Row_Number_X,
ROW_NUMBER() OVER(PARTITION BY JahrNr ORDER BY JJJJMMTT) AS day_of_year_X
FROM dbo.[_tblAlleTage]
) a
WHERE ....

Resultset:

JJJJMMTT ID jahrnr NTILE_X Dense_Rank_X Rank_X Row_Number_X day_of_year_X
19900101 0 1990 1 1 1 1 1
19900102 1 1990 1 1 1 2 2
19900103 2 1990 1 1 1 3 3
19900104 3 1990 1 1 1 4 4
19900105 4 1990 1 1 1 5 5
....
19901230 363 1990 1 1 1 364 364
19901231 364 1990 1 1 1 365 365
19910101 365 1991 1 2 366 366 1
19910102 366 1991 2 2 366 367 2
19910103 367 1991 2 2 366 368 3
19910104 368 1991 2 2 366 369 4

You can also assign Row_Numbers per NTILE, but it's a bit tricky:

DECLARE @ntile_value AS INT;

select @ntile_value = Count(*) from dbo.[_tblAlleTage]
set @ntile_value = @ntile_value / 480

SELECT * FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY a.[NTILE_X] ORDER BY a.Row_Number_X) AS NTILE_ROW_X

FROM
(
SELECT JJJJMMTT,
[ID],
jahrnr,
DENSE_RANK() OVER(ORDER BY JahrNr) AS Dense_Rank_X,
RANK() OVER(ORDER BY JahrNr) AS Rank_X,
ROW_NUMBER() OVER(ORDER BY JJJJMMTT) AS Row_Number_X,
NTILE(@ntile_value) OVER(ORDER BY [JJJJMMTT]) AS NTILE_X
FROM dbo.[_tblAlleTage]
) a
) b
WHERE ...

mfg Klaus

1 3 4 5 ...6 ...7 8