Forum Replies Created

Viewing 15 posts - 556 through 570 (of 7,613 total)

  • Reply To: Subtract time passed midnight

    I just did a few sample rows, I'm not going to type in all that data.

    DROP TABLE IF EXISTS #data;
    CREATE TABLE #data ( ENTRY_HOUR tinyint NULL, ENTRY_MINUTE...

    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 25, 2023 at 4:01 pm

    #4141722

  • Reply To: How to Reseed an Identity Column on Temp Table After data has been removed

    You cannot update the value of an identity column.  You would to insert a new row and delete the old row.  That will be way too much overhead.

    And, as Thom...

    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 23, 2023 at 2:43 pm

    #4140816

  • Reply To: Conventions when writing queries/stored procedures

    Jeff Moden wrote:

    edwardwill wrote:

    ...but I don't think I'll ever be converted to leading commas - I think that's at least partly because I'm an English language pedant and believe that 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 22, 2023 at 8:42 pm

    #4140576

  • Reply To: Read data based on date-range in huge table is slow

    I wouldn't just arbitrarily add a non-clus index without checking existing index usage ("index usage stats") AND reviewing missing indexes ("missing index" stats).

    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 20, 2023 at 8:14 pm

    #4140165

  • Reply To: Conventions when writing queries/stored procedures

    edwardwill wrote:

    Anyway, my question is to do with square brackets and including schema names. ... My inclination is to leave off as much stuff as possible (he also used 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".

    January 20, 2023 at 8:10 pm

    #4140163

  • Reply To: Read data based on date-range in huge table is slow

    How do you typically query the table?  If you normally query by a range of datetime, then changing the clus index makes perfect sense.

    First, change the SET @table_name_pattern = '%'...

    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 20, 2023 at 3:57 pm

    #4140079

  • Reply To: Help with Joins

    Something like this.  The key is using aliases (AS ...) for the tables to make the code easier to write and to follow:

    SELECT t0.column1, t1.Description, t2.Description, ...
    FROM...

    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 18, 2023 at 9:48 pm

    #4139199

  • Reply To: Troubles with in memory OLTP performance degradation

    Although SQL itself should issue CHECKPOINT commands when being shut down, I've found that explicitly issuing them myself prior to shutdown often allows SQL to restart faster.  So, personally, yes,...

    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 18, 2023 at 4:02 pm

    #4139079

  • Reply To: Join aggregated queries via a date formatted YYYY-MM

    First try getting rid of extraneous parts of the query (reducing the GROUP BY in the outer query):

    SELECT [W_SE-MFG].dbo.YearMonthNo(J1.EntryDate) AS Per, SUM(J1.RunTime) AS TReg, MAX(J2.TNQ) AS TNQ
    FROM...

    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 17, 2023 at 8:32 pm

    #4138753

  • Reply To: Parsing Stored Procedures for Table Dependencies

    Maybe just scan the procedure text for "~TRUNCATE TABLE~" (where ~ is not A-Z0-9$_@).  Then parse out the next word from the text to get the table name.

    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 12, 2023 at 10:18 pm

    #4137071

  • Reply To: Job History says running, but Job Activity says idle

    Again, are you only looking at history for the current instance_id value?

    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 12, 2023 at 8:37 pm

    #4137026

  • Reply To: Job History says running, but Job Activity says idle

    webrunner wrote:

    ScottPletcher wrote:

    What do you mean "the job history says the job is running."  The jobhistory never shows that, it only show the past history.

    Be sure to verify the instance_id. ...

    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 12, 2023 at 8:36 pm

    #4137025

  • Reply To: Job History says running, but Job Activity says idle

    What do you mean "the job history says the job is running."  The jobhistory never shows that, it only show the past history.

    Be sure to verify the instance_id.  If it's...

    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 12, 2023 at 7:53 pm

    #4136992

  • Reply To: ISNULL in case statement

    ISNULL/COALESCE is not as clear as explicitly checking for NULL:

    SELECT COL1,
    CASE WHEN Table1.COL2 = 0 then 'ZERO'
    WHEN Table1.COL2 = 1 OR Table1.Col2 IS NULL then 'ONE'
    ELSE ''...

    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 10, 2023 at 6:48 pm

    #4136057

  • Reply To: Can log backups cause Operating system error 32?

    No, database and/or log backups would not cause this error.

    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 9, 2023 at 5:38 pm

    #4135590

Viewing 15 posts - 556 through 570 (of 7,613 total)