Most upvoted comment

Top rated programming books on Reddit rank no. 11

I just received an ultimatum, improve my SQL server skill set or I’ll be fired. Need help.(r/learnprogramming)

Hey I’m a MSSQL database developer/ETL engineer and a T-SQL programmer. I can definitely point you to some good resources. In general, technet & BOL is an awesome resource. Say what you want about Microsoft, but generally development library/documentation are really, really good.

• For variables, you should have a good idea of all the datatypes that SQL Server supports. The datatypes that I wouldn’t worry about right now (or never have to worry about) are spatial, hierarchyid, sql_variant, and timestamp. The table type is used for passing in table valued parameters into stored procedures.

• Table variables are just tables stored in memory. Note that this isn’t the same as in-memory tables (which SQL Server doesn’t support until very, very recently so nobody expects you to know this but you can look up Hekaton). So don’t assume that these will be necessarily faster than physical tables. The big question is typically when would you use a temp table vs a table variable. I would read this stackexchange answer as well as all the linked articles. Once you get the hang of it table variables, you can look into using table valued parameters which are tables that are passed into stored procedures. I would definitely read the quintessential article by Erland Sommarskog on passing “arrays” into sprocs. There’s some sample C#/VB code as well. (In fact all of Erland’s articles are pure gold, so read up as much as you can).

• As for “obscure commands and functions”, I would get started here. The functions you want to be familiar with are Aggregate, Ranking, Conversion, Date, Logical, Mathematical, and String. After that you want to look into Ranking functions, and Cursor Functions. Ranking functions are used with “Window Functions” which uses the OVER clause. This is a whole separate and advanced topic that is extremely, extremely valuable. It’s covered in great detail by Itzik Ben-Gan in this book on High Performance T-SQL. It’s a small book but it’s worth its weight in gold. As far as cursors, I don’t have any advice for you that you probably didn’t already hear. The proper use of cursors is a nuanced topic that requires deep understanding of the optimizing agent. My advice is learn to write a cursor, but don’t use it unless you are performing sysadmin tasks, iterating over multiple tables, or you know what you are doing. This will come with experience.

• Common Table Expressions are wonderful. It improves readability of your T-SQL, but it also allows you to write recursive queries. Generally speaking, you would use a CTE when you would use a subquery. Since you are doing a lot of ETL, one place where you would want to use a temp table over a CTE is when you need to pop an index over a result set in a staging table. Let’s say you’re loading data into a table, and you need to perform a lot of transformations. The source table doesn’t have the proper indexes. In this case, instead of loading straight from source to destination and do all your transformations in a CTE, you would “stage” your data into a temp table, put indexes on the appropriate column(s), and then perform your transformations.

• XML. Does your team/company want you to shred xml columns? That’s rough. XML is typically at the application layer. I personally hate storing XML in SQL Server. I’m not an expert on XML queries because I hardly ever use it. I would strongly recommend this resource. It’s a nice tutorial for XML queries in SQL Server, and it helped me tremendously when I was learning. Sorry I can’t help much here, however, because I avoid XML like the plague.

If you are heavily involved in ETL, I would get intimately familiar with the MERGE statement, described here and discussion on optimization here. For ETL, it comes in very handy. Also, once you learn MERGE, read my little blurb about how to set NULL = NULL to be true. It works pretty well with MERGE statements.

Aside from BOL/technet, I would browse through other SQL Server Stairways. There’s also a few good blogs/articles I recommend: Anything by Grant Fritchey (/u/scarydba), Brent Ozar (/u/BrentOzar) and his team, and Kimberly Tripp off the top of my head. Itzik Ben-Gan is a T-SQL God. He has a lot of good books out (one I pointed to before). /r/SQLServer and /r/SQL are good resources for questions.

God, I could go on forever, but I think this might be enough to get you started.

Also, PM me for any questions.

More details about a book.

Permalink : /r/learnprogramming/comments/252w9n/i_just_received_an_ultimatum_improve_my_sql/