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

t-sql

Datentypen eines Abfrageergebnisses ermitteln

7. August 2013 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Mit ein wenig Erfahrung und der Kenntnis der Datentypen des SQL Servers und insbesondere der Rangfolge der Datentypen kann man leicht ausmachen, welcher Datentyp bei einer Berechnung von einer Abfrage zurück geliefert wird.
War man sich nicht sicher, konnte man einfach mit einer SELECT ... INTO Anweisung aus dem Abfrageergebnis eine neue Tabelle erstellen lassen und sich dann in deren Definition die Typen ansehen.
Mit dem SQL Server 2012 wurde die neue dynamische Verwaltungsfunktion sys.dm_exec_describe_first_result_set eingeführt, mit dessen Hilfe man zu einer Abfrage die Datentypen selektieren kann.
Einfaches Beispiel mit ein paar Berechnungen sowie festen Werten und einer Funktion:
 
DECLARE @sql nvarchar(max);
-- Rückgabetypen einer Abfrage mit berechneten / festen Werten.   SET @sql =
N'SELECT 10 / 2 AS ResInteger' +
N'      ,10 / 2.0 AS ResNumeric1' +
N'      ,10.0 / 2.00000 AS ResNumeric2' +
N'      ,1.2345E6 AS ResFloat' +
N'      ,''Varchar'' AS ResVarchar' +
N'      ,N''NVarchar'' AS ResNVarchar' +
N'      ,CONVERT(varchar, '''') AS ResDefault' +
N'      ,1/1 AS ResNull' +
N'      ,ISNULL(1/1, 1/1) AS ResNotNull' +
N'      ,ISNULL(null, null) AS ResNullOrNotNull'

SELECT FRS.column_ordinal AS ordinal
      ,FRS.name
      ,FRS.is_nullable
      ,FRS.system_type_name AS systemtype
      ,FRS.max_length AS maxlength
      ,FRS.precision
      ,FRS.scale
      ,FRS.collation_name
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS FRS

 

 

Das Ergebnis:

 

DESCRIBE_SC1.jpg

 

Interessant finde ich hier, wann ein berechnetes Feld als Nullable betrachtet wird und wann nicht.

 

Über den 3ten Parameter @include_browse_information kann man steuern, wie "tief" detailliert das Ergebnis ausfallen soll.
Wenn man z.B. sys.dm_exec_describe_first_result_set auf eine Abfrage auf eine View anwendet und dabei den Parameter mit Wert 0 angibt, erhält man "nur" die Spaltenwerte für das finalle Abfrageergebnis, wie zuvor gesehen.
Übergibt man den Parameter mit Wert = 1, erhält man über die "source_..." Spalten noch die Information, aus welcher Basistabelle die Spalten eigentlich kommen.
Gutes Beispiel ist hier die View vSalesPerson, die Werte aus diversen Tabellen zuück liefert:
 

DECLARE @sql nvarchar(max);

-- Abfrage auf View mit den Daten zu den Quelltabellen.
SET @sql =
N'SELECT *
FROM [AdventureWorks2012].[Sales].[vSalesPerson]';

SELECT FRS.column_ordinal
      ,FRS.system_type_name
      ,FRS.source_schema
      ,FRS.source_table
      ,FRS.source_column
      ,FRS.name AS finalname
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1) AS FRS;

Ergebnis:
DESCRIBE_SC2.jpg
Das funktioniert sogar bei Abfragen auf System Views

 

DECLARE @sql nvarchar(max);

-- Abfrage auf System Views.
SET @sql =
N'SELECT *
  
FROM sys.tables AS TBL
       INNER JOIN
       sys.columns AS COL
           ON TBL.object_id = COL.object_id';
SELECT FRS.column_ordinal
      ,FRS.system_type_name
      ,FRS.source_schema
      ,FRS.source_table
      ,FRS.source_column
      ,FRS.name AS finalname
      ,FRS.source_database
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1) AS FRS;

 

hier sieht man sogar, aus welcher Systemtabelle die Daten kommen:

 

DESCRIBE_SC3.jpg

Mit Hilfe der DMF kann man ein Statement überprüfen lassen, ob es syntaktisch Korrekt und ob es im aktuellen Kontext auch fehlerfrei ausgeführt werden. Treten Fehler auf, erhält man die Fehlermeldung im Ergebnis mit der column_ordinal = 0; normalerweise beginnt der Wert mit 1.
Das ist zum Beispiel hilfreich, wenn man SQL Statements von einer Appliaktion dynamisch zusammen mit Benutzereingaben erstellt und vor der Ausführung kontrollieren will, ob Fehler auftreten werden, bevor sie es tun.
Beispiel:
 
DECLARE @sql nvarchar(max);

-- Abfrage parsen & kompilieren lassen,
-- um evtl. Fehlermeldungen zu erhalten
SET @sql =
N'SELECT UnbekanntesFeld from sys.objects';

SELECT FRS.error_number
     
,FRS.error_severity
      ,FRS.error_state
      ,FRS.error_message
      ,FRS.error_type
      ,FRS.error_type_desc
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 0) AS FRS
WHERE FRS.column_ordinal = 0;
DESCRIBE_SC4.jpg
Siehe auch MSDN:
sys.dm_exec_describe_first_result_set (Transact-SQL): http://msdn.microsoft.com/de-de/library/ff878258.aspx
sys.dm_exec_describe_first_result_set_for_object (Transact-SQL): http://msdn.microsoft.com/de-de/library/ff878236.aspx
sp_describe_first_result_set (Transact-SQL): http://msdn.microsoft.com/de-de/library/ff878602.aspx
Weiterlesen

Verarbeiten von EZB Währungskursen

18. April 2011 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Umrechnungskurse für Währungen werden in vielen Fachbereichen und Anwendungsfällen benötigt. Der Börsenbroker benötigt sie im Sekundentakt für die Aktienbewertung, beim Verkauf von Import-Artikel mit Tagespreisen wird der aktuelle Tageskurs benötigt und die Buchhaltung verwendet für die Fremdwährung OP Bewertung einen Monats-Durchschnittskurs.

Die EZB Europäische Zentralbank veröffentlich die Tages-Umrechnungskurse seit Anfang 1999 als XML Datei im Internet. Man kann eine Datei mit den Kursen des letzten Banktages (TARGET Tag), der letzten 90 Tagen und die gesamte Aufstellung erhalten.

Diese Xml Daten bieten sich natürlich dazu an, sie einzulesen und per T-SQL weiter zu verarbeiten, um so eine Referenztabelle mit Umrechnungskursen zu pflegen. Etwas gekürzt sieht der Inhalt der Xml Datei so aus:

 <?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01"
xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time='2011-04-18'>
<Cube currency='USD' rate='1.4275'/>
<Cube currency='JPY' rate='118.25'/>
<Cube currency='BGN' rate='1.9558'/>
<Cube currency='CZK' rate='24.188'/>
</Cube>
</Cube>
</gesmes:Envelope>

 

Grundsätzlich ist das Verarbeiten kein Problem, allerdings muss man, um die Xml Daten überhaupt lesen zu können, zunächst die verwendete Namespace mittels WITH XmlNameSpaces deklarieren, hier gesmes:Envelope. Will man zugleich auch noch CTE (Common Table Expressions) verwenden, muss man nur berücksichtigen, das XmlNameSpaces an erste Stelle der WITH Anweisung gesetzt wird, die CTE’s dürfen erst darauf folgen.

Hat man das Namespace erst einmal deklariert, erfolgt das weitere Auslesen der Daten wie bei allen anderen Xml Daten auch. Das ganz Statement für eine Tabelle, das Einlesen und Umwandeln sieht dann so aus:

  -- ECB Währungskurse

 -- Tabelle für die Tageskurse anlegen, sofern noch nicht vorhanden. 
 IF OBJECT_ID('[dbo].[EcbDailyExchangeRates]', 'U ') IS NULL
CREATE TABLE [dbo].[EcbDailyExchangeRates]
( [ExcDate] date NOT NULL
,[Currency] char(3) NOT NULL
,[Rate] decimal(19, 6) NOT NULL
,CONSTRAINT [PK_EcbDailyExchangeRates] PRIMARY KEY CLUSTERED
( [ExcDate] ASC
,[Currency] ASC
) WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF
  ,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
);
GO
 -- Sourcen der Ecb Xml Dateien:
-- Letzter Tag: http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
-- Letzten 90 Tage: http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist-90d.xml
-- Komplett: http://www.ecb.int/stats/eurofxref/eurofxref-hist.xml

 -- Laden der Xml Datei in eine Variable 
 DECLARE @ecb XML;
SET @ecb = (SELECT CONVERT(xml, EcbSrc.BulkColumn) AS XmlRates
FROM OPENRowsET( BULK N'D:\eurofxref-hist.xml'
,SINGLE_BLOB) AS EcbSrc);
 -- Selektion der Kurse und Einfügen von fehlenden Werten 
;WITH XMLNAMESPACES
( 'http://www.gesmes.org/xml/2002-08-01' as gesmes
,DEFAULT 'http://www.ecb.int/vocabulary/2002-08-01/eurofxref')

 INSERT INTO [dbo].[EcbDailyExchangeRates]
( [ExcDate], [Currency], [Rate])
SELECT Cubes.Rows.value('@time', 'date') AS [ExcDate]
,Nodes.Rows.value('@Currency', 'char(3)') AS [Currency]
,Nodes.Rows.value('@Rate', 'decimal(19, 6)') AS [Rate]
FROM @ecb.nodes('/gesmes:Envelope/Cube/*') AS Cubes(Rows)
CROSS APPLY
Cubes.Rows.nodes('Cube') as Nodes(Rows)
LEFT JOIN
EcbDailyExchangeRates AS Dst
ON Dst.ExcDate = Cubes.Rows.value('@time', 'date')
AND Dst.Currency = Nodes.Rows.value('@Currency', 'char(3)')
WHERE Dst.ExcDate IS NULL
AND NOT Nodes.Rows.value('@Currency', 'char(3)') IS NULL;
 
Weiterlesen

Verlauf der Systemspeichernutzung ermitteln

24. Februar 2011 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Die DMV sys.dm_os_ring_buffers, die mit Microsoft SQL Server Version 2005 eingeführt wurde, gehört zu den wenigen, die offiziell nicht dokumentiert ist und in der BOL zu den DMVs zum Betriebssystem steht „..not supported …only for information … not guaranteed …“. Nun, für Applikationen würde ich die DMV als Informationsquelle nicht verwenden, aber ein Blick schadet nicht, vielleicht liefert sie doch interessante Informationen.

 

Die View selbst ist sehr „flach“, nur 4 Felder:

-       ring_buffer_address varbinary(8): Mit der kann ich noch nichts anfangen

-       ring_buffer_type nvarchar(60): Eine Typebezeichnung in Klartext

-       timestamp bigint: Weder datetime noch rowversion, sondern reine Zahlen

-       record nvarchar(2048): Die Daten sehen aus wie XML, sind nur nicht so deklariert

 

Der Inhalt von „timestamp“ war schnell geklärt, es sind die Ticks (= Millisekunden) seit dem Rechnerneustart, hat also nichts mit @@TIMETICKS zu tun. Über das Feld ms_ticks aus dem DMV sys.dm_os_sys_info kann man den aktuellen Ticks-Stand ermitteln und darüber auf den eigentlichen Zeitpunkt rückrechnen.

Welche Type es (aktuell) gibt, kann mit einer einfachen Abfrage ermitteln:

 

 SELECT ring_buffer_type 
  ,COUNT(*) AS Occured 
 FROM master.sys.dm_os_ring_buffers 
 GROUP BY ring_buffer_type 
 ORDER BY ring_buffer_type 

 

Ergebnis (auf Laptop, der kurz vorher gestartet wurde):

 

ring_buffer_type

Occured

RING_BUFFER_MEMORY_BROKER

6

RING_BUFFER_RESOURCE_MONITOR

1

RING_BUFFER_SCHEDULER

824

RING_BUFFER_SCHEDULER_MONITOR

18

RING_BUFFER_XE_BUFFER_STATE

4

RING_BUFFER_XE_LOG

1

 

Na, das erste, was hier mein Interesse weckt, ist natürlich RING_BUFFER_RESOURCE_MONITOR. Wie schon erwähnt, sehen die Daten in „record“ wie XML aus und in den Datentypen kann man es auch konvertieren. Ein solcher „record“ für einen Resource_Monitor Eintrag sieht so aus:

 

 <Record id="0" type="RING_BUFFER_RESOURCE_MONITOR" time="1261574341"> 
  <ResourceMonitor> 
  <Notification>RESOURCE_MEMPHYSICAL_HIGH</Notification> 
  <IndicatorsProcess>0</IndicatorsProcess> 
  <IndicatorsSystem>1</IndicatorsSystem> 
  <NodeId>0</NodeId> 
  <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect> 
  <Effect type="APPLY_HIGHPM" state="EFFECT_ON" reversed="0">0</Effect> 
  <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect> 
  </ResourceMonitor> 
  <MemoryNode id="0"> 
  <ReservedMemory>1671680</ReservedMemory> 
  <CommittedMemory>16636</CommittedMemory> 
  <SharedMemory>0</SharedMemory> 
  <AWEMemory>0</AWEMemory> 
  <SinglePagesMemory>2496</SinglePagesMemory> 
  <MultiplePagesMemory>10232</MultiplePagesMemory> 
  </MemoryNode> 
  <MemoryRecord> 
  <MemoryUtilization>100</MemoryUtilization> 
  <TotalPhysicalMemory>2086596</TotalPhysicalMemory> 
  <AvailablePhysicalMemory>832044</AvailablePhysicalMemory> 
  <TotalPageFile>4416176</TotalPageFile> 
  <AvailablePageFile>2442676</AvailablePageFile> 
  <TotalVirtualAddressSpace>2097024</TotalVirtualAddressSpace> 
  <AvailableVirtualAddressSpace>326988</AvailableVirtualAddressSpace> 
  <AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace> 
  </MemoryRecord> 
 </Record> 

 

Die Namen der Tags sind wie die aus den Speicher Performance Counters von Windows. XML lässt sich in SQL Server ab 2005 prima abfragen und das machen wir mal.

Auf einem Produktiv-System ausgeführt gibt es einige „RESOURCE_MEMPHYSICAL_HIGH“ Einträge; was für einen Server mit laufenden MS SQL Server aber auch nichts Ungewöhnliches ist.

Aber so kann man mit einfachen Mitteln sein System etwas monitoren und das auch noch rückwirkend.

 

 DECLARE @actualTS BIGINT 
 SET @actualTS = (SELECT ms_ticks FROM sys.dm_os_sys_info) 
 PRINT @actualTS 
   
 SELECT ORB.EventTime 
  ,ORB.Notif 
  ,ORB.AvailPhyMem 
  ,ROUND(CONVERT(float, 100 * AvailPhyMem) / TotalPhyMem, 3) AS PercentAvailable 
  ,ORB.MemUtil 
  ,ORB.ResvMem 
  ,ORB.CommMem 
  ,ORB.recordXml 
 FROM (SELECT DATEADD(s, EventTs, GETDATE()) AS EventTime 
  ,ORB.recordXml.value('(./Record/ResourceMonitor/Notification)[1]', 'varchar(50)') AS Notif 
  ,ORB.recordXml.value('(./Record/MemoryNode/ReservedMemory)[1]', 'int') AS ResvMem 
  ,ORB.recordXml.value('(./Record/MemoryNode/CommittedMemory)[1]', 'int') AS CommMem 
  ,ORB.recordXml.value('(./Record/MemoryRecord/MemoryUtilization)[1]', 'int') AS MemUtil 
  ,ORB.recordXml.value('(./Record/MemoryRecord/TotalPhysicalMemory)[1]', 'int') AS TotalPhyMem 
  ,ORB.recordXml.value('(./Record/MemoryRecord/AvailablePhysicalMemory)[1]', 'int') AS AvailPhyMem 
  ,recordXml 
  FROM (SELECT CONVERT(int, (ORB.timestamp - @actualTS) / 1000) AS EventTs 
  ,CONVERT(xml, record) AS RecordXml 
  FROM master.sys.dm_os_ring_buffers AS ORB 

            WHERE ORB.ring_buffer_type = N'RING_BUFFER_RESOURCE_MONITOR'

            ) AS ORB

      ) AS ORB

ORDER BY EventTime desc

Weiterlesen

Microsoft SQL Server FileStream

5. Mai 2010 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

SQL FileStream zum effektiven Speichern von BLOBs = Binary Large Objects ist im Microsoft SQL Server ab Version 2008 neu hinzugekommen. Es kombinierte die Vorteile der Ablage im SQL Server als VarBinary Daten mit der Möglichkeit der transaktionellen Verarbeitung und der Ablage im File-System mit der performanten Zugriffsmöglichkeit über Windows Streaming / API Zugriff.

 

Es gibt ein wirklich sehr gutes und umfangreiches Whitepaper „FILESTREAM Storage in SQL Server 2008“ im MSDN Bereich von Paul S. Randall, das alle Aspekte rund um FileStream beleuchtet. Was soll ich dem hinzufügen?

 

Ich habe nun auch mal etwas experimentiert und ein paar erste Erfahrungen gesammelt:

·         Mein erster Test war unter Windows XP mit MS SQL Server 2008 Express Edition ohne SP, der unter dem Account „NetworkServices“ lief. Die Anlage der Dateigruppe für FileStream schlug mit einer nicht gerade aussagekräftigen Fehlermeldung fehl wie
Unable to open the physical file "c:\Program Files\Microsoft SQL Server\...". Operating system error -2147024891: "0x80070005(Access is denied.)"
 Eine kurze Recherche ergab, dass der Dienst unter einem Windows Account laufen muss, damit es funktioniert. Einen entsprechenden Eintrag gibt es bereits bei MS Connect. Mit meiner Developer Edition unter Windows Vista mit Dienstkonto „Local System“ hingegen funktionierte es ohne Änderungen.

·         Änderungen an FileStream Daten, also UPDATE und DELETE, werden nicht direkt im Transaktionsprotokoll protokolliert, sondern in Form von revisionierten Dateien. Diese verbleiben im Dateisystem bis zur nächsten Voll- bzw. Log-Sicherung; das muss man bei der Planung des Speicherplatzes beachten.

 

 USE [master] 
 GO 
 EXEC sp_configure filestream_access_level, 2; 
 GO 
 RECONFIGURE; 
 GO 
   
 ALTER database AdventureWorks 
 ADD FILEGROUP [FileStreamFG] 
 CONTAINS FILESTREAM 
 GO 
   
 ALTER database AdventureWorks 
 ADD FILE 
 ( 
  NAME= 'FileStream', 
  FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL10DEV\FG' 
 ) 
 TO FILEGROUP [FileStreamFG] 
 GO 
   
 USE [AdventureWorks] 
 GO 
   
 CREATE TABLE dbo.FileStreamStorage 
  ( 
  [Id] int NOT NULL IDENTITY(1, 1) PRIMARY KEY, 
  [Guid] uniqueidentifier NOT NULL UNIQUE ROWGUIDCOL DEFAULT NEWID(), 
  [FileName] varchar(255) NOT NULL, 
  [FileStreamData] varbinary(MAX) FILESTREAM NULL  
  ) ON [PRIMARY] 
  FILESTREAM_ON [FileStreamFG] 
 GO 
   
 INSERT INTO dbo.FileStreamStorage ([FileName], [FileStreamData]) 
 VALUES ('Test1.txt', CONVERT(varbinary(max), N'Hello World')); 
   
 INSERT INTO dbo.FileStreamStorage ([FileName], [FileStreamData]) 
 VALUES ('Test2.txt', CONVERT(varbinary(max), N'Hello World again')); 
   
 INSERT INTO dbo.FileStreamStorage ([FileName], [FileStreamData]) 
 VALUES ('Test3.txt', CONVERT(varbinary(max), N'Hello World again')); 
   
 INSERT INTO dbo.FileStreamStorage ([FileName], [FileStreamData]) 
 VALUES ('Test4.txt', CONVERT(varbinary(max), 'Hello World again')); 
 GO 
   
 DELETE FROM dbo.FileStreamStorage 
 WHERE [Id] <= 4; 
Weiterlesen

Referenzdaten Bankleitzahlen

30. April 2010 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Gute, weil vollständige und korrekte Referenzdaten, sind schwierig zu finden; in der Regel kann man sie nur bei kommerziellen Anbieter für einiges an Geld beziehen.

Referenzdaten, die dann auch noch regelmäßig aktualisiert werden und zu allem Überfluss auch noch frei verfügbar sind, sind eine Seltenheit.

Das Verzeichnis für deutsche Bankleitzahlen ist so eine Seltenheit. Sie wird von der Deutschen Bundesbank herausgegeben, ist auf deren Seite frei verfügbar und wird auch quartalsweise aktualisiert. Eine Dokumentation des Dateiformates gibt es auch.

Das Dateiformat ist einfach gehalten, es eine Textdatei und die Spalten haben jeweils eine fester Breite. In dem Fall ist die einfachste Möglichkeit, die Daten zu importieren, mit OpenRowSet und der Bulk Option zu arbeiten. Als ich mir die Dokumentation dazu durchgelesen habe, fiel mir auf, dass zwar ausführlich die Anwendung für zeichengetrennte Formate beschrieben ist inklusive Beispiele, aber in keiner Weise, wie man Daten mit fester Spaltenbreite handhabt. Also Mut zur Lücke, eine Formatdatei definiert ohne Trennzeichen und die Spaltenbreite angegeben; die maximal Breite muss man eh festlegen und siehe da, der Import per BULK funktioniert einwandfrei.

Kleines Problem ist, das der Dateiname „versioniert“ wird, also der Dateiname das Datum der Veröffentlichung beinhaltet. Zwar gibt es fest definierte Regeln, wann das neue Verzeichnis veröffentlicht wird, man kann das Datum errechnen, aber das erschwert den Automatismus, um die Datei per SSIS über FTP / http herunter zu laden, um sie dann weitere zu verarbeiten.

 

Hier nun der Aufbau der Format-Datei und das Statement für den Bulk – Import; die gesamte SSMS Solution gibt es auch zum Download, sie beinhaltet auch das Tabellen Design.

 

Format Datei BLZ_Format.txt:

 

 8.0 
 13 
 1 SQLCHAR 0 8 "" 1 Blz "" 
 2 SQLCHAR 0 1 "" 2 BlzFuehrend "" 
 3 SQLCHAR 0 58 "" 3 Bezeichnung "" 
 4 SQLCHAR 0 5 "" 4 Plz "" 
 5 SQLCHAR 0 35 "" 5 Ort "" 
 6 SQLCHAR 0 27 "" 6 Kurzbezeichnung "" 
 7 SQLCHAR 0 5 "" 7 InstitutsnummerPAN "" 
 8 SQLCHAR 0 11 "" 8 Bic "" 
 9 SQLCHAR 0 2 "" 9 PruefzifferMethode "" 
 10 SQLCHAR 0 6 "" 10 Nummer "" 
 11 SQLCHAR 0 1 "" 11 Aenderung "" 
 12 SQLCHAR 0 1 "" 12 Loeschung "" 
 13 SQLCHAR 0 8 "\n" 13 NachfolgerBlz "" 

 

Das SQL Statement für den Import über OpenRowSet.

 

 -- Daten einfügen 
 INSERT INTO [BLZ].[dbo].[Blz] 
  ([Blz] 
  ,[BlzFuehrend] 
  ,[Bezeichnung] 
  ,[Plz] 
  ,[Ort] 
  ,[Kurzbezeichnung] 
  ,[InstitutsnummerPAN] 
  ,[Bic] 
  ,[PruefzifferMethode] 
  ,[Nummer] 
  ,[Aenderung] 
   ,[Loeschung] 
  ,[NachfolgerBlz])  
 SELECT [Blz] 
  ,CONVERT(tinyint, [BlzFuehrend]) AS BlzFuehrend 
  ,RTRIM([Bezeichnung]) AS [Bezeichnung] 
  ,[Plz] 
  ,RTRIM([Ort]) AS [Ort] 
  ,RTRIM([Kurzbezeichnung]) AS [Kurzbezeichnung] 
  ,CONVERT(int, CASE WHEN [InstitutsnummerPAN] = '' 
  THEN NULL 
  ELSE [InstitutsnummerPAN] 
  END) AS [InstitutsnummerPAN] 
  ,CASE WHEN [Bic] = ''  
   THEN NULL 
  ELSE [Bic] END AS [Bic] 
  ,[PruefzifferMethode] 
  ,CONVERT(int, [Nummer]) AS [Nummer] 
  ,[Aenderung] 
  ,CONVERT(bit, [Loeschung]) AS [Loeschung] 
  ,[NachfolgerBlz] 
 FROM OPENROWSET(BULK N'D:\Projekte\Blz\blz_20100308.txt' 
  ,FORMATFILE = N'D:\Projekte\Blz\blz_format.txt' 
  ,CODEPAGE = 'OEM'  
  ,ERRORFILE = N'D:\Projekte\Blz\blz_error.txt' 
  ) AS Blz 
 GO 
Weiterlesen

Übersicht der Objekte je Dateigruppe

22. Januar 2010 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Das Boardmittel der Dateigruppen gibt es schon ewig im Microsoft SQL Server und das in allen Editionen, einschließlich der Express Editon (ok, in der Compact Edition gibt es das nicht). Je datenspeicherndem Objekt, also für Tabellen, Indizes, separate je Tabelle für BLOB Felder und auch für Partitionen, kann man festlegen, in welcher Dateigruppe es gespeichert werden soll. Einzige Ausnahme sind die PrimaryKey-Indizes, die werden immer in der gleichen Dateigruppe gespeichert; logischerweise, da sie physikalisch zusammen hängen.

Zum Organisieren von großen (in Hinsicht von Speicherplatz) Datenbanken ist das ein gutes und einfaches Mittel, um den verwendeten Speicherplatz über mehrere Dateien und damit Laufwerke hinweg zu verteilen. Zudem kann man Dateigruppen einzeln Sichern & Wiederherstellen. Last but not least kann man einzelne Dateigruppen auf „ReadOnly“ setzten, um so zum Beispiel Archive-Daten zuverlässig vor Änderungen zu schützen (inkl. durch dem DBA; also einem Selbst,). Folglich braucht man die Daten auch nur einmalig sichern; das spart Backup-Ressourcen.

Wenn man bei der Anlage von Objekte konsequent die richtige Dateigruppe berücksichtigt, ist ja auch alles in Ordnung. Aber wie findet man heraus, welches Objekt in welcher Dateigruppe angelegt wurde. Hinzu kommt evtl. noch, dass man Tabellen partitioniert hat und die Partitionen über mehrere Dateigruppen verteilt sind.

Der SQL Server weiß es natürlich und über die System-Views kann man die Daten auch abfragen; hier das T-SQL Statement dazu:

 

 -- Auflistung der Objekte + Indizes  
 -- je Dateigruppe / Partion 
 SELECT DS.name AS DataSpaceName 
  ,AU.type_desc AS AllocationDesc 
  ,AU.total_pages / 128 AS TotalSizeMB 
  ,AU.used_pages / 128 AS UsedSizeMB 
   ,AU.data_pages / 128 AS DataSizeMB 
  ,SCH.name AS SchemaName 
  ,OBJ.type_desc AS ObjectType  
  ,OBJ.name AS ObjectName 
  ,IDX.type_desc AS IndexType 
  ,IDX.name AS IndexName 
 FROM sys.data_spaces AS DS 
  INNER JOIN  
  sys.allocation_units AS AU 
  ON DS.data_space_id = AU.data_space_id 
  INNER JOIN sys.partitions AS PA 
  ON (AU.type IN (1, 3)  
  AND AU.container_id = PA.hobt_id) 
  OR 
  (AU.type = 2 
  AND AU.container_id = PA.partition_id) 
  INNER JOIN sys.objects AS OBJ 
  ON PA.object_id = OBJ.object_id 
  INNER JOIN sys.schemas AS SCH 
  ON OBJ.schema_id = SCH.schema_id 
  LEFT JOIN sys.indexes AS IDX 
  ON PA.object_id = IDX.object_id 
  AND PA.index_id = IDX.index_id 
 ORDER BY DS.name 
  ,SCH.name 
  ,OBJ.name
,IDX.name
Weiterlesen

Read / Write Aufkommen je Datenbank

20. Januar 2010 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Jede Datenbank im produktiven Betrieb wird etwas anders verwendet. In die eine wird meistens in zyklischen Abständen geschrieben und nur selten mal gelesen; das wären zum Beispiel Archive-Datenbanken für Alt-Daten. Dann gibt es Datenbanken, in die im laufenden Betrieb in erster Linie die aktuellen Transaktionen mit hohem Detailgrad geschrieben werden, sogenannte OLTP Systeme. Die Daten hier raus werden dann leicht desnormalisiert & voraggregiert in Dataware-House System für weitere Auswertungen überführt; in ein OLAP System. Aus diesen wird naturgemäß mehr gelesen als geschrieben.

Als DBA sollte man die Verwendung seiner Datenbanken kennen, um Speicherplatz entsprechend planen zu können. Archive-Datenbanken ändern sich kaum, werden sowieso gesichert und kaum abgefragt, also kann man sie auf ein einfaches (günstigen) Festplattensystem ablegen. OLAP Datenbanken beruhen auf den Produktiv-Datenbanken, mal von Sicherungen abgesehen, können sie eh wieder neu aufgebaut werden; da sind vor allem schnelle Systeme gefragt wie RAID0, um Abfragen fürs Reporting schnell abzuarbeiten. OLTP hingegen sind das Herzstück der Datenhaltung, hier sollte man immer in ein sicheres sowie auch schnelles Speichersystem investieren.

Im Microsoft SQL Server 2005 (und auch 2008) kann man in einem gewissen Rahmen über die DMV (Dynamic Management Views) Statistiken die Anzahl von gelesenen und geschriebenen Seiten je Datenbank ermitteln. Ausgangspunkt ist dabei die DMV sys.dm_exec_query_stats, die schon für die häufigsten verwendeten Abfragen verwendet wurde und die benötigen Statistiken bereit hält.

Setzt allerdings voraus, das der Server schon eine Zeit lang läuft und die Statistiken aufgebaut sind und wie schon erwähnt mit parametrisierten Abfragen gearbeitet wird, sonst ist jedes (dynamisch generiertes) Statement ein eigenständiges. Nicht immer steht der Datenbank-Kontext für ein Statement fest, diese Queries werden dann unter „AdHoc Queries“ aufgeführt.

Grob aus Erfahrungswerten raus geschätzt, ist in „normalen“ ERP Datenbanken das Verhältnis von Write – Read ~ 1:20. Wie gesagt, das variiert je nach Verwendung.

Mit dem folgenden Statement kann man sich also eine Übersicht verschaffen, wichtig sind hier die LogicalReads und LogicalWrites:

 

 -- Übersicht der Logical Reads & Writes je Datenbank 
 -- Beruht auf den DMV Statistiken. 
 SELECT ISNULL(DB.name, 'AdHoc Queries') AS DatabaseName 
  ,SUM(EQS.execution_count) AS ExecutionCounts 
  ,SUM(EQS.total_worker_time) AS WorkerTime 
  ,SUM(EQS.total_physical_reads) AS PhysicalReads 
  ,SUM(EQS.total_logical_reads) AS LogicalReads 
  ,SUM(EQS.total_logical_writes ) AS LogicalWrites 
 FROM sys.dm_exec_query_stats AS EQS 
  CROSS APPLY sys.dm_exec_sql_text(EQS.sql_handle) as EST 
  LEFT JOIN sys.databases AS DB 
  ON EST.dbid = DB.database_id 
 GROUP BY ISNULL(DB.name, 'AdHoc Queries')
ORDER BY LogicalReads DESC, LogicalWrites DESC
Weiterlesen

Von DataSet zu Coma Separated List und dann wieder zurück

18. Dezember 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Es ist eine häufige Anforderung, Werte von mehreren Datensätze aus einem MS Sql Server per T-SQL als eine Coma Separated List (csv) auszugeben, z.B. im Reporting.

Analog dazu gibt es die Anforderung zum Umkehrschluß: Man hat einen csv-String und möchte das als RecordSet erhalt, um z.B. als Referenz darauf zu JOINen.

Wie geht’s? Mit ein paar kleinen Tricks recht einfach.

Zunächst DataSet => csv

Hier gibt es wie immer mehrere Varianten, ich zeige 2. Die eine kumuliert die Wert über eine Variable und gibt die dann aus, die andere verwendet die XML Funktion (ab Version 2005) und ersetzt für das Ergebnis die Tags.

 

 SET NOCOUNT ON; 
 -- Werte anlegen 
 CREATE TABLE #csv 
  (value varchar(3)); 
 INSERT INTO #csv VALUES ('Jan'); 
 INSERT INTO #csv VALUES ('Feb'); 
 INSERT INTO #csv VALUES ('Mar'); 
 INSERT INTO #csv VALUES ('Apr'); 
 INSERT INTO #csv VALUES ('May'); 
 INSERT INTO #csv VALUES ('Jun'); 
 INSERT INTO #csv VALUES ('Jul'); 
 INSERT INTO #csv VALUES ('Aug'); 
 INSERT INTO #csv VALUES ('Sep'); 
 INSERT INTO #csv VALUES ('Oct'); 
 INSERT INTO #csv VALUES ('Nov'); 
 INSERT INTO #csv VALUES ('Dec'); 
 GO 
   
 -- Weg über eine Variable 
 DECLARE @csv varchar(200) = ''; 
 SELECT @csv = @csv + value + ';' 
 FROM #csv 
 -- Liste ausgeben 
 SELECT @csv AS csv; 
   
 -- Über XML; da muss zwischendurch konvertiert und Tags entfernt werden 
 DECLARE @sep char(1); 
 SET @sep = ';'; 
 SELECT REPLACE(REPLACE(StrTbl.StrValue, '<tag>', ''), '</tag>', '') AS csv 
 FROM  
  (SELECT StrValue = 
  CONVERT(varchar(400), 
  (SELECT tag = Value + @sep 
  FROM #csv 
  FOR XML PATH ('tag'), Type 
  ) 
  ) 
  ) AS StrTbl 
   
 GO -- Aufräumen 
 DROP TABLE #csv; 

 

Ergebnis:

 

 csv 
 ----------------------------------------------- 
 Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec; 
   
 csv 
 ----------------------------------------------- 
 Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec; 

 

 

So, und nun wieder zurück. Es gibt einen csv String und der soll nun wieder aufgesplittet. Auch nicht wirklich aufwendig, wenn man es über eine CTE (Common Table Expression) erledigt:

 

 DECLARE @csv varchar(200); 
 DECLARE @sep char(1); 
   
 -- Voraussetzung: Abschließender Separator 
 SET @csv = 'Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec;'; 
 SET @sep = ';'; 
   
 WITH cte (Id, Value, Rest) 
 AS (SELECT 1 as Id 
  ,SUBSTRING(@csv, 1, CHARINDEX(@sep, @csv, 1) - 1) AS Value 
  ,SUBSTRING(@csv, CHARINDEX(@sep, @csv, 1) + 1, LEN(@csv)) AS Value 
  UNION ALL 
  SELECT Id + 1 AS Id 
  ,SUBSTRING(Rest, 1, CHARINDEX(@sep, Rest, 1) - 1) AS Value 
  ,SUBSTRING(Rest, CHARINDEX(@sep, Rest, 1) + 1, LEN(Rest)) AS Value 
  FROM cte 
  WHERE CHARINDEX(@sep, Rest, 1) <> 0) 
 SELECT Id, Value 
 FROM cte;  

 

Ergebnis:

 

 Id Value 
 ----------- ------ 
 1 Jan 
 2 Feb 
 3  Mar 
 4 Apr 
 5 May 
 6 Jun 
 7 Jul 
 8 Aug 
 9 Sep 
 10 Oct 
 11 Nov 
 12 Dec 
Weiterlesen

CheckDB mit auswertbarem Ergebnis

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

Zu den regelmäßigen und wichtigsten Wartungsarbeiten eines DBA (Database Administrator) gehört das Ausführen aller Datenbanken mittels DBCC CHECKDB(0);, um sie auf Konsistenz und Fehlerfreiheit zu überprüfen.

Das Problem ist nur, das je komplexer die Datenbank ist, so umfangreicher das Protokoll als Ergebnis ist. Diese Ergebnisliste durchzusehen ist aufwendig und es kommt leicht vor, dass man wichtige Meldungen übersieht. Da wäre es doch besser, wenn man das Protokoll auf bestimmte Ergebnisse hin selektieren kann.

Das kann man mit dem folgenden Script, es schreibt das ResultSet in eine temporäre Tabelle und selektiert daraus die wichtigsten Eckdaten und kumulierte Werte.

Das wäre in abgewandelter Form eine gute Basis für einen „benutzerdefinierten Report“ in SSMS; muss ich aber noch dran arbeiten.

Ebenso wäre interessant, eine History-Tabelle zu verwenden, um den Datenbank-Status zeitlich zu protokollieren; schaden kann es jedenfalls nicht.

 

 -- Für MS SQL Server 2005 
 -- CheckDB für die aktuelle Datenbank ausführen und 
 -- auswerten (Status-Übersicht und Details). 
 -- Die Mindest-Stati Werte können konfiguriert werden. 
 DECLARE @MinState int, @MinStatus int, @MinLevel int 
   
 SET @MinState = 1; 
 SET @MinStatus = 0 
 SET @MinLevel = 10; 
   
 -- Temporäre Tabelle für das CheckDB Ergebnis 
 CREATE TABLE #CheckDB 
  ([Error] int, [Level] int, [State] int,  
  [MessageText] varchar(7000), [RepairLevel] int, [Status] int, 
  [DbId] int, [ObjectID] int, [IndexId] int, [PartitionId] int, 
  [AllocUnitId] int, [File] int, [Page] int, [Slot] int,  
  [RefFile] int, [RefPage] int, [RefSlot] int, [Allocation] int); 
   
 -- CheckDB ausführen und Ergebnis in die TempTabelle 
 INSERT INTO #CheckDB 
  ([Error], [Level], [State], [MessageText], [RepairLevel],  
  [Status], [DbId], [ObjectID], [IndexId], [PartitionId], 
  [AllocUnitId], [File], [Page], [Slot], [RefFile],  
  [RefPage], [RefSlot], [Allocation]) 
 EXEC ('DBCC CHECKDB(0) WITH TABLERESULTS'); 
   
 -- Die schließende Meldung mit der Zusammenfassung 
 SELECT [MessageText] 
 FROM #CheckDB 
 WHERE [Error] = 8989; 
   
 -- Übersicht mit Anzahl je Stati 
 SELECT CDB.Error, CDB.Level, CDB.State, CDB.Status, 
  CDB.RepairLevel, COUNT(*) AS CountOccurence 
 FROM #CheckDB AS CDB 
 WHERE CDB.State >= @MinState 
  AND CDB.Status >= @MinStatus 
  AND CDB.Level >= @MinLevel 
 GROUP BY CDB.Error, CDB.Level, CDB.State, 
  CDB.Status, CDB.RepairLevel; 
   
 --Ergebnis-Details selektieren 
 SELECT OBJ.name AS ObjName, OBJ.type_desc AS ObjType, 
  IDX.Name AS IndexName, 
  ALU.type_desc AS AllocationType, 
  CDB.Error, CDB.Level, CDB.State, CDB.Status, 
  CDB.RepairLevel, MessageText 
 FROM #CheckDB AS CDB 
  LEFT JOIN sys.objects AS OBJ 
  ON CDB.ObjectId = OBJ.object_id 
  LEFT JOIN sys.indexes AS IDX 
  ON CDB.ObjectId = IDX.object_id 
  AND CDB.IndexID = IDX.index_id 
  LEFT JOIN sys.allocation_units AS ALU 
  ON CDB.AllocUnitId = ALU.allocation_unit_id 
 WHERE CDB.State >= @MinState 
  AND CDB.Status >= @MinStatus 
  AND CDB.Level >= @MinLevel 
 ORDER BY ObjType, ObjName, IndexName; 
 GO 
   
 -- Aufräumen 
 DROP TABLE #CheckDB; 
 GO 
Weiterlesen

Sperren und die betroffenen Objekte ermitteln

11. November 2009 , Geschrieben von Olaf Helper Veröffentlicht in #T-SQL

Das bei DML Aktionen Sperren entstehen, weiß jeder. Wenn man sich in nicht das gerade einfache & trockene Thema einliest und seine Daten kennt, kann in etwa abschätzen, wann welche Sperren entstehen.

Im alten Aktivitätsmonitor oder mit der mittlerweile abgekündigten System Stored Procedure EXEC sp_lock kann man sich die Sperren anzeigen, allerdings ist die Auskunft nicht so informativ.

Über die DMV des Microsoft SQL Server 2005 kann man mehr Informationen ermitteln, wie z.B. auf welche Objekte Sperren liegen.

Interessant ist es dann auch mal mit den unterschiedlichen Isolation Levels oder Table-Hints zu experimentieren. Oder zu sehen, welche weitere Sperren durch ForeignKey Constraints oder History-Trigger entstehen. Zum Experimentieren startet man einfach eine explizite Transaktion, führt das DML Statement aus, wartet aber mit dem Commit. Dann kann man ich Ruhe die Sperren selektieren und committed erst dann. Das sollte aber tunlichst nur auf Test-Systemen machen, sonst gilt: Transaktionen so kurz wie nur irgend möglich.

Beispiel:

 

 USE [AdventureWorks2005] 
 GO 
   
 BEGIN TRANSACTION; 
   
 UPDATE Person.Person WITH (RowLock)  
 -- oder PagLock, TabLock, TabLockX 
 SET FirstName = RTRIM(FirstName) 
 WHERE BusinessEntityID = 1; 
   
 -- Mit dem Commit warten; 
 -- erst die Sperren selektieren 
 COMMIT TRANSACTION; 

 

Hier ein Beispiel für die Selektion der Sperren und deren Daten; wenn es interessiert, kann weitere Felder der Tabellen mit selektieren:

 

 -- Ermitteln aller Sperren einer Datenbank mit den 
 -- Daten der betroffenen Objekte für Sql Server 2005 
 SELECT DB.name AS DatabaseName 
  ,TL.request_owner_type AS RequestType 
  ,TL.request_status AS RequestStatus 
  ,TL.request_mode AS RequestMode  
  ,ES.session_id AS SPID 
  ,ES.login_name AS LoginName 
  ,ES.status as SessionStatus 
  ,TL.resource_type AS ResourceType 
  ,TL.resource_description AS ResourceDescription 
  ,COALESCE(SO.name, PO.name, DB.name) AS ObjectName 
  ,COALESCE(SO.type_desc, PO.type_desc, 'DATABASE') AS ObjectType 
  ,IX.name as IndexName 
  ,OWT.wait_type AS OsWaitType 
  ,OT.task_state AS OsTaskState 
  ,OT.pending_io_count AS OsTaskPendingIo 
 FROM sys.dm_tran_locks AS TL 
  INNER JOIN sys.databases AS DB 
  ON TL.resource_database_id = DB.database_id 
  INNER JOIN sys.dm_exec_sessions AS ES 
  ON TL.request_session_id = ES.session_id 
  LEFT JOIN sys.dm_os_waiting_tasks AS OWT 
   ON TL.lock_owner_address = OWT.resource_address 
  LEFT JOIN sys.dm_os_tasks AS OT 
  ON ES.session_id = OT.session_id 
  LEFT JOIN sys.objects AS SO 
  ON TL.resource_associated_entity_id = SO.object_id 
  AND TL.resource_type = 'OBJECT' 
  LEFT JOIN sys.partitions AS PT 
  ON TL.resource_associated_entity_id = PT.hobt_id 
  AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT') 
  LEFT JOIN sys.objects AS PO 
  ON PT.object_id = PO.object_id 
  LEFT JOIN sys.indexes AS IX 
  ON PT.index_id = IX.index_id 
  AND PT.object_id = IX.object_id 
 WHERE 1 = 1 
  -- Optimale Filter; einfach entkommentieren 
  --AND TL.resource_database_id = DB_ID() -- Nur aktuelle DB betrachten 
  --AND ES.status <> 'sleeping' -- Keine schlafende Prozesse 
  --AND SO.type_desc = 'USER_TABLE' -- Nur Sperren auf Tabelle 
  --AND SO.name = 'Persons' -- Nur zu einem Objekt 
  --AND TL.request_mode LIKE '%X'  -- Nur exklusive Locks 
 ORDER BY DB.name, ObjectName, ResourceType 
  ,IndexName, ES.session_id 
Weiterlesen
1 2 3 4 5 6 > >>