SQL commands are used in SQL to perform various functions. These functions include building database objects, manipulating objects, populating database tables with data, updating existing data in tables, deleting data, performing database queries, controlling database access, and overall database administration.

The main categories of SQL Commands are,
    Data Definition Language (DDL)
    Data Manipulation Language (DML)
    Data Query Language (DQL)
    Data Control Language (DCL)
    Transaction Control Language (TCL)

Data Definition Language (DDL)
Data Definition Language (DDL) is a set of SQL commands used to define a database's structure, including tables, indexes, and constraints.DDL commands are used to create, modify, and delete database objects. Here are some common DDL commands:

CREATE
This command creates new database objects, such as tables, views, indexes, and constraints.

Example
CREATE TABLE Employee (
   EmployeeID INT PRIMARY KEY,
   FirstName VARCHAR(50),
   LastName VARCHAR(50),
   Email VARCHAR(100),
   HireDate DATE,
   Salary DECIMAL(10, 2),
   DepartmentID INT
);

CREATE VIEW RecentOrders AS
SELECT OrderID, OrderDate, CustomerID, TotalAmount
FROM Orders

CREATE INDEX idx_Employee_DepartmentID ON Employee (DepartmentID);

CREATE PROCEDURE InsertOrder
  @OrderDate DATE,
  @CustomerID INT,
  @TotalAmount DECIMAL(10,2)
AS
BEGIN
  INSERT INTO Orders (OrderDate, CustomerID, TotalAmount)
  VALUES (@OrderDate, @CustomerID, @TotalAmount)
END;

CREATE FUNCTION GetYearsWithCompany (@EmployeeID INT)
RETURNS INT
AS
BEGIN
  DECLARE @YearsWithCompany INT;
  SELECT @YearsWithCompany = DATEDIFF(YEAR, HireDate, GETDATE())
  FROM Employees
  WHERE EmployeeID = @EmployeeID;
  RETURN @YearsWithCompany;
END;

CREATE TRIGGER OrderAuditTrigger
ON Orders
AFTER INSERT
AS
BEGIN
  INSERT INTO OrderAudit (OrderID, OrderDate, CustomerID, TotalAmount)
  SELECT OrderID, OrderDate, CustomerID, TotalAmount
  FROM inserted;
END;

ALTER
This command is used to modify the structure of existing database objects, such as adding or removing columns from a table or changing the data type of a column.

Examples
ALTER TABLE Employees
ADD EmailAddress varchar(100);

ALTER TABLE Employees
DROP COLUMN EmailAddress;

ALTER TABLE Employees
ALTER COLUMN Salary decimal(10, 2);

ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

ALTER VIEW SalesData
AS SELECT ProductID, ProductName, QuantitySold
FROM Sales
WHERE SaleDate BETWEEN '2022-01-01' AND '2022-12-31';

ALTER PROCEDURE GetEmployeesByDepartment
   @DepartmentID int
AS
BEGIN
   SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
END;

ALTER INDEX idx_Employees_LastName
ON Employees(LastName, FirstName)
INCLUDE (Email);

ALTER FUNCTION GetTotalSales
(@StartDate DATE, @EndDate DATE)
RETURNS MONEY
AS
BEGIN
   DECLARE @TotalSales MONEY;
   SELECT @TotalSales = SUM(TotalAmount)
   FROM Sales
   WHERE SaleDate BETWEEN @StartDate AND @EndDate;
   RETURN @TotalSales;
END;

ALTER TRIGGER trg_Employees_Insert
ON Employees
AFTER INSERT
AS
BEGIN
   INSERT INTO EmployeeAudit(EmployeeID, AuditDate, EventType)
   SELECT EmployeeID, GETDATE(), 'INSERT'
   FROM inserted;
END;

DROP
This command deletes an existing database object, such as a table, view, or index.
DROP TABLE Employee;
DROP VIEW Get_EmployeeDetail;
DROP INDEX idx_Employees_Name;
DROP PROCEDURE GetEmployeesByDepartment;
DROP FUNCTION my_function;
DROP TRIGGER my_trigger ON my_table;

TRUNCATE
This command deletes all data from a table but keeps the table structure intact.
TRUNCATE TABLE suppliers;

RENAME
This command renames an existing database object, such as a table or column.
EXEC sp_rename 'old_table_name', 'new_table_name';
EXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';

Note: SQL Server doesn't support the RENAME keyword in the ALTER TABLE statement. Instead, you can use the sp_rename system stored procedure to rename a table and table column.

COMMENT
This command adds comments to a database object, such as a table or column, to provide additional information about the object.

Single-line comments: These comments start with two hyphens "--" and continue until the end of the line. For example:
SELECT * FROM customers -- This is a comment
Multi-line comments: These comments start with "/" and end with "/". They can span multiple lines. For example:
/* This is a
multi-line comment */

In short, DDL commands are used for creating and modifying the structure of a database.

Data Manipulation Language (DML)
Data Manipulation Language (DML) is a set of SQL commands used to manipulate data stored in a database. DML commands retrieve, insert, update, and delete data in tables. Here are some common DML commands:
SELECT

This command retrieves data from one or more tables in a database.
SELECT column1, column2, ..., columnN FROM table_name;
SELECT name, email FROM customers;
SELECT * FROM customers;

INSERT
This command is used to insert new data into a table.
INSERT INTO customers  VALUES ('RAJ', '[email protected]', '7019160263');
INSERT INTO customers (name, email, phone) VALUES ('RAJ', '[email protected]', '7019160263');
INSERT INTO OrderDetail (CustomerName, City, Country)
SELECT Name, City, Country FROM Customers;

UPDATE
This command is used to modify existing data in a table.
UPDATE customers
SET email = '[email protected]', first_name = 'Rj'
WHERE id=1

DELETE
This command is used to delete data from a table.
DELETE FROM customers; -- delete all data
DELETE FROM customers -- delete record from customers which id is 5
WHERE id = 5;

MERGE
This command performs insert, update, or delete operations on a target table based on the data in a source table.

-- Insert Merge
MERGE employees AS target
USING employees_new AS source
ON (target.id = source.id)
WHEN NOT MATCHED THEN
  INSERT (id, name, salary)
  VALUES (source.id, source.name, source.salary);

-- Update Merge

MERGE INTO customers c
USING (
  SELECT id, phone, address
  FROM customers
  WHERE email IN ('[email protected]', '[email protected]', '[email protected]')
) s
ON (c.id = s.id)
WHEN MATCHED THEN
  UPDATE SET c.phone = s.phone, c.address = s.address;

-- Delete Merge

MERGE INTO orders o
USING (
  SELECT order_id
  FROM orders
  WHERE order_date < '2022-01-01'
) s
ON (o.order_id = s.order_id)
WHEN MATCHED THEN DELETE;

DML commands are essential for managing the data stored in a database. By using DML commands, users can add, update, or delete data in a table, which is crucial for maintaining the data's accuracy and integrity.

Data Query Language (DQL)
Data Query Language (DQL) is a subset of SQL commands used to retrieve data from one or more tables in a database. DQL commands are also known as data retrieval commands.

Here are some common DQL commands,
SELECT

This command retrieves data from one or more tables in a database.
SELECT column1, column2, ..., columnN FROM table_name;
SELECT name, email FROM Employees;
SELECT * FROM Employees;

DISTINCT
This command is used to retrieve unique values from a column in a table.

SELECT DISTINCT category
FROM products;

WHERE
This command is used to filter data based on specific criteria.

SELECT *
FROM customers
WHERE age > 30;

UPDATE customers
SET email = '[email protected]'
WHERE id = 1;

DELETE
FROM customers
WHERE age > 30 AND email LIKE '%@yahoo.com';

ORDER BY
This command is used to sort data in ascending or descending order.

SELECT *
FROM customers
ORDER BY age DESC;

UPDATE customers
SET age = age + 1
WHERE id IN (
    SELECT id
    FROM customers
    ORDER BY age ASC
);

DELETE FROM customers
WHERE age > 50
ORDER BY age DESC;

GROUP BY
This command is used to group data based on one or more columns.

SELECT product, SUM(quantity * price) as total_sales
FROM sales
GROUP BY product;

UPDATE sales
SET price = (
    SELECT AVG(price)
    FROM sales
    WHERE product = sales.product
)
WHERE product IN (
    SELECT product
    FROM sales
    GROUP BY product
);

DELETE FROM sales
WHERE product IN (
    SELECT product
    FROM sales
    GROUP BY product
    HAVING COUNT(*) = 1
);

JOIN
This command combines data from two or more tables into a single result set.

SELECT orders.id, customers.name, orders.product, orders.quantity, orders.price
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

SELECT departments.name AS department_name, employees.name AS employee_name
FROM departments
RIGHT JOIN employees ON departments.id = employees.department_id;

SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

SELECT employees.name AS employee_name, departments.name AS department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.id;

SELECT A.EmployeeName AS EmployeeName1, B.EmployeeName AS EmployeeName2, A.City
FROM Employee A, Employee B
WHERE A.EmployeeID <> B.EmployeeID
AND A.City = B.City
ORDER BY A.City;

DQL commands are essential for retrieving data from a database. Using DQL commands, users can filter, sort, and group data based on specific criteria,  which is crucial for analyzing and interpreting the data stored in the database.

Data Control Language (DCL)
Data Control Language (DCL) is a set of SQL commands used to control access to a database. DCL commands are used to grant or revoke permissions to users and roles.
Here are some common DCL commands:

GRANT
This command is used to grant permissions to a user or a role.
GRANT SELECT ON mydatabase.mytable TO myuser;
GRANT SELECT, INSERT, UPDATE ON mydatabase.mytable TO myuser;
GRANT SELECT, INSERT ON mydatabase.* TO myuser;

REVOKE
This command is used to revoke permissions from a user or a role.
REVOKE SELECT ON mydatabase.mytable FROM myuser;
REVOKE SELECT, INSERT, UPDATE ON mydatabase.mytable FROM myuser;
REVOKE ALL PRIVILEGES ON mydatabase.* FROM myuser;
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

This command is used to deny permissions to a user or a role.
DENY SELECT ON mydatabase.mytable TO myuser;
DENY SELECT, INSERT, UPDATE ON mydatabase.mytable TO myuser;

DCL commands are essential for managing access to a database. Using DCL commands, database administrators can control who has access to the database and what actions they can perform on the data stored in the database.

This is critical for maintaining the security and integrity of the data stored in the database.

Transaction Control Language (TCL)
Transaction Control Language (TCL) is a set of SQL commands used to manage transactions in a database. A transaction is a sequence of one or more SQL statements treated as a single unit of work. TCL commands are used to commit or rollback transactions. Here are some common TCL commands:

COMMIT
This command permanently saves the changes made by a transaction to the database.
CREATE PROCEDURE update_employee_salary
    @employee_id INT,
    @new_salary DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;

    UPDATE company.employees
    SET salary = @new_salary
    WHERE id = @employee_id;

    COMMIT;
END

ROLLBACK
This command is used to undo the changes made by a transaction and restore the database to its previous state.
CREATE PROCEDURE update_employee_salary
    @employee_id INT,
    @new_salary DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;

    UPDATE company.employees
    SET salary = @new_salary
    WHERE id = @employee_id;

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK;
        RETURN;
    END;

    COMMIT;
END

SAVEPOINT
This command is used to set a savepoint within a transaction, which allows you to roll back to a specific point in the transaction.
CREATE PROCEDURE transfer_funds
    @from_account INT,
    @to_account INT,
    @amount DECIMAL(10, 2)
AS
BEGIN
    BEGIN TRANSACTION;

    -- Savepoint
    SAVE TRANSACTION transfer_start;

    UPDATE bank.accounts
    SET balance = balance - @amount
    WHERE id = @from_account;

    IF @@ERROR <> 0
    BEGIN
        -- Rollback to savepoint
        ROLLBACK TRANSACTION transfer_start;
        RETURN;
    END;

    UPDATE bank.accounts
    SET balance = balance + @amount
    WHERE id = @to_account;

    IF @@ERROR <> 0
    BEGIN
        -- Rollback entire transaction
        ROLLBACK;
        RETURN;
    END;

    COMMIT;
END

SQL

RELEASE SAVEPOINT

This command is used to remove a savepoint within a transaction.

CREATE PROCEDURE example_procedure
AS
BEGIN
    BEGIN TRANSACTION;

    -- Perform some operations
    INSERT INTO myTable (column1, column2) VALUES (1, 'A');
    INSERT INTO myTable (column1, column2) VALUES (2, 'B');
    INSERT INTO myTable (column1, column2) VALUES (3, 'C');

    -- Set a savepoint
    SAVE TRANSACTION mySavepoint;

    -- More operations
    INSERT INTO myTable (column1, column2) VALUES (4, 'D');
    INSERT INTO myTable (column1, column2) VALUES (5, 'E');

    -- Check for errors
    IF @@ERROR <> 0
    BEGIN
        -- Rollback to savepoint
        ROLLBACK TRANSACTION mySavepoint;
        RETURN;
    END;

    -- Mark the savepoint as complete
    RELEASE SAVEPOINT mySavepoint;

    COMMIT;
END;

TCL commands are essential for managing transactions in a database. Using TCL commands, users can ensure that changes made to the database are consistent and reliable, even if there are errors or failures during the transaction. This is critical for maintaining the integrity of the data stored in the database.

Note
it's generally a good practice to use transactions in stored procedures to ensure data integrity and prevent data corruption. Using transactions and committing or rolling back changes as needed can help ensure that your database remains consistent and reliable.

Conclusion
I hope the blog has helped you understand SQL commands.

HostForLIFEASP.NET SQL Server 2019 Hosting

There are some questions that those who are familiar with the T-SQL dialect of SQL ask themselves:
    Why is my query working slowly?
    Does the query I wrote use an index?
    Why is the index I created not used?
    Why is this query slower (faster) than the other one, despite returning the same result?

If you want answers to these questions, one of the tools that must be used to understand them is Execution plans.

What is an Execution Plan?
EThe executionPlan is a description of the most efficient execution of the query we wrote, calculated by the optimizer. That is, Execution Plan shows us in which version your query is implemented, and using which algorithm it is executed.

In short, how SQL Server will or has executed our query.

Let's look at everything from the beginning,

After we write a query, the execution of the request goes through several stages. Those stages are concentrated into 2 large groups:

• Events in relation engine (RE)
• Events in Storage Engine (SE)

In the RE(relation engine), the query is parsed and executed by the query optimizer. The query optimizer prepares an execution plan. This Execution Plan is then sent in binary format to the SE(storage engine), which uses this plan during the execution of queries. Transaction and index operations are realized in the storage engine.

IToexplain what we said in a consistent and simple form, let's first open SQL SERVER and let such a request be executed. (Queries will be applied to the AdventureWorks2012 database)
use AdventureWorks2012
GO
SELECT
         hremphist.businessentityid
       , person.person.firstname
       , person.person.lastname
       , humanresources.employee.jobtitle
       , humanresources.department.name AS DepartmentName
       , hremphist.startdate
FROM (
                 SELECT
                         businessentityid
                       , departmentid
                       , startdate
                       , ROW_NUMBER() OVER (PARTITION BY businessentityid ORDER BY startdate DESC) AS rn
                 FROM humanresources.employeedepartmenthistory
         ) AS hremphist
         JOIN humanresources.department
                 ON humanresources.department.departmentid = hremphist.departmentid
         JOIN humanresources.employee
                 ON humanresources.employee.businessentityid = hremphist.businessentityid
         JOIN person.person
                 ON person.businessentityid = hremphist.businessentityid
WHERE hremphist.rn = 1;

We can see the Execution Plan of the query by pressing Ctrl+L
Let's see together what execution processes the above request goes through,

1) Query Parsing
As soon as we execute the query we wrote, it is taken by RE and checked whether it is true or false from the syntactic side (Parsing). If there are no syntactic problems, the query has been successfully parsed. It should be remembered that if the query is DDL, it is not optimized! Because CREATE TABLE, which is a DDL operation of the example, has only one syntax. Only DML operations can be optimized. The result of the query parsing process gives us a parse tree (sequence tree, query tree).

2) Algebrizer
If the DML request has passed the parsing process successfully, it is sent to Algebrizer, a special mechanism. Algebrizer performs logical processes such as the existence of objects used by us, the names of table columns, compatibility of types, etc. As a result of these processes, Algebrizer returns us a binary format. Returns a "query processor tree", which results are processed by the query optimizer.

3) Query Optimizer prepares Execution Plan for us.

4) Storage Engine
Physical processing of the request is performed at this stage, and the request is executed based on the Execution Plan.

The SQL Query Optimizer performs the generation and preparation of Execution Plans. When preparing an Execution Plan, it necessarily refers to statistics and indexes and uses the information collected there by the execution plan maker. In the next step, the Execution Plan given by the Query Optimizer is sent to the storage engine; based on this, our query is physically executed. It should be remembered that when an Execution Plan is prepared, the prepared plan is added to the cache (processor cache) so that it is stored in the memory. During the next similar Execution Plan preparation, the Optimizer does not have to do additional work and can use the previously generated plan.

Depending on the complexity of the query, T-SQL QUERY OPTIMIZER generates several Execution Plans for us and tries to choose the fastest one among them. However, it is necessary to consider that there are often queries for which Execution Plans can be generated for their implementation, which may take several minutes. Therefore, Query Optimizer makes the necessary selection among the first generated plan combinations, not all. Gave us the perfect Execution Plan. Our EPs will be stored in the plan cache not to generate a plan for similar questions every time.

To delete cached data, run the following command:
DBCC FREEPROCCACHE

We can also delete the required plan by passing sql_handle and plan_handle specifically.

What can cause the current Execution Plan to change?

    Execution of parallel queries
    The statistics had changed or became outdated when the Execution Plan was made.
    Entering information into the temporary table

It should be remembered that there are 2 main forms of EP(Execution Plan) in T-SQL,
    The estimated Execution Plan (Estimated execution plan) is the plan the SQL Optimizer estimates before the query is executed.
    Actual Execution Plan (Real execution plan) – is a plan received only after the request is released for execution.

Although these plans store a completely different set of data, they do not differ from each other at first glance.

There are 3 main forms of execution plans,
    Graphical description (Graphical plans)
    Description in text format (Text Plans)
    In XML format (XML Plans)

The most commonly used representation form of execution plans is a graphical plan. Although the graphic description form does not reflect all the details at first glance, detailed information can be seen in the outer panel.

Although Text Plans have been declared "deprecated" by SQL Server, we can still use them. In the Text version, you can get detailed information about the plan the first time, speed it up, and edit it in a text editor.
SET SHOWPLAN_ALL ON;
GO
SELECT TOP 100 [BusinessEntityID]
      ,[NationalIDNumber]
      ,[LoginID]
      ,[OrganizationNode]
      ,[OrganizationLevel]
      ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
      ,[SalariedFlag]
      ,[VacationHours]
      ,[SickLeaveHours]
      ,[CurrentFlag]
      ,[rowguid]
      ,[ModifiedDate]
FROM [ADV_Works].[HumanResources].[Employee]

In the test code example above, we enabled the detailed description of the "estimated" plan in text form. To disable this mode, write OFF instead oExecutionrun the query.
SET SHOWPLAN_TEXT ON;--active
GO
SET SHOWPLAN_TEXT OFF;--deactive
GO
SET STATISTICS PROFILE ON;--active text mode for actual plan

XML Plans - XML plans have 2 forms of description:

• SHOWPLAN_XML-is generated until execution
• STATISTICS_XML is generated after query execution

The most used form of EPs is a graphical form (Graphical Execution plans).

• Ctrl+M (for Actual) Ctrl+L (for Estimated)
• By selecting the "Include Actual Execute plan" or "Display Estimated Execute Plan" button on the toolbar
• By right-clicking on the session, we wrote the query and selected "Include Actual Execution Plan" or "Display Estimated Execute plan" from the drop-down menu.

HostForLIFEASP.NET SQL Server 2019 Hosting

Do you find it difficult to manage and organize your SQL Server databases? Are you constantly struggling to navigate through a sea of tables, views, and stored procedures? If so, you're not alone. Many developers and database administrators face this same problem every day, and it can be frustrating and time-consuming.

But what if there was a solution to this problem? What if there was a way to simplify database management and make it easier to access the data you need? That's where schemas come in.

In this article, we'll explain what a schema is in SQL Server and how it can help solve your database management woes. We'll delve into the benefits of using schemas and show you how to create and manage them in your SQL Server environment.

With our step-by-step guide, you'll learn how to use schemas to organize your database objects into logical groups, improve security by controlling access to specific schemas, and simplify database maintenance by reducing complexity. So, if you're ready to take your SQL Server skills to the next level and tackle the problem of database management head-on, let's dive in.

A schema in a SQL database is a collection of logical data structures. The schema is owned by a database user and has the same name as the database user. From SQL Server 2005, a schema is an independent entity (container of objects) different from the user who creates that object. In other words, schemas are very similar to separate namespaces or containers that are used to store database objects.

Security permissions can be applied to schemas; hence, schemas are essential for separating and protecting database objects based on user access rights. It improves flexibility for security-related administration of the database.
User schema separation

Before SQL Server, database object owners and users were the same things, and database objects (table, index, view, and so on) were owned by the user. In other words, database objects were directly linked to the user, and the user could not delete them without removing the database object that was associated with the user.

In SQL Server, a schema separation is introduced; now, the database object is no longer owned by a user, group, or role. The schema can be owned by the user, group, or role. The schema can have multiple owners. The schema ownership is transferrable. Database objects are created within the schema. Now the user can be dropped off without dropping off the database object owned by the user. But the schema cannot be deleted if it contains a database object.

The following are the advantages of user schema separation:

    The schema ownership is transferrable.
    Database objects can be moved among the schemas.
    A single schema can be shared among multiple users.
    A user can be dropped without dropping the database objects associated with the user.
    Provides more control of access and level of access.

Default schema

The default schema is the first schema searched when resolving object names. The user can be defined within the default schema. Using the "SCHEMA_NAME" function, we can determine the default schema for the database.

The schema can be the default for the user by defining DEFAULT_SCHEMA with CREATE USER or ALTER USER. If no default schema is defined, then SQL will assume "DBO" as the default schema. Note that no default schema is associated with a user if the user is authenticated as a member of the group in the Windows operating system. In this case, a new schema will be created, and the name will be the same as the user name.

Advantages of using Schema

• Act as object protection tool: A schema can be a very effective object projection tool combined with the appropriate level of user permissions. A DBA can maintain control access to an object, which would be crucial.
• Managing a logical group of database objects within a database: Schemas allow database objects to be organized into a logical group. This would be advantageous when multiple teams are working on the same database application, and the design team wants to maintain the integrity of the database tables.
• Easy to maintain the database: A schema allows a logical grouping of the database objects. The schema can help us when the database object name is the same but falls into a different logical group.

Other Advantages

• A single schema can be shared among multiple databases and database users.
• A database user can be dropped without dropping database objects.
• Manipulation of and access to the object is now complex and more secure. The schema acts as an additional layer of security.
• Database objects can be moved among schemas.
• The ownership of schemas is transferable.

Example of a Schema in SQL
Let's say we have a database that contains information about a company's employees, departments, and projects. To organize this information, we can create separate schemas for each of these categories.

For example, we can create a schema called "employees" to store tables related to employee information, such as employee names, job titles, and salaries. We can also create a schema called "departments" to store tables related to department information, such as department names and locations. Finally, we can create a schema called "projects" to store tables related to project information, such as project names, budgets, and timelines.

By creating separate schemas for each category, we can easily manage and access the information we need without having to navigate through a large and complex database. This also helps to improve security by restricting access to certain schemas based on user roles and permissions.

To create a schema in SQL Server, we can use the following syntax:
CREATE SCHEMA schema_name

For example, to create a schema called "employees", we can use the following query:
CREATE SCHEMA employees

We can then create tables within the schema using the following syntax:
CREATE TABLE schema_name.table_name

For example, to create a table called "employee_info" within the "employees" schema, we can use the following query:
CREATE TABLE employees.employee_info (
   employee_id INT PRIMARY KEY,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   job_title VARCHAR(50),
   salary DECIMAL(10,2)
);

This creates a table within the "employees" schema that stores information about employee IDs, first names, last names, job titles, and salaries.

A schema in SQL Server is a way to organize database objects such as tables, views, and stored procedures into logical groups. By creating separate schemas for different categories of information, we can easily manage and access the data we need, improve security, and simplify database maintenance.

HostForLIFEASP.NET SQL Server 2019 Hosting

A few days ago, I found an extraordinary thing with SQL SERVER 2005, and I strongly feel that this is a bug in SQL Server that can create lots of data discrepancies.

Let's go through the script.
    Create a new fresh database.
    CREATE DATABASE DB_TEST

Use this Database.
USE DB_TEST

Create a table name tblCategory.
CREATE TABLE tblCategory
(
    CategoryID INT PRIMARY KEY,
    CategoryNAME VARCHAR(50)
)

Create another table named tblProduct.
CREATE TABLE tblProduct
(
    ProductID INT PRIMARY KEY,
    CategoryID INT FOREIGN KEY REFERENCES tblCategory(CategoryID),
    IsDamaged BIT
)

Insert 5 rows in tblCategory.
INSERT INTO tblCategory VALUES (1,'Category1')
INSERT INTO tblCategory VALUES (2,'Category2')
INSERT INTO tblCategory VALUES (3,'Category3')
INSERT INTO tblCategory VALUES (4,'Category4')
INSERT INTO tblCategory VALUES (5,'Category5')

Insert 10 rows in tblProduct.
INSERT INTO tblProduct VALUES (1,1,0)
INSERT INTO tblProduct VALUES (2,1,0)
INSERT INTO tblProduct VALUES (3,2,0)
INSERT INTO tblProduct VALUES (4,2,0)
INSERT INTO tblProduct VALUES (5,3,0)
INSERT INTO tblProduct VALUES (6,3,0)
INSERT INTO tblProduct VALUES (7,4,0)
INSERT INTO tblProduct VALUES (8,4,0)
INSERT INTO tblProduct VALUES (9,4,0)
INSERT INTO tblProduct VALUES (10,5,0)

Select statements to confirm that data is entered or not.
SELECT * FROM tblCategory
SELECT * FROM tblProduct

Here is a select query that is incorrect. The query tells us to select ProductID from tblCategory where categoryId = 1, But the tblCategory table does not have a column named ProductID. So when we execute this query, it throws an error, which is the expected behavior.
SELECT ProductID FROM tblCategory WHERE CategoryID = 1

Here is the magic. I have used the above incorrect select query with an update statement as an inner query. What do you think? What should happen when you execute this query? This query should throw an error as my inner select query is incorrect. But execute this query, and you will be shocked.
UPDATE tblProduct SET IsDamaged = 1
WHERE ProductID IN
(SELECT ProductID FROM tblCategory WHERE CategoryID = 1)

Oops!!!! All the data in IsDamaged is set to 1, but my inner select query(SELECT ProductID FROM tblCategory WHERE CategoryID = 1) is wrong. 10 rows were affected.

Initially, I thought this was a bug, but it's not. The inner query first tries to find the column in the current table (inner query's table), and if it is not found, it will look for the outer query table. It is the best practice to use the tableName.ColumnName in the inner query.
UPDATE tblProduct SET IsDamaged = 1
WHERE ProductID IN
(SELECT tblCategory.ProductID FROM tblCategory WHERE CategoryID = 1)

Now this inner query will throw an error. So next time, be careful whenever you are working with inner queries.

HostForLIFEASP.NET SQL Server 2019 Hosting

The apply keyword was introduced mainly for selecting data by combining a select query with a table-valued function, which is nothing but a select query on another table (or the result of any business logic in the function). To understand it better, let's call our main table the left table and the second table (in the table-valued function) the right table.

Let's start by creating sample data. We will have two tables, Employee and Department. Here, our Employee table will be the left table, and the Department table will be the right one. Each employee can only belong to one department.

Our intent will be to join these two tables to get the required data. One solution that immediately comes to mind is using a LEFT, RIGHT, or INNER JOIN, which will depend on our requirements. Let's see the results with the use of JOINS.

APPLY works the same way as the co-related subquery, with the fact that the select query on which the application is used, in other words, the right table, will be executed for every record of the main table or the left table, that is nothing but how the co-related sub query works. The same results can be obtained using the apply keyword. It can be applied in either of the following two ways:

    CROSS APPLY- Works the same as the INNER JOIN on two queries.
    OUTER APPLY- Works the same as the LEFT JOIN on two queries.

Let's change the queries using these two apply forms and see the results.

As we can see above, CROSS APPLY gives the same result as the INNER JOIN, and OUTER APPLY gives the same result as the LEFT OUTER JOIN. The difference with the JOIN is that APPLY results in the execution of the select statement of the Department query for each record of the Employee record (the same as that of a co-related sub-query).

Next, suppose we were using the co-related subquery. But we need to view the rest of the columns of the second table. In other words, the Department table. Can we do that? Unless we add some twist to the query, it doesn't seem to be. But this can be easily done with the APPLY keyword. Add the name of the columns we want to view in the select statement of the Department, and we are done. Let's change our queries and see the results:

Another possible and extensive use of APPLY is with the table-valued function. We create a simple function that returns Department details by Id. Next, we replace our select statement for Department with a call to the user-defined function. See the query below:

So, depending on the requirements, we can add or remove the columns' names in the function call's SELECT statement. To summarize, we can use the apply keyword as.

    A co-related subquery with the advantage of selecting multiple columns.
    A join with the table-valued user-defined function to select multiple columns from the second table.

So this was about the use of the apply keyword.

HostForLIFEASP.NET SQL Server 2019 Hosting

Note
Before going next, first, make sure you have SQL Server Integration Services installed. Open the Visual Studio SSIS package project and right-click on the project and hit Deploy to deploy all packages, if you want to install individual packages then right-click on the package and hit deploy.

The first window is the introduction windows click the Next button.

We have two deployment targets,

    SSIS in SQL Server
    SSIS in Azure Data Factory

As in this article, we are going to deploy on SQL Server, so we must select SSIS in SQL Server and click Next.

Select a destination, Enter the SQL Server name, Authentication type, Username, and password, and click Connect. Once connect Browse the project folder path if available, if not available create a directory in SSISDB and create a new project, and hit Next.

You can review all the given changes and hit Deploy.

You can check the deployment result in the last windows. If all results are passed, then click close.

The above screenshot shows that all results are passed and successfully deployed.

Go to SQL Server and expand Integration Services Catalogs and go to SSISDB you can see the created folder and project and deployed packages there.

Conclusion
In this article, we have learned how to deploy SSIS Project to SQL Server.

HostForLIFEASP.NET SQL Server 2019 Hosting

Change Data Capture (CDC) captures the data of insert, update and delete activity. When you insert or delete the data in the table it maintains a record of the same data. When you update the data it maintains records before updating the data and after updating the data.

To understand the change data capture we go through the following process.

Process

Step 1. Create DB
CREATE DATABASE CDC_DEMO
GO

Step 2. Create a Table
Create one table in the preceding database.

Execute the following query and the "CDC_DEMO_TABLE1" table is created.
USE CDC_DEMO
GO

CREATE TABLE CDC_DEMO_TABLE1
(
    ID      INT         IDENTITY(1,1) PRIMARY KEY,
    Name        VARCHAR(50)     NOT NULL,
    Age     INT         NOT NULL,
);
GO

You can check the table in the Object Explorer.

Step 3. Insert Rows
Insert some rows into the table "CDC_DEMO_TABLE1".

Here we inserted two rows into the table.
USE CDC_DEMO
GO

INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Peter',34)
GO
INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Scott',38)
GO

Step 4. Enable CDC on DB
We have a database, table, and some rows in the table, now we need to enable CDC on the database.
Execute the following query and it will show whether CDC is enabled or not for the database.
USE CDC_DEMO
GO

SELECT name, database_id, is_cdc_enabled
FROM SYS.DATABASES
WHERE name = 'CDC_DEMO'

"is_cdc_enabled" has the value "0", which means it is not enabled for the database.

Execute the following query to enable CDC on the database. We need to execute the "sys.sp_cdc_enable_db" Stored Procedure to enable CDC on the database. It is necessary to execute it before we know any tables are enabled for the CDC.
USE CDC_DEMO
GO
EXEC sys.sp_cdc_enable_db
GO

This will create some system tables.

Check again and verify that CDC is enabled on the database.
USE CDC_DEMO
GO

SELECT name, database_id, is_cdc_enabled
FROM SYS.DATABASES
WHERE name = 'CDC_DEMO'

Now "is_cdc_enabled" has the value 1, in other words, it is enabled.

Step 5. Enable CDC on Table
Enable CDC on the "CDC_DEMO_TABLE1" table.
Before enabling CDC, we need to check whether it is enabled already or not. Execute the following query and we have a list of all tables with CDC status.
USE CDC_DEMO
GO
SELECT [name], is_tracked_by_cdc  FROM SYS.TABLES
GO

The value of "is_tracked_by_cdc" is "0" for the "CDC_DEMO_TABLE1" table, in other words, CDC is not enabled for this table.
Execute the following query to enable CDC on the table.
USE CDC_DEMO;
GO
EXECUTE sys.sp_cdc_enable_table
  @source_schema = N'dbo'
  , @source_name = N'CDC_DEMO_TABLE1'
  , @role_name = NULL
GO

We can check in the Object Explorer that one more table is created under the system tables, "cdc.dbo_CDC_DEMO_TABLE1_CT".

Check again and verify that CDC is enabled on the table.

USE CDC_DEMO
GO
SELECT [name], is_tracked_by_cdc  FROM SYS.TABLES
GO

Now "is_tracked_by_cdc" has the value 1, which represents that CDC is enabled for the table.

Step 6. Insert Operation
We have enabled CDC for the database and table. Now let's check where SQL Server persists in the change log when we insert the data in the table.
Execute the following query to insert one row into the table.
USE CDC_DEMO
GO

INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Alex',35)
GO

Open the table "CDC_DEMO_TABLE1" and we can see that one row is inserted with the ID 3.

The change log is captured in the table "cdc.dbo_CDC_DEMO_TABLE1_CT". You can see the entire row that we have created. One more thing you can observe here is that the _$operation value is 2, in other words for Insert values.

Step 7. Update Operation
Now let's check by updating any of the rows in the table. Execute the following script that will update the value of the name field where id = 3.
USE CDC_DEMO
GO

UPDATE CDC_DEMO_TABLE1
SET Name = 'Jigi'
WHERE id = 3
GO

Open the table and verify that the value is changed.

Open the "cdc.dbo_CDC_DEMO_TABLE1_CT" table and you can see that the updated data is captured in two rows. One is with operation 3 and the other with operation 4. Operation value 3 means before updating and value 4 means after updating.

Step 8. Delete Operation
To check the captured data after the delete operation, execute the following script that deletes the record with id=3.
USE CDC_DEMO
GO

DELETE FROM CDC_DEMO_TABLE1
WHERE id = 3
GO

Open the table and verify that the record is deleted from the table.

Open the "cdc.dbo_CDC_DEMO_TABLE1_CT" table and you can see that the deleted row is captured with operation value 1.

We have seen a change in data capture for insert, update and delete operations and for those only one system table is used, "cdc.dbo_CDC_DEMO_TABLE1_CT". But there are more than six tables that were created when enabling CDC on the database. So let's see the schema and values for those tables:

Cdc.captured_columns
Provides the information of columns that are tracked for the changed data capture.

Cdc.change_tables
Provides the information in the table. It shows the default value for "capture_instance" since we have not provided a parameter when enabling CDC on the table.

Cdc.ddl_history
Provides the information for any schema changes. Currently, this table doesn't have any value since we did not change any schema for the table. So let's change the schema and check the values. Execute the following query to change the schema for the table:
USE CDC_DEMO
GO

ALTER TABLE CDC_DEMO_TABLE1
ALTER COLUMN Name VARCHAR(100) NOT NULL
GO

We have changed the datatype from varchar(50) to varchar(100) for the name field.

Open the "cdc.ddl_history" table and we can see that the ddl_command is captured as in the following:

Cdc.index_columns
Provides the information if any of the index columns are changed.

Cdc.Isn_time_mapping
Provides information about the start and end time for the operation done for changes.

Cdc.systranschemas
Provides the information for the schema changes.

Step 9. Disable CDC on Table

Execute the following query to disable CDC on the table.
USE CDC_DEMO;
GO

EXECUTE sys.sp_cdc_disable_table
    @source_schema = N'dbo',
    @source_name = N'CDC_DEMO_TABLE1',
    @capture_instance = N'dbo_CDC_DEMO_TABLE1'
GO

We can observe in the Object Explorer that one table is removed under the system tables, "cdc.dbo_CDC_DEMO_TABLE1_CT". That means CDC is disabled for this table.

Step 10. Disable CDC on Database
Execute the following query to disable CDC on the database. 
USE CDC_DEMO
GO
EXEC sys.sp_cdc_disable_db
GO

We can observe in the Object Explorer that all the tables are removed under the system tables. That means CDC is disabled on the database.

HostForLIFEASP.NET SQL Server 2019 Hosting

Full-text search is one of the needs of an application to find data in a database. The full-Text Index feature in SQL Server allows you to run a full text search on a database table. In this article, we will learn about full-text index in SQL Server, including what full-text index is, how to create a full-text search index, and other use cases.

What is Full-Text Index in SQL Server?
Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables. Full-Text index helps to perform complex queries against character data. These queries can include word or phrase searching. Before we can run full-text queries on a table, we first need to create a full-text index on the table. Only one full-text index is allowed per table, and this index can contain up to 1024 columns. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, char, nvarchar, text, ntext, image, XML, or varbinary.

Full-text queries perform searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase. A full-text query returns any document that contains at least one match (also known as a hit). A match occurs when a target document contains all the terms specified in the full-text query and meets any other search conditions, such as the distance between the matching terms.

Why do we need a Full Text Index (FTI) if we can use a statement for searching?
Let us consider a scenario; I have a table with column name data. What query will we use if we want to search for the name ‘smith’ in the data column, basically, we use the command below.

The above query is efficient for the above scenario, but what if you're not looking for an exact match? FTS has some better algorithms for matching data, as does some better statistics on variations of names. Therefore, FTS can provide better performance for matching Smith, Smythe, Smithers, etc., when you look for Smith. In such a case, FTS provides better results compared to the traditional like method.
When to use FTI over the LIKE statement?

    A word or phrase close to the search word or phrase
    When the result size is several hundred thousand
    Millions of rows, each with a string like "wordAwordBwordC..."
    Any word derived from a particular root (for example, run, ran, or running)

How to Create a Full-Text Index?
Now, I will explain how to create a full-text index. But, first, we will read two methods to create the full-text index, using manually and using the SQL command.

Create Full-Text Index Manually
The following steps are performed to create the Full Text Index.
    Create a Full-Text Catalog
    Create Full-Text Index
    Populate the Index

1. Create a Full-Text Catalog
The full-text catalog is used for the full-text index. If we don’t specify the full-text catalog, then SQL Server will use the default catalog. So now we have learned how to create a full-text catalog.

To create a full-text catalog, select your database, go to the Storage folder, right-click on Full-Text Catalog, and select the New Full-Text Catalog option.

Now provide a name for the full-text catalog.

You can see that a new catalog has been created in the Storage folder.

2. Create Full-Text Index
To create a full-text index choose your table and right-click on that table and select the “ Define Full-Text Index” option.

Now select Unique Index. It is compulsory that for “Full-Text Index” table must have at least one unique index.

Select columns name and language types for columns. You can only select character-based and image-based columns.

Select change tracking.

Now select the full-text catalog for the index.

The last image confirms that the full-text index is created successfully. Now we populate this full-text index.

3. Populate the Index
To populate the index, right-click on the table and select the “Start Full Population” option.

Create Full-Text Index using SQL Command
Use the following command syntax to create the Full Text Index.

Syntax
CREATE FULLTEXT INDEX ON table_name
[ ( { column_name
[ TYPE COLUMN type_column_name ]
[ LANGUAGE language_term ]
[ STATISTICAL_SEMANTICS ]
} [ ,...n]
) ]
KEY INDEX index_name
[ ON<catalog_filegroup_option> ]
[ WITH [ ( ] <with_option> [ ,...n] [ ) ] ]
[;]

<catalog_filegroup_option>::=
{
fulltext_catalog_name
| ( fulltext_catalog_name, FILEGROUP filegroup_name )
| ( FILEGROUP filegroup_name, fulltext_catalog_name )
| ( FILEGROUP filegroup_name )
}

<with_option>::=
{
CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [, NO POPULATION ] }
| STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
| SEARCH PROPERTY LIST [ = ] property_list_name
}

Parameters

Example

CREATE FULLTEXTCATALOG New_CatalogASDEFAULT;
CREATE FULLTEXTINDEX ON dbo.Employee(EmployeeName TYPECOLUMN varchar LANGUAGE 1033,EmpSalary TYPECOLUMN varchar LANGUAGE 1033)
KEY INDEX UN_Pankaj
ON
New_Catalog

After creating the FULL Text Catalog and Full Text Index, we now learn how to use these in search queries for better performance. There are four principal T-SQL functions that allow one to interact with your Full-Text indices:

CONTAINS and  FREETEXT Method
CONTAINS and FREETEXT functions return a boolean value, meaning we could use them directly in a WHERE clause. The remaining two return a two-column table—KEY and RANK, allowing one to manage ranked searches.

FREETEXT
FREETEXT T-SQL function performs predicate searches for values that match the meaning and not just the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches:

• Separates the string into individual words based on word boundaries (word-breaking).
• Generates inflectional forms of the words (stemming).
• Identifies a list of expansions or replacements for the terms based on matches in the thesaurus.

Example
SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski
WHERE
FREE TEXT(Keyword_Text,'Hotel Above')

Output

CONTAINS
CONTAINS searches for precise or fuzzy (less precise) matches to single words and phrases, words within a certain distance of one another, or weighted matches in SQL Server. It is similar to the Freetext but with the difference that it takes one keyword to match with the records, and if we want to combine other words as well in the search, then we need to provide the “and” or “or” in search.

CONTAINS can search for
    A word or phrase.
    The prefix of a word or phrase.
    A word near another word.
    A word is inflectionally generated from another (for example, the word drive is the inflectional stem of drives, drove, driving, and driven).
    A word that is a synonym of another word using a thesaurus (for example, the word "metal" can have synonyms such as "aluminum" and "steel").

Example
SELECT TOP 10 tmski.Keyword_TextFROMdbo.TblMaster_Searching_Keyword_Infotmski
WHERE
CONTAINS(Keyword_Text,'Hotel OR Above')

Use FULL-Text Index search when you have a large volume of data, and you want to perform a search for textual data columns for specific words and phrases. Full-Text Index can be used to search words, phrases, and multiple forms of a word or phrase using FREETEXT (), CONTAINS () with “and” or “or” operators (FREETEXT, CONTAINS).

HostForLIFEASP.NET SQL Server 2019 Hosting

A SQL Server Index is used on a database table for faster data access. In this article, learn what SQL Server Indexes are, why we need them, and how to implement them in SQL Server.

Indexes in SQL Server
SQL Indexes are used in relational databases to retrieve data quickly. They are similar to indexes at the end of the books whose purpose is quickly finding a topic. SQL provides Create Index, Alter Index, and Drop Index commands used to create a new index, update an existing one, and delete an index in SQL Server.

• Data is internally stored in a SQL Server database in "pages" where the size of each page is 8KB.
• A continuous eight pages are called an "Ex. "nt."
• When we create the table, one extent will be allocated for two tables; when it is computed, it is filled with the data. Then another time will be given, and this extent may or may not be continuous to the first extent.

Table Scan
In SQL Server, a system table with the name sysindexes contains information about indexes available on tables in the database. If a table has no index, there will be one row in the sysindexes table related to that table indicating no index on the table when you write a select statement with a condition where clause, the first SQL Server will refer to the "indid" (index id).

Columns of the "Sysindex" table determine whether or not the column on which you write the conditions has an index. When that indid columns, to get an address of the table's first extent and then searches each row of the table for the given value. This process checks the given condition with every table row, called a table scan. A drawback of table scan is that if there is no increase in rows in the table, the time taken to retrieve the data will increase, affecting performance.

Type of Indexes
SQL Server supports two types of indexes:

• Clustered Index
• Non-Clusterd Index.

Clustered Index in SQL Server
A B-Tree (computed) clustered index is the Index that will arrange the rows physically in the memory in sorted order.

An advantage of a clustered index is that searching for a range of values will be fast. A clustered index is internally maintained using a B-Tree data structure leaf node of the btree of the clustered Index will contain the table data; you can create only one clustered Index for a table.
How do we Retrieve the data with clustered Index?

When you write a select statement with a condition in a where clause, then the first SQL Server will refer to the "indid" columns of the "Sysindexes" table and when this column contains the value "1".

Then it indexes the table with a clustered index. In this case, it refers to the columns ." The Root" node of the B-tree of clustered index searches in the b-tree to find the leaf node that contains the first row that satisfies the given conditions and retrieves all the rows that satisfy the given situation that will be in sequence.

Insert and Update with Clustered Index
Since a clustered index arranges the rows physically in the memory in sorted order, insert and will become slow because the row must be inserted or updated in sorted order.
Finally, the page into which the row must be inserted or updated, and if free space is not available on the page, create the free space and then perform the insert, update and delete.

To overcome this problem while creating a clustering index, specify a fill factor, and when you specify a fill factor as 70, then in every page of that table, 70% will fill with data, and the remaining 30% will be left free. Since free space is available on every page, the insert and update will be fast.

Nonclustered Index in SQL Server

A nonclustered index is an index that will not arrange the rows physically in the memory in sorted order.
An advantage of a nonclustered index is that searching for the values in a range will be fast.
You can create a maximum of 999 nonclustered indexes on a table, 254 up to SQL Server 2005.
A nonclustered index is also maintained in a B-Tree data structure. Still, leaf nodes of a B-Tree of the nonclustered Index contain the pointers to the pages that contain the table data and not the table data directly.

How do we Retrieve data with a nonclustered index?
When you write a select statement with a condition in a where clause, then SQL Server will refer to the "indid" columns of the sysindexes table, and when this column contains a value in the range of 2 to 1000, then it indicates that the table has a non –clustered Index. In this case, it will refer to the columns root of the sysindexes table to get two addresses.

The root node of a B-Tree of a nonclustered index, and then search in the B-Tree to find the leaf node that contains the pointers to the rows that contain the value you are searching for and retrieve those rows.
Insert and Update with a Nonclustered Index

There will be no effect of inserting and updating with a nonclustered index because it will not arrange the row physically in the memory in sorted order.
With a nonclustered index, rows are inserted and updated at the end of the table.

How to Create Indexes in SQL Server?
Use the create index command with the following system to create an index.
create [unique][clustered /non clusted] index :
<indexname> on <object name>(<column list>)
[include(<columnlst>)]
[with fillfactor=<n>]

By default, an index is nonclustered.

For example, the following examples create a nonclustered index on department_no of emp tables.
create index DNoINdex on Emp(DeptNo)

Simple & Composite Indexes

• Based on the number of columns on which an index is created, indexes are classified into simple and composite indexes.
• When indexes are created on single columns, it is called a simple index; when combined with multiple columns, it's called a composite index.

For example, the following example creates a nonclustered index in combination with the emp table's department number and job columns.
create index dnotedxi on emp(deptno asc,job desc)

Unique Index
    When an index is created using the keyword unique, it is called a unique index; you create a unique index on columns, and a unique index constraint will be created.
    If the columns on which you create a unique index contain duplicate values, then a unique index will not be created, and you will get an error.

Altering an Index
To alter an index, use an alter index command that has the following syntax:
    Alter index <ind Name> on <object Name> 
    rebuild/Recognize/Disable.

Alter the Index using the rebuild option. The Rebuild option will recreate the computer index; the recognize option will reorganize leaf nodes of the b-tree to Index. The disable option will disable the Index when it is eligible and then enable it.

For example, the following example alters the index "d" oidx" "available on the department number of columns on the emp table.
alter index DNOiDX on EMp rebuild

Getting a list of indexes
This stored procedure is used to compute a list of indexes available on a table.
sp_helpindex 'Stud'

Deleting indexes
Use the drop index' command that has the following syntax:
drop index <indexname> on <object name>

For example, the following example deletes the Index dnoidex available on the department number columns of the emp table.
drop index doindex on student

HostForLIFEASP.NET SQL Server 2019 Hosting

What is Temporal Table?
A system-versioned temporal table is a type of table in SQL Server that automatically tracks the historical changes to the data in the table, by keeping a separate history table that stores all the previous versions of the data. This allows you to easily see how the data has changed over time and also enables you to perform temporal queries to retrieve the data as it existed at a specific point in time. The system-versioning is done by using two columns, one for the start date and one for the end date, that keep track of when the data was valid. The system manages this automatically and transparently to the user.

You can also create a temporal table by specifying the Transact-SQL statements directly, as shown in the example below. Note that the mandatory elements of every temporal table are the PERIOD definition and the SYSTEM_VERSIONING clause with a reference to another user table that will store historical row versions:

CREATE TABLE [dbo].[AppUsers]
    (
        [UserID] int NOT NULL PRIMARY KEY CLUSTERED
      , [UserName] nvarchar(100) NOT NULL
      , [PagesVisited] int NOT NULL
      , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN
      , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END HIDDEN
      , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
     )
     WITH
     (
        SYSTEM_VERSIONING = ON
        (
            HISTORY_TABLE = dbo.AppUsersArchive,
            HISTORY_RETENTION_PERIOD = 2 MONTHS
        )
     );

ALTER TABLE AppUsers
    ADD
        ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START HIDDEN
            constraint DF_ValidFrom DEFAULT DATEADD(second, -1, SYSUTCDATETIME())
        , ValidTo datetime2 (2) GENERATED ALWAYS AS ROW END HIDDEN
            constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
        , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

    ALTER TABLE AppUsers
        SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AppUsersArchive));

Advantage of Temporal Table

The main advantage of using a system-versioned temporal table is the ability to easily track and analyze changes to the data over time. Some specific benefits include:

• Auditing: Temporal tables can be used to track changes to the data, making it easier to identify who made a change and when.
• Point-in-time reporting: You can retrieve the state of the data at a specific point in time, which is useful for generating historical reports or analyzing trends over time.
• Data recovery: If data is accidentally deleted or modified, you can easily recover the previous version of the data.
• Improved data integrity: By keeping a separate history table, temporal tables can help ensure that historical data is not accidentally modified or deleted.
• Improved performance: By keeping a separate history table, temporal tables can help improve query performance for certain types of queries that retrieve historical data.

It's worth noting that temporal tables are only available in SQL Server 2016 and later.

Temporal Table Considerations and Limitations

There are some considerations and limitations to be aware of when working with temporal tables, due to the nature of system-versioning:

• A temporal table must have a primary key defined in order to correlate records between the current table and the history table, and the history table can't have a primary key defined.
• The SYSTEM_TIME period columns used to record the ValidFrom and ValidTo values must be defined with a datatype of datetime2.
• By default, the history table is PAGE compressed.
• While temporal tables support blob data types, such as (n)varchar(max), varbinary(max), (n)text, and image, they'll incur significant storage costs and have performance implications due to their size. As such, when designing your system, care should be taken when using these data types.
• History table must be created in the same database as the current table. Temporal querying over linked servers isn't supported.
• History table can't have constraints (primary key, foreign key, table or column constraints).
• TRUNCATE TABLE isn't supported while SYSTEM_VERSIONING is ON.
• Direct modification of the data in a history table isn't permitted.
• INSTEAD OF triggers aren't permitted on either the current or the history table to avoid invalidating the DML logic. AFTER triggers are permitted only on the current table. 
• They're blocked on the history table to avoid invalidating the DML logic.

Hope the article would have helped you in understanding Temporal tables.

HostForLIFEASP.NET SQL Server 2019 Hosting