Forum Replies Created

Viewing 15 posts - 721 through 735 (of 7,619 total)

  • Reply To: SQL Query Best Approach Recommendations

    Here's the original post:

    "I have a little exercise and would like to know your opinion on the most effective/best way to approach.  Please see the result I am looking 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".

    July 31, 2022 at 1:02 am

    #4070474

  • Reply To: Best approach for column data type alters for a large table?

    You're welcome.

    Yeah, you'll need lots of extra disk space.

    Presumably the log file is already a big size for a db that includes a table that size.  But you might want...

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

    July 29, 2022 at 6:22 pm

    #4070079

  • Reply To: Best approach for column data type alters for a large table?

    For example purposes, I'll assume your clustered index is on a $IDENTITY column, say "id".

    (10) Create a new table, with the new data types, and the clustered index (only!) 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".

    July 29, 2022 at 5:27 pm

    #4070062

  • Reply To: Execution Times vs Logical Reads/Scan Count

    You need to convert the "NOT IN"(s) to "NOT EXISTS"(s), like this:

    NOT EXISTS(SELECT 1 FROM TB_REINPUT TR WITH (NOLOCK) WHERE TR.ORDER_DATE>=@LAST_ORDER_DATE AND TR.STATION_ID=@P_STATION_ID AND

    TR.ORDER_DATE+COMMIT_NO = TB_WORK_ORDER_MFD01.ORDER_DATE+TB_WORK_ORDER_MFD01.COMMIT_NO)

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

    July 29, 2022 at 2:48 pm

    #4070002

  • Reply To: query that will return the information of every table in a database

    You don't really need sys.indexes in the row-count query:

    OUTER APPLY (
        SELECT SUM(p.rows) AS TotalRows
        FROM sys.partitions p
       ...

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

    July 28, 2022 at 6:08 pm

    #4069605

  • Reply To: Substring charindex Mid - Get name within String

    My approach was similar (I couldn't post to this site for a while):

    SELECT 
        text_column,
        LTRIM(RTRIM(SUBSTRING(text_column, CASE WHEN position_after_required_name = 0...

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

    July 28, 2022 at 6:05 pm

    #4069604

  • Reply To: Substring charindex Mid - Get name within String

    Need the sample data in a directly usable format, not in a picture, which we can't write T-SQL against.

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

    July 28, 2022 at 5:13 pm

    #4069597

  • Reply To: Get Saturday from the week specified in column

    The idea being that you can change the starting day to adjust for any day of the week.  0=Mon, 1=Tues, ..., 5= Sat, 6=Sun.

    For example, if you wanted the next...

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

    July 27, 2022 at 11:31 pm

    #4069183

  • Reply To: Get Saturday from the week specified in column

    I use a consistent method for any day, so I don't use techniques like the one given.

    Instead, I recommend:

    DATEADD(DAY, -DATEDIFF(DAY, 5, S1SDAT) % 7 + 7, S1SDAT)

    for example:

    SELECT

    S1SDAT,

    DATEADD(DAY, -DATEDIFF(DAY, 5,...

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

    July 27, 2022 at 11:29 pm

    #4069179

  • Reply To: Index created in the last few days

    There is no query that will tell you what index(es) were created in the last few days.  SQL Server itself does not store that info anywhere.

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

    July 27, 2022 at 12:54 am

    #4068689

  • Reply To: Using Lag while getting previous row record not working

    I'm very confused about what final results you want to see.  Here's a query that returns all columns; adjust it to return only the columns you want.

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

    July 25, 2022 at 7:10 pm

    #4068055

  • Reply To: Ordering Collation of Linked Server

    Paul White and/or Itzik Ben-Gan have done write ups on the problem with non-unique sorts.  The solution is indeed to make sure you specify ORDER BY values that will insure...

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

    July 25, 2022 at 6:16 pm

    #4068031

  • Reply To: How to get count and values merged by string aggregate 

    select m.CompanyId,m.PackageId,m.partsfamilyid,max(m.countparts) as countparts,
        STRING_AGG('(' + CONVERT(VARCHAR(8000), countPartsValues) + ')' + CONVERT(VARCHAR(8000), MSLIDValue),'|') WITHIN GROUP(ORDER BY MSLIDValue ASC) AS MSLDIFF  
    from #final m
    inner...

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

    July 21, 2022 at 5:38 pm

    #4066568

  • Reply To: Finding the row before the negative value 

    SELECT d1.*
    FROM #DETAIL d1
    WHERE d1.IDR < (SELECT MIN(d2.IDR) FROM #DETAIL d2 WHERE d2.ID = d1.ID AND d2.BALANCE < 0)

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

    July 20, 2022 at 4:59 pm

    #4066066

  • Reply To: @@DBTS changes by itself (overnight?)

    What is the definition of table1?

     

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

    July 20, 2022 at 3:46 pm

    #4066047

Viewing 15 posts - 721 through 735 (of 7,619 total)