Converting List To DataTable

In my previous post SqlBulkCopy I wrote about how to load data from .NET code into SQL Server database using efficient BULK INSERT command. This class needs a DataTable or IDataReader instance as a source. I’ve collected a couple of examples how to convert List to DataTable.

1. Variation of Marc Gravell solution.
Source: https://stackoverflow.com/a/14548027

2. Solution created by Jennifer Hubbard, Bill Wagner, etc.
Source: How to: Implement CopyToDataTable<T> Where the Generic Type T Is Not a DataRow

SqlBulkCopy

SQL Server have great commands like BULK INSERT or OPENROWSET(BULK…) to insert a huge amount of data into a database. .NET Framework has a class SqlBulkCopy that provides similar capabilities in C#. Full documentation is available on MSDN article SqlBulkCopy Class. But SqlBulkCopy has some restrictions on input data. It can read from:

  • DataRow[] array;
  • DataTable;
  • IDataReader.

A simple example of usage can be as follows:

SQL Server Profiler shows that the correct command was really executed.

In order to use SqlBulkCopy class elegantly, it would better to have a method or extension to convert a generic list to DataTable or IDataReader. But it’s a topic for my next post.

Calculating Size Of A Database Table

If you want to get a statistics on a number of database pages used by a specific table in SQL Server, you can query DMV sys.dm_db_partition_stats.

The following query will show you the number of pages per table and each heap/clustered/nonclustered index.

Updating XML Data In SQL Server

Inspired by this excellent article Examples of using XQuery to update XML Data in SQL Server, I wrote a quick memo how to update XML data in SQL Server.

1. modify command can update only the first occurrence it finds. The command looks as following.

2. If you need to update all occurrences, make a loop to iterate all of them.

3. You can substitute a hard-coded path with a variable, but only for the last tag.

Source code for this example update_xml.zip

Offline Installer For Visual Studio 2017

Microsoft does not offer an ISO image for Visual Studio 2017, we need to make it ourselves by running web installer with some command-line arguments. Here are the links to official pages:

Create an offline installer for Visual Studio 2017

Visual Studio 2017 workload and component IDs

Example to grab community edition:

Layout parameter is mandatory, it points to a folder where the downloaded files would be saved.
Workload_id_list is a list of component IDs, separated by space. If you skip it, all components will be downloaded.

[UPDATE 2017-06-21]
Workloads contain only the recommended components. As mentioned by geoyar, you need to include optional components manually.

Component ID Description
Visual Studio core editor (included with Visual Studio Community 2017) Microsoft.VisualStudio.Workload.CoreEditor The Visual Studio core shell experience, including syntax-aware code editing, source code control and work item management.
Azure development Microsoft.VisualStudio.Workload.Azure Azure SDK, tools, and projects for developing cloud apps and creating resources.
Data storage and processing Microsoft.VisualStudio.Workload.Data Connect, develop and test data solutions using SQL Server, Azure Data Lake, Hadoop or Azure ML.
.NET desktop development Microsoft.VisualStudio.Workload.ManagedDesktop Build WPF, Windows Forms and console applications using the .NET Framework.
Game development with Unity Microsoft.VisualStudio.Workload.ManagedGame Create 2D and 3D games with Unity, a powerful cross-platform development environment.
Linux development with C++ Microsoft.VisualStudio.Workload.NativeCrossPlat Create and debug applications running in a Linux environment.
Desktop development with C++ Microsoft.VisualStudio.Workload.NativeDesktop Build classic Windows-based applications using the power of the Visual C++ toolset, ATL, and optional features like MFC and C++/CLI.
Game development with C++ Microsoft.VisualStudio.Workload.NativeGame Use the full power of C++ to build professional games powered by DirectX, Unreal, or Cocos2d.
Mobile development with C++ Microsoft.VisualStudio.Workload.NativeMobile Build cross-platform applications for iOS, Android or Windows using C++.
.NET Core cross-platform development Microsoft.VisualStudio.Workload.NetCoreTools Build cross-platform applications using .NET Core, ASP.NET Core, HTML, JavaScript, and CSS
Mobile development with .NET Microsoft.VisualStudio.Workload.NetCrossPlat Build cross-platform applications for iOS, Android or Windows using Xamarin.
ASP.NET and web development Microsoft.VisualStudio.Workload.NetWeb Build web applications using ASP.NET, ASP.NET Core, HTML, JavaScript, and CSS.
Node.js development Microsoft.VisualStudio.Workload.Node Build scalable network applications using Node.js, an asynchronous event-driven JavaScript runtime.
Office/SharePoint development Microsoft.VisualStudio.Workload.Office Create Office and SharePoint add-ins, SharePoint solutions, and VSTO add-ins using C#, VB, and JavaScript.
Universal Windows Platform development Microsoft.VisualStudio.Workload.Universal Create applications for the Universal Windows Platform with C#, VB, JavaScript, or optionally C++.
Visual Studio extension development Microsoft.VisualStudio.Workload.VisualStudioExtension Create add-ons and extensions for Visual Studio, including new commands, code analyzers and tool windows.
Mobile development with JavaScript Microsoft.VisualStudio.Workload.WebCrossPlat Build Android, iOS and UWP apps using Tools for Apache Cordova.
Unaffiliated components Component.GitHub.VisualStudio GitHub extension for Visual Studio
Unaffiliated components Microsoft.Component.Blend.SDK.WPF Blend for Visual Studio SDK for .NET
Unaffiliated components Microsoft.Component.HelpViewer Help Viewer
Unaffiliated components Microsoft.Net.Component.3.5.DeveloperTools .NET Framework 3.5 development tools
Unaffiliated components Microsoft.VisualStudio.Component.DependencyValidation.Community Dependency Validation
Unaffiliated components Microsoft.VisualStudio.Component.LinqToSql LINQ to SQL tools
Unaffiliated components Microsoft.VisualStudio.Component.TestTools.Core Testing tools core features
Unaffiliated components Microsoft.VisualStudio.Component.TypeScript.2.0 TypeScript 2.0 SDK
vs_community__1238641179.1486458197.exe –layout C:\VS2017 –lang en-US –add Microsoft.VisualStudio.Workload.CoreEditor Microsoft.VisualStudio.Workload.Azure Microsoft.VisualStudio.Workload.Data Microsoft.VisualStudio.Workload.ManagedDesktop Microsoft.VisualStudio.Workload.NetCoreTools Microsoft.VisualStudio.Workload.NetWeb Microsoft.VisualStudio.Workload.Node Microsoft.VisualStudio.Workload.Office Microsoft.VisualStudio.Workload.VisualStudioExtension Component.GitHub.VisualStudio Microsoft.Component.Blend.SDK.WPF Microsoft.Component.HelpViewer Microsoft.Net.Component.3.5.DeveloperTools Microsoft.VisualStudio.Component.DependencyValidation.Community Microsoft.VisualStudio.Component.LinqToSql Microsoft.VisualStudio.Component.TestTools.Core Microsoft.VisualStudio.Component.TypeScript.2.0

OBJECTPROPERTY Function In SQL Server

SQL Server has an excellent metadata function OBJECTPROPERTY() that allows to view a lot of properties of database objects. For example, if table has a trigger, clustered key, check constraint or default. The full list is available on MSDN article OBJECTPROPERTY (Transact-SQL).

For example, if you want to list all tables that has no primary key, you can do this with a simple query:

Set Up NAT In Windows 10 Hyper-V

Windows 10 has a feature that allows to create NAT network in Hyper-V and put virtual machines behind the NAT. The steps to implement this are based on the Set up a NAT network article, I’ve reorganized it for simplicity.

All the steps must be performed from PowerShell console being run as Administrator.

1. Create Virtual Switch

My virtual switch will be named NAT, you will see it in Hyper-V manager. If everything is OK, you’ll see the following:

2. Find the interface index

Remember the ifIndex of a newly created virtual switch (named NAT).

3. Configure the NAT gateway

I will use subnet 172.16.0.x with mask 255.255.255.0. In this command you specify the IP address of the NAT default gateway 172.16.0.1 with 1 in the last position.

4. Create NAT network

In this command you specify subnet 172.16.0.0 with 0 in the last position.

 

Now you can check virtual switch. When you open Control Panel -> Network and Internet -> Network Connections, you will see adapter “vEthernet (NAT)”. It must be set to IP address 172.16.0.1 and subnet mask 255.255.255.0.

Inside virtual machine you should set up TCP/IPv4 properties for network adapter manually like this:

IP Address 172.16.0.2 or any other IP up to 172.16.0.254
Subnet mask 255.255.255.0
Default gateway 172.16.0.1
DNS server Place IP address of DNS server that is used on host network adapter, i.e. 192.168.1.1 or 8.8.8.8

SQL Server Service Accounts

When you install SQL Server, you need to choose accounts for SQL Server services. Here are some usefull links that can help you to make a correct decision:

As overall installation manual you can use Jonathan Kehayias’ guide described in his post SQL Server Installation Checklist.

Pagination And Total Number Of Rows From One SELECT

If you have a business requirement to implement a pagination in user interface, you could use OFFSET/FETCH or ROW_NUMBER() to retrieve rows from SQL Server. But when you need to show the total number of rows that can be retrieved from database, it becomes harder.

You can use two queries – one for counting the total number, second for extracting the page, but this is not a good idea. Why? First, you’ll have negative impact on performance because you need 2 queries, second, you can get different results cause data could change between these two queries.

Luckily, there are some solutions to do this in one query. Because SQL Server could not return rows and assign a value in one query, we need to put the total number as an additional column into a resultset.

1. Using COUNT(*) OVER()

It’s a very simple query to write. As for performance, I’ll compare all solutions later.

2. Using CTE to count all records

3. Extracting keys to temporary table

The idea is to extract primary keys to temporary table or CTE, get a page of rows, then join it with the original table. We need to use ROW_NUMBER() to assign ordinal number to every row in order to get a page. There are 2 ways to implement pagination.

3a. Extracting keys and filtering row numbers in WHERE

3b. Extracting keys and using OFFSET/FETCH

Now I’ll compare these queries for IO statistics and query optimizer costs.

1. Using COUNT(*) OVER() 2. Using CTE to count all records 3a. Extracting keys and filtering row numbers in WHERE 3b. Extracting keys and using OFFSET/FETCH
Select all rows IO statistics Table ‘Worktable’. Scan count 3, logical reads 4728… Table ‘sysschobjs’. Scan count 1, logical reads 25 Table ‘sysschobjs’. Scan count 2, logical reads 26 Table ‘sysschobjs’. Scan count 2, logical reads 46 Table ‘sysschobjs’. Scan count 2, logical reads 138
Query cost 15% 11% 25% 50%
Select with WHERE clause IO statistics Table ‘Worktable’. Scan count 3, logical reads 169… Table ‘sysschobjs’. Scan count 1, logical reads 4 Table ‘sysschobjs’. Scan count 2, logical reads 8 Table ‘sysschobjs’. Scan count 2, logical reads 28 Table ‘Worktable’. Scan count 0, logical reads 0… Table ‘Workfile’. Scan count 0, logical reads 0… Table ‘sysschobjs’. Scan count 3, logical reads 31
Query cost 4% 6% 35% 55%

From my viewpoint, the second solution is the best cause it needs the least number of pages to be read and it does not create a table in TempDB.

Monitoring SQL Server Backups With DMV

I have a rather long-running backup that was started via Maintenance plan. So I have no output about the progress of a particular backup. In this case you can query DMV to get the information needed.

The output would be like this.

backup-progress-with-dmv

As you see, one backup is running while the others are pending.