//Inner Join
WITH t2o AS
(
SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
FROM t2
)
SELECT
t1.*, t2o.*
FROM t1
INNER JOIN t2o ON t2o.t1_id = t1.id AND t2o.rn <= 3 //Record fillter after join
SELECT t1.* FROM sys.objects t1
INNER JOIN myFunction(O.name) F ON F.schema_id= O.schema_id //NOT USE FUNCTION : gettin error in inner join
//Cross Apply
SELECT t1.*, t2o.*
FROM t1
CROSS APPLY // First fillter record then join is done --Result Incres speed
(
SELECT TOP 3 * FROM t2
WHERE t2.t1_id = t1.id
ORDER BY
t2.rank DESC
) t2o
SELECT F.* FROM sys.objects O
CROSS APPLY //Not Error
(
SELECT * FROM myFunction(O.name) WHERE F.schema_id= O.schema_id
WITH t2o AS
(
SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
FROM t2
)
SELECT
t1.*, t2o.*
FROM t1
INNER JOIN t2o ON t2o.t1_id = t1.id AND t2o.rn <= 3 //Record fillter after join
SELECT t1.* FROM sys.objects t1
INNER JOIN myFunction(O.name) F ON F.schema_id= O.schema_id //NOT USE FUNCTION : gettin error in inner join
//Cross Apply
SELECT t1.*, t2o.*
FROM t1
CROSS APPLY // First fillter record then join is done --Result Incres speed
(
SELECT TOP 3 * FROM t2
WHERE t2.t1_id = t1.id
ORDER BY
t2.rank DESC
) t2o
SELECT F.* FROM sys.objects O
CROSS APPLY //Not Error
(
SELECT * FROM myFunction(O.name) WHERE F.schema_id= O.schema_id
No comments:
Post a Comment