DB - Day7

Index

Indexes are used to speed-up query process in SQL Server, resulting in high performance. They are similar to textbook indexes. In textbooks, if you need to go to a particular chapter, you go to the index, find the page number of the chapter and go directly to that page. Without indexes, the process of finding your desired chapter would have been very slow.

The same applies to indexes in databases. Without indexes, a DBMS has to go through all the records in the table in order to retrieve the desired results. This process is called table-scanning and is extremely slow. On the other hand, if you create indexes, the database goes to that index first and then retrieves the corresponding table records directly.

Clustered Index

A clustered index defines the order in which data is physically stored in a table. Table data can be sorted in only way, therefore, there can be only one clustered index per table. In SQL Server, the primary key constraint automatically creates a clustered index on that particular column.

CREATE CLUSTERED INDEX i2 
ON Student(st_name)

---

Non Clustered Index

A non-clustered index doesn’t sort the physical data inside the table. In fact, a non-clustered index is stored at one place and table data is stored in another place. This is similar to a textbook where the book content is located in one place and the index is located in another. This allows for more than one non-clustered index per table.

It is important to mention here that inside the table the data will be sorted by a clustered index. However, inside the non-clustered index data is stored in the specified order. The index contains column values on which the index is created and the address of the record that the column value belongs to.

When a query is issued against a column on which the index is created, the database will first go to the index and look for the address of the corresponding row in the table. It will then go to that row address and fetch other column values. It is due to this additional step that non-clustered indexes are slower than clustered indexes.

Unique Constraint Creates a NON Clustered index by default

Create non clustered Index

CREATE NONCLUSTERED INDEX i2 
ON Student(st_name)

This will create a unique constraint and a non clustered index

CREATE UNIQUE INDEX i5
ON Student(st_name)

---

Cursor

1. Declare Cursor => Assign Select

2. Declare Variables => assign values for each row

3. Open Cursor Declare pointer at first row

4. Fetch Row Data into memory => Next Row

5. Check on @@fetch_status =0

6. Close Cursor

7. Deallocate Cursor

FOR update is the default of cursor

DECLARE c1 CURSOR
FOR SELECT st_id ,St_fname 
FROM Student 
WHERE St_address ='Cairo'
FOR READ ONLY  -- OR FOR UPDATE

DECLARE @id int,@name VARCHAR(20)
OPEN c1
FETCH c1 INTO @id,@name
WHILE @@FETCH_STATUS =0
	BEGIN
	SELECT @id,@name
	FETCH c1 INTO @id,@name 
	END
CLOSE c1
DEALLOCATE c1

Concatenation for all names in one string

DECLARE c1 CURSOR

FOR SELECT DISTINCT(st_fname) 
FROM Student 
WHERE St_fname IS NOT NULL
FOR READ ONLY

DECLARE @name VARCHAR(10),@all_names VARCHAR(200) =''

OPEN c1
FETCH c1 INTO @name
WHILE @@FETCH_STATUS =0
	BEGIN 
	SET @all_name=CONCAT(@all_names,',',@name)
	FETCH c1 INTO @name
	END
SELECT @al_names
CLOSE c1
DEALLOCATE c1

Update Salaries in instructor

DECLARE C1 CURSOR

FOR SELECT Salary FROM Instructor
FOR UPDATE

DECLARE @sal INT
OPEN c1
FETCH c1 INTO @sal
WHILE @@FETCH_STATUS=0
	BEGIN
	IF @sal >=3000
	UPDATE Instructor
		SET salary = salary*1.2
	WHERE CURRENT OF c1
	ELSE IF @salary<3000
		UPDATE Instructor
			SET Salary =salary*1.1
	WHERE CURRENT OF c1
		
	ELSE 
		DELETE FROM Instructor
		WHERE CURRENT OF c1
	FETCH c1 INTO @sal
	END
CLOSE c1
DEALLOCATE c1

---

View

Views in SQL are a kind of virtual table. A view also has rows and columns like tables, but a view doesn’t store data on the disk like a table. View defines a customized query that retrieves data from one or more tables, and represents the data as if it was coming from a single source.

We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain conditions.

• Is a select statement

• Specify user view of data

• Hide DB Object

• Limit Access of data

• Simplify Construction of complex queries

• Has No parameter

• Has No DML Queries inside its body

• standard view can be considered as virtual table

Types Of Views

1. Standard View

2. Partitioned View

3. materialized View

Standard View

CREATE VIEW VCairo 
AS
	SELECT id,name
	FROM Student
	WHERE address ='cairo'

Access View

SELECT * 
FROM VCairo

CREATE VIEW VCairo(stID,stName)  --alias names
AS
	SELECT id,name
	FROM Student
	WHERE address ='cairo'

ALTER SCHEMA hr TRANSFER Vcairo

This prevent insert values in address other than Cairo

CREATE VIEW VCairo(stID,stName,sadd)  --alias names
AS
	SELECT id,name,address
	FROM Student
	WHERE address ='cairo'
WITH CHECK OPTION

Partitioned View

CREATE VIEW Vstuds
AS 
	SELECT *
	FROM Cairoserver.iti.dbo.Students
	UNION ALL
	SELECT *
	FROM Alexserver.DB.hr.Studs

---

Securing View

This will return The script that created view

SP_HELPTEXT 'vcairo'

So you Should encrypt the view when creating it

CREATE VIEW VCairo(stID,stName) 
WITH ENCRIPTION
AS
	SELECT id,name
	FROM Student
	WHERE address ='cairo'

---

Inserting into View

You Can Insert data into view if the view access one table this will reflect into your table because view is not a table You cant insert or update if one of the values the view can't access is mandatory it must be allowing null or default or identity or driven

INSERT INTO VCairo
VALUES(800,'ali','cairo')

---

Pivot and Group

Sales Table

ProductID	SalesmanName	Quantity
1	ahmed	10
1	Khalid	20
1	ali	45
2	ahmed	15
2	khalid	30
2	ali	20
3	ahmed	30
4	ali	80
1	ahmed	25
1	khalid	10
1	ali	100
2	ahmed	55
2	khalid	40
2	ali	70
3	ahmed	30
4	ali	90
3	khalid	30
4	khalid	90

After selecting it will display another row with the sum of all quantities

SELECT SalesmanName ,SUM(Quantity) AS Qty
FROM Sales
GROUP BY ROLLUP(quantity)

Name	Qty
ahmed	165
ali	405
khalid	220
NULL	790
___
This will add rows for sum of quantities for each product and sum of all quantities
```sql
SELECT ProductID,SalesmanName,sum(quantity) AS "Quantities"
FROM sales
GROUP BY ROLLUP(ProductID,SalesmanName)
```

ProductID	ahmed	Quantities
1	ahmed	35
1	ali	145
1	khalid	30
1	NULL	210
2	ahmed	70
2	ali	90
2	khalid	70
2	NULL	230
3	ahmed	60
3	khalid	30
3	NULL	90
4	ahmed	170
4	ali	90
4	NULL	260
NULL	NULL	790
___
## Cube
Groups sales data by all combinations of product and salesman including subtotal for each salesman with each product and total sale for each product
```sql
SELECT ProductID,SalesmanName,sum(quantity) AS "Quantities"
FROM sales
GROUP BY cube(ProductID,SalesmanName)
```

ProductID	ahmed	Quantities
1	ahmed	35
2	ahmed	70
3	ahmed	60
NULL	ahmed	165
1	ali	145
2	ali	90
4	ali	170
NULL	ali	405
1	khalid	30
2	khalid	70
3	khalid	30
4	khalid	90
NULL	khalid	220
NULL	NULL	790
1	NULL	210
2	NULL	230
3	NULL	90
4	NULL	260
___
## Grouping Sets
Calculates sales quantities grouped separately by Product and by SalesmanName.
```sql
SELECT ProductID,SalesmanName,sum(quantity) AS "Quantities"
FROM sales
GROUP BY grouping sets(ProductID,SalesmanName)
```

ProductID	SalesmanName	Quantities
NULL	ahmed	165
NULL	ali	405
NULL	khalid	220
1	NULL	210
2	NULL	230
3	NULL	90
4	NULL	260
___
## Pivot
Transforms sales data by pivoting SalesmanName into columns (ahmed, ali, khalid) with their respective total Quantity values.
```sql
SELECT *
FROM sales
PIVOT (sum(Quantity) FOR SalesmanName IN ([ahmed],[ali],[khalid])) as Pvt
```

ProductId	ahmed	ali	khalid
1	35	145	30
2	70	90	70
3	60	NULL	30
4	NULL	170	90
___