Once again, we will dive into the internals of the SQL Server, since someone asked in the forum "How to find who created a database"....

Understanding what we have under the hood is not only done for the sake of learning a theory, but it directly impacts your daily work, and it can help us to improve performance and reduce resources dramatically.

During this session we will go over the structure of tables behind the scenes. We will see the changes behind the scenes related to DDL actions which we execute on tables.

Using this information, I will demonstrate several real case scenarios, and the dramatic impact of understanding the internals on choosing our solution.

Is the order of columns important? Is dropping a column the right solution? Questions like those are very common in the forums and usually does not get the right answers. The answers might be a game changer in production environment

This session is a live demo!

This post summarize my lecture from last week at PASS DBA Virtual Group.

Understanding what we have under the hood is not only done for the sake of learning a theory, but it directly impacts your daily work, and it can help us to improve performance and reduce resources dramatically.

During this session we discussed the structure of tables behind the scenes. We saw the changes behind the scenes related to DDL actions, which we execute on tables. Using this information, I demonstrated several real case scenarios, and the dramatic impact of understanding the internals on choosing our solution.

Is the order of columns important? Is dropping a column is the right solution and what is the impact? Questions like those are very common in the forums and usually does not get the right answers even by experts, but the answers might be a game changer in production environment. 

This session is a live demo!
There is no presentation to share, but in this post I will share the code and some points.

THIS POST DOES NOT REPLACE THE SESSION BUT COMES TO SUMMERTIME IT
It is highly recommended to watch the recording first

A common question that raise in forums: Is The Order of columns important? 

We focus on the database's side and not the client's side point-of-view. For example, is order of columns related to performance, is it related to errors that might rise only when use specific order, or will SELECT query return different DATA (not important the order of the result SET but the DATA) if the order of columns is different...

In this post I want to present some simple demo which answer the question

Update 2019-03-21: adding example from my last lecture.

We have a table with Integer Column, which is no longer needed. What should we do in order to reclaim the size and/or improve performance?

(1) DROP column
(2) UPDATE values to zero
(3) Change the column to null
(4) ALTER column type

This post presents some important points related to discussion about a real-life case, which was asked in the MSDN's forums. For the sake of the post, I will present a summarize of the solutions, which were mentioned in the original thread, and a short discussion on each.

This post is advance topic, level 500 - Internals!

Update 2019-03-019: After presenting a lecture in PASS user group, i add some more points and code.

Several days ago I got a support call from a senior DBA, saying that "someone" in the company dropped a column in a table by mistake, before they copied several of the values,which they needed. Moreover, he explained that they do have any backup of the database and therefore they lost important data.

Before he contacted to me, he tried to get help in the forums, to contact several experts, and he found Microsoft's documentation which claimed that "This action cannot be undone". The question was simply a request to find away to get the lost data... 

Can it be done?!?

The short answer is, that a common user which work only with the LOGICAL layer of SQL Server using the documented tools cannot undone this action, but for SQL Server internals level of users, this is a different story...