SSMS – Linked Server – Column Metadata

Background

Had a good meeting this morning while we try to figure out how to better support our Developers.

One of the issues they brought up was an inability to view the datatype of linked Server tables.

Pictorial

Here is the deepest granularity when we connect to our Linked Server.

Image

ssms.columns.20190114.0425PM.PNG

Explanation

  1. We see the following
    • Server
    • Databases
    • Objects
      • Tables
      • Views

View Columns

Script

Outline

Here are avenues we can explore to view column metadata :-

  1. sp_columns_ex
  2. Openquery/sys
    • sys.all_columns
  3. Openquery/informational_schema
    • informational_schema.columns

Choices

exp_columns_ex

Syntax

exec sp_columns_ex
		  @table_server
		, @table_catalog
		, @table_schema
		, @table_name   

Sample

declare @linkedServer   sysname
declare @linkedDatabase sysname
declare @linkedSchema   sysname
declare @linkedTable    sysname

set @linkedServer= 'AWS-JobBuilder'
set @linkedDatabase = 'acs_ap'
set @linkedSchema = 'dbo'
set @linkedTable = 'ap_user'

exec sp_columns_ex
		  @table_server  = @linkedServer
		, @table_catalog = @linkedDatabase
		, @table_schema  = @linkedSchema
		, @table_name    = @linkedTable

Output

sp_tables_ex.2019014.0443pm

openquery/sys.*

Syntax

select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                    select
                              [server] = serverproperty(''servername'')
                            , [database] = db_name()
                            , [schema] = tblSS.name
                            , [object] = tblSAO.name
                            , [column] = tblSAC.name
                            , [type]   = tblST.[name]
                            , tblST.max_length
                            , tblST.is_nullable

                    from   sys.schemas tblSS

                    inner join sys.all_objects tblSAO

                            on tblSS.schema_id = tblSAO.schema_id

                    inner join sys.all_columns tblSAC

                        on tblSAO.object_id = tblSAC.object_id

                    inner join sys.types tblST

                        on  tblSAC.system_type_id = tblST.system_type_id
                        and tblSAC.user_type_id = tblST.user_type_id

              '
        )

Output

openquery.sys.all.2019014.0452pm

openquery/information_schema.columns

Syntax
select top 10 *

from   openquery
        (
              [AWS-JobBuilder]

            , '
                select top 100 

                          [server] = serverproperty(''servername'')

                        , [database] = tblSIC.[TABLE_CATALOG]

                        , [schema] = tblSIC.[TABLE_SCHEMA]

                        , [object] = tblSIC.[TABLE_NAME]

                        , [column] = tblSIC.[COLUMN_NAME]

                        , [position] = tblSIC.[ORDINAL_POSITION]

                        , [dataType] = tblSIC.[DATA_TYPE]

                        , [charMaxLength] = tblSIC.[CHARACTER_MAXIMUM_LENGTH]

                        , [charOctetLength] = tblSIC.[CHARACTER_OCTET_LENGTH]

                from   information_schema.columns tblSIC

              '
        )			   	

Output

openquery.openquery.informational_columns.2019014.0515PM.PNG

Summary

Unfortunately, SQL Server Management Studio ( SSMS ) v17.x does not let us view columns on Linked Servers.

To gather column level metadata, one has to write code.

DBeaver – Table Relationships

Background

Just wanting to make sure that I am properly tracking table relationships in MySQL.

 

Code

INFORMATION_SCHEMA

INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Code

Sample


set @schema := 'sakila';
set @table := 'inventory';

select 

         case

            when tblKCU.TABLE_NAME = @table then 'References'
            when REFERENCED_TABLE_NAME = @table then 'Referenced'

         end as 'isReferencingOrReferenced'
       , tblKCU.CONSTRAINT_NAME as 'constraint'
       , tblKCU.TABLE_NAME as 'table'
       , tblKCU.COLUMN_NAME as 'column'

       , tblKCU.REFERENCED_TABLE_NAME as 'refTable'
       , tblKCU.REFERENCED_COLUMN_NAME as 'refColumn'

       , tblKCU.ORDINAL_POSITION as 'colPos'

from   information_schema.KEY_COLUMN_USAGE tblKCU

where  tblKCU.TABLE_SCHEMA = @schema

/* Foreign Key Relationship */
and    (

             ( tblKCU.TABLE_NAME = @table )
          or ( tblKCU.REFERENCED_TABLE_NAME = @table )

       )

/* Skip Primary Key */
and    (

              ( tblKCU.TABLE_NAME is not null )
          and ( tblKCU.REFERENCED_TABLE_NAME is not null )

       )

order by       

         tblKCU.TABLE_NAME
       , tblKCU.COLUMN_NAME

       , tblKCU.REFERENCED_TABLE_NAME
       , tblKCU.REFERENCED_COLUMN_NAME

       , tblKCU.CONSTRAINT_NAME       

       , tblKCU.ORDINAL_POSITION

;

Output

 

Tools

DBeaver

Outline

Steps to follow to track visually through DBeaver

  1. Launch DBeaver
  2. Connect to MySQL Instance
  3. Navigate to Database Tables <Specific Table>
  4. Double Click on the selected table
  5. On the right panel, choose the “ER Diagram” Tab
  6. Review the shown tables and their relationships to each other

Image

MySQL – information_schema.statistics

Background

As a quick follow-up to our discussion of metadata on tables, let us discuss indexes.

BTW, the discussion on tables is here.

INFORMATION_SCHEMA.STATISTICS

Code


select

		tblStat.TABLE_NAME as 'table'

	  , tblStat.INDEX_NAME as 'index'

	  , case tblStat.NON_UNIQUE
			   when 0 then 'No'
			   when 1 then 'Yes'
			   else '?'
		end as 'Unique'


	  , GROUP_CONCAT( COLUMN_NAME order by SEQ_IN_INDEX ) as colList


	  , tblStat.CARDINALITY


from  INFORMATION_SCHEMA.STATISTICS tblStat


where  tblStat.TABLE_SCHEMA = 'drupal'


group by

		 tblStat.TABLE_SCHEMA
	   , tblStat.TABLE_NAME
	   , tblStat.INDEX_NAME


order by

		 tblStat.TABLE_SCHEMA
	  ,  tblStat.TABLE_NAME
	  ,  tblStat.INDEX_NAME


LIMIT 25

;


Output

 

Comparison with Other Databases

  1. GROUP_CONCAT
    • The group_concat is very, very powerful and has few equal in other database platforms
    • Function Documentation

 

SHOW INDEX

To get a bit more information, specifically cardinality ( Number of unique values in each column combination ), we can issue “show index” against the table.

Code

Syntax


show index from [table] from [database];

Sample


show index from search_index from drupal;

Output

References

  1. Reference
    • INFORMATION_SCHEMA Tables
      • INFORMATION_SCHEMA STATISTICS Table
    • Show Index
    •  Group_Function
      • Group By Function
        Link
    • Group_Concat

DBeaver

Background

Here I am having used SQL Server Management Studio ( SSMS ) for so long, I am satisfied.

It’s GUI is versely superior to SQL 2000 Query Analyzer and it exposes just about every SQL Functionality that I use on daily basis.

 

Stumped

Spent hours trying to capture a query’s grid display unto Excel, but Excel column size was getting in the way.

And, so googled for ways to display columns as rows.

 

DBeaver

Googled and found DBeaver.

The nice thing about it is that I can export queries result set into XML or HTML.

 

Download

DBeaver is available here.

 

Export Query

Screen Shot

Data Transfer Target Type and Format

Image

Data Transfer – Extraction Settings

Image

Data Transfer – Settings – See export settings

Image

 

Data Transfer – Output – Configure export output parameters

Image

Data Transfer – Confirm – Check results

Image

 

Sample Files

HTML

XML

 

Other Functionalities

Database Diagram

Database Modeling is also easy and nice.

 

Table Structure

Properties

References

DDL

 

Data

 

Database Supported

DBeaver supports both SQL and NoSQL Databases.

For SQL databases, it relies on JDBC.

Driver Manager

MySQL :- Secondary Indexes and the Clustering Keys – Day 2

Preface

In our last post, we spoke about how one might not need to add the Clustering columns when defining a Secondary Index.

At that point, we touched on the fact that just reviewing the Query Plan via “Visual Explain” might not fully reveal whether the Index used is “covering” or whether the Clustered Index is also accessed.

 

Metadata

Indexes

Index – Key name & Columns

List the index names and the corresponding columns

Code


SHOW INDEX FROM dblab.errorLog;

Output

ShowIndex-20160620-1114AM

Explanation

  1. Clustered Index
    • PRIMARY
      • Column Names :- id
      • Cardinality :- 111209
  2. Non Clustered Indexes
    • INDX_DBA_RECORDCREATED
      • Column Names :- record_created
      • Cardinality :- 184

 

Index – Get Index Size

Get Index Size

Code


set @database := 'dblab';
set @table := 'errorlog';
set @convertToMB := 1024 * 1000;

select 
           database_name
         , table_name
         , index_name
         , stat_name
         , @@innodb_page_size as innodb_page_sizeInBytes
         , stat_value*@@innodb_page_size as IndexSizeInBytes
         , (stat_value*@@innodb_page_size) / (@convertToMB) as IndexSizeMB

from mysql.innodb_index_stats 

where stat_name = 'size'

and   database_name = @database

and   table_name = @table

;

Output

IndexStats-20160620-0111PM

Explanation

  1. Clustered Index
    • PRIMARY
      • Size :- 28.2240 MB
  2. Non Clustered Indexes
    • INDX_DBA_RECORDCREATED
      • Size :- 2.5760
  3. Note
    • Note that our table is INNODB, and as such
      • The default page size is 16384 bytes
      • Clustered ( PRIMARY )
        • The data is saved within the Clustered Index

 

Instrumentation

 

Visual Explain

Index Covered

IndexCovered

Index Not Covered

IndexNotCovered

 

Explanation

In the screenshots above, the Visual Explain:

  1. Shows the same exact singular operator for a covered and non-covering Index Scan
  2. The cost is different
    • Covered Index :- 1.41
    • Non Covering Index :- 2.41

 

 

Tabular Explain

Covering Index Exists

Guide

In the Query below, we are filtering on record_created and fetching same column ( record_created ) , and the Clustering Column ( id)

SQL


set @currentTime := Now();
set @currentDate := curdate();
	 
explain select  
            tblEL.record_created
          , tblEL.id
from    dblab.ErrorLog tblEL
where   tblEL.record_created 
         between @currentDate and @currentTime
;

 

Output

Explain-Tabular-20160620-1111AM

 

 

Non-Covering Index Exists

Guide

In the Query below, we are filtering on record_created and fetching same column ( record_created ) , the Clustering Column ( id), and an additional column ( userid) which is not part of the Indexed Columns, nor part of the Clustering keys.

SQL


set @currentTime := Now();
set @currentDate := curdate();
	 
explain select  
            tblEL.record_created
          , tblEL.id
          , tblEL.userid
from    dblab.ErrorLog tblEL
where   tblEL.record_created 
         between @currentDate and @currentTime
;

Output

Explain-Tabular-20160620-1107AM

 

Explanation

  1. The columns of the Index, INDX_DBA_RECORDCREATED, that we are using is record_created
  2. Here is what Explain returns as a Tabulated Output
  3.  Columns
    • Extra
      • In the first Query, we are covering and the Extra column indicates so by stating ‘Using where; Using index’
      • In the second Query, we are not covering as the Extra column reads ‘Using index condition

Summary

With the Explain command, both the graphical and the tabulated output have merits.

The Graphical has Costing information; while the tabulated furthers our understanding of whether the Index employed fully covers our need or whether additional work needs to be done upon processing the Index.

MySQL :- Secondary Indexes and the Clustering Keys

Preface

RDMS Database tables can either be stored as a Heap or Clustered. When stored as a Heap, data is appended as they come in.  When Clustered, data is sequenced based on the Clustering Columns.

Secondary Indexes

For Clustered tables, the Clustering data is written as an additional data on each record.  On the other hand for Heaps, the RID is recorded.

We will soon see that this is an important decision when selecting indexing columns for both Clustered and Secondary indexes.

 

Database

Let us consider the implication of storing the Clustering columns for Secondary Indexes.

MySQL

We will use the same table we used for our last post.

Here is what the table looks like.

Table Columns

TableColumns

Table Indexes

And, here are the Indexes.

TableIndexes

Explanation

We have two indexes:

  1. PRIMARY
    • Columns
      • id
  2. INDX_DBA_RecordCreated
    • Columns
      • record_created

 

Query

Let us issue a query against the table and issue explain to determine if an index is employed and any additional operators included.

SQL

SQL – Fetch on Secondary Index Columns and Clustered Index Columns

SQL Code


set @currentTime := Now();
set @currentDate := curdate();
	 
select  tblEL.record_created, id
from    dblab.ErrorLog tblEL
where   tblEL.record_created 
         between @currentDate and @currentTime
;

 

Output

Visual Explain

VisualQueryPlan-20160620-0351AM

SQL – Fetch on Secondary Index Columns, Clustered Index Column, and an additional column

SQL Code


set @currentTime := Now();
set @currentDate := curdate();
	 
select  
            tblEL.record_created
          , tblEL.id
          , tblEL.userid
from    dblab.ErrorLog tblEL
where   tblEL.record_created 
         between @currentDate and @currentTime
;

 

Output

Visual Explain

VisualQueryPlan

Index Range Scan

VisualQueryPlan_Clipped

Explanation

  1. Secondary Index and Clustering Columns
    • Fully satisfied with index on Secondary Index Columns
      • Operation
        • Index Range Scan
          • Index Name – INDX_DBA_RecordCreated
      • Secondary Index columns consulted for where clause
      • Secondary Index / Clustering Key consulted for projected clustering columns
      • Query Cost :- 1.41
  2. Secondary Index, Clustering Columns, and additional column(s)
    • Though, not all columns that need to be presented are available from the Index, the Index is still very useful for filtering, and it is used
    • Query Cost :- 2.41

 

Summary

Secondary Indexes do not need to include the Clustering Columns, as those columns are automatically hard-wired in.

When all filtering and projected columns are referenced in the Secondary Indexes, they can offer fast and sole access to the needed result.

On the other hand, when a query references other columns besides the columns that make up the Secondary Index and the Clustering Columns, it does not appear that the Access Path is fully realized through the Explain Guide.

But, if we pay close attention to Query Costs we are able to furtherance our comparison.

Technical: Microsoft – SQL Server – Analysis Services – MDX Query – Error – hierarchy already appears in the Axis1 axis

Technical: Microsoft – SQL Server – Analysis Services – MDX Query – Error – hierarchy already appears in the Axis1 axis

Introduction

Getting up to speed with SQL Server Analysis Service Cube Browsing and Reporting. And, finding that I need to get comfortable with MDX.

MDX is a query language for querying OLAPS.

Data Source View

DataSourceView


Fact Table Browser

What we are trying to do, that is filter on specific Country IDs and Names, is easy to do when using a query or reporting tool. Before Filtering Browser-Before-Filtering

Post Filtering Browser-After-Filtering

Query

But, when we resort to MDX and try to do same, we find out that we have to work a bit harder. In the next two examples, we attempt to filter by using the where clause.

Error Message :- The MDX function CURRENTMEMBER failed because current coordinate is empty.

In this scenario, we pass in a non-existence Country ID (47)


select

	{
		[Measures].[GDP Amount]

	} on COLUMNS

	, {

		[Country].[Country ID]

	  } on ROWS

from  [DBLAB]

where (

		{

			 [Country].[Country ID].&[47]

		}

	  )

Here is our error message:

Executing the query ...
The MDX function CURRENTMEMBER failed because current coordinate is empty.
Execution complete

Error Message – The Geography hierarchy already appears in the Axis1 axis.

In this scenario, we pass in an existing Country ID (4)


select

	{
		[Measures].[GDP Amount]

	} on COLUMNS

	, {

		[Country].[Country ID]

	  } on ROWS

from  [DBLAB]

where (

		{

			 [Country].[Country ID].&[4]

		}

     )

 

Error Message:

The Country ID hierarchy already appears in the Axis1 axis.

Resolution

There are a couple of ways to address our little problem.

Our proposed solution involves introducing our filtering at the Row sub-section.

To do so we can employ the exists or the Filter expressions.

Fix Problem by using exists keyword



select

	{
		[Measures].[GDP Amount]

	} on COLUMNS

	, EXISTS
		(
			  [Country].[Country ID].Members
			, {
			         [Country].[Country ID].&[3]			
			       , [Country].[Country ID].&[4]
                           }

	       ) on ROWS

from  [DBLAB]

Fix Problem by using “filter” keyword


select

	{
	    [Measures].[GDP Amount]

	} on COLUMNS

	, FILTER
		(
	 	    [Country].[Country ID].Members
			, (
				    ([Country].[Country ID].Member_value = 2)
				 or ([Country].[Country ID].Member_value = 3)
			  )	 

	    ) on ROWS

from  [DBLAB]

References

References – MDX Query

References – Restricting the Query with Query and Slicer Axes

References – Blogs

 

References – MDX Query – Q/A