Ms Sql Server code snippets

Adhoc Query#

EXEC sp_who2 -- 59 | optional exact connection Id

ALTER DATABASE [Destination_Database]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [Destination_Database]
SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO

USE [master]
RESTORE DATABASE [Destination_Database]
    FROM DISK = N'Path to backup physical file'
        WITH FILE = 1,  MOVE N'Destination_Database_mdf_name' TO N'Path to destination database mdf',
        MOVE N'Destination_Database_ldf_name' TO N'Path to destination database ldf',
        NOUNLOAD,
        REPLACE,
        STATS = 5
GO

ALTER DATABASE [Destination_Database]
SET ONLINE WITH ROLLBACK IMMEDIATE;

ALTER DATABASE [Destination_Database]
SET MULTI_USER WITH ROLLBACK IMMEDIATE;
GO

WITH CTE AS (
    SELECT a.Id AS Id,a.ParentId, 1 AS lvl
        FROM ParentChildTable a
        WHERE ParentId = 'xxx' -- Set level one determine
    UNION ALL
    SELECT a.Id, a.ParentId, lvl + 1
        FROM ParentChildTable a
            INNER JOIN CTE c ON a.ParentId = c.Id
        WHERE lvl < 3 -- Restrict level deep
)
SELECT Id, ParentId, lvl FROM CTE

-- Better create our own calendar table and reuse
DECLARE @dStartDate DATE = '20220101', @dEndDate DATE = '20220301';
WITH CTE AS (
    SELECT @dStartDate AS [DayInYear]
    UNION ALL
    SELECT DATEADD(DAY, 1, [DayInYear])
        FROM CTE
        WHERE DATEADD(DAY, 1, [DayInYear]) < @dEndDate
)
SELECT DayInYear
    FROM CTE
    OPTION (MAXRECURSION 0);

SELECT Id, AmountMoney -- Median by Id
        INTO #tmp
        FROM dbo.Source
        ORDER BY AmountMoney;

    SELECT TOP 0 Id, AmountMoney
        INTO #tmp_Result
        FROM dbo.Source;

    DECLARE @nId INT = 0, @c BIGINT = 1;

    DECLARE iterating_cursor CURSOR FOR
        SELECT DISTINCT Id
            FROM #tmp

    OPEN iterating_cursor
        FETCH NEXT FROM iterating_cursor
        INTO @nId;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT @c = COUNT(*) FROM dbo.#tmp WHERE Id = @nId;

        INSERT INTO #tmp_Result
            SELECT Id, AVG(1.0 * AmountMoney) AS Median
                FROM (
                    SELECT Id, AmountMoney
                        FROM dbo.#tmp
                        WHERE Id = @nId
                        ORDER BY AmountMoney
                        OFFSET (@c - 1) / 2 ROWS
                        FETCH NEXT 1 + (1 - @c % 2) ROWS ONLY
                ) AS x
                GROUP BY Id;

        SELECT @c = 1;
        FETCH NEXT FROM iterating_cursor
        INTO @nId;
    END
    CLOSE iterating_cursor;
    DEALLOCATE iterating_cursor;

    SELECT * FROM #tmp_Result

    DROP TABLE #tmp
    DROP TABLE #tmp_Result

BULK INSERT Sales
FROM 'D:\temp\test.csv'
WITH (FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR='\n',
    BATCHSIZE=10000,
    MAXERRORS=2);

Store Procedure#

• Simple structure generate insert

Permission Grant#

USE [Destination_Database]
CREATE USER [userName] FOR LOGIN [loginName]
EXEC sp_addrolemember 'db_datawriter', 'loginName'
EXEC sp_addrolemember 'db_datareader', 'loginName'
EXEC sp_addrolemember 'db_ddladmin', 'loginName'

Tool Box#

• Almost equivalent random distribution
• Concatenate string from a single column