I feel really fortunate that I am part of the SQL Server community because there are so many great people you can talk with and who are willing to share their knowledge for free. What you are going to read below is a short interview with Kris Wenzel who shares his advises on how to become a database developer. Kris has quite some experience in this field and definitely has things to share! Enjoy:
- Kris, how did you get started with databases? – My first exposure to SQL was in 1985 when I took a database theory class in college. The class studied the theoretical side of SQL, but it was enough at the time for me to see the power of databases. And I had to have Data Center Decommissioning | OceanTech help me. I totally liked the idea that my programs could have a database do the dirty work of searching and sorting data for me!My career took me in a different direction for a couple of years, and it wasn’t until 1998 that I got to work with SQL Server 6.5. By then I was doing development work in Visual BASIC. Along the way I did my own DBA work, so over the years I’ve come to appreciate SQL Server from both a DBA and developer’s perspective. Now when I interact with SQL it is either via C# or Excel 2013 PowerPivot.
- Why do you think is important for developers to know database concepts and SQL? – It is important for developers to know SQL so that they can understand how to create better interactions between their programs and the database, two factors come to mind: Code Location and Network. When writing program logic it is important to understand when codeshould be handled within the application or in the database. Personally, my rule of thumb is to try to keep data processing as close to the database as possible. So, if I’m working on something that will iterate through thousands of records, I ask myself two questions:
– Is it better to execute one query that pulls back a thousand records or to execute one query a thousand times?
– Would it make more sense to do the processing on the Server instead of within the application?
For instance, if I need to calculate the average age of currently enrolled college students I would elect to write one SQL statement that uses the AVERAGE function as opposed to pulling back all the student’s ages to the application, to count, sum, and ultimately average their ages. It is also important to understand that there is usually a network between your code and the database. This could be a high-speed network in a data center, or a much slower WAN (Wide Area Network) across an enterprise. When considering the network there are a lot of the challenges revolve around latency and network traffic. Executing the query once is like speaking a couple of sentences and then having the listener acknowledge the get the idea. Can you imagine if they had to acknowledge they “got” every word as it was spoken?By bundling requests into a single command I’ve seen execution times reduce by a factor of ten!
- What is the best way for a .Net developer to access data in SQL Server? – There are several modern ways to access SQL Server from the .Net framework, such as LINQ, Entity Framework, and SQLConnection. My opinion is to start out with the basics, which means using SQLConnection. The nice thing is once you’re familiar with SQLConnection, the same concepts can translate to other SQL DBMS’s such as MySQL or Oracle since they all have own connection classes which such as MySQLConnection which mirror SQLConnection. SQLConnection is used in conjunction with SQLCommand and SQLDataReader. In its simplest use, SQLConnection is used to establish a connection to the database, then SQLCommand is used to pass a SQL statement to the SQLDataReader, which then executes the command. The site dotnetperls.com, has a really good code example you can follow.
- What are the things that each developer needs to consider when designing a database schema? – I think one of the most important concepts the developer can learn is normalisation. Normalisation can be your friend when it comes to avoiding update anomalies or data inconsistencies, but can also be a foe when it comes to pulling data back for presentation. If you follow normalisation by the book, your database can become too complex and become very hard to understand and maintain. Pulling the database back can be a challenge! It can involve many joins that not only add complexity to your code, but also place a drain on database resources. I think it’s important to strike a balance so that you still benefit from having consistent information yet having it be easy to query. I’ve seen hybrid models, where transaction data is normalised, yet highly visible on screen reports, such as a home page task list, are denormalized copies of the data. This certainly brings more moving parts to the application, but does place a priority on keeping data consistent while considering application performance in key areas. We definitely need to also include indexes in this conversation. It goes without saying that you need to have indexes on primary and foreign keys. Yet you need to look at all the queries that are run and understand whether the database can benefit indexes. Unfortunately, the answer doesn’t reveal itself until you database increases in size. That is why it is really important to load test. Only once you have loaded up the database, and I mean really loaded it up with data, will you realise whether performance suffers. Once you do see a slow down, you can use the query plans to understand what are the slowest running queries. I think it’s important to understand that database performance can be drastically improved by just adding one index. I recently experienced issue where a process was taking about 20 hours to run. We looked at the queries and realised that we were missing an index that was later on added and the process took only four hours after that.
- Where can a developer start learning about SQL Server? – If you looking to get the basics of SQL Server and SQL queries under your belt I would recommend visiting essentialSQL.com. Be sure to sign up for the newsletter. It will take you through some free training. By the time you go through the sequence you’ll have a good understanding of SQL Queries and key database concepts.
One thing that stands the site up from others is that I’m also going to some of the practical aspects of databases such as normalisation, data modelling, how database management system operates, but also some of the underpinning ideas that make it tick.We tackle some tough issues, and from the feedback I’ve received, we are accomplishing what we set out to do. That is, to teach people to use SQL in simple English.
Kris Wenzel’s Bio
Kris is a graduate in Computer Engineering from the University of Michigan. Today, Kris run essentialSQL.com. A web site devoted to teaching beginners SQL and SQL Server. Kris has over twenty years of development and DBA experience. He has worked on a wide variety of databases include, MySQL, Oracle, SQLite, and SQL Server.