none
Issues of getting indexes information - SQL Server

    Question


  • Trying to get table indexes information in SQL Server 2012 I identified a strange situation for one scenarion.
    I have a table that contains two indexes referenced to some fields: Field_1 and Field_3 mapped over int, null columns (the number means the existing field order into the table designed few years ago...).

    I am trying to get information about these indexes like this:

     nRetCode = ::SQLStatistics(hstmtAux, NULL, 0, NULL, 0, (TCHAR*)(LPCTSTR)strTempTable, SQL_NTS, SQL_INDEX_ALL, SQL_ENSURE);
        if (nRetCode == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO)
        {
            nRetCode = ::SQLBindCol(hstmtAux, 4, SQL_C_SHORT, &swNonUnique, sizeof(SWORD), &cbNonUnique);
            nRetCode = ::SQLBindCol(hstmtAux, 5, SQL_CHAR,    szIdxQualif, sizeof(CHAR) * 130, &cbIdxQualif);
            nRetCode = ::SQLBindCol(hstmtAux, 6, SQL_C_CHAR,  szIdxName, sizeof(CHAR) * 130, &cbIdxName);          
            nRetCode = ::SQLBindCol(hstmtAux, 7, SQL_C_SHORT, &swType, sizeof(SWORD), &cbType);
            nRetCode = ::SQLBindCol(hstmtAux, 8, SQL_C_SHORT, &swSeqInIdx, sizeof(SWORD), &cbSeqInIdx);
            nRetCode = ::SQLBindCol(hstmtAux, 9, SQL_C_CHAR,  szIdxColName, sizeof(CHAR) * 130, &cbIdxColName);
    
            while (bNoFetch || (nRetCode = ::SQLExtendedFetch(hstmtAux, SQL_FETCH_NEXT, 1, &crow, &rgfRowStatus)) == SQL_SUCCESS || nRetCode == SQL_SUCCESS_WITH_INFO)
            {
               if (cbIdxName != SQL_NULL_DATA && _tcslen((TCHAR)szIdxName) > 0) 
               {
                   // rest of the code
               }
             // the rest of the code

    Becasuse SQLExtendedFetch() is deprecated I used SQLFetchScroll() but the behavior is the same from my interest point of view.

    Usally, I get the right information about indexes but in one situation I encounte a strange behavior. It's about having a clustered index into a scenario.

    When Field_1 is Non-Unique, Non-Clustered and Field_3 is Clusted index I get the right information.
    But if the index Field_1 is Clustered and the Field_3 is Non-Unique, Non-Clustered I get no information about Field_1 index (eg. szIdxName and szIdxColName are "" and their length is -1 that means SQL_NULL_DATA). 
    So, I have no Index information. Within while loop, with the next iteration I get correct information about the second index Field_3.

    I'm not sure whether the problem is with SQLStatistics, the bindings or SQLFetchScroll (they all always return SQL_SUCCESS). It looks like a problem with the driver when the first index is clustered.

    According to SQLStatistics documentation if my swType parameter is SQL_TABLE_STAT I have no information for index or column. For this scenarios I have no indexes of combined columns. 
    For the good scenario I observed that my while loop had 3 iterations including one of having swType = SQL_TABLE_STAT without information in szIdxName. But for the bad scenario the loop had only 2 iteration.  So it looks like SQLExtendedFetch() is not stepping for one index.

    Any ideas for fixing this problem or alternative ways for retrieving indexes information?

    Monday, January 06, 2014 3:21 PM

Answers

All replies