Forum Replies Created

Viewing 15 posts - 2,431 through 2,445 (of 7,619 total)

  • Reply To: Adding another table to a query but only wanting the top 1 for each record

     

    SELECT ...same_as_before_except_add_patches_columns...
    FROM customers

    LEFT OUTER JOIN (
        SELECT *, ROW_NUMBER() OVER(PARTITION BY customer ORDER BY Created DESC) AS row_num
        FROM [CIS].[dbo].[patches]
    ) AS...

    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 26, 2020 at 4:47 pm

    #3737836

  • Reply To: Importing csv file into SQL table

    What I usually do in those situations, if the file's not way too extremely large (2GB max file size; for any size up to 1.5GB, I've found that it takes...

    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 24, 2020 at 9:27 pm

    #3736778

  • Reply To: Room booking system - calculating room occupancy

    I think this will give you what you need.  I went down to the minute to allow bookings like '10:10' to '10:45'.  You may not have that now, but it...

    • This reply was modified 6 years, 5 months ago by ScottPletcher. Reason: Added comment about tally table size
    • This reply was modified 6 years, 5 months ago by ScottPletcher. Reason: Changed the tally table to be 10K rows instead of 1000

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

    #3736377

  • Reply To: I was asked to get rid of this Dynamic SQL for performance purposes.

    You're right, you can't avoid dynamic SQL here.  You need dynamic SQL here to insure you get the best query plan.

    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 20, 2020 at 2:36 pm

    #3735866

  • Reply To: Get the correct output

    Steve Collins wrote:

    Steve and Jeff, thank you.  Good to have other eyes on this. 🙂  What should I do with the earlier incorrect code?  Wipe it out?  Should I have kept...

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

    #3735711

  • Reply To: Get the correct output

    I'd stick with something more SQL-ish, and also more readily readable and understandable, like below.  If you're converting Access to SQL Server, it's convenient to use IIF, otherwise avoid it. ...

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

    #3735704

  • Reply To: Concatenating to a string

    Phil Parkin wrote:

    I'd make it less cluttered by using a variable for the literal single quote:

    DECLARE @H CHAR(1) = '''';

    the CONCAT version then becomes something like 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".

    March 19, 2020 at 7:53 pm

    #3735699

  • Reply To: Concatenating to a string

     

    '''' + p.firstname + ''',''' +
    p.lastname + ''',''' +
    i.city + ''',''' +
    t.stateAbbrveation + ''',''' +
    Convert(char(5), c.peopleId) + ''''
    as insert_string

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

    #3735648

  • Reply To: Restore

    Every SQL database restore must start with a full backup restore.

    After that, you can, if you want to, restore a differential backup (that was taken after the full backup you...

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

    #3735135

  • Reply To: SELECT from COLUMN if exists

    Use this to generate the SELECT statements to review.  After you've verified the statements, run them if / as you see fit.

    DECLARE @DTCHANGEDON_found bit
    DECLARE @DTCREATEDON_found bit
    DECLARE @end_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 17, 2020 at 7:24 pm

    #3735128

  • Reply To: Get a couple fruits

    You really should start using the new syntax, with parentheses around the row count:

    SELECT TOP (2) *

    FROM STRING_SPLIT(@s, ',') AS ss

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

    #3735037

  • Reply To: DBCC SHRINKFILE EMPTYFILE Running REALLY Slow

    Make sure you pre-allocate enough log space to handle any logging required.  If the log file has to grow dynamically, that will be very slow.

    Make sure the new data files...

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

    #3734866

  • Reply To: WITH Query definition not working

    Maybe a permissions issue?  I don't see why drives wouldn't show up for that query.

    That code is extremely inefficient though, since it pulls drive info once for every file, instead...

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

    #3734863

  • Reply To: TDE related questions

    Ultimately any part of a disk drive file can be corrupted (or attacked?).  A bad controller, etc., can cause unexpected corruption.

    Again, it's my understanding that there is no way 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".

    March 16, 2020 at 7:39 pm

    #3734839

  • Reply To: Could you list db modeling tools you use on daily basis?

    It's very difficult for many people to split the logical and physical design.  But, it's critical to a good design that you keep the two separate.  Getting a developer involved...

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

    #3734838

    • Viewing 15 posts - 2,431 through 2,445 (of 7,619 total)