SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables.  One of the capabilities is to allow for stored procedures to call other stored procedures.  This is called “nesting” one stored procedure within the context of the calling stored procedure.  In addition, you can instantiate a temporary table from within the parent procedure that can be utilized within the context of the child procedure.

But wait! I’m not done yet!

You can also instantiate a temporary table with the same name as the parent temporary table within the child procedure.  But there is a catch!

From Microsoft Documentation:

Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure. 

In other words, the name and the schema of the child temporary table must be identical to the name and schema of the parent temporary table.  Otherwise, mass confusion reigns supreme and you could get errors when attempting to do any data modifications in the child temporary table.

Let’s look at some code so that we can see the behavior.

Examining the behavior

First, here’s the code to show the example:

USE Test
GO

CREATE OR ALTER PROCEDURE dbo.parent
AS
BEGIN
       CREATE TABLE #test (name sysname)
       INSERT #test (name) VALUES ('Joey')

       EXEC dbo.child

       SELECT * FROM #test
END
GO

CREATE OR ALTER PROCEDURE dbo.child
AS
BEGIN
       CREATE TABLE #test (fname sysname)
       INSERT #test (fname) VALUES ('John')

       SELECT * FROM #test
END
GO

-- Execute the parent procedure
EXEC dbo.parent

Now, let’s step through it so that we have a clear understanding of what it is doing.

1. Create a parent procedure that creates a temporary table called #test with “name” as the column name.
   1. Insert a row into the parent temporary table
   2. Call a child stored procedure
   3. Select from the parent temporary table
2. Create a child stored procedure that creates a temporary table also called #test with “fname” as the column name. Note that this column name is different from the parent temporary table.
   1. Insert a row into the child temporary table
   2. Select from the child temporary table

Below is the error that is returned when executing the code block.

The error above is stating that the “fname” column does not exist within the temporary table but we can see from the code block that is most definitely is.  This means that SQL Server is attempting to update the parent temporary table and NOT the child temporary table as one might expect.

Summary

When working with nested procedures as well as nested temporary tables, make sure to either have the identical name and schema or make sure to use a different naming convention.  By doing so you help to eliminate any issues of the SQL Server not resolving to the appropriate temporary table.

© 2021, John Morehouse. All rights reserved.

Happy New Year!!

Looking back at my Happy New Year! post from the start of 2020, I of course, didn’t realize the brute force of the global pandemic that was about to hit.  Even with the dumpster fire that was 2020, good things still occurred even in the midst of the world burning.  Babies were born.  Relationships started.  New doors opened with new opportunities and the list continues.  2020 is behind us now and in the review mirror.  Let’s focus on making 2021 a vast improvement from 2020.

With that said, I’m going to repeat my thoughts for the new year.  They are still valid and hold true.

Here are some thoughts for the new year:

• Don’t wait to start that project.  Some day never comes around.
• You are perfect the way you are.  However, if you want to get healthy, know that you aren’t alone.  You got this.
• Tell those people close to you how you feel about them.  Tomorrow may not come for some.
• Tell your kids (fur babies count too!) that you love them as often as you can.  My kids hear it from as often as I can.
• Finally, be kind to each other.  The world is harsh enough as it is without us beating each other up.

Hopefully this post comes at a time where we see the Covid vaccine rolling out to the masses.  Until then, keep wearing a mask and stay safe out there.

Peace.

© 2021 – 2020, John Morehouse. All rights reserved.

The post Happy New Year! first appeared on John Morehouse.

Christmas is this week so not a technical post for this week.  Just a simple post wishing you and your family as many blessing as possible (especially in the year 2020) and good tidings during this holiday time.  I hope that 2020 wasn’t too harsh on you or anybody close to you.  May the holidays bring you peach and joy!

Take care and wear a mask!

© 2020, John Morehouse. All rights reserved.

The post Merry Christmas first appeared on John Morehouse.

Unequivocally, yes on-premises SQL Server Instances are still relevant.

While I’m a firm believer that the cloud is not a fad and is not going away, it’s just an extension of a tool that we are already familiar with.  The Microsoft marketing slogan is “It’s just SQL” and for the most part that is indeed true.  However, that does not mean that every workload will benefit from being in the cloud.  There are scenarios where it does not make sense to move things to the cloud so let’s take a look at a few of them.

The cloud can cost a lot

There is no such thing as a free lunch and the cloud is not excluded.  I am sure that we’ve all heard horror stories of individuals leaving resources active which in turned costed large sums of money. While the cloud offers up a wide range of capabilities in aiding the day-to-day life of IT professionals everywhere, it might not be cost effective for your given workload or data volumes. Compute resources and all things associated with that cost money.  If you need higher CPU, more money.  If you need terabytes of storage, more money.  If you need a higher CPU to memory ratio for that virtual machine, more money.  All of the resources the cloud offers you essential rent and the bigger the space, the more money it takes. Of course, all of this is dependent on your organizational requirements and associated workloads.

By having an on-premises environment you can implement a lower cost of ownership for hardware.  This being said, the cloud offers up more efficient means of upgrade and scaling which is usually limited with on-premises ecosystems which can actually save you money.  It’s a trade-off that organizations have to weigh to see if moving to the cloud makes sense.

You want control of all things

Most things in the cloud require that organizations relinquish control.  That is just a plain fact and that’s not changing.  We are trading speed and agility from an infrastructure perspective for a lower ability to control certain aspects of the architecture.  For example, with Azure SQL Database (Platform as a Service), database administrators no longer can control database backup method or frequency.  In exchange for this loss of control, though, backups are taken automatically for us. In my opinion, this is a more than fair exchange and I sleep better knowing that a tried and vetted backup process is taking care of things without my intervention.

You have specific compliance or regulation requirements

While most of the players in the public cloud space (Azure, Amazon, Google) are all certified for a multitude of compliance regulations, it’s possible that you have a very specific one that the provider is unable to meet.  If this is the case, then your ability to move to the cloud is limited and you are forced to remain on-premises.  Regulations could also impose issues when moving to that cloud.  These regulations could be imposed by the governing body of the organization or be sourced from various places.  If this is the case, it’s possible that the cloud is not a viable solution for your organization.

I do suspect that as cloud technology continues to advance, regulations and compliances will slowly be brought into the fold and allow for appropriate cloud implementations.

You do not have the expertise

Put simply, you do not have the knowledge internally to successfully migrate to the cloud nor do you have the budget to hire someone to move you to the cloud.  Shameless plug, this one of our core competencies here at Denny Cherry & Associates Consulting.  We help organizations (big or small) get into the cloud to help push their data ecosystem forward.  However, not every organization can afford to hire consultants (short or long term) to help them with such a project.  In this instance, until you can get the expertise to help you are left with either staying on-premises or trying to figure it out on your own.  In some respects, the cloud opens new security exposures that must be accounted for when moving to it.  If these are not accounted for the organization severe issues could arise so I recommend not going down the “we’ll figure it out as we go” method without some level of guidance.

Your workloads do not perform in the cloud

Even though I am a huge fan of Azure, some workloads just won’t perform well unless you break out your wallet (see the first paragraph).  Even with proper performance tuning, the performance comparison between on-premises and the cloud is not going to be a true apples to apples comparison.  The infrastructure is just too vastly different to really get that “exact” level of comparison.  Organizations must find that sweet spot between performance infrastructure costs and frankly, sometimes that sweet spot dictates remaining with on-premises hardware.

Summary

There are probably many other reasons why on-premises infrastructures will continue to be relevant.  Each organization may have unique requirements that having SQL Server on their own hardware is the only solution.  Remember, regardless of where you deploy SQL Server, it is just SQL and it’ll behave the same (mostly).  This does not mean that you should not continue to expand your skill sets.  Make sure to continue to learn about cloud technologies so that when your organization is ready to make the leap, you can do so in a safe and secure manner.

© 2020, John Morehouse. All rights reserved.

The post Is On-premises SQL Server Still Relevant? first appeared on John Morehouse.