Wenner-Online, XLDev

private Homepage

mcts on sql server 2005 mcp on sql server 2000 1und1
Hier stelle ich meine "kleine" Sammlung von T-SQL Code Snippets für Microsoft SQL Server zu Verfügung. Die Skripts laufen in den meisten Fällen auf SQL Server >= 2005.
Weitere Vorschläge und/oder Anregungen nehme ich gerne entgegen.

typeExklusiven Zugriff auf eine SQL Server Datenbank per T-SQL forcieren

20.06.2011 posted by fwenner

Wenn man auf eine Datenbank eines SQL Server exklusiven Zugriff benötigt (z.B. für RESTORE DATABASE) kann dieses auch per T-SQL realisiert werden. 
-- set single user

ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- drop the database

DROP DATABASE [DatabaseName]

-- restore the database

RESTORE DATABASE [DatabaseName] FROM  DISK = N'C:\Test.bak' WITH

typeGet identity values of a bulk insert

09.05.2011 posted by fwenner

Mit der SQL Server OUTOUT Clause kann man sich bei einem Massen-Insert die von der Datenbank vergebenen IDs zurückliefern lassen.
Mehr dazu: http://msdn.microsoft.com/en-us/library/ms177564.aspx 
CREATE TABLE dbo.TestTable

(

  TestID INT NOT NULL IDENTITY (1, 1),

  TestValue VARCHAR(255) NULL,

  CreatedOn DATETIME DEFAULT GETDATE() NOT NULL

)



INSERT INTO dbo.TestTable (TestValue)

OUTPUT INSERTED.TestID, INSERTED.CreatedOn

SELECT Name FROM master.dbo.syscolumns

typeeine Liste von Werten von vertikal nach horizontal bringen

19.11.2010 posted by fwenner

Hin und wieder wird eine Liste von Werten nicht in Form von Zeilen benötigt sondern als String-Kette.
Hier sehen Sie wie es ganz einfach möglich ist eine solche Liste zu erstellen.

DECLARE @Databases VARCHAR(MAX)

SET @Databases = ''

SELECT @Databases = @Databases ✛ ', ' ✛ name FROM sys.databases

SET @Databases = STUFF(@Databases, 1, 2, '')

SELECT @Databases

typeTesten ob ein String UNICODE ist oder nicht

19.11.2010 posted by fwenner

Mit dieser SQL Funktion kann man testen ob ein String, der als NVARCHAR übergeben wird, ein UNICODE String ist.
Das Ganze funktioniert über einen Test ob das Higher-Byte des Strings gefüllt ist oder nicht.

CREATE FUNCTION fn_IsUnicode(@String NVARCHAR(1000)) RETURNS INT AS

BEGIN

  DECLARE @Len INT

  DECLARE @Cnt INT

  SET @Len = LEN(@String)

  SET @Cnt = 0

  WHILE @Cnt < @Len

  BEGIN

    SET @Cnt = @Cnt   1

    IF (UNICODE(SUBSTRING(@String, @Cnt, 1)) & -256) / 255 > 0

      RETURN @Cnt

  END

  RETURN 0

END

typeProbleme mit Seiten- und Zeilenanzahl beheben

20.08.2010 posted by fwenner

Nach einer Migration von SQL Server 2000 Datenbanken auf SQL Server >= 2005 kann es zu Problemen mit falschen Werte bei den Seiten- und Zeilenzahlen in den Katalogsichten geben. Diese Ungenauigkeiten können falsche Angaben über die Speicherplatzverwendung verursachen. Mit folgendem Statement werden diese Information für die angegebene Datenbank korrigiert. 
DBCC UPDATEUSAGE ("Name der Datenbank")

typeSnapshot-Isolation verwenden und aktivieren

20.08.2010 posted by fwenner

Microsoft stellt ab SQL Server 2005 einen neuen Snapshot-Isolationsgrad bereit, um die Parallelität Anwendungen zu erhöhen. In früheren Versionen von SQL Server basierte diese Parallelität nur auf Sperren, wodurch in machen Anwendungen Probleme mit Deadlocks entstanden. Snapshot-Isolation beruht der Zeilenversionserstellung und soll durch das Vermeiden von Lese-Schreib-Blockierungen die Leistung steigern. Zur Aktivierung müssen die folgenden Statements ausgeführt werden. 
-- der Datenbank SNAPSHOT_ISOLATION erlauben

ALTER DATABASE "Name der Datenbank" SET ALLOW_SNAPSHOT_ISOLATION ON

-- SNAPSHOT_ISOLATION als Default setzen

ALTER DATABASE  "Name der Datenbank" SET READ_COMMITTED_SNAPSHOT ON

-- aktuellen Status abfragen

SELECT name, snapshot_isolation_state, is_read_committed_snapshot_on FROM sys.databases

typeeinfaches Beispiel für einen Cursor (Schleife) mit T-SQL

11.04.2010 posted by fwenner

Diese Beispielt zeigt die grundlegende Verwendung von datengebundenen Schleifen in T-SQL 
DECLARE @NewID INT

DECLARE @OldID INT   -- Cursor erstellen mit den SQL der Quelldaten



DECLARE curKopieren CURSOR READ_ONLY FAST_FORWARD FOR

SELECT ID FROM Tab1   WHERE ID IN (1,2,3) 



-- Bedingung für die zu kopierenden Sätze (hier über die ID)      

-- Cursor öffnen und den ersten Satz lesen

OPEN curKopieren

FETCH NEXT FROM curKopieren INTO @OldID



-- Solange Daten abgeholt werden konnten ...   

WHILE @@FETCH_STATUS = 0

BEGIN        



  -- ersten Datensatz kopieren

  INSERT INTO Tab1 (Spalte1, Spalte2)

  SELECT Spalte1, Spalte2 FROM Tab1 WHERE ID = @OldID



  -- neue ID nach dem Insert abholen und merken

  SET @NewID = @@IDENTITY



  -- zweiten Datensatz kopieren

  INSERT INTO Tab2 (IDTab1, Spalte1, Spalte2)

  SELECT @NewID, Spalte1, Spalte2 FROM Tab2     WHERE IDTab1 = @OldID



  -- nächsten Satz lesen

  FETCH NEXT FROM curKopieren INTO @OldID

END

  

-- wichtig, unbedingt am Ende den Cursor schließen UND freigeben

CLOSE curKopieren

DEALLOCATE curKopieren

typeDateTime2SmallDateTime

11.04.2010 posted by fwenner

Mit dieser Funktion können alle Werte auf einen Feld von Typ DateTime in einen Wert von Typ SmallDateTime umgewandelt werden 
CREATE FUNCTION DateTime2SmallDateTime(@DateTime datetime) RETURNS smalldatetime AS   

BEGIN   

  DECLARE @Result smalldatetime

  SET @Result = null

  DECLARE @Year smallint

  SET @Year = 0

  IF (NOT @DateTime IS NULL)

  BEGIN

    SET @Year = YEAR(@DateTime)

    IF  (@Year < 1900) 

    BEGIN

      SELECT  @Result = dateadd(ss, datepart(ss,@DateTime), 

                                  dateadd(n, datepart(n,@DateTime), 

                                  dateadd(hh,datepart(hh,@DateTime),'01.01.1900'))) 

    END     

    ELSE

    IF (@Year > 2076)

    BEGIN

      SELECT  @Result = dateadd(ss, datepart(ss,@DateTime),                         

                                  dateadd(n, datepart(n,@DateTime),

                                  dateadd(hh,datepart(hh,@DateTime),'06.06.2076')))

    END

    ELSE

    BEGIN

      SELECT  @Result = @DateTime

    END

  END

  RETURN @Result

END

typeFind the right art of your primary key

11.04.2010 posted by fwenner

Vor einiger Zeit wurde ich in meinem Job mit der Frage konfrontiert was denn der ideale Weg für einen Primärschlüssel für eine Tabelle auf Microsoft® SQL Server ist. Es gibt hier die unterschiedlichsten Ansätze.
Ich habe hier mal die häufigsten Ansätze vergleichen.
  • manuelle vergebene Nummern
  • Identity Werte
  • GUID als Default
  • GUID manuell vergeben
mehr dazu gibt es hier

typeString2Numeric

11.04.2010 posted by fwenner

Mit dieser Funktionen können Strings in Zahlen umgewandelt werden 
CREATE FUNCTION String2Numeric(@Input varchar(max)) RETURNS INTEGER

BEGIN

  DECLARE @pos INTEGER

  SET @Pos = PATINDEX('%[^0-9]%',@Input)

  WHILE @Pos > 0

  BEGIN

    SET @Input = STUFF(@Input,@pos,1,'')

    SET @Pos = PATINDEX('%[^0-9]%',@Input)

  END

  RETURN CAST(@Input as INTEGER)

END

typexp_cmdshell aktivieren

11.04.2010 posted by fwenner

Vorgehensweise von Aktivieren der SQL Server Prozedure xp_cmdshell 
-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1

GO



-- To update the currently configured value for advanced options.

RECONFIGURE

GO



-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO



-- To update the currently configured value for this feature.

RECONFIGURE

GO