To change the Database Table Collations dynamically, you can generate update scripts with the following snippet

declare  @toCollation sysname 
      
SET    @toCollation = 'Latin1_General_100_CI_AS' --  << insert your destination collation name

SELECT 'ALTER TABLE ' + INFORMATION_SCHEMA.COLUMNS.TABLE_NAME +
       '   ALTER COLUMN ' + COLUMN_NAME + ' ' + DATA_TYPE +
       CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 then '(max)'
            WHEN DATA_TYPE in ('text','ntext') then ''
            WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL 
             THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
            ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ') END
       +' COLLATE ' + @toCollation+ ' ' + CASE IS_NULLABLE
                                           WHEN 'YES' THEN 'NULL'
                                           WHEN 'No' THEN 'NOT NULL' 

END
FROM INFORMATION_SCHEMA.COLUMNS INNER JOIN INFORMATION_SCHEMA.TABLES
ON  INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA  = INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA
WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar','text','ntext')
AND TABLE_TYPE = 'BASE TABLE'
and COLLATION_NAME <> @toCollation

The following statement maps an existing SQL Server Login to an existing Database User

EXEC sp_change_users_login 'update_one', 'loginName', 'dbUserName'

Tutorial to create a matrix in TSQL from a table containing detail values:
http://www.tsqltutorials.com/pivot.php

Datum-Spielchen in TSQL

http://ryanfarley.com/blog/archive/2005/02/15/1689.aspx

Der folgende Code selektiert die Anzahl der verschiedene Werte von OtherValue für jedes Resultat der Gruppierung.

SELECT
     ID,
     COUNT(DISTINCT OtherValue) 
FROM
     MyTable
GROUP BY
     ID
HAVING 
     COUNT(DISTINCT OtherValue) > 1

This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes. 

http://vyaskn.tripod.com/code.htm#inserts

One of the most handy features introduced in SQL 2005 were the ranking functions; ROW_NUMBER(), RANK(), and DENSE_RANK(). For anyone who hasn’t been introduced to these syntactic gems, here’s a quick rundown:

http://thehobt.blogspot.com/2009/02/rownumber-rank-and-denserank.html

ROW_NUMBER()

Aufsteigende Zeilen-Nummer gemäss ORDER BY, optional beschränkt auf Subsets gegeben durch PARTITION BY.

RANK()

Aufsteigende Zeilen-Wertigkeit/Rang (gleiche Zeileninhalte ergeben gleicher Rang) gemäss ORDER BY, optional beschränkt auf Subsets gegeben durch PARTITION BY. Es können Lücken entstehen, wenn mehrere Zeilen den gleichen Rang haben (Bsp. 1, 2, 2, 4, 5 <-- 3 wird ausgelassen, da 2 zwei Mal vorkommt).

DENSE_RANK()

Identisch zu RANK() mit der Ausnahme, dass keine Lücken in der Nummerierung entstehen (Bsp. 1, 2, 2, 3, 4 <-- inkl. 3)

USE myDB
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 90)"
GO
EXEC sp_updatestats
GO

SQL SERVER 2005 uses ALTER INDEX syntax to reindex database. SQL SERVER 2005 supports DBREINDEX but it will be deprecated in future versions.

Diese Anweisung prüft myString auf einen leeren String (0 Zeichen oder alles Leerzeichen).

SELECT NULLIF(myString,'')

Führende 0 aus String entfernen (aus 0013a wird 13a)

STUFF(RTRIM(HAUSNR), 1, PATINDEX('%[^0]%',RTRIM(HAUSNR)) - 1, '')

Laufnummer/Sequenz berechnen

Berechnet für alle Einträge eine fortlaufende Nummer, basierend auf einer gegebenen Sortierung und beginnend mit der höchsten bereits vorhandenen Laufnummer.

SELECT
       ID,
       ROW_NUMBER() OVER(ORDER BY ID) + (SELECT MAX(LaufNr) FROM MyTable) LaufNr
FROM
       [MyTable]

Daten in XML-Spalte suchen

CREATE TABLE #mal
(
   id INT,
   n xml
)


INSERT INTO #mal (id, n) VALUES (1, '<dd><a>Micky</a><b>Maus</b></dd>') 
INSERT INTO #mal (id, n) VALUES (1, '<dd><a>Donald</a><b>Duck</b></dd>') 


SELECT *, n.value('(/dd/a)[1]', 'varchar(max)') FROM #mal
WHERE n.value('(/dd/a)[1]', 'varchar(max)') = 'Micky'


DROP TABLE #mal

für kompliziertere XMLs:

<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <Parameter>
     <Name>RETURN_VALUE</Name>
   </Parameter>
   <Parameter>
     <Name>FirstName</Name>
     <Value xsi:type="xsd:string">Tom</Value>
   </Parameter>
   <Parameter>
     <Name>LastName</Name>
     <Value xsi:type="xsd:string">Binggeli</Value>
   </Parameter>
   <Parameter>
     <Name>NationalityID</Name>
     <Value xsi:type="xsd:int">2</Value>
   </Parameter>
</Parameters>


select * from t_databaseaudit
where
parameters.exist('/Parameters/Parameter[Name="LastName"][Value="Binggeli"]')=1

Simpler XML Export

SELECT * FROM MyTable FOR XML RAW ('MyTable'), ELEMENTS XSINIL, ROOT('Export')

As described in the topic, Constructing XML Using FOR XML, RAW and AUTO mode do not provide much control over the shape of the XML generated from a query result. However, EXPLICIT mode provides the most flexibility in generating the XML you want from a query result.

The EXPLICIT mode query must be written in a specific way so that the additional information about the required XML, such as expected nesting in the XML, is explicitly specified as part of the query. Depending on the XML you request, writing EXPLICIT mode queries can be cumbersome. You may find that Using PATH Mode with nesting is a simpler alternative to writing EXPLICIT mode queries.

Because you describe the XML you want as part of the query in EXPLICIT mode, you must ensure that the generated XML is well formed and valid.

http://msdn2.microsoft.com/en-us/library/ms189068.aspx

SELECT 
                 1                                as Tag,
                NULL                                as Parent,
                BPJ.BPJ_ID                        as [ParentTable!1!BPJ_ID],
                BPJ.BPJ_Bezeichnung as [ParentTable!1!BPJ_Bezeichnung],
                NULL                                as [SubTable!2!BPP_ID!ELEMENT],
                NULL                                as [SubTable!2!BPP_Datum!ELEMENT],
                NULL                                as [SubTable!2!BPP_Ausgefuehrt!ELEMENTXSINIL],
                NULL                                as [OtherTable!3!FNK_Funktion!ELEMENTXSINIL]
FROM   
                ParentTable BPJ 
UNION ALL
SELECT 
                 2                                as Tag,
                 1                                as Parent,
                BPP.BPJ_ID,                        
                NULL,                                
                BPP_ID,
                BPP_Datum,                        
                BPP_Ausgefuehrt,     
                NULL                                
FROM   
                ParentTable BPJ 
                LEFT JOIN SubTable BPP ON BPJ.BPJ_ID = BPP.BPJ_ID
WHERE   BPP_Datum IS NOT NULL
UNION ALL
SELECT 
                 3                                as Tag,
                 2                                as Parent,
                BPP.BPJ_ID,                        
                NULL,                                
                BPP_ID,
                NULL,                        
                NULL,     
                CTI_Bez                                
FROM   
                SubTable BPP
                LEFT JOIN CodeInhaltCodeTypView CTV ON CTV.CTI_ID = BPP.CTI_FunktionsCode
WHERE   BPP_Datum IS NOT NULL
ORDER BY [ParentTable!1!BPJ_ID], [SubTable!2!BPP_ID!ELEMENT], Tag, Parent, [SubTable!2!BPP_Datum!ELEMENT]          
FOR XML EXPLICIT, ROOT ('ParentTable')

As described in the topic, Constructing XML Using FOR XML, RAW and AUTO mode do not provide much control over the shape of the XML generated from a query result. However, EXPLICIT mode provides the most flexibility in generating the XML you want from a query result.

The EXPLICIT mode query must be written in a specific way so that the additional information about the required XML, such as expected nesting in the XML, is explicitly specified as part of the query. Depending on the XML you request, writing EXPLICIT mode queries can be cumbersome. You may find that Using PATH Mode with nesting is a simpler alternative to writing EXPLICIT mode queries.

Because you describe the XML you want as part of the query in EXPLICIT mode, you must ensure that the generated XML is well formed and valid.

http://msdn2.microsoft.com/en-us/library/ms189068.aspx

SQL Benutzerverwaltung einrichten

c:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe

Erster nicht-NULL Wert aus einer Liste.

CASE 
         WHEN COALESCE(PRS_Name, PRS_Zusatzname, PRS_Vorname) IS NULL THEN '' 
         ELSE ', ' + ISNULL(PRS_Name + ' ','') + ISNULL(PRS_Zusatzname + ' ','') + ISNULL(PRS_Vorname,'') 
END

This article tackles two related questions:

• How can I use the result set from one stored procedure in another, also expressed as How can I use the result set from a stored procedure in a SELECT statement?
• How can I pass a table as a parameter from one stored procedure to another?

In this article I will discuss a number of methods, and also point out their advantages and drawbacks. Some of the methods apply only when you want to reuse a result set, whereas others apply in both situations. In the case you want to reuse a result set, most methods require you to rewrite the stored procedure in one way or another, but there are some methods that do not.

http://www.sommarskog.se/share_data.html

Prüfung auf das Vorhandensein einer temporären Tabelle 

IF (OBJECT_ID('tempdb..#TabellenName') IS NULL)
BEGIN
        ...
END