1- Write a script for Making attach and detech for

AdventureWorks2016CTP3 Database
Attach:

USE [master]

GO

CREATE DATABASE [AdventureWorks2008R2] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\

MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_Data.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\

MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_log.LDF' )

FOR ATTACH

GO

Detech:
USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2008R2'

GO

2- Write a script for add 8 secondary and 4 primary files with

files 4 file groups

Filegroup:
USE [master]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILEGROUP [filegroups1]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILEGROUP [filegroups2]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILEGROUP [filegroups3]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILEGROUP [filegroups4]

GO
Secondary File:
USE [master]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile1', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile1.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups1]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile2', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile2.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups1]

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile3', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile3.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups2]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile4', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile4.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups2]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile5', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile5.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups3]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile6', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile6.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups3]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile7', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile7.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups4]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile8', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile8.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups4]
GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile9', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile9.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups5]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile10', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile10.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups5]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile11', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile11.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups6]

GO

ALTER DATABASE [AdventureWorks2008R2] ADD FILE ( NAME =

N'secandaryfile12', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\secandaryfile12.ldf' , SIZE =
8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [filegroups6]

GO

3- Why we cannot put transaction log file in a FileGroup?

Filegroups are used to store and group data for backup, logic,
security and performance purposes. Log files dont need to be in
the same position.

Filegroups group data files together for the purpose of table

storage. Log files don't store tables or indexes, so they can't
logically be part of a filegroup

User-defined filegroups are any filegroups that are specified by

using the FILEGROUP keyword in a CREATE DATABASE or ALTER
DATABASE statement.

Log files are never part of a filegroup. Log space is managed

separately from data space.
4- Move transaction log file to a different location via TSQL
5- move a Data File to another FileGroup
6- Moving files while the database is online
7- Which Filegroup is that Partition Using? How
Many Rows Does It Have? 8- How to Find Default File
Group
8- How to Find Default File Group

9- Every student should choose one table and write the code
for following

 Write code for partition table mapping to 4-file groups

USE [AdventureWorks2008R2]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [MFUN](datetime) AS RANGE LEFT FOR VALUES
(N'2007-11-30T00:00:00', N'2007-12-30T00:00:00', N'2008-01-
30T00:00:00',
N'2008-02-29T00:00:00', N'2008-03-30T00:00:00', N'2008-04-
30T00:00:00',
N'2008-05-30T00:00:00', N'2008-06-30T00:00:00', N'2008-07-
30T00:00:00',
N'2008-08-30T00:00:00',N'2008-09-30T00:00:00',N'2008-10-30T00:00:00')

CREATE PARTITION SCHEME [Msch] AS PARTITION [MFUN] TO

([filegroups1], [filegroups2], [filegroups3], [filegroups4],
[PRIMARY])

 Get the number of rows and the range for each

partition in a table
 Why partition function works for datetime2 but not for
datetime1
 how to alter partition scheme and partition function
with these additional ranges to existing partition
function and scheme?
 Write code for row and page compression table
alter table Person.address rebuild with (data_compression=row)

alter table Person.address rebuild with (data_compression=page)

 Tell what compression level my tables/indexes are

 Create clustered and Nonclustered Indexes for any
Column in chosen table
clustered
ALTER TABLE [Person].[Address] DROP CONSTRAINT
[PK_Address_AddressID] WITH ( ONLINE = OFF )
 ALTER TABLE [Person].[Address] ADD CONSTRAINT
[PK_Address_AddressID] PRIMARY KEY CLUSTERED ([AddressID] ASC)

Nonclustered
CREATE NONCLUSTERED INDEX [IX_Address_StateProvinceID] ON [Person].
[Address]

( [StateProvinceID] ASC)

 Write code for compression index

Alter Index Pk_Address_AddressID on Person.Address rebuild

 With coding Get information on specific indexes,

and Check Fragmentation of Index
select db_id (database_id) as DBName , object_id(OBJECT_ID) as
TableName , index_id ,index_type_desc,

avg_fragmentation_in_percent , fragment_count ,
avg_fragment_size_in_pages , page_count

from sys.dm_db_index_physical_stats(DB_ID( 'AdventureWorks2008R2'),

object_id('Person.Address'),null,null,null)

 What is page splits? How many page splits is too many?

What can I do to help reduce them

o page splits:
‫تقسيم الصفحة هو عملية نقل نصف الصفوف أو اإلدخاالت في بيانات كاملة أو صفحة‬ 
.‫فهرس إلى صفحة جديدة إلى إفساح المجال لصف جديد أو إدخال فهرس‬
‫إنشاء صفحتين غير متتاليتين من صفحة واحدة تفتقر إلى المساحة لقبول البيانات الجديدة‬ 
‫التي ينبغي أن تكون عليها تم إدراجها في الصفحة بسبب الترتيب المنطقي للبيانات‬
. ‫المحددة في تلك الصفحة‬

o How many page splits is too many :

.‫ يزيد عداد ترقيم الصفحات مع زيادة نشاط حمل العمل على الخادم‬، ‫في معظم الحاالت‬ 

o What can I do:

1. ‫قم بزيادة عامل التعبئة في فهارسك‬.
2. ‫إعادة بناء الفهارس الخاصة بك في كثير من األحيان‬.
3. ‫أضف فهارس مجمعة إلى المفاتيح األولية المتزايدة بشكل رتيب‬.
4. ‫ إخراج فرعي أسرع‬/ ‫احصل على نظام إدخال‬.

List and with code explain the techniques to index maintenance

ALTER index PK_Address_AddressID on Person.Address rebuild
 ALTER index PK_Address_AddressID on Person.Address reorganize

Query to determine table filegroup by index and partition.

Filegroup:
exec sp_helpfilegroup
select * from sys.filegroups

index:
select * from sys.indexes

10-Enableing query store for the database

11- Find queries with high memory grants – using Query Store

--Gather and report on most memory hungry queries

DECLARE @Reportinginterval int;
DECLARE @Database sysname;
DECLARE @StartDateText varchar(30);
DECLARE @TotalExecutions decimal(20,3);
DECLARE @TotalDuration decimal(20,3);
DECLARE @TotalCPU decimal(20,3);
DECLARE @TotalLogicalReads decimal(20,3);
DECLARE @SQL varchar(MAX);
--Set Reporting interval in days
SET @Reportinginterval = 1;
SET @StartDateText = CAST(DATEADD(DAY, -@Reportinginterval,
GETUTCDATE()) AS varchar(30));
--Cursor to step through the databases
DECLARE curDatabases CURSOR FAST_FORWARD FOR
SELECT [name]
FROM sys.databases
WHERE is_query_store_on = 1
AND state_desc = 'ONLINE';
--Temp table to store the results
DROP TABLE IF EXISTS #Stats;
CREATE TABLE #Stats (
DatabaseName sysname,
SchemaName sysname NULL,
ObjectName sysname NULL,
QueryText varchar(1000),
MaxMemoryGrantMB decimal(20,3)
);
OPEN curDatabases;
FETCH NEXT FROM curDatabases INTO @Database;
--Loop through the datbases and gather the stats
WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = '

USE [' + @Database + ']
INSERT INTO #Stats
SELECT
DB_NAME(),
s.name AS SchemaName,
o.name AS ObjectName,
SUBSTRING(t.query_sql_text,1,1000) AS QueryText,
(MAX(rs.max_query_max_used_memory)/128) AS MaxMemoryMB
FROM sys.query_store_query q
INNER JOIN sys.query_store_query_text t
ON q.query_text_id = t.query_text_id
INNER JOIN sys.query_store_plan p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rsi
ON rs.runtime_stats_interval_id =
rsi.runtime_stats_interval_id
LEFT JOIN sys.objects o
ON q.OBJECT_ID = o.OBJECT_ID
LEFT JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE rsi.start_time > ''' + @StartDateText + '''
GROUP BY s.name, o.name, SUBSTRING(t.query_sql_text,1,1000)
OPTION(RECOMPILE);'
EXEC (@SQL);
FETCH NEXT FROM curDatabases INTO @Database;
END;
CLOSE curDatabases;
DEALLOCATE curDatabases;
--Report Results
SELECT TOP 50
DatabaseName,
SchemaName,
ObjectName,
QueryText,
MaxMemoryGrantMB
FROM #Stats
WHERE QueryText not like 'INSERT INTO #Stats%' --Exclude
current query
ORDER BY MaxMemoryGrantMB DESC;
DROP TABLE #Stats;
 12-Write a query using the Sales.SalesOrderDetail table to display a value
(“Under 10” or “10–19” or “20–29” or “30–39” or “40 and over”) based on the
OrderQty value by using the CASE function. Include the SalesOrderID and
OrderQty columns in the results.
SELECT SalesOrderID, OrderQty,
CASE WHEN OrderQty BETWEEN 0 AND 9 THEN
'Under 10' WHEN OrderQty BETWEEN 10 AND 19
THEN '10-19'
WHEN OrderQty BETWEEN 20 AND 29 THEN '20-29'
WHEN OrderQty BETWEEN 30 AND 39 THEN '30-39'
ELSE '40 and over' end AS range FROM
Sales.SalesOrderDetail;

13-Write a query that displays the names of the customers along with the product
names that they have purchased. Hint: Five tables will be required to write this
query!
SELECT FirstName, MiddleName, LastName, Prod.Name
FROM Sales.Customer AS C
INNER JOIN Person.Person AS P ON C.PersonID = P.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader AS SOH ON C.CustomerID = SOH.CustomerID
INNER JOIN Sales.SalesOrderDetail AS SOD
ON SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product AS Prod ON SOD.ProductID = Prod.ProductID;

14-Display the CustomerID, SalesOrderID, and OrderDate for each

Sales.SalesOrderHeader row as long as the customer has placed at least five orders.
Use any of the techniques from this section to come up with the query
--subquery
SELECT CustomerID, SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader
WHERE CustomerID IN
(SELECT CustomerID
FROM Sales.SalesOrderHeader GROUP
BY CustomerID
HAVING COUNT(*) > 4);
--CTE
WITH c AS (
SELECT CustomerID
FROM Sales.SalesOrderHeader GROUP
BY CustomerID
HAVING COUNT(*) > 4)
SELECT c.CustomerID, SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader AS SOH
INNER JOIN c ON SOH.CustomerID = c.CustomerID;
--derived table
SELECT c.CustomerID, SalesOrderID, OrderDate
FROM Sales.SalesOrderHeader AS SOH
INNER JOIN (
SELECT CustomerID
FROM Sales.SalesOrderHeader GROUP
BY CustomerID
HAVING COUNT(*) > 4) AS c ON SOH.CustomerID = c.CustomerID;

15-Write a SELECT INTO statement that creates a table, dbo.tempCustomerSales,

showing every CustomerID from the SalesLT.Customer along with a count of the orders
placed and the total amount due for each customer.

SELECT COUNT(ISNULL(SalesOrderID,0)) AS CountOfORders,

c.CustomerID, SUM(TotalDue) AS TotalDue
INTO
dbo.tempCustomerSales
FROM SalesLT.Customer
AS c
LEFT JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID;

16-Write a query that can be used to find the queries that use the most reads,
writes, andworker time (CPU).
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC -- logical reads

 -- ORDER BY qs.total_logical_writes DESC -- logical writes

-- ORDER BY qs.total_worker_time DESC -- CPU time

17-How to Reduce High Virtual Log File (VLF) Count

‫ننسخ ملف السجل االفتراضي‬ .1

‫نحدد قاعدة البيانات التي نريد نقلل عدد ملفات السجل االفتراضية‬ .2
3. Right-click tasks shrink files file type=log
ok

18-Write a statement that attempts to insert a duplicate row into the

HumanResources.Department table. Use the @@ERROR function to display the
error.
DECLARE @Error INT;
INSERT INTO
HumanResources.Department(DepartmentID,Name,GroupName,ModifiedDate)
VALUES (1,'Engineering','Research and Development',GETDATE());
SET @Error =
@@ERROR;
IF @Error > 0 BEGIN
PRINT @Error;
END;

19-With examples write a code for TRANSACTION ISOLATION to Read

UNCommitted, SERIALIZABLE,and REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL READ REPEATABLE

20-Write a t- code to identify the locks and process ids involved in the blocking chain
SELECT -- use * to explore

request_session_id AS spid,

resource_type AS restype,

resource_database_id AS dbid,

DB_NAME(resource_database_id) AS dbname,

resource_description AS res,

resource_associated_entity_id AS resid,

request_mode AS mode,

request_status AS status
FROM sys.dm_tran_locks;

21-CREATE LOGINS FOR SYSADMIN and another LOGIN

for security user, has ability to shutdown server and
change permissions
4.
USE [master]

GO

CREATE LOGIN [YEMENSBA] WITH PASSWORD=N'YEMENSBA' MUST_CHANGE,

DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GO

ALTER SERVER ROLE [sysadmin] ADD MEMBER [YEMENSBA]

GO

GO

CREATE SERVER ROLE [shutdownadmin]

GO

use [master]

GO

GRANT SHUTDOWN TO [shutdownadmin]

GO

USE [master]

GO

CREATE LOGIN [SBAYEMEN] WITH PASSWORD=N'YEMENSBA' MUST_CHANGE,

DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GO

ALTER SERVER ROLE [securityadmin] ADD MEMBER [SBAYEMEN]

GO

ALTER SERVER ROLE [setupadmin] ADD MEMBER [SBAYEMEN]

GO

22-CREATE SERVER ROLE FOR SHUTING DOWN and ROLE FOR EXECUTING
STORED PROCEDURES
ROLE FOR SHUTING DOWN:
USE [master]
GO
CREATE SERVER ROLE [shutdownadmin]
GO
use [master]
GO
GRANT SHUTDOWN TO [shutdownadmin]
GO
ROLE FOR EXECUTING STORED PROCEDURES:
USE AdventureWorks2008R2_Data
GO
CREATE ROLE db_SPExecute

23-Create a login trigger to catch the transaction of

user at tamping authorized to data base witch enable
its a permission between time period
USE Master

go

CREATE TRIGGER trg_track_logons

ON ALL SERVER

WITH EXECUTE AS 'YAMENESBA'

FOR LOGON

AS

BEGIN

IF ORIGINAL_LOGIN() = 'YEMENSBA'

and

DATEPART (hh,GETDATE()) between 1 and 23

BEGIN

ROLLBACK

INSERT AdventureWorks2008R2_Data.dbo.RestrictedLogons

(Login, TimeStamp)

VALUES (ORIGINAL_LOGIN(),GETDATE())

END

END

24-How Often Should I Backup my Database

25-What is the difference between full, transaction log,

and differential SQL Server database backups? And how
 do I restore from each backup type? What database
options are available? What are common restore
operations? Using code explain that
Full: does a backup of all database pages and "marks" them as backed up.

Transaction log: does a backup of the transaction log; requires that at least one Full backup
has been done before hand.

Differential: does a backup of all database pages that have changed since the last full
backup. Full restore:
USE Master

26-Write a code that make an automatic backup for

AdventureWorks2016CTP3 every day at 10:00 pm

automatic ‫ شغال وفي وضع‬sql server agent ‫نتأكد إذا كان‬ 

‫ ومن‬job ‫ نكتب اسم‬--- sql server agent --- job---job new ‫ندخل على‬ 
‫قائمة‬
Select a page ---- steps -- ‫ نختار من الشاشة‬new --- ‫ اكتب اسم‬step
name ‫ اكتب داخل‬command ‫ الجملة التي تقوم بالنسخة االحتياطي‬---- ok
--- ‫ ونختار‬schedules --- ‫ اختار‬new ‫ اكتب اسم‬--- ‫ من الشاشة‬schedules
‫ اعدل الخيار‬occurs ‫ الى‬daily

‫ نعدل الوقت في‬occurs to 10:00 pm

Ok
Ok