适用于NET开发人员25个实用的SQL Server教程

     <p align="justify"><a href="/misc/goto?guid=4958185059737346274" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server Tutorials on Date Time</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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. </span></p>    <p align="justify"><a href="/misc/goto?guid=4958185060476182301" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">Troubleshoot Deadlocks using SQL Server Profiler 2005/2008</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - In this article, we will see how to capture deadlocks while modifying data using SQL Server Profiler in SQL Server 2005/2008 </span></p>    <p align="justify"><a href="/misc/goto?guid=4958185061204630628" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Queries – beyond TRUE and FALSE</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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? </span></p>    <p align="justify"><a href="/misc/goto?guid=4958185061940319055"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Search Similar String in a Table</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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. </span></p>    <p align="justify"><a href="/misc/goto?guid=4958185062678171940" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Calculate Summary and Column Summary</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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. </span></p>    <p align="justify"><a href="/misc/goto?guid=4958185063410807482" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Common mistake while Calculating Quarter Sales</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - When it comes to calculating quarter sales, I have always seen developers grouping only by quarter which is incorrect.</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185064147317144" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">Load Comma Delimited file (csv) in SQL Server</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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. </span></p>    <p align="justify"><a href="/misc/goto?guid=4958185064887667899" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Export Table to CSV</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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. </span></p>    <p align="justify"><a href="/misc/goto?guid=4958185065625672906" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Insert Date and Time in Separate Columns</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185066365051818" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Distinct Count across Multiple Tables</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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.</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185067097867299" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Highest and Lowest Values in a Row</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - Calculate both the highest and lowest values in a row without using an UNPIVOT operator.</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185067842207275" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">Fastest Way to Update Rows in a Large Table in SQL Server</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185068579187290" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server–Error Handling using Try Catch Block</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185069319595286" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: DateTime vs DateTime2</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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.</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185070042237109" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">XML Basics in SQL Server 2005 and 2008</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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.</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185070780228466" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Combine Multiple Rows Into One Column with CSV output</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - In response to one of my posts on </span><span style="font-family:Verdana;font-size:x-small;">Combining Multiple Rows Into One Row</span><span style="font-family:Verdana;font-size:x-small;">, 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.</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185071716688518" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Count based on Condition</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185072470472143" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server: Convert to DateTime from other Datatypes</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - In this post, we will see how to convert data of different datatypes to a DateTime datatype, in SQL Server.</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185073412003803" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">Rollback Transaction in SQL Server</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - In this article, I have explained how to use a Try..Catch block to commit and rollback transaction</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185074166736090" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">Rollback Nested Transactions in Stored Procedure - SQL Server</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - In this article, we will use an example to see how to rollback <em>nested</em> transactions in Stored Procedures</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185074906060491" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">Concatenate Strings in SQL Server - Different ways</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - There are many ways to concatenate data in a single column. This post shows some of these ways.</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185075651625366" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">SQL Server CLR User Defined Function using Visual Studio 2010</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - In this post, we will see how to create a User Defined Function using Visual Studio 2010</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185076384158718" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">Check if Database Exists In SQL Server – Different ways</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - A very frequently asked question is how to to check if a Database exists in SQL Server. Here are some different ways.</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185077138763737" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">Find the Most Used Stored Procedures in SQL Server</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - Let us see how to use the <em>sys.dm_exec_query_stats</em> DMV to return the 3 most used stored procedures in your SQL Server database</span></p>    <p align="justify"><a href="/misc/goto?guid=4958185077881484833" target="_blank"><strong><span style="font-family:Verdana;font-size:x-small;">Find the Nth Maximum and Minimum Value in a Column</span></strong></a><span style="font-family:Verdana;font-size:x-small;"> - 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.</span></p>