Find Dependent Objects

One new feature that SQL Server 2008 offers is more reliable dependency information. SQL Server 2005 offered a DMV (dynamic management view) called sys.sql_dependencies. It is now replaced by a more reliable sys.sql_expression_dependencies. The following script will show all the procedures that reference a given table name, along with the columns the procedure references also. Please note, this does not take into account any dynamic SQL. [cc lang=”sql”] USE MYDatabase GO DECLARE @TableName varchar(100) SET @TableName = ‘mytable’ SELECT SourceSchema = OBJECT_SCHEMA_NAME(sed.referencing_id) ,SourceObject = OBJECT_NAME(sed.referencing_id) ,ReferencedDB = ISNULL(sre.referenced_database_name, DB_NAME()) ,ReferencedSchema = ISNULL(sre.referenced_schema_name, OBJECT_SCHEMA_NAME(sed.referencing_id)) ,ReferencedObject = sre.referenced_entity_name ,ReferencedColumnID = sre.referenced_minor_id ,ReferencedColumn = sre.referenced_minor_name FROM sys.sql_expression_dependencies sed CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ‘.’ + OBJECT_NAME(sed.referencing_id), ‘OBJECT’) sre WHERE sed.referenced_entity_name = @TableName AND sre.referenced_entity_name = @TableName [/cc] Continue reading ...

Drop Index

[cc lang=”sql”] DROP INDEX table_name.index_name [/cc]

Choosing the Best Clustered Index

One of the most common database performance issue most companies face is the improper choice of indexes or the lack of indexes. Most essential, is the clustered index. A well-chosen clustered index will save CPU, Memory, and IO. Choosing the clustered index for a table goes hand-in-hand with the writing of the queries for a table. It is best to design the queries and the index simultaneously to keep consistency. The majority of the time this is not an option. When we have not written the queries, the best way to understand the optimal clustered index is to study the queries that are run against the table. Once you understand the queries that run against a table, it is helpful to think of each of these queries broken down into three parts: The select list – Be on the lookout for the queries that return the most columns from the table. The join columns – The most common columns used to join other tables against the table. The where clause – The most common columns used to filter the result sets. Avoiding Fragmentation Before going into depth in choosing a clustered index we need to know what causes fragmentation. We ideally want to answer no to the following: Will the clustered value(s) be updated? Can new rows be inserted between already inserted clustered rows? The reason for answering no above is because we do not want fragmentation at the clustered level. The clustered index is like the foundation of a […] Continue reading ...

Concatenate Rows

This solution does not require a variable and it also allows you to do joins against other tables within one statement. The substring function removes the leading comma. Download the presidents table here. [cc lang=”sql”] SELECT ColumnList = SUBSTRING( (SELECT ‘,’ + ISNULL(President,”) FROM dbo.Presidents FOR XML PATH(”)) ,2,8000) [/cc] Using a common table expression (CTE) you can also recursively union the CTE with itself in order to generate a comma delimited list. This is a useful function for a CTE. The CTE itself does concatenate more than what is needed however it’s still relatively clean. Download the presidents table here. [cc lang=”sql”] ;WITH PresidentList (PresidentNumber, President) AS ( SELECT 1 , CAST(” AS VARCHAR(max)) UNION ALL SELECT cte.PresidentNumber + 1 ,cte.President + pl.President + ‘, ‘ FROM ( SELECT RowNum = Row_Number() OVER (ORDER BY PresidentNumber) ,President FROM dbo.Presidents ) pl JOIN PresidentList cte ON pl.RowNum = cte.PresidentNumber ) SELECT TOP 1 President FROM PresidentList ORDER BY PresidentNumber DESC [/cc] The next two examples require a variable, and when using a variable you cannot display any other columns from the table in the result set. There is a misnomer floating around much of the TSQL community with regards to concatenating strings using COALESCE. In actuality, this has nothing to do with COALESCE. COALESCE is simply used to replace a potential NULL value in one of the rows. Here is the code without using a COALESCE. [cc lang=”sql”] DECLARE @ColumnList varchar(8000) SET @ColumnList = ” — Set this to empty […]

Using the Merge Statement to populate a historical table with Effective Dating

One of my favorite uses for the MERGE statement introduced in SQL Server 2008 is the updating of a historical table. With versions prior to 2008 this operation had to be performed in two separate statements. Merge helps us to streamline the process. The advantage to the database engine when using a Merge statement is in it’s minimal locking. Prior to merge, we had to update the record if it existed. And if it did not exist, we inserted the record. Long story short, it was double the amount of locking before the optimizer knew which rows to exclusively lock. To understand what’s going on in the statement below, you have to read starting from the MERGE statement. This is where the initial updating is taking place first. As you scroll down, you will see the OUTPUT clause. This actually outputs the results of the MERGE statment. You can think of this output as a virtual table named “merged”. The $action variable is an intrinsic column (if you will), that contains the action that row played during the MERGE. Lastly, if you scroll to the top, you will find the insert statement that finally inserts the new “current” record for the corresponding “expired” record we just retired. This was the same row we used for the criteria to UPDATE the expired row, however we did not use any of it’s values. [cc lang=”sql”] DECLARE @Now datetime = GETDATE() DECLARE @EffToDate datetime = ‘2079-06-06T00:00:00.000’ DECLARE @JobID int = 1 — This […] Continue reading ...

Featured Articles

 Site Author

  • Thanks for visiting!