25 Useful SQL Server Tutorials For .NET Developers

As a developer who has been developing data oriented .NET applications for over a decade now, I have become a strong believer of the fact that a developer’s knowledge is incomplete, without having knowledge of the database and network he/she is interacting with. A couple of my colleagues and I run a site called www.sqlservercurry.com where we record our experiences with the databases, and share it with fellow devs in the form of blog posts. In this article, I will be sharing 25 T-SQL Scripts and Tutorials from our blog that I feel would be useful if you are developing .NET Centric Database Solutions.

Please retweet and share this list with other fellow developers. Thanks!

Here’s the list for your reference:

SQL Server Tutorials on Date Time - One of the most frequently asked question by SQL developers, is on handing Date, Time and other related datetime values in SQL Server. Developers are always looking out for solutions which demand either converting Date Time Values or finding date time ranges and so on. Madhivanan and I have already written a couple of articles on handling DateTime in SQL Server. In this post I will share some of the links with you.

Troubleshoot Deadlocks using SQL Server Profiler 2005/2008 - In this article, we will see how to capture deadlocks while modifying data using SQL Server Profiler in SQL Server 2005/2008

SQL Queries – beyond TRUE and FALSE - Most of the SQL novices are more accustomed to thinking in terms of two-valued logic (TRUE, FALSE) in SQL. But SQL uses three-valued logic – TRUE, FALSE and UNKNOWN. It means that the value of an expression may be TRUE, FALSE or UNKNOWN. Confused?

SQL Server: Search Similar String in a Table - There are may ways to look for similar strings in a SQL Server column. The most common method is to make use of LIKE operator. Let us see the different ways to look for similar string in a table.

SQL Server: Calculate Summary and Column Summary - This post shows how to show a summary of similar rows as well as column summary for SQL Server table data. Suppose you want to generate the total of a column and display it at the end of the result set.

SQL Server: Common mistake while Calculating Quarter Sales - When it comes to calculating quarter sales, I have always seen developers grouping only by quarter which is incorrect.

Load Comma Delimited file (csv) in SQL Server - We often need to import data that comes from different data sources, into a database like SQL Server. Usually data comes in the form of a comma delimited file aka CSV file. These are the two easy ways to import data from a CSV file into a table of a SQL Server Database – Using Bulk Insert and Using SQL Server Management Studio.

SQL Server: Export Table to CSV - Exporting data from SQL Server to a .csv file is often needed to use that data into a different system. There are two easy ways to do this – using BCP and using SQL Server Management Studio.

SQL Server: Insert Date and Time in Separate Columns - If there is a need to store date and times values in separate columns, you can store Date values in the Datetime column and Time values in either the char datatype or the time datatype (Sql Server 2008), as shown in this post

SQL Server: Distinct Count across Multiple Tables - One of my clients had a SQL Server database with an email column repeated across multiple tables. He wanted two queries – all email’s that are not repeated across the table, i.e. distinct ones as well as a count of the email addresses across multiple tables in the database.

SQL Server: Highest and Lowest Values in a Row - Calculate both the highest and lowest values in a row without using an UNPIVOT operator.

Fastest Way to Update Rows in a Large Table in SQL Server - Many a times, you come across a requirement to update a large table in SQL Server that has millions of rows (say more than 5 millions) in it. In this article I will demonstrate a fast way to update rows in a large table

SQL Server–Error Handling using Try Catch Block - In this post, we will see how to handle errors in SQL Server 2005 and 2008. In SQL Server 2005, Microsoft has introduced a new construct to handle errors in SQL Server that is ‘TRY – CATCH’. It is similar to .NET ‘Try – Catch’ block for handling the exceptions

SQL Server: DateTime vs DateTime2 - The datetime2 datatype was introduced in SQL Server 2008 along with the date and time datatypes. Unlike the datetime datatype in SQL Server, the datetime2 datatype can store time value down to microseconds and avoids the 3/1000 second rounding issue.

XML Basics in SQL Server 2005 and 2008 - In this article, we will see how to write basic queries to insert, query and generate XML data in SQL Server 2005/2008. For this demonstration, I am using the ‘Northwind’ database.

SQL Server: Combine Multiple Rows Into One Column with CSV output - In response to one of my posts on Combining Multiple Rows Into One Row, SQLServerCurry.com reader “Pramod Kasi” asked a question – How to Combine Multiple Rows Into One Column with CSV (Comma Separated) output. This post explains how to do so.

SQL Server: Count based on Condition - Many a times, you may want to do a count in SQL Server, based on a condition. The easiest way is to use a combination of SUM and CASE as shown in this article

SQL Server: Convert to DateTime from other Datatypes - In this post, we will see how to convert data of different datatypes to a DateTime datatype, in SQL Server.

Rollback Transaction in SQL Server - In this article, I have explained how to use a Try..Catch block to commit and rollback transaction

Rollback Nested Transactions in Stored Procedure - SQL Server - In this article, we will use an example to see how to rollback nested transactions in Stored Procedures

Concatenate Strings in SQL Server - Different ways - There are many ways to concatenate data in a single column. This post shows some of these ways.

SQL Server CLR User Defined Function using Visual Studio 2010 - In this post, we will see how to create a User Defined Function using Visual Studio 2010

Check if Database Exists In SQL Server – Different ways - A very frequently asked question is how to to check if a Database exists in SQL Server. Here are some different ways.

Find the Most Used Stored Procedures in SQL Server - Let us see how to use the sys.dm_exec_query_stats DMV to return the 3 most used stored procedures in your SQL Server database

Find the Nth Maximum and Minimum Value in a Column - Here’s a simple query to find the Nth Maximum and Minimum values in a SQL Server table column using the Row_Number() function. We will find the 3rd highest and 3rd lowest values in the column.

If you are interested in learning more, please visit www.sqlservercurry.com

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigious Microsoft MVP award for Sixteen consecutive years. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.

Get in touch with him on Twitter @suprotimagarwal or at LinkedIn