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.

View Index MetaData – SQL Server - The sp_helpindex stored procedure gives useful information like name, description and key about the indexes on a table or view. However for retrieving detailed information, you should make use of the sys.indexes object catalog view.

Change an Existing Index in SQL Server - With SQL Server, you can use the DROP_EXISTING clause to rebuild the index, add or drop columns, modify options, modify column sort order, or change the partition scheme or filegroup. Let us see how to rebuild the index and then add a column to an existing index

Find the Most Used Stored Procedures in SQL Server – This post uses the sys.dm_exec_query_stats DMV to return the 3 most used stored procedures in your SQL Server database

Useful SQL Server System Stored Procedures You Should Know - System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Here’s a bunch of System Stored Procedures that are used on a frequent basis (in no particular order).

Find the Missing Identity Numbers in SQL Server 2005/2008 - We as Database Developers often come across tables that have identity columns. Many a times, there are missing rows in that table, which have probably been deleted during a database operation. Have you wondered how to find the missing rows?

Find Out The Parameters Of A Stored Procedure in SQL Server 2005/2008 - Recently while working on an application that heavily uses Stored Procedures, I had to often open up SQL Server Management Studio/ Visual Studio Server Explorer to physically check the parameters of a Stored Procedure. This became cumbersome at times since the procs were in different databases and I wish there were a query that returned me the Stored Procedure parameters, as soon as I supply it with the stored procedure name. If you have been looking out for something similar, here’s what I came up with

Identify Tables that have Clustered index but no NonClustered indexes - I was recently helping a client determine tables in his SQL Server 2005/2008 which had Clustered Index but did not have any nonclustered index on them


SQL Server: Disable Table Constraints (all or some) - Constraints let you define a way to automatically enforce the integrity of a database. A table constraint is declared independently from a column and can be applied to more than one column in a table. Sometimes you may need to disable one or all table constraints, in order to import data, truncate tables etc.

SQL Server: Count Rows in Tables and its Size - In this post we will see how to count rows in all the tables of a database using SQL Server.

View Object Dependencies in SQL Server 2008 - Viewing object dependencies within a database as well as between databases and servers has become easier in SQL Server 2008. SQL Server 2008 introduces a catalog view (sys.sql_expression_dependencies) and dynamic management functions (sys.dm_sql_referenced_entities & sys.dm_sql_referencing_entities) that can help in dependency
tracking.

Rename a SQL Server database - Sometimes we may need to rename a SQL Server database for testing purposes or when a testing database goes online, we may need to rename it on a production database. This article shows how.

SQL CLR Stored Procedure using Visual Studio 2010 - In this post, we will see how to use SQL CLR functionality for defining Stored Procedure using C# in VS 2010. We will also see how to enable CLR under SQL Server.

List all Default Values in a SQL Server Database - I had earlier written a query to Find a Column Default value using T-SQL. Here’s how to find the default value of all columns in all tables of a database

Find Column Default Value using T-SQL – SQL Server - Here’s how to find the default value of a Column using T-SQL.

Find Unused Objects in your SQL Server Database - Here are a couple of options that you can use to find unused objects in your database

SQL Server: Calculate tempdb Space using DMV - In this article, we will see how to calculate total and free space in tempdb using the sys.dm_db_file_space_usage DMV, which returns space usage information for each file in the database.

List of Undocumented Stored Procedures in SQL Server - I have been searching for a list of Undocumented Stored Procedures in SQL Server for quite a while. Here is a list collected from forums and other sites for your reference.

List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database - While programming a Data Layer, I often refer to the database and its tables for the datatypes and its sizes. It’s real handy to be able to print a list of the properties of the columns. Here’s a query that will help you fetch the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database

Display List of All Databases with their Sizes in SQL Server 2005/2008 - Here’s a query that lists the properties of a database using the sys.master_files and sys.databases, both System Views for retrieving Database information.

Find Out Tables in a Database Having Identity Columns - The simplest way I use to find out the tables in SQL Server 2005/2008 having Identity Columns is to use the query over here


About The 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 a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

6 comments:

Raghav Khunger said...

I agree with same. Good information Suprotim.

silence said...

I agree. Great post.

kjmclark said...

Oracle?

CoreAn_Crack3rZ said...

Useful information! But I'm bound to MSAccess only... :(

link pyramid said...

Excellent post. I also share the same opinion that .NET developer should have a certain degree of knowledge about the database that they are working on. Being a .NET developer myself, i find this post very inspiration.

Kiquenet said...

any updates in 2017 ? best practices NET and SQL Server ?