Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recent content by ZenRaven

  1. ZenRaven

    All possible combinations with a twist

    I had checked out that same post on SO and was trying to wrap my head around it while waiting for other possible responses on here. I got sidetracked and still haven't had the time to sit down and go through what it's actually doing. I'm going to come up with several test cases to run through...
  2. ZenRaven

    All possible combinations with a twist

    Dynamic number of groups
  3. ZenRaven

    All possible combinations with a twist

    You are correct, values will not show up in multiple groups.
  4. ZenRaven

    All possible combinations with a twist

    So there's 2 problems I need to overcome to get the recursive CTE to work. 1) The windowing function I used to give an incremental value to each row didn't work as expected. This is probably due to the way CTEs work. Good for performance, bad for me. The ROW_NUMBER windowing function does the...
  5. ZenRaven

    All possible combinations with a twist

    Nevermind, I got too excited and didn't realize I was only returning 23 rows, not 23 groups. Should be 46 rows with 23 distinct groups. Argh! Feeling a bit recursive... how appropriate
  6. ZenRaven

    All possible combinations with a twist

    I'm so close. The DENSE_RANK in the recursive member of the CTE is what's incorrect. I was hoping I could partition by a number of rows which is why I included the level. I'm using SS2012 and saw a ROWS clause but it kept throwing error about being used in a windowing function and I'm not sure...
  7. ZenRaven

    All possible combinations with a twist

    Thanks, that makes me feel better that it's not only me that has a hard time understanding it. Here's the manual, non-vertical method for producing the output CREATE TABLE #temp1 (GroupID INT, MyValue INT) INSERT INTO #temp1 (GroupID, MyValue) VALUES (1,8),(2,7),(2,9),(3,1),(3,6),(3,3)...
  8. ZenRaven

    All possible combinations with a twist

    I've been working on a problem that I can't quite figure out. I've tried different combinations of cross joins, CTEs, windowing functions, etc but could never quite get there. I'm also not wanting to go the dynamic SQL route. Can someone please help? Given a variable set of grouped values...
  9. ZenRaven

    FileGroup/File Configuration

    We're a small shop and this production box is the only one with an array large enough to handle these configurations so it's really not anything I can play around with. Is your "probably not" based on the fact that the file(s) are all on 1 logical drive or other factors? --Adam --"He who knows...
  10. ZenRaven

    FileGroup/File Configuration

    SQL Server 2008 R2 I have a 90 GB database running on RAID 10 with 8 spindles. I have 398 tables out of which 3 comprise 87% of all data. Here is the breakdown of percentage of total size: Table1 Clustered 24% Table1 NonClustered 22% Table2 Clustered 17% Table2 NonClustered 12% Table3...
  11. ZenRaven

    Complex Update on 3 Million or so Records

    I noticed you had a rowid. I tried something like this but it doesn't handle the last seq_num. You'd also have to do some conversion on seq_num as it looks like it isn't int. I haven't looked at it but the link markros provided is probably more elegant and efficient. DECLARE @table TABLE (...
  12. ZenRaven

    Complex Update on 3 Million or so Records

    Are there any other fields that identify the order these records are in? Maybe an identity field? How do you know any given INV 12 is actually "under" SEQ_NUM 00 and not 02? --Adam --"He who knows best knows how little he knows." - Thomas Jefferson
  13. ZenRaven

    Stored Procedure performance issue

    Just realized I wrote "unusually" in my previous post. Should have been "usually", ha. The index didn't make a difference since there are so few records in the temp table. --Adam --"He who knows best knows how little he knows." - Thomas Jefferson
  14. ZenRaven

    Stored Procedure performance issue

    The only scan is on a temp table which the main table joins to. The temp table unsually has under 5 rows. The seek on the main table is now 73% and the join to the temp table 27%. The query result set count varies but I would say anywhere between a couple hundred and 15,000 records usually...
  15. ZenRaven

    Stored Procedure performance issue

    Thought some of you might be interested in an update. While I started down the correct path with parameter sniffing, the final issue was a not-so-covering index. I was thrown off by the large variance in execution times because I was not taking into account the buffer cache. Clearing buffer and...
See more

Part and Inventory Search

Back
Top