🧠 Difference Between Functions, Stored Procedures, and Views in SQL Server

 📘 Introduction

In SQL Server, Functions, Stored Procedures, and Views are three powerful components that simplify database programming and improve performance.
Although they look similar in some ways, their purpose, behavior, and execution are quite different.

Let’s understand each in detail with real-world examples and learn when to use them.

---

🔹 What is a Function in SQL Server?

A Function in SQL Server is a reusable block of code that performs a specific task and returns a value. Functions are mainly used for calculations, data formatting, and reusable logic inside queries.

✅ Key Features:

• Must return a value (scalar or table).

• Cannot perform INSERT, UPDATE, or DELETE operations on database tables.

• Can be called inside a SELECT statement.

• Cannot use transactions.

🧩 Types of Functions:

1. Scalar Function: Returns a single value.

2. Table-Valued Function: Returns a table.

💡 Real-Time Example:

Suppose your e-commerce database needs to calculate the total price including tax for each product.

CREATE FUNCTION dbo.fn_GetTotalPrice(@Price DECIMAL(10,2), @Tax DECIMAL(5,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Price + (@Price * @Tax / 100)
END;

Now, you can call this function inside a query:

SELECT ProductName, dbo.fn_GetTotalPrice(Price, 18) AS TotalPrice
FROM Products;

👉 Use Case: Best used for reusable calculations and logic that needs to return a value in queries.

---

🔹 What is a Stored Procedure in SQL Server?

A Stored Procedure is a precompiled collection of SQL statements that perform one or more database operations.
It can insert, update, delete, or retrieve data, and also handle complex business logic.

✅ Key Features:

• Can return multiple result sets.

• Supports transactions and error handling.

• Can modify data in tables.

• Can accept input, output, and return parameters.

• Improves performance due to precompilation and execution plan reuse.

💡 Real-Time Example:

Let’s say you want to add a new customer into your CRM database.

CREATE PROCEDURE dbo.sp_AddCustomer
    @CustomerName NVARCHAR(100),
    @Email NVARCHAR(100),
    @City NVARCHAR(50)
AS
BEGIN
    INSERT INTO Customers (CustomerName, Email, City)
    VALUES (@CustomerName, @Email, @City);

    SELECT 'Customer added successfully' AS Message;
END;

Execute the stored procedure:

EXEC dbo.sp_AddCustomer 'John Doe', 'john@example.com', 'Hyderabad';

👉 Use Case: Best for performing multiple DML operations (Insert, Update, Delete) or complex business transactions.

---

🔹 What is a View in SQL Server?

A View is a virtual table that displays data from one or more tables through a SQL query.
It doesn’t store data physically — it simply saves a SQL query for easy reuse.

✅ Key Features:

• Simplifies complex joins and queries.

• Provides data security by restricting access to specific columns or rows.

• Can be updated if it’s based on a single table.

• Improves data abstraction.

💡 Real-Time Example:

Suppose you have a Sales table and a Customer table. You can create a View to easily get the Customer Sales Summary.

CREATE VIEW vw_CustomerSales AS
SELECT 
    c.CustomerName,
    SUM(s.TotalAmount) AS TotalSales
FROM Customers c
JOIN Sales s ON c.CustomerID = s.CustomerID
GROUP BY c.CustomerName;

Now, just query the View like a table:

SELECT * FROM vw_CustomerSales;

👉 Use Case: Best for reporting, data analysis, and abstracting complex joins.

---

⚖️ Comparison Table: Functions vs Stored Procedures vs Views

Feature	Function	Stored Procedure	View
Returns Value	Yes (Scalar/Table)	Optional	Acts as a virtual table
Can Modify Data	❌ No	✅ Yes	Limited (if single table)
Can Use Transactions	❌ No	✅ Yes	❌ No
Used Inside SELECT	✅ Yes	❌ No	✅ Yes
Performance	High for calculations	High for large operations	Medium
Reusability	High	High	High
Compilation	Compiled each time	Precompiled	Not compiled (query-based)

---

🧠 Real-World Scenario: E-Commerce Example

Requirement	Best Choice	Reason
Calculate product discount	Function	Returns computed value
Insert new order & update stock	Stored Procedure	Performs multiple DML operations
Generate monthly sales report	View	Simplifies query for reporting tools

---

🚀 Best Practices

1. Use Functions for small, reusable logic that doesn’t modify data.

2. Use Stored Procedures for complex business workflows.

3. Use Views to simplify data access and improve query readability.

4. Avoid heavy logic inside Views — use Indexed Views for performance if needed.

5. Combine all three effectively for a clean database architecture.

---

🏁 Conclusion

Understanding the differences between Functions, Stored Procedures, and Views in SQL Server helps developers design efficient, modular, and maintainable databases.
Each component has its strengths — use them wisely to balance performance, reusability, and security.

How IoT Works Using .NET Core Web API and SQL Server – Step by Step Guide

 Introduction to IoT (Internet of Things)

The Internet of Things (IoT) is a revolutionary technology that connects physical devices to the internet, allowing them to collect, transmit, and receive data. IoT enables automation, monitoring, and smarter decision-making in smart homes, healthcare, industrial automation, and more.

In this article, we will explore how IoT works and demonstrate a practical example using .NET Core Web API and SQL Server, which allows developers to build scalable IoT applications.

---

How IoT Works – Step by Step

IoT operates through a series of connected components:

1. Devices and Sensors

These are physical devices that monitor or measure conditions like temperature, humidity, motion, or light.

Example: A temperature sensor in a smart home detects 35°C in a room.

2. Connectivity

Devices transmit data to a server or cloud using protocols such as HTTP, MQTT, or CoAP. Wi-Fi, 4G/5G, and LoRaWAN are common connectivity options.

3. Data Storage and Processing

Servers, such as a .NET Core Web API connected to SQL Server, store and process the data. Analytics or automated actions can be performed based on this data.

4. Visualization and Action

The processed data is presented to users through web dashboards, mobile apps, or alerts. Actions can also be automated, such as switching on a fan if temperature exceeds a threshold.

---

IoT Architecture Example Using .NET Core and SQL Server

Here’s a simple smart home temperature monitoring system:

[Temperature Sensor] --> [IoT Device / Raspberry Pi] --> [Internet] 
    --> [ASP.NET Core Web API] --> [SQL Server Database] 
    --> [Web Dashboard / Mobile App]

Components:

• Sensor: Collects temperature data.

• IoT Device: Sends data to API.

• Web API: Receives data and stores in SQL Server.

• Database: Saves all readings for analytics.

• Dashboard: Visualizes real-time data and triggers alerts.

---

Step 1: Setting Up SQL Server Database

Create a database named IoTDB with a table to store temperature readings:

CREATE TABLE TemperatureReadings (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    SensorId NVARCHAR(50),
    Temperature FLOAT,
    ReadingTime DATETIME DEFAULT GETDATE()
);

---

Step 2: Creating .NET Core Web API Project

dotnet new webapi -n IoTWebApi
cd IoTWebApi
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

---

Step 3: Define Entity and DbContext

public class TemperatureReading
{
    public int Id { get; set; }
    public string SensorId { get; set; }
    public double Temperature { get; set; }
    public DateTime ReadingTime { get; set; } = DateTime.Now;
}

using Microsoft.EntityFrameworkCore;

public class IoTDbContext : DbContext
{
    public IoTDbContext(DbContextOptions<IoTDbContext> options) : base(options) { }
    public DbSet<TemperatureReading> TemperatureReadings { get; set; }
}

---

Step 4: Configure Database Connection

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=YOUR_SERVER_NAME;Database=IoTDB;Trusted_Connection=True;"
  },
  "AllowedHosts": "*"
}

Program.cs

builder.Services.AddDbContext<IoTDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
builder.Services.AddControllers();

---

Step 5: Create Temperature API Controller

[ApiController]
[Route("api/[controller]")]
public class TemperatureController : ControllerBase
{
    private readonly IoTDbContext _context;
    public TemperatureController(IoTDbContext context) => _context = context;

    [HttpPost]
    public async Task<IActionResult> PostReading([FromBody] TemperatureReading reading)
    {
        _context.TemperatureReadings.Add(reading);
        await _context.SaveChangesAsync();
        return Ok(reading);
    }

    [HttpGet]
    public async Task<IActionResult> GetReadings()
    {
        var readings = await _context.TemperatureReadings
            .OrderByDescending(r => r.ReadingTime)
            .ToListAsync();
        return Ok(readings);
    }
}

---

Step 6: Simulate IoT Sensor Data

POST Example (JSON)

{
  "SensorId": "Room1",
  "Temperature": 35.5
}

• This request stores the temperature reading in SQL Server.

• GET api/temperature retrieves all readings for analytics and dashboard display.

---

Step 7: Visualizing Data

• Use Angular, React, or Blazor to consume the API.

• Display real-time graphs and trigger alerts if the temperature exceeds thresholds.

---

Benefits of This IoT Implementation

1. Real-Time Monitoring: Track temperature changes instantly.

2. Automation: Trigger devices or alerts automatically.

3. Data Analytics: Store historical data for insights.

4. Scalable: Multiple sensors and rooms can be integrated easily.

---

Conclusion

IoT bridges the physical world with the digital world. By combining .NET Core Web API and SQL Server, developers can create scalable, efficient, and real-time IoT systems for smart homes, industries, and more. With proper dashboards and analytics, businesses and users can make informed decisions and improve automation.