How much should I know as a Database Developer/ BI

TheCount1 · August 21, 2017, 7:29am

Hi, so I’m still fairly new in my career. So far I know the basics of using SQL Server’s SSMS, IS, and RS. Other than going more in depth with these few things, what else should I know if I want to be an expert Database dev/ BI pro?

BGL · August 21, 2017, 11:45am

…where to start

This is a very expansive subject the more you learn the more you will understand you will never know everything regarding database design, BI, and SQL Server. You will likely end up becoming stronger in certain areas through necessity.

The SQL Server community is large, and helpful so you never need to worry about not finding an answer. You can also always find something connected to SQL Server or data/databases in general that has nothing to do with any existing projects to learn about.

For now if you are a dev start learning about good coding practices and how to optimise T-SQL and database design. You can nearly always find a different way to do something e.g. Temp Tables vs. Table Variables.

Also, don’t ignore the basics - learn how to use BCP, SQLCMD and some PowerShell, you won’t always be able to use SSMS/SSIS or the data import wizard etc.

Dynamic_Gravity · August 21, 2017, 2:42pm

How far is too far for data separation when designing a database? Web Development

I’m starting to learn how to design databases, and I’m learning preferred methods for structure. One of the main points is to “Divides your information into subject-based tables to reduce redundant data.” which makes sense to me intuitively. Why would I store something that will be re-used 100 different times like a State abbreviation (i.e. AL for Alaska, or even Alaska as a name) in 100 different fields rather than store it once and just link to that one field? But then I thought “how much is too much?” with that. Say I have an employee table, and I know common Last Names like Smith, Brown, etc would appear more than once. Or a date of hire. How far should that be split out between tables linking to tables? I understand single instance items should probably just go into the table itself, but I also have to be aware of tables that will end up with a lot of blank space because of it. Is there a method for figuring out these types of things beyond manually sifting through the data to discern what needs to be separated and what needs to be amalgamated using logic? Essentially a way to take already stored information that does not follow all these rules and find a database structure that fits all the data and does follow these rules. http://www.studytonight.com/dbms/database-normalization.php

BGL · August 21, 2017, 5:54pm

@Vitalius @TheCount1

I wish these books/web-guides had been available when I first started working with SQL Server many years ago;

http://www.red-gate.com/hub/books/

http://www.sqlservercentral.com/stairway/