Make a Table By running the query and adding the information
CREATE TABLE employeeslist
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    [Name] VARCHAR(100) NOT NULL,
    Gender VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO employeeslist VALUES ('Peter', 'M', 'London', 30000);
INSERT INTO employeeslist VALUES ('Laura', 'F', 'London', 35000);
INSERT INTO employeeslist VALUES ('Anna', 'F', 'London', 33000);
INSERT INTO employeeslist VALUES ('Tom', 'M', 'London', 38000);
INSERT INTO employeeslist VALUES ('Maria', 'F', 'Liverpool', 36000);
INSERT INTO employeeslist VALUES ('Catherine', 'F', 'Liverpool', 32000);
INSERT INTO employeeslist VALUES ('Lily', 'F', 'Liverpool', 35000);
INSERT INTO employeeslist VALUES ('Mia', 'F', 'Liverpool', 31000);
INSERT INTO employeeslist VALUES ('Grace', 'F', 'Leeds', 37000);
INSERT INTO employeeslist VALUES ('Arthur', 'M', 'Leeds', 35000);

CREATE TABLE employeeslist1
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    [Name] VARCHAR(100) NOT NULL,
    Gender VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    salary INT NOT NULL
);

INSERT INTO employeeslist1 VALUES ('Peter', 'M', 'London', 30000);
INSERT INTO employeeslist1 VALUES ('Laura', 'F', 'London', 35000);
INSERT INTO employeeslist1 VALUES ('Grace', 'F', 'Liverpool', 12000);
INSERT INTO employeeslist1 VALUES ('Sophia', 'F', 'Leeds', 37000);
INSERT INTO employeeslist1 VALUES ('Alfie', 'M', 'York', 38000);

Questions
1. Fetch a resultant table that includes all records from both the table as one table.
Answers
select * from employeesGH
union all
select * from employeesGH1

2. Fetch a resultant table that includes all records from both tables as one table but no duplicate values.
Answer
select * from employeesGH
union
select * from employeesGH1

3. Fetch a resultant table that has the id, name, and city from the first table and the id and name from the second table in the same order.
Answer
select Id,name,city from employeesGH
union
select id,name from employeesGH1
* this will result in error as no. of colums should be equal in both select statements.

4. Fetch a resultant table that has id and city from the first table and name and id from the second table in the same order.
Answer
select name,city,Id from employeesGH
union
select id,name,city from employeesGH1
* this will result in error as the datatype of id and city and name and city is not same.

5. Fetch a resultant table that has the id, name, and city from the first table and the id, city, and name from the second table in the same order.
Answer
select id,[name],city from employeesGH
union
select id,city,[name] from employeesGH1
this will not result in error as the datatype of city and name is same but it will not be the proper result.

Union vs Union All
Union eliminates redundant rows but Union All does not
Union operates more quickly than Union All because it uses unique sorts to eliminate duplication.

Note: The select statement should have the same number of columns, data types, and column orders.

Summary
All of Union and Union together -> combines two or more select queries' result sets.

HostForLIFEASP.NET SQL Server 2022 Hosting

After seeing that my online application was frequently experiencing SQL Server Request Timeouts, I examined the server and discovered that the CPU was nearly entirely utilized.

SQL Server used up most of the resources.

I was aware that there was a problem with my database query function, but I'm not sure which specific queries from my online application are to blame. Thus, I run the following SQL Studio Management Studio query to obtain the top ten database queries in terms of resource consumption.

SELECT TOP 10
    qs.total_logical_reads + qs.total_logical_writes AS total_io,
    qs.execution_count,
    qs.total_worker_time AS total_cpu_time,
    qs.total_elapsed_time,
    st.text AS query_text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
    total_io DESC;

And the query will give me such result.

You can examine your most costly queries by looking at the 'query_text' column, or you can look for ways to optimize your query by looking at the query_plan column.

In my situation, I've come to the conclusion that improper indexing is the main reason behind one of my tables, which has millions of rows in it.

Additionally, I was able to spot a few queries that had string manipulation functions like SUBSTRING, REVERSE, CHARINDEX, and others but did not employ an appropriate join statement. In order to make it more effective and less resource-intensive, I must rework a few queries.

HostForLIFEASP.NET SQL Server 2022 Hosting

Dynamic SQL can be used to truncate every table in a database. When performing this kind of operation, use caution because there is no way to undo the deletion of all data across all tables.

This is an illustration of how to write a query in a SQL Server database to truncate every table.

DECLARE @TableName NVARCHAR(128)
DECLARE @TruncateQuery NVARCHAR(MAX)

DECLARE tableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TruncateQuery = 'TRUNCATE TABLE ' + @TableName
    EXEC sp_executesql @TruncateQuery

    FETCH NEXT FROM tableCursor INTO @TableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

Kindly take note of

• This operation carries a risk: When tables are truncated, all data is lost and cannot be recovered.
• Make a backup of your data: It's imperative to have a backup of your data in case the original is needed before running any queries that alter data in this manner.
• Examine and make sure it passes: Before running such queries in a production environment, always properly evaluate and test in a secure setting.

Before carrying out such operations, it's also imperative to take permissions and the effect of truncating tables in a live system into account.

HostForLIFEASP.NET SQL Server 2022 Hosting