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

December 30, 2014July 30, 2017

Selecting Rows from Comma-Delimited String in SQL Server

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.

SELECT x.i.value('(./text())[1]', 'varchar(2)')
FROM (
    SELECT XMLList = CAST('<i>' + REPLACE(@string, ',', '</i><i>') + '</i>' AS XML).query('.')
    ) a
CROSS APPLY XMLList.nodes('i') x(i);

SELECT x.i.value('(./text())[1]', 'varchar(2)')

FROM (

SELECT XMLList = CAST('<i>' + REPLACE(@string, ',', '</i><i>') + '</i>' AS XML).query('.')

) a

CROSS APPLY XMLList.nodes('i') x(i);

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.

December 30, 2014September 2, 2016

SQL Server Data Types: MONEY vs DECIMAL

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.

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.

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.

December 30, 2014July 14, 2015

SSDT Database Unit Test: Cleanup after Test Execution

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.

Click the link Click here to create in the center of Test Designer.
Write T-SQL code to delete test data.

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:

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.

December 30, 2014July 14, 2015

SSDT Database Unit Test: Checking a Simple INSERT Command

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

and one stored procedure CreateClients:

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.

To create a simple test we must do:

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

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.

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

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.

December 30, 2014July 14, 2015

Simple Way to Create a Database Unit Test Project in SSDT

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

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.

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.

December 30, 2014July 14, 2015

Create a New Database Project in SSDT from Existing Database

One of the very useful features in SQL Server Data Tools (SSDT) is a possibility of offline database projects. If you have an existing database in SQL Server it’s quite easy to create a database project from it.

In Visual Studio click File -> New -> Project
In New Project window select SQL Server template, select SQL Server Database Project and enter the desired name for you project, then press OK

In Soltion Explorer right-click you project, select Import in context menu, then Database.
In Import Database page press New Connection button (if you had not created any connection earlier).

In Connection Properties page enter the Server name and select a database name in dropdown list.

In Import Database page press Start button. Visual Studio will create the definitions of your database objects in database project.
After all is done just press Finish button. Now let’s go to the Solution Explorer and examine the code.

December 30, 2014July 14, 2015

Installation of SQL Server Data Tools

SQL Server 2005-2008R2 was shipped with a Business Intelligence Development Studio. This is a Visual Studio shell with special templates/add-ons/plugins, etc. that are needed for development of BI solutions in SQL Server (analysis, reporting and integration services). SQL Server 2012 has introduced two new tools that are build on Visual Studio. They are SQL Server Data Tools (SSDT) and SQL Server Data Tools – Business Intelligence (SSDT-BI). The latter is a successor of BIDS.

As stated in MSDN, Microsoft SQL Server Data Tools provides an integrated environment for database developers to carry out all their database design work for any SQL Server platform (both on and off premise) within Visual Studio. Database developers can use the SQL Server Object Explorer in VS to easily create or edit database objects and data, or execute queries. Developers will also appreciate the familiar VS tools we bring to database development, specifically; code navigation, IntelliSense, language support that parallels what is available for C# and VB, platform-specific validation, debugging and declarative editing in the TSQL Editor, as well as a visual Table Designer for both database projects and online database instances.To install the latest and up-to-date versions of software you need:

Visual Studio 2013 Professional Edition or higher. You can select only SSDT and unselect the other options.

Install the latest update via Visual Studio update channel (Tools –> Extensions and Updates –> Updates).