Forum Replies Created

Viewing 15 posts - 661 through 675 (of 7,619 total)

  • Reply To: query one table three for three different where variables

    brian.cook wrote:

    Hi Scott, getting an error on Column not found in this piece;

    MAX(CASE WHEN month = @month AND year = @year THEN ControlPointName END)

    That one is part of the...

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

    September 8, 2022 at 4:33 pm

    #4085501

  • Reply To: Database Inconsistent Errors

    Run CHECKDB to determine errors.  Backup the existing db to make sure you'll never be worse off than you are now.

    If an error is in a non-clustered index, simply drop...

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

    September 8, 2022 at 4:30 pm

    #4085500

  • Reply To: query one table three for three different where variables

    I think this will do it, but I have not tested it.


    SELECT
        MAX(CASE WHEN month = @month AND year = @year THEN ControlPointName END)...

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

    September 8, 2022 at 4:13 pm

    #4085496

  • Reply To: How do I query to exclude the "first position of a 3 digit number"

    Jeff Moden wrote:

    ScottPletcher wrote:

    I'd strongly urge you to avoid a tinyint.  Mainly because the room number is "904", not just 4 (and not 94).  If you think about it, "room 4"...

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

    September 7, 2022 at 4:25 pm

    #4085022

  • Reply To: 2 new Column from string 

    ;WITH cte_test_data AS (
        SELECT CAST('163 Bathroom {BATHROOM CLEANING}{2.5%}' AS varchar(100)) AS string
    )
    SELECT
        string AS original_string,
        CASE WHEN...

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

    September 7, 2022 at 4:18 pm

    #4085018

  • Reply To: How do I query to exclude the "first position of a 3 digit number"

    I'd strongly urge you to avoid a tinyint.  Mainly because the room number is "904", not just 4 (and not 94).  If you think about it, "room 4" is meaningless,...

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

    September 6, 2022 at 3:47 pm

    #4084534

  • Reply To: How do I query to exclude the "first position of a 3 digit number"

    Jeff Moden wrote:

    Steve Jones - SSC Editor wrote:

    ScottPletcher wrote:

    I have to say, in this case I would use a char/varchar column for room numbers, not an int.  Even if the hotel guest rooms are all...

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

    September 4, 2022 at 11:38 pm

    #4083835

  • Reply To: How do I query to exclude the "first position of a 3 digit number"

    I have to say, in this case I would use a char/varchar column for room numbers, not an int.  Even if the hotel guest rooms are all numeric, sometimes other...

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

    September 3, 2022 at 10:53 pm

    #4083655

  • Reply To: How do I query to exclude the "first position of a 3 digit number"

    Phil Parkin wrote:

    aaron.reese wrote:

    Agreed, which is why it should be stored as two separate fields.  Could do it on the table with a couple of computed columns.

    Or do it the way...

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

    September 2, 2022 at 7:39 pm

    #4083438

  • Reply To: Finding first record that meets conditions + allowing grouping

    Lol, that's exactly what I came up with:

    ;WITH cte_city_months AS (
        SELECT CT.CityID, CT.MonthID, ROW_NUMBER() OVER(PARTITION BY CT.CityID ORDER BY CT.MonthID) AS row_num
     ...

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

    August 31, 2022 at 6:08 pm

    #4082555

  • Reply To: Clustered Index leaves a lot of empty space

    Of course that's just the general technique.  You'd want to use random values in a range to represent NULLs -- such as any date between, say, Jan 01, 1901 and...

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

    August 31, 2022 at 3:15 pm

    #4082453

  • Reply To: Clustered Index leaves a lot of empty space

    And here's the trigger.  The app INSERTs / UPDATEs NULL values, and SELECTs return NULL values, but a NULL never actually appears in the table.

    Again, I've never had this type...

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

    August 31, 2022 at 2:55 pm

    #4082446

  • Reply To: Clustered Index leaves a lot of empty space

    Here's a sample table and the corresponding view, with one non-nullable datetime and one non-nullable int column that have placeholder values that return NULL when querying the table.

    For now, I'll...

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

    August 30, 2022 at 1:32 pm

    #4081988

  • Reply To: Clustered Index leaves a lot of empty space

    Jeff Moden wrote:

    ScottPletcher wrote:

    You state, "A monster index will have to be rebuilt where it may not have been fragmented at all without compression."  What is the basis for that claim?

    Same...

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

    August 29, 2022 at 6:44 pm

    #4081719

  • Reply To: All spids showing resource semaphore waits

    (1) What is the 'cost threshold for parallelism' on that instance?  If it's too low, SQL could be trying use parallelism far too often.

    (2) Check the SQL log for error-type...

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

    August 29, 2022 at 2:49 pm

    #4081644

Viewing 15 posts - 661 through 675 (of 7,619 total)