SQL Important Questions

CASCADE in SQL Server with example

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table.

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.

If you specify this option, when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The advantage of the ON DELETE CASCADE option is that it allows you to reduce the quantity of SQL statements needed to perform delete actions.

 Collapse | Copy Code

select * from dbo.ProductDetails

select * from dbo.Products

CREATE TABLE [dbo].[Products](

[ProductID] [int] NOT NULL,

[ProductDesc] [varchar](50) NOT NULL,

CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED

(

[ProductID] ASC

)) ON [PRIMARY]

CREATE TABLE [dbo].[ProductDetails](

[ProductDetailID] [int] NOT NULL,

[ProductID] [int] NOT NULL,

[Total] [int] NOT NULL,

CONSTRAINT [PK_ProductDetails] PRIMARY KEY CLUSTERED

(

[ProductDetailID] ASC

)) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ProductDetails] WITH CHECK ADD CONSTRAINT 

[FK_ProductDetails_Products] FOREIGN KEY([ProductID])

REFERENCES [dbo].[Products] ([ProductID])

ON UPDATE CASCADE

ON DELETE CASCADE

INSERT INTO Products (ProductID, ProductDesc)

SELECT 1, 'Bike'

UNION ALL

SELECT 2, 'Car'

UNION ALL

SELECT 3, 'Books'

INSERT INTO ProductDetails

([ProductDetailID],[ProductID],[Total])

SELECT 1, 1, 200

UNION ALL

SELECT 2, 1, 100

UNION ALL

SELECT 3, 1, 111

UNION ALL

SELECT 4, 2, 200

UNION ALL

SELECT 5, 3, 100

UNION ALL

SELECT 6, 3, 100

UNION ALL

SELECT 7, 3, 200

SELECT *

FROM Products

SELECT *

FROM ProductDetails

DELETE

FROM Products

WHERE ProductID = 1

DROP TABLE ProductDetails

DROP TABLE Products

Using Coalesce() in sqlserver

When we have multi-value attribute with single or more null values in a Table, the Coalesce() function is very useful.

Using the Code

If you consider the below facts placed in a employee table with Id, Name, ph_no, Alt_no, Office no.

id	Name	Ph_ no	Alt_ no	Office no
101	Albert	999999	456453	321333
102	khan	null	null	123455
103	victor	112121	null	null
104	lovely	null	null	1897321

The above Employee table may have single value or three values. If it has single value, then it fills null values with remaining attributes.

When we retrieve the number from employee table, that number Should Not be Null value. To get not null value from employee table, we use Coalesce() function. It returns the first encountered Not Null Value from employee table.

Collapse | Copy Code

select id , name ,coalesce(Ph_no,Alt_no,Office_no) as contact number from employee 

It returns:

id	Name	Contactnumber
101	Albert	999999
102	khan	123455
103	victor	112121
104	lovely	1897321

CROSS APPLY and OUTER APPLY 

The APPLY operator comes in two variants, CROSS APPLY and OUTER APPLY. It is useful for joining two SQL tables or XML expressions. CROSS APPLY is equivalent to an INNER JOIN expression and OUTER APPLY is equivalent to a LEFT OUTER JOIN expression.

Since APPLY works on a row-by-row level:

·         It is usually slower than JOIN due to its row-by-row nature. In many situations SQL Server's query planner will optimize APPLYs to run as if they are JOINs.    

·         They will normally match the speed of using single-field expressions in a query since they act in the same manner and will be optimised similarly. 

·         For "multi-field expressions" they will mostly exceed the speed of multiple single-field expressions in many scenarios since they will translate into a lower quantity of effective lookups. 

·         They will match or exceed the speed of UNPIVOT statements depending on query complexity. 

Explaining by example

Instead of giving definitions I would like to explain by example.  Think of CROSS APPLY as a row-by-row INNER JOIN.  If we have: 

SELECT *

FROM Vehicles V

INNER JOIN MileageLog ML ON V.ID = M.VehicleID  

to join a vehicle and its mileage log we could do exactly the same thing using CROSS APPLY: 

SELECT * 

FROM Vehicles V

CROSS APPLY (SELECT * FROM MileageLog ML WHERE V.ID = ML.VehicleID) ML 

These two queries will produce identical results.   We could use OUTER APPLY instead of CROSS APPLY to get the same effect as a LEFT JOIN.  That is 

SELECT * 

FROM Vehicles V

LEFT JOIN MileageLog ML ON V.ID = ML.VehicleID   

will give the same results as: 

SELECT *

FROM Vehicles V

OUTER APPLY (SELECT * FROM MileageLog ML WHERE V.ID = ML.VehicleID) ML 

Notice how our ON condition becomes a WHERE condition in the subquery. Also notice how we give an alias for the APPLY just like we can alias tables in a JOIN statement - this is required for APPLY statements. 

CROSS APPLY versus INNER JOIN

They're two slightly different animals that can be used for the same purposes, as in your example. CROSS APPLY is your only option for "joining" table value functions and "expanding" xml documents, though.

Some queries, particularly parallel queries, can exhibit vastly improved performance using CROSS APPLY, provided you have the requisite processor threads and indexing strategy.

While most queries which employ CROSS APPLY can be rewritten using an INNER JOIN, CROSS APPLY can yield better execution plan and better performance, since it can limit the set being joined yet before the join occurs.