If you were referred to this article in answer to your question in an SQL Server forum, you should read the short version of this article first and chances are very. Practice SQL Interview Question 2 This question was asked in a Google interview Given the 2 tables below, User and UserHistory User userid name phonenum. SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i. e. joining a leftouter. We have a number of Views, Stored Procs, Table Values and Scalar functions. How can we see how often or even how many times these have been called from within SQL ServerDifferences between SQL Server temporary tables and table variables. I stumbled across this very good article in my search to an explanation for a weird behaviour between two identical SQL servers 2. R2, Dell Powere. Edge 2. R, 4. GByte RAM. I run a view on which I group later and in total after grouping, there are never more than 3. Thats why Ive decided to do the post processing by means of storing the result after grouped in a table variable and do further processings with it. However, the one server gives me a time out, the other one returns a result set within 2s. Before grouping, were talking about 1. Im running the grouping statement using the view with an insert transact on the table variable. Probably the wrong approach, ist it VIEWCREATE VIEW dbo. Book. PerfASSELECT DISTINCT c. Jobnumber, s. Description AS Shift, c. Reel. Stand, c. Batch, c. Date. Stamp, c. b. Date AS Day, o. First. Name o. Surname AS User. Name, c. ASC,c. o. DESC, t. Make. Ready. Date. Time AS m. Ready,ISDATElm. Datestamp AS LM, lm. Datestamp AS d. LM,NULLIFCOALESCElm. Username,c. Username,c. Username AS u. LM, t. Production. Start. Date. Time AS p. Start, ISDATEfb. Date. Stamp AS LS, fb. Date. Stamp AS d. LS, ISDATEdb. Date. Stamp AS DB, db. Date. Stamp AS d. DB, t. Production. Finish. Date. Time AS p. End, ISDATElr. Date. Stamp AS LR, lr. Date. Stamp AS d. LR, COALESCElr. Username,c. Username AS u. LRFROM SELECT Jobnumber, Reel. Stand, Batch, Date. Stamp, CASTDate. Stamp AS DATE AS b. Date,ROWNUMBER OVER PARTITION BY Jobnumber, Reel. Stand ORDER BY Date. Stamp ASC AS o. ASC, ROWNUMBER OVER PARTITION BY Jobnumber, Reel. Stand ORDER BY Date. Stamp DESC AS o. DESC, ROWNUMBER OVER PARTITION BY Batch ORDER BY Date. Stamp ASC AS c. Piece, Username FROM Stock. Control. dbo. Consume c INNER JOIN Stock. Control. dbo. Batch AS b ON b. Batch c. Batch INNER JOIN Stock. Control. dbo. Tickets t ON c. Jobnumber t. Ticket. IDLEFT OUTER JOIN Stock. Control. dbo. Operator o ON c. Username o. User. Name LEFT OUTER JOIN Stock. Control. dbo. Shifts s ON t. End. Shift s. Shift late start bookings. LEFT OUTER JOIN Consume fb ONfb. Jobnumber c. Jobnumber AND fb. Batch c. Batch AND c. ASC 1 AND fb. Date. Stamp t. Production. Start. Date. Time late return bookings LEFT OUTER JOIN SELECT, ROWNUMBER OVER PARTITION BY Batch ORDER BY Date. Stamp ASC AS r. Piece FROM Stock. Control. dbo. Return lr ONc. Batch lr. Batch AND c. Piece lr. r. Piece AND c. DESC 1 AND lr. Date. Stamp t. Production. Finish. Date. Time double bookings LEFT OUTER JOIN SELECT ROWNUMBER OVER PARTITION BY Jobnumber, Reel. Stand ORDER BY Date. Stamp AS o. ASC FROM Stock. Control. dbo. Consume d. B ON c. o. ASC db. ASC 1 AND c. Date. Stamp db. Date. Stamp AND c. Jobnumber db. Jobnumber AND c. Reel. Stand db. Reel. Stand late moves LEFT OUTER JOIN Stock. Control. dbo. Move lm ON c. Batch lm. Batch AND c. ASC 1 AND lm. Datestamp BETWEEN t. Make. Ready. Date. Time AND c. Date. Stamp OR c. o. ASC 1 AND DATEDIFFmi,lm. Datestamp,c. Date. Stamplt 5 WHERE t. Status IN 3,4 AND t. Gross. Weight 1 GO2. GROUPING AND POST PROCESSINGDECLARE book. Perf TABLE g. Col VARCHAR3. Reels INT, LM INT, p. LM FLOAT, s. LM FLOAT, LS INT, p. LS FLOAT, s. LS FLOAT, DB INT, p. DB FLOAT,s. DB FLOAT, LR INT, p. LR FLOAT, s. LR FLOAT, p. Total FLOAT ,s. Total FLOAT INSERT book. Perf g. Col, Reels, LM, p. LM,LS, p. LS, DB, p. DB, LR, p. LR, p. TotalSELECT Shift AS g. Col, NULLIFCOUNTBatch,0 AS Reels, NULLIFSUMLM,0 AS LM, ROUND1. SUMLMCASTCOUNTBatch1e 6 AS FLOAT,2 AS p. LM,NULLIFSUMLS,0 AS LS, ROUND1. SUMLSCASTCOUNTBatch1e 6 AS FLOAT,2 AS p. LS,NULLIFSUMDB,0 AS DB, ROUND1. SUMDBCASTCOUNTBatch1e 6 AS FLOAT,2 AS p. DB, NULLIFSUMLR,0 AS LR, ROUND1. SUMLRCASTCOUNTBatch1e 6 AS FLOAT,2 AS p. LR, ROUND1. 001 SUMLM LS DB LRCASTCOUNTBatch1e 6 AS FLOAT,2 AS p. Total FROM Stock. Control. dbo. Book. Perf. WHERE Day BETWEEN 2. AND 2. 01. 3 0. AND OR NOT LM 1 AND o. ASc 1GROUP BY Shift ORDER BY Shift UPDATE book. Perf SET s. LM p. LM, s. LS p. p. LS,s. DB p. p. DB,s. LR p. p. LR, s. Total p. p. Total FROM SELECT MINp. LM AS p. LM, MINp. LS AS p. LS, MINp. DB AS p. DB, MINp. LR AS p. LR, MINp. Total AS p. Total FROM book. Perf p UPDATE book. Perf SET s. LM ISNULLp. LM s. LM1. 00 NULLIFs. LM,1. 00,1, s. LS ISNULLp. LS s. LS1. 00 NULLIFs. LS,1. 00,1, s. DB ISNULLp. DB s. DB1. 00 NULLIFs. DB,1. 00,1, s. LR ISNULLp. LR s. LR1. 00 NULLIFs. LR,1. 00,1, s. Total ISNULLp. Total s. Total1. NULLIFs. Total,0,1 SELECT ROUND1. LM,0 AS scaleLM, ROUND1. LS,0 AS scaleLS, ROUND1. DB,0 AS scaleDB, ROUND1. LR,0 AS scaleLR, ROUND1. Total,0 AS scaleTotal, Stock. Control. dbo. hex. Colors. LM AS c. LM,Stock. Control. Colors. LS AS c. LS,Stock. Control. Colors. DB AS c. DB,Stock. Control. Colors. LR AS c. LR,Stock. Control. Colors. Total AS c. Total FROM book. Perf ORDER BY p. Total ASC. CROSS APPLY Explained. My first introduction to the APPLY operator was using the DMVs. For quite a while after first being introduced, I didnt understand it or see a use for it. While it is undeniable that it is has some required uses when dealing with table valued functions, its other uses evaded me for a while. Luckily, I started seeing some code that used it outside of table valued functions. It finally struck me that it could be used as a replacement for correlated sub queries and derived tables. Thats what well discuss today. I never liked correlated subqueries because it always seemed like adding full blown queries in the select list was confusing and improper. SELECTSales. Order. ID soh. Sales. Order. ID,Order. Date soh. Order. Date,Max. Unit. Price SELECT MAXsod. Unit. Price FROM Sales. Sales. Order. Detail sod WHERE soh. Sales. Order. ID sod. Sales. Order. IDFROM Adventure. Works. Sales. Sales. Order. Header AS sohccIt always seemed to me that these operations should go below the FROM clause. So to get around this, I would typically create a derived table. Which didnt completely feel right either, but it was still just a bit cleaner cc langsqlSELECTsoh. Sales. Order. ID,soh. Order. Date,sod. maxunitprice. FROM Adventure. Works. Sales. Sales. Order. Header AS soh. JOINSELECTmaxunitprice MAXsod. Unit. Price,Sales. Order. IDFROM Sales. Sales. Order. Detail AS sod. GROUP BY sod. Sales. Order. ID sod. ON sod. Sales. Order. ID soh. Sales. Order. IDccWhat made this ugly was the need to use the GROUP BY clause because we could not correlate. Also, even though SQL almost always generates the same execution plan as a correlated sub query, there were times when the logic inside the derived table got so complex, that it would not limit the result set of the derived table by inferring the correlation first. This made this kind of query sometimes impractical. Luckily, this is where the CROSS APPLY steps in so nicely. It gives us the best of both worlds by allowing us to correlate AND not have the query embedded in the select list cc langsqlSELECTsoh. Sales. Order. ID,soh. Order. Date,sod. maxunitprice. FROM Adventure. Works. Sales. Sales. Order. Header AS soh. CROSS APPLYSELECTmaxunitprice MAXsod. Unit. PriceFROM Sales. Sales. Order. Detail AS sod. WHERE soh. Sales. Order. ID sod. Sales. Order. ID sodccThe other advantage this has over the correlated sub query is when we want to add more columns in our SELECT list, we do not have to completely repeat the entire query. We still have it in one place, making it somewhat modular. So instead of this cc langsqlSELECTSales. Order. ID soh. Sales. Order. ID,Order. Date soh. Order. Date,Max. Unit. Price SELECT MAXsod. Unit. Price FROM Sales. Sales. Order. Detail sod WHERE soh. Sales. Order. ID sod. Sales. Order. ID 1,Sum. Line. Total SELECT SUMLine. Total FROM Sales. Sales. Order. Detail sod WHERE soh. Sales. Order. ID sod. Sales. Order. ID 2. FROM Adventure. Works. Sales. Sales. Order. Header AS sohccWe have this cc langsqlSELECTsoh. Sales. Order. ID,soh. Order. Date,sod. maxunitprice,sod. FROM Adventure. Works. Sales. Sales. Order. Header AS soh. CROSS APPLYSELECTmaxunitprice MAXsod. Unit. Price,sumlinetotal SUMsod. Line. TotalFROM Sales. Sales. Order. Detail AS sod. WHERE soh. Sales. Order. ID sod. Sales. Order. ID sodccAs for the execution plans, in my experience CROSS APPLY has always won. Not always by a lot, but it still wins. So what is OUTER APPLY Its equivalent to a left join on the derived table. cc langsqlSELECTsoh. Sales. Order. ID,soh. Order. Date,sod. maxunitprice. FROM Adventure. Works. Sales. Sales. Order. Header AS soh. LEFT JOINSELECTmaxunitprice MAXsod. Unit. Price,Sales. Order. IDFROM Sales. Sales. Order. Detail AS sod. GROUP BY sod. Sales. Order. ID sod. ON sod. Sales. Order. ID soh. Sales. Order. IDcccc langsqlSELECTsoh. Sales. Order. ID,soh. Order. Date,sod. maxunitprice. FROM Adventure. Works. Sales. Sales. Order. Header AS soh. OUTER APPLYSELECTmaxunitprice MAXsod. Unit. PriceFROM Sales. Sales. Order. Detail AS sod. WHERE soh. Sales. Order. ID sod. Sales. Order. ID sodcc.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |