Forum Replies Created

Viewing 15 posts - 1,951 through 1,965 (of 7,613 total)

  • Reply To: update using self-join - confirm it looks at old values

    OK, technically, other queries in the same transaction that did the UPDATE could see it before committing it, in something like this:

    BEGIN TRANS

    UPDATE

    SELECT

    COMMIT TRANS

    But not within the context of a...

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

    January 21, 2021 at 7:34 pm

    #3835997

  • Reply To: What hasn't changed?

    Actually you'd get an error, since alias "s." is not defined.

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

    January 21, 2021 at 3:59 pm

    #3835920

  • Reply To: update using self-join - confirm it looks at old values

    The old value.  The new value can't be seen by a transaction modifying that table until the transaction's been committed.

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

    January 21, 2021 at 3:53 pm

    #3835917

  • Reply To: Taking inner subset result of one join and combining this with an outer join

    You really need to provide directly usable table structures and data.  That is:

    CREATE TABLE and INSERT statement(s)

    CREATE TABLE #df1 /*or just df1*/

    ( version varchar(10) NOT NULL, host ... )

    INSERT INTO...

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

    January 20, 2021 at 10:42 pm

    #3835671

  • Reply To: Is the order of the columns in the NC index important

    SSMS is not great about how best to define indexes.

    Would you be able to post the query and the DDL for the table, including the indexes?

    And please confirm, are 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".

    January 20, 2021 at 7:03 pm

    #3835619

  • Reply To: Group By and JOIN within SQL Server (when dealing with multiple columns)

    Maybe as below.  No directly usable data so I can't test it.  This code takes advantage of the fact that df1.host is known to be equal to df2.pc.

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

    January 20, 2021 at 2:39 am

    #3835308

  • Reply To: Interview question about CPU

    I don't think there is a single "best" CPU%.  But I would be OK with generally 80-90%, as long as there weren't periods where it spiked above 95% and stayed...

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

    January 17, 2021 at 4:32 am

    #3834251

  • Reply To: Help on turning query

    huishi.ca wrote:

    thanks. It was vendor supported application. We are not allowed to change any query though

    I'm confused then.  When you originally asked:

    Any suggestion to tune this query?

    Did you expect we...

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

    January 16, 2021 at 6:00 am

    #3834041

  • Reply To: alternative to temp tables with 3M+ records and growing

    You really should not use a temp table for this.  It's a lot of overhead copying rows to the temp table, esp. if you then separately build an index afterward. ...

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

    January 16, 2021 at 5:55 am

    #3834040

  • Reply To: alternative to temp tables with 3M+ records and growing

    It shouldn't be clustered, but you do need an index, as I noted above, on:

    ( user_id, allowed_member_id )

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

    January 15, 2021 at 6:26 pm

    #3833887

  • Reply To: alternative to temp tables with 3M+ records and growing

     If the user has a restriction on the members he can view, we're inserting into a temp table only the members he is allowed to see;

    Isn't the list of allowed...

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

    January 15, 2021 at 3:07 pm

    #3833817

  • Reply To: CONTAINS any of multiple values 

    contains (PRODUCTS, 'Product1 OR Product2 OR Product3 OR Product4 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".

    January 15, 2021 at 9:08 am

    #3833690

  • Reply To: SQL Permissions

    frederico_fonseca wrote:

    kind of you can.

    https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-server-permissions-transact-sql?view=sql-server-ver15

    • View any database
    • connect any database
    • view any definition
    • select all user securables

    Nice.  I was familiar with the others but not with "select all user...

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

    January 14, 2021 at 5:41 pm

    #3833449

  • Reply To: Table Elimination In a View

    You specified an INNER JOIN.  That means SQL must check to see if a matching row(s) exist in the other table because, if not, SQL can't return the row.  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".

    January 14, 2021 at 5:38 pm

    #3833448

  • Reply To: SQL Permissions

    Your understanding is correct, I think.  As Grant noted, since tables, views, etc., exist only within a database, and not at the instance level, you can't grant permissions to them...

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

    January 13, 2021 at 11:30 pm

    #3832194

    • Viewing 15 posts - 1,951 through 1,965 (of 7,613 total)