Relational databases are accessed using SQL (Structured Query Language). It facilitates data storage, retrieval, updating, and deletion. Almost every business uses SQL, including banking systems and internet. The ability to use SQL is essential if you wish to work with data. For the most crucial SQL topics, this cheat sheet will provide you with concise and understandable definitions, code examples, and essential points.

1. SELECT Statement
Definition: Used to fetch data from a table.

Example
SELECT name, age FROM Students;

Key Point: Use * to select all columns.

2. WHERE Clause
Definition: Filters records based on a condition.

Example
SELECT * FROM Students WHERE age > 18;

Key Point: Works with operators like =, >, <, BETWEEN, IN, LIKE.

3. ORDER BY
Definition: Sorts the result in ascending or descending order.

Example
SELECT * FROM Students ORDER BY age DESC;

Key Point: Default sort is ascending (ASC).

4. INSERT INTO
Definition: Adds new records to a table.

Example
INSERT INTO Students (name, age) VALUES ('John', 20);

Key Point: Match the column order with the values.

5. UPDATE
Definition: Changes existing records.

Example
UPDATE Students SET age = 21 WHERE name = 'John';

Key Point: Always use WHERE to avoid updating all rows.

6. DELETE
Definition: Removes data from a table.

Example
DELETE FROM Students WHERE name = 'John';

Key Point: Without WHERE, all rows will be deleted.

7. CREATE TABLE
Definition: Creates a new table.
Example
CREATE TABLE Students (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

Key Point: Define data types clearly.

8. ALTER TABLE
Definition: Changes the structure of a table.
Examples
    Add column:
    ALTER TABLE Students ADD email VARCHAR(100);

Drop column:
ALTER TABLE Students DROP COLUMN email;

9. DROP TABLE
Definition: Deletes the table and its data.

Example
DROP TABLE Students;

Key Point: This action cannot be undone.

10. JOINs
Definition: Combines rows from two or more tables.

Example (INNER JOIN)
SELECT Students.name, Marks.score
FROM Students
INNER JOIN Marks ON Students.id = Marks.student_id;

Types
    INNER JOIN: Only matching records.
    LEFT JOIN: All records from left + matches.
    RIGHT JOIN: All from right + matches.
    FULL JOIN: All records from both.

11. GROUP BY
Definition: Groups rows based on a column and applies an aggregate function.

Example:
SELECT age, COUNT(*) FROM Students GROUP BY age;

Key Point: Always use with aggregate functions.

12. HAVING
Definition: Filters groups created by GROUP BY.

Example:
SELECT age, COUNT(*)
FROM Students
GROUP BY age
HAVING COUNT(*) > 1;

Key Point: Use HAVING after GROUP BY.

13. Aggregate Functions
Definition: Perform calculations on multiple values.
Common ones: COUNT(), SUM(), AVG(), MIN(), MAX()

Example
SELECT AVG(age) FROM Students;

14. DISTINCT
Definition: Removes duplicate rows.

Example
SELECT DISTINCT age FROM Students;

15. BETWEEN, IN, LIKE
BETWEEN

SELECT * FROM Students WHERE age BETWEEN 18 AND 25;

IN
SELECT * FROM Students WHERE age IN (18, 21, 23);

LIKE
SELECT * FROM Students WHERE name LIKE 'A%';

16. LIMIT / TOP
Definition: Restricts number of rows returned.
SELECT * FROM Students LIMIT 5;

SELECT TOP 5 * FROM Students;

17. UNION
Definition: Combines results of two SELECT statements.

Example
SELECT name FROM Students
UNION
SELECT name FROM Teachers;

Key Point: Both queries must have the same number of columns.

18. Subquery
Definition: A query inside another query.

Example
SELECT name FROM Students
WHERE age = (SELECT MAX(age) FROM Students);

19. Views
Definition: A virtual table based on the result of a query.

Example
CREATE VIEW Teenagers AS
SELECT * FROM Students WHERE age BETWEEN 13 AND 19;

20. Indexes
Definition: Improve search performance on columns.

Example
CREATE INDEX idx_name ON Students(name);

Key Point: Indexes speed up SELECT but may slow down INSERT/UPDATE.

21. Constraints
Definition: Rules applied on columns.

Types
    PRIMARY KEY: Unique and not null.
    FOREIGN KEY: Links to another table’s primary key.
    UNIQUE: Ensures all values are different.
    NOT NULL: Prevents null values.
    CHECK: Validates a condition.

Example
CREATE TABLE Students (
  id INT PRIMARY KEY,
  age INT CHECK (age >= 0)
);

22. DEFAULT Constraint
Definition: Sets a default value for a column if none is provided.

Example
CREATE TABLE Students (
  id INT,
  name VARCHAR(50),
  status VARCHAR(10) DEFAULT 'active'
);

Key Point: Helps avoid NULL when no value is provided.

23. CASE Statement
Definition: Adds conditional logic inside a query.

Example
SELECT name,
       CASE
         WHEN age >= 18 THEN 'Adult'
         ELSE 'Minor'
       END AS age_group
FROM Students;

Key Point: Works like if-else in SQL.

24. EXISTS
Definition: Checks whether a subquery returns any row.

Example
SELECT name FROM Students s
WHERE EXISTS (
  SELECT 1 FROM Marks m WHERE m.student_id = s.id
);

Key Point: Faster than IN in many cases for large datasets.

25. NOT IN vs NOT EXISTS
Caution: NOT IN fails if any NULL exists in subquery. Use NOT EXISTS for safer logic.

Example
SELECT name FROM Students
WHERE NOT EXISTS (
  SELECT 1 FROM Graduates WHERE Students.id = Graduates.id
);

26. COALESCE and ISNULL
Definition: Replace NULL with a default value.

Example
SELECT name, COALESCE(email, 'Not Provided') FROM Students;

Key Point: COALESCE returns the first non-null value from a list.

27. Window Functions
Definition: Performs calculation across a set of rows related to the current row.
Common ones: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

Example
SELECT name, age,
       RANK() OVER (ORDER BY age DESC) AS age_rank
FROM Students;

Key Point: Unlike aggregate functions, window functions do not collapse rows.

28. Common Table Expressions (CTE)
Definition: A temporary named result set used in a query.

Example
WITH Teenagers AS (
  SELECT * FROM Students WHERE age BETWEEN 13 AND 19
)
SELECT * FROM Teenagers;

Key Point: Makes complex queries cleaner and readable.

29. Stored Procedures
Definition: A saved group of SQL statements that can be executed repeatedly.

Example
CREATE PROCEDURE GetAllStudents
AS
BEGIN
  SELECT * FROM Students;
END;

Execution
EXEC GetAllStudents;

30. Functions (User-Defined Functions)
Definition: Return a value based on input parameters.

Example
CREATE FUNCTION GetFullName (@first VARCHAR(50), @last VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
  RETURN (@first + ' ' + @last);
END;

31. Transactions
Definition: A set of SQL operations that execute together or not at all.

Example
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Key Point: Use ROLLBACK to undo changes if something fails.

32. ACID Properties
Definition: Ensures reliable processing of transactions.

• Atomicity: All or nothing
• Consistency: Valid state before/after
• Isolation: Transactions do not affect each other
• Durability: Changes are permanent

33. Normalization

• Definition: Organizing data to reduce redundancy.
• Common Forms: 1NF, 2NF, 3NF
• Key Point: Normalize to avoid data anomalies. Denormalize only when performance requires it.

34. Foreign Key
Definition: A column that links to another table’s primary key.

Example
CREATE TABLE Marks (
  id INT PRIMARY KEY,
  student_id INT,
  FOREIGN KEY (student_id) REFERENCES Students(id)
);

35. Triggers
Definition: SQL code that runs automatically on data change (INSERT, UPDATE, DELETE).

Example
CREATE TRIGGER LogDelete
AFTER DELETE ON Students
FOR EACH ROW
BEGIN
  INSERT INTO DeletedRecords (name) VALUES (OLD.name);
END;

36. SET Operators (UNION ALL, INTERSECT, EXCEPT)
UNION ALL: Includes duplicates.
INTERSECT: Returns common rows between two queries.
EXCEPT: Returns rows in first query but not in second.

Example
SELECT name FROM Students
INTERSECT
SELECT name FROM Teachers;

37. Backup and Restore
Backup Example (SQL Server)
BACKUP DATABASE School TO DISK = 'D:\backup\school.bak';

Restore Example
RESTORE DATABASE School FROM DISK = 'D:\backup\school.bak';

38. TEMPORARY Tables
Definition: Temporary tables exist only during the session or transaction.

Example (MySQL)
CREATE TEMPORARY TABLE TempStudents (
  id INT,
  name VARCHAR(50)
);

Key Point: Good for storing intermediate results. Automatically dropped at session end.

39. TABLE Variables (SQL Server)
Definition: A variable to temporarily hold table data.

Example
DECLARE @Temp TABLE (id INT, name VARCHAR(50));
INSERT INTO @Temp VALUES (1, 'John');

40. MERGE Statement (UPSERT)
Definition: Performs INSERT, UPDATE, or DELETE in one statement based on a match.

Example
MERGE INTO Students AS target
USING (SELECT 1 AS id, 'John' AS name) AS source
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (source.id, source.name);

Key Point: Use cautiously. Complex logic can lead to bugs if not tested well.

41. Recursive CTE
Definition: A CTE that refers to itself. Useful for hierarchical data (e.g., org charts, folder trees).

Example
WITH OrgChart AS (
  SELECT id, manager_id, name FROM Employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM Employees e
  JOIN OrgChart o ON e.manager_id = o.id
)
SELECT * FROM OrgChart;

42. JSON Data Handling
Definition: Querying and storing JSON in SQL columns (supported in PostgreSQL, MySQL, SQL Server).

Example (PostgreSQL)
SELECT data->>'name' AS name FROM users WHERE data->>'age' = '25';

Key Point: Useful when data structure is flexible or semi-structured.

43. PIVOT and UNPIVOT

Definition
    PIVOT: Converts rows to columns.
    UNPIVOT: Converts columns to rows.

Example (SQL Server):
SELECT * FROM
(SELECT subject, score FROM Marks) AS SourceTable
PIVOT (
  MAX(score) FOR subject IN ([Math], [Science])
) AS PivotTable;

Key Point: Makes reporting easier. Avoid overuse in core logic.

44. ROLLUP and CUBE
Definition: Extensions to GROUP BY for creating subtotals and grand totals.

ROLLUP
SELECT department, role, COUNT(*)
FROM Employees
GROUP BY ROLLUP (department, role);

CUBE
GROUP BY CUBE (department, role);

Key Point: Saves time when generating hierarchical reports.

45. WITH TIES
Definition: Returns additional rows that match the last value in an ORDER BY ... TOP query.

Example:
SELECT TOP 3 WITH TIES name, score
FROM Marks
ORDER BY score DESC;

Key Point: Useful when ranks are tied and you want to return all top scorers.

46. SEQUENCES
Definition: Auto-incrementing number generator independent of tables.

Example:
CREATE SEQUENCE student_seq START WITH 1 INCREMENT BY 1;
SELECT NEXT VALUE FOR student_seq;

Key Point: More flexible than IDENTITY in some databases.

47. IDENTITY vs SEQUENCE
IDENTITY: Tied to a table. Auto-increments per insert.
SEQUENCE: Independent object. Can be reused across tables or manually advanced.

48. LOCKING & ISOLATION LEVELS
Definition: Controls how transactions read/write shared data.

Levels

• READ UNCOMMITTED: Dirty reads allowed
• READ COMMITTED: Only committed data
• REPEATABLE READ: Prevents non-repeatable reads
• SERIALIZABLE: Strictest, full isolation

Key Point: Choose based on consistency vs performance trade-off.

49. DEADLOCK

• Definition: Two or more sessions wait forever for resources locked by each other.
• Fix: Reduce transaction size, always lock objects in the same order, use proper isolation levels.

50. EXECUTION PLAN

• Definition: Visual or textual explanation of how SQL Server/MySQL/PostgreSQL will execute the query.
• Key Point: Use EXPLAIN, SHOW PLAN, or right-click → “Display Estimated Execution Plan” in SQL Server Management Studio.

51. ANALYZE / UPDATE STATISTICS
Definition: Refreshes metadata so the query planner makes better decisions.

Example (PostgreSQL):
ANALYZE Students;

SQL Server:
UPDATE STATISTICS Students;

52. PARTITIONING

• Definition: Divides large tables into smaller, more manageable parts (partitions).
• Key Point: Boosts performance on very large tables. Query optimizer uses partition elimination.

53. Sharding

• Definition: Horizontal partitioning across databases or servers.
• Key Point: Needed for very high scale. Not supported by default in many RDBMS — requires custom implementation or external tools.

54. Temporal Tables (System-Versioned Tables)
Definition: Track historical changes automatically.

Example (SQL Server 2016+):
CREATE TABLE StudentsHistory (
  id INT,
  name VARCHAR(50),
  VALID_FROM DATETIME2 GENERATED ALWAYS AS ROW START,
  VALID_TO DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (VALID_FROM, VALID_TO)
) WITH (SYSTEM_VERSIONING = ON);

HostForLIFEASP.NET SQL Server 2022 Hosting

The Query Store: What is it?
With SQL Server 2016, a performance monitoring tool called the Query Store was added. It records query execution history, query plans, and performance metrics over time, much like a black box recorder. Because Query Store retains historical data, it is simpler to troubleshoot performance issues that have already occurred, in contrast to standard DMVs that only provide live statistics.

Why Use Query Store?

• Monitor how query performance changes over time.
• Detect and troubleshoot query regressions (when performance drops).
• Compare performance before and after deployments.
• Identify and force optimal plans for queries.
• Track resource usage (CPU, IO, duration) per query.

How to Enable Query Store?
Option 1. T-SQL
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE = ON;

Option 2. SSMS GUI

• Right-click the database → Properties
• Navigate to the Query Store page
• Set Operation Mode (Requested) to Read Write
• Adjust other settings like capture mode and retention as needed

What Data does Query Store Capture?

Internal Views for Query Store Analysis

• sys.query_store_query: Stores each normalized query
• sys.query_store_plan: Stores query execution plans
• sys.query_store_runtime_stats: Stores execution metrics
• sys.query_store_wait_stats: Captures wait types per query (SQL 2017+)

Built-in SSMS Reports
SSMS provides graphical reports for easier analysis.

• Right-click the database
• Select Reports → Standard Reports → Query Store
• Explore reports like.
  • Top Resource Consuming Queries
  • Tracked Queries
  • Query Plan Changes
  • Regressed Queries

Plan Forcing Example
If SQL Server chooses a suboptimal plan, you can force a better one.

Steps to Force a Plan via T-SQL
EXEC sp_query_store_force_plan
    @query_id = 102,
    @plan_id = 301;

To Unforce a Plan
EXEC sp_query_store_unforce_plan
    @query_id = 102,
    @plan_id = 301;

Note: Forced plans remain in use until manually unforced or if the plan becomes invalid due to schema changes.

How to Clean Query Store?
Remove Specific Plan or Query
EXEC sp_query_store_remove_plan @plan_id = 123;
EXEC sp_query_store_remove_query @query_id = 456;

Clear All Data from Query Store
ALTER DATABASE [YourDatabaseName]
SET QUERY_STORE CLEAR;

Query Store Modes

Limitations and Considerations

• Slight overhead on heavily loaded systems (~1–5%)
• Disk space usage can grow—monitor data size regularly
• Not available in SQL Server 2014 or earlier
• Retention settings control how long historical data is kept

Conclusion
The Query Store is a powerful feature for any SQL Server environment focused on performance, stability, and transparency. It simplifies identifying performance problems and helps maintain consistent performance by allowing query plan control. Use it proactively in development, staging, and production environments to avoid surprises and ensure optimal performance.

HostForLIFEASP.NET SQL Server 2022 Hosting

Specifically, we will leverage data warehouse ideas, SQL methodologies, and BI-focused schema architecture to design and implement a Data Mart in this article.

Step 1. Understand Business Requirements for the Data Warehouse

• Engage business stakeholders to define reporting needs.
• Identify key facts (e.g., total sales, loan balance) and dimensions (e.g., product, branch, customer).
• Define the grain of the data mart (e.g., "daily branch sales").

Step 2. Design the Data Warehouse Schema (Dimensional Modeling)
Use a Star Schema or Snowflake Schema, optimized for SQL queries.

• Fact Table (e.g., Fact_Sales): Contains numeric metrics and foreign keys
• Dimension Tables (e.g., Dim_Customer, Dim_Product): Contain descriptive attributes to slice and filter data

Step 3. Source to Target Mapping (STM)

• Identify source systems (e.g., OLTP DBs like Core Banking, ERP).
• Document field-level mappings from source to warehouse tables.
• Define SQL-based transformations (e.g., date format conversion, category lookup).

Step 4. Build the ETL in SQL or ELT with dbt
Using SQL or dbt (Data Build Tool).

• Extract: Pull raw data from staging tables.
• Transform: Use SQL CTEs or dbt models to clean and join.
• Load: Insert transformed data into fact/dimension tables.

Tips

• Use SCD Type 2 for dimensions
• Maintain surrogate keys
• Use indexes and partitions for performance

Step 5. Connect to BI Tools

• Expose the SQL-based data mart to Power BI, Tableau, or Looker.
• Use pre-aggregated views for performance.
• Provide semantic consistency by using dimensional hierarchies.

Step 6. Testing and Optimization

• Write SQL test cases for row counts, null checks, and data accuracy.
• Schedule SQL scripts or dbt jobs for refresh.
• Monitor performance with query logs or warehouse query history.

Conclusion
Building a Data Mart using SQL and data warehousing best practices is essential for scalable and high-performing BI. With the right design and automation in tools like dbt or SQL scripts, your organization can gain accurate, fast insights. In the next article, we’ll walk through building a Sales Data Mart in SQL + dbt with dimensional modeling and snapshotting logic.

HostForLIFEASP.NET SQL Server 2022 Hosting

Data is a strategic asset in today's information-driven economy, not just a byproduct of operations. Organizations may innovate, maximize performance, and make well-informed decisions that support growth when data is handled methodically. However, achieving this promise necessitates a disciplined approach to Data Resource Management (DRM) in addition to technology investment.

Managing Data as a Strategic Resource
Treating data as a valuable resource begins with a mindset shift. Like financial or human capital, data deserves deliberate stewardship. This includes:

• Inventory and Classification: Cataloging data assets based on sensitivity, usage, and business value.
• Ownership and Accountability: Defining clear roles for data stewardship across departments.
• Value Realization: Linking data to outcomes—such as improved customer experience, reduced risk, and operational efficiency.

By embedding these principles in organizational culture, enterprises align data usage with business strategy.

Data Governance: Building Trust Through Structure
Governance ensures that data remains secure, compliant, and aligned with organizational objectives. Key components include:

• Policy Frameworks: Establishing rules for data usage, sharing, and retention.
• Compliance Assurance: Meeting regulatory requirements (GDPR, HIPAA, etc.) across jurisdictions.
• Decision Rights: Clarifying who can make decisions about data quality, access, and modification.

Effective governance is not a one-time initiative—it is an evolving framework that adapts to technological and regulatory change.

Data Quality Assurance and Cleansing
High-quality data is foundational to reliable insights. Quality assurance and cleansing techniques address structural and semantic inconsistencies:

• Data Profiling: Evaluating datasets to detect anomalies and redundancies.
• Validation Rules: Implementing checks for accuracy, completeness, and consistency.
• Cleansing Strategies: Removing duplicate records, correcting formatting errors, and standardizing entries.

Proactive assurance mechanisms reduce analytical blind spots and improve downstream data reliability.

Data Integration Across Ecosystems
Seamless integration of data across platforms is vital for a unified business view. Effective integration practices include:

• ETL/ELT Processes: Extracting, transforming, and loading data from heterogeneous sources into a central repository.
• API-Driven Architecture: Facilitating real-time data flow between applications and services.
• Semantic Harmonization: Aligning data definitions and schemas to ensure consistency across systems.

Integrated data supports enterprise-wide decision-making and eliminates silos.

Data Storage Optimization
Efficient storage strategies ensure scalability and cost-effectiveness. Key considerations involve:

• Tiered Storage Models: Assigning data to storage media based on access frequency and business value.
• Compression Techniques: Reducing file size without losing fidelity to optimize space.
• Retention and Archival Policies: Automating data lifecycle transitions to minimize clutter and ensure compliance.

Storage optimization balances performance with sustainability, especially in data-intensive environments.

Data Visualization Techniques
Visual representation converts complex datasets into intuitive insights. Common techniques include:

• Dashboards and Reports: Real-time overviews of KPIs tailored for executive and operational users.
• Interactive Charts: Drill-down capabilities using bar graphs, pie charts, scatter plots, and heatmaps.
• Geospatial Mapping: Visualizing data across geographic dimensions for logistics and market intelligence.

Effective visualization bridges the gap between raw data and strategic understanding.

Ensuring Integrity and Accessibility
Data integrity and accessibility are twin engines of operational continuity and user empowerment:

• Data Integrity: Protects against unauthorized changes, corruption, or loss through encryption, backups, and versioning.
• Accessibility: Ensures users can retrieve necessary data—securely and efficiently—through robust access control and search capabilities.

Well-structured access policies preserve trust while empowering business users.

Strategic Stewardship in the Digital Age
Organizations must make investments in comprehensive DRM procedures that cut across departmental lines if they want to fully utilize data. Data becomes a dynamic force for innovation and resilience when it is supported by robust governance, reliable quality standards, smooth integration, and insightful visualizations.

HostForLIFEASP.NET SQL Server 2022 Hosting

One common task that a Database Administrator performs is the movement of data from one database to another. This scenario often arises when organizations need to consolidate data from multiple sources, migrate data to a data warehouse, or perform data analytics on a set of data stored in different databases. A classic scenario is ingesting data from one table in a database to another table in a different database, especially when both tables have identical columns. In this article, we'll explore the steps to accomplish this task effectively in SQL Server Management Studio

Establish the Database Connection
Before you can transfer data between tables in different databases, you must ensure that you have the necessary permissions and connectivity to access both databases, which often involves configuring connection settings, including server addresses, authentication credentials, and database names. 

Understand the Tables
It is important to ensure that you have a clear understanding of both the source and destination tables. In our case, the tables have the same columns, which simplifies the process. However, you should still verify that the column names, data types, and constraints match between the two tables. Any discrepancies can lead to data integrity issues during the transfer.

Write & Execute the SQL Query
To perform the data transfer, you'll need to write the SQL query by using the INSERT INTO ... SELECT the statement, which is commonly used for that purpose. The query should specify the source table, the columns to be transferred, and the destination table. Once you've written the SQL query, execute it by clicking on the Execute button. In our example, we want to transport the data in the DimCustomer table of the SalesData database into the DimCustomer table in the data warehouse database. Both tables have the same table structure.

In the screenshot below, we wrote a simple query to fetch all the records from that table.
SELECT * FROM DimensionCustomer;

In the next screenshot below, we switched to the destination database: DataWarehouse, and we selected all the columns in the dimCustomer table without any data ingested thus far. This is necessary to verify the structure of the destination table.

In the next screenshot below, we wrote and executed the query, and from the message displayed, a total of 18,484 records were inserted into the destination table.

INSERT INTO DataWarehouse.dbo.DimCustomer (
    CustomerKey,
    FirstName,
    LastName,
    BirthDate,
    MaritalStatus,
    Gender,
    EmailAddress,
    AnnualIncome,
    NumberofChildren,
    EducationalLevel,
    Address,
    PhoneNumber,
    FirstTransactionDate
)
SELECT
    CustomerKey,
    FirstName,
    LastName,
    BirthDate,
    MaritalStatus,
    Gender,
    EmailAddress,
    AnnualIncome,
    NumberofChildren,
    EducationalLevel,
    Address,
    PhoneNumber,
    FirstTransactionDate
FROM SalesData.dbo.DimensionCustomer;

Verify the Results
After the query execution is complete, it's essential to verify the results. To verify, we executed a simple query mentioned below, and all the records were returned.
SELECT * FROM dimCustomer;

HostForLIFEASP.NET SQL Server 2022 Hosting

Since this is all about using Stored Procedures to conduct actions on SQL tables, I will walk you through each step of using a DataList control to insert, select, edit, cancel, update, and remove data.

First Step: SQL Database
To add a data value to an EmployeeData table, first build a table in a database store in the manner shown below.

Table: EmployeeData
    Create Table EmployeeData (  
    EmpID int identity (1, 1) Primary Key,  
    EmpName varchar(30),  
    Contact nchar(15),  
    EmailId nvarchar(50)  
    )  

Stored Procedure:
Also create an insert procedure, a select procedure, an update procedure and a delete procedure as in the following.  
    Select procedure for DataList control.
        create procedure sp_FillData  
        As  
        Begin  
        set nocount on;  
        select EmpID, EmpName, Contact, EmailID from EmployeeData  
        End  
    Insert procedure.
        create procedure sp_InsertEmployeeData @EmpName varchar(30),  
        @Contact nchar(15),  
        @EmailId nvarchar(50) As Begin  
        set  
        nocount on;  
        Insert into dbo.EmployeeData (EmpName, Contact, EmailId)  
        values  
        (@EmpName, @Contact, @EmailId) End  
    Delete procedure.
        Create procedure sp_DeleteEmployeeData  
        @EmpID int  
        As  
        Begin  
        set nocount on;  
        Delete from EmployeeData where EmpID=@EmpID  
        End  
    Select procedure.
        create procedure sp_SelectEmployeeData  
        @EmpID int  
        As  
        Begin  
        set nocount on;  
        select EmpID, EmpName, Contact, EmailID from EmployeeData where EmpID=@EmpID  
        End  
    Update procedure.
        Create procedure sp_UpdateEmployeeData @EmpID int,  
        @EmpName varchar(30),  
        @Contact nchar(15),  
        @EmailId nvarchar(50) As Begin  
        set  
        nocount off;  
        UPDATE  
        Employeedata  
        SET  
        EmpName = @EmpName,  
        Contact = @Contact,  
        EmailId = @EmailId  
        WHERE  
        EmpID = @EmpID End  

Step 2: Maintain database connection
Provide the database connection string inside the project's Web.Config as in the following:
    <connectionStrings>  
       <add name="connstring" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>  
    </connectionStrings>  

Step 3: UI design
In Visual Studio create a UI design using the following procedure:
Go to Solution Explorer.
Right-click on the project and click Add tab.
Click Add New Item as in the following:

Figure 1: Add web form 
Now I will write the design code inside DataList.aspx. In this page add some TextBox controls, Button controls and a DataList Control.
Also add two button controls and TextBox controls inside the DataList data row update and delete with DataList event Item commands. The following is the UI design code.
    <%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="DataList.aspx.cs" Inherits="UI_DataList" %>  
      
    <asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">  
         
    </asp:Content>  
      
    <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">  
        <div>  
            <fieldset style="width: 269px" ><legend><b>DataList Example</b></legend>  
                <div style="width: 250px; background-color: #99FF66;">  
            <asp:Table runat="server">  
                <asp:TableRow>  
                    <asp:TableCell>Name</asp:TableCell><asp:TableCell><asp:TextBox ID="txtName" runat="server"></asp:TextBox ></asp:TableCell>  
                </asp:TableRow>  
      
                <asp:TableRow>  
                    <asp:TableCell>Contact</asp:TableCell><asp:TableCell><asp:TextBox ID="txtContact" runat="server"></asp:TextBox></asp:TableCell>  
                </asp:TableRow>  
      
                <asp:TableRow>  
                    <asp:TableCell>Email id</asp:TableCell><asp:TableCell><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></asp:TableCell>  
                </asp:TableRow>  
      
                <asp:TableRow>  
                    <asp:TableCell></asp:TableCell><asp:TableCell><asp:Button ID="btnSave" Text="Add Record" runat="server" OnClick="btnSave_Click" /></asp:TableCell>  
                </asp:TableRow>  
            </asp:Table>  
            </div>  
            </fieldset>  
            <br />  
        <fieldset style="width: 535px"><legend><b>Employee Information</b></legend>  
        <div style="background-color: #66FF66">  
        <asp:DataList ID="DataListEmp" runat="server"   
                 DataKeyField="EmpID"   
                 OnDeleteCommand="DataListEmp_DeleteCommand"   
                 OnEditCommand="DataListEmp_EditCommand"  
                 OnUpdateCommand="DataListEmp_UpdateCommand"   
                 OnCancelCommand="DataListEmp_CancelCommand" Width="527px" >  
                <HeaderTemplate>  
                <table><tr style="background-color: #800000; color: #FFFFFF">  
                <th>Name</th><th>Contact</th><th>Email ID</th><th>Action</th></tr>  
                </HeaderTemplate>  
                <ItemTemplate>  
                <tr >  
                <td><%# DataBinder.Eval(Container.DataItem, "EmpName")%></td>  
                <td><%# DataBinder.Eval(Container.DataItem,"Contact")%></td>  
                <td><%# DataBinder.Eval(Container.DataItem, "EmailId")%></td>  
                <td><asp:Button ID="imgbtnedit" runat="server" Text="Edit"  ToolTip="Edit" CommandName="Edit"/></td>  
                <td><asp:Button ID="btndelete" runat="server" Text="Delete" CommandName="Delete" ToolTip="Delete"/></td>  
                </tr>            
                </ItemTemplate>  
                <EditItemTemplate>             
                <tr>  
                 <td><asp:TextBox BackColor="Yellow" Font-Bold="true"  ID="txtName" runat="server" Text='<%# Eval("EmpName") %>'></asp:TextBox></td>  
                 <td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtContact" runat="server" Text='<%# Eval("Contact") %>'></asp:TextBox></td>  
                 <td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtEmail" runat="server" Text='<%# Eval("EmailId") %>'></asp:TextBox></td>  
                 <td><asp:Button ID="btnupdate" runat="server"  ToolTip="Update" Text="Update" CommandName="Update" /></td>  
                 <td><asp:Button ID="btncancel" runat="server"  ToolTip="Cancel" Text="Cancel" CommandName="Cancel" /></td>  
                </tr>  
                </EditItemTemplate>  
            </asp:DataList>  
            </div>  
            </fieldset>  
            </div>  
    </asp:Content>  

Step 4: UI code
Here is all about an operation on a DataList row records.
In this first write code for the TextBox value insertion into the SQL table by the preceding Insert Procedure, then write code for the inserted record display in the DalaList control. Write the code for two buttons, one is the delete and the second is an update button code.

Now create an update command, an edit command, a cancel command, an Item command according to the following Figure 3 and also maintain a datakeyfield in the property data section as in Figure 2. 

Figure 2: Add Data Key Field

Figure 3: Add Action Command
Code
    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Web;  
    using System.Web.UI;  
    using System.Web.UI.WebControls;  
    using System.Configuration;  
    using System.Data;  
    using System.Data.SqlClient;  
      
    public partial class UI_DataList : System.Web.UI.Page  
    {  
        string connection = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;  
        protected void Page_Load(object sender, EventArgs e)  
        {  
            if (!IsPostBack)  
            {  
                GetEmpDataList();  
            }  
        }  
        protected void btnSave_Click(object sender, EventArgs e)  
        {  
            using (SqlConnection con = new SqlConnection(connection))  
            {  
                using (SqlCommand cmd = new SqlCommand("sp_InsertEmployeeData", con))  
                {  
                    cmd.CommandType = CommandType.StoredProcedure;  
      
                    cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();  
                    cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();  
                    cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();  
      
                    con.Open();  
                    cmd.ExecuteNonQuery();  
                    con.Close();  
      
                    Clear();  
                    Response.Write("<script type=\"text/javascript\">alert('Record Inserted Successfully');</script>");  
                    GetEmpDataList();  
                }  
            }  
        }  
        void Clear()  
        {  
            txtName.Text = String.Empty;  
            txtContact.Text = String.Empty;  
            txtEmail.Text = String.Empty;  
        }  
        private void GetEmpDataList()  
        {  
            using (SqlConnection con = new SqlConnection(connection))  
            {  
                SqlDataAdapter sd = new SqlDataAdapter("sp_FillData", con);  
                sd.SelectCommand.CommandType = CommandType.StoredProcedure;  
                DataTable dt = new DataTable();  
      
                sd.Fill(dt);  
      
                if (dt.Rows.Count > 0)  
                {  
                    DataListEmp.DataSource = dt;  
                    DataListEmp.DataBind();  
                }  
             }  
        }  
        protected void DataListEmp_DeleteCommand(object source, DataListCommandEventArgs e)  
        {  
           int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());  
      
           using (SqlConnection con = new SqlConnection(connection))  
           {  
               using (SqlCommand cmd = new SqlCommand("sp_DeleteEmployeeData", con))  
               {  
                   cmd.CommandType = CommandType.StoredProcedure;  
                   cmd.Parameters.AddWithValue("@EmpID",EmpID);  
      
                   con.Open();  
                   cmd.ExecuteNonQuery();  
                   con.Close();  
      
                   Response.Write("<script type=\"text/javascript\">alert('Record Deleted Successfully');</script>");  
                   GetEmpDataList();  
               }  
           }  
        }  
        protected void DataListEmp_EditCommand(object source, DataListCommandEventArgs e)  
        {  
            DataListEmp.EditItemIndex = e.Item.ItemIndex;  
            GetEmpDataList();  
        }  
        protected void DataListEmp_CancelCommand(object source, DataListCommandEventArgs e)  
        {  
            DataListEmp.EditItemIndex = -1;  
            GetEmpDataList();  
        }  
        protected void DataListEmp_UpdateCommand(object source, DataListCommandEventArgs e)  
        {  
            int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());  
      
            TextBox txtName = (TextBox)e.Item.FindControl("txtName");  
            TextBox txtContact = (TextBox)e.Item.FindControl("txtContact");  
            TextBox txtEmail = (TextBox)e.Item.FindControl("txtEmail");  
      
            using (SqlConnection con = new SqlConnection(connection))  
            {  
                using (SqlCommand cmd = new SqlCommand("sp_UpdateEmployeeData", con))  
                {  
                    cmd.CommandType = CommandType.StoredProcedure;  
      
                    cmd.Parameters.AddWithValue("@EmpID", EmpID);  
                    cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();  
                    cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();  
                    cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();  
      
                    con.Open();  
                    cmd.ExecuteNonQuery();  
                    con.Close();  
      
                    Clear();  
                    Response.Write("<script type=\"text/javascript\">alert('Record Update Successfully');</script>");  
                    DataListEmp.EditItemIndex = -1;  
                    GetEmpDataList();  
                }  
            }  
        }  
    }  

Step 5: Browser Side
Now run your new page in the browser. I hope you understood how to work with the DataList control Action Command Event and SQL Procedures.
Have a nice day. 

HostForLIFEASP.NET SQL Server 2022 Hosting

report on credit and debit transactions in banking.

USE [SqlBank]

CREATE   FUNCTION [dbo].[FNGetTransaction](@CID BIGINT)
RETURNS @Tab_TRansaction TABLE (id BIGINT ,CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200)
, CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime,Remarks varchar(max) , IsmailSend int)
AS
BEGIN

DECLARE @TempAC_ID BIGINT;

SET @TempAC_ID = (SELECT TOP 1 A.AC_ID FROM Tbl_Account A join Tbl_Cust
  C ON A.CID=C.CID WHERE c.CID=@CID)

DECLARE @Tbl_Tran Table
(id BIGINT,
CreditAmt MONEY,DebitAmt MONEY,Tr_Type VARCHAR(250),
TranDate DATETIME ,AC_ID BIGINT ,Balance DECIMAL(18,2),TType VARCHAR(200),
 CustName varchar(max),AC_NO bigint,Address varchar(max),Mobile varchar(max),
Email varchar(max),AC_OpDate datetime  ,
Remarks varchar(max)  , IsmailSend int
)


INSERT INTO @Tbl_Tran(id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate,Remarks,IsmailSend)
SELECT TR.TR_ID, CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END CreditAmt,
CASE WHEN tr.TR_CrDrType ='Dr' THEN tr.TR_Amt ELSE 0 END DebitAmt ,Tr.TR_Type,tr.TR_Date,Tr.AC_ID ,

 CASE WHEN tr.TR_CrDrType ='Cr' THEN tr.TR_Amt ELSE 0 END - CASE WHEN tr.TR_CrDrType ='Dr'
 THEN tr.TR_Amt ELSE 0 END  Balance,
 Tr.TR_CrDrType  ,C.CName ,Acc.AC_NO ,C.CAddress ,C.CMObile,C.CEmail ,Acc.AC_OpDate ,
 Tr.Remarks , Tr.IsmailSend

FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc with(nolock) ON acc.AC_ID=Tr.AC_ID
      join Tbl_Cust C with(nolock) ON C.CID=Acc.CID
WHERE Acc.CID=@CID;

WITH Tbl_CTE_Tran
as
(
SELECT T2.id,T2.CreditAmt,T2.DebitAmt,SUM(T1.CreditAmt-T1.DebitAmt) Balance,
T2.Tr_Type,T2.TranDate,T2.AC_ID
,T2.TType,T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate,t2.Remarks,t2.IsmailSend FROM @Tbl_Tran T1
join @Tbl_Tran T2 on T1.id<=T2.id WHERE T2.AC_ID=@TempAC_ID
GROUP BY T2.id,T2.CreditAmt,T2.DebitAmt,T2.Tr_Type,T2.TranDate,T2.AC_ID,T2.TType,
T2.CustName ,T2.AC_NO ,T2.Address
 ,T2.Mobile,T2.Email ,T2.AC_OpDate  ,t2.Remarks ,t2.IsmailSend
)

INSERT INTO @Tab_TRansaction (id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks ,IsmailSend
 )
SELECT id,CreditAmt,DebitAmt,Tr_Type,TranDate,AC_ID,Balance,TType  ,CustName ,AC_NO ,Address
 ,Mobile,Email ,AC_OpDate ,Remarks,IsmailSend
FROM Tbl_CTE_Tran  with(nolock)
WHERE AC_ID=@TempAC_ID

RETURN
END

STEP 2 : Create Procedure & Call above funtion in Procedure
USE [SqlBank]

CREATE PROC [dbo].[PROC_TRansaction]
(
@TR_ID int=null output,
@CID bigint=null,
@TR_Amt decimal(18,2)=null,
@AC_ID bigint =null,
@Flag varchar(100)=null,
@AC_No bigint=null,
@Remarks varchar(max)=null,
@MTR_ID int=null output,
@Balance decimal(18,2)=null output
)
AS
BEGIN

DECLARE @TempTRAmount decimal(18,2)
DECLARE @Temp_ACID bigint

DECLARE @Tran_ScopID bigint;
DECLARE @Tran_ID bigint;
DECLARE @MMTR_ID bigint;

BEGIN TRAN Tbl_Transaction_Tran
   BEGIN  TRY


      IF(@Flag = 'Tran')
      BEGIN

IF EXISTS(SELECT 1 FROM Tbl_Transaction Tr with(nolock) join Tbl_Account Acc
with(nolock) ON acc.AC_ID=Tr.AC_ID WHERE Acc.CID=@CID)
   BEGIN


 SELECT  a.id id ,a.DebitAmt,a.CreditAmt,a.Balance
 ,a.Tr_Type, isnull(Format(a.TranDate,'dd-MMM-yyyy HH:mm'),'') TranDate, NCHAR(8377) Rupees ,a.TType,a.Remarks
   FROM dbo.FNGetTransaction(@CID) a
--      JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id

--GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType


END
ELSE
BEGIN
Select 'No Transaction summary found...?' OpMsg
END
    END

ELSE IF(@Flag = 'IN')
    BEGIN

    SET @Temp_ACID = (SELECT Top 1 A.AC_ID  FROM Tbl_Account A with(nolock)
     Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE A.AC_No=@AC_No)

    DECLARE @SenderName varchar(max)
        SET @SenderName = (SELECT Top 1 c.CName  FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID WHERE c.CID=@CID)

    DECLARE @ReciverName varchar(max)
       SET @ReciverName = (SELECT Top 1 c.CName FROM Tbl_Account A with(nolock)
        Join Tbl_Cust C with(nolock) ON A.CID=C.CID
       WHERE A.AC_No=@AC_No)


SET @TempTRAmount = (
 SELECT TOP 1 ISNULL(SUM(b.balance),0) Balance
   FROM dbo.FNGetTransaction(@CID) a
JOIN dbo.FNGetTransaction(@CID) b ON b.id<=a.id

GROUP BY a.id,a.DebitAmt,a.CreditAmt,a.Tr_Type,a.TranDate,a.AC_ID,a.TType ORDER BY a.id desc)

if(@TR_Amt > @TempTRAmount)
BEGIN

Select 'Insuffitient Balance' as msg

END
ELSE
  BEGIN
  Declare @FixScratchAmt decimal(18,2)=500;

  --if not exists (select 1 from Tbl_Transaction Where TR_Date=CURRENT_TIMESTAMP and Ref_TranACC=@AC_ID)
  --begin
  Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Online - Transfer To - '+ @ReciverName + ' '+Cast(@Ac_NO as varchar(max))+' ',
  ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@AC_ID,'Dr','Tran-' +CAST(@AC_ID as varchar(max)),0,'S',0,@Remarks)

  set @Tran_ID = @@IDENTITY;
  set @TR_ID= @Tran_ID;
  set @Tran_ScopID= SCOPE_IDENTITY();
  Set @Balance = (SELECT TOP 1 BALANCE FROM dbo.FNGetTransaction(@CID) order by id desc)


  if(@TR_Amt >= @FixScratchAmt)
  begin
   Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Cash Back From S Bank7 ',10,CURRENT_TIMESTAMP,@AC_ID,'Cr',0,1,'R',0,'Cash back from Sbank7. Pay & win more cash back ')
  END

Insert INTO Tbl_Transaction (TR_Type,TR_Amt,TR_Date,AC_ID,TR_CrDrType,Ref_TranACC,isdelete,IsTranType,IsMailSend,Remarks)
  Values                ('Recived From ' + @SenderName + ' Tran - '+Cast(@Tran_ScopID as varchar(max))+'-'+
  CAST(@AC_ID as varchar(max)),ISNULL(@TR_Amt,0),CURRENT_TIMESTAMP,@Temp_ACID,'Cr','Tran-'
  +Cast(@Tran_ScopID as varchar(max))+'-'+ CAST(@AC_ID as varchar(max)),0,'R',0,@Remarks)
  set @MMTR_ID = @@IDENTITY;
  set @MTR_ID = @MMTR_ID;


 -- Select TOP 1 c.CEmail ReciverEmail,c.CName From Tbl_Cust c join Tbl_Account a on a.CID=c.CID where a.AC_No=@AC_No

 -- SELECT top 1 A.AC_No,t.TR_Amt,t.TR_CrDrType,@SenderName SenderName,'http://saipathrikar.blogspot.com/' Url ,

 --   (
 --  SELECT top 1 fna.Balance

 --  FROM dbo.FNGetTransaction(@CID) fna

    --order by fna.id desc
 -- ) Bal , @ReciverName ReciverName


  -- FROM Tbl_Transaction T join Tbl_Account A on
  -- T.AC_ID= A.AC_ID Where A.CID=@CID
  --order by t.TR_ID desc

 -- SELECT top 1 A.AC_No,t.TR_Amt,t.TR_CrDrType,t.Ref_TranACC +'  ' +
 --  @SenderName SenderName,'http://saipathrikar.blogspot.com/' Url  ,

 --  (
 --  SELECT top 1 fna.Balance

 --  FROM Tbl_Account a cross apply dbo.FNGetTransaction(a.cid) fna
 --   where a.ac_ID=fna.AC_ID and a.AC_No=@AC_No
    --order by fna.id desc
 -- ) Bal


 --  FROM Tbl_Transaction T join Tbl_Account A on
 --  T.AC_ID= A.AC_ID Where A.AC_No=@AC_No
 -- order by t.TR_ID desc

 -- end

 -- This Sectioin is for pdf data

 --SELECT  a.id Tran_id ,a.Tr_Type Type,a.TranDate Date,a.CreditAmt,a.DebitAmt,a.TType CrDr,a.Balance

 --  FROM dbo.FNGetTransaction(@CID) a


 --  SELECT  a.id Tran_id ,a.Tr_Type Type,a.TranDate Date,a.CreditAmt,a.DebitAmt,a.TType CrDr,a.Balance
 --  FROM Tbl_Account ac cross apply dbo.FNGetTransaction(ac.cid) a
 --   where ac.ac_ID=a.AC_ID and ac.AC_ID=@Temp_ACID


    END

    END

IF(@@TRANCOUNT > 0)
  BEGIN
  COmmit tran Tbl_Transaction_Tran
  END

END TRY
BEGIN CATCH

IF(@@TRANCOUNT > 0)
            BEGIN
            ROLLBACK TRAN Tbl_Transaction_Tran
            END

            DECLARE @USERID varchar(max),@ERRORLINE varchar(max)
            ,@ERRORMESSAGE varchar(max),@ERRORPROCEDURE varchar(500),@ERRORSEVERITY varchar(max)
            ,@ERRORSTATE varchar(max), @ErroFrm varchar(max)

            SELECT @USERID = SUSER_SNAME(),@ERRORLINE=ERROR_LINE(),@ERRORMESSAGE=ERROR_MESSAGE(),
                   @ERRORPROCEDURE=ERROR_PROCEDURE(),@ERRORSEVERITY=ERROR_SEVERITY(),
                  @ERRORSTATE= ERROR_STATE() ,@ErroFrm = 'Backend'

        EXEC Proc_ERRORLOG @USERID,@ERRORLINE,@ERRORMESSAGE,@ERRORPROCEDURE,@ERRORSEVERITY,@ERRORSTATE,0,@ErroFrm

END CATCH

END

SQL

Step 1.  Create Table valued function for calculate credit,debit amount with total balance

STEP 2. Execute Procedure to Check Report.

exec [dbo].[PROC_TRansaction]

@CID =2,@Flag='Tran'

STEP 3. Output

Thank you for reading this article.

HostForLIFEASP.NET SQL Server 2022 Hosting

This comprehensive reference manual compares and classifies the most used SQL queries in Oracle and SQL Server, arranged by:

• DDL: Data Definition Language
• DML: Data Manipulation Language
• TCL: Transaction Control Language
• DCL: Data Control Language
• DQL: Data Query Language

1. DDL – Data Definition Language
Used for defining or modifying database objects like tables and columns.
Oracle Syntax Oracle Syntax

2. DML – Data Manipulation Language
Used for managing the data within tables.

Oracle Syntax Oracle Syntax

3. TCL – Transaction Control Language
Used to manage changes made by DML operations.

4. DCL – Data Control Language
Used to control access and privileges on database objects.

5. DQL – Data Query Language
Used to query data from tables.

Bonus Query Comparisons

Summary Table – SQL Query Categories

HostForLIFEASP.NET SQL Server 2022 Hosting

Microsoft created SQL Server 2025, a potent relational database management system (RDBMS) ideal for database applications at the enterprise level. If you properly follow the instructions, installing SQL Server 2025 on a Windows computer is simple.

System Requirements
Make sure your system meets the following requirements before proceeding:

• Operating System: Windows 10, Windows Server 2019, or higher
• Processor: Minimum 2 GHz; recommended multi-core processor
• RAM: Minimum 4 GB; recommended 8 GB or higher
• Disk Space: At least 10 GB of free space
• .NET Framework: Version 4.8 or higher

Step-by-Step Installation
Step 1: Download SQL Server 2025
Visit the official Microsoft website and navigate to the SQL Server 2025 download page. Choose the appropriate version (Standard, Enterprise, or Developer) based on your needs, and download the installation package. Or click here to download SQL Server 2025, then fill out the form below to download the SQL Server 2025 public preview version.

Then the SQL Server Media file will download as shown in the snapshot below.

Step 2: Launch the Installer
Once the download is complete:

• Locate the installer file in the download folder (file name = SQL2025-SSEI-Eval.exe).
• Right-click on the file and select Run as Administrator to ensure proper installation. 

Step 3: Choose Installation Type
The installation wizard will prompt you to select the type of installation:

• Basic Installation: Recommended for beginners or small-scale projects.
• Custom Installation: Allows you to choose specific features and settings.
• Here I am selecting Basic for now, as shown in the snapshot below.

For most users, selecting Custom Installation provides the flexibility needed to tailor the installation to their environment. Then select Agree to accept the Microsoft SQL Server License Terms.

Then choose the installation location. For now, I am keeping the default installation path, which is C:\Program Files\Microsoft SQL Server, and click on the Install button. Then, the download and install package will begin as shown in the snapshot below. It may take a few minutes, depending on your internet speed. Once the download finishes, installation will start automatically. Just wait for a few minutes.

Step 4: Configure Instance
During installation:

• Choose between a Default Instance or a Named Instance.
• A Default Instance is typically named MSSQLSERVER and works for general purposes.
• Named Instances are useful for running multiple SQL Server versions on the same machine.

If SSMS is already installed, then click on connect now, or click on the close button, then connect in SSMS, or if SSMS is not installed already, then click on install SSMS, or follow my other article to learn about What is SQL Server Management Studio (SSMS) and How to install SSMS in Windows. Or if you are installing SQL Server for the first time on your machine, then follow the steps mentioned below.

Step 5: Set Up Server ConfigurationThe wizard will ask for server configuration details:

• Specify the Authentication Mode:
• Windows Authentication: Recommended for integration with Windows accounts.
• Mixed Mode: Allows both Windows and SQL Server authentication.

Provide a strong password for the system administrator (SA) account if using Mixed Mode.

Step 6: Select Features
Choose the features you wish to install:

• Database Engine Services: For managing databases.
• Analysis Services: For data analytics.
• Reporting Services: For generating reports.
• Integration Services: For ETL processes.

Make sure to only select the features that are relevant to your project to save system resources, and then click on the next button as shown in the snapshot below.

Step 7: Installation Progress
Once all configurations are set, the installer will begin installing SQL Server 2025. This process may take several minutes. Monitor the progress bar and ensure the installation completes without errors.

Step 8: Verify Installation
After installation:
Open SQL Server Management Studio (SSMS) or a similar tool.
Connect to the newly installed SQL Server instance using your credentials.
Run a simple query to test database functionality.

Conclusion
Following these steps will help you install SQL Server 2025 successfully on a Windows system. Make sure to keep software and drivers up to date for optimal performance and security. If you require any clarification/suggestions on the article, please leave your questions and thoughts in the comment section below. Follow C# Corner to learn more new and amazing things about SQL or to explore more technologies. Thanks for reading, and I hope you like it.

HostForLIFEASP.NET SQL Server 2022 Hosting

The most recent iteration of Microsoft's well-liked relational database management system, SQL Server 2022, significantly boosts Tempdb's performance. During a variety of processes, including sorting, joining, and indexing, data is temporarily stored in the Tempdb, a unique database in SQL Server. Temporary objects like temporary tables and table variables are likewise stored in the Tempdb. We'll examine the significant enhancements to Tempdb performance in SQL Server 2022 in more detail in this post.

Prior to delving into the specifics of the enhancements, it is crucial to comprehend the significance of Tempdb performance for SQL Server. SQL Server relies heavily on Tempdb to carry out a number of tasks, and any Tempdb performance problems can have a big effect on SQL Server's overall performance. This is particularly true for systems with high transaction volumes, where Tempdb may become a performance snag. The efficiency of Tempdb-related activities should significantly increase for enterprises thanks to SQL Server 2022's enhancements.

So, what are the improvements in Tempdb performance in SQL Server 2022? The improvements are mainly related to the following areas:

• Reducing contention for the Tempdb data file
• Reducing contention for the Tempdb metadata
• Improved scalability of Tempdb

Reducing Contention for the Tempdb Data File
One of the most significant improvements in SQL Server 2022 is the reduction in contention for the Tempdb data file. Reducing contention for the Tempdb data file is an important aspect of optimizing the performance of the SQL Server. In earlier versions of SQL Server, contention for the Tempdb data file was a significant bottleneck, especially in high-transaction systems. SQL Server 2022 has introduced a new algorithm that reduces contention for the Tempdb data file by distributing page allocations across multiple Uniform Resource Identifiers (URIs). Contention for the Tempdb data file can occur when multiple sessions or transactions are trying to access the same data page simultaneously, leading to contention and slowing down overall database performance. To reduce contention for the Tempdb data file, here are some strategies that can be used:

Increase the number of Tempdb data files
By default, SQL Server creates only one Tempdb data file, but you can increase the number of data files to match the number of processor cores available in the system. This can help to distribute the workload across multiple files, reducing contention for a single file.

Use trace flag 1118
This trace flag forces SQL Server to allocate uniform extents to Tempdb data files instead of mixed extents. Doing so reduces contention for the PFS (Page Free Space) page and improves performance.

Separate Tempdb from user databases
When Tempdb and user databases share the same disk, there is a higher chance of contention. By moving Tempdb to a separate disk, the disk IO is optimized, and the contention is reduced.

Use SSDs for Tempdb storage
Solid State Drives (SSDs) provide faster access to data, reducing the time taken for I/O operations. This can help to improve the performance of Tempdb, especially during heavy workloads.

Optimize Tempdb usage
Tempdb stores temporary data such as worktables, table variables, and cursors. Optimizing the usage of these objects, such as avoiding the use of temporary tables when not necessary and minimizing the use of cursors, can reduce the workload on Tempdb, leading to improved performance.
Reducing Contention for the Tempdb Metadata

The Tempdb database is a system database that stores temporary user objects, temporary tables, temporary stored procedures, and other temporary data generated during query processing. The metadata of these objects is stored in a special system table called sys.system_internals_allocation_units. As multiple user sessions can access Tempdb simultaneously, there can be contention for the Tempdb metadata, leading to performance issues. Here are a few strategies to reduce contention for the Tempdb metadata in SQL Server:

• One way to reduce contention for the Tempdb metadata is to reduce the number of user connections. You can limit the number of users who can connect to the SQL Server instance or restrict access to specific applications or users.
• By default, SQL Server creates a single data file for Tempdb, which can lead to contention for the Tempdb metadata. To reduce this contention, you can configure multiple Tempdb data files, each stored on a separate physical disk. This allows multiple threads to access Tempdb simultaneously, reducing contention for the Tempdb metadata.
• Another way to reduce contention for the Tempdb metadata is to move the Tempdb database to a dedicated disk. By doing this, you can reduce the amount of disk I/O generated by other databases, improving the performance of Tempdb and reducing contention for the Tempdb metadata.
• Global temporary tables are stored in Tempdb and can lead to contention for the Tempdb metadata, especially if they are accessed by multiple user sessions simultaneously. To reduce contention for the Tempdb metadata, you can reduce the use of global temporary tables or replace them with local temporary tables stored in the user database.
• It is important to monitor the Tempdb metadata contention regularly. You can use SQL Server Profiler or System Monitor to monitor the Tempdb metadata contention and identify performance issues. By doing this, you can take proactive measures to reduce contention for the Tempdb metadata and improve the performance of your SQL Server instance.

Improved Scalability of Tempdb
The scalability of Tempdb is an important consideration for managing large databases and improving the performance of SQL Server. Few ways to improve the scalability of Tempdb in SQL Server:

• By default, Tempdb has only one data file and one log file. This can cause contention and performance issues as the database grows. To improve scalability, you can split Tempdb into multiple data files, one for each CPU core or up to 8 cores per file for OLTP workloads. This allows SQL Server to spread the load across multiple files and reduce contention.
• Tempdb should be set to automatically grow as needed to avoid running out of space. However, the default settings may not be optimal for your workload. Configure the auto growth settings for Tempdb based on your database's usage and expected growth rate. You should also preallocate space for Tempdb to avoid fragmentation and disk space issues.
• Monitoring Tempdb usage is critical to identifying performance issues and tuning the database. Use SQL Server's built-in tools like DMVs and performance counters to monitor Tempdb usage, including page allocation, contention, and IO operations. This can help you identify bottlenecks and adjust the database configuration to improve performance.
• Separating the data and log files for Tempdb can improve performance by allowing them to be stored on different disks or storage systems. This can reduce contention and improve IO performance.
• Using solid-state drives (SSDs) for Tempdb can significantly improve performance by reducing IO latency and increasing throughput. SSDs are faster than traditional hard disk drives and can enhance the scalability of Tempdb.

Optimizing the database configuration, monitoring usage, and using appropriate hardware can improve the performance and scalability of SQL Server for your workload.

Conclusion
SQL Server 2022's Tempdb enhancements are important for improving SQL Server's overall performance. Improving Tempdb's scalability, lowering contention for the Tempdb data file, and lowering contention for the Tempdb metadata are the primary areas for improvement. A new algorithm that divides page allocations among several URIs reduces contention for the Tempdb data file. This algorithm can be further optimized by increasing the number of Tempdb data files, utilizing trace flag 1118, separating Tempdb from user databases, utilizing SSDs for Tempdb storage, and optimizing Tempdb usage. To reduce contention for the Tempdb metadata, users can reduce the number of user connections, configure multiple Tempdb data files, move Tempdb to a dedicated disk, reduce the use of global temporary tables, and monitor the Tempdb metadata contention regularly. Improved scalability of Tempdb can be achieved by enabling the indirect checkpoint feature, configuring Instant File Initialization, using smaller Tempdb files, and enabling Tempdb snapshot isolation. These improvements to Tempdb performance in SQL Server 2022 will significantly enhance the performance of high-transaction systems and provide faster processing of temporary data.

HostForLIFEASP.NET SQL Server 2022 Hosting