More To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users.

updating 2 tables in sql-38

For large tables, however, such a simplistic approach is impractical because the transaction size will lock out users.

When you are faced with updating most or all rows in a large table and you want to avoid locking out users, you can use the TOP clause to limit the transaction size.

So, I created stored procedure sp_SOS, an expanded version of sp_spaceused, which can be used to calculate SQL Server object space and to perform other functions.

With sp_SOS I wanted to retain the core functionality of sp_spaceused -- i.e., the algorithm to sum up data, index and reserved and unused spaces for an object.

The formula is "Total(MB) - Unused(MB) == Used(MB) = Index(MB) Data(MB)", i.e., the used space is the simultaneous result of total minus unused as well as index plus data.

The "reserved" column in sp_spaceused is actually equivalent to the total size in sp_SOS.

However, there’s a trick to using this clause: You need to keep track of which rows have already been updated so that you don’t update them a second time.

The OUTPUT clause in SQL Server 2005 and later provides a way to expose which rows are affected by a Data Manipulation Language (DML) statement.

Very often, I find it inadequate to meet my intentions.

For instance, when I want an overview of user table sizes in a given SQL Server database, or wish to see the top 10 biggest indexed objects or need to summarize the total space owned by a group of tables, sp_spaceused is of no use.

Sp_SOS takes eight input parameters briefly described in Table 1.