Forum Replies Created

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

  • Reply To: Opaque Encryption - with No Views, or Special SQL

    You would do that with view(s).  The ENCRYPT / DECRYPT would be only within the view(s).

    The users don't have to query anything different, as you can make the original 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".

    January 25, 2021 at 7:44 pm

    #3837332

  • Reply To: Looking for tips on Optimizing

    You're welcome!  I'm glad it helped.  Interesting q to play with.

    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 25, 2021 at 7:38 pm

    #3837329

  • Reply To: Looking for tips on Optimizing

    Maybe something like this?:


    SELECT Item, Date1, Date2, Date3
    FROM @headerTable
    UNION ALL
    SELECT
        Item,
        CAST(MAX(CASE WHEN week# = 1 THEN Quantity END) AS varchar(10)),
    ...

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

    #3836401

  • Reply To: Looking for tips on Optimizing

    Yeah, this can be definitely done in a standard SELECT.

    Do you want to show only the weeks that had a match or do you want to show all 52 weeks,...

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

    #3836382

  • Reply To: Lookup and insert into column without dynamic SQL?

    I'm confused about a couple of things.

    First, why do you only want to INSERT "field3" (really column3)?

    There's no link or association to #FieldValues.  Surely there must be some columns missing...

    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:56 pm

    #3836005

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

    SQL provide missing index info that you can use to help determine whether you need other index(es) or not, or even whether you need to change the clustered index 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".

    January 21, 2021 at 7:38 pm

    #3835998

  • 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

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