Enum Extention

Enum Extention :

    public static class EnumExtention
    {
        public static Dictionary<int, string> GetEnumData(this Type EnumType)
        {
            Dictionary<int, string> objEnumList = new Dictionary<int, string>();

            if (EnumType != null && EnumType.IsEnum)
            {
                foreach (int intCount in Enum.GetValues(EnumType))
                {
                    objEnumList.Add(intCount, Enum.GetName(EnumType, intCount).ToString().Replace("_", " "));
                }
            }
            return objEnumList;
        }
    }

Get Enum Data :

        private enum enumUserType
        {
            Admin,
            Sales_Department,
            Purchase_Department,
            Guest
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            ddlUserType.DataSource = typeof(enumUserType).GetEnumData();
            
            ddlUserType.DataTextField = "Value";
            ddlUserType.DataValueField = "Key";

            ddlUserType.DataBind();
        }

Output :

<select name="ddlUserType" id="ddlUserType">
    <option value="1">Admin</option>
    <option value="2">Sales Department</option>
    <option value="3">Purchase Department</option>
    <option value="4">Guest</option> 
</select>

OUTPUT Clause

SQL QUERY :

    CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblOutPut ( OutputAction VARCHAR(20), ID INT, GroupID INT, NewValueName VARCHAR(10), OldValueName VARCHAR(10) )
    
    INSERT INTO #tblSource VALUES (1,1,'a11')
    INSERT INTO #tblSource VALUES (2,1,'a21')
    INSERT INTO #tblSource VALUES (3,1,'a31')
    
    --- Inserted
    BEGIN
        INSERT INTO #tblTarget
                OUTPUT 
                    'Inserted' AS OutputAction
                    , INSERTED.ID 
                    , INSERTED.GroupID
                    , INSERTED.Name
                    , NULL
                INTO #tblOutPut
            SELECT * FROM #tblSource
            UNION ALL
            SELECT ID + 10, GroupID, Name FROM #tblSource;
    END
    
    --- Updated
    BEGIN
        UPDATE #tblTarget
        SET Name = CAST(ID AS VARCHAR) + ' : ' + Name
        OUTPUT 
            'Updated' AS OutputAction
            , INSERTED.ID 
            , INSERTED.GroupID
            , INSERTED.Name
            , deleted.Name
        INTO #tblOutPut;
    END
    
    --- Deleted
    BEGIN
        DELETE FROM #tblTarget
            OUTPUT 
                'Deleted' AS OutputAction
                , deleted.ID 
                , deleted.GroupID
                , null
                , deleted.Name
            INTO #tblOutPut
        WHERE ID >= 10;
    END
    
    SELECT * FROM #tblOutPut

    DROP TABLE #tblSource
    DROP TABLE #tblTarget
    DROP TABLE #tblOutPut

Output :

OutputAction         ID          GroupID     NewValueName OldValueName
-------------------- ----------- ----------- ------------ ------------
Inserted             1           1           a11          NULL
Inserted             2           1           a21          NULL
Inserted             3           1           a31          NULL
Inserted             11          1           a11          NULL
Inserted             12          1           a21          NULL
Inserted             13          1           a31          NULL
Updated              1           1           1 : a11      a11
Updated              2           1           2 : a21      a21
Updated              3           1           3 : a31      a31
Updated              11          1           11 : a11     a11
Updated              12          1           12 : a21     a21
Updated              13          1           13 : a31     a31
Deleted              11          1           NULL         11 : a11
Deleted              12          1           NULL         12 : a21
Deleted              13          1           NULL         13 : a31

Merge Statement with insert into output table

    DECLARE @tblMergeOutput TABLE
    (
        ID                BIGINT IDENTITY(1,1)
        , MergeAction    NVARCHAR(100)
        , InsertedId    INT
        , DeletedId        INT
        , GroupID        INT
    )

    CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
    CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

    INSERT INTO #tblSource VALUES (1,1,'a11')
    INSERT INTO #tblSource VALUES (2,1,'a21')
    INSERT INTO #tblSource VALUES (3,1,'a31')
    INSERT INTO #tblSource VALUES (4,1,'a41')

    INSERT INTO #tblTarget VALUES (1,1,'a1')
    INSERT INTO #tblTarget VALUES (3,1,'a3')
    INSERT INTO #tblTarget VALUES (5,1,'a5')
    INSERT INTO #tblTarget VALUES (6,2,'b2')
    INSERT INTO #tblTarget VALUES (7,2,'b1')

    
    ---- Method 2 : Using Output insert into table
    BEGIN
        INSERT INTO @tblMergeOutput
            SELECT MergeAction, InsertedId, DeletedID, GroupID 
            FROM
            (
                MERGE #tblTarget
                USING (
                        SELECT *
                        FROM #tblSource
                    ) AS tblSource
                        ON tblSource.GroupID = #tblTarget.GroupID
                            AND tblSource.ID = #tblTarget.ID
                WHEN MATCHED THEN 
                    UPDATE
                    SET Name = tblSource.Name
                WHEN NOT MATCHED THEN
                    INSERT (
                                ID
                                , GroupID
                                , Name
                            )
                    VALUES ( 
                                tblSource.ID
                                , tblSource.GroupID
                                , tblSource.Name
                            )
                WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
                    THEN DELETE
                OUTPUT 
                    $action                AS MergeAction
                    , inserted.Id        AS InsertedId
                    , deleted.Id        AS DeletedID
                    , tblSource.GroupID    AS GroupID
            ) AS MergeOutput (MergeAction, InsertedId, DeletedID, GroupID);
    END

    SELECT * FROM #tblTarget order by GroupID, ID
    SELECT * FROM @tblMergeOutput

    DROP TABLE #tblSource
    DROP TABLE #tblTarget

MERGE Statement and compatibility level

In SQL 2008 Merge Statement when execute and give the error like this

Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

Solution
SQL Command

ALTER DATABASE <DBName>
SET COMPATIBILITY_LEVEL = 100 

Checking current user Menu Rights

SQL QUERY :

    SET NOCOUNT ON

    DECLARE @CurrentUserTypeID INT = 1  ------ <---- Pass Current User Type

    -----------------------------------
    DECLARE @tblUserType TABLE
    (
        UserTypeID            INT
        , TypeName        VARCHAR(50)
        , MenuRights    BIGINT
    )

    INSERT INTO @tblUserType 
    VALUES (1, 'Admin', 1)
        , (2, 'GenralManager', 2)
        , (3, 'SalesManager', 4)
        , (4, 'SalesMan', 8)
        , (5, 'Client', 16)

    DECLARE @tblMenu TABLE
    (
        MenuID            INT
        , MenuName        VARCHAR(50)
        , MenuRights    BIGINT
    )

    INSERT INTO @tblMenu 
    VALUES (1, 'Admin Setting', 1)
        , (2, 'Genral Setting', 3)
        , (3, 'Sales Report', 7)
        , (4, 'Sales Order Folloup', 15)
        , (5, 'Client FeedBack', 31)

    DECLARE @UserRights BIGINT = 0

    SELECT @UserRights = MenuRights
    FROM @tblUserType
    WHERE UserTypeID = @CurrentUserTypeID 

    SELECT * 
    FROM @tblMenu
    WHERE MenuRights & @UserRights = @UserRights

Admin Menu List :
MenuID      MenuName                                           MenuRights
----------- -------------------------------------------------- --------------------
1           Admin Setting                                      1
2           Genral Setting                                     3
3           Sales Report                                       7
4           Sales Order Folloup                                15
5           Client FeedBack                                    31

GenralManager Menu List :
MenuID      MenuName                                           MenuRights
----------- -------------------------------------------------- --------------------
2           Genral Setting                                     3
3           Sales Report                                       7
4           Sales Order Folloup                                15
5           Client FeedBack                                    31

SalesManager Menu List :
MenuID      MenuName                                           MenuRights
----------- -------------------------------------------------- --------------------
3           Sales Report                                       7
4           Sales Order Folloup                                15
5           Client FeedBack                                    31

Cross Apply in XML

Query :

 DECLARE @XML XML = 
 '<Customers>;
  <Customer ID="1">;
   <Address1>;Address - 11</Address1>;
   <Address2>;Address - 12</Address2>;
  </Customer>;
  <Customer ID="2">;
   <Address1>;Address - 21</Address1>;
   <Address2>;Address - 22</Address2>;
  </Customer>;
 </Customers>;'

 SELECT 
  Customer.value('@ID', 'INT') AS CustomerID
  , CustAdd1.value('.', 'VARCHAR(25)') Address1
  , CustAdd2.value('.', 'VARCHAR(25)') Address2
 FROM @XML.nodes('/Customers/Customer')a(Customer)
  CROSS APPLY Customer.nodes('Address1')aa(CustAdd1)
  CROSS APPLY Customer.nodes('Address2')bb(CustAdd2)

Output :
     ----------- ------------------------- -------------------------
     CustomerID  Address1                  Address2
     ----------- ------------------------- -------------------------
     1           Address - 11              Address - 12
     2           Address - 21              Address - 22
     ----------- ------------------------- -------------------------

Merge Statement

SQL Statement:

DECLARE @tblMergeOutput TABLE
 (
  ID                BIGINT IDENTITY(1,1)
  , MergeAction    NVARCHAR(100)
  , InsertedId    INT
  , DeletedId        INT
  , GroupID        INT
 )

 CREATE TABLE #tblSource ( ID INT, GroupID INT, Name VARCHAR(10) )
 CREATE TABLE #tblTarget ( ID INT, GroupID INT, Name VARCHAR(10) )

 INSERT INTO #tblSource VALUES (1,1,'a11')
 INSERT INTO #tblSource VALUES (2,1,'a21')
 INSERT INTO #tblSource VALUES (3,1,'a31')
 INSERT INTO #tblSource VALUES (4,1,'a41')

 INSERT INTO #tblTarget VALUES (1,1,'a1')
 INSERT INTO #tblTarget VALUES (3,1,'a3')
 INSERT INTO #tblTarget VALUES (5,1,'a5')
 INSERT INTO #tblTarget VALUES (6,2,'b2')
 INSERT INTO #tblTarget VALUES (7,2,'b1')

 MERGE #tblTarget
 USING (
   SELECT *
   FROM #tblSource
  ) AS tblSource
   ON tblSource.GroupID = #tblTarget.GroupID
    AND tblSource.ID = #tblTarget.ID
 WHEN MATCHED THEN 
  UPDATE
  SET Name = tblSource.Name
 WHEN NOT MATCHED THEN
  INSERT (
     ID
     , GroupID
     , Name
    )
  VALUES ( 
     tblSource.ID
     , tblSource.GroupID
     , tblSource.Name
    )
 WHEN NOT MATCHED BY SOURCE AND #tblTarget.GroupID = 1
  THEN DELETE
 OUTPUT 
  $action
  , inserted.Id
  , deleted.Id
  , tblSource.GroupID
 INTO @tblMergeOutput;

 SELECT * FROM #tblSource
 SELECT * FROM #tblTarget order by GroupID, ID
 SELECT * FROM @tblMergeOutput

 DROP TABLE #tblSource
 DROP TABLE #tblTarget

Target Table Output:

 ------------------
 ID  GroupID   Name
 ------------------
 1   1         a11
 2   1         a21
 3   1         a31
 4   1         a41
 6   2         b2
 7   2         b1
 ------------------

Merge Table Output:

 ----------------------------------------------------
 ID  Merge Action    InsertedID   DeletedID   GroupID
 ----------------------------------------------------
 1   INSERT          2            NULL        1
 2   INSERT          4            NULL        1
 3   UPDATE          1            1           1
 4   UPDATE          3            3           1
 5   DELETE          NULL         5           NULL
 ----------------------------------------------------

User Tree

DECLARE @tblUInfo TABLE (UserID INT, ParentUserID INT, UserName VARCHAR(100) )

INSERT INTO @tblUInfo VALUES (1,0, 'A')
INSERT INTO @tblUInfo VALUES (2,1, 'A1')
INSERT INTO @tblUInfo VALUES (3,1, 'A2')
INSERT INTO @tblUInfo VALUES (4,0, 'B')
INSERT INTO @tblUInfo VALUES (5,4, 'B1')
INSERT INTO @tblUInfo VALUES (6,4, 'B2')

;WITH cteNodes AS
(
 --initialization
 SELECT UserID, ParentUserID, ISNULL(UserName, '' ) AS UserTree, 0 AS UserLevel
 FROM @tblUInfo
 --WHERE ParentUserID IS NULL

 UNION ALL

 ----recursive execution
 SELECT P.UserID, N.ParentUserID, ISNULL( N.UserName, '' ), P.UserLevel + 1
 FROM cteNodes AS P
  INNER JOIN @tblUInfo AS N ON N.UserID = P.ParentUserID
)
--- SELECT * FROM cteNodes ORDER BY UserID, UserLevel OPTION (MaxRecursion 32767)

, cteEmployeePath AS
(
 SELECT tblUInfo.UserID
  , tblUInfo.UserName
  ,(
   SELECT CAST(UserTree AS VARCHAR ) + ':'
   FROM cteNodes AS UserParth
   WHERE UserParth.UserID = tblUInfo.UserID
   ORDER BY UserLevel DESC
   FOR XML PATH('')
   ) AS UserTree
  ,(
   SELECT MAX(UserLevel)
   FROM cteNodes AS UserParth
   WHERE UserParth.UserID = tblUInfo.UserID
  ) AS UserLevel
 FROM @tblUInfo AS tblUInfo
)
SELECT UserID, ( REPLICATE('--', UserLevel) + UserName ) AS UserName 
FROM cteEmployeePath 
ORDER BY UserTree 
OPTION (MaxRecursion 32767)

Pivot / UnPivot Example

SQL Query :

   DECLARE @Tbl TABLE(Col1 INT, A VARCHAR(1), B VARCHAR(1), C VARCHAR(1) )

   INSERT INTO @Tbl
    SELECT 1, 'a', 'b', 'c'
    UNION 
    SELECT 2, 'd', 'e', 'f'
    UNION 
    SELECT 3, 'g', 'h', 'i'

   SELECT 
    *
   FROM
   (
    SELECT *
    FROM
    (
     SELECT 
      Col1 AS Cols
      , A AS [A(N1)] 
      , B AS [B(N2)] 
      , C AS [C(N3)] 
     FROM @Tbl
    ) A
    UNPIVOT
    ( [Value] For [Col1] IN ( 
           [A(N1)]
           , [B(N2)]
           , [C(N3)]
          ) ) AS upvt
   ) B
   PIVOT
   (
    MIN ( [Value])
    FOR [Cols]
    IN ([1], [2], [3])
   ) AS pvt;

Input :

    ----------------
    Col1       A B C
    ----------------
    1          a b c 
    2          d e f
    3          g h i
    ----------------

OutPut :

    
    -------------------
    Col1          1 2 3
    -------------------
    A(N1)         a d g
    B(N2)         b e h
    C(N3)         c f i
    -------------------

Search Mutipal Keyword in Multipal Field

BEGIN  --Key Words
    DECLARE @String as VARCHAR(500)
    SET @String = 'Kansas,64108'
END

BEGIN  --Sample Data
    DECLARE @UserMaster TABLE (UserName VARCHAR(50), Address VARCHAR(50), City VARCHAR(50), Zip VARCHAR(50))
    INSERT INTO @UserMaster VALUES ('aa', '1810 Cherry Street', 'Kansas City', '64108')
    INSERT INTO @UserMaster VALUES ('bb', '1810 Cherry Street', 'New york City', '64119')
    INSERT INTO @UserMaster VALUES ('cc', '1810 Cherry Street', 'Kansas City', '64108')
    INSERT INTO @UserMaster VALUES ('dd', '1810 Cherry Street', 'New york City', '64119')
    INSERT INTO @UserMaster VALUES ('ee', '1810 Cherry Street', 'Kansas City', '64108')
    INSERT INTO @UserMaster VALUES ('ff', '1810 Cherry Street', 'New york City', '64119')
END

BEGIN  --Return Result
    DECLARE @XML XML
    SET @XML ='<Root><SearchKey>' + REPLACE( @String, ',', '</SearchKey><SearchKey>') + '</SearchKey></Root>' 

    SELECT UserMaster.*, SearchInfo.SearchKey
    FROM @UserMaster AS UserMaster
        INNER JOIN ( 
                        SELECT x.Name.value('.', 'VARCHAR(MAX)') AS SearchKey
                        FROM @XML.nodes('/Root/SearchKey')x(Name)
                    ) SearchInfo ON UserMaster.City Like '%' + SearchInfo.SearchKey + '%'
        OR UserMaster.Zip Like '%' + SearchInfo.SearchKey + '%'
        OR UserMaster.Address Like '%' + SearchInfo.SearchKey + '%'
END

Find Which Transaction is currently Running

SELECT s_tst.[session_id],
   s_es.[login_name] AS [Login Name],
   S_tdt.[database_transaction_begin_time] AS [Begin Time],
   s_tdt.[database_transaction_log_record_count] AS [Log Records],
   s_tdt.[database_transaction_log_bytes_used] AS [Log Bytes],
   s_tdt.[database_transaction_log_bytes_reserved] AS [Log Reserved],
   s_est.[text] AS [Last T-SQL Text],
   s_eqp.[query_plan] AS [Last Query Plan]
FROM sys.dm_tran_database_transactions s_tdt
   JOIN sys.dm_tran_session_transactions s_tst
      ON s_tst.[transaction_id] = s_tdt.[transaction_id]
   JOIN sys.[dm_exec_sessions] s_es
      ON s_es.[session_id] = s_tst.[session_id]
   JOIN sys.dm_exec_connections s_ec
      ON s_ec.[session_id] = s_tst.[session_id]
   CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle]) AS s_est
   OUTER APPLY sys.dm_exec_query_plan (s_ec.[most_recent_sql_handle]) AS s_eqp
ORDER BY [Begin Time] ASC;

Truncate Log

--BACKUP LOG  WITH TRUNCATE_ONLY
--Go


--============= For sql 2008 =====================
USE
GO
SELECT file_id, name FROM sys.database_files
GO
DBCC SHRINKFILE (2, TRUNCATEONLY); 

GET Database diffrence

----Declaration
BEGIN 
 DECLARE @Status_TableNotFound  INT = 1
 DECLARE @Status_FieldNotFound  INT = 2
 DECLARE @Status_FieldTypeChnage  INT = 3
 DECLARE @Status_FieldLengthChange INT = 4
ENd

--Get Table / Filed List
BEGIN
 SELECT 
  --- db1
  db1Objects.id AS db1Id
  , db1Objects.name AS db1TableName
  , db1Column.name AS db1FieldName 
  , db1Column.xType as db1FieldType 
  , UPPER(Type_Name(db1Column.xtype)) AS db1FieldTypeName
  , db1Column.length as db1FieldLength
  , db1Column.ColStat & 1 AS db1IsIdentity

  --- db2
  , ISNULL( db2Objects.id, 0) AS db2Id
  , ISNULL( db2Objects.name, '') AS db2TableName
  , ISNULL( db2Column.name, '') AS db2FieldName 
  , ISNULL( db2Column.xType, 0 ) as db2FieldType 
  , UPPER(ISNULL( Type_Name(db2Column.xtype), '' )) AS db2FieldTypeName
  , ISNULL( db2Column.length, 0 ) as db2FieldLength
  , ISNULL( db2Column.ColStat & 1, 0 ) AS db2IsIdentity
  , 0 AS Status
 INTO #FieldCheck
 FROM <DBName1>.dbo.SysObjects AS db1Objects 
  INNER JOIN <DBName1>.dbo.SysColumns AS db1Column ON db1Column.id = db1Objects.id
   AND db1Objects.xtype = 'U'
   AND db1Objects.category = 0
  
  LEFT OUTER JOIN <DBName2>.dbo.SysObjects AS db2Objects ON db2Objects.name = db1Objects.name
   AND db2Objects.xtype = 'U'
   AND db2Objects.category = 0
  LEFT OUTER JOIN <DBName2>.dbo.SysColumns AS db2Column ON db2Column.id = db2Objects.id
   AND db2Column.name = db1Column.name
END

--- Check for Status
BEGIN
 --- TableNotFound
 UPDATE #FieldCheck
 SET Status = @Status_TableNotFound
 WHERE db1TableName != db2TableName
 
 --- FieldNotFound
 UPDATE #FieldCheck
 SET Status = @Status_FieldNotFound
 WHERE db1FieldName != db2FieldName
  AND Status = 0
 
 --- FieldTypeChnage
 UPDATE #FieldCheck
 SET Status = @Status_FieldTypeChnage
 WHERE db1FieldName = db2FieldName
  AND db1FieldTypeName != db2FieldTypeName
  AND Status = 0
 
 --- FieldLengthChange
 UPDATE #FieldCheck
 SET Status = @Status_FieldLengthChange
 WHERE db1FieldLength != db2FieldLength
  AND Status = 0
END


--- TableNotFound
SELECT DISTINCT db1TableName AS TableNotFound
FROM #FieldCheck
WHERE Status = @Status_TableNotFound

--- FieldNotFound
SELECT DISTINCT 
 db1TableName AS TableName
 , db1FieldName FieldNotFound
 , 'ALTER TABLE ' + db1TableName + 
  ' ADD ' + db1FieldName + 
  ' ' + db1FieldTypeName + 
  ' ' + ( 
    CASE 
     WHEN db1FieldTypeName IN ( 'VARCHAR', 'NVARCHAR' )
      THEN '(' + CAST( db1FieldLength AS VARCHAR(10) ) + ')'
     ELSE ''
    END
        ) +
  '; UPDATE ' + db1TableName +
  ' SET  ' + db1FieldName + ' = ' +
  (
   CASE
    WHEN db1FieldTypeName LIKE '%CHAR%' 
      OR db1FieldTypeName LIKE '%TEXT%' 
      OR db1FieldTypeName LIKE '%XML%' 
     THEN ''''''
    WHEN db1FieldTypeName LIKE 'INT' 
      OR db1FieldTypeName LIKE 'SMALLINT'
      OR db1FieldTypeName LIKE 'BIGINT'
      OR db1FieldTypeName LIKE '%BINARY%'
      OR db1FieldTypeName LIKE 'float'
      OR db1FieldTypeName = 'BIT'
     THEN '0'
    ELSE 'object'
   END
  )
  ' ; '
FROM #FieldCheck
WHERE Status = @Status_FieldNotFound

--- FieldTypeChnage
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName, db2FieldTypeName
FROM #FieldCheck
WHERE Status = @Status_FieldTypeChnage


--- FieldLengthChange
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName AS FieldType, db1FieldLength, db2FieldLength
FROM #FieldCheck
WHERE Status = @Status_FieldLengthChange

DROP TABLE #FieldCheck

Find and Replace text in Datatable

SET NOCOUNT ON
DECLARE @FindName     VARCHAR(MAX) = 'AAA' --- <- Find Value
DECLARE @ReplaceName  VARCHAR(MAX) = 'BBB' --- <- Replace Value

DECLARE @Table TABLE
(
 ID INT IDENTITY(1,1)
 , TableName  VARCHAR(100)
 , ColumnName VARCHAR(100)
)
INSERT INTO @Table
 SELECT
  tableSchema.TABLE_SCHEMA + '.' + SysObjects.name
  , SysColumns.Name
 FROM sysobjects 
  INNER JOIN SysColumns ON SysColumns.id = sysobjects .id
   AND SysColumns.xtype IN ( 167, 175, 239, 231, 35, 99 )
  INNER JOIN information_schema.tables AS tableSchema ON tableSchema.TABLE_NAME = SysObjects.name
 WHERE sysobjects.xtype='U'
 ORDER BY sysobjects.Name, SysColumns.Name
 
DECLARE @NoOfRecord INT = 0 
DECLARE @CurrentRecord INT = 1
DECLARE @TableName VARCHAR(100) = ''
DECLARE @ColumnName VARCHAR(100) = ''
DECLARE @SQLCommand VARCHAR(MAX) = ''

SELECT @NoOfRecord = MAX(ID)
FROM @Table

WHILE (@CurrentRecord <= @NoOfRecord )
 BEGIN
  SELECT
   @TableName = TableName
   , @ColumnName = ColumnName
  FROM @Table
  WHERE ID = @CurrentRecord
  
  SET @SQLCommand = 'IF EXISTS(SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%' + @FindName + '%'')' +
   ' BEGIN ' +
   ' PRINT ''UPDATE ' + @TableName +
   ' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ''''' + @FindName + ''''', ''''' + @ReplaceName + ''''') ''' +
   ' END '
   
  -- PRINT @SQLCommand
  EXECUTE(@SQLCommand)
  SET @CurrentRecord = @CurrentRecord + 1
 END

Second highest Salary

DECLARE @Employees TABLE(
    EmployeeID INT IDENTITY,
    EmployeeName VARCHAR(15),
    Department VARCHAR(15),
    Salary NUMERIC(16,2)
)
 
INSERT INTO @Employees VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees VALUES('jj','Finance', 25000)
INSERT INTO @Employees VALUES('D Adams','Finance', 15000)
 
INSERT INTO @Employees VALUES('M Williams','IT', 80000)
INSERT INTO @Employees VALUES('D Jones','IT', 40000)
INSERT INTO @Employees VALUES('J Miller','IT', 50000)
INSERT INTO @Employees VALUES('L Lewis','IT', 50000)
 
INSERT INTO @Employees VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees VALUES('T Clerk','Back-Office', 10000)

SELECT * 
FROM
(
 SELECT *, DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS EmpRank
 FROM @Employees
) AS Employees
WHERE EmpRank = 2

Get field list from string

DECLARE @FileFormat VARCHAR(50)
 SET @FileFormat = '1#34#6#8#01#34#6#8#01#34#6#8#01#34#6#8#0'
 
 ;WITH MyTable AS 
 (
  SELECT @FileFormat AS FileFormat
   , CHARINDEX('#', @FileFormat) + 1 AS FirstIndex
   , (
    CHARINDEX('#', SUBSTRING(@FileFormat, CHARINDEX('#', @FileFormat ) + 1, LEN( @FileFormat ) ) ) - 1
    ) AS SecondIndex
  
  UNION ALL
  
  SELECT SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ) AS FileFormat
   , CHARINDEX('#', SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ) ) + 1 AS FirstIndex
   , (
    CHARINDEX('#', SUBSTRING(SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ), CHARINDEX('#', SUBSTRING( FileFormat, FirstIndex + SecondIndex + 1, LEN( FileFormat ) ) ) + 1, LEN( FileFormat ) ) ) - 1
    ) AS SecondIndex
  FROM MyTable
  WHERE LEN( FileFormat ) > 0
   AND SecondIndex > 0
 )
 
 SELECT 
  (
   CASE 
    WHEN FirstIndex - 2 > 0 THEN SUBSTRING(FileFormat, 1, FirstIndex - 2)
    ELSE SUBSTRING(FileFormat, 1, LEN( FirstIndex ) )
   END
  ) AS PreviousField
  ,(
   CASE 
    WHEN SecondIndex > 0 THEN SUBSTRING(FileFormat, FirstIndex, SecondIndex)
    ELSE ''
   END
  ) AS FieldName
 FROM MyTable
 OPTION (MAXRECURSION 0)

Get Datatabase LOG Size

SELECT
     (size * 8)/1024.0 AS size_in_mb,
     CASE
        WHEN CAST( max_size AS BIGINT )= -1 THEN 9999999   -- Unlimited growth, so handle this how you want
        ELSE (CAST( max_size AS BIGINT ) * 8)/1024.0
     END AS max_size_in_mb
FROM
     sys.database_files
WHERE
     data_space_id = 0   -- Log file

Insert query of datatable

DECLARE @PassTableName as varchar(1000)

SET @PassTableName = 'tbl_ReturnsEFileFieldMapping' --- 'Type Table Name'

BEGIN

 DECLARE @SQL_Insert AS Varchar(8000)
 DECLARE @SQL_Values AS Varchar(8000)

 DECLARE @FieldName AS Varchar(200)
 DECLARE @FieldType as int
 DECLARE @FieldTypeName as varchar(100)
 DECLARE @FieldLength as int
 DECLARE @FieldIdentity as int

 SET @SQL_Insert = 'SELECT ''INSERT INTO ' + @PassTableName + ' ( '
 SET @SQL_Values = ' VALUES ( '

 DECLARE Fields_Cursor CURSOR FOR 
  SELECT Name AS FieldName, 
   xType as FieldType, 
   Type_Name(xType) AS FieldTypeName, 
   Length as FieldLength, 
   ColStat & 1 AS IsIdentity
  FROM SysColumns 
  WHERE ID IN ( SELECT ID FROM SysObjects WHERE Name = REPLACE( REPLACE( REPLACE( @PassTableName, 'dbo.', ''), 'Report.', ''), 'EQISAdmin.', '') )

 OPEN Fields_Cursor
 FETCH NEXT FROM Fields_Cursor INTO @FieldName, @FieldType, @FieldTypeName, @FieldLength, @FieldIdentity

 WHILE @@FETCH_STATUS = 0
 BEGIN

--  IF @FieldIdentity = 0 -- Identity is No
--  BEGIN

   SET @SQL_Insert = @SQL_Insert + @FieldName + ', '

   IF @FieldType = 48 OR @FieldType = 56 OR @FieldType = 127 OR @FieldType = 59 OR @FieldType = 52  -- Small Int OR Integer OR Big Integer OR Real OR SmallInt
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(20), ISNULL(' + @FieldName + ', '''' ) ) + '', '
    END
   ELSE IF @FieldType = 106 OR @FieldType = 108 -- Decimal OR Numeric
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(30), ISNULL(' + @FieldName + ', '''') ) + '', '
    END
   ELSE IF @FieldType = 60 OR @FieldType = 122 -- Money OR Small Money
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(30), Convert( Decimal(30,4), ISNULL(' + @FieldName + ', 0) )) + '', '
    END
   ELSE IF @FieldType = 62 -- Float
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(30), Convert( Decimal(30,10), ISNULL(' + @FieldName + ',0) )) + '', '
    END
   ELSE IF @FieldType = 104  -- Bit
    BEGIN
     SET @SQL_Values = @SQL_Values + ''' + Convert( varchar(1), ISNULL(' + @FieldName + ','''') ) + '', '
    END
   ELSE IF @FieldType = 61 OR @FieldType = 58  -- Datetime OR SmallDatetime
    BEGIN
     SET @SQL_Values = @SQL_Values + ''''''' + Convert( varchar(20), ISNULL(' + @FieldName + ','''') ) + '''''', '
    END
   ELSE IF @FieldType = 167 OR @FieldType = 175 OR @FieldType = 239 OR @FieldType = 231 -- Varchar OR Char OR nChar OR nVarchar
    BEGIN
     SET @SQL_Values = @SQL_Values + ''''''' + Replace( ISNULL(' + @FieldName + ', ''''), '''''''', '''''''''''' ) + '''''', '
    END
   ELSE IF @FieldType = 35 OR @FieldType = 99 -- Text OR nText
    BEGIN
     SET @SQL_Values = @SQL_Values + ''''''' + Replace( Convert( varchar(8000), ISNULL(' + @FieldName + ','''')), '''''''', ''''''''''''  ) + '''''', '
    END
   ELSE IF @FieldType = 241 -- XML
    BEGIN
     SET @SQL_Values = @SQL_Values + ''''''' + Replace( Convert( varchar(8000), ISNULL(' + @FieldName + ','''')), '''''''', ''''''''''''  ) + '''''', '
    END

--  END


    FETCH NEXT FROM Fields_Cursor INTO @FieldName, @FieldType, @FieldTypeName, @FieldLength, @FieldIdentity

 END

 CLOSE Fields_Cursor
 DEALLOCATE Fields_Cursor

 SET @SQL_Insert = LTRIM( RTRIM( @SQL_Insert ) )
 IF LEN( @SQL_Insert ) > 1
  BEGIN
   SET @SQL_Insert = SUBSTRING( @SQL_Insert, 1, LEN( @SQL_Insert ) -1 )
  END

 SET @SQL_Values = LTRIM( RTRIM( @SQL_Values ) )
 IF LEN( @SQL_Values ) > 1
  BEGIN
   SET @SQL_Values = SUBSTRING(@SQL_Values, 1, LEN( @SQL_Values ) -1 )
  END
 
 --PRINT ( @SQL_Insert + ' ) ' + @SQL_Values + ' ) '' FROM ' + @PassTableName )
 EXEC  ( @SQL_Insert + ' ) ' + @SQL_Values + ' ) '' FROM ' + @PassTableName )

END
GO

Hierarchical Tree Structures in Database Tables

DECLARE @emp TABLE ( 
EmployeeID INT, 
FirstName VARCHAR(15), 
LastName VARCHAR(15), 
ReportsTo INT 
) 
 

DECLARE @ord TABLE ( 
OrderID INT, 
EmployeeID INT 
) 
 

INSERT INTO @emp(EmployeeID, FirstName, LastName, ReportsTo) 
SELECT 2,'Andrew','Fuller',NULL
UNION ALL SELECT 1,'Nancy','Davolio',2 
UNION ALL SELECT 3,'Janet','Leverling',2 
UNION ALL SELECT 4,'Margaret','Peacock',2 
UNION ALL SELECT 5,'Steven','Buchanan',2 
UNION ALL SELECT 8,'Laura','Callahan',2 
UNION ALL SELECT 6,'Michael','Suyama',5 
UNION ALL SELECT 7,'Robert','King',5 
UNION ALL SELECT 9,'Anne','Dodsworth',5  
--UNION ALL SELECT 10,'A','A',1  
--UNION ALL SELECT 11,'B','B',1  
--UNION ALL SELECT 12,'C','C',1   
--UNION ALL SELECT 13,'D','D',10  

--SELECT * FROM @emp 
 

INSERT INTO @ord (OrderID, EmployeeID)  
SELECT 10258,1 UNION ALL 
SELECT 10270,1 UNION ALL 
SELECT 10275,1 UNION ALL 
SELECT 10265,2 UNION ALL 
SELECT 10277,2 UNION ALL 
SELECT 10251,3 UNION ALL 
SELECT 10253,3 UNION ALL 
SELECT 10256,3 UNION ALL 
SELECT 10250,4 UNION ALL 
SELECT 10252,4 UNION ALL 
SELECT 10248,5 UNION ALL 
SELECT 10254,5 UNION ALL 
SELECT 10249,6 UNION ALL 
SELECT 10289,7 UNION ALL 
SELECT 10303,7 UNION ALL 
SELECT 10308,7 UNION ALL 
SELECT 10262,8 UNION ALL 
SELECT 10268,8 UNION ALL 
SELECT 10276,8 UNION ALL 
SELECT 10278,8 UNION ALL 
SELECT 10255,9 UNION ALL 
SELECT 10263,9  
 

;WITH cteNodes AS
(
 --initialization
 SELECT EmployeeID, ISNULL(ReportsTo, 0 ) AS ReportsTo, 0 AS EmpLevel
 FROM @emp

 UNION ALL

 ----recursive execution
 SELECT P.EmployeeID, ISNULL( N.ReportsTo, 0 ), P.EmpLevel + 1
 FROM cteNodes AS P
  INNER JOIN @emp AS N ON N.EmployeeID = P.ReportsTo
)
, cteEmployeePath AS
(
 SELECT DISTINCT Emp.EmployeeID
  , ISNULL( Emp.ReportsTo, 0 ) AS ReportsTo
  ,(
   SELECT '$' + CAST(ReportsTo AS VARCHAR(20) ) + '$'
   FROM cteNodes AS EmpParth
   WHERE EmpParth.EmployeeID = Emp.EmployeeID
   ORDER BY EmpLevel DESC
   FOR XML PATH('')
   ) 
   + '$' + CAST( Emp.EmployeeID AS VARCHAR(10) ) + '$' AS EmployeePath
   ,(
   SELECT '$0$'
   FROM cteNodes AS EmpParth
   WHERE EmpParth.EmployeeID = Emp.EmployeeID
   ORDER BY EmpLevel DESC
   FOR XML PATH('')
   ) AS EmpLevel
 FROM @emp AS Emp
)

SELECT
  (
  REPLICATE(' ', 3*( LEN( REPLACE( cteEmployeePath.EmpLevel, '$', '') ) - 1 ) ) 
  + Emp.LastName
  + ' ' 
  + Emp.FirstName 
  ) AS EmployeeName
  , ( LEN( REPLACE( cteEmployeePath.EmpLevel, '$', '') ) - 1 ) AS Level
  , SUM( CASE WHEN Ord.EmployeeID = Emp.EmployeeID THEN 1 ELSE 0 END ) AS by_self
  , SUM( CASE WHEN Ord.EmployeeID != Emp.EmployeeID THEN 1 ELSE 0 END ) AS by_sub
  , COUNT( Ord.OrderId ) AS total 
FROM cteEmployeePath 
 INNER JOIN @emp AS Emp ON Emp.EmployeeID = cteEmployeePath.EmployeeID
 INNER JOIN cteEmployeePath AS UnderEmployee ON UnderEmployee.EmployeePath Like cteEmployeePath.EmployeePath + '%'
 INNER JOIN @ord AS Ord ON Ord.EmployeeID = UnderEmployee.EmployeeID
GROUP BY Emp.EmployeeId, cteEmployeePath.EmpLevel, Emp.FirstName, Emp.LastName, cteEmployeePath.EmployeePath
ORDER BY cteEmployeePath.EmployeePath

Backup & Restore Database

---- Backup Database :
BEGIN
    backup database  to disk = 'c:\.bak'
END

---- Restore Database :
BEGIN
  RESTORE DATABASE Salary 
  FROM  DISK = N'C:\DBBackUp 2009-12-23.bak' 
  WITH  FILE = 1,  
  MOVE N'Salary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB.mdf',  
 MOVE N'Salary_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL  \Data\DB_Log.ldf',  
   NOUNLOAD,  STATS = 10
END