SQL Server

Host: localhost
Port: 1433
Database: master
Username: sa
Password: your_password

Host: localhost\SQLEXPRESS

{
  host: 'localhost',
  port: 1433,
  ssl: true,  // Enable encryption
  trustServerCertificate: false  // Verify certificate
}

Host: your-server.database.windows.net
Port: 1433
Database: your-database
Username: your-username@your-server
Password: your-password
SSL: Enabled

CREATE CLUSTERED INDEX idx_id ON users(id);
CREATE NONCLUSTERED INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);

CREATE TABLE users (
  id INT IDENTITY(1,1) PRIMARY KEY,  -- Start at 1, increment by 1
  name NVARCHAR(100) NOT NULL
);

-- Insert without specifying id
INSERT INTO users (name) VALUES ('Alice');

-- Reseed identity
DBCC CHECKIDENT ('users', RESEED, 1000);

CREATE PROCEDURE GetUser
  @UserId INT
AS
BEGIN
  SELECT * FROM users WHERE id = @UserId;
END;

-- Execute
EXEC GetUser @UserId = 1;

-- Scalar function
CREATE FUNCTION dbo.GetFullName(
  @FirstName NVARCHAR(50),
  @LastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGIN
  RETURN @FirstName + ' ' + @LastName;
END;

-- Table-valued function
CREATE FUNCTION dbo.GetUsersByStatus(@Status NVARCHAR(20))
RETURNS TABLE
AS
RETURN (
  SELECT * FROM users WHERE status = @Status
);

CREATE TRIGGER trg_UpdateTimestamp
ON users
AFTER UPDATE
AS
BEGIN
  UPDATE users
  SET updated_at = GETDATE()
  FROM users u
  INNER JOIN inserted i ON u.id = i.id;
END;

-- Create login and user
CREATE LOGIN myuser WITH PASSWORD = 'SecurePassword123!';
CREATE USER myuser FOR LOGIN myuser;

-- Grant permissions
GRANT SELECT, INSERT ON users TO myuser;

-- Add table comment
EXEC sp_addextendedproperty 
  @name = N'MS_Description',
  @value = N'Stores user information',
  @level0type = N'SCHEMA', @level0name = 'dbo',
  @level1type = N'TABLE', @level1name = 'users';

-- Add column comment
EXEC sp_addextendedproperty 
  @name = N'MS_Description',
  @value = N'User email address',
  @level0type = N'SCHEMA', @level0name = 'dbo',
  @level1type = N'TABLE', @level1name = 'users',
  @level2type = N'COLUMN', @level2name = 'email';

WITH UserStats AS (
  SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(total) as total_spent
  FROM orders
  GROUP BY user_id
)
SELECT 
  u.name,
  s.order_count,
  s.total_spent
FROM users u
JOIN UserStats s ON u.id = s.user_id;

SELECT 
  name,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

-- Parse JSON
SELECT 
  JSON_VALUE(data, '$.name') as name,
  JSON_VALUE(data, '$.age') as age
FROM users;

-- Convert to JSON
SELECT 
  id, 
  name, 
  email
FROM users
FOR JSON PATH;

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name NVARCHAR(100),
  salary DECIMAL(10,2),
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON);

-- Query historical data
SELECT * FROM employees FOR SYSTEM_TIME AS OF '2024-01-01';

-- Create full-text catalog and index
CREATE FULLTEXT CATALOG ft_catalog;
CREATE FULLTEXT INDEX ON articles(title, content) KEY INDEX PK_articles;

-- Search
SELECT * FROM articles WHERE CONTAINS(content, 'database AND performance');

BEGIN TRANSACTION;
BEGIN TRY
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION;
  THROW;
END CATCH;

SET SHOWPLAN_XML ON;
GO
SELECT * FROM users WHERE email = '[email protected]';
GO
SET SHOWPLAN_XML OFF;

services:
  sqlserver:
    image: mcr.microsoft.com/mssql/server:2022-latest
    ports:
      - "1433:1433"
    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: YourStrong@Password
      MSSQL_PID: Developer
    volumes:
      - ./docker/sqlserver/init.sql:/docker-entrypoint-initdb.d/init.sql