Forum Replies Created

Viewing 15 posts - 5,356 through 5,370 (of 7,619 total)

  • RE: String as primary key

    Jeff Moden (2/27/2015)

    ScottPletcher (2/27/2015)

    Eric M Russell (2/27/2015)

    As a general rule, the clustered key should be sequentially incremented (to avoid page splits and accumulating fragmentation) and it should be narrow, because...

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

    February 27, 2015 at 11:44 am

    #1780738

  • RE: String as primary key

    Eric M Russell (2/27/2015)

    As a general rule, the clustered key should be sequentially incremented (to avoid page splits and accumulating fragmentation) and it should be narrow, because it will be...

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

    February 27, 2015 at 11:19 am

    #1780729

  • RE: String as primary key

    Lynn Pettis (2/27/2015)

    ScottPletcher (2/27/2015)

    Lynn Pettis (2/26/2015)

    ScottPletcher (2/26/2015)

    Either cluster directly on the string itself. Or, if you don't want to do, encode the string into an int (or smallint 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".

    February 27, 2015 at 10:46 am

    #1780718

  • RE: String as primary key

    Lynn Pettis (2/26/2015)

    ScottPletcher (2/26/2015)

    Either cluster directly on the string itself. Or, if you don't want to do, encode the string into an int (or smallint if you have less...

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

    February 27, 2015 at 9:25 am

    #1780655

  • RE: Run Stored Procedure without Writing to Transaction Log?

    Depending on the specifics, it might be less overhead to save what you need from the summary tables, truncate them, then do a bulk insert of everything back to them...

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

    February 26, 2015 at 2:33 pm

    #1780408

  • RE: Mortgage amortization table

    Change to money rather than more decimal places. I believe the law now restricts to 4 decimal places when calculating most interest accumulation.

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

    February 26, 2015 at 2:26 pm

    #1780407

  • RE: String as primary key

    Also, let's look at SQL's missing index and index usage stats for the table:

    USE [<your_db_name_here>] --change to desired db if not already there

    SET DEADLOCK_PRIORITY LOW --probably irrelevant, but just in...

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

    February 26, 2015 at 2:00 pm

    #1780404

  • RE: Insert data into Table variable but need to insert 1 or 2 rows depending on data

    Insert Into @OFBDATA

    (ShiftStartdate,

    ProdShiftcolor,

    Line,

    DieNo,

    Goodparts,

    ScrapParts)

    Select

    scrapdata.Rundate,ScrapData.ProdShiftcolor,ScrapData.Line,ScrapData.DieNo,

    case when which_row = 'F' then scrapdata.[Good parts F ] else scrapdata.[Good parts R ] END,

    case when which_row = 'F'...

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

    February 26, 2015 at 1:55 pm

    #1780401

  • RE: String as primary key

    Either cluster directly on the string itself. Or, if you don't want to do, encode the string into an int (or smallint if you have less than ~20,000 values)...

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

    February 26, 2015 at 1:25 pm

    #1780385

  • RE: Querying Last Quarter's data

    I think something roughly like this:

    (SELECT COUNT(DISTINCT daysworked) AS 'Days Worked'

    FROM (SELECT CAST(DATEPART(MM, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/' + CAST(DATEPART(DD, DATEADD(HOUR, -8, ActualEnd)) AS VARCHAR) + '/'...

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

    February 26, 2015 at 1:20 pm

    #1780383

  • RE: DEADLOCK PRIORITY issue

    That's interesting. I didn't realize SQL would convert that. Not sure exactly the optimizer knows when it's "safe" to do that.

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

    February 25, 2015 at 12:50 pm

    #1780043

  • RE: DEADLOCK PRIORITY issue

    mbhandari (2/25/2015)Thanks for your invaluable comments. I looked at the execution plans for IF( SELECT COUNT(*)... ) and IF EXISTS options and its giving me identical plan. But I am...

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

    February 25, 2015 at 12:27 pm

    #1780039

  • RE: DEADLOCK PRIORITY issue

    All true. However, you can improve the coding of the second query:

    Instead of:

    IF

    (

    SELECTCOUNT(*)

    FROMMY_CODE

    ) > 0 </frame>

    Use:

    IF EXISTS(SELECT TOP (1) 1 FROM MY_CODE)

    You're having...

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

    February 24, 2015 at 2:28 pm

    #1779751

  • RE: Default constraint?

    It is described in BOL. Under "ALTER TABLE", "table constraints". Notice that the text "[CONSTRAINT constraint_name]" is optional, followed by the required "DEFAULT constant_expression FOR column". Viz:

    ALTER...

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

    February 23, 2015 at 4:04 pm

    #1779441

  • RE: Using an expression in a GROUP BY clause

    Assuming you don't have a "TimeGroup" (as calc'd below) of 0, maybe this code will give you the exact result you want:

    SELECT

    Time_Min * 100 AS Time_Min,

    ...

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

    February 23, 2015 at 3:58 pm

    #1779440

Viewing 15 posts - 5,356 through 5,370 (of 7,619 total)