[T-SQL] Ranking functions, views, and predicates |
- Ranking functions, views, and predicates
- How can I create intervals fro start-dates only
- Sql Query
- Stop large queries from running
- BCP Error : Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'.
- outputting the Stored Proc output to Text File Pipe (|) Seperated with Headers
- For Learning
| Ranking functions, views, and predicates Posted: 18 Mar 2013 05:52 AM PDT So, I ran into a nifty little "bug" I'll call it. From what I can find this was an issue in SQL Server 2005 but supposedly addressed in SQL Server 2008. I'm running Microsoft SQL Server 2008 R2 (SP2) - 10.50.4260.0 (X64). Here is my scenario:View is defined as follows:[code="sql"]CREATE VIEW dbo.vw_BatchItemsSELECT BatchID, FieldA, FieldB, CAST(ISNULL(ROW_NUMBER() OVER (PARTITION BY BatchID ORDER BY DateEntered), 0) AS INT) AS CheckOrderFROM <myDatabase>.<mySchema>.<myTable>;[/code]Now when I code a procedure to query the view and add a WHERE clause it looks like this:[code="sql"]CREATE VIEW dbo.usp_sel_BatchItems (@BatchID INT)ASSELECT *FROM <myDatabase>.<mySchema>.vw_BatchItemsWHERE BatchID = @BatchID;[/code]We found that this procedure now is in our top 10 (well, the top 1 of the top 10) I/O consuming procedures (the underlying tables are large). While the query still runs fast we found that the ROW_NUMBER is causing an index scan on the index over BatchID. When we remove the ranking function from the view the SELECT statement now does an index seek on the index over BatchID. And after reviewing both execution plans it clearly shows that the predicate is not pushed to the view when the ROW_NUMBER() function is in the view. Without it the predicate is pushed to the view.Any thoughts on this one? Is there a way to force SQL server to perform the WHERE condition first instead of scanning the entire index to resolve the ROW_NUMBER() function? In our case, we only need the function to return results based on the filtered criteria anyway. |
| How can I create intervals fro start-dates only Posted: 18 Mar 2013 07:23 AM PDT Let's assume we have1) A key value, like a product number2) and a ValidFrom date, like thisMyProductKey, 20100111MyProductKey, 20110205MyProductKey, 20120101What we want is the followingMyProductKey, 20100111, 20110204MyProductKey, 20110205, 20111231MyProductKey, 20120101, NULLWhat would be the simplest way to do this?TIAPeter |
| Posted: 17 Mar 2013 07:02 PM PDT I have to return one result set by writing two different logic. Both of the logic are very complex.. one logic returns about 5 columns and other logic too returns 5 columns. I am not sure how to combine all columns and display the result in one place as there is not only one key.. there is no fixed key to link both results from temp table... Any idea? If my question is not clear then please let me know.. I will give an example.. |
| Stop large queries from running Posted: 17 Mar 2013 05:41 PM PDT Hi,I have a query window with a lot of code but I do not want to run it entirely in error, is there something I can place in the beginning of the code that will throw me a message?Thanks,Akber. |
| Posted: 18 Mar 2013 05:47 AM PDT DECLARE @Cmd VarChar(4000) DECLARE @SProcName VarChar(40) DECLARE @SP_Parm1Value VarChar(40) DECLARE @SP_Parm2Value VarChar(4000) set @SProcName = 'Rpt_JobTransactionsSp'set @SP_Parm2Value = '|SRMQDIC|,|ROD|,|B|,|HNS|,|0|,|B|,null,null,null,null,|000|,|9999|,|1/1/2013|,|3/17/2013|,null,null,null,null,null,null,null,null,|T|,null,null,|1|,|0|,|1033|'set @SP_Parm2Value = REPLACE (@SP_Parm2Value,'|', '''')-- Build BCP Command set @CMD = '' Set @Cmd = @Cmd + 'bcp "exec ' + db_name() Set @Cmd = @Cmd + '..' + @SProcName + ' 'Set @Cmd = @Cmd + @SP_Parm2Value + '"'Set @Cmd = @Cmd + ' Queryout ' + ' "c:\temp\query.txt" ' + ' -T -c -t^| -S '+ @@servernameSELECT @Cmd--output looks like thisbcp "exec RSDV_App..Rpt_JobTransactionsSp 'SRMQDIC','ROD','B','HNS','0','B',null,null,null,null,'000','9999','1/1/2013','3/17/2013',null,null,null,null,null,null,null,null,'T',null,null,'1','0','1033'" Queryout "c:\temp\query.txt" -T -c -t^| The following statement works great and gives me desired data in the grid.exec RSDV_App..Rpt_JobTransactionsSp 'SRMQDIC','ROD','B','HNS','0','B',null,null,null,null,'000','9999','1/1/2013','3/17/2013',null,null,null,null,null,null,null,null,'T',null,null,'1','0','1033'But when i execute the follwing commandEXEC master..xp_cmdshell @cmdErrors outoutput---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQLState = 37000, NativeError = 2812Error = [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.EXTGEN_InitSessionContextSp'.NULLThere is a prcedure 'dbo.EXTGEN_InitSessionContextSp' running inside 'Rpt_JobTransactionsSp'.Whats the workaround for this ? |
| outputting the Stored Proc output to Text File Pipe (|) Seperated with Headers Posted: 18 Mar 2013 04:56 AM PDT Command to runExec Rpt_OrderReport '1/1/2013' , '1/31/2013'Need a code which will save the output to Text file with headers and column data pipe (|) seperated. |
| Posted: 18 Mar 2013 12:00 AM PDT I want to improve my DB Knowledge , i have experience in MS-SQL and ORACLE.My Problem is when i face any interview i couldn't write query very fast .i have taken very long time to finished that query .please help me |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment