Forum Replies Created

Viewing 15 posts - 1,846 through 1,860 (of 7,613 total)

  • Reply To: Newb - SQL Server IIF() Function

    CASE, ISNULL and COALESCE are all an integral part of SQL Server and follow its general syntax.  It's because IIF is so unlike other other functions and expressions in SQL...

    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 1, 2021 at 6:30 pm

    #3851845

  • Reply To: Question about deadlock

    Does the table have an index with column1 in it, or preferably where column1 is the first column in an index?  If no, SQL would have to scan the table...

    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 1, 2021 at 6:17 pm

    #3851840

  • Reply To: poor query performance by joining large table and new CE

    If the main table is really that narrow (few bytes), then compress the non-clus index to reduce the pages more:

    CREATE UNIQUE NONCLUSTERED INDEX IX1_IattribINT ON dbo.AttribINT (...

    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 1, 2021 at 4:07 pm

    #3851787

  • Reply To: poor query performance by joining large table and new CE

    I'd suggest first try creating a non-clustered index on ( OID, AID ).  Cluster the temp table on ( OID /*and AID if available*/ ).

    CREATE UNIQUE NONCLUSTERED...

    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 1, 2021 at 3:07 pm

    #3851759

  • Reply To: poor query performance by joining large table and new CE

    Need to see the DDL for AttribINT, including all index definitions.

    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 1, 2021 at 2:36 pm

    #3851743

  • Reply To: Newb - SQL Server IIF() Function

    Kristen-173977 wrote:

    I dislike IIF ... its "familiar" for people that use similar functions in Excel, but I think CASE is better in SQL

    IIF only has two outcomes, true/false, and 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".

    February 28, 2021 at 3:38 am

    #3851200

  • Reply To: Remove Carriage Returns / Line Breaks / Any other reason the "text" moves down

    Yeah, that code should do it.

    But if you're using the column name "ClientNotes" directly in a SELECT you will get the original column value, not the one after the REPLACEs. ...

    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 28, 2021 at 3:19 am

    #3851189

  • Reply To: Better query to get count based on column value

    Most typically:

    select name,count(*) as name_count
    from dbo.table_name
    where name like '%ra[jm]%' and P = 1

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

    #3851089

  • Reply To: Newb - SQL Server IIF() Function

     

    IIF([var1Title] != '', [var1Title] + ISNULL(' - ' + [var1Topic], '') + ISNULL(' - ' + [var1Name], ''), '') AS Item1

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

    #3850640

  • Reply To: Preventing concurrent inserts

    I'd avoid a guid if at all possible.

    Make sure you have an index keyed first on c1, so that determining the max(c1) is a single, quick seek.  An INSERT 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".

    February 26, 2021 at 4:25 pm

    #3850638

  • Reply To: Validate phone numbers in SQL

     

    SELECT 
        phone_string,
        phone,
        --LEN(phone) AS phone_len,
        CASE WHEN 
        CASE WHEN...

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

    #3850637

  • Reply To: Query Optimizer Suggests Wrong Index and Query Plan -- Why?

    I wouldn't make the Status a key column, since it's likely to change so often, at least in the earlier life stage of the row.  The rows are small anyway,...

    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, 2021 at 8:17 am

    #3850463

  • Reply To: Please help to get duration between two 4 or 3 character string

    Jeff Moden wrote:

    Johan Bijnens wrote:

    I do not consider "2359 to 2359" a valid case, as that might imply it may also span multiple days

    Ordinarily, I'd agree but to consider the span 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".

    February 26, 2021 at 7:58 am

    #3850447

  • Reply To: Multilevel BOM with Inventory

    But does the Adjacency model actual satisfy BOM requirements?

    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, 2021 at 7:51 am

    #3850445

  • Reply To: trigger email alerts in case file is delivered or failed for the job

    Are you using SQL mail (sp_send_dbmail)?  If so, you can use the mail status table in msdb to check on the status of any email.  Look at the "sysmail_%" views.

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

    #3850206

Viewing 15 posts - 1,846 through 1,860 (of 7,613 total)