Forum Replies Created

Viewing 15 posts - 1,771 through 1,785 (of 7,613 total)

  • Reply To: CASE Statement with Dates in WHERE clause 

    SELECT DISTINCT 
        BatchID, EDIProviderSort,
        CONVERT(varchar(10), BatchDateConv, 101) AS BatchDatte,
        DName

    FROM UPSBatchInvNo

    CROSS APPLY (
        SELECT...

    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 22, 2021 at 6:01 pm

    #3860910

  • Reply To: Database list and the last access date

    SQL Server doesn't store, and thus doesn't know, the "last accessed date" for each db.

    After SQL starts, when any index is accessed, an entry for that db and index is...

    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 22, 2021 at 4:42 pm

    #3860892

  • Reply To: Space Error

    The first two are not full but don't have room to expand by the specified amount.

    Alter the first two files to have a maxsize of their current size.  That 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".

    March 22, 2021 at 2:51 pm

    #3860840

  • Reply To: Substract 2 values

    Steve Collins wrote:

    with testCostTable([Period], [Category], [Value]) as (

    Also, the column names used are all SQL Server reserved words.  That's something many SQL developers find annoying :).  In my...

    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 22, 2021 at 2:46 pm

    #3860839

  • Reply To: Statistics Update Frequency

    Jeff Moden wrote:

    Jackie Lowery wrote:

    What's the best way to know which index statistics need to be updated on a daily basis?  I currently update stats on Friday, but I've noticed SQL is...

    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 22, 2021 at 2:39 pm

    #3860837

  • Reply To: Statistics Update Frequency

    That seems about right.  I'd say also consider doing tables every day that get a manual update in 2 days' time; they're close enough to once-a-day anyway to me.  "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".

    March 22, 2021 at 2:36 pm

    #3860835

  • Reply To: Counting Instances in TEXT

     

    SELECT ca.name, ca.nentry
    FROM (
        SELECT
            SUM(CASE WHEN '.' + contents + '.' LIKE '%[^A-Za-z]bull[^A-Za-z]%' THEN 1 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".

    March 22, 2021 at 2:24 pm

    #3860826

  • Reply To: Is this a RBAR?

    The Windowing functions tend to be extraordinarily efficient, so I'd do the request this way:

    SELECT DISTINCT f.[Id] AS [FormId], ver.[FormVersion]
    FROM @forms f
    LEFT OUTER JOIN (
      ...

    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 22, 2021 at 2:17 pm

    #3860825

  • Reply To: pagination with case

    In a CASE <condition> THEN <result>, the <result> must be a single value (technically called a "scalar" value).  Not allowed are SQL keywords or operators (>=, <) as part 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".

    March 19, 2021 at 2:08 pm

    #3859949

  • Reply To: Statistics Update Frequency

    "Tell" SQL to always use a full table scan for those indexes that need it.  Do that by running this one time at your convenience:

    -- index_name is optional, of course

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

    March 19, 2021 at 12:36 am

    #3859728

  • Reply To: How to stop a user creating a new table

    If necessary, you could even add a DDL trigger as a failsafe to make absolutely sure that user doesn't create (or drop) any tables in that db.

    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 18, 2021 at 7:05 pm

    #3859618

  • Reply To: How to stop a user creating a new table

    Grant the user the db_datareader and db_datawriter roles, etc., using the commands below.  You can ignore any errors except those dealing with the user's name:

    ALTER ROLE db_datareader ADD MEMBER [your_user_name_here];

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

    March 18, 2021 at 7:01 pm

    #3859617

  • Reply To: A couple of questions on backward compatibility in SQL Server

    1. No.  You simply cannot restore a backup created in a later version of SQL to an earlier version.  You'd have to use some other method of moving the db...

    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 18, 2021 at 1:57 pm

    #3859511

  • Reply To: Help filtering my WHERE clause

    The Dixie Flatline wrote:

    Summing case statements instead of doing a PIVOT, is referred to as a cross tab.

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%e2%80%93-converting-rows-to-columns-1

    Exactly.  I used a CASE in a SUM but not instead of a PIVOT, so...

    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 18, 2021 at 1:47 pm

    #3859509

  • Reply To: Need to SELECT Columns in Table A where one column closely matches in Table B

    You're likely wasting resources by using a CROSS JOIN.  Just do a standard INNER JOIN using the LIKE comparison as the join condition.

    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, 2021 at 10:29 pm

    #3859174

Viewing 15 posts - 1,771 through 1,785 (of 7,613 total)