אוק31
Written by:
ronen ariely
31/10/2021 09:44 
Advanced riddle for community experts - Can you answer it without keep reading the post?
Several days ago, I publish a riddle in the SQL Server product family on Facebook.
I published links to this Facebook thread on my profile on Facebook, linkedin and twitter . I meant that people will click the link -> join the group -> and continue the discussion in the original thread, but my friends start to answer and discuss the riddle on linkedin as well.
In fact, the answers came at the end in the discussion on my profile on Facebook. Therefore, in this post, I will "merge" the information from the different social media networks and also present some of the clarification questions which raised. But first, you need to know what this is all about...
The short version of the riddle is this: When we are executing a CREATE TABLE then the get the error that the table already exists, but when ew are exploring the SSMS Object explorer, then we cannot see any such such table. Can you explain why and how to reproduce this station?
Well... Can you answer this question without keep reading the post?
Let's add some blank lines for the sake of these who do not want to cheat and watch the answer(s) 😀
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Just for fun - a bit about the discussion on Social media networks
Well... As expected this riddle opened a nice and interesting discussions. Moreover, a few hours after I published it The discussion split 🙂
I will not add all the links but You can click this link to watch the original discussion where the answer(s) that I was looking for were given and this link on my profile where the first answer (one I was not thinking about) was given.
Let's go over some of the clarifications which members asked for, and some points which were raised. It will give you more time to think about the riddle and maybe find an answer.
❓ Q: Are you using an outdated version of SSMS?
✔ A: No
❓ Q: is the instance On prem SQL Server, SQL Azure DB, Azure MI or Synapse?
✔ A: All of the above
❓ Q: Have you right clicked on the database and this will refresh the new table. It is annoying that SSMS doesn't auto-refresh explorer after DDL.
✔ A: No.
I mean that I agree it is annoying that SSMS doesn't auto-refresh explorer after DDL, but this is not relevant to the riddle.
❓ Q: Many questions an ideas were raised which related to bugs like: Issues with mdf file? I am seeing the uncertainty principle or schrodingers cat in sql server for this. But I hope it's not that;
A: No. Nothing related to bug. This is a consistent and a normal behavior, in specific scenario.
Q: Then it must be permissions
A: No.
Q:
.
.
.
.
from Saada David came with a very nice option: A synonym is already existing with same name.
which cover the description I gave. It is not the one I spoke about, so the we have another option and the game continue...
OK, here some more information to clarify: The table exists, it is a real simple user table. We can use the table like any other table (no relation to permission). We can see the table when we execute "select * from sys.tables"
.
.
.
Manoj Pandey found the answer which is using filters - a feature exists in the SSMS which allows us in a few clicks to choose what the SSMS present (I will publish a full post in my blog in a few hours) - well done Manoj and well done Saada
a few experts tried their best to answer the question. In fact, when I asked this riddle, then I had two answers in my mind, but during the discussion another answer was raise, so if you found only one answer at this time, then you can keep thinking about the riddle just before you keep reading since it seems that we have three answers to this riddle 😀
Manish Kumar found the answer
The short answer is that SQL Server allow us to manage Extended Properties to objects and SSMS check for specific property named "microsoft_database_tools_support". If this exists for specific table for example then SSMS will not show the table.
Managing Extended Properties is done using the stored procedures sp_addextendedproperty; sp_updateextendedproperty; and sp_dropextendedproperty
This is an extremely important feature!
Unfortunately not many DBAs know about Extended Properties even so Microsoft use it in all their sample database as should be.
This feature allow us to store in the database free key/value information about most objects, for administration purpose and more!
For example, you can describe tables for these who will come after you, so they will know what this table is about and maybe even some plan and thought you have about the table.
Check AdventureWorks2019 for example. Search for Extended Properties. Each table has well organized information which describe the table, stored in the properties of the table🙂
USE AdventureWorks2019
GO
select * FROM sys.extended_properties
GO
But these properties are also use for other uses. For example SSMS use it to choose if it need to display the object or not
.
.
imagine now fireworks and balloons and victory sirens!
WE HAVE A WINNER!
The short answer is that SQL Server allow us to manage Extended Properties to objects and SSMS check for specific property named "microsoft_database_tools_support". If this exists for specific table for example then SSMS will not show the table in the list of user tables.
Managing Extended Properties is done using the stored procedures sp_addextendedproperty; sp_updateextendedproperty; and sp_dropextendedproperty
This is an extremely important feature!
Unfortunately not many DBAs know about Extended Properties even so Microsoft use it in all their sample database as should be.
This feature allow us to store in the database free key/value information about most objects, for administration purpose and more!
For example, you can describe tables for these who will come after you, so they will know what this table is about and maybe even some plan and thought you have about the table.
Check AdventureWorks2019 for example. Search for Extended Properties. Each table has well organized information which describe the table, stored in the properties of the table🙂
USE AdventureWorks2019
GO
select * FROM sys.extended_properties
GO
But these properties are also use for other uses. For example SSMS use it to choose if it need to display the object or not