Forum Replies Created

Viewing 15 posts - 496 through 510 (of 7,613 total)

  • Reply To: what's the performance difference between unique clustered index and primary key

    CreateIndexNonclustered wrote:

    ScottPletcher wrote:

    CreateIndexNonclustered wrote:

    It's 16 Gb. My laptop from 10-11 years ago had double that amount of ram. My phone from 2010ish with its 480x800 resolution screen had that amount...

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

    March 17, 2023 at 4:41 am

    #4161750

  • Reply To: what's the performance difference between unique clustered index and primary key

    CreateIndexNonclustered wrote:

    It's 16 Gb. My laptop from 10-11 years ago had double that amount of ram. My phone from 2010ish with its 480x800 resolution screen had that amount of storage.

    Maybe...

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

    March 16, 2023 at 9:38 pm

    #4161676

  • Reply To: what's the performance difference between unique clustered index and primary key

    A PK is not necessarily an irrelevant cost.  If I have 2B rows, for example, the "free" PK costs me at least 16GB of row and storage space.

    Some tables simply...

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

    March 16, 2023 at 7:47 pm

    #4161633

  • Reply To: Is it possible to SUM on two different ranges in one SELECT query? 

    SELECT 
        SUM(CASE WHEN t.create_date BETWEEN @v_from_date AND @v_to_date THEN t.max_column_id_used ELSE 0 END) AS tables_created,
        SUM(CASE WHEN t.modify_date BETWEEN @v_from_date...

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

    March 16, 2023 at 2:49 pm

    #4161510

  • Reply To: what's the performance difference between unique clustered index and primary key

    In certain cases or usage situations, SQL Server will require a primary key.  One reason is that a primary key is guaranteed to be unique and non-NULL (that is, by...

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

    March 16, 2023 at 2:16 pm

    #4161505

  • Reply To: system_versioning = on/off permissions

    Jeffrey Williams wrote:

    Can you explain further why you want to allow this user to enable/disable system versioning?  As far as I am aware - the only reasons to temporarily disable versioning...

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

    March 15, 2023 at 9:42 pm

    #4161231

  • Reply To: what's the performance difference between unique clustered index and primary key

    No.  Performance-wise they are exactly the 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".

    March 15, 2023 at 7:29 pm

    #4161155

  • Reply To: Average runtime beyond 24 hr

    To get durations beyond 12 hours, I think you can just do:

    WHERE h.run_duration > 120000

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

    March 15, 2023 at 5:54 pm

    #4161087

  • Reply To: system_versioning = on/off permissions

    If the proc as an appropriate "EXEC AS" clause, I would think it would work.

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

    March 14, 2023 at 6:40 pm

    #4160701

  • Reply To: How to make sure for each ID there was no records exists with text

    Yes, in theory you should be able to use just a WHERE clause and "HAVING COUNT(*) < 3", but I don't like to assume that there aren't duplicate description entries.

    Also,...

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

    March 13, 2023 at 9:53 pm

    #4160325

  • Reply To: How to make sure for each ID there was no records exists with text 

    SELECT ID
    FROM ExampleTable
    WHERE Description IN ('Apex', 'BILL', 'SIERRA')
    GROUP BY ID
    HAVING MAX(CASE WHEN Description = 'Apex' THEN 1 ELSE 0 END) = 0 OR
        MAX(CASE...

    • This reply was modified 3 years, 3 months ago by ScottPletcher. Reason: Add WHERE clause to perhaps improve efficiency

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

    March 13, 2023 at 3:31 pm

    #4160180

  • Reply To: SELECT with computed values

    I think adding the WeekDay and Hour columns to the main table make the most sense.  Computed columns will be more efficient than a trigger, but they do have some...

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

    March 6, 2023 at 9:09 pm

    #4157453

  • Reply To: Use a flag, or calculate, to retrieve counts

    The current approach seems ok to me, as long as people are rigorous about changing the status to "Declined" or "Cancelled" if the day can't be taken as scheduled.

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

    March 6, 2023 at 6:41 pm

    #4157413

  • Reply To: Help with database design

    Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    ScottPletcher wrote:

    Jonathan AC Roberts wrote:

    I believe it's acceptable to use either singular or plural names for database table names,

    Acceptable, perhaps.  But can you name any major RDBMS...

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

    March 6, 2023 at 3:58 pm

    #4157331

  • Reply To: Hash Match performance issue

    It's possible that creating your own statistics would help SQL here, but without more details, it's impossible to say what stats to 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".

    March 6, 2023 at 2:56 pm

    #4157301

Viewing 15 posts - 496 through 510 (of 7,613 total)