Every .NET Developer Should Know About the Database they are working with

I am a strong believer that a Developer should understand the databases and network he/she is interacting with. As a .NET Developer, having TSQL and SQL Server Administration knowledge to some degree of depth, really helps to design and develop your applications as well as communicate with the DBAs and admins you work with. A lot of us have testing and staging environments and being able to manage those environments, saves the organization on some resources.

Also with the economies changing rapidly, many developer positions require you to have knowledge about querying and managing databases. Flexibility and the ability to learn and understand the application and it’s components differentiates a good developer from the others and keeps you ahead in the race. Although BI is an essential part of an application, in this article, I will focus on the administration of a database.

In this article, I have attempted to list some articles we have written in the past that will give you information about handling a SQL Server 2005/2008 database. Hope you like them!

Please retweet and share this post with fellow developers. Thanks!

Identifying SQL Server Bottlenecks and Performance Issues

SQL Server: Identify Memory and Performance Issues in T-SQL Queries and Fix them - Sometimes you may notice that some T-SQL queries are taking too much time to execute and thus slowing down the performance of SQL Server and other applications. You can find and rectify these queries using the following methods shown in this article

Find the Most Time Consuming Code in your SQL Server Database - This post will demonstrate how to find T-SQL code (SQL Server 2005/2008) that takes the most time to execute.

Monitor Running Processes in SQL Server 2005/2008 - You often need to monitor the processes running on the server in order to improve the performance, by tuning them. A common option is to make use of a profiler. But in SQL Server versions starting 2005, we can also use dynamic management views.

SQL Server: Monitor Long SQL Agent Jobs - In SQL Server, there are various jobs running on the server, each solving a different purpose. Sometimes these jobs run for a long time. The best way to identify and monitor these long running jobs is to make use of a profiler.

Important DMVs to monitor CPU – SQL Server - Dynamic Management Views (DMVs) can be very useful to diagnose and troubleshoot common performance problems in SQL Server. Here are some important Dynamic Management Views (DMV’s) to monitor the CPU where SQL Server is installed.

Monitor Activities on your SQL Server 2005/2008 - Have you heard about or used SQL Server MVP Adam Mechanics Who is Active This is a must have script for DBA’s and if you have been using sp_who or sp_who2, then you will love it.

List of Database Engine Error Messages - All system and user-defined error messages in an instance of the Database Engine are contained in the sys.messages catalog view. Here’s how to query this catalog view to list the Error Messages based on their severity

Moving Data in SQL Server

Move Data to a Different Table using OUTPUT clause – Shows how to move some data from one table to another table and then delete the data from source table.

SQL Server: Transfer Objects From One Schema to Another - This post describes how to transfer objects from one schema to another

Transfer Logins from SQL Server 2005 to SQL Server 2008 - When you want to move a database from one server to another, you can take a backup of the database and restore into the other server. However this will not transfers the logins from one SQL server to another, say SQL Server 2005 to SQL Server 2008. This post shows the right way to do it.

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.

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.

SQL Server Migration Assistant v5.0 - Microsoft recently announced the release of SQL Server Migration Assistant (SSMA) v5.0, a tool to further simplify the user experience in automating the migration of Oracle, Sybase, MySQL and Microsoft Access databases to SQL server or SQL Azure

SQL Server: Move Table to a new File Group - If a database is expected to grow rapidly, it becomes challenging to maintain the data files in the same location/drive. To manage disk space well, it one solution to this problem is to identify large tables and accordingly move new data to a new File group, which can be mapped to a different drive path. This article shows how to move a SQL Server Table from one File group to another.

Know your SQL Server Objects and Manipulate them

SQL Server Management Objects 2008 (SMO) New Features - In this article we will practically explore some features of SQL Server Management Objects.

Filtered Index Vs Indexed Views in SQL Server 2008 - In this article I have listed some differences between Filtered Index and Indexed View that will help understand the differences between the two.