SQL Server (MSSQL)

# Connect using Windows Authentication
sqlcmd -S localhost -E

# Connect with SQL Authentication
sqlcmd -S 127.0.0.1 -U sa -P 'StrongPassword123' -d master

SELECT @@VERSION;
GO                  -- Execute the current batch
:r filename.sql     -- Include and execute external file
QUIT                -- Exit sqlcmd
!! dir              -- Execute shell command

-- Single-line comment
/* Multi-line
   comment block */
SELECT 1; -- Trailing comment

SELECT 
    CAST(255 AS TINYINT) AS t_int,    -- 1 byte (0-255)
    CAST(2147483647 AS INT) AS i_int, -- 4 bytes
    CAST(9.22E18 AS BIGINT) AS b_int, -- 8 bytes
    123.45 AS d_decimal,              -- DECIMAL(5,2)
    $12.34 AS m_money;                -- 8-byte currency

SELECT 
    CAST('fixed' AS CHAR(5)),         -- Non-Unicode fixed
    CAST('var' AS VARCHAR(10)),       -- Non-Unicode variable
    N'Unicode' AS n_text,             -- Unicode (UCS-2/UTF-16)
    0xDEADBEEF AS b_bytes;            -- VARBINARY

SELECT 
    GETDATE() AS dt,                  -- Legacy DATETIME
    SYSDATETIMEOFFSET() AS dto,       -- DATETIMEOFFSET (with TZ)
    CAST(GETDATE() AS DATE) AS d,     -- Date only
    DATEADD(day, 1, GETDATE()) AS tmw;-- Date math

SELECT 
    CAST(1 AS BIT) AS is_true,        -- 0, 1, or NULL
    CASE WHEN 1=1 THEN 'Y' ELSE 'N' END AS flag; -- No ENUM type

SELECT 
    FirstName + ' ' + LastName AS FullName,
    Total / 1024.0 AS SizeMB
FROM Users;

SELECT * FROM Nodes
WHERE IPAddress LIKE '192.168.%'
  AND Status IN (1, 2)
  AND LastSeen > DATEADD(hour, -1, GETDATE());

SELECT Name, Priority FROM Queue
ORDER BY Priority DESC, CreatedAt ASC;

-- Take top 5
SELECT TOP 5 * FROM Logs ORDER BY Id DESC;

-- Paginate: Skip 10, take 5 (SQL 2012+)
SELECT * FROM Logs 
ORDER BY Id 
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

SELECT u.UserName, r.RoleName
FROM Users u
INNER JOIN UserRoles ur ON u.Id = ur.UserId
LEFT JOIN Roles r ON ur.RoleId = r.Id;

-- Cross Apply (Join to table-valued function or subquery)
SELECT u.Name, p.Item
FROM Users u
CROSS APPLY (SELECT TOP 1 Item FROM Orders WHERE UserId = u.Id) p;

SELECT Email FROM Leads
UNION -- Removes duplicates
SELECT Email FROM Customers
EXCEPT
SELECT Email FROM OptOut;

SELECT t.Name, s.Total
FROM Teams t
JOIN (SELECT TeamId, SUM(Points) AS Total FROM Stats GROUP BY TeamId) s
  ON t.Id = s.TeamId;

SELECT Category, COUNT(*) AS Qty
FROM Inventory
GROUP BY Category;

SELECT UserId, COUNT(*)
FROM Logins
GROUP BY UserId
HAVING COUNT(*) > 100;

SELECT 
    COUNT(DISTINCT ClientIP) AS UniqueIPs,
    STRING_AGG(Tags, ';') AS AllTags -- SQL 2017+
FROM Connections;

SELECT Name FROM Products p
WHERE EXISTS (SELECT 1 FROM Sales s WHERE s.ProductId = p.Id);

WITH MonthlySales AS (
    SELECT Month, SUM(Amount) AS Total FROM Sales GROUP BY Month
)
SELECT * FROM MonthlySales WHERE Total > 5000;

WITH OrgChart AS (
    SELECT Id, Name, ManagerId FROM Emp WHERE Id = 1
    UNION ALL
    SELECT e.Id, e.Name, e.ManagerId 
    FROM Emp e JOIN OrgChart o ON e.ManagerId = o.Id
)
SELECT * FROM OrgChart;

SELECT 
    Name, 
    Category,
    Salary,
    AVG(Salary) OVER(PARTITION BY Category) AS CatAvg
FROM Employees;

SELECT 
    Name, 
    ROW_NUMBER() OVER(ORDER BY Score DESC) AS Pos
FROM Players;

SELECT 
    Val,
    LAG(Val) OVER(ORDER BY Time) AS PrevVal
FROM Telemetry;

INSERT INTO Settings (Key, Value)
OUTPUT Inserted.Id -- Get generated ID back
VALUES ('Timeout', '30');

UPDATE p SET p.Price = p.Price * 1.1
FROM Products p
JOIN Categories c ON p.CatId = c.Id
WHERE c.Name = 'Premium';

DELETE FROM Logs WHERE Timestamp < DATEADD(day, -30, GETDATE());

MERGE TargetTable AS T
USING SourceTable AS S ON T.Id = S.Id
WHEN MATCHED THEN 
    UPDATE SET T.Val = S.Val
WHEN NOT MATCHED THEN 
    INSERT (Id, Val) VALUES (S.Id, S.Val);

CREATE TABLE Devices (
    Id INT IDENTITY(1,1) PRIMARY KEY, -- Auto-increment
    UUID UNIQUEIDENTIFIER DEFAULT NEWID(),
    Payload NVARCHAR(MAX) -- Large text
);

ALTER TABLE Devices ADD Status TINYINT DEFAULT 0;

CREATE TABLE Sensors (
    Id INT PRIMARY KEY,
    Value FLOAT CHECK (Value >= 0),
    DeviceId INT FOREIGN KEY REFERENCES Devices(Id) ON DELETE CASCADE
);

CREATE INDEX IX_Device_Status ON Devices(Status);
-- Show execution plan
SET SHOWPLAN_TEXT ON;
GO
SELECT * FROM Devices WHERE Status = 1;
GO
SET SHOWPLAN_TEXT OFF;

CREATE VIEW ActiveDevices AS 
SELECT * FROM Devices WHERE Status = 1;

BEGIN TRANSACTION;
UPDATE Accounts SET Bal -= 100 WHERE Id = 1;
UPDATE Accounts SET Bal += 100 WHERE Id = 2;
IF @@ERROR <> 0 ROLLBACK ELSE COMMIT;

CREATE PROCEDURE GetDeviceStatus @Id INT
AS
BEGIN
    SELECT Status FROM Devices WHERE Id = @Id;
END;
GO
EXEC GetDeviceStatus @Id = 5;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT COUNT(*) FROM LargeTable;
GO
SET STATISTICS IO OFF;

CREATE SCHEMA archive;
CREATE USER AppUser FOR LOGIN AppLogin;
GRANT SELECT ON SCHEMA::dbo TO AppUser;