STOP! Your SQL Server Log File is Out of Control - The RIGHT Way to Manage It
Is your SQL log file consuming all your disk space? 😱 Learn the safe, automated way to manage transaction log growth without breaking your backup chain. Free script included! #SQLServer #LogManagement #DBA

Introduction: The DBA's Nightmare - The 500GB Log File That Ate Your Server
It's 3 AM. Your monitoring system alerts: "Disk C: at 99% capacity." You investigate and find a single  .ldf  file has ballooned to hundreds of gigabytes. Panic sets in. Do you shrink it? Will it break something? How did this happen?

If you've faced this scenario, you're not alone.  Transaction log management is one of the most misunderstood aspects of SQL Server administration. In this guide, we'll give you not just an automated solution, but more importantly, the  knowledge to use it safely and effectively.

Critical Disclaimer: Read This Before Proceeding
Shrinking log files is generally NOT a best practice.  It should be treated as an emergency procedure, not regular maintenance. Frequent shrinking leads to:

• Virtual Log File (VLF) fragmentation  - causing performance degradation
• Immediate regrowth  - the very problem you're trying to solve
• Potential data loss  if not handled correctly

The proper long-term solution is:

• Proper backup strategy  (Transaction log backups for FULL recovery)
• Right-sizing  your log file from the beginning
• Monitoring growth patterns

Use this script for  emergency situations only  or in  development environments.

The Emergency Log Management Script 

-- =====================================================-- Procedure: spLogClear - EMERGENCY Transaction Log Management-- Description: Use ONLY for emergency log file reduction.--              Not recommended for regular maintenance.-- Author: FreeLearning365.com-- Usage: EXEC [dbo].[spLogClear]-- =====================================================

ALTER PROCEDURE [dbo].[spLogClear]ASBEGIN
    SET NOCOUNT ON;

    DECLARE
        @DBName SYSNAME,
        @LogFileName SYSNAME,
        @StartTime DATETIME = GETDATE(),
        @Msg NVARCHAR(MAX),
        @ErrorMsg NVARCHAR(MAX),
        @SQL NVARCHAR(MAX),
        @OriginalRecoveryModel NVARCHAR(60),
        @CurrentRecoveryModel NVARCHAR(60);

    BEGIN TRY
        -- SECTION 1: INITIALIZATION & VALIDATION
        -- ===========================================

        -- Dynamically detect current database context
        SET @DBName = DB_NAME();

        -- Safety Check: Prevent execution on system databases
        IF @DBName IN ('master', 'model', 'msdb', 'tempdb')
        BEGIN
            SET @Msg = 'CRITICAL: This procedure cannot be run on system databases. Attempted on: ' + @DBName;
            RAISERROR(@Msg, 16, 1);
            RETURN;
        END

        -- Detect the logical name of the transaction log file
        SELECT TOP 1 @LogFileName = name
        FROM sys.database_files
        WHERE type_desc = 'LOG';

        -- Safety Check: Ensure log file was found
        IF @LogFileName IS NULL
        BEGIN
            RAISERROR('Could not identify transaction log file for database: %s', 16, 1, @DBName);
            RETURN;
        END

        -- SECTION 2: AUDITING & RECOVERY MODEL MANAGEMENT
        -- ==================================================

        -- Capture original recovery model for restoration
        SELECT @OriginalRecoveryModel = recovery_model_desc
        FROM sys.databases
        WHERE name = @DBName;

        -- Log process initiation
        INSERT INTO log.LogShrinkAudit
        (DBName, LogFileName, StartTime, Status, Message)
        VALUES
        (@DBName, @LogFileName, @StartTime, 'Started',
         'Emergency log shrink initiated. Original Recovery: ' + @OriginalRecoveryModel);

        PRINT 'Starting emergency log management for database: ' + @DBName;
        PRINT 'Log file name: ' + @LogFileName;
        PRINT 'Original recovery model: ' + @OriginalRecoveryModel;

        -- SECTION 3: THE CORE LOG MANAGEMENT PROCESS
        -- =============================================

        -- STEP 3.1: TEMPORARY RECOVERY MODEL SWITCH
        -- WARNING: This breaks the log backup chain in FULL recovery!
        SET @SQL = N'ALTER DATABASE [' + @DBName + N'] SET RECOVERY SIMPLE;';
        EXEC(@SQL);
        PRINT 'Temporarily switched to SIMPLE recovery model.';

        -- STEP 3.2: CHECKPOINT - Flushes dirty pages to data file
        CHECKPOINT;
        PRINT 'Checkpoint completed.';

        -- STEP 3.3: SHRINK LOG FILE (THE MAIN EVENT)
        -- Parameter 0 = shrink to smallest possible size
        SET @SQL = N'DBCC SHRINKFILE (N''' + @LogFileName + N''', 0);';
        EXEC(@SQL);
        PRINT 'Log file shrink completed.';

        -- STEP 3.4: RIGHT-SIZE THE LOG FILE (CRITICAL STEP!)
        -- Prevents immediate autogrowth. Adjust 1000MB based on your needs.
        SET @SQL = N'ALTER DATABASE [' + @DBName + N']
                    MODIFY FILE (NAME = N''' + @LogFileName + N''',
                                SIZE = 1000MB,
                                MAXSIZE = UNLIMITED,
                                FILEGROWTH = 500MB);';
        EXEC(@SQL);
        PRINT 'Log file resized to prevent immediate regrowth.';

        -- STEP 3.5: RESTORE ORIGINAL RECOVERY MODEL
        IF @OriginalRecoveryModel = 'FULL'
        BEGIN
            SET @SQL = N'ALTER DATABASE [' + @DBName + N'] SET RECOVERY FULL;';
            EXEC(@SQL);
            PRINT 'Recovery model restored to FULL.';

            -- CRITICAL: Take a FULL backup after restoring FULL recovery
            -- This establishes a new backup chain
            SET @Msg = 'IMPORTANT: Take a FULL database backup immediately to re-establish backup chain.';
            PRINT @Msg;
        END

        -- SECTION 4: SUCCESS REPORTING
        -- ===============================

        SET @Msg = N'Emergency log management completed successfully for database [' + @DBName +
                   N']. Process completed at ' + CONVERT(NVARCHAR(30), GETDATE(), 120);

        INSERT INTO log.LogShrinkAudit
        (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES
        (@DBName, @LogFileName, @StartTime, GETDATE(), 'Success', @Msg);

        PRINT @Msg;
        PRINT '=== PROCESS COMPLETED SUCCESSFULLY ===';

    END TRY

    BEGIN CATCH
        -- SECTION 5: COMPREHENSIVE ERROR HANDLING
        -- ==========================================

        SET @ErrorMsg = 'ERROR [' + CAST(ERROR_NUMBER() AS NVARCHAR(10)) + ']: ' +
                        ERROR_MESSAGE() + ' (Line: ' + CAST(ERROR_LINE() AS NVARCHAR(10)) + ')';

        PRINT 'PROCESS FAILED: ' + @ErrorMsg;

        -- Attempt to restore original recovery model on failure
        BEGIN TRY
            IF @OriginalRecoveryModel IS NOT NULL AND @OriginalRecoveryModel != 'SIMPLE'
            BEGIN
                SET @SQL = N'ALTER DATABASE [' + @DBName + N'] SET RECOVERY ' + @OriginalRecoveryModel + N';';
                EXEC(@SQL);
                PRINT 'Original recovery model restored after failure.';
            END
        END TRY
        BEGIN CATCH
            PRINT 'WARNING: Could not restore original recovery model after failure.';
        END CATCH

        -- Log the failure
        INSERT INTO log.LogShrinkAudit
        (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES
        (@DBName, ISNULL(@LogFileName, 'Unknown'), @StartTime, GETDATE(), 'Failed', @ErrorMsg);

        -- Re-throw error for external handling (e.g., SQL Agent)
        THROW;

    END CATCH
END
GO

Step-by-Step Implementation Guide
Step 1. Prerequisites & Safety Checks
Create the Audit Table:

CREATE SCHEMA [log];
GO

CREATE TABLE [log].[LogShrinkAudit](
    [AuditID] [int] IDENTITY(1,1) NOT NULL,
    [DBName] [sysname] NOT NULL,
    [LogFileName] [sysname] NOT NULL,
    [StartTime] [datetime] NULL,
    [EndTime] [datetime] NULL,
    [Status] [nvarchar](50) NULL,
    [Message] [nvarchar](max) NULL)

Permissions Required:

• ALTER DATABASE  permission on the target database
• INSERT  permission on the audit table
• Membership in  db_owner  role is typically required

Step 2. Initial Testing (SAFE MODE)
NEVER run this in production without testing first!

-- TEST 1: Check what would happen (read-only check)-- Examine current log size and VLFsSELECT
    name AS [LogFileName],
    size * 8.0 / 1024 AS [SizeMB],
    CASE WHEN size = max_size THEN 'FULL' ELSE 'GROWTH AVAILABLE' END AS [Status]FROM sys.database_files
WHERE type_desc = 'LOG';

-- Check VLF count (high count = fragmentation)DBCC LOGINFO;

Step 3. Emergency Execution

Only proceed if you have a genuine emergency and understand the risks:
-- Execute the emergency procedureEXEC [dbo].[spLogClear];
-- Monitor the audit logSELECT * FROM [log].[LogShrinkAudit] ORDER BY StartTime DESC;
-- RITICAL POST-PROCESS STEP: Take a FULL backupBACKUP DATABASE [YourDatabase]
TO DISK = 'D:\Backups\PostShrink_FullBackup.bak'WITH COMPRESSION, INIT;

Pros, Cons, and Best Practices 

The RIGHT Way: Long-Term Log Management Strategy

Instead of regular shrinking, implement this

For FULL Recovery Model Databases
-- Schedule frequent transaction log backupsBACKUP LOG [YourDatabase]
TO DISK = 'D:\LogBackups\YourDatabase_Log.trn'WITH COMPRESSION;

    Right-Size from Beginning

-- Set appropriate initial size and growthALTER DATABASE [YourDatabase]MODIFY FILE (NAME = YourDatabase_Log,
         SIZE = 4096MB,
         FILEGROWTH = 1024MB); -- Fixed growth, not percentage

    Monitoring & Alerting

-- Regular monitoring querySELECT
    name AS DatabaseName,
    (size * 8.0 / 1024) AS LogSizeMB,
    (CAST(used_space AS float) * 8 / 1024) AS UsedSpaceMB,
    (size * 8.0 / 1024) - (CAST(used_space AS float) * 8 / 1024) AS FreeSpaceMB
FROM sys.dm_db_log_space_usage
CROSS APPLY sys.databases
WHERE sys.databases.database_id = sys.dm_db_log_space_usage.database_id;

Business Case & Limitations

Business Case:  Prevents production outages due to disk space exhaustion. Maintains system availability during unexpected log growth scenarios.

Limitations

• Temporary solution  - doesn't address root cause
• Performance impact  - VLF fragmentation affects write performance
• Recovery implications  - breaks the point-in-time recovery capability until a new full backup is taken
• Not a substitute  for a proper backup strategy

"The truth your DBA won't tell you about log files"
"From panic to solution in 5 minutes"
"The emergency fix that saved our production server"
"Why your log file keeps growing (and how to stop it forever)"
"The shrink operation that won't get you fired"

Remember: This script is your  emergency fire extinguisher  - essential to have, but you hope you never need to use it! 

Alternative Approach
Executive summary/business case

• Why this matters
• Large or uncontrolled log files consume disk, slow backups, complicate restores, and amplify ransomware damage.
• Frequent, ad-hoc shrinking causes fragmentation and performance problems.

The right approach: prevent log growth with a correct recovery model + frequent log backups, monitor, and only shrink when necessary with a documented, auditable process.

Business benefits

• Predictable disk usage and lower storage costs.
• Faster restores and improved availability.
• Audit trail for operations (compliance & change control).

Key principles & best standard (short)

• Prefer prevention over cure:  take frequent transaction-log backups (FULL model) or use SIMPLE model only for dev/test.
• Avoid routine shrinking:  shrink only when required (e.g., after one-off huge operation, log growth due to long-running transaction, or DB restore/maintenance).
• Logically plan growth settings:  set sensible  FILEGROWTH  and  INITIAL SIZE —Avoid tiny percent-based growth for large logs.
• Automate monitoring & alerts:  watch  log_reuse_wait_desc , free space, and autogrowth events.
• Audit & document any shrink operation.  Record who ran it, why, and the before/after sizes.

Common causes of log growth
Long-running or uncommitted transactions
Replication, CHANGE_TRACKING, CDC, DB mirroring, AlwaysOn, long-running backups
Missing or infrequent log backups in the FULL recovery model
Bulk operations (index rebuilds, large loads)

Pros & Cons of shrinking logs
Pros
Instantly reclaims disk after one-time surge.
Useful after large one-off operations or restoring from a backup with a larger log.

Cons
Causes file fragmentation and potential performance degradation.
Shrinking regrows logs if operations continue — regrowth is expensive.
Not a long-term solution to recurring growth.

Best practices (step-by-step checklist before shrinking)

• Investigate root cause: Check  DBCC SQLPERF(LOGSPACE)  and  sys.databases   log_reuse_wait_desc .
• Fix underlying issue: e.g., schedule frequent log backups, commit/kill long transactions, disable/reconfigure features.
• Take a transaction log backup (FULL model)  to free up virtual log files (VLFs) if possible.
• Delay shrinking until log is reusable  (log_reuse_wait_desc = NOTHING).
• Document & audit : always insert an audit record before/after shrink.
• Shrink in a maintenance window and monitor performance, autogrowth.
• Adjust file growth strategy: e.g.,  FILEGROWTH = 512MB  for busy OLTP DBs, avoid 1% growth for large files.
• Perform index maintenance afterwards if required (heavy shrink may impact fragmentation).

Security, permissions & masking sensitive fields

• Do not run shrink scripts as  sa  unless required. Use a least-privilege account with  ALTER DATABASE  permissions or run as an approved operator via SQL Agent.
• Mask sensitive values in audit/log tables (if you store server or path info that could reveal structure). Example: store  ServerName  hashed or store only the first/last 8 chars.
• Record operator identity using  SUSER_SNAME()  or  ORIGINAL_LOGIN()  — but don’t store personal data that is not necessary.

Table design (you provided) — small improvements
Add a default  StartTime  and index for queries:
ALTER TABLE log.LogShrinkAudit
ADD CONSTRAINT DF_LogShrinkAudit_StartTime DEFAULT (GETDATE()) FOR StartTime;

CREATE NONCLUSTERED INDEX IX_LogShrinkAudit_DBName_StartTime
ON log.LogShrinkAudit(DBName, StartTime DESC);

Masking note: if you log paths or server names and want to mask, store a hash:  HASHBYTES('SHA2_256', @FullServerPath)  (store varbinary) and keep mapping in a secure admin-only table.

Production-ready stored procedure (improved, audited, safe)
Key improvements over the original:

Detects  log_reuse_wait_desc  and refuses to shrink unless reason is  NOTHING  or explicit override given.

Optional  @Force  for documented emergency shrink with required  @Reason  param.

Records before/after sizes, VLF count, user who ran it.

Graceful error handling and throttled shrink (shrinks to target percent/size).

Avoids setting recovery model from FULL→SIMPLE→FULL automatically (dangerous) unless explicitly allowed.

Warning:  Changing recovery model from FULL→SIMPLE causes break in log backup chain. Only do if you know consequences.

-- ================================================-- dbo.spLogClear_Managed-- Safer, auditable transactional log cleanup procedure-- Usage: EXEC dbo.spLogClear_Managed @TargetSizeMB=1024, @Force=0, @AllowRecoveryChange=0, @Reason='...';-- ================================================CREATE OR ALTER PROCEDURE dbo.spLogClear_Managed
(
@TargetSizeMB INT = 1024,            -- desired final size in MB (approx)
@MinFreePercent INT = 10,            -- shrink only if free space percent > this
@Force BIT = 0,                      -- 1 = allow shrink even if log_reuse_wait != NOTHING (use with caution)
@AllowRecoveryChange BIT = 0,        -- 1 = allow temporary switch to SIMPLE (dangerous, breaks log chain)
@Reason NVARCHAR(4000) = NULL        -- required if @Force = 1 or @AllowRecoveryChange = 1
)
ASBEGIN
SET NOCOUNT ON;
DECLARE @DBName SYSNAME = DB_NAME();
DECLARE @LogFileName SYSNAME;
DECLARE @StartTime DATETIME = GETDATE();
DECLARE @BeforeSizeMB INT;
DECLARE @BeforeUsedPercent DECIMAL(5,2);
DECLARE @AfterSizeMB INT;
DECLARE @SQL NVARCHAR(MAX);
DECLARE @WaitDesc SYSNAME;
DECLARE @Operator SYSNAME = SUSER_SNAME();
DECLARE @AuditMsg NVARCHAR(MAX);

BEGIN TRY
    -- 1. Log initial state: find log file
    SELECT TOP(1)
        @LogFileName = name
    FROM sys.database_files
    WHERE type_desc = 'LOG';

    -- 2. Get current log size & used percent
    SELECT
        @BeforeSizeMB = CAST(size/128.0 AS INT),
        @BeforeUsedPercent = CAST( ( (size - CAST(FILEPROPERTY(name,'SpaceUsed') AS INT)) * 100.0 / NULLIF(size,0) ) AS DECIMAL(5,2) )
    FROM sys.database_files
    WHERE type_desc = 'LOG' AND name = @LogFileName;

    -- 3. Determine log reuse wait reason
    SELECT @WaitDesc = log_reuse_wait_desc
    FROM sys.databases
    WHERE name = @DBName;

    -- 4. Audit start
    INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, Status, Message)
    VALUES (@DBName, @LogFileName, @StartTime, 'Started',
            'Initiated by ' + ISNULL(@Operator,'Unknown') + '; log_reuse_wait_desc=' + ISNULL(@WaitDesc,'Unknown'));

    -- 5. Safety checks
    IF @Force = 0 AND @WaitDesc <> 'NOTHING'
    BEGIN
        SET @AuditMsg = 'Abort: log_reuse_wait_desc = ' + @WaitDesc + '. Use proper log backup or resolve wait reason before shrinking.';
        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES(@DBName, @LogFileName, @StartTime, GETDATE(), 'Aborted', @AuditMsg);
        RETURN;
    END

    IF @Force = 1 AND (@Reason IS NULL OR LEN(@Reason) < 5)
    BEGIN
        THROW 51010, 'Force requested but @Reason is required (short justification).', 1;
    END

    -- 6. Optionally switch recovery model (DANGEROUS)
    IF @AllowRecoveryChange = 1
    BEGIN
        IF @Reason IS NULL OR LEN(@Reason) < 5
            THROW 51011, 'AllowRecoveryChange requires a justified @Reason.', 1;

        SET @SQL = N'ALTER DATABASE [' + @DBName + '] SET RECOVERY SIMPLE WITH NO_WAIT;';
        EXEC(@SQL);

        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, Status, Message)
        VALUES(@DBName, @LogFileName, @StartTime, 'Info', 'Recovery switched to SIMPLE temporarily. Reason: ' + @Reason);
    END

    -- 7. Do a checkpoint if in SIMPLE or after log backup
    CHECKPOINT;

    -- 8. Recompute size and used percent (safe check)
    SELECT
        @BeforeSizeMB = CAST(size/128.0 AS INT),
        @BeforeUsedPercent = CAST( ( (size - CAST(FILEPROPERTY(name,'SpaceUsed') AS INT)) * 100.0 / NULLIF(size,0) ) AS DECIMAL(5,2) )
    FROM sys.database_files
    WHERE type_desc = 'LOG' AND name = @LogFileName;

    -- 9. Check min free percent condition
    IF @BeforeUsedPercent <= (100 - @MinFreePercent)
    BEGIN
        -- compute target shrink size in pages (approx) using DBCC SHRINKFILE target in MB
        SET @SQL = N'DBCC SHRINKFILE ([' + @LogFileName + '], ' + CAST(@TargetSizeMB AS NVARCHAR(20)) + ');';
        EXEC(@SQL);

        -- Capture after size
        SELECT @AfterSizeMB = CAST(size/128.0 AS INT)
        FROM sys.database_files
        WHERE type_desc = 'LOG' AND name = @LogFileName;

        SET @AuditMsg = 'Shrink attempted. BeforeSizeMB=' + CAST(@BeforeSizeMB AS NVARCHAR(20)) +
                        '; AfterSizeMB=' + CAST(ISNULL(@AfterSizeMB, -1) AS NVARCHAR(20));
        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES(@DBName, @LogFileName, @StartTime, GETDATE(), 'Success', @AuditMsg);
    END
    ELSE
    BEGIN
        SET @AuditMsg = 'Abort: insufficient free space in log to shrink safely. UsedPercent=' + CAST(@BeforeUsedPercent AS NVARCHAR(10));
        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, EndTime, Status, Message)
        VALUES(@DBName, @LogFileName, @StartTime, GETDATE(), 'Skipped', @AuditMsg);
    END

    -- 10. Restore recovery model if changed (only if @AllowRecoveryChange = 1)
    IF @AllowRecoveryChange = 1
    BEGIN
        SET @SQL = N'ALTER DATABASE [' + @DBName + '] SET RECOVERY FULL WITH NO_WAIT;';
        EXEC(@SQL);

        INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, Status, Message)
        VALUES(@DBName, @LogFileName, GETDATE(), 'Info', 'Recovery switched back to FULL. Ensure you take a full + log backup to re-establish chain.');
    END
END TRY
BEGIN CATCH
    DECLARE @ErrMsg NVARCHAR(MAX) = ERROR_MESSAGE();
    INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, EndTime, Status, Message)
    VALUES(@DBName, ISNULL(@LogFileName,'Unknown'), @StartTime, GETDATE(), 'Failed', @ErrMsg);
    THROW; -- re-throw for agent visibility
END CATCH
END
GO

Usage examples
Normal safe run (abort if log is in use):
EXEC dbo.spLogClear_Managed @TargetSizeMB=1024, @MinFreePercent=20;

Emergency (must supply reason):
EXEC dbo.spLogClear_Managed @TargetSizeMB=512, @Force=1, @Reason='Emergency disk pressure after large archive load';

Temporary recovery change (dangerous; document and follow-up!):
EXEC dbo.spLogClear_Managed @TargetSizeMB=512, @AllowRecoveryChange=1, @Reason='One-off maintenance, will take full backup after';

Alternatives & automation optionsPreferred:  Fix root cause (frequent log backups) — implement a schedule  BACKUP LOG  to reduce VLF usage.

• Ola Hallengren  — maintenance solution includes integrity checks, backup, and cleanup.
• PowerShell approach:  use  Invoke-Sqlcmd  to check state and issue shrink if needed; easier to integrate with vaults and monitoring.
• Third-party:  enterprise tools that manage log shipping, archiving, and unintrusive cleanup.

Monitoring & telemetry (what to alert on)
log_reuse_wait_desc  ≠ NOTHING — alert when it grows continuously.

Rapid autogrowth events — alert on frequent autogrowth.

Free disk space thresholds.

Number of VLFs (very large counts cause performance issues) — use  DBCC LOGINFO  or DMV-based scripts.

Risk mitigation & rollback plan
Before : always take current full + log backup.

If the shrink fails or the DB becomes suspect: have an emergency restore plan; always test restores in staging.
After: if you changed the recovery model, take a full backup, then restart the log backup schedule to reestablish the chain.

Masking sensitive fields example
When inserting path/server info into logs, store only hashed values or partials:
INSERT INTO log.LogShrinkAudit (DBName, LogFileName, StartTime, Status, Message)
VALUES(@DBName, HASHBYTES('SHA2_256', @LogFileName), GETDATE(), 'Started', 'Initiated by ' + SUSER_SNAME());

Store mapping in a separate secure table accessible only to auditors.

• “Ransomware-resistant log strategy”
• “Zero-trust backup & log hygiene”
• “Immutable audit trail”
• “Predictable storage, instant restoration”
• “Proactive log management — not reactive surgery”

SET SERVEROUTPUT ON in PL/SQL: Why It Matters and How to Use It
When working with Oracle PL/SQL, one of the first and most essential commands you'll encounter is:

SET SERVEROUTPUT ON;
Though it looks simple, this command plays a crucial role in how PL/SQL programs behave, especially when you want to display output from procedures, anonymous blocks, or scripts using DBMS_OUTPUT.PUT_LINE.

Why Do We Use SET SERVEROUTPUT ON?
By default, PL/SQL code runs silently inside the Oracle database engine. This means that even if your program generates output, you won’t see any result on your screen — unless you explicitly enable server output.

The command SET SERVEROUTPUT ON instructs SQL*Plus or SQL Developer to display output from the DBMS_OUTPUT buffer, allowing you to see the results of your PL/SQL program.

Syntax
SET SERVEROUTPUT ON;

You can also control the buffer size and format (optional):
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED;

When Should You Use It?
Use SET SERVEROUTPUT ON in situations like:

When using DBMS_OUTPUT.PUT_LINE to display output.

During testing and debugging of PL/SQL procedures or anonymous blocks.

To trace variable values or track the flow of control in your code.

Example: Using SET SERVEROUTPUT ON in a PL/SQL Block

Here’s a simple example that declares variables and uses DBMS_OUTPUT.PUT_LINE to display them:
SET SERVEROUTPUT ON;

DECLARE
    eno NUMBER(5) NOT NULL := 2;
    ename VARCHAR2(15) := 'Branson Devs';
    edept CONSTANT VARCHAR2(15) := 'Web Developer';
BEGIN
    dbms_output.put_line('Declared Values:');
    dbms_output.put_line(' Employee Number: ' || eno || ' Employee Name: ' || ename);
    dbms_output.put_line('Constant Declared:');
    dbms_output.put_line(' Employee Department: ' || edept);
END;
/

Output (Only Visible If SERVEROUTPUT Is ON):
Declared Values:
Employee Number: 2 Employee Name: Branson Devs
Constant Declared:
Employee Department: Web Developer

Important: If you omit SET SERVEROUTPUT ON, the DBMS_OUTPUT.PUT_LINE results will not be displayed, even though the block executes successfully.

Tips for Using SET SERVEROUTPUT ON
In SQL Developer, go to View > DBMS Output, then click the green + icon to enable output for your session.
In SQL*Plus, always run SET SERVEROUTPUT ON before any PL/SQL block that uses output.
Use SET SERVEROUTPUT OFF when you no longer need the output to be displayed.

Conclusion
The SET SERVEROUTPUT ON command is small but vital for writing and debugging PL/SQL code. It provides visibility into your PL/SQL logic by allowing output messages to be isplayed on screen, making your development workflow smoother and more transparent.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

The common language used to manage and communicate with relational databases is called Structured Query Language, or SQL. SQL provides the capabilities to effectively store, manage, and retrieve data, whether you're developing an enterprise application, corporate dashboard, or website.

What is SQL?
SQL (pronounced “ess-que-el” or sometimes “sequel”) is a domain-specific language used to communicate with relational database management systems (RDBMS) such as:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle Database

SQLite
SQL lets you describe what data you want, while the database engine figures out how to get it.

Core Features of SQL

• Data Definition: Create and modify the structure of databases (tables, views, indexes).
• Data Manipulation: Insert, update, delete, and retrieve records.
• Data Control: Manage permissions and security (GRANT, REVOKE).
• Transaction Control: Commit or roll back changes safely.

Basic SQL Commands
Here are some of the most commonly used SQL statements:

Create a Table
CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

Name VARCHAR(50),

Position VARCHAR(50),

Salary DECIMAL(10,2)

);

Insert Data
INSERT INTO Employees (EmployeeID, Name, Position, Salary)

VALUES (1, 'Alice', 'Developer', 65000);

Retrieve Data
SELECT Name, Position

FROM Employees

WHERE Salary > 60000;

Update Data
UPDATE Employees

SET Salary = 70000

WHERE EmployeeID = 1;

Delete Data
DELETE FROM Employees

WHERE EmployeeID = 1;

Why SQL is Important?

• Universality: Nearly all relational databases use SQL or a close dialect.
• Powerful Queries: Combine, group, and filter data with ease.
• Data Integrity: Enforce constraints (primary keys, foreign keys) to keep data consistent.
• Scalability: Handle anything from a small app’s data to enterprise-level systems.

Common Uses of SQL

• Business intelligence and reporting
• Backend for web and mobile apps
• Data analytics and dashboards
• Financial and inventory systems
• Data migration between platforms

Advantages of SQL

• Human-readable, declarative syntax
• Optimized by database engines for performance
• Portable across platforms with minimal changes
• Supports complex operations with relatively simple commands

Limitations

• Not ideal for unstructured or semi-structured data (that’s where NoSQL databases shine).
• Large, complex queries can become hard to maintain without proper design.
• Performance tuning may require knowledge of indexes, execution plans, and normalization.

Conclusion
The foundation of relational systems' data handling is SQL. You acquire a useful ability that forms the basis of practically every contemporary software program by becoming proficient with its commands and comprehending how databases arrange data. SQL is an essential skill for anyone dealing with data, whether they are an analyst, developer, or data scientist.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

The most of the harm is typically caused by a few statements when a SQL Server feels "slow." With Dynamic Management Views (DMVs), you can locate them the quickest. The worst offenders are uncovered by CPU, IO, duration, and "what's running right now" using the copy-paste scripts below, along with instructions on how to read the results and what to do next.

    Requirements: VIEW SERVER STATE permission. Numbers like total_worker_time and total_elapsed_time are in microseconds unless noted.

What “high usage” means (pick the lens)?

• CPU: how much processor time a query uses.
• IO: logical/physical reads and writes (memory and disk pressure).
• Duration: how long a query takes end-to-end.
• Currently running: live workload that may be blocking others.

You’ll use a different script depending on which lens you want.

Top queries by CPU
-- Top 20 queries by total CPU since the plan was cached
SELECT TOP 20
    DB_NAME(st.dbid)                                        AS database_name,
    qs.execution_count,
    qs.total_worker_time/1000.0                             AS total_cpu_ms,
    (qs.total_worker_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_cpu_ms,
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_duration_ms,
    qs.total_logical_reads + qs.total_physical_reads        AS total_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
          - qs.statement_start_offset)/2) + 1)              AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Tip: Add WHERE DB_NAME(st.dbid) = 'YourDbName' if you only care about one database.

Top queries by IO (reads/writes)
-- Top 20 by total reads; add writes if you care about heavy DML
SELECT TOP 20
    DB_NAME(st.dbid)                                        AS database_name,
    qs.execution_count,
    (qs.total_logical_reads + qs.total_physical_reads)      AS total_reads,
    (qs.total_logical_writes + qs.total_physical_writes)    AS total_writes,
    (qs.total_logical_reads + qs.total_physical_reads) / NULLIF(qs.execution_count,0) AS avg_reads,
    (qs.total_logical_writes + qs.total_physical_writes) / NULLIF(qs.execution_count,0) AS avg_writes,
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_duration_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
          - qs.statement_start_offset)/2) + 1)              AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_reads DESC;

Top queries by average duration
-- Queries that are slow per execution (not just popular)
SELECT TOP 20
    DB_NAME(st.dbid)                                        AS database_name,
    qs.execution_count,
    (qs.total_elapsed_time/1000.0)                          AS total_duration_ms,
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0) AS avg_duration_ms,
    (qs.total_worker_time/1000.0)/NULLIF(qs.execution_count,0)  AS avg_cpu_ms,
    (qs.total_logical_reads + qs.total_physical_reads) / NULLIF(qs.execution_count,0) AS avg_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset END
          - qs.statement_start_offset)/2) + 1)              AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE qs.execution_count > 0
ORDER BY avg_duration_ms DESC;

Stored procedures that hit the server hardest
Use sys.dm_exec_procedure_stats to get procedure-level rollups (cleaner than trying to stitch statements together).
-- Procedures by total CPU
SELECT TOP 20
    DB_NAME(ps.database_id)                                 AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id)        AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id)               AS procedure_name,
    ps.execution_count,
    ps.total_worker_time/1000.0                             AS total_cpu_ms,
    (ps.total_worker_time/1000.0)/NULLIF(ps.execution_count,0) AS avg_cpu_ms,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4  -- skip system DBs; remove if you want them
ORDER BY ps.total_worker_time DESC;

-- Procedures by total reads (IO)
SELECT TOP 20
    DB_NAME(ps.database_id)                                 AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id)        AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id)               AS procedure_name,
    ps.execution_count,
    (ps.total_logical_reads + ps.total_physical_reads)      AS total_reads,
    ((ps.total_logical_reads + ps.total_physical_reads)/NULLIF(ps.execution_count,0)) AS avg_reads,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4
ORDER BY total_reads DESC;

-- Procedures by average duration
SELECT TOP 20
    DB_NAME(ps.database_id)                                 AS database_name,
    OBJECT_SCHEMA_NAME(ps.object_id, ps.database_id)        AS schema_name,
    OBJECT_NAME(ps.object_id, ps.database_id)               AS procedure_name,
    ps.execution_count,
    (ps.total_elapsed_time/1000.0)/NULLIF(ps.execution_count,0) AS avg_duration_ms,
    ps.last_execution_time
FROM sys.dm_exec_procedure_stats AS ps
WHERE ps.database_id > 4 AND ps.execution_count > 0
ORDER BY avg_duration_ms DESC;

What’s heavy right now (live view)?
-- Currently executing requests ordered by CPU time
SELECT
    r.session_id,
    r.status,
    DB_NAME(r.database_id)            AS database_name,
    r.cpu_time                        AS cpu_ms,         -- already in ms
    r.total_elapsed_time              AS elapsed_ms,     -- already in ms
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    SUBSTRING(t.text, r.statement_start_offset/2 + 1,
        (CASE WHEN r.statement_end_offset = -1
              THEN DATALENGTH(t.text)
              ELSE r.statement_end_offset END - r.statement_start_offset)/2 + 1) AS running_statement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

If you see blocking_session_id populated, chase that session and fix the blocker first.

Group similar statements together (query_hash)
Same query text with different literals can appear as separate rows. Aggregate by query_hash to see the true top offenders.
-- Roll up by query_hash to combine similar statements
WITH q AS (
    SELECT
        qs.query_hash,
        qs.execution_count,
        qs.total_worker_time,
        qs.total_elapsed_time,
        qs.total_logical_reads + qs.total_physical_reads AS total_reads
    FROM sys.dm_exec_query_stats qs
)
SELECT TOP 20
    query_hash,
    SUM(execution_count)                                  AS executions,
    SUM(total_worker_time)/1000.0                         AS total_cpu_ms,
    (SUM(total_worker_time)/1000.0)/NULLIF(SUM(execution_count),0) AS avg_cpu_ms,
    (SUM(total_elapsed_time)/1000.0)/NULLIF(SUM(execution_count),0) AS avg_duration_ms,
    SUM(total_reads)                                      AS total_reads
FROM q
GROUP BY query_hash
ORDER BY total_cpu_ms DESC;

Filters you’ll actually use
Add these lines to any query above as needed:
-- Only one DB
WHERE DB_NAME(st.dbid) = 'YourDbName'

-- Only statements executed in the last day (approx; uses last_execution_time)
WHERE qs.last_execution_time >= DATEADD(DAY, -1, SYSUTCDATETIME())

-- Exclude trivial one-off executions
AND qs.execution_count >= 5

Read the numbers the right way

• High total + low average: popular query. Optimize for throughput (indexing, cached plan quality).
• Low total + very high average: rare but slow. Optimize for latency (rewrite, avoid RBAR/scalar UDFs, better joins).
• High duration but modest CPU/IO: usually blocking or waits. Check wait_type, blocking_session_id, and missing indexes that cause scans.
• Metrics reset when plans get evicted or the instance restarts. Treat them as a rolling window, not forever history.

Quick wins to try after you find a culprit

• Add the right index (covering where needed). Look at the actual plan’s missing index hints, then design a lean index yourself (don’t blindly accept 12-column monsters).
• Kill implicit conversions (mismatched data types, e.g., NVARCHAR vs INT).
• Replace SELECT * with exact columns (cuts reads).
• Update statistics if they’re stale; consider WITH RECOMPILE for bad parameter sniffing cases (sparingly).
• Avoid scalar UDFs in hot paths; inline logic or use APPLY.
• Batch big writes; keep transactions short.

Bonus: store a snapshot for trending
If you want a daily/15-minute snapshot to trend over time:
-- One-time setup
CREATE TABLE dbo.TopQuerySnapshot
(
    captured_at           DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
    database_name         SYSNAME,
    executions            BIGINT,
    total_cpu_ms          BIGINT,
    avg_cpu_ms            DECIMAL(18,2),
    avg_duration_ms       DECIMAL(18,2),
    total_reads           BIGINT,
    query_text            NVARCHAR(MAX)
);

-- Collector (schedule as an Agent Job)
INSERT INTO dbo.TopQuerySnapshot (database_name, executions, total_cpu_ms, avg_cpu_ms, avg_duration_ms, total_reads, query_text)
SELECT TOP 50
    DB_NAME(st.dbid),
    qs.execution_count,
    qs.total_worker_time/1000,
    (qs.total_worker_time/1000.0)/NULLIF(qs.execution_count,0),
    (qs.total_elapsed_time/1000.0)/NULLIF(qs.execution_count,0),
    (qs.total_logical_reads + qs.total_physical_reads),
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

Now you can chart trends and prove improvements.

Common questions
Does this include plans not in cache?
No. DMVs reflect what’s cached. For long-term history, enable Query Store (SQL Server 2016+) and analyze sys.query_store_runtime_stats.

What about currently blocked sessions?
Use the “live view” script; chase the blocking_session_id, then inspect that session’s SQL text and plan.

Conclusion
CPU comes first, followed by IO and duration. If users are currently complaining, use the live view. Check its strategy, add the appropriate index, correct data types, and retest when you've identified a heavy hitter. Big results are typically achieved with small, targeted modifications.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

The amount of data generated and stored by organizations has been growing exponentially in recent years. Big data refers to this large and complex data sets that traditional data processing applications cannot handle. Managing and analyzing big data is becoming increasingly important for businesses to gain insights and stay ahead of the competition. Microsoft SQL Server is a powerful database management system capable of handling big data.

In this article, we will use appropriate examples to explore the techniques for working with big data in SQL Server.

Partitioning
Partitioning is a technique used to break large tables into smaller, more manageable pieces called partitions. Each partition contains a subset of the data, which can be processed and queried independently. SQL Server supports horizontal partitioning, which involves splitting data based on a column or set of columns.

For example, consider a table containing sales data for a large retail store. Partitioning the table based on the year column would create a separate partition for each year of data. This would allow queries to be run on individual partitions rather than the entire table, resulting in faster query performance. First we need to create a table with partitioned indexes. We can use the below code to create a table called "Sales" with a partitioned index on the "SaleDate" column:
CREATE TABLE [dbo].[Sales]
(
    [SaleID] [int] NOT NULL PRIMARY KEY,
    [Product] [nvarchar](50) NOT NULL,
    [SaleDate] [date] NOT NULL,
    [SaleAmount] [money] NOT NULL
)
WITH (MEMORY_OPTIMIZED = OFF, DURABILITY = ON)
GO

CREATE PARTITION FUNCTION [PF_Sales_SaleDate]
    (date)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-02-01', '2023-03-01');

CREATE PARTITION SCHEME [PS_Sales_SaleDate]
    AS PARTITION [PF_Sales_SaleDate]
    TO ([FG_Sales_202301], [FG_Sales_202302], [FG_Sales_202303], [FG_Sales_202304]);

CREATE CLUSTERED INDEX [CI_Sales_SaleDate]
ON [dbo].[Sales] ([SaleDate])
WITH (DROP_EXISTING = ON)
ON [PS_Sales_SaleDate] ([SaleDate]);

The code creates a table with columns for SaleID, Product, SaleDate, and SaleAmount, and defines a primary key on the SaleID column. The table is defined as DURABILITY = ON to ensure data is written to disk, but MEMORY_OPTIMIZED = OFF to ensure that data is not stored in memory. The partition function and scheme are defined to partition the table based on the SaleDate column, with partitions for January, February, and March of 2023 and at last, a clustered index is created on the SaleDate column, using the partition scheme to distribute the index across the partitions. Once we have created the table, we can insert some data into it using below query.

INSERT INTO [dbo].[Sales] ([SaleID], [Product], [SaleDate], [SaleAmount])
VALUES (1, 'Product A', '2022-01-01', 100.00),(2, 'Product B', '2022-01-02', 200.00),
       (3, 'Product C', '2022-01-03', 300.00),(4, 'Product A', '2022-02-01', 400.00),
       (5, 'Product B', '2022-02-02', 500.00),(6, 'Product C', '2022-02-03', 600.00);

Now whenever we can query the Sales table, the partitioned index will automatically be used. SQL Server can scan only the partitions that contain the relevant data. This improves query performance and reduces the amount of disk I/O required. Partitioning indexes is a powerful feature in SQL Server that can significantly improve the performance of queries on large tables. By partitioning a table based on a specific column, SQL Server can scan only the relevant partitions, reducing disk I/O and improving query performance.

Columnstore Indexes
Columnstore indexes are a specialized type of index that is optimized for large data warehouses. They store data in columns rather than rows, which makes them much more efficient for querying large datasets. Columnstore indexes are particularly useful for frequently queried but rarely updated data.

For example, consider a table containing customer sales data for a large online retailer. A columnstore index could be created on the Product columns. This would allow for very fast querying of the total sales for each product. First we need to create a table called "Sales" with a columnstore index on the "Product" column. To create Colunmstore Index:
CREATE TABLE [dbo].[Sales]
(
    [SaleID] [int] NOT NULL PRIMARY KEY,
    [Product] [nvarchar](50) NOT NULL,
    [SaleDate] [date] NOT NULL,
    [SaleAmount] [money] NOT NULL
)
WITH (MEMORY_OPTIMIZED = OFF, DURABILITY = ON)
GO

CREATE CLUSTERED COLUMNSTORE INDEX [CSI_Sales_Product]
ON [dbo].[Sales]([Product]);

Above query creates columnstore index on the Product column as a clustered index, which means that the entire table is stored in a columnar format. Now whenever we can query the Sales table, it will be much faster than a query on a traditional row-based index because the columnstore index is created on the Product column

In-Memory OLTP
In-Memory OLTP is a new feature in SQL Server that allows for creating memory-optimized tables. These tables are stored entirely in memory, which makes them much faster than traditional disk-based tables. In-Memory OLTP is beneficial for applications requiring high performance and low latency. For example, consider a table containing stock market data. In-Memory OLTP could create a memory-optimized table that stores the latest market data. This would allow for very fast querying of real-time stock prices. To create a memory-optimized filegroup, which will contain the memory-optimized tables we can use the below query:
ALTER DATABASE [MyDatabase] ADD FILEGROUP [InMemoryFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

It will add a new filegroup called "InMemoryFilegroup" to the "MyDatabase" database, which contains memory-optimized data. Now we will create a memory-optimized table that will be stored entirely in memory:
CREATE TABLE [dbo].[MarketData_MemoryOptimized]
(
    [Name] [nvarchar](50) NOT NULL PRIMARY KEY NONCLUSTERED,
    [Price] [decimal](18, 2) NOT NULL,
    [Timestamp] [datetime2](0) NOT NULL,
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

The "MarketData_MemoryOptimized" table is defined as MEMORY_OPTIMIZED, which means it will be stored entirely in memory, and DURABILITY is set to SCHEMA_ONLY, which means it won't be written to disk. In-Memory OLTP can be a powerful tool for storing and querying real-time data in memory-optimized tables. By storing data in memory, In-Memory OLTP can improve query performance and reduce latency for certain types of database workloads, such as real-time market data.

PolyBase
PolyBase is a feature in SQL Server that allows for integrating external data sources, such as Hadoop or Azure Blob Storage. PolyBase allows for querying structured and unstructured data, making it a powerful tool for working with big data.

For example, consider a large financial services company that stores customer data in Hadoop. The company may want to analyze customer behavior and trends to improve their services, but querying the large amount of data stored in Hadoop can be difficult and time-consuming. This is where PolyBase comes in - by connecting SQL Server directly to the Hadoop data source, the company can query the data easily and quickly, allowing for in-depth analysis of customer behavior and trends.
EXEC sp_configure 'polybase enabled', 1;
RECONFIGURE;

CREATE EXTERNAL DATA SOURCE HadoopDataSource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://<HadoopNameNode>:<PortNumber>',
    CREDENTIAL = HadoopCredential
);

CREATE EXTERNAL TABLE CustomerData_Hadoop
(
    CustomerID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    EmailAddress VARCHAR(50),
    Address VARCHAR(100),
    City VARCHAR(50),
    State VARCHAR(2),
    ZipCode VARCHAR(10)
)
WITH (
    LOCATION = '/customerdata',
    DATA_SOURCE = HadoopDataSource,
    FILE_FORMAT = TEXTFILE
);

The above code enables PolyBase in SQL Server, creates an external data source called HadoopDataSource that points to the Hadoop cluster at the location hdfs://<HadoopNameNode>:<PortNumber>., and creates an external table called CustomerData_Hadoop that maps to the data stored in Hadoop. The LOCATION option specifies the location of the data in Hadoop, and the DATA_SOURCE option specifies the external data source to use to query the data. The FILE_FORMAT option specifies the format of the data in Hadoop, in this case, TEXTFILE.

PolyBase allows us to query external data sources directly from SQL Server. This can be a valuable tool for organizations that have large amounts of data stored in external sources and need to query that data quickly and easily.

Conclusion
SQL Server offers a number of effective tools and methods for handling large amounts of data. There are numerous methods for maximizing the speed of big data sets, ranging from partitioning and columnstore indexes to In-Memory OLTP and PolyBase. In today's data-driven world, companies may remain ahead of the competition and obtain insightful knowledge by employing these strategies.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Don't worry if you're just getting started. I'll go over the fundamental SQL commands that every novice should know in this blog. These commands will provide you with a solid foundation regardless of whether you are developing a basic application or are just learning about SQL Server.

What is SQL?
SQL stands for Structured Query Language. It is a programming language for storing and processing information in a relational database.

With SQL, you can,

• Create and manage databases
• Add and retrieve data
• Update or delete records
• Control access to data

SQL Commands
1. DDL ( Data Definition Language )
What it does: DDL commands define and modify the structure of database objects like tables, schemas, or databases.

Common DDL Commands

• CREATE
• ALTER
• DROP
• TRUNCATE

Example

Note. DDL commands are auto-committed — once executed, you cannot roll them back.

2. DML – Data Manipulation Language

What it does: DML commands let you insert, update, or delete actual data inside your tables.

Common DML Commands

• INSERT
• UPDATE
• DELETE

Example

Note. Use WHERE carefully, forgetting that it can update or delete every row in the table.

3. DQL – Data Query Language
What it does: DQL is all about retrieving data from the database using queries.

Main DQL Command
SELECT

Example

This is the most-used category for anyone working with reports, dashboards, or APIs.

4. TCL – Transaction Control Language
What it does: TCL commands help manage transactions in SQL. These are useful when you want to ensure multiple operations succeed or fail together.

Common TCL Commands
    BEGIN TRANSACTION
    COMMIT
    ROLLBACK
    SAVEPOINT (optional/advanced)

Example

Best used when making multiple changes that must all succeed or fail together.
5. DCL – Data Control Language

What it does: DCL commands are about access control and permissions in the database.

Common DCL Commands
    GRANT
    REVOKE

Example
Data Control

Helpful for controlling individuals in settings where security is important, such production settings.

Conclusion
Understanding SQL command categories like DDL, DML, DQL, TCL, and DCL makes it much easier to work with databases. Whether you're creating tables, inserting data, running queries, or managing transactions, knowing which command to use and helps you write better and safer SQL.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Performance analysis and query optimization are crucial duties while working with SQL Server. To assist developers and DBAs in troubleshooting and monitoring SQL query performance, SQL Server Management Studio (SSMS) comes with a number of built-in tools. Client statistics are one such underappreciated yet effective instrument. 

In SSMS, what are client statistics?
In SSMS, client statistics give specific details about how a query operates from the standpoint of the client (SSMS), as opposed to the SQL Server engine. Client statistics provide information on the duration of each client-side step and aid in comparing different executions of the same query, whereas execution plans describe how the query is carried out.

How to Enable and Use Client Statistics in SSMS?

• Open SSMS and connect to your SQL Server.
• Open a New Query window.
• Click on the Query menu.
• Select Include Client Statistics (or press Shift + Alt + S).
• Run your SQL query.

You’ll notice a new tab named "Client Statistics" appears next to the "Results" and "Messages" tabs as above.
It will show multiple results where we can comapare persormance result as below. Note: Statistics will show up to 10 results. After that, it will add in 10th result as the latest.

Statistics
Key Metrics in Client Statistics

1. Query Profile Statistics

2. Time Statistics

3. Aggregate Totals
When you run the same query multiple times (e.g., for performance comparison), SSMS shows:

Why Use Client Statistics?
Benefits

• Helps in query optimization.
• Assists in identifying network delays.
• Aids in performance tuning during development or testing.
• Enables comparative analysis of different query versions.

Real-World Scenario Example
Suppose you're optimizing a stored procedure. You can,

• Enable client statistics.
• Run the stored procedure once with your original query.
• Modify your query (e.g., by adding an index or changing a join).
• Re-run and compare the Total execution time, Network packets, and Rows returned.

This helps you quantify improvements or spot regressions easily.

Limitations to Keep in Mind

• Client Statistics reflect client-side performance, not full server-side analysis.
• They don't replace Execution Plans or Dynamic Management Views (DMVs).
• Useful mainly during development or testing, not in production environments.

Conclusion
Client Statistics in SSMS are a simple yet powerful feature to understand query performance from the client perspective. Though often overlooked, they are valuable for developers and DBAs working on performance tuning and query optimization. Next time you run a SQL query in SSMS, give Client Statistics a try it might give you insights you weren't expecting!

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

SQL provides various numeric functions that help perform mathematical operations on numeric data. These functions are useful for calculations, rounding, and other numerical transformations.

Common Numeric Functions

• ABS(): Returns the absolute value of a number.
• CEILING(): Rounds a number up to the nearest integer.
• FLOOR(): Rounds a number down to the nearest integer.
• ROUND(): Rounds a number to a specified number of decimal places.
• POWER(): Returns the value of a number raised to a given power.
• SQRT(): Returns the square root of a number.
• EXP(): Returns the exponential value of a number.
• LOG(): Returns the natural logarithm of a number.
• LOG10(): Returns the base-10 logarithm of a number.
• RAND(): Returns a random float value between 0 and 1.
• SIGN(): Returns the sign of a number (-1, 0, or 1).
• PI(): Returns the value of PI (3.14159265358979).
• DEGREES(): Converts radians to degrees.
• RADIANS(): Converts degrees to radians.
• MOD(): Returns the remainder of a division.
• TRUNCATE(): Truncates a number to a specified decimal place.

Example Usage of Numeric Functions
1. Using ABS() Function
SELECT ABS(-15) AS AbsoluteValue;

2. Using CEILING() and FLOOR() Functions
SELECT CEILING(4.3) AS CeilValue, FLOOR(4.7) AS FloorValue;

Output

3. Using ROUND() and TRUNCATE() Functions
SELECT ROUND(123.456, 2) AS RoundedValue, TRUNCATE(123.456, 2) AS TruncatedValue;

Output

4. Using POWER() and SQRT() Functions
SELECT POWER(5, 3) AS PowerValue, SQRT(25) AS SquareRoot;

Output

5. Using MOD() Function
SELECT MOD(10, 3) AS ModResult;

6. Using PI(), DEGREES(), and RADIANS() Functions
SELECT
    PI() AS PiValue,
    DEGREES(PI()) AS DegreesValue,
    RADIANS(180) AS RadiansValue;

Output

When to Use Numeric Functions?

• Financial Calculations: Useful for interest rates, tax calculations, and rounding amounts.
• Data Analysis: Helps in statistical computations and mathematical transformations.
• Scientific Computing: Essential for performing complex mathematical calculations.
• Random Value Generation: Used for sampling, simulations, and random selections.

Advantages of Numeric Functions

• Simplifies mathematical computations in SQL.
• Enhances query efficiency by using built-in SQL functions.
• Provides precise and accurate results for calculations.

Numeric functions play a crucial role in SQL for performing various mathematical operations.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Using ROW_NUMBER() and PARTITION BY (Preferred Approach)
The ROW_NUMBER() function assigns a unique row number to each record within a partition (group). We can use this to identify and delete duplicates while keeping only the required data.

Query Syntax
WITH CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS row_num
    FROM table_name
    WHERE condition  -- Apply filtering condition here
)
DELETE FROM CTE
WHERE row_num > 1;

Example
Consider a Customer table with duplicate entries based on Email.

Removing Duplicates While Keeping the First Entry.

;WITH CTE AS (
    SELECT ID
    FROM (
        SELECT ID,
               ROW_NUMBER() OVER (PARTITION BY NAME, Email, City ORDER BY ID) AS RN
        FROM Customers
        WHERE City = 'NY'  -- Only NY state filtering condition
    ) AS sub
    WHERE RN > 1
)
DELETE FROM Customers
WHERE ID IN (SELECT ID FROM CTE);

Explanation of the Query
Identifies Duplicates

• ROW_NUMBER() OVER (PARTITION BY Name, Email, City ORDER BY ID)
• Assign a row number (RN) for each duplicate group, keeping the first record (RN = 1).

Filters Out Duplicates (RN > 1): Only marks duplicate records where City = 'NY'.
Deletes Duplicate Records: Deletes only IDs from the CTE that have RN > 1
This syntax will be useful when we are joining more tables and deleting duplicates from one specific table.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

I recently worked on a project to assess the schema of a third-party vendor. Our organization has an internal support ticket tracking tool. The program used a SQL database, and after calculating its cost, we opted not to extend the contract. The objective was to create an in-house platform for managing internal support tickets.

My responsibility was to review the schema of the internal support database. We couldn't figure out what data was in which table because the structure was intricate and the table names were tough. Eventually, I was able to determine the relationship between tables and what data was contained in each. I've also given the columns proper names so that we can quickly identify what data is contained in which column. I used the sp_rename method to rename tables.

This article explains how to rename a column using the sp_rename stored procedure. I also demonstrated how to rename a column in SQL Server Management Studio. First, let us look at the fundamentals of renaming a column.

The Basics of Renaming Columns
Renaming a table's column is a simple task. We can use a system-stored process called sp_rename. Additionally, we can utilize SQL Server Management Studio to rename a column. The sp_rename stored procedure can rename the following:

• Database objects like tables, Stored procedures, and functions.
• Indexes and statistics
• User-defined datatypes.

In this article, we will learn how to rename any column of an SQL Server table using the sp_rename stored procedure.

How can you rename a column in SQL Server?
In SQL Server, we may rename any column or object using the sp_rename stored procedure. In this post, we'll look at how to rename columns using the sp_rename function.

The syntax for the sp_rename stored procedure is as follows.

Exec sp_rename 'original_schema_name.original_table_name.original_column_name', 'new_column_name' ,'object_type'

In the syntax

• original_schema_name.original_table_name.original_column_name: Specify the table name whose column you want to rename. If you are renaming a column of a table that exists in the user-defined schema, you must specify the table name in three three-part names.
• new_column_name: Specify the new name of the column.
• object_type: Specify the object type.

Let us understand the process with simple examples. Suppose you want to rename a column of the patient table. The original column name is Address, and we want to change it to patient_address. The sp_rename command to rename the column is as follows.

USE HospitalManagementSystem
GO
EXEC sys.sp_rename 'patients.address','patient_address','COLUMN'

Once the column is renamed, let us verify that the column has been renamed successfully. You can run the below query to view the columns of the patient table.
use HospitalManagementSystem
go
select table_name,column_name from information_schema.columns where table_name='Patients'

Output

As you can see in the above image, the column Address has been changed to patient_address.

Let us take another example. Suppose you want to rename the column of Sales. invoice table which exists in the Wideworldimportors database. The current name of the column is InvoiceDate, and the new name will be InvoiceCreateDate. The query to rename the column is the following.
EXEC sys.sp_rename 'Sales.Invoices.InvoiceDate','InvoiceCreateDate','COLUMN'

Here you can see, that we are changing the column name of the invoice table which is in the Sales schema. Therefore, we have used three-part naming. Once the column is renamed, execute the following T-SQL query to verify that the column has been renamed.
select table_name,column_name from information_schema.columns where table_name='Invoices'

Output

Renaming other objects in SQL Server
The sp_rename stored procedure can be used to rename other database objects, such as indexes, constraints, and stored procedures. The syntax of the sp_rename operation stays unchanged. The object_type argument for the sp_rename column will change. Let us consider a basic example.

Assume we wish to rename the index of the sales invoice table. The index's present name is 'IX_Sales_Invoices_ConfirmedDeliveryTime', which we would like to modify to 'IX_Sales_Invoices_ConfirmedDeliveryTime_New'. In the query, the object_type argument in the sp_rename method will be set to INDEX. The query to rename the index is shown below.

EXEC sys.sp_rename 'Sales.Invoices.IX_Sales_Invoices_ConfirmedDeliveryTime','IX_Sales_Invoices_ConfirmedDeliveryTime_New','INDEX'

Once the index is renamed, you can query sys. indexes dynamic management view to verify that the index has been renamed successfully. Note that whenever we rename any index, the statistics associated with the index will be renamed as well. Here is the query to verify both changes.
SELECT object_name(object_id)[TableName], name [IndexName], Type_desc [Index Type]
FROM sys.indexes where object_id=object_id('Sales.Invoices')

Output

Using SQL Server Management Studio to Rename
We can use SQL Server Management Studio to rename the database object. In the first section, we learnt how to rename columns and indexes using the sp_rename stored procedure.

In this example, we'll see how to rename a constraint in SQL Server Management Studio. For demonstration, I'll rename the constraint in the Sales.invoice table. The present constraint name is DF_Sales_Invoices_InvoiceID, which we will rename to Default_Sales_Invoices_InvoiceID. As the name implies, this constraint is a default constraint.

First, launch SQL Server Management Studio and connect to your database server. Expand databases. Expand the Wideworldimportors database.

A database contains many tables. Expand the Sales, Invoice, and Constraint tables. Press F2 or right-click on DF_Sales_Invoices_InvoiceID and choose Rename.

The name will be editable. Change the name to Default_Sales_Invoices_InvoiceID and hit enter. The name will be changed. The SQL Server management studio prompts a confirmation message that looks like the following image.

Click OK to change the name. Once changes are made, execute the following T-SQL query to verify that the constraint has been renamed successfully.
SELECT
  [constraint].name AS constraint_name,
  OBJECT_NAME([constraint].parent_object_id) AS table_name,
  [column].name AS column_name from
  sys.default_constraints [constraint]
JOIN
  sys.columns [column] ON [constraint].parent_object_id = [column].object_id
    AND [constraint].parent_column_id = [column].column_id
    where  OBJECT_NAME([constraint].parent_object_id)='Invoices'

Output

Let us take a look at some limitations and things to be considered before renaming any column.

Limitations and Considerations
If you are renaming any column in a table or renaming any object in a database, you must consider the following limitations and possible issues that might break the application.

• ALTER permission is needed on the object that you want to rename. Suppose you want to rename a column name; you must have ALTER object permission on the table whose column you are renaming.
• Renaming a column name always breaks the stored procedure or other objects (View, function, etc.) that are referencing that column. For example, you are renaming a column that is being used in a view. Therefore, make sure you modify all the stored procedures, functions, and triggers that reference the column that was renamed. You can use sys.sql_expression_dependencies to find all dependencies of the column.
• When you rename a stored procedure, the object's name in sys.sql_modules will not change. Hence Microsoft recommends dropping and recreating an object instead of just renaming it.
• When you rename a column of a table that is part of replication, the replication might break so if we want to rename the column of the replicated table, first, we must pause the replication, then rename the column using sp_rename or SQL Server management studio, update all database objects that are referencing the column, and finally, reinitialize replication with the new snapshot.

Conclusion
In this tutorial, we learned how to rename any column in a table. I demonstrated how to rename a column using a system-stored process called sp_rename, complete with syntax and easy examples. We also learned how to rename a column using SQL Server Management Studio. We can also use other tools, such as dbForge Studio for SQL Server, to run the stored procedure to rename a column. We also reviewed the limits and other difficulties that must be addressed before to renaming any object or column.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.