Feldkonvertierung Microsoft SQL Server 2008

Immer wieder kommen mir Datenbanken/Tabellen zu Gesicht, welche Felder vom Datentyp vom char(1) mit Werten wie J/N etc. enthalten. Die Gründe können verschiedener Art sein:

  • Traditionelle/alte Datenbank
  • Höhere Lesbarkeit der Felder bei (J/N zu 0/1)

Leider erkauft man sich damit aber auch einige Probleme.

  • Langsamere Abfragen
  • Höherer Speicherverbrauch
  • Komplexere Abfrage und/oder Programmlogik

Es liegt also nahe, diese Felder zu konvertieren. Beim Microsoft SQL Server 2008 ist der bessere Datentyp für solche Anwendungsfälle der Datentyp Bit, welcher 0, 1 und Null speichern kann. Vorher müssen jedoch noch die Inhalte der Tabelle entsprechend umgewandelt werden.

Was nicht automatisch funktioniert, ist das umwandeln von Felder, welche in einem Key bzw. Index enthalten sind. Hier ist etwas Handarbeit gefragt den hier muss der Key/Index gelöscht, das Feld konvertiert werden und dann wieder der Key/Index erzeugt werden.

Bei Arbeiten in Test- und Echtumgebungen lohnt es sich eventuell, diese Befehle auch in das Konvertierungsskript zu schreiben, um dann später schnell das Produktivsystem umzuwandeln.

Das Konvertierungsskript

Das folgende Skript liest nun alle Char(1) Felder aus der gewählten Datenbank aus. Natürlich können auch Char(1) Felder vorhanden sein, welche mehrere Werte beinhalten. Diese werden nicht berücksichtigt, sondern in diesem Fall nur Felder, welche “J” und “N” als Inhalt haben.

Felder, welche in einem Index verwendet werden, werden übersprungen. Die Werte werden anschließend automatisch konvertiert. “N” wird zu 0, “J” zu 1 um dann eine Umwandlung in den neuen Datentyp zu ermöglichen. Anschließend erfolgt die Datentypkonvertierung. Zum Abschluss wird noch eine Liste mit Feldern angezeigt, welche aufgrund einer Verwendung in einem Index nix konvertiert werden konnten.

-- Varibalen
DECLARE @tname NVARCHAR(48)
DECLARE @cname NVARCHAR(48)
DECLARE @sql as varchar(1000)
IF OBJECT_ID(N'tempdb..#temp', N'U') IS NOT NULL 
  DROP TABLE #temp;
CREATE TABLE #temp
(
  tname varchar(48),
  cname varchar(48),
  anz int
)
-- Alle Keyfelder auslesen
IF OBJECT_ID(N'tempdb..#keytable', N'U') IS NOT NULL 
  DROP TABLE #keytable;
select 
    i.name as IndexName, 
    o.name as TableName, 
    ic.key_ordinal as ColumnOrder,
    ic.is_included_column as IsIncluded, 
    co.[name] as ColumnName
into #keytable
from sys.indexes i 
join sys.objects o on i.object_id = o.object_id
join sys.index_columns ic on ic.object_id = i.object_id 
    and ic.index_id = i.index_id
join sys.columns co on co.object_id = i.object_id 
    and co.column_id = ic.column_id
where i.[type] = 2 
and i.is_unique = 0 
and i.is_primary_key = 0
and o.[type] = 'U'
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal;


-- Ermittlung aller Char(1) Felder
DECLARE tabcurs CURSOR FOR
  SELECT tbl.name AS 'Table', c.name AS 'Column Name' 
  FROM sys.columns as c
  INNER JOIN sys.tables AS tbl ON tbl.object_id = c.object_id
  INNER JOIN sys.types as t ON c.system_type_id = t.system_type_id
  LEFT JOIN #keytable as k ON k.TableName = tbl.name AND k.ColumnName = c.name
  WHERE t.name in ('char') AND c.max_length = 1
  AND k.IndexName IS NULL
  ORDER BY tbl.name;
-- Cursor durchlaufen
OPEN tabcurs;
FETCH NEXT FROM tabcurs INTO @tname, @cname
WHILE @@FETCH_STATUS = 0
BEGIN
  -- alle Tabellen zwischenspeichern dabei nur J/N Tabellen nicht in Zähler berücksichtien
  --PRINT @tname + ' ' + @cname;
  SET @sql = 'INSERT INTO #temp VALUES(''' + @tname + ''', ''' + @cname + ''', (SELECT COUNT(DISTINCT(' + @cname + ')) FROM ' + @tname + ' WHERE ' + @cname + ' NOT IN (''J'', ''N'')))';
  EXEC(@sql);
  FETCH NEXT FROM tabcurs INTO @tname, @cname
END
-- Speicher freigeben
CLOSE tabcurs;
DEALLOCATE tabcurs;

-- Konvertierung
DECLARE tabcurs CURSOR FOR
  SELECT tname, cname
  FROM #temp
  WHERE anz = 0; -- nur J/N Tabellen berücksichtigen
OPEN tabcurs;
FETCH NEXT FROM tabcurs INTO @tname, @cname
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @tname + ' ' + @cname
  SET @sql = 'UPDATE ' + @tname + ' SET ' + @cname + ' = ''1'' WHERE ' + @cname + ' = ''J'';';
  --PRINT @sql
  EXEC(@sql);
  SET @sql = 'UPDATE ' + @tname + ' SET ' + @cname + ' = ''0'' WHERE ' + @cname + ' = ''N'';';
  --PRINT @sql
  EXEC(@sql);
  SET @sql = 'ALTER TABLE ' + @tname + ' ALTER COLUMN ' + @cname + ' bit;';
  --PRINT @sql
  EXEC(@sql)
  FETCH NEXT FROM tabcurs INTO @tname, @cname
END
-- Speicher freigeben
CLOSE tabcurs;
DEALLOCATE tabcurs;

-- Spalten für manuelle Umstellung anzeigen (Indexfelder)
SELECT tbl.name AS 'Table', c.name AS 'Column Name' 
  FROM sys.columns as c
  INNER JOIN sys.tables AS tbl ON tbl.object_id = c.object_id
  INNER JOIN sys.types as t ON c.system_type_id = t.system_type_id
  LEFT JOIN #keytable as k ON k.TableName = tbl.name AND k.ColumnName = c.name
  WHERE t.name in ('char') AND c.max_length = 1
  AND k.IndexName IS NOT NULL
  ORDER BY tbl.name;

Einschränkungen und Hinweise

Das Konvertierungsskript hat einige Einschränkungen, welche man wissen muss.

  • Felder in Keys/Indexe werden bewusst nicht konvertiert. Hier ist Handarbeit gefragt.
  • Bei anderen Werten wie “J” / “N” ist eine Anpassung notwendig
  • Durch die Datenbankänderungen sind Änderungen in Programmen notwendig!

Eine Besonderheit beim SQL Server sollte man auch noch beachten. Der SQL Server kann keine Primary Keys mit Felder bilden, welche einen NULL-Wert beinhalten können. Deswegen ist es wichtig, die Felder mit der Einschränkung NOT NULL auszustatten, welche später in einem Primary Key verwendet werden sollen. Also hier als Beispiel:

ALTER TABLE testtabelle ALTER COLUMN spalte_in_pk bit NOT NULL;

Ohne diesen Zusatz legt der SQL Server den Key nicht an!