Forum Replies Created

Viewing 15 posts - 1,576 through 1,590 (of 7,613 total)

  • Reply To: VLOOKUP Equivalent in SQL 

    SELECT t1.ID, t1.Amount, t2.Amount_VLOOKUP
    FROM table1 t1
    CROSS APPLY (
        SELECT CASE
            WHEN t1.Amount < t2.col1 THEN 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".

    May 14, 2021 at 2:47 pm

    #3883471

  • Reply To: Removing prefix and suffix from a column 

    UPDATE yt
    SET name = LTRIM(RTRIM(name3))
    FROM your_table yt
    CROSS APPLY (
        SELECT CASE WHEN yt.name LIKE 'ZZZZ%' THEN STUFF(yt.name, 1, 4, '') ELSE yt.name END 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".

    May 14, 2021 at 2:45 pm

    #3883470

  • Reply To: high CPU

    Jeff Moden wrote:

    For my databases, I've adopted the "If it moves, shoot it" philosophy for statistics updpates.  In other words, if the RowModCntr is not zero and the stats haven't been...

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

    May 12, 2021 at 8:49 pm

    #3882752

  • Reply To: roles needed to execute Stored procedures/function in SQL server

     

    leo_dec wrote:

    Hi

    Thanks for your reply.

    Does that mean that I need to run below grant execute SP SQL Script for each SP for user?

    GRANT create procedure ON DATABASE::[db name] TO...

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

    May 11, 2021 at 2:53 pm

    #3881933

  • Reply To: SELECT column based on parameter

    tshad wrote:

    Is there a way in the same SELECT statement to show different columns without using dynamic SQL?

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

    May 11, 2021 at 2:48 pm

    #3881932

  • Reply To: SQL 2016 - Need help with complex calculations based on paycodes !

    Some of the amounts in the original didn't seem to add up, and the rules weren't specific for FTYPE and FCODE, but here's my best guess so far:

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

    May 10, 2021 at 7:14 pm

    #3881617

  • Reply To: Minimum permission for user to grant execute on a stored procedure they created

    I think a separate schema(s) is a valid approach.  Indeed, it has some advantages.  For one, the developer-created objects are in the db to which they apply.  If you create...

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

    May 10, 2021 at 4:00 pm

    #3881530

  • Reply To: SQL 2016 - Need help with complex calculations based on paycodes !

    Very busy right now, as soon as I get time, I'll follow up on this ... unless, of course, someone else has already jumped in and solved it.

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

    May 10, 2021 at 3:52 pm

    #3881527

  • Reply To: LATCH_EX when creating a clustered index

    Create the clustered index only before loading the table.  Create the non-clustered indexes after loading the table.

    If this is a permanent table, seriously consider using data compression.  Although it takes...

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

    May 10, 2021 at 3:49 pm

    #3881526

  • Reply To: Very strange hanging process...

    Yeah.  I'm not sure why MS hasn't changed the SQL Server default value for that setting.  They've made other adjustments to critical starting values, particularly for tempdb, but somehow haven't...

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

    May 10, 2021 at 2:10 pm

    #3881476

  • Reply To: SQL 2016 - Need help with complex calculations based on paycodes !

    DiabloSlayer wrote:

    So more logic changes requested by the client.

    Similar query but luckily we now have a week indicator with extra columns and new paycodes that should be left out of...

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

    May 9, 2021 at 7:54 am

    #3881089

  • Reply To: Minimum permission for user to grant execute on a stored procedure they created

    I don't see a huge security issue with a separate schema.  Thus, I don't see why this would necessarily fail an audit.  That seems to be an overly broad claim.

    I have...

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

    May 8, 2021 at 5:57 am

    #3880803

  • Reply To: Minimum permission for user to grant execute on a stored procedure they created

    I go the opposite way.  In development / QA / etc. (i.e., not prod!), I give developers most permissions.  The only thing I prevent -- when I'm allowed to --...

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

    May 7, 2021 at 5:56 pm

    #3880641

  • Reply To: How to find unique values in a table

    The very best solution to overall performance, and to blocking/deadlocking, is to go back and get the best clustered index keys on every table.  Hint: this will most often not, repeat...

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

    May 6, 2021 at 9:52 pm

    #3880303

  • Reply To: How to find unique values in a table

    Sorry, I somehow thought was from a different person (pietlinden), totally my fault, I was going on auto-pilot.

     

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

    May 6, 2021 at 9:49 pm

    #3880302

    • Viewing 15 posts - 1,576 through 1,590 (of 7,613 total)