1.Useful Queries

 

1         Dupliates, nTH Records, CTE 1.1         nthRecord with CTE      WITH C AS(              SELECT SC.CustomerName                        ,CT.CustomerTransactionID                        ,TransactionAmount                        ,ROW_NUMBER() OVER(PARTITION BY SC.CustomerID ORDER BY TransactionDate) as RN              FROM [Sales].[CustomerTransactions] CT              INNER JOIN [Sales].[Customers] SC ON CT.CustomerID = SC.CustomerID      )      SELECT RN, CustomerName                ,CustomerTransactionID                ,TransactionAmount      FROM C      WHERE RN = 55      ORDER BY CustomerName; -         we can delete where RN > 1  - here instead of select we can delete from C where rn >1 1.2         nth Record with  dense_rank() use WideWorldImporters select * from (      select p.PreferredName,      dense_rank() over (order by PreferredName ) rdense_rank      from [Application].[People] p ) tbl where rdense_rank = 3 2         Merge with CTE – Insert and update      WITH SourceTableCTE AS      (              SELECT * FROM SourceTable      )      MERGE       TargetTable AS target      USING SourceTableCTE AS source       ON (target.PKID = source.PKID)      WHEN MATCHED THEN                  UPDATE SET target.ColumnA = source.ColumnA      WHEN NOT MATCHED THEN              INSERT (ColumnA) VALUES (Source.ColumnA); 3         Useful Char Fucntions --------Name start with M SELECT * FROM [Application].[People] p WHERE CHARINDEX('M',PreferredName) = 1 SELECT * FROM [Application].[People] p WHERE LEFT(PreferredName, 1) = 'M' SELECT * FROM [Application].[People] p WHERE SUBSTRING(PreferredName, 1, 1) = 'M' SELECT PreferredName, CHARINDEX('M',PreferredName) FROM [Application].[People] where CHARINDEX('M',PreferredName) > 0 4         Best Practices 4.1         Dev Pace – Table details in Excel 4.1.1        sys tables SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],        T.[name] AS [table_name], AC.[name] AS [column_name],        TY.[name] AS system_data_type, AC.[max_length],       AC.[precision], AC.[scale], AC.[is_nullable], AC.[is_ansi_padded]  FROM sys.[tables] AS T    INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]   INNER JOIN sys.[types] TY ON AC.[system_type_id] = TY.[system_type_id] AND AC.[user_type_id] = TY.[user_type_id]   --WHERE T.[is_ms_shipped] = 0  ORDER BY T.[name], AC.[column_id] 4.1.2        INFORMATION_SCHEMA.COLUMNS SELECT TABLE_SCHEMA ,    TABLE_NAME ,    COLUMN_NAME ,    ORDINAL_POSITION ,    COLUMN_DEFAULT ,    DATA_TYPE ,    CHARACTER_MAXIMUM_LENGTH ,    NUMERIC_PRECISION ,    NUMERIC_PRECISION_RADIX ,    NUMERIC_SCALE ,    DATETIME_PRECISION FROM   INFORMATION_SCHEMA.COLUMNS;