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.

One easy method to see if the given MySQL table has any rows is to run "SELECT 1 FROM TABLE". Rather than extracting any information from the table, it provides a result set that has a single column with the value 1 for each row that meets the requirements in the WHERE clause (if any).

Now let's look at an example.

First Step
I am going to make an Employee table.
CREATE TABLE Employee
(
employeeId INT IDENTITY(1,1) PRIMARY KEY,
employeeName VARCHAR(50)
)

Then Inserting Some values in that Employee table.
INSERT INTO Employee (employeeName)
VALUES('Johnson'),('Richard'), ('Willam'),('John')

After inserting the value, the result will be as follows:

Step 2
Execute the following Query without the WHERE Clause.
SELECT 1 FROM Employee

Output Message: (4 rows affected)

Result

Now Exceute with WHERE Clause.
SELECT 1 FROM Employee WHERE employeeName LIKE 'John%'

Output Message: (2 rows affected)

Result

I hope this blog has helped you to better grasp how to use SQL Server's SELECT 1 From the table function. Wishing you luck.

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.