Forum Replies Created

Viewing 15 posts - 2,506 through 2,520 (of 7,619 total)

  • Reply To: Weird CAST/TRY_CAST Behaviour

    Thom A wrote:

    kuopaz wrote:

    Out of interest - by 'later', are you saying that the CAST/ TRY_CAST is being executed in the SELECT before the WHERE?

    That shouldn't be possible. Per Logical...

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

    February 10, 2020 at 6:09 pm

    #3724460

  • Reply To: getting rid of "OR"

    I think creating a table and using EXCEPT / INTERSECT would perform better than a long string of ORs.  Either would also take care of NULL values for you.  So,...

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

    February 10, 2020 at 6:07 pm

    #3724459

  • Reply To: Need to replace this inefficient CASE statement

     

    WHERE PS.popup_gid = 5 OR
        (PS.popup_gid = 2 AND (T.record_Key_4 = '1' OR T.Record_Key_5 NOT IN ('I', 'X')))

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

    February 7, 2020 at 4:12 pm

    #3723987

  • Reply To: Getting only MM/DD from the Column of type varchar(50)

    Just CAST() the column as a date, that gives you the most flexibility with the format of the column:

    CONVERT(varchar(5), CAST(alertQueue.[Pickup_Date] AS date), 101) AS PickupDate

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

    February 6, 2020 at 6:49 pm

    #3723723

  • Reply To: How can we ensure whether database is migrated successfull from DBA Side

    Be sure to run the Data Migration Assistant on every user db.  Don't assume one db is "just like" another one.

    If the db is open and usable after it migrates,...

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

    February 5, 2020 at 3:55 pm

    #3723364

  • Reply To: Exclude Specific Index From Query

    Rats, I'd hoped it would work too.

    If it's keyed lookups, the size of the underlying table won't matter, SQL would still do the lookup, I understand that.

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

    February 4, 2020 at 5:46 pm

    #3723054

  • Reply To: get year/month min/max from dates

    Here's the general structure.  You can gen this out from the sys.columns table so you don't have to write out the code by hand.

    SELECT 
      ...

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

    February 3, 2020 at 9:42 pm

    #3722766

  • Reply To: Index suggestions for table

    Yes, there will be some limited page splits.  But the reads against the table will be vastly more efficient, even with the splits.

    It's certainly possible that [TimeDayId] then [Material_Werksdaten_key] would...

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

    January 31, 2020 at 7:04 pm

    #3722120

  • Reply To: How to work with case statement names

    Add an outer query, something like this:

    SELECT *, [Gross_Revenue] -  [Debits_and_Credits_Cost] AS NetRev
    FROM (
        SELECT Count(*) AS 'Count of Tranactions',
       ...

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

    January 31, 2020 at 6:23 pm

    #3722107

  • Reply To: Index suggestions for table

    You should cluster the table on:

    ( Material_Werksdaten_key, TimeDayId )

    Your join is very confusing.  You need to specify the TimeDayId(s) directly, not use a function on it.

    Is TimeDayId one value per...

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

    January 31, 2020 at 6:12 pm

    #3722105

  • Reply To: General SQL question/assistance 

    select Id, CompletedDate, Code 
    from (
        select *, row_number() over(partition by id order by completeddate desc) AS row_num
        from #temp_dat1
    ) as...

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

    January 30, 2020 at 10:55 pm

    #3721808

  • Reply To: How to avoid loop using delimitedsplit8k

    Ignore the previous entry, this is much closer:

    SELECT 
        MAX(CASE WHEN DS2.ItemNumber = 1 THEN DS2.Item ELSE '' END) AS Id,
       ...

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

    January 30, 2020 at 3:43 pm

    #3721662

  • Reply To: How to avoid loop using delimitedsplit8k

    Something like this?!  It's only a start, obviously.

    SELECT DS2.*
    FROM dbo.DelimitedSplit8K(@text, '|') DS1
    CROSS APPLY dbo.DelimitedSplit8K(DS1.Item, ',') DS2

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

    January 30, 2020 at 3:36 pm

    #3721658

  • Reply To: Recommendation on catching up with SQL from Older version

    Yep.  You'll need to go thru each "What's New" separately, for:

    SQL Server 2012; 2014; and 2016.

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

    January 29, 2020 at 10:59 pm

    #3721405

  • Reply To: Exclude Specific Index From Query

    Interesting q.  I've been looking into this, the best try I've found so far is to skew the row and page stats so high that SQL will almost certainly (?)...

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

    January 28, 2020 at 7:57 pm

    #3721001

Viewing 15 posts - 2,506 through 2,520 (of 7,619 total)