8 posts tagged with "sql-server"

-- Tạo Full Backup
BACKUP DATABASE TenDatabase
TO DISK = 'C:\Backup\TenDatabase_Full.bak'
WITH INIT, NAME = 'TenDatabase-Full Database Backup';

-- Tạo Differential Backup
BACKUP DATABASE TenDatabase
TO DISK = 'C:\Backup\TenDatabase_Diff.bak'
WITH DIFFERENTIAL, INIT, 
NAME = 'TenDatabase-Differential Database Backup';

-- Tạo Transaction Log Backup
BACKUP LOG TenDatabase
TO DISK = 'C:\Backup\TenDatabase_Log.trn'
WITH INIT, NAME = 'TenDatabase-Transaction Log Backup';

-- Restore Full Backup
RESTORE DATABASE TenDatabase
FROM DISK = 'C:\Backup\TenDatabase_Full.bak'
WITH REPLACE, RECOVERY;

-- Restore Full Backup
RESTORE DATABASE TenDatabase
FROM DISK = 'C:\Backup\TenDatabase_Full.bak'
WITH NORECOVERY;

-- Restore Differential Backup
RESTORE DATABASE TenDatabase
FROM DISK = 'C:\Backup\TenDatabase_Diff.bak'
WITH RECOVERY;

-- Restore Full Backup
RESTORE DATABASE TenDatabase
FROM DISK = 'C:\Backup\TenDatabase_Full.bak'
WITH NORECOVERY;

-- Restore Differential Backup
RESTORE DATABASE TenDatabase
FROM DISK = 'C:\Backup\TenDatabase_Diff.bak'
WITH NORECOVERY;

-- Restore Transaction Log
RESTORE LOG TenDatabase
FROM DISK = 'C:\Backup\TenDatabase_Log.trn'
WITH RECOVERY;

-- Tạo Full Backup hàng ngày
BACKUP DATABASE TenDatabase
TO DISK = 'C:\Backup\TenDatabase_Full_' + 
    CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
WITH INIT, NAME = 'TenDatabase-Full Database Backup';

-- Tạo Differential Backup hàng ngày
BACKUP DATABASE TenDatabase
TO DISK = 'C:\Backup\TenDatabase_Diff_' + 
    CONVERT(VARCHAR(8), GETDATE(), 112) + '.bak'
WITH DIFFERENTIAL, INIT, 
NAME = 'TenDatabase-Differential Database Backup';

-- Tạo Transaction Log Backup mỗi giờ
BACKUP LOG TenDatabase
TO DISK = 'C:\Backup\TenDatabase_Log_' + 
    CONVERT(VARCHAR(8), GETDATE(), 112) + '_' +
    CONVERT(VARCHAR(2), DATEPART(HOUR, GETDATE())) + '.trn'
WITH INIT, NAME = 'TenDatabase-Transaction Log Backup';

SELECT 
    database_name,
    backup_start_date,
    backup_finish_date,
    backup_size,
    backup_type
FROM msdb.dbo.backupset
ORDER BY backup_start_date DESC;

RESTORE FILELISTONLY
FROM DISK = 'C:\Backup\TenDatabase_Full.bak';

RESTORE HEADERONLY
FROM DISK = 'C:\Backup\TenDatabase_Full.bak';

-- Tạo Clustered Index
CREATE CLUSTERED INDEX IX_Orders_OrderID
ON Orders(OrderID);

-- Tạo Non-Clustered Index
CREATE NONCLUSTERED INDEX IX_Customers_City
ON Customers(City);

-- Tạo Composite Index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON Orders(CustomerID, OrderDate);

-- Tạo Filtered Index
CREATE NONCLUSTERED INDEX IX_Products_Active
ON Products(ProductID, ProductName)
WHERE Discontinued = 0;

-- Bật Execution Plan
SET SHOWPLAN_TEXT ON;
GO

-- Truy vấn cần phân tích
SELECT 
    c.CustomerName,
    COUNT(o.OrderID) as OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerName;

-- Sử dụng INNER JOIN thay vì LEFT JOIN khi có thể
SELECT 
    c.CustomerName,
    o.OrderID
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;

-- Sử dụng EXISTS thay vì IN
SELECT ProductName
FROM Products p
WHERE EXISTS (
    SELECT 1 
    FROM OrderDetails od 
    WHERE od.ProductID = p.ProductID
);

-- Sử dụng Index Seek
SELECT ProductName
FROM Products
WHERE ProductID = 1;

-- Tránh sử dụng hàm trong WHERE
-- Không tốt
SELECT OrderID
FROM Orders
WHERE YEAR(OrderDate) = 2023;

-- Tốt hơn
SELECT OrderID
FROM Orders
WHERE OrderDate >= '2023-01-01' 
AND OrderDate < '2024-01-01';

SELECT 
    OBJECT_NAME(ips.OBJECT_ID) as TableName,
    i.name as IndexName,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
    DB_ID(), NULL, NULL, NULL, NULL) ips
JOIN sys.indexes i ON ips.object_id = i.object_id
    AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30;

-- Rebuild một index
ALTER INDEX IX_Orders_OrderID ON Orders REBUILD;

-- Rebuild tất cả index của một bảng
ALTER INDEX ALL ON Orders REBUILD;

-- Update statistics cho một bảng
UPDATE STATISTICS Orders;

-- Update statistics cho toàn bộ database
EXEC sp_updatestats;

-- Kiểm tra index usage
SELECT 
    OBJECT_NAME(i.object_id) as TableName,
    i.name as IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups
FROM sys.dm_db_index_usage_stats ius
JOIN sys.indexes i ON ius.object_id = i.object_id
    AND ius.index_id = i.index_id;

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (
    SELECT AVG(UnitPrice)
    FROM Products
);

SELECT CategoryName, AvgPrice
FROM (
    SELECT CategoryID, AVG(UnitPrice) as AvgPrice
    FROM Products
    GROUP BY CategoryID
) AS CategoryAvg
JOIN Categories ON CategoryAvg.CategoryID = Categories.CategoryID;

SELECT 
    ProductName,
    UnitPrice,
    (SELECT AVG(UnitPrice) FROM Products) as AvgPrice
FROM Products;

WITH SalesCTE AS (
    SELECT 
        ProductID,
        SUM(Quantity) as TotalQuantity
    FROM OrderDetails
    GROUP BY ProductID
)
SELECT 
    p.ProductName,
    s.TotalQuantity
FROM Products p
JOIN SalesCTE s ON p.ProductID = s.ProductID;

WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT 
        EmployeeID,
        ManagerID,
        EmployeeName,
        1 as Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        e.EmployeeName,
        eh.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

SELECT 
    ProductName,
    UnitPrice,
    ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) as PriceRank
FROM Products;

SELECT 
    ProductName,
    UnitPrice,
    RANK() OVER (ORDER BY UnitPrice DESC) as PriceRank,
    DENSE_RANK() OVER (ORDER BY UnitPrice DESC) as DensePriceRank
FROM Products;

SELECT 
    OrderID,
    OrderDate,
    LAG(OrderDate) OVER (ORDER BY OrderDate) as PreviousOrder,
    LEAD(OrderDate) OVER (ORDER BY OrderDate) as NextOrder
FROM Orders;

CREATE PROCEDURE sp_GetCustomerOrders
    @CustomerID int
AS
BEGIN
    SELECT 
        o.OrderID,
        o.OrderDate,
        p.ProductName,
        od.Quantity,
        od.UnitPrice
    FROM Orders o
    JOIN OrderDetails od ON o.OrderID = od.OrderID
    JOIN Products p ON od.ProductID = p.ProductID
    WHERE o.CustomerID = @CustomerID;
END;

CREATE PROCEDURE sp_InsertOrder
    @CustomerID int,
    @OrderDate datetime,
    @ShipAddress nvarchar(100)
AS
BEGIN
    INSERT INTO Orders (CustomerID, OrderDate, ShipAddress)
    VALUES (@CustomerID, @OrderDate, @ShipAddress);
    
    SELECT SCOPE_IDENTITY() as NewOrderID;
END;

CREATE PROCEDURE sp_CalculateOrderTotal
    @OrderID int,
    @Total decimal(18,2) OUTPUT
AS
BEGIN
    SELECT @Total = SUM(Quantity * UnitPrice)
    FROM OrderDetails
    WHERE OrderID = @OrderID;
END;

CREATE FUNCTION fn_CalculateDiscount
(
    @Price decimal(18,2),
    @DiscountPercent decimal(5,2)
)
RETURNS decimal(18,2)
AS
BEGIN
    DECLARE @DiscountAmount decimal(18,2);
    SET @DiscountAmount = @Price * (@DiscountPercent / 100);
    RETURN @Price - @DiscountAmount;
END;

CREATE FUNCTION fn_GetProductInventory
(
    @MinQuantity int
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        p.ProductID,
        p.ProductName,
        p.UnitsInStock,
        c.CategoryName
    FROM Products p
    JOIN Categories c ON p.CategoryID = c.CategoryID
    WHERE p.UnitsInStock >= @MinQuantity
);

CREATE FUNCTION fn_GetSalesByPeriod
(
    @StartDate date,
    @EndDate date
)
RETURNS @SalesTable TABLE
(
    ProductID int,
    ProductName nvarchar(100),
    TotalQuantity int,
    TotalAmount decimal(18,2)
)
AS
BEGIN
    INSERT INTO @SalesTable
    SELECT 
        p.ProductID,
        p.ProductName,
        SUM(od.Quantity) as TotalQuantity,
        SUM(od.Quantity * od.UnitPrice) as TotalAmount
    FROM Products p
    JOIN OrderDetails od ON p.ProductID = od.ProductID
    JOIN Orders o ON od.OrderID = o.OrderID
    WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
    GROUP BY p.ProductID, p.ProductName;
    
    RETURN;
END;

-- Gọi với tham số đơn
EXEC sp_GetCustomerOrders @CustomerID = 1;

-- Gọi với OUTPUT
DECLARE @Total decimal(18,2);
EXEC sp_CalculateOrderTotal @OrderID = 1, @Total = @Total OUTPUT;
SELECT @Total as OrderTotal;

-- Scalar Function
SELECT 
    ProductName,
    UnitPrice,
    dbo.fn_CalculateDiscount(UnitPrice, 10) as DiscountedPrice
FROM Products;

-- Table-Valued Function
SELECT * FROM fn_GetProductInventory(10);

-- Multi-Statement Table-Valued Function
SELECT * FROM fn_GetSalesByPeriod('2023-01-01', '2023-12-31');

SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- Lấy tất cả cột
SELECT * FROM Customers;

-- Lấy các cột cụ thể
SELECT CustomerName, City, Country 
FROM Customers;

-- Lọc dữ liệu
SELECT * FROM Customers 
WHERE Country = 'Vietnam';

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

-- Thêm một bản ghi
INSERT INTO Customers (CustomerName, ContactName, City)
VALUES ('ABC Company', 'John Doe', 'Hanoi');

-- Thêm nhiều bản ghi
INSERT INTO Customers (CustomerName, ContactName, City)
VALUES 
    ('XYZ Corp', 'Jane Smith', 'Ho Chi Minh'),
    ('123 Ltd', 'Bob Johnson', 'Da Nang');

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

-- Cập nhật một bản ghi
UPDATE Customers
SET ContactName = 'New Name', City = 'New City'
WHERE CustomerID = 1;

-- Cập nhật nhiều bản ghi
UPDATE Products
SET Price = Price * 1.1
WHERE CategoryID = 1;

DELETE FROM table_name
WHERE condition;

-- Xóa một bản ghi
DELETE FROM Customers
WHERE CustomerID = 1;

-- Xóa nhiều bản ghi
DELETE FROM Orders
WHERE OrderDate < '2020-01-01';

CREATE DATABASE TenDatabase
ON PRIMARY
(
    NAME = TenDatabase_Data,
    FILENAME = 'C:\Data\TenDatabase.mdf',
    SIZE = 10MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 5MB
)
LOG ON
(
    NAME = TenDatabase_Log,
    FILENAME = 'C:\Data\TenDatabase.ldf',
    SIZE = 5MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 5MB
);

-- Tạo index cho cột thường xuyên được sử dụng trong WHERE
CREATE INDEX IX_Customers_Email ON Customers(Email);

-- Tạo composite index cho nhiều cột
CREATE INDEX IX_Orders_CustomerDate ON Orders(CustomerID, OrderDate);

-- Không tốt: Sẽ scan toàn bộ index
SELECT * FROM Customers WHERE Email LIKE '%@gmail.com';

-- Tốt hơn: Sử dụng điều kiện chính xác
SELECT * FROM Customers WHERE Email = 'example@gmail.com';

-- Không tốt
SELECT o.OrderID, c.CustomerName
FROM Orders o
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID;

-- Tốt hơn
SELECT o.OrderID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

-- Tốt: Bắt đầu với bảng có ít dữ liệu nhất
SELECT o.OrderID, c.CustomerName, p.ProductName
FROM OrderDetails od
INNER JOIN Orders o ON od.OrderID = o.OrderID
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON od.ProductID = p.ProductID;

-- Không tốt
SELECT * FROM Customers;

-- Tốt hơn
SELECT CustomerID, CustomerName, Email FROM Customers;

-- Tốt: Sử dụng TOP với ORDER BY
SELECT TOP 10 OrderID, OrderDate, TotalAmount
FROM Orders
ORDER BY OrderDate DESC;

-- Không tốt: Không SARGable
SELECT * FROM Orders
WHERE YEAR(OrderDate) = 2024;

-- Tốt hơn: SARGable
SELECT * FROM Orders
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';

-- Không tốt
SELECT * FROM Products
WHERE LOWER(ProductName) = 'laptop';

-- Tốt hơn
SELECT * FROM Products
WHERE ProductName = 'Laptop';

WITH MonthlySales AS (
    SELECT 
        YEAR(OrderDate) AS Year,
        MONTH(OrderDate) AS Month,
        SUM(TotalAmount) AS TotalSales
    FROM Orders
    GROUP BY YEAR(OrderDate), MONTH(OrderDate)
)
SELECT 
    Year,
    Month,
    TotalSales,
    AVG(TotalSales) OVER (ORDER BY Year, Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAverage
FROM MonthlySales;

-- Không tốt
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

-- Tốt hơn
SELECT CustomerID, CustomerName
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

-- Không tốt
SELECT 
    CustomerID,
    CustomerName,
    (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) AS OrderCount
FROM Customers;

-- Tốt hơn
SELECT 
    c.CustomerID,
    c.CustomerName,
    COUNT(o.OrderID) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID, c.CustomerName;

DECLARE @TempOrders TABLE (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE
);

INSERT INTO @TempOrders
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate >= DATEADD(MONTH, -1, GETDATE());

CREATE TABLE #TempOrders (
    OrderID INT,
    CustomerID INT,
    OrderDate DATE
);

INSERT INTO #TempOrders
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderDate >= DATEADD(MONTH, -1, GETDATE());

-- Bật Execution Plan
SET SHOWPLAN_TEXT ON;
GO

-- Truy vấn cần phân tích
SELECT o.OrderID, c.CustomerName, p.ProductName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Products p ON o.ProductID = p.ProductID
WHERE o.OrderDate >= '2024-01-01';

-- Tắt Execution Plan
SET SHOWPLAN_TEXT OFF;
GO