(scottish) sql bob blog

rss

Some thoughts of your typical data shepherd / data plumber / data dance teacher sort of person.


SuperUser Account
SuperUser Account

Twitter handle - @SqlBobScot


Exploring Temporary Objects in SQL Server
There are many mysteries in life fortunately there are few about table variables.  That said when I look at other people's code, or when I hear their opinions about Table Variables, there is some mis-understandings about Table variables.  Let's be clear I love table variables, like many things in life if used correctly they are amazing.  It's easy to misuse something especially if we make assumptions and yes, I am a pointing a finger at myself.  

When I talk to people about table variables one of the items of advice that I give them, is that table variables are NOT created in memory.  Just like temporary tables are created in TempDB.  There might be some people reading this which disagree with me.  So, let's run some code. 

/*Create a table variable, with a column name which is kind of unique*/ 
DECLARE @TestTableVariable AS TABLE(ID_VerySpecialColumnNameForThisBlog INT); 

/*Place some values into the table variable, just so it has something   
In the table variable*/ 
INSERT INTO @TestTableVariable 
VALUES(1); 
INSERT INTO @TestTableVariable 
VALUES(2); 
DECLARE @TestTableVariable AS TABLE(ID_VerySpecialColumnNameForThisBlog INT); 
IF EXISTS 
(
    SELECT * 
    FROM tempdb.sys.tables AS T 
         INNER JOIN tempdb.sys.columns AS C ON T.Object_ID = C.Object_ID 
    WHERE C.Name = 'ID_VerySpecialColumnNameForThisBlog' 

    BEGIN 
        SELECT 'Found it, look here -> ',  
               T.Name,  
               C.Name,  
               C.column_id 
        FROM tempdb.sys.tables AS T 
             INNER JOIN tempdb.sys.columns AS C ON T.Object_ID = C.Object_ID 
        WHERE C.Name = 'ID_VerySpecialColumnNameForThisBlog'; 
   END; 
ELSE 
    BEGIN 
        SELECT 'Opps cannot find temp table with a column name of [ID_VerySpecialColumnNameForThisBlog] my bad'; 
    END; 

So first the code created a table variable, when the table variable was created, the name of the column was rather unique (there is a reason for that).  Next there was some records inserted into the table variable, just for a bit of fun. 

So the query in the EXISTS() just checks to see if there is a table created in TempDB and one of the columns has a name exactly the same as the one in the table variable created.  This shows that the Table Variable is created in TempDB, just like a temporary table.  Maybe you still do not believe me?  Well maybe this from Microsoft will clarify the point  

A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).” 


TSQL Tuesday Post - Impostor Syndrome
When I first saw this T-SQL Tuesday topic, I must admit that I was surprised that this had never been a topic before now.  As I write this post, there is a part of me which is struggling with Impostor Syndrome.  Which is ironic, although not surprising, at least to myself.  

Speaking for myself, impostor syndrome is something I feel when I am around others in the community.  Since I do not say it often enough the SQL family is the most talented, amazing group of people.  Comparing myself to others I do not feel as talented or amazing.  For example, when I was lucky enough to be chosen to speak at Data Relay 2019, it was one of the most interesting challenges that I had to overcome.  Seeing the people that were also chosen to speak, it was obvious there was an amazing line up. 

So what was my method for dealing with the feelings of impostor syndrome when at Data Relay 2019?  Well there was several things I did.  First I researched and then rehearsed the heck out of my session.  One of the awesome aspects of Data Relay, is you are one of the crew.  You are encouraged to be part of the set-up crew, unload equipment, speak to attendees, hand out badges, etc.  This kept me busy and my mind occupied whilst I was waiting for my turn to speak.  One other thing was to remind myself that I have a unique way of viewing things.  Often I am surprised when I share something and people see something new.  Just as many other people have shared something unique and different with me. 

As a “community-educated” (@TheStephLocke) developer I have to admit that much of my second career has had a constant background of Impostor Syndrome.  Even when I changed career and finally secured a role working as a full time( kind of) developer (long story).  Since then and even now, I feel that everyone else knows more than me.  This I know is not correct, yet it is also what drives me to keep learning more.  That way it is turned into something positive and is used to motivate me.  The other advantage is that I hope it keeps me honest and humble.  I remember only too well how it feels to struggle to understand and grasp new concepts.  The delight I get from showing people new things they have not seen before cannot be measured.  Even better when you see them using their new-found knowledge. 

Does this mean that I am some incredible amazing person who has conquered Impostor Syndrome?

via GIPHY

No I can honestly say I have not.  It is something I have struggled with over many years. There are, at least two jobs that I have done, looking back at now I recognise that impostor syndrome was an issue when I was working in those roles.  Fortunately, I have discovered coping strategies to help me deal with this.  When I compare myself to others, my automatic response is to see anyone and everyone as better or more knowledgeable than myself.  So what I have learned to do is step back and remind myself of what I have achieved to date, which successes that I have made happen.  Things I have contributed to.  Remind myself that I might not know everything, that said, I have the knack and ability to solve complex challenges.  This is something I remind myself of, sometimes on a daily basis.  Is this easy? “heck no!!!” 

Another strategy I have used is positive self-talk, what am I talking about?  This article has a nice summary of self-talk.  For me it is all too easy to listen to that part of my mind which is less than positive about something, listening to the words and accepting them.  Now I recognise that I have a choice that I can make.  My personal self-talk is a result of experiences, and what has been said to me in the past.  What I do is make a choice and tell myself what I want to be and what I know that I am more than capable of being.  There is no doubt some people who read this, will be sceptical and dismiss it out of hand.  That is their choice.  Just as I write this @LuiseFreese has just tweeted this  



The tweet says to me that , changing your self-talk can make a difference. 

More than once I have considered speaking about this and some other topics, hopefully I will submit a session with them in it to a conference one day soon….. 

When I speak in my sessions, I have one goal.  It is clear and that goal is what I focus on when preparing and then delivering my sessions.  That one person will hear what I say and that it helps just one person.  That is the goal of this post that one person will be helped even just realising it’s not just you that feels that way.  Also that you have the power to change.