We will go over some key SQL ideas in this blog and provide samples of SQL Server code for each. These ideas include index types as well as standard procedures like writing joins, working with views and triggers, and generating and modifying tables.

Index Clustering
The data rows in the table are sorted and stored by a clustered index according to the key values. There can only be one clustered index per table.

-- Create a clustered index on the 'Id' column of the 'Employee' table
CREATE CLUSTERED INDEX IX_Employee_Id
ON Employee(Id);

Non-Clustered Index
A non-clustered index stores the index structure separately from the actual table data, creating pointers to the rows.
-- Create a non-clustered index on the 'Name' column of the 'Employee' table
CREATE NONCLUSTERED INDEX IX_Employee_Name
ON Employee(Name);

Create Table
Creating a table involves defining the columns and their data types.
-- Create an 'Employee' table
CREATE TABLE Employee (
    Id INT PRIMARY KEY,
    Name NVARCHAR(50),
    DepartmentId INT,
    HireDate DATE
);

Insert Multiple Rows
Insert multiple rows into a table in one query.

-- Insert multiple rows into the 'Employee' table
INSERT INTO Employee (Id, Name, DepartmentId, HireDate)
VALUES
(1, 'Alice', 1, '2021-01-01'),
(2, 'Bob', 2, '2021-02-01'),
(3, 'Charlie', 1, '2021-03-01');

Alter Table
Modify an existing table by adding or modifying columns.
-- Add a new 'Salary' column to the 'Employee' table
ALTER TABLE Employee
ADD Salary DECIMAL(10, 2);

Update Row
Updating the data of a row in a table.

-- Update the salary of the employee with Id 1
UPDATE Employee
SET Salary = 70000
WHERE Id = 1;

Rename Table
Renaming an existing table.
-- Rename 'Employee' table to 'Staff'
EXEC sp_rename 'Employee', 'Staff';

Delete Rows
Delete specific rows from a table based on a condition.
-- Delete an employee with Id 2
DELETE FROM Employee
WHERE Id = 2;

Drop Table
Delete the entire table and all of its data.

-- Drop the 'Employee' table
DROP TABLE Employee;

Truncate Table
Remove all rows from a table without logging each row deletion.
-- Truncate the 'Employee' table
TRUNCATE TABLE Employee;

Cursor
A cursor is used to retrieve rows from a result set one at a time.
DECLARE @EmployeeId INT;
DECLARE employee_cursor CURSOR FOR
SELECT Id FROM Employee;

OPEN employee_cursor;
FETCH NEXT FROM employee_cursor INTO @EmployeeId;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Do something with each row
    PRINT @EmployeeId;
    FETCH NEXT FROM employee_cursor INTO @EmployeeId;
END;

CLOSE employee_cursor;
DEALLOCATE employee_cursor;

View
A view is a virtual table based on a query.
-- Create a view to show employee names and hire dates
CREATE VIEW EmployeeView AS
SELECT Name, HireDate
FROM Employee
WHERE HireDate > '2021-01-01';

Trigger
A trigger is a special kind of stored procedure that automatically runs when an event occurs in the database.
-- Create a trigger that prints a message after inserting into the Employee table
CREATE TRIGGER trg_AfterInsertEmployee
ON Employee
AFTER INSERT
AS
BEGIN
    PRINT 'New employee inserted!';
END;

WITH CTE (Common Table Expression)
CTEs are used to create a temporary result set that can be referenced in a SELECT, INSERT, UPDATE, or DELETE statement.
WITH EmployeeCTE AS (
    SELECT Name, Salary
    FROM Employee
    WHERE Salary > 60000
)
SELECT *
FROM EmployeeCTE;

Inner Join
An inner join returns rows when there is at least one match in both tables.
-- Inner join between Employee and Department
SELECT e.Name, d.DepartmentName
FROM Employee e
INNER JOIN Department d
ON e.DepartmentId = d.Id;

Left Join
A left join returns all rows from the left table and the matched rows from the right table. Unmatched rows will return NULL for columns from the right table.
-- Left join between Employee and Department
SELECT e.Name, d.DepartmentName
FROM Employee e
LEFT JOIN Department d
ON e.DepartmentId = d.Id;

Right Join
A right join returns all rows from the right table and the matched rows from the left table. Unmatched rows will return NULL for columns from the left table.
-- Right join between Employee and Department
SELECT e.Name, d.DepartmentName
FROM Employee e
RIGHT JOIN Department d
ON e.DepartmentId = d.Id;

Self Join
A self-join is a regular join but joins the table with itself.
-- Self join on Employee table to find employees and their managers
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerId = e2.Id;

Cross Join
A cross join returns the Cartesian product of two tables, meaning every row in the left table is combined with every row in the right table.
-- Cross join between Employee and Department
SELECT e.Name, d.DepartmentName
FROM Employee e
CROSS JOIN Department d;

Cross Apply
Cross Apply works like an inner join but is used to join a table with a table-valued function.
-- Cross apply example
SELECT e.Name, sub.TopDepartment
FROM Employee e
CROSS APPLY (
    SELECT TOP 1 d.DepartmentName AS TopDepartment
    FROM Department d
    WHERE d.Id = e.DepartmentId
) sub;

ROW_NUMBER()
The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set.

-- Assign row numbers to employees based on salary
SELECT
    Name,
    Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber
FROM
    Employee;

RANK()
The RANK() function assigns a rank to each row within a partition, with gaps in rank when there are ties.

-- Assign ranks to employees based on salary
SELECT Name,
       Salary,
       RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employee;

DENSE_RANK()
The DENSE_RANK() function assigns ranks to rows without gaps between ranks when there are ties.
-- Assign dense ranks to employees based on salary
SELECT
    Name,
    Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM
    Employee;

This concludes our overview of some essential SQL concepts and SQL Server code examples. These queries and operations form the foundation of working with relational databases, making them crucial for both beginners and advanced users alike.

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.

How does Database Mail work?
The SQL Server Database Engine may send emails using Database Mail, an enterprise email solution. Your database applications can send users emails by using Database Mail. The messages may include files from any resource on your network in addition to query results.

Step 1: In order to send mail from SQL Server, we must first install Microsoft SQL Server Management Studio (SSMS) on our PC or laptop. Thus, download and install SSMS first if you haven't already on your PC.

Step 2
Now, Open SSMS and connect it to your SQL Server instance.
Expand Management option, there you can find the Database Mail option. As you can see in the following image.

Now, Right click on Database Mail and select the Configure Database Mail option.
This will open a Database Mail Configuration wizard. Select next to continue.
Now choose the first option, Set up Database Mail, by performing the following tasks, and click next.

Now, give your database mail Profile Name and its description as whatever you want. Here, I am giving "Test Profile" as the profile name. In the SMTP account section, select the Add account option to create a new SMTP account as in the following image.

Now, enter the information shown in the following image in the New Database Mail Account box. I'm sending emails from this account using Gmail. Thus, I set the server name to smtp.gmail.com and the port number to 587.

Use the email address and password from which you wish to send emails when logging into SMTP Authentication and Outgoing Mail Server. For security reasons, confirm that the "The server requires a secure connection" check box is checked as well. When you are finished, click OK. To move on to the next screen, click next.

Step 3. The next screen is about making a Database mail profile, either public or private. You can leave this option and move to the next screen. Again, select next to finish the creation process for the Database Mail profile.

Now you are all set up to send e-mail. Now go to Management, right-click on Database Mail, and select Send Test E-mail... option to send test mail as in the following image.

Choose Test Profile as Database Mail Profile from Dropdown and enter To email address, Subject, and Body, and click Send Test E-Mail. Now your e-mail has been sent successfully from SQL Server.

Step 4. If you want to send mail using SQL query, then you can use the system database 'msdb' and its stored procedure 'sp_send_dbmail' as follows.
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Test Profile',
    @recipients = '[email protected]',
    @subject = 'Test Mail',
    @body = 'This is the test mail.',
    @body_format = 'HTML';

To check the status of the e-mail you sent, you can execute the following query in SQL Server.
USE msdb;
GO

SELECT *
FROM sysmail_allitems;

In this query result, you can find all the details about the mail sent from the SQL Server. You can check the sent_status column to check the status of mail.
So, this is all about using the database mail in the SQL Server to send e-mails. I hope you understood my explanation and liked this article. Thank you!

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.