ScottPletcher

Forum Replies Created

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

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,...

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...

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...

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, 7 months ago by ScottPletcher. Reason: Reworded the description of the table to make it clearer

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...

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.

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...

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).

November 24, 2020 at 7:03 pm

#3813607

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

Eirikur Eiriksson wrote:

ScottPletcher wrote:

Eirikur Eiriksson wrote:

  IF (
        (
            (ABS(SIGN(@Var1-@VarA)))...

November 24, 2020 at 6:14 pm

#3813579

Reply To: Simple Recovery Model - Active VLFs cannot be truncated

jdlayton83 wrote:

As far as I know, after doing a lot of research, once you deploy in memory tables you cannot delete filegroups. It's some kind of product limitation....

Yep, that is...

November 23, 2020 at 10:41 pm

#3813223