Study your Data

When you are in charge of databases with tons of objects, making a small change over an object can be difficult to replicate if you have a lot of objects that depend on it. For this post, we will focus on how to update column changes on dependent views. For this type of task, there are 2 scenarios: You explicitly defined column names on the dependent views, or defined WITH SCHEMABINDING: For this case, you need to perform a find/replace task, this can be difficult to achieve since there are no native tools to do it (as far as I know). You can use third-party free tools like RedGate SQL Search or ApexSQL SQL Search , both having its pro and cons, so take a look at both tools and find out what it suits best your needs. You implicitly defined column names on dependant views: The famous SELECT * , even when this is not a best practice , it is easy to replicate changes for this case, we will show you how to do it. Setting up our example We will use WideWorldImporters

SQL Server engine does an excellent job to optimize query performance and it has different methods to achieve this goal, one of these methods is the data caching. Data caching consist of putting 8KB pages from storage into memory as they are needed, so the database engine can access the required data again in a much faster way. At a glance, the way SQL Engine caches the data is the following: When a request is made the engine checks if the data is already cached in memory (or buffer pool), if the data is already available, then it is returned. If the data is not available, then it is retrieved from storage and put into memory until the buffer pool is full. How long the data is stored in the buffer pool depends on various aspects: the memory allocated to SQL Server, size of your database, workload, type of queries executed, In-memory OLTP.. etc. When the buffer pool does not have enough space to allocate the desired memory, then a data spill occurs, it consists of additiona