Forum Replies Created

Viewing 15 posts - 9,796 through 9,810 (of 49,552 total)

  • RE: INCLUDE option in index

    Evil Kraig F (3/5/2014)

    GilaMonster (3/5/2014)

    I would create, as an index for that query:

    create index IX_A_Col1 on A ( Col1, IndexID )

    Having it as a second key column is more useful...

    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

    March 5, 2014 at 1:31 pm

    #1694479

  • RE: INCLUDE option in index

    Evil Kraig F (3/5/2014)

    No, the clustered index is already included in all nonclustered indexes. The include would be superfluous unless you're trying to protect against someone changing the clustered...

    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

    March 5, 2014 at 1:28 pm

    #1694476

  • RE: INCLUDE option in index

    I would create, as an index for that query:

    create index IX_A_Col1 on A ( Col1, IndexID )

    Having it as a second key column is more useful than having it as...

    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

    March 5, 2014 at 1:23 pm

    #1694473

  • RE: INCLUDE option in index

    N_Muller (3/5/2014)

    Would there be any benefit to include columns that are part of the primary key on a non-clustered index, even if I don't intend to add it to 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

    March 5, 2014 at 1:02 pm

    #1694466

  • RE: Get Object Name from database_id, file_id, Page_id

    SQLRNNR (3/5/2014)

    GilaMonster (3/5/2014)

    Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.

    SELECT *

    FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa

    ...

    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

    March 5, 2014 at 12:10 pm

    #1694452

  • RE: 1 Simple Question

    iso is a CD/DVD image. Either burn it to a DVD and use that or get a program which can mount iso files as virtual drives.

    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

    March 5, 2014 at 12:09 pm

    #1694451

  • RE: SPROC's calling SPROC's and the Plan Cache...

    WaIIy (3/5/2014)

    Now, does that in itself effect the plan cache(ability) at all of the SPROC?

    No

    Force any kind of recompile each time etc.?

    No

    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

    March 5, 2014 at 12:06 pm

    #1694450

  • RE: Get Object Name from database_id, file_id, Page_id

    sqlbuddy123 (3/5/2014)

    Check this one ..

    SELECT *, OBJ.name AS Object_Name

    FROM sys.dm_os_buffer_descriptors AS bd

    INNER JOIN

    (

    ...

    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

    March 5, 2014 at 11:48 am

    #1694444

  • RE: Get Object Name from database_id, file_id, Page_id

    Potentially. I haven't tried this because I don't have a 2012 instance available. It's undocumented.

    SELECT *

    FROM sys.dm_db_database_page_allocations(DB_ID('<Database Name>'), NULL, NULL, NULL, 'DETAILED') dpa

    WHERE allocated_page_file_id...

    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

    March 5, 2014 at 11:39 am

    #1694440

  • RE: Windowed Function in Where Clause

    :hehe:

    btw, these are valid

    with cte as (

    select Person

    , row_number() over (order by Person) AS OrderingKey

    ...

    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

    March 5, 2014 at 8:49 am

    #1694351

  • RE: Windowed Function in Where Clause

    You can't use a windowing function in a where clause. The examples you show work because they're not using a windowing function in a where clause, they're using it in...

    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

    March 5, 2014 at 8:09 am

    #1694319

  • RE: Query of 100 mil rows with multiple parameters

    ChrisM@Work (3/5/2014)

    I think most folks do now know that the cluster keys behave similarly to include columns in nc indexes

    Key columns, unless the nonclustered index is defined UNIQUE. Not...

    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

    March 5, 2014 at 7:07 am

    #1694283

  • RE: Query of 100 mil rows with multiple parameters

    ChrisM@Work (3/5/2014)

    If the person looking at your query is a bogstandard TSQL developer, what would you expect them to know about index internals?

    Nothing. I have to assume that because I'm...

    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

    March 5, 2014 at 7:01 am

    #1694280

  • RE: Query of 100 mil rows with multiple parameters

    ChrisM@Work (3/5/2014)

    GilaMonster (3/5/2014)

    ChrisM@Work (3/5/2014)

    If I script out the nc index containing the cluster key as an include column from the code I posted above, the cluster key column still...

    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

    March 5, 2014 at 6:38 am

    #1694270

  • RE: null query

    If you want to count the number of rows where the age is null, you probably want this:

    select count(*) from dbo.prim

    where age is null

    Count(*) means count the number of rows....

    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

    March 5, 2014 at 6:27 am

    #1694261

Viewing 15 posts - 9,796 through 9,810 (of 49,552 total)