Posted on

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

Share on FacebookShare on LinkedInShare on Twitter Share

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.
Posted on

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

Share on FacebookShare on LinkedInShare on Twitter Share

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.

Posted on

Inserting Multiple Values Into Table in SQL Server

Share on FacebookShare on LinkedInShare on Twitter Share

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)

  • use INSERT INTO … SELECT with UNION ALL

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

Using Table-Valued Parameters in SQL Server Stored Procedures

Share on FacebookShare on LinkedInShare on Twitter Share

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
Posted on

Generating a Comma-Delimited String from Rows in SQL Server

Share on FacebookShare on LinkedInShare on Twitter Share

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 http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation

Posted on

Selecting Rows from Comma-Delimited String in SQL Server

Share on FacebookShare on LinkedInShare on Twitter Share

Recently I had to find a solution for the following task: JQuery UI plugin makes a comma-delimited string of values, C# passes this string to SqlCommand object as a parameter, then calls a stored procedure in SQL Server. The stored procedure must filter rows in a big table by these values. One of the solution is to parse a string of values into a table, then join with a big table.

Quick search with Google helps me to find an appropriate solution in Brad Schulz blog (you can read the full article here).

[UPDATE 2017-07-31]
I’ve found an excellent article written by Erland Sommarskog Arrays and Lists in SQL Server with probably all possible solutions.

Here is an extract of the most important part.

Be careful with varchar(2) in first line. It should correspond with the max length of values in your string. As you see in the screenshot below the last value 100 in the string is truncated to 10 (only 2 chars) in the last row.

Select rows from string - bad truncation

Posted on

SQL Server Data Types: MONEY vs DECIMAL

Share on FacebookShare on LinkedInShare on Twitter Share

I’ve seen some notes that we should avoid using MONEY data types in SQL Server. But why?

At first glance it seems that MONEY data types is similar to NUMERIC/DECIMAL with precision 19 and scale 4. It can be check very easily – just assign maximum value of MONEY data type to decimal variables with different precision.

money is like decimal(19.4)
As you see we could not able to assign such a big value to DECIMAL(18,4).
Great! Let’s go further. The next question that arise – what’s the difference between MONEY and DECIMAL(19,4)? It’s not so obvious to find it. It lies in rounding of arithmetic operations.
money and decimal rounding
The results of division are the same, but the results of division and multiplication are not. I suppose you understand where the rounding occurred.
I think it’s a good advice not to use MONEY data type in SQL Server databases.
Posted on

SSDT Database Unit Test: Cleanup after Test Execution

Share on FacebookShare on LinkedInShare on Twitter Share

In my previous research about database unit testing in SQL Server Data Tools I’ve found that data inserted into tables during test had not been deleted automatically. You should create a cleanup action manually.

  • Choose an item Post-test in 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 delete test data.
Post-test delete

I truncate table Clients and run my test. Test is OK and there’s no test data in Clients table. So I could run my test as many times as needed.

There’s a possibility to include test condition even in post-test phase. Wonderful, I’ll check that test data is deleted successfully.

  • Add simple SELECT command to query rows with test guid.
  • Add test condition to check no data is returned. This can be made with Row Count = 0 condition or Empty ResultSet condition. I choose the second.
The whole post-test action looks like this:
post-test delete with check
Unit test has passed. If you are in doubt about all actions or not were really performed, you can check the output of the test. To see it click the Output link at the bottom of Test Explorer pane.
Test output
Posted on

SSDT Database Unit Test: Checking a Simple INSERT Command

Share on FacebookShare on LinkedInShare on Twitter Share

I have a very simple database for experiments with SQL Server unit tests. ClientsDB database contains just one table Clients with no records:

Clients table

and one stored procedure CreateClients:
CreateClient stored procedure

I have created a database project ClientsDatabase and a SQL Server unit test project ClientsDBTestProject to test my stored procedure. The autogenerated test (slightly formatted for readability) is shown below.

spCreateClientTest

To create a simple test we must do:

  1. change assignments in first SELECT command and set test parameters such as client guid and name;
  2. delete the last SELECT command because the stored procedure does not return any value (there’s no RETURN statement in SP);
  3. insert a SELECT command before EXECUTE command to check that there’s no row with the test guid in Clients table;
  4. add a SELECT command after EXECUTE command to check that row with the test guid has inserted into Clients table;
  5. delete the autogenerated test condition inconclusiveCondition1;
  6. add first test conditions with type Row Count. Set the property Row Count to 0. Also you should check that property ResultSet is 1;
  7. add second test condition with type Row Count. Set the property Row Count to 1 and change ResultSet to 2.

Finally our unit test should look like this:

spCreateClientTest - final

Now we are ready to start our unit test.

  • Open Test Explorer (Test -> Windows -> Test Explorer)
  • Right-click dbo_CreateClientTest, then Run Selected Tests or simply click Run All because we have only one unit test.
  • If test has completed successfully, we see a green mark near the test name.
spCreateClientTest - success
Our test is working, everything seems OK. But…
Check the data in Clients table (thanks to SSDT, it can be made from Visual Studio now, or you can use SQL Server Management Studio).
select from Clients
 And now we see our test record. Is it funny? I think NO. This means that test data is not deleted after the test has been executed therefore the next time we run our test it would fail. Don’t forget about it!
It’s not a tragedy because SSDT has capabilities to solve this issue. I’ll write a post about it later.
Posted on

Simple Way to Create a Database Unit Test Project in SSDT

Share on FacebookShare on LinkedInShare on Twitter Share

Another interesting feature of SQL Server Data Tools (SSDT) is a unit testing. SSDT enables to develop and execute unit tests interactively in Visual Studio. You can write tests for stored procedures, functions and triggers.

To create a simple database unit test project you need Visual Studio + SSDT, of course, and a database project (how to create it I wrote in this post).

  • Open your database project in Visual Studio
  • Find the object to be tested in Solution Explorer
  • Right-click and select Create Unit Tests
Create unit test
  • In Create Unit Tests window check that only one database object is selected (on which you right-clicked earlier).
  • Choose the type of test project (Visual Basic or C#). This does not influence the database tests, it’s just a language of test project. As you see later all the work around the test will be made in the graphical interface of test designer.
  • Type the name of your test project and the name of a test class.
Name unit test project
  • Press OK button.
  • In SQL Server Test Configuration choose an existing connection (I’ve created it earlier during the creation of database project) or create a new connection, then press OK.
  • Visual Studio will create a database test project and unit test. This unit test will be opened automatically in Test Designer.

Unit test