November 12, 2018 at 9:11 pm
Comments posted to this topic are about the item Fundamentals
November 13, 2018 at 2:35 am
The fundamentals are the reason why I still get new SQL Certifications.
Seriously, certifying for a new SQL Server Version means reading the book you've read oh so many times AGAIN, just this time it's a reminder and look at new features.
November 13, 2018 at 6:46 am
We still expect anyone working in those areas knows how to backup a database, how to write good T-SQL, how to set security for objects, and more.
What are the best resource for learning how to backup a database, as well as learning good practices for writing proper T-SQL queries? Is there any specific book or course you can recommend? I am a newbie but I want to really master this. Are Itzik Ben Gan books the best resources I am looking for? Are there other authors whose books I should check out to guide me? Thanks so much for some guidance!
November 13, 2018 at 8:47 am
primitivefuture2006 - Tuesday, November 13, 2018 6:46 AMWe still expect anyone working in those areas knows how to backup a database, how to write good T-SQL, how to set security for objects, and more.
What are the best resource for learning how to backup a database, as well as learning good practices for writing proper T-SQL queries? Is there any specific book or course you can recommend? I am a newbie but I want to really master this. Are Itzik Ben Gan books the best resources I am looking for? Are there other authors whose books I should check out to guide me? Thanks so much for some guidance!
A couple of good choices for your immediate question are in the book section on this site:
SQL Server Backup and Restore by Shawn McGehee
Defensive Database Programming by Alex Kuznetsov
I'm a retired accidental DBA, perhaps one or more of the real experts will respond.
November 13, 2018 at 9:42 am
crlewis42 - Tuesday, November 13, 2018 8:47 AMprimitivefuture2006 - Tuesday, November 13, 2018 6:46 AMWe still expect anyone working in those areas knows how to backup a database, how to write good T-SQL, how to set security for objects, and more.
What are the best resource for learning how to backup a database, as well as learning good practices for writing proper T-SQL queries? Is there any specific book or course you can recommend? I am a newbie but I want to really master this. Are Itzik Ben Gan books the best resources I am looking for? Are there other authors whose books I should check out to guide me? Thanks so much for some guidance!
A couple of good choices for your immediate question are in the book section on this site:
SQL Server Backup and Restore by Shawn McGehee
Defensive Database Programming by Alex KuznetsovI'm a retired accidental DBA, perhaps one or more of the real experts will respond.
Thanks so much! I will check out those books, looking forward to expanding my knowledge and skill-set 🙂
November 13, 2018 at 12:37 pm
We also have a Stairway to T-SQL ( a couple actually) here to help you learn.
November 13, 2018 at 7:07 pm
primitivefuture2006 - Tuesday, November 13, 2018 6:46 AMWe still expect anyone working in those areas knows how to backup a database, how to write good T-SQL, how to set security for objects, and more.
What are the best resource for learning how to backup a database, as well as learning good practices for writing proper T-SQL queries? Is there any specific book or course you can recommend? I am a newbie but I want to really master this. Are Itzik Ben Gan books the best resources I am looking for? Are there other authors whose books I should check out to guide me? Thanks so much for some guidance!
I guess I have to ask... what do you already know about T-SQL, tables, and indexes? Do you know about such things as SARGability? How's your date math? Can you explain all of the different types of JOINs and why you would use them? Etc, etc.
I'm trying to figure out what level you're at because some of the books that have been recommended to you might be discouraging if you don't truly know the fundamentals.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2018 at 8:22 am
Jeff Moden - Tuesday, November 13, 2018 7:07 PMprimitivefuture2006 - Tuesday, November 13, 2018 6:46 AMWe still expect anyone working in those areas knows how to backup a database, how to write good T-SQL, how to set security for objects, and more.
What are the best resource for learning how to backup a database, as well as learning good practices for writing proper T-SQL queries? Is there any specific book or course you can recommend? I am a newbie but I want to really master this. Are Itzik Ben Gan books the best resources I am looking for? Are there other authors whose books I should check out to guide me? Thanks so much for some guidance!
I guess I have to ask... what do you already know about T-SQL, tables, and indexes? Do you know about such things as SARGability? How's your date math? Can you explain all of the different types of JOINs and why you would use them? Etc, etc.
I'm trying to figure out what level you're at because some of the books that have been recommended to you might be discouraging if you don't truly know the fundamentals.
I have a basic understanding of Indexes, and I learned all the join types and the various use cases for them. I have no knowledge of SARGability. I have near zero knowledge of what guidelines I should follow when writing queries for performance (I learned some basic things like JOINs are preferred over sub-queries if they both return the same result). I am in a tough predicament where I can't figure out my T-SQL knowledge level, but I am studying everyday after work. Everything I am learning is from various books and video courses I bought.
I don't know what I don't know....if that makes sense. For this reason, I am trying to discover and study resources that may be more advanced to build upon my knowledge. That way I am somewhat prepared to start professional work in T-SQL in the future.
November 18, 2018 at 2:19 pm
primitivefuture2006 - Thursday, November 15, 2018 8:22 AMJeff Moden - Tuesday, November 13, 2018 7:07 PMI guess I have to ask... what do you already know about T-SQL, tables, and indexes? Do you know about such things as SARGability? How's your date math? Can you explain all of the different types of JOINs and why you would use them? Etc, etc.
I'm trying to figure out what level you're at because some of the books that have been recommended to you might be discouraging if you don't truly know the fundamentals.
I have a basic understanding of Indexes, and I learned all the join types and the various use cases for them. I have no knowledge of SARGability. I have near zero knowledge of what guidelines I should follow when writing queries for performance (I learned some basic things like JOINs are preferred over sub-queries if they both return the same result). I am in a tough predicament where I can't figure out my T-SQL knowledge level, but I am studying everyday after work. Everything I am learning is from various books and video courses I bought.
I don't know what I don't know....if that makes sense. For this reason, I am trying to discover and study resources that may be more advanced to build upon my knowledge. That way I am somewhat prepared to start professional work in T-SQL in the future.
Perfect. And you definitely have the right attitude and have made the realization that a whole lot of people haven't yet made... the understanding that they don't know what they don't know and kind of have to "get lucky" because they don't even know what questions to ask.
You know the basics. I'd say the next 2 steps are probably the most important (they were to me when I was first learning and as I continue to learn).
Start deep learning on indexes. Here are a couple of outstanding links that will take you almost womb to tomb on indexes. They're definitely worth listening to more than once and they should be required learning for everyone dealing with databases from neophyte to alumni.
https://www.youtube.com/watch?v=7B4Gx4oQ8tQ
https://www.youtube.com/watch?v=QjCEkI8Qm5c
The other thing that a lot of people miss out on (even for languages other than T-SQL) is where the true power of any language lays... built in functions. You don't necessarily have to memorize the syntax but you should at least know what is possible. To start that pile of critical knowledge, you need know what's available and the fundamental purpose of each. You can find that out at the following link and the embedded links from there.
https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-2017
The trouble is, such "official" documentation is sorely lacking in esoteric examples. For example, you can have a photographic memory of what DATEDIFF() and DATEADD() do as described in the documentation without ever making the realization of how to use them together to find the first of the month for any given date after the first of January, 1900 (e.g. SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0); ). Once that's understood, it may still not be understood how to create a list of dates for all months from a given start date thru a given end date. Worse than that, there are a half dozen or more methods that can be used for either but some are going to have much better performance and much lower resource usage than others. For example, using a Recursive CTE (rCTE) is a convenient method of generating a list of such dates but a well formed While Loop will actually beat that and then there are things like the use of a "Tally" or "Numbers" table that will smoke the rCTE and While Loop methods and then also find out that Itzik Ben-Gan devised a method using "Cascading CTEs" (my name for his method and I abbreviate as cCTE) that is nearly as fast, requires no physical manifestation, and is therefor "readless".
THAT kind of esoteric knowledge is what truly separates the "wanna-be's" from the "can be's" and "are be's" (not to be mistaken for one of my favorite fast food chains). 😀
There are two places to get some of that knowledge... from books (expensive) and online (especially from communities like the community here at SSC).
Just a word of warning though. Although I believe my Dad was quoting someone else, he once gave me some advice and it has helped me immensely over the last 40 years... "Half of all this is written is wrong. The other half is written in such a fashion that you can't tell". 😉 He was especially correct when it came to the internet where, for example, you can find people that still claim that it's a "Best Practice" to use rCTEs to produce a sequence of numbers (e.g. Count from some predetermined value to another predetermined value using a predetermined "step" size {usually 1}) or using XML to split a comma delimited string even though both been proven time and time again to be one of the absolute worst methods for what they do.
With that in mind, keep that "intellectual curiosity" you have going. Never settle when training (and, sometimes, in real life coding). Always ask, "Is there a better way?" and the test the hell out of things. Embrace the scientific method when training.
One of the things I ran into early in my career (and I still do to this day but especially in the early web days) was "experts" on different forums that would argue about which method was faster based on the "expert knowledge" or supposed "experience". I remembered another thing my Dad taught me (again, a quote from someone else)... "One GOOD test is worth a thousand expert opinions". Of course, that goes back to the original quote of half the stuff on the internet being written in such a fashion that you can't actually tell if something is good or not. With that in mind, learn to create your own GOOD tests. For that and in the world of databases, you need to learn to build a shedload of random but constrained test data in the proverbial blink of an eye. I humbly submit to you the following two articles to help you begin mastering that particular healthy practice. Neither are the ultimate end all to be all but they make provide a really good start.
http://www.sqlservercentral.com/articles/Data+Generation/87901/
http://www.sqlservercentral.com/articles/Test+Data/88964/
Last but not least, hang around here and check out a lot of the posts where people ask questions about how to do something and see what some people provide for solutions. The really cool part about this community is that people will compare methods by creating such tests and post their complete work for all to see and compare. When you're ready, take a shot a posting some of your own answers with the understanding that people will do a comparison and will provide usually helpful, demonstrative criticism. Also don't get discouraged my some of the total blasts of "well, in my expert opinion" hooie and some of the heated discussions that take place. Even that becomes a lesson in how to deal with sometimes terribly difficult people and situations.
When it comes to T-SQL and programming, demonstrable code is the only thing that matters. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2018 at 2:24 pm
p.s. Google for "SARGable predicates SQL Server". It's an important concept on your journey to mastering high performance T-SQL.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2018 at 7:49 am
Jeff Moden - Sunday, November 18, 2018 2:24 PMp.s. Google for "SARGable predicates SQL Server". It's an important concept on your journey to mastering high performance T-SQL.
Thanks so much for the kind words, and great advice! If I ever reach some level of success in my journey I will for sure try to help others just like you and others here have helped me.
I made a spreadsheet with nearly all the functions mentioned on Microsoft's documentation website, I will complete the descriptions over time as I learn their use cases. Here's the link: https://docs.google.com/spreadsheets/d/1vec7uP8-fepJdv6aqot4n2iRaNxePgDGGePKibA1jUA/edit#gid=1847852479 . The documentation website to me is very un-appealing, I dislike the format and their descriptions are very brief and confusing. I am going to test a lot of these functions with the sample databases to see what they are actually doing.
Indexes are definitely something I must grasp fully. It is a topic I am constantly seeing being discussed in the forums and in blog posts. I will for sure watch the videos you posted. Thanks so much again for your advises 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply