Forum Replies Created

Viewing 15 posts - 2,566 through 2,580 (of 7,613 total)

  • Reply To: Truncate or Insert data with conditions

    To give the best chance of using minimal logging, be sure to lock the table that is being INSERTed into:

    ...

    INSERT INTO dbo.B WITH (TABLOCK)

    ...

    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:34 pm

    #3710255

  • Reply To: Reusable Variable

    That's an extraordinarily complex task when you really look into the more difficult cases, with honorifics (Dr., etc.), suffixes (III, IV) and so on.

    I would do this process one time,...

    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 23, 2019 at 11:03 pm

    #3710149

  • Reply To: Reusable Variable

    Or this:

    SELECT lastName, LEFT(lastName, 5) AS lName FROM myTable

    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 23, 2019 at 7:57 pm

    #3710114

  • Reply To: Subtract values from records within the same column 

    SELECT SUM(Expenses) AS Expense, Vendor, MAX(UploadDate) AS UploadDate
    FROM dbo.a_table
    GROUP BY Vendor

    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 19, 2019 at 7:30 pm

    #3709223

  • Reply To: Scalar function use results of exec(@sql)

    If it's not Azure, i.e. you have access to the master db, create a proc in the master db and you can use it from any db and it will...

    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 19, 2019 at 3:29 pm

    #3709149

  • Reply To: Issue using ROWNUM

    You don't have a FROM clause in the subquery, so it's not really a valid subquery, even if the "Z" alias is defined somewhere else in the query.

    You'd be better...

    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 18, 2019 at 7:32 pm

    #3708885

  • Reply To: Count with different intervals

     

    declare @minutes_interval int
    set @minutes_interval = 15 /*or 10 or 60 or whatever*/

    select 
        dateadd(minute, datediff(minute, 0, starttime) / @minutes_interval * @minutes_interval, 0),
      ...

    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 18, 2019 at 3:43 pm

    #3708830

  • Reply To: Question on Index

    Based on the extremely limited info so far, I would say this is likely the (very) rare case where you should leave the main table as a heap and 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".

    December 17, 2019 at 8:36 pm

    #3708605

  • Reply To: Convert char to datetime

    Datetimes are char data when inputting to SQL Server.  As long as the value is valid, don't specify your own CAST or CONVERT, let SQL do it implicitly.

    If you have...

    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 16, 2019 at 8:59 pm

    #3708281

  • Reply To: Creating Split backups Question

    Several.  In our normal course of business, we do periodic restores, and I request periodic restores just to make sure they're working correctly.  This particular company has done extremely well...

    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 16, 2019 at 5:45 pm

    #3708235

  • Reply To: Creating Split backups Question

    I gotta say, my life's been so much easier since we out-sourced the backup part of my DBA job.  I just define the backup rules, and someone else insures 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".

    December 16, 2019 at 3:48 pm

    #3708192

  • Reply To: Pivot Query

    Here it is.

    DECLARE @end_date date
    DECLARE @semester_name nvarchar(10)
    DECLARE @sql nvarchar(max)
    DECLARE @sql2 nvarchar(max)
    DECLARE @start_date date

    SET @semester_name = 'Winter'

    SELECT TOP (1) @start_date = begDtg, @end_date = endDtg
    FROM dbo.trainingSemester
    WHERE semesterName =...

    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 13, 2019 at 7:46 pm

    #3707772

  • Reply To: Having trouble with simple select query where all ordere item statuses = shipped

     

    SELECT OrderId
    FROM OrderItems
    GROUP BY OrderId
    HAVING SUM(CASE WHEN Status = 'Shipped' THEN 1 ELSE 0 END) < COUNT(*)

    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 13, 2019 at 5:17 pm

    #3707742

  • Reply To: Creating Split backups Question

    frederico_fonseca wrote:

    ScottPletcher wrote:

    If you're also looking into overall performance, don't neglect to specify both BUFFERCOUNT and MAXTRANSFERSIZE.

    Yup - I did that in a few cases where squeezing any amount of...

    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 12, 2019 at 6:05 pm

    #3707477

  • Reply To: Reset Sequences

    Issue an ALTER SEQUENCE statement with the change(s) you want to make.

    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 12, 2019 at 6:04 pm

    #3707476

Viewing 15 posts - 2,566 through 2,580 (of 7,613 total)