December 2014 – Page 2 – Andrey Zavadskiy – SQL and .NET developer, trainer, speaker

December 30, 2014September 2, 2016

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.

CREATE VIEW ClientsInfo
AS
SELECT c.ClientName, p.PhoneNumber
FROM Clients c
JOIN PhoneNumbers p ON c.ClientGuid = p.ClientGuid;

CREATE VIEW ClientsInfo

SELECT c.ClientName, p.PhoneNumber

FROM Clients c

JOIN PhoneNumbers p ON c.ClientGuid = p.ClientGuid;

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

INFORMATION_SCHEMA views

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

December 30, 2014January 15, 2016

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.

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

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

Click the link Click here to create in the center of Test Designer.
Write T-SQL code to disable the constraint.

ALTER TABLE Clients NOCHECK CONSTRAINT CK_Clients_ClientName_Length;

1	ALTER TABLE Clients NOCHECK CONSTRAINT CK_Clients_ClientName_Length;

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

ALTER TABLE Clients NOCHECK CONSTRAINT ALL;

1	ALTER TABLE Clients NOCHECK CONSTRAINT ALL;

Now our test really passed.

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

December 30, 2014September 2, 2016

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.

DECLARE @spname sysname;

DECLARE SPCursor CURSOR FOR
SELECT SCHEMA_NAME(schema_id) + '.' + name
FROM sys.objects
WHERE type = 'P';

OPEN SPCursor;
FETCH NEXT FROM SPCursor INTO @spname;
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC('DROP PROCEDURE ' + @spname);
  FETCH NEXT FROM SPCursor INTO @spname;
END
CLOSE SPCursor;
DEALLOCATE SPCursor;

DECLARE @spname sysname;

DECLARE SPCursor CURSOR FOR

SELECT SCHEMA_NAME(schema_id) + '.' + name

FROM sys.objects

WHERE type = 'P';

OPEN SPCursor;

FETCH NEXT FROM SPCursor INTO @spname;

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC('DROP PROCEDURE ' + @spname);

FETCH NEXT FROM SPCursor INTO @spname;

END

CLOSE SPCursor;

DEALLOCATE SPCursor;

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

December 30, 2014September 2, 2016

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.

OK! We have the correct result. Now we want to find mismatches. Just add NOT before a subquery.

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).

Match queries find the exact rows as shown above but the results of mismatch queries are different.

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:

Don’t use NULL columns if it doesn’t really needed
Test thoroughly your queries for NULL/NOT NULL matches

December 30, 2014November 22, 2016

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:

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main(string[] args)
    {
      DataTable dtTVParameter = new DataTable();
      dtTVParameter.Columns.Add("PhoneNumber", typeof(string));
      dtTVParameter.Columns.Add("PhoneTypeID", typeof(Int16));

      DataRow row1 = dtTVParameter.NewRow();
      row1["PhoneNumber"] = "88005555555";
      row1["PhoneTypeID"] = 1;
      dtTVParameter.Rows.Add(row1);

      DataRow row2 = dtTVParameter.NewRow();
      row2["PhoneNumber"] = "88007777777";
      row2["PhoneTypeID"] = 2;
      dtTVParameter.Rows.Add(row2);

      using (SqlConnection conn1 = new SqlConnection("Data Source=(local);Initial Catalog=ClientsDB;Integrated Security=True"))
      {
        using (SqlCommand cmd1 = new SqlCommand("[dbo].[SaveClientPhoneNumbers]", conn1))
        {
          cmd1.CommandType = CommandType.StoredProcedure;
          cmd1.Parameters.AddWithValue("@ClientGuid", "14a579d2-3689-45e1-af6f-7dfbb17d57cf");
          cmd1.Parameters.Add("@PhoneNumbers", SqlDbType.Structured);
          cmd1.Parameters["@PhoneNumbers"].Value = dtTVParameter;

          conn1.Open();
          cmd1.ExecuteNonQuery();
        }
      }
    }
  }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1

{

class Program

{

static void Main(string[] args)

{

DataTable dtTVParameter = new DataTable();

dtTVParameter.Columns.Add("PhoneNumber", typeof(string));

dtTVParameter.Columns.Add("PhoneTypeID", typeof(Int16));

DataRow row1 = dtTVParameter.NewRow();

row1["PhoneNumber"] = "88005555555";

row1["PhoneTypeID"] = 1;

dtTVParameter.Rows.Add(row1);

DataRow row2 = dtTVParameter.NewRow();

row2["PhoneNumber"] = "88007777777";

row2["PhoneTypeID"] = 2;

dtTVParameter.Rows.Add(row2);

using (SqlConnection conn1 = new SqlConnection("Data Source=(local);Initial Catalog=ClientsDB;Integrated Security=True"))

{

using (SqlCommand cmd1 = new SqlCommand("[dbo].[SaveClientPhoneNumbers]", conn1))

{

cmd1.CommandType = CommandType.StoredProcedure;

cmd1.Parameters.AddWithValue("@ClientGuid", "14a579d2-3689-45e1-af6f-7dfbb17d57cf");

cmd1.Parameters.Add("@PhoneNumbers", SqlDbType.Structured);

cmd1.Parameters["@PhoneNumbers"].Value = dtTVParameter;

conn1.Open();

cmd1.ExecuteNonQuery();

}

December 30, 2014September 2, 2016

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.

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.

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.

December 30, 2014September 2, 2016

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.

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

December 30, 2014September 2, 2016

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

INSERT INTO PhoneNumbers (
  PhoneGuid,
  PhoneNumber,
  PhoneTypeID
)
VALUES
  (NEWID(), '88001111111', 1),
  (NEWID(), '88002222222', 2),
  (NEWID(), '88003333333', 2);

INSERT INTO PhoneNumbers (

PhoneGuid,

PhoneNumber,

PhoneTypeID

)

VALUES

(NEWID(), '88001111111', 1),

(NEWID(), '88002222222', 2),

(NEWID(), '88003333333', 2);

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

use INSERT INTO … SELECT with UNION ALL

INSERT INTO PhoneNumbers (  
  PhoneGuid,  
  PhoneNumber,  
  PhoneTypeID  
)
SELECT NEWID(), '88001111111', 1  
UNION ALL  
SELECT NEWID(), '88002222222', 2  
UNION ALL  
SELECT NEWID(), '88003333333', 2;

INSERT INTO PhoneNumbers (

PhoneGuid,

PhoneNumber,

PhoneTypeID

)

SELECT NEWID(), '88001111111', 1

UNION ALL

SELECT NEWID(), '88002222222', 2

UNION ALL

SELECT NEWID(), '88003333333', 2;

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

December 30, 2014November 18, 2016

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:

convert values to string, for example, separated with comma;
pass as XML;
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 TYPE [dbo].[PhoneNumberType] AS TABLE
(
  PhoneNumber VARCHAR(20),
  PhoneTypeID SMALLINT
)

CREATE TYPE [dbo].[PhoneNumberType] AS TABLE

(

PhoneNumber VARCHAR(20),

PhoneTypeID SMALLINT

)

create a stored procedure

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

CREATE PROCEDURE [dbo].[SaveClientPhoneNumbers]
  @ClientGuid UNIQUEIDENTIFIER,  
  @PhoneNumbers PhoneNumberType READONLY  
AS  
BEGIN  
  SET NOCOUNT ON;  
    
  DELETE FROM PhoneNumbers  
  WHERE ClientGuid = @ClientGuid;  
    
  INSERT INTO PhoneNumbers (  
    ClientGuid,  
    PhoneNumber,  
    PhoneTypeID  
    )  
  SELECT @ClientGuid, PhoneNumber, PhoneTypeID  
  FROM @PhoneNumbers;  
END

CREATE PROCEDURE [dbo].[SaveClientPhoneNumbers]

@ClientGuid UNIQUEIDENTIFIER,

@PhoneNumbers PhoneNumberType READONLY

BEGIN

SET NOCOUNT ON;

DELETE FROM PhoneNumbers

WHERE ClientGuid = @ClientGuid;

INSERT INTO PhoneNumbers (

ClientGuid,

PhoneNumber,

PhoneTypeID

)

SELECT @ClientGuid, PhoneNumber, PhoneTypeID

FROM @PhoneNumbers;

END

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.

[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

December 30, 2014September 2, 2016

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

SELECT STUFF(
  (
    SELECT ',' + [field_name]
    FROM [table_name]
    FOR XML PATH(''), TYPE
  ).value('.', 'varchar(max)'),
  1, 1, '') AS [string_of_values]

SELECT STUFF(

(

SELECT ',' + [field_name]

FROM [table_name]

FOR XML PATH(''), TYPE

).value('.', 'varchar(max)'),

1, 1, '') AS [string_of_values]

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.

UPDATE 2016-04-13:

Here is another great article about different ways how to make group concatenation and performance comparison http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation