Every .NET Developer Should Know Their Database Well Enough

added by SuprotimAgarwal

8/31/2011 12:34:18 PM

340 Views

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.

Read the full story

7 comments

vijayst

8/31/2011 9:31:49 PM

There are good references to articles involving database fundamentals and TSQL in your article.

If a developer uses an ORM framework like Entity framework, is it still important for the developer to understand the TSQL fundamentals?

dpeterson

9/1/2011 8:55:47 AM

I think that it's even more important to know your database if you're using an ORM rather than datareaders/datatables. Having a good understanding of what's happening to the database when you use your ORM will help you identify performance problems and/or bugs more quickly. Some of those issues are more obvious when using datatables or datareaders as the sql being executed is apparent.

Personally, I don't think there's any excuse for a developer working with databases to not have a solid grasp of SQL as well as intimate knowledge of the database schema they are working with.

vijayst

9/1/2011 9:19:52 AM

Are there any debugging tools provided by the ORM layer to see the SQL query that is generated? From the SQL server, I am aware of the profiler tool that lets us monitor the performance of SQL queries. Can the SQL queries generated by the ORM layer viewed within the Visual Studio environment?

dpeterson

9/1/2011 9:30:23 AM

It depends on the ORM. NHibernate, for instance, logs all of its sql with log4net. So in addition to using the sql server profiler to analyze the queries being sent, you can get a cleaner/friendlier log that way.

It's also possible to use the queries that were logged to do performance tuning on your database, using those queries as the workload file. It's very important to understand the design and purpose of your database when doing any sort of performance tuning, as it's possible the profiler may suggest deleting an index to improve write speeds, even though that index is very important for read performance. The profiler is only as informed about what your database is used for as what is in the workload file, and knows even less about the reasons for where to choose read speed over write speed and vice-versa when it comes to indexes.

SuprotimAgarwal

9/1/2011 10:30:08 AM

vijayst: this link should answer your question

hxxp://www.dotnetcurry.com/ShowArticle.aspx?ID=647

vijayst

9/1/2011 12:36:25 PM

@dpeterson, @SuprotimAgarwal, Thank you for the clarifications. It looks like both NHibernate and Entity Framework has methods to return the SQL query as a string.

bsenoff

9/14/2011 9:08:19 AM

Best of DNK August 2011. Nice job @SuprotimAgarwal