Forum Replies Created

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

  • 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

  • Reply To: Best Practice use of Synonyms to reference Other-Database Tables

    Kristen-173977 wrote:

    Jeff Moden (11/1/2015)

    At work, we don't allow the use of 3 or 4 part naming conventions anywhere except in the synonyms themselves and it has worked out extraordinarily well.

    I...

    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 27, 2020 at 7:31 pm

    #3720751

  • Reply To: Trigger for only updating specific column

    In the WHEN MATCHED part of the MERGE, specify only the columns you want UPDATEd.

    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 22, 2020 at 10:19 pm

    #3717584

  • Reply To: Error converting data type varchar to float

    OK, let's make a couple of quick fixes and then do a quick check for a couple of common errors, and see if we can find any bad data after...

    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 22, 2020 at 8:46 pm

    #3717562

  • Reply To: SQL Server Case statement conditions

    COALESCE(partially_NULL_column, joined_table.column, another_column_in_table_thats_never_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".

    January 22, 2020 at 5:54 pm

    #3717510

  • Reply To: Require Recommendations | Index Rebuild

    webrunner:

    You'd get orders of magnitude more improvement by changing all tables to make sure they have the best clustered index for overall performance and based on how data is (almost) always...

    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 21, 2020 at 9:27 pm

    #3717173

  • Reply To: Setting fill factor on an auto-increment INT clustered index

    Q1) Potentially, if the length of the varchar update was longer than the original status length.  For example, if the first status was 'New' but a later status was 'Updated',...

    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 21, 2020 at 7:46 pm

    #3717150

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