Kategorie: "SQLServer"

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

List invalid table and fieldnames - Regular Expressions used

English version at the end - as usual

Hi,

ich musste heute testen, ob bzw. welche Feld- und Tabellennamen "ungültige" Zeichen (wie Leerzeichen etc.) enthalten.
Bei dieser Gelegenheit habe ich "Regular Expressions" eingesetzt.
Das Ergebnis habe ich in eine Access-Demo-Datenbank "gegossen".

Diese Demo-Datenbank ist in meinem Ondrive im Verzeichnis Accesss_DBs unter RegEx_ListAllFieldnames.accdb zu finden

Diese kleine Datenbank zeigt einige Dinge:
a) Einfache Verwendung "Regular Expressions" (regulärer Ausdrücke)
b) Auflistung aller Tabellen und Feldnamen in MSAccess
c) Auflistung aller Tabellen und Feldnamen in MSSQL Server (als View)
d) Wie kann man testen, ob Tabellen- und Feldnamen nicht "verbotene" Zeichen enthalten, insbesondere "Leerzeichen", "Bindestrich", "Schrägstrich" usw.? Die deutschen Umlaute sind zulässig und es werden korrigierte Feld- und Tabellennamen vorschlagen (ungültige Zeichen werden durch Unterstrich ersetzt)

---------------------------------------------------------------------

a) Regular Expressions: Solange das vollständige VBSCRIPT installiert ist, funktioniert dies, da RegEx Teil von VBSCRIPT ist
Das Modul "mdl_Regular_Expressions" enthält zwei funktionsbereite Funktionen die sowohl die Auswahl als auch die Ersetzung von Zeichenfolgen mit Regex zeigen.
Die Abfragen "... Invalid..names" zeigen, wie beide verwendet werden.

b) Das Modul mdl_ListAllAccFieldNames erstellt die Tabelle _tblTabFelder, die alle Tabellen- und Feldnamen von MSAccess-Tabellen enthält
 - und bei korrekter Verknüpfung auch die verknüpften Tabellen. Leider sind die vorhandenen MSAccess Meta-Daten nicht direkt als Abfrage verfügbar.

c) Das Modul INFO__SQL_VIEW___qry_All_Tables_Columns enthält eine MSSQL-View, in der alle Tabellen- und Feldnamen der MSSQL-Datenbank erstellt werden.

d) Die 4 "qry ... Invalid ... Names" Abfragen zeigen nur diejenigen Feldnamen, die "falsche" Werte besitzen, an. In der letzten Spalte wird eine "korrigierte Version" angezeigt.
Wie dies ausgeführt wird, wird im Modul mdl_Regular_Expressions angezeigt, da jede Abfrage beide Funktionen enthält.

 Makro Autoexec: Es ruft einfach automatisch die Funktion "TableInfo_AllTabs" des Moduls mdl_ListAllAccFieldNames auf
Es erstellt und füllt die Tabelle "_tblTabFelder", mit der Sie die beiden MSAccess-Abfragen ad hoc ausführen können.

 Die verknüpfte Tabelle "qry_All_Tables_Columns" funktioniert nicht direkt für Sie. Sie müssen zuerst die View auf dem MSSQL-Server erstellen und dann die View als Tabelle in MSAccess neu verknüpfen.

mfg Klaus

-------------------------------------------------------------------------------------------------------------------------------------

Hi,

Today I had to test whether or which field and table names contain "invalid" characters (such as spaces, etc.).
On this occasion I used "Regular Expressions".
I poured the result into an Access demo database.

This demo database can be found in my OneDrive in the Accesss_DBs directory under RegEx_ListAllFieldnames.accdb

This small Database will show several things:
a) Easy using of Regular Expressions
b) Listing of all Tables and Fieldnames in MSAccess
c) Listing of all Tables and Fieldnames in MSSQL Server (as View)
d) How to test table- and field-names to not contain "forbidden" characters, especially "blank", "hyphen", "slash" etc. The German umlauts are allowed
and suggest corrected field- and table-names (invalid chars are replaced by Underline)

---------------------------------------------------------------------

a) Regular Expressions: As Long as the complete VBSCRIPT is installed on the system, this will work, as RegEx is part of VBSCRIPT
The module "mdl_Regular_Expressions" contains two ready to use functions to select and replace strings with Regex
The queries "...Invalid..names" show how to use both functions.

b) The module mdl_ListAllAccFieldNames creates the table _tblTabFelder which containes all table- and field-names of MSAccess tables
- and if correctly linked - also the linked tables. Unfortunately, the existing MSAccess meta-data is not directly available as a query.

c) The INFO__SQL_VIEW___qry_All_Tables_Columns module containes an SQL Server View which creates all table- and field-names of the MSSQL database.

d) The 4 "qry...Invalid...names" Queries show only incorrect names, and the last column shows the "corrected version"
how its done is showed in the module mdl_Regular_Expressions, as each query contains both functions.

Macro Autoexec: It just automatically calls the function "TableInfo_AllTabs" of module mdl_ListAllAccFieldNames
The table "_tblTabFelder" is created and filled, so that you can run the two MSAccess queries ad hoc ...

The linked table "qry_All_Tables_Columns" will not work for you directly, you first have to create the view on the server and then relink the view as table in MSAccess.

best regards Klaus

CU Update für SQL 2017 und für SSMS

Hallo,

SSMS - CHeck Update ... 17.8.1 

lt. https://sqlserverbuilds.blogspot.com/

 

Build File version KB / Description Release Date
14.0.3030.27 2017.140.3030.27 4341265 Cumulative update 9 (CU9) for SQL Server 2017  Latest CU July 18, 2018 *new

FileTable - Externe Dateien im SQL Server verarbeiten - ganz einfach

Hallo,

sofern man (als Admin) Zugriff auf einen SQL Server 2012 (oder Neuer) unter Windows Prem (also weder AZURE noch LINUX) hat, (auch in der Express Edition) kann man sehr einfach und bequem FileTable verwenden. Dabei handelt es sich um eine Windows / SQL Server Technologie, die einem ermöglicht, Dateien in einem speziellen Directory im Betriebssystem "ganz normal" zu verwalten (erstellen / ändern / löschen), wobei im SQL Server  eine eigene Tabelle mitführt wird, in der automatisch diese Änderungen mit protokolliert werden, d.h. man hat im SQL Server eine Tabelle, die in Echtzeit - immer und automatisch aktuell - Auskunft über Dateien incl. Pfad gibt.

Was in meinen Augen eindeutig für den FileTable Ansatz spricht (wenn die Grundvoraussetzungen stimmen/gegeben sind), ist der minimalistische Erstellungs- sowie der WESENTLICH geringere Programmieraufwand als mit klassischem VBA Blobs ... (Warum soll ich mich mit ADO Stream o.ä. "rumärgern", wenn der SQL Server mir fast die gesamte Arbeit abnimmt ... <g>)  Zudem: Die "extern" gespeicherten Daten gehen nicht zu Lasten der DB-Größe, die ja bei der Express Edition bekanntlich auf 10 GB limitiert ist.
 
Wenn ich mit externen Dateien in Access zu tun habe, dann meistens in Zusammenhang mit zwei Anwendungsfällen:
a) Der Kunde möchte einem Vorgang/Person eine bestehende Datei (Bild, Dok, Vorlage, etc.) zuordnen
b) Der Kunde erzeugt ein Dokument, (Brief, PDF, Bild, Musikstück ...) das automatisch einem Vorgang zugeordnet werden soll....
In beiden Fällen eignet sich FileTable ganz hervorragend dafür. (PS:In  VBA - Application.FollowHyperlink Me!Pfadname - ist dein Freund)...

D.h. man hat im SQL Server eine stets aktuelle Tabelle mit den Dateinamen incl. Pfad und kann (theoretisch) diese Dateien auch direkt via Blob im SQL Server bearbeiten. 

In Onedrive, im Ordner Filestream, habe ich eine Datei (im Word und PDF Format)  mit weiteren techn. Details dazu hinterlegt. PS: Die Anregungen dazu habe ich mir aus dem Buch "Access und SQL Server" von Minhorst / Jungbluth geholt. (Bei mir Kap. 16 - Ab Seite 439)"

mfg 

Klaus Oberdalhoff

Microsoft SQL Server Migration Assistant v7.7 for Access enthält wider separaten Download für X86-er Versionen !!!!

English at the bottom of the German part.

Die neueste Version des SSMA für Access 7.7 enthält - oh Wunder - auch wieder eine eigene X86-er Version zum separaten download

 

Microsoft SQL Server Migration Assistant v7.7 for Access

enthält wieder einen separaten Download für X86-er Versionen !!!!

https://www.microsoft.com/en-us/download/details.aspx?id=54255

 

The newest version 7.7 of SSMA for Access - oh wonder - contains TWO different files for download again. A 64 bit one and a X86 one ...

Microsoft SQL Server Migration Assistant v7.7 for Access

contains seperate download for X86-Versions again !

https://www.microsoft.com/en-us/download/details.aspx?id=54255

 

 

1 3 4 5 ...6 7