Overblog
Folge diesem Blog Administration + Create my blog
Blog von Olaf Helper

SQL Server 2008 - Eine Einführung in den SQL Server von Microsoft

28. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #MSSQL

Im MSDN Portal gibt es einen 10 teiligen Webcast zum Thema „Einführung in den SQL Server 2008“ vom bekannten Ruprecht Dröge (BeConstructed).

Weiterlesen

Aktuelle Cumulative Updates zum MS SQL Server 2008 - Juli

27. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #Download MSSQL

In diesem Monat gab es zum MS SQL Server 2008 zwei Updates, das CU #6 zur RTM und das das CU #3 fürs SP1. Details man beim Microsoft SQL Server Release Service

 

Cumulative Update #6 for SQL Server 2008 RTM

Cumulative Update #3 for SQL Server 2008 SP1

 

Bei der Gelegenheit habe ich auch meine List mit den MS SQL Server Buildnumbers aktualisiert.

Weiterlesen

Microsoft Expression Tools gehen in die 3te Runde

26. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #.NET

Die neue Version 3 der Microsoft Expression Tools für die Web-Entwicklung, speziell auch ausgelegt für Silverlight und RIA, stehen als 60 tägige Trial Version im Download Center zur Verfügung.

 

Microsoft Expression Encoder 3 Trial (25,3 MB)

Microsoft Expression Design 3 Trial (53,6 MB)

Microsoft Expression Blend 3 + SketchFlow Trial (71,2 MB)

 

Microsoft Expression Blend 3 SDK (3,0 MB)

Weiterlesen

Microsoft Office SharePoint Designer 2007 – kostenlos

23. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #Office

Da surft man ganz unschuldig etwas im Internet und über was stolpert man da:

In diversen Shops findet man den „Microsoft Office SharePoint Designer 2007“ noch im Portfolio für ca. 360,- EUR, während seit Mitte April 2009 diese Software von Microsoft offiziell verschenkt wird, will heißen, sie steht zum kostenlosen Download zur Verfügung.

Es gibt den Designer in den diversen Sprachen, so auch in Deutsch, der Download ist ca. 318 MB groß. Wie so üblich muss man sich vorher einmal mittels seiner Live-Id registrieren.

 

Siehe:
Microsoft SharePoint Designer Home Page

-  Download Microsoft SharePoint Designer 2007 Deutsch - kostenlos

Weiterlesen

VBA Funktion VAL für den Microsoft Sql Server

21. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

In VBA = „Visual Basic for Application“ gibt es die Funktion VAL, von der ich mal sagen würde, sie ist so gerade eben noch deterministisch: Sie ermittelt aus ein String von links an die numerischen Zeichen bis zum ersten Nicht-Numerischen. Neben den Vorzeichen + und – wird nur noch der Punkt als Dezimaltrennzeichen akzeptiert; der aber fix unabhängig von Ländereinstellungen. Dies wird dann als Zahlenwert zurückgeliefert.

Wozu kann die Funktion nun nützlich sein? Um aus Texten die linksbündige Zahl zu ermitteln und das wiederum kann man nutzen, um etwas benutzerfreundlicher zu sortieren. Microsoft Windows Vista macht es schon so, das Zahlen innerhalb von Dateinamen als solche erkannt & für die Sortierung interpretiert werden, statt streng rein alphanumerisch zu sortieren.
Beispiel:

 

Statt

Dessen

1abc

1abc

10abc

2efg

2efg

3hij

20efg

10abc

200efg

20efg

3hij

200efg

Zyx

Abc

Abc

Zyx

 

Meine erste Implementierung der VAL Funktion setzt diese genau so um, wie in VBA definiert. Eine analoge Funktion, die die erste Zahl an beliebiger Stelle ermittelt, werde ich später noch umsetzen.

 

Die Sortierung nach den linksbündigen Zahlen sieht dann z.B. so aus: Erste Sortierung, ob VAL eine Zahle <> 0 liefert, dann nach dem Zahlenwert selbst und zum Schluß nach dem String; die wollen ja auch sortiert werden.

 

 SELECT Wert 
 FROM Tabelle 
 ORDER BY CASE WHEN dbo.fnVAL(Wert) = 0  
  THEN 1 ELSE 0 END, 
  dbo.fnVAL(Wert), 
  Wert 

 

Hier nun das Sql Script zur Anlage der Funktion „fnVAL“:

 

 -- Alte Version vorher löschen 
 IF NOT OBJECT_ID('dbo.fnVAL', 'FN') IS NULL 
  DROP FUNCTION dbo.fnVal 
 GO 
   
 CREATE FUNCTION dbo.fnVAL 
  (@value varchar(8000)) 
 -- Autor: Olaf Helper 
 -- Stand: 18.07.2009 
 -- Typ: User Defined Function 
 -- Name: fnVAL 
 -- Analog zur VBA Funktion:  
   
 RETURNS sql_variant 
 AS 
 BEGIN 
  DECLARE @retun sql_variant, @loop int, @firstdot int; 
  DECLARE @actualChar as char, @return sql_variant, @exit as bit; 
  SET @return = 0; 
  SET @firstdot = 0; 
  SET @exit = 0; 
   
  -- Eingabewert ohne Spaces 
  SET @value = REPLACE(LTRIM(RTRIM(ISNULL(@value,'0'))), ' ', ''); 
  -- Dezimaltrenner ist der Punkt, kein Komma 
  SET @value = REPLACE(@value, ',', 'x'); 
   
  -- Vorzeichen vorhanden? 
  -- Führende Null schadet nicht (keine Veränderung des Ergebnisse) 
  -- und erleichter das Auswerten 
  IF LEFT(@value, 1) = '-' OR LEFT(@value, 1) = '-' 
  BEGIN 
  SET @loop = 2; 
  SET @value = SUBSTRING(@value, 1, 1)  
   + '0' 
   + SUBSTRING(@value, 2, LEN(@value) - 1); 
  END 
  ELSE 
  BEGIN 
  SET @loop = 1; 
  SET @value = '0' + @value; 
  END 
   
  SET @actualChar = SUBSTRING(@value, @loop, 1); 
  WHILE @loop < LEN(@value) 
   AND @exit = 0 
   AND (@actualChar LIKE '[0-9]' OR @actualChar ='.')  
   AND (@firstdot = 0 
   OR (@firstdot >= 1 AND @actualChar <> '.')) 
  BEGIN 
  IF @actualChar = '.' AND @firstdot = 0 
  SET @firstdot = 1; 
   
  SET @actualChar = SUBSTRING(@value, @loop + 1, 1); 
  IF NOT (@actualChar LIKE '[0-9]' OR @actualChar ='.') 
  SET @exit = 1; 
  ELSE 
   IF (@firstdot = 0  
   OR (@firstdot >= 1 AND @actualChar <> '.')) 
  SET @loop = @loop + 1 
  ELSE 
  SET @exit = 1; 
   
  END 
  SET @value = SUBSTRING(@value, 1, @loop); 
  IF RIGHT(@value, 1) = '.' 
  SET @loop = @loop - 1; 
   
  -- Der numerische Teil ist soweit gefunden, nun konvertieren 
  -- Mit Kommastelle? 
  IF @firstdot <= 0  
  SET @return = CONVERT(bigint, SUBSTRING(@value, 1, @loop)); 
  ELSE 
  SET @return = CONVERT(float, SUBSTRING(@value, 1, @loop)); 
   
  RETURN(@return); 
 END; 
 GO 
   
 -- Unit Test 
 -- Zunächst einfache, die funktionieren müssen 
 SELECT dbo.fnVAL('1') UNION ALL 
 SELECT dbo.fnVAL('11') UNION ALL 
 SELECT dbo.fnVAL('11xyz') UNION ALL 
 SELECT dbo.fnVAL('111.11,') UNION ALL 
 SELECT dbo.fnVAL('Hicks'); 
   
 -- Nun Problemfälle 
 SELECT dbo.fnVAL(NULL) UNION ALL -- kann FN mit NULL umgehen? 
 SELECT dbo.fnVAL('.') UNION ALL -- Nur Dezimaltrenner? 
 SELECT dbo.fnVAL(' ') UNION ALL -- Nur leer? 
 SELECT dbo.fnVAL('') UNION ALL -- Nur leerer? 
 SELECT dbo.fnVAL(',') UNION ALL -- Nur Anti-Dezimaltrenner? 
 SELECT dbo.fnVAL('1-1') UNION ALL -- Mit Vorzeichen drin(soll 1 ergeben) 
 SELECT dbo.fnVAL('-') UNION ALL -- Nur Vorzeichen? 
 SELECT dbo.fnVAL('...')   -- Pünktchen? 
Weiterlesen

Laufende Prozesse einschließlich deren Sql Statements ermitteln

20. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

„Die Programme laufen heute wieder so träge, es dauert Alles Ewigkeiten“. Ein durchaus typischer Anruf eines Anwenders, der einen vermuten lässt, dass der Mircosoft Sql Server ausgelastet ist. Da fragt man als DBA sich wieder, was da alles auf dem Sql Server läuft und ihn der auslastet.

Auskunft kann hier der Server selbst geben. Über die „Dynamic Management Views (DMV)“ (ab Sql Server 2005) kann man die aktuellen Daten zu den Prozessen inkl. den ausgeführten Sql Statements ermitteln. Voraussetzung ist natürlich, das man sich überhaupt noch verbinden und der Sql Server antworten kann; aber dazu gibt es zur Not ja noch die DAC = Dedicated Admin Connection.

 

 -- Ermittelt alle laufende Prozesse mit deren 
 -- Sql Statements 
 SELECT PRO.spid, PRO.loginame, PRO.hostname, PRO.program_name, 
  DB.name AS DatabaseName, PRO.open_tran, PRO.blocked, 
  REQ.command, SQL.text AS SqlStatement, 
  SES.prev_error, SES.row_count,  
  REQ.wait_resource, REQ.wait_time 
 FROM sys.sysprocesses AS PRO 
  CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS SQL 
  LEFT JOIN sys.sysdatabases AS DB 
   ON SQL.dbid = DB.dbid 
   LEFT JOIN sys.dm_exec_requests AS REQ 
  ON PRO.request_id = REQ.request_id 
   AND PRO.spid = REQ.session_id 
   LEFT JOIN sys.dm_exec_sessions AS SES 
   ON REQ.session_id = SES.session_id 
Weiterlesen

Indizes defragmentieren

15. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Im Groben, Großen und Ganzen läuft eine Microsoft Sql Server Datenbank so ohne weiteres Zutun, wenn sie richtig konfiguriert ist. Alles Indizes und Statistiken werden automatisch aktualisiert, man muss sich eigentlich um nichts Weiteres kümmern.

Um eine optimale Performanz bei zu behalten, sollte man seinen Indizes und Statistiken eine regelmäßige Pflege zukommen lassen. Dazu legt man einen Wartungsplan an, der diese neue aufbaut; dazu gibt es extra einen Wizard mit den nötigen Optionen, was einem schon zeigt, dass das auch von Microsoft als regelmäßige Wartung angesehen wird.

Das Re-Indizieren hat zum einen das kleine Problem, das es eine Offline-Operation ist (Ausnahme: Enterprise Edition, da geht es auch Online), die betroffene Tabelle ist für den Zeitraum gesperrt.

Zum Anderen ist es mit wachsender Tabellengröße eine zeitaufwendige Angelegenheit und es wird alles mit protokolliert, wodurch das Transaktionsprotokoll entsprechend anwächst.

Eine Alternative stellt das Defragmentieren von Indizes dar. Es ist zwar nicht so effektive wie ein Reindex, dafür läuft es schneller ab und ist eine echte Online-Operation, auf die betroffene Tabelle kann also weiterhin zugegriffen werden. Man sollte aber trotzdem dabei nicht vergessen, dass es eine entsprechend hohe I/O Last erzeugt.

Hier ein T-SQL Script, das über die Fragmentierung-Informationen der Indizes diese ermittelt und defragmentiert.

 
 -- Indizes in absteigender Reihenfolge nach 
 -- Umfang der Fragmentation defragmentieren 
 DECLARE @TableName sysname, @IndexID smallint, @IndexName sysname; 
 DECLARE @MinFragPer float, @FragScale as varchar(5); 
 SET @MinFragPer = 10.0 -- Min Fragmentierungsgrad in Prozent 
   
 -- Alle relevanten Indizes ermitteln, die den Mindest-Fragmentierungsgrad 
 -- übersteigen 
 DECLARE Fragment CURSOR LOCAL FOR  
  SELECT SCH.name + '.' + OBJ.name AS TableName,  
  IDX.index_id AS IndexID, IDX.name as IndexName, 
  CONVERT(varchar(5), ROUND(FRG.avg_fragmentation_in_percent, 2)) AS FragScale 
  FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) AS FRG 
  INNER JOIN sys.objects AS OBJ 
  ON FRG.object_id = OBJ.object_id 
  INNER JOIN sys.schemas as SCH 
  ON OBJ.schema_id = SCH.schema_id 
  INNER JOIN sys.indexes AS IDX 
  ON FRG.index_id = IDX.index_id 
  AND FRG.object_id = IDX.object_id 
  -- Nur wirklich fragmentierte IDX rausfiltern 
  WHERE IDX.index_id > 0 -- keine Heaps 
  AND FRG.avg_fragment_size_in_pages > 1 -- min. über eine Seite 
  AND FRG.avg_fragmentation_in_percent > @MinFragPer 
 ORDER BY FRG.fragment_count DESC, FRG.page_count DESC, FRG.avg_fragment_size_in_pages DESC 
   
 -- Cursor mit den Tabellen / Indizes öffnen 
 OPEN Fragment 
 FETCH NEXT FROM Fragment 
  INTO @TableName, @IndexID, @IndexName, @FragScale 
 WHILE @@FETCH_STATUS = 0 
 BEGIN 
  -- Info Ausgabe 
  PRINT @Tablename + ' - ' +  
   @IndexName + '(' +CONVERT(varchar(10), @IndexID) + ') = ' + 
   @FragScale + ' %' 
   
  FETCH NEXT FROM Fragment 
  INTO @TableName, @IndexID, @IndexName, @FragScale 
  DBCC INDEXDEFRAG (0, @TableName, @IndexID) WITH NO_INFOMSGS  
 END 
   
 -- Cursor schließen und abräumen 
 CLOSE Fragment 
 DEALLOCATE Fragment 

 

Protokollausgabe:

 

 Production.WorkOrder - IX_WorkOrder_ProductID(3) = 10.1 % 
 Production.BillOfMaterials - PK_BillOfMaterials_BillOfMaterialsID(2) = 33.33 % 
 Production.BillOfMaterials - AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate(1) = 15 % 
 Purchasing.PurchaseOrderDetail - IX_PurchaseOrderDetail_ProductID(2) = 17.65 % 
 Production.Product - PK_Product_ProductID(1) = 23.08 % 
 Production.BillOfMaterials - IX_BillOfMaterials_UnitMeasureCode(3) = 30 % 
 Production.ProductInventory - PK_ProductInventory_ProductID_LocationID(1) = 42.86 % 
 HumanResources.JobCandidate - PK_JobCandidate_JobCandidateID(1) = 18.18 % 
 Purchasing.PurchaseOrderHeader - IX_PurchaseOrderHeader_VendorID(2) = 33.33 % 
 Purchasing.PurchaseOrderHeader - IX_PurchaseOrderHeader_EmployeeID(3) = 33.33 % 
 Sales.StoreContact - PK_StoreContact_CustomerID_ContactID(1) = 20 % 
 Sales.SpecialOfferProduct - PK_SpecialOfferProduct_SpecialOfferID_ProductID(1) = 33.33 %
Production.ProductCostHistory - PK_ProductCostHistory_ProductID_StartDate(1) = 33.33 %
Weiterlesen

Query Statistics (DMV)

14. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Die „Dynamic Management Views“ vom Microsoft SQL Server 2005 / 2008 bieten zahlreiche Information, wie auch wie häufig SQL Statements ausgeführt wurden.

Das ist interessant, um zu sehen, was am meisten verwendet wird und wo man am ehesten mal über Optimierung nachdenken könnte. Das Ermitteln geht einfach über die DMV sys.dm_exec_query_stats.

Falls das nun ein unerwartetes Ergebnis bringt oder die bisher als „Most Used“ vermuteten SQL Statements fehlen, dann kann es einen einfachen Grund haben: Es werden keine parametrisierte Queries (Commands) verwendet. Dadurch, dass jedes Statement dynamisch mit den Werten generiert wird, sieht für den Sql Server jedes dieser Statements wie ein Uniques aus; somit gibt es jedes Mal ein eigenen Ausführungsplan und somit ist auch nichts mit den Statistiken über mehr als einmal verwendete Statements.

Was sagt uns das: Besser immer parametrisierte Queries verwenden!

Übrigens: Wenn man trotzdem mal ein Ergebnis sehen möchte, dann das Script mal auf der Datenbank „distribution“ (für die Replikation) oder auf der „ReportServer“ (vom Reporting Service) ausführen; sofern vorhanden. Da erhält man auf jeden Fall ein Ergebnis.

 

 -- Anzahl Ausführungen von SQL Statements ermitteln 
 SELECT TOP 100 
  EQS.execution_count AS Anzahl, 
  EQS.last_execution_time AS LetzteVerwendung, 
  SUBSTRING(EST.text,  
  EQS.statement_start_offset / 2,  
  (CASE WHEN EQS.statement_end_offset = -1  
  THEN LEN(convert(nvarchar(max), EST.text)) * 2  
  ELSE EQS.statement_end_offset  
  END  
  - EQS.statement_start_offset) / 2 
  ) AS QueryText, 
  OBJ.name AS ObjectName, OBJ.type_desc AS ObjectType, 
  PAR.name AS ParentName, PAR.type_desc AS ParentType 
 FROM sys.dm_exec_query_stats AS EQS 
  CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) AS EST 
  INNER JOIN sys.objects AS OBJ 
  ON EST.objectid = OBJ.object_id 
  LEFT JOIN sys.objects AS PAR 
  ON OBJ.parent_object_id = PAR.object_id 
 WHERE EST.dbid = DB_ID() 
 ORDER BY EQS.execution_count DESC 
Weiterlesen

Performance Test der CLR User Aggregation FirstString und LastString

14. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #.NET

Die Implementierung einer eigenen CLR Aggregation, wie FirstString / LastString aus meinem Blog, ist sehr einfach umzusetzen. Nun stellt sich mir noch die Frage: Wie performant ist das gegenüber den integrierten Aggregationen wie MIN / MAX?

Also machen wir mal einen Test. Basis soll eine einfache Tabelle mit einem VarChar Feld sein ohne jeden Index; es soll eh über alles aggregiert werden und da wird sowieso ein Full-Table-Scan ausgelöst. Zunächst eben die Tabelle mit 50.000 Einträgen anlegen, auf meinem Notebook dauert das ca.  55 Sekunden.

 -- Performance-Test für CLR Aggregation FIRST / LAST 
 -- gegenüber den integrierten MIN /MAX bei Strings 
 SET STATISTICS TIME OFF; 
 GO 
 CREATE TABLE AggrTest (String varchar(20)); 
 GO 
 SET NOCOUNT ON; 
 DECLARE @loop int; SET @loop = 0; 
 WHILE @loop < 100000 
 BEGIN 
  INSERT INTO AggrTest  
   VALUES (RIGHT(REPLICATE('0', 20) + CONVERT(varchar(20), @loop), 20)); 
  SET @loop = @loop + 1; 
 END -- Dauer ca.  
 GO 

 

Da ich schon ahne, was auftreten wird, habe ich einmal den Sql Server Dienst neu gestartet, damit die damit mein Assembly OlafHelper.SqlServer.Aggregate auch nicht geladen. Ist.

Hier nun das Test-Script, zunächst Min/Max und dann FirstString/LastString. Dazwischen wird der Procedure und Daten-Cache gelöscht.

 -- Cache & Co löschen 
 DBCC FREEPROCCACHE WITH NO_INFOMSGS; 
 DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; 
 GO 
 PRINT 'Min/Max' 
 SET STATISTICS TIME ON 
 GO 
   
 -- Nun die Zeitmessung 
 SELECT MIN(String), MAX(String) 
 FROM AggrTest 
 GO 
 SET STATISTICS TIME OFF; 
 GO 
 -- Cache & Co löschen 
 DBCC FREEPROCCACHE WITH NO_INFOMSGS; 
 DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS; 
 GO 
 PRINT 'First/LastString' 
 SET STATISTICS TIME ON; 
 GO 
   
 SELECT dbo.FirstString(String), dbo.LastString(String) 
 FROM AggrTest 
 GO 
 SET STATISTICS TIME OFF; 
 -- Aufräumen 
 DROP TABLE AggrTest 

 

Ergebnis der ersten Ausführung:

 

Min/Max

SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 16 ms, verstrichene Zeit = 16 ms.

SQL Server-Ausführungszeiten:             CPU-Zeit = 218 ms, verstrichene Zeit = 252 ms.

SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.

 

First/LastString

SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 578 ms, verstrichene Zeit = 1317 ms.

SQL Server-Ausführungszeiten:             CPU-Zeit = 374 ms, verstrichene Zeit = 449 ms.

SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.

 

Ergebnis der zweiten Ausführung:

 

Min/Max

SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 26 ms.

 SQL Server-Ausführungszeiten:            CPU-Zeit = 188 ms, verstrichene Zeit = 257 ms.

SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.

 

First/LastString

SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 25 ms.

SQL Server-Ausführungszeiten:             CPU-Zeit = 265 ms, verstrichene Zeit = 330 ms.

SQL Server-Analyse- und Kompilierzeit: CPU-Zeit = 0 ms, verstrichene Zeit = 0 ms.

 

Der Grund für die großen Unterschiede zwischen der ersten und zweiten Ausführung ist einfach, denn nach dem Sql Server Dienst-Neustart muss zunächst einmal das CLR Assembly geladen werden. Einmal im Speicher ist die Ausführung gleich schneller und

Weiterlesen

SoundEx für den deutschsprachigen Raum

13. Juli 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Man kennt das Problem: Man sucht in einem Adressstamm nach einer Person und findet ihn auf die Schnelle nicht, weil man nicht genau weiß, ob sich „Meier“ nun Maier, Meyer, Mayer oder Mayr schreibt.

Für den englischsprachigen Raum gibt es im MS Sql Server die integrierte Funktion SOUNDEX, die eine Ähnlichkeitssuche (Unscharfe Suche) ermöglicht.

Beispiel:

 SELECT SOUNDEX('Tailer') AS S1,  
  SOUNDEX('Tayler') AS S2,  
  SOUNDEX('Tyler') AS S3 

Die Selektion liefert 3 identische Ergebnisse

 S1 S2 S3 
 ----- ----- ----- 
 T460 T460 T460 

also egal nach welchem Namen ich suche, ich finde immer alle drei ähnlich geschriebenen Namen.

Die Funktion hat aber auch seine kleinen Nachteile

-       Sie ist sehr auch englisch-sprachige Namen abgestimmt

-       Das Ergebnis ist mit 4 Zeichen ein doch sehr „zusammen geraffte“ Teilmenge des Namens

 

Für meine Ansprüche und eben deutschsprachige Namen habe ich mir selbst mal eine SoundExDE Funktion erstellt, zudem wird das Ergebnis nicht auf 4 Zeichen begrenzt. Dadurch ist die Ergebnismenge von Selektionen zwar mit unter geringer, aber dafür auch präziser; werden will schon hunderte von nur entfernt ähnliche Namen durchgehen.

So sieht sie aus:

 -- Alte Version löschen 
 IF NOT OBJECT_ID('dbo.fnSoundExDE', 'FN') IS NULL 
  DROP FUNCTION [dbo].[fnSoundExDE] 
 GO 
   
 -- Erzeugt aus dem übergebenen Text einen phonetischen SoundEx Text für 
 -- einen Ähnlichkeitsvergleich (unscharfer Vergleich)  
 -- Optimiert für deutschsprachige Texte 
 -- Parameter:  
 -- @sValue = der umzusetztende Text 
 -- @iIgnoreNumbers = 1 - Zahlenwerte werden ignoriert, 0 = Zahlen werden beachtet 
   
 CREATE FUNCTION [dbo].[fnSoundExDE](@sValue AS nvarchar(4000),  
  @iIgnoreNumbers AS tinyint) 
 RETURNS varchar(4000) 
 AS 
 BEGIN 
  DECLARE @sSoundEx AS nvarchar(4000) 
  DECLARE @sResult as varchar(4000) 
  DECLARE @iLoop AS smallint 
  DECLARE @iHit AS smallint 
   
  --Nur Großbuchstaben ohne Leerzeichen 
  SET @sSoundEx = UPPER(LTRIM(RTRIM(@sValue))) 
  SET @sSoundEx = REPLACE(@sSoundEx, ' ', '') 
   
  IF LEN(@sSoundEx) > 0 
  BEGIN 
  -- Umlaute substituieren 
  SET @sSoundEx = REPLACE(@sSoundEx, N'Ä', 'E') -- Wie ein E 
  SET @sSoundEx = REPLACE(@sSoundEx, N'Ö', 'OE') 
  SET @sSoundEx = REPLACE(@sSoundEx, N'Ü', 'UE') -- Wie Y 
  SET @sSoundEx = REPLACE(@sSoundEx, N'ß', 'S') 
   
  -- Von Unicode in ASCII casten 
  SET @sResult = CONVERT(varchar(4000), @sSoundEx) 
   
  -- Nun die erste Stufe der Ersetzung von Buchstabenkombinationen 
  SET @sSoundEx = REPLACE(@sSoundEx, 'EI', 'AY') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'AI', 'AY') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'EY', 'AY') 
   
  SET @sSoundEx = REPLACE(@sSoundEx, 'CZ', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'DS', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'SC', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'SZ', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'TZ', 'C') 
   
  SET @sSoundEx = REPLACE(@sSoundEx, 'PH', 'V') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'PF', 'V') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'QU', 'KV') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'UE', 'Y') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'EU', 'OY') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'AE', 'E') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'KS', 'X') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'IE', 'Y') 
   
  SET @sSoundEx = REPLACE(@sSoundEx, 'AH', 'A') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'EH', 'E') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'IH', 'I') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'UH', 'E') 
   
  -- Jetzt die zweite Stufe 
  SET @sSoundEx = REPLACE(@sSoundEx, 'K', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'G', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'Q', 'C') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'I', 'Y') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'W', 'V') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'F', 'V') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'T', 'D') 
  SET @sSoundEx = REPLACE(@sSoundEx, 'P', 'B') 
   
  --Zum Schluß alle doppelten und Nicht-Buchstaben entfernen 
  SET @iLoop = 1 
  WHILE @iLoop <= LEN(@sResult) 
  BEGIN 
  SET @iHit = 1 
  IF (@iIgnoreNumbers <> 0 AND NOT SUBSTRING(@sResult, @iLoop, 1) LIKE '[A-Z]') 
  OR (@iIgnoreNumbers = 0 AND NOT (SUBSTRING(@sResult, @iLoop, 1) LIKE '[A-Z]' OR SUBSTRING(@sResult, @iLoop, 1) LIKE '[0-9]')) 
  BEGIN 
  SET @sResult = SUBSTRING(@sResult, 1, @iLoop - 1) + SUBSTRING(@sResult, @iLoop + 1, LEN(@sResult) - @iLoop) 
  SET @iHit = 0 
  END 
   
  IF @iLoop <= LEN(@sResult) AND @iLoop > 1 
  IF SUBSTRING(@sResult, @iLoop, 1) = SUBSTRING(@sResult, @iLoop - 1, 1) 
  BEGIN 
  SET @sResult = SUBSTRING(@sResult, 1, @iLoop - 1) + SUBSTRING(@sResult, @iLoop + 1, LEN(@sResult) - @iLoop) 
  SET @iHit = 0 
  END 
   
  SET @iLoop = @iLoop + @iHit 
  END 
  END 
   
  RETURN @sResult 
   
 END 

 

So, nun noch ein kleiner Test, ob es so funktioniert wie gewünscht:

 -- Unittest für 'dbo.fnSoundExDE' 
 CREATE TABLE #Namen 
  (Name nvarchar(20), 
   mySoundEx nvarchar(20)); 
   
 INSERT INTO #Namen ([Name]) VALUES ('Müller') 
 INSERT INTO #Namen ([Name]) VALUES ('Mueller') 
 INSERT INTO #Namen ([Name]) VALUES ('Müler') 
 INSERT INTO #Namen ([Name]) VALUES ('Weihermüller') 
 INSERT INTO #Namen ([Name]) VALUES ('Weiden-Müller') 
 INSERT INTO #Namen ([Name]) VALUES ('Mühler') -- Der ist nur fast so ähnlich 
 INSERT INTO #Namen ([Name]) VALUES ('Muehler') -- Der ist nur fast so ähnlich 
   
 UPDATE #Namen SET mySoundEx = dbo.fnSoundExDE([Name], 1) 
   
 SELECT *, SOUNDEX([Name]) AS Sx 
 FROM #Namen 
 WHERE mySoundEx LIKE '%' + dbo.fnSoundExDE('Müller', 1) + '%' 
   
 GO 
 DROP TABLE #Namen 

Ergebnis:

 Name mySoundEx Sx 
 -------------------- -------------------- ----- 
 Müller MUELER M460 
 Mueller MUELER M460 
 Müler MUELER M460 
 Weihermüller WEIHERMUELER W654 
 Weiden-Müller WEIDENMUELER W350 
Weiterlesen
1 2 > >>