Forum Replies Created

Viewing 15 posts - 2,761 through 2,775 (of 7,613 total)

  • Reply To: Left join not pulling all from left table

    For an OUTER JOIN, you must put conditions on the possibly-missing table in the JOIN clause, not in the WHERE clause.

    select * from #temp1 T

    left join #temp2...

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

    July 23, 2019 at 2:54 pm

    #3665407

  • Reply To: Joining two tables using wild cards to populate a third

    I don't know about the overall logic, but the JOINs above seem needlessly convoluted.  Instead, maybe:

      INNER JOIN #UserB AS B
        ON ...

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

    July 22, 2019 at 6:53 pm

    #3665188

  • Reply To: In a t-sql 2012, I can trying to make a case statement work in an update statem

    I think the Milestone table does not contain a column named "TOT_ABSENCES".  Please review the column names in the Milestone 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".

    July 15, 2019 at 5:14 pm

    #3663117

  • Reply To: delete last 9 h

    Create an index keyed on pbRecordId on that 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".

    July 12, 2019 at 8:55 pm

    #3662510

  • Reply To: Index Size Question

    The clustered index is the table itself.  That is, all columns are stored in the clustered index.  Thus, the width is the total width of the entire row.

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

    July 8, 2019 at 6:33 pm

    #3660895

  • Reply To: Create tables from metadatatable

    The system itself already has all the metadata views you need.  It's best to just use those views: sys.objects, sys.columns, sys.key_constraints, etc..

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

    July 8, 2019 at 4:51 pm

    #3660838

  • Reply To: How to compress indices without rebuilding them

    I too would not drop compression to do a load, especially a large one.  Page compression can help data load much faster by reducing I/O (compression takes more CPU but...

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

    July 5, 2019 at 7:05 pm

    #3660302

  • Reply To: Seeing a lot of HEAPS tables with large row counts on prod. Is this a problem ?

    If these are staging tables, just be sure to TRUNCATE the table rather than DELETE the rows.  TRUNCATE will keep the table allocations clean without having to create a clustering...

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

    July 5, 2019 at 7:00 pm

    #3660295

  • Reply To: T-SQl or Store Procedure which fit better

    I prefer to use CROSS APPLY(s) for such calcs.

    Select emp_code, emp_name,emp_last_name, emp_max_pay, emp_yearly_salary
    From dbo.table_name
    Cross Apply (
        Select case when emp_max_pay < 10000 then emp_max_pay...

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

    July 5, 2019 at 6:58 pm

    #3660294

  • Reply To: Query improvement

    I agree with Jonathan: you've already got the exact index needed to support that query.

    What specific version and edition of SQL Server are you on?

    In particular, does the specific version...

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

    July 5, 2019 at 4:58 pm

    #3660261

  • Reply To: add prefix to numeric values 

    UPDATE dbo.table_name
    SET TPN = RIGHT(REPLICATE('0', 8) + CAST(TPN AS varchar(9)), 9)
    WHERE TPN NOT LIKE '%[^0-9]%'

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

    July 3, 2019 at 1:19 pm

    #3659753

  • Reply To: Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.

    That codes parses fine for me on SQL 2016.

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

    July 3, 2019 at 1:12 pm

    #3659750

  • Reply To: IF/THEN within a view 

    , CAST(p.PeopleID AS varchar(10)) + CASE WHEN p.instructorInt = -1 THEN '-1' ELSE '' END AS OriginalDatabaseId

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

    July 2, 2019 at 8:51 pm

    #3659523

  • Reply To: simple query question, using ROLLUP instead of COMPUTE

    Try this:

    select 
    cast(SystemUser as varchar(25)) as "User",
    LogonTime as "Date",
    cast(HOST_NAME as varchar(25)) as "Workstation",
    SPID as "session",
    cast(APP_NAME as varchar(55)) as "Program",
    count(SPID) over () AS SystemUserCount /*add this line*/
    from ServerLogonHistory
    where...

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

    July 2, 2019 at 4:16 pm

    #3659391

  • Reply To: index usage help

    Yes, it would also prevent Oracle doing a seek on an index.

    If lastUpdatedDate is a date or datetime, just compare it directly to a variable of the matching type, or...

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

    July 2, 2019 at 1:07 pm

    #3659270

Viewing 15 posts - 2,761 through 2,775 (of 7,613 total)