Forum Replies Created

Viewing 15 posts - 2,116 through 2,130 (of 7,619 total)

  • Reply To: Practice SQL querying with dates and aggregations

    Lord Slaagh wrote:

    Scott - I agree with Jeff in the sense that this test is somewhat unrealistic because you have CTE's and sub queries at your disposal. However, if this is...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 29, 2020 at 5:50 pm

    #3814913

  • Reply To: Join to lookup table on multiple columns

    Jeffrey Williams wrote:

    Except that single outer apply will be executed for each row in the Meter table - and the data must be sorted each time based on the filters from...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 28, 2020 at 1:31 am

    #3814597

  • Reply To: Practice SQL querying with dates and aggregations

    Pretty good overall, reasonably close.

    But:

    the middle score will not necessarily be the avg score (sum/count is same as avg);

    if there are fewer than 3 scores, leave extra scores NULL.  For...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 28, 2020 at 1:28 am

    #3814596

  • Reply To: Join to lookup table on multiple columns

    Jeffrey Williams wrote:

    Just curious - but how are these solutions better than using multiple joins?  If this is a performance related issue - fix the use of the upper functions on...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 27, 2020 at 7:20 pm

    #3814539

  • Reply To: Practice SQL querying with dates and aggregations

    Here's the table structure:

    student_scores ( student_id int NOT NULL, assignment_number int NOT NULL, score int NOT NULL )

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 27, 2020 at 7:16 pm

    #3814538

  • Reply To: How to know which index is invalid index on a table?

    Typically, you'd want columns used in the WHERE condition to be in the index keys, for the reasons noted by Mr. Gale.

    select *from SaleOrderLines where OrderID='value1' and itemid=value2' and status='open'

    However,...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 27, 2020 at 3:23 pm

    #3814476

  • Reply To: Join to lookup table on multiple columns

    Yeah, sorry, adjust all the "d." from the query.  You didn't provide any usable sample data for us to test with, so you can't expect fully tested code :-).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers".  Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 27, 2020 at 3:12 pm

    #3814475

  • Reply To: Join to lookup table on multiple columns

    You can use a single table scan / lookup and will often get better performance from doing so:

    select t.*, s.Description as SubTypeCD_DESC, 
        d.AOD_METHOD_DESC,...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 27, 2020 at 1:49 am

    #3814317

  • Reply To: comma issues in sql statement

    Jeff Moden wrote:

    As a bit of a sidebar, your WHERE clauses for the start (>=) and end dates (<) are CORRECT.  Your calculation for end date is not.  You could miss...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 27, 2020 at 1:25 am

    #3814315

  • Reply To: How to know which index is invalid index on a table?

    For best overall performance, it's extremely likely that you need to change the clustering index on the table to be ( OrderID, ItemID ).  Especially if the table is now...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 27, 2020 at 1:14 am

    #3814314

  • Reply To: Practice SQL querying with dates and aggregations

    This is the main test q I use to test query-writing skill.  It's not as trivial / obvious as it sounds at first.

    A table contains 1, 2 or 3 rows...

    • This reply was modified 5 years, 9 months ago by ScottPletcher. Reason: Reworded the description of the table to make it clearer

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 26, 2020 at 3:47 am

    #3814083

  • Reply To: IF IF IF versus IF Condition 1 and condition 2 and condition X

    Jeff Moden wrote:

    You guys must be suffering from Covid or something.  You guys know better than to banter about with claims of performance without demonstrative code to prove your position. Especially...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 26, 2020 at 3:44 am

    #3814081

  • Reply To: How to know which index is invalid index on a table?

    Yes, you can drop IDX1; IDX12 can cover all queries that would use IDX1.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 26, 2020 at 3:42 am

    #3814080

  • Reply To: Primary Key/Foreign Key Same Field in 1:1 Relationship?

    Sure, you can do that.  And you should do that.

    You could also move those columns (attributes) into the main table, but it's often useful to have a separate 1-1 table...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 25, 2020 at 3:41 pm

    #3813910

  • Reply To: Event Notifications - Can these be set up to monitor for changes to table data?

    No, you'd use table DML triggers to handle table modifications (DELETE  and/or INSERT and/or UPDATE).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    November 24, 2020 at 7:03 pm

    #3813607

    • Viewing 15 posts - 2,116 through 2,130 (of 7,619 total)