Finding Dependencies Between Database Objects in SQL Server

When I need to analyze dependencies, I should find the answer for 2 questions:

1) which objects does my piece of code depends on? In this case, I get the impact from the others.
When you look at this view definition, you understand that the view depends on two tables.

2) which objects does my piece of code influence on? Now I produce the impact to others.

Microsoft SQL Server has some capabilities to analyze dependencies.

  • catalog view sys.objects
This view shows all database objects and dependencies between parent and child objects such as constraints and triggers. Additional info can be found in MSDN
There are 20 views that can obtain various information about database metadata from different angles. Full list is in MSDN
  • system stored procedure sp_depends

According to information in MSDN, this stored procedure will be removed in the future.

  • dynamic managements functions sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities


Working With Dependencies In SSDT Unit Test: Disable Contstraints

When we run a piece of T-SQL code, it can be dependent on another T-SQL code such as function. For example, stored procedure queries data with only one SELECT command. In this command you can use scalar functions in SELECT clause, table-valued functions or views in FROM clause, scalar functions in WHERE clause (that’s a very bad habit, but it’s possible), etc.

Our T-SQL code can be affected by other database objects such as constraints and triggers. When we insert or update data, these objects can prevent to enter incorrect values, so our command would fail.

Let’s look at how they would impact on database unit testing.
I modify the test case I’ve created earlier in this post and add a simple constraint.

Table with constraint

My test passed because ClientName (‘John Smith’) consists of 10 chars. Now I change to something shorter, and the test failed.

failed test

There’s no error in stored procedure I’ve been testing, it’s a violation of constraint. So to pass this test (I mean to test the functionality of stored procedure) I should eliminate constraint from being applied during the test. It can be done in the following manner:

  • choose an item Pre-test in the dropdown list shown below
Create post-test
  • Click the link Click here to create in the center of Test Designer.
  • Write T-SQL code to disable the constraint.

Alternatively, we can disable all constraints in a table with one command:

Now our test really passed.

pre-test disable constraint
But don’t forget to enable constraint in Post-Test section of unit test.

Deleting All Stored Procedures In SQL Server Database

This is a quite simple task to do with cursor because we need to execute command DROP PROCEDURE. So we create a cursor, fetch all names of stored procedure and drop them.

Here’s the script that implement the above logic.

Update 2015-11-26: The original script with SELECT name FROM sys.objects … works well if you have only the dbo schema. To delete all stored procedures in all schemas we need to use two-part objects names schema.object.

If we change the object type in first WHERE clause (for example, ‘FN’ for scalar functions) and use appropriate command in EXEC statement (DROP FUNCTION in this case) we can delete all database objects of another type. Full list of object types can be found here

NULL Values In WHERE Predicate of SQL Queries (IN, NOT IN And EXISTS). How to Avoid Wrong Results

As you know SQL Server can hold NULL values in table’s columns. It usually means that a value of the column is unknown. For example, what is better to answer the question about on which floor does your friend live: on the 1st or 2nd, maybe 5th, N-th or just say “I’m afraid I don’t know exactly”. So NULL values can save this situation.

But when we try to match values we should be cautious about NULL values. Let’s look at the following examples.
I create two tables with sample data in tempdb.

So we have two matches by first name and one row in Clients table has no match. You can find matches with the help of subqueries starting with IN or EXISTS.
matched rows
OK! We have the correct result. Now we want to find mismatches. Just add NOT before a subquery.
unmatched rows with null
OK! It works correctly.
Now I will insert a new row into Clients table with no first name (there will be a NULL value in the column).
data with null
Match queries find the exact rows as shown above but the results of mismatch queries are different.
unmatched rows with null
It’s somewhat surprising that first query with NOT IN could not find the row with NULL value. To include NULL values you can add additional comparison like OR FirstName IS NULL in ther WHERE clause.
The T-SQL code snippets can be downloaded from null_values_in_where_predicate
To sum up, I would recommend the following:
  1. Don’t use NULL columns if it doesn’t really needed
  2. Test thoroughly your queries for NULL/NOT NULL matches

Calling SQL Server Stored Procedure With Table-Valued Parameter in C#

In one of my previous post I described how to use table-value parameter in T-SQL code. Today I will show how to use it in your .NET application.

This is a sample C# console application. The most important things you should pay attention to are the following:

  1. create DataTable with structure equivalent to table type defined in SQL database (in my case string for varchar(20), and Int16 for smallint)
  2. fill your DataTable with data
  3. add parameter to SqlCommand object with type SqlDbType.Structured
  4. assign DataTable to parameter’s Value property

Assigning Value To Variable in SQL Server: One Big SELECT vs Many Small SELECT/SET

In my previous post I compared one big DECLARE statement vs many small statements. The result was there’s no significant difference between these two approaches. This time I wonder is assignment sensitive to how it is implemented? I mean if you will use one big SELECT command or many small SELECT or SET commands.

At first I would clarify that there’s no difference to SQL Server how you write a single assignment – with SELECT or SET command. Just look at the execution plan – each command imply the ASSIGN operation.

select and set

For experiment I use stored procedure from previous test and write a new one that implement one assignment in SELECT command. If we look at estimated execution plan we’ll see that first stored procedure OneBigDeclare has 30 ASSIGN operation, on the other side the stored procedure OneBigDeclareWithBigSelect has only one ASSIGN operation. Probably the speed of the second SP will be higher.

single and composite assignment

All source code can be downloaded from assignment_execution_test

My results
Average execution time of a cycle:
OneBigDeclare – 77.98ms
ManySmallDeclare – 140.22ms

Great! One big assignment is quite twice faster than 30 single assignments.

DECLARE Variables in SQL Server: One Big Statement vs Many Small Statements

I heard that one big DECLARE statement is better than a plenty of small statements. I tried to check which approach is really better. To find the answer I’ve created two stored procedures OneBigDeclare and ManySmallDeclare. Also I’ve written a simple test that executes 100 cycles. Each cycle executes each stored procedure 10 thousand times.

All source codes can be downloaded from declare_execution_test

My results
Average execution time of a cycle:
OneBigDeclare – 165.62ms
ManySmallDeclare – 171.12ms

It seems that one big DECLARE statement is better for just 3.3%. But when you look at the graph of cycle’s execution time you see that it’s not always true. On X axis is the sequence number of a cycle, on the Y axis is the execution time in milliseconds.

Declare test - execution time graph

So I can’t prove that one big DECLARE statement is really better.

Inserting Multiple Values Into Table in SQL Server

If you need to insert multiple values into table it can be made with one of these methods:

  • write multiple rows after VALUES clause of INSERT command

I used here a thing called table-value constructor. You can read more about it in the MSDN article Table Value Constructor (Transact-SQL)


Important! The first method can be used in SQL Server 2008 version or higher.

Using Table-Valued Parameters in SQL Server Stored Procedures

When you need to pass a definite number of parameters to a stored procedure it’s quite simple. You should define as much parameters as needed. But when you need to pass a non-definite number of parameters (maybe 5, 7, 10 or even 100) it’s harder to implement.

There are some ways to make it:

  1. convert values to string, for example, separated with comma;
  2. pass as XML;
  3. use a table-valued parameter.
In the first two cases you must serialize and deserialize values. The last methods allows to use a parameter like a standard database table. I’ll use it to implement the stored procedure that save phone numbers of the client.
  • create a table type for Phone Numbers

  • create a stored procedure

Important! We must declare a table-value parameter as READONLY.

Now we can declare a variable with type PhoneNumberType, insert some rows into it and call the stored procedure. I use this approach for unit test.
SaveClientPhoneNumbers unit test
[UPDATE 2016-11-18]
There are some considerations about performance of table-valued parameters. Look at Jeremiah Peschka’s post to get more information Using SQL Server’s Table Valued Parameters

Generating a Comma-Delimited String from Rows in SQL Server

I had to solve the task how to make a string of values from a database table because JQuery UI plugin accepts only a comma-delimited string. I used a XML solution suggested by Anith Sen in his article Concatenating Row Values in Transact-SQL

All you need is to replace text in square brackets with actual names. If you need another separator just change the comma char in the 3rd row to something else.

And finally there’s a screenshot of working example.

Select string from rows

UPDATE 2016-04-13:

Here is another great article about different ways how to make group concatenation and performance comparison