Forum Replies Created

Viewing 15 posts - 2,551 through 2,565 (of 7,613 total)

  • Reply To: Index larger than table

    .1. The PK itself.

    .2. No.

    .3. The clustering key column(s) are stored in higher-level index entries.  The deeper the index depth, the more space the upper levels of the index take.

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

    #3713384

  • Reply To: Fifi Calculation

    Jeff Moden wrote:

    jcelko212 32090 wrote:

    >> I don't think I was particularly snarky.

    THAT, good Sir, is the crux of the problem. 😉

    I don't think his first post was particularly snarky, esp. for him,...

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

    #3713369

  • Reply To: Fifi Calculation

    jcelko212 32090 wrote:

    My next correction is a little more controversial. The reason that debits and credits exist has to do with the fact that when modern bookkeeping began in the Renaissance,...

    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 7, 2020 at 5:48 pm

    #3713303

  • Reply To: Difference between where and INNER JOIN comparing a value

    I left one out, the sample INNER JOIN:


    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A', 10),(2, 'B', 20), (3, 'C', 30)) AS data(key_col, col1, col2)
    ), cte_B...

    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 3, 2020 at 3:58 pm

    #3712169

  • Reply To: Difference between where and INNER JOIN comparing a value

    Here are queries to demonstrate the points above.  Assume for this example that B.col2 is a NOT NULL column.

    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A',...

    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 2, 2020 at 5:07 pm

    #3711894

  • Reply To: Difference between where and INNER JOIN comparing a value

    Grant Fritchey wrote:

    ...OUTER JOIN, if the filter is on the OUTER table, it needs to go to the JOIN criteria or it converts the OUTER JOIN to an INNER JOIN which...

    • This reply was modified 6 years, 6 months ago by ScottPletcher. Reason: Reworded, for clarity

    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 2, 2020 at 3:59 pm

    #3711830

  • Reply To: persisted computed column error

    You need to specify an explicit format (conversion code) for the date conversions, viz:

    CONVERT(varchar(100),ISNULL(despatch_group_start_date,'01/01/1900'), <format_code_needed_here>)

    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 2, 2020 at 3:52 pm

    #3711828

  • Reply To: Temporal Table Columns

    OK, so I guess the AFTER trigger would have to use the deleted table to check for UPDATEs vs. just relying on the UPDATE() function.

    CREATE TRIGGER dbo.TriggerSource_AFTER_UPDATE
    ...

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

    December 31, 2019 at 3:35 pm

    #3711462

  • Reply To: Temporal Table Columns

    I don't know, maybe I'm not thinking about it right, but I was anticipating something like below.  SQL checks a column value being UPDATEd and doesn't actually change it if...

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

    December 30, 2019 at 6:26 pm

    #3711252

  • Reply To: Temporal Table Columns

    Jeff Moden wrote:

    ScottPletcher wrote:

    I've had to use "extension" tables here a few times too, for various reasons.

    If you have an identity column present, do remember / be aware of the issues...

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

    December 30, 2019 at 4:52 pm

    #3711225

  • Reply To: Temporal Table Columns

    I've had to use "extension" tables here a few times too, for various reasons.

    If you have an identity column present, do remember / be aware of the issues with SCOPE_IDENTITY()...

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

    December 30, 2019 at 3:19 pm

    #3711194

  • Reply To: Temporal Table Columns

    For (1), you could add a column to the table itself to store the original login name there too (or, far better to save space, an id (that you assign)...

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

    December 27, 2019 at 8:40 pm

    #3710864

  • Reply To: Temporal Table Columns

    1.  No way around it.  The temporal table must exactly match the original schema.  My guess is that this provides efficiency in the way SQL Server internally implements the Temporal...

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

    December 27, 2019 at 7:16 pm

    #3710836

  • Reply To: Temporal Table Columns

    Jeff Moden wrote:

    2. ...   Current trigger technology won't even directly allow the saving of LOB columns because the LOB values don't show up in the INSERTED/DELETED logical tables in standard DML...

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

    December 27, 2019 at 6:25 pm

    #3710825

  • Reply To: Curious Case of SUM Function

    Great article, very interesting and thought provoking.

    Thanks again for taking the time to post this.

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

    December 24, 2019 at 3:38 pm

    #3710256

Viewing 15 posts - 2,551 through 2,565 (of 7,613 total)