Join Joey D'Antoni for an in-depth discussion in this video What you should know, part of SQL Server Performance for Developers.
- [Instructor] So let's talk about the tools and techniques you'll use for this course along with some of the prerequisites. The prerequisites to this course are a basic knowledge of relational databases. I'd like you to have an understanding of data structures like tables and indexes and databases and how all those things kind of work together, and I'd like you to have a basic knowledge of the T-SQL language that's used with SQL Server. If you've worked with other databases in the past, and you're new to SQL Server, some of these language constructs may be slightly different, but they should be pretty similar to what you've used if you've used Oracle, MySQL, or Postgres.
So what is Transact SQL specifically? This is Microsoft's implementation of the NC SQL, SQL language. It is the main language that's used to create objects and query data within SQL Server. The big difference between T-SQL and say PL-SQL, which is Oracle's procedure language is T-SQL is used for all SQL constructs within Microsoft SQL Server, whereas, for example, PL-SQL and Oracle is only used for stored procedure and package creation. T-SQL has a lot of flexibility.
It's a very mature language, and it gives us the building blocks for any application that relies on a database, and beyond T-SQL, we can also implement CLR or .net constructs that run in the context of our database code. We won't be focusing on that on this course, but you have lots of options. So what this course covers. This course will focus exclusively on the database engine. We're not going to focus on any of the business intelligence tools like reporting services or analysis services. You're going to learn about anti-patterns, what not to do, the importance of good design, specifically around designing indexes for performance.
We're going to focus on query execution. This is really important to understand how execution plans are generated and understanding what requirements you have and how to read an execution plan so you can understand how your query is performing. You're going to learn about temporary objects like temp tables, table variables as well an n-memory temp tables, and that's kind of a new feature for SQL 2016. You will also learn about indexing and performance and how to better understand and how to adjust your indexes to get better performance from your queries.
The tools you'll need if you want to go along with the class are a SQL Server instance. You can used developer edition, which is free, or if you want to use Azure, you can use Azure's SQL database, which is Microsoft's database platform as a service. You'll want to have SQL Server Management Studio or SQL Operations Studio, in order to do your development and manage your databases. Both of these tools are free and it's important to note that Management Studio is no longer included with SQL Server, so if you're installing SQL Server 2016 or 2017, you'll have to install Management Studio separately.
Finally, if you're a dev, you'll probably want to use Visual Studio Community edition, and you can download SQL Server data tools in conjunction with that. You can still code SQL in Visual Studio without SQL Server data tools, but it will make it easier to have management of your database objects like tables and indexes and those sorts of things.
- Query execution
- How to read an execution plan
- What not to do with SQL Server
- Why cursors are bad
- Why scalar UDFs are expensive
- Datatypes and design
- What is a columnstore index?
- Transaction isolation