Forum Replies Created

Viewing 15 posts - 47,521 through 47,535 (of 49,552 total)

  • RE: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    Miranda Johnson (1/30/2008)

    we use dbo.sp_Whatever when we execute the sprocs. I was told this will make it not look at the master table is that...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 1:09 pm

    #773497

  • RE: confused on a select into..

    Pretty much, assuming the table tower.nbdoc exists.

    You just need to specify the column names for the insert and in the select. eg

    INSERT INTO ATable (Col1, col2, col3, col4)

    SELECT Value1, value2,...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 1:07 pm

    #773496

  • RE: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    gova (1/30/2008)

    You beat me on that Gilamonster when I was typing the sample code.

    🙂 I'm feeling a bit lazy tonight, so very simple sample code

    BTW.

    order by case when @sortorder...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 1:05 pm

    #773494

  • RE: Trigger causing performance issues

    Yes and no.

    I prefer not to force row locks unless I know that only a very small number of rows are going to be updated. Preferably tune the trigger so...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 12:39 pm

    #773477

  • RE: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    Then you can try something like this.

    select col1, col2 from

    (Select distinct col1, col2, col3 from sometable) subquery

    order by case when @sortorder = 1 then col1 else col3 end...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 12:29 pm

    #773468

  • RE: Trigger causing performance issues

    aruram (1/30/2008)

    I am the DBA working with the application team on this, but from my understanding .. the intent is to update the status on a bunch of test results...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 12:24 pm

    #773464

  • RE: Trigger causing performance issues

    1st suggestion - rewrite the trigger and remove the cursor.

    2nd suggestion - same as the first. 😉

    What's the idea behind the trigger? What are you trying to do when the...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 11:57 am

    #773454

  • RE: Microsoft: "Don´t use the prefix sp_"

    Matt Miller (1/30/2008)

    Secondary comment - which gets kind of interesting as to the scoping issue.

    If the procedure in MASTER is marked as a system stored proc, then it overrides the...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 11:48 am

    #773449

  • RE: Like Operator.

    DonaldW (1/30/2008)

    Significantly slower than you saw in your test.

    Probably because you cleared the cache. I tend to prime the cache before running tests (run the query once so that I...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 11:41 am

    #773445

  • RE: transactional log

    Also note that truncating a log (removing inactive log entries) and shrinking the log (reducing the size on disk) are two separate things.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 11:37 am

    #773443

  • RE: Microsoft: "Don´t use the prefix sp_"

    goran (1/30/2008)

    WTF, this is like kindergarten!

    Gail, when you have found one (1!) application in the whole universe that uses the same name on a system stored procedure as on...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 11:36 am

    #773440

  • RE: Please help

    Please don't cross post. It wastes people's time.

    Please direct replies to the following thread:

    Please help (SQL2K5)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 11:30 am

    #773438

  • RE: database read - Time out

    Could be indexes, could be the queries, could be blocking. Without more information it's very hard to say.

    Can you post the query that times out and the schema and indexes...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 11:23 am

    #773435

  • RE: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    Are those distincts necessary?

    I believe that when distinct is specified, the order by (in your case, the case statement) must appear in the select clause.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 30, 2008 at 11:20 am

    #773433

  • RE: Regarding Lock

    You can look in the dmv sys.dm_tran_locks

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

    January 29, 2008 at 11:58 pm

    #773136

Viewing 15 posts - 47,521 through 47,535 (of 49,552 total)