johnpalmer (![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png)
johnpalmer) wrote2013-02-06 02:37 pm
johnpalmer) wrote2013-02-06 02:37 pm
SQL Server blogging, and test of cut tags on Dreamwidth
I'm hoping to do some SQL Server blogging. *Please* feel free to skip the stuff after the cut tag.
If you know enough about computers that this doesn't go over your head, and can give some friendly advice about the writing, I'd like to know how it comes across. (If it's dead boring, and no, it's *not* that you don't understand it, I'd like to know that. I mean, I'd rather know that it's fascinating reading, but only if it's true :-) )
So - this is a blog dedicated to teaching folks about SQL Server, sharing my knowledge with others.
Why? Well - I'd like to learn to write better. And I'd like to learn to teach better. And I'd like to "pay forward" some of the people who've helped me learn SQL Server. And, of course, I like to show off, and love to hear myself talk :-).
Let's start with the very basics. What is SQL Server? If you're a Windows Sysadmin, and you've just been told you need to support a bit of SQL Server, what are you supporting? SQL Server is a relational database management system. So, what's that?
Okay: one of the things a great many programs will need to do is store information. Even a great many games need to save information - what stage of the game have you reached? What have you earned in the game so far? What is the game state (are there tasks you've started but haven't finished? Are there tasks you can no longer perform? What's different between now, and a brand new game?) These types of storage are usually easy to manage; you might need one or a few files, and with proper care and testing, a programmer can create the routines to manage that storage without any difficulties.
What happens when it's complicated? For example, what if it's an auto parts inventory and sales program? Now you have to track parts by a variety of factors (one air filter fits several cars, over certain model years, but might have multiple manufacturer identification codes). Here, things get complicated - when you start developing the system, you need to think about all the possible ways that the data might be used, and what people might need to use to search for parts or sales or deliveries. You might need to use the inventory program to interface with the bookkeeping program, and might need to calculate sales commissions or vendor discounts. Each time you think of new factors, you might need to change how you model the data, and how it gets stored. You also need to know a great deal about the computer operating system and the hardware, so you can figure out how to make sure the data gets written in a consistent manner, no matter what happens to the server, or the hardware. And you need consistent performance - you need to know how the program will work under expected, and higher than expected, workloads.
Let's face it; that gets hideously complicated, for any but the simplest of programs. It's easier to have someone else do all of that work for us, and then license that tool for building applications. That's what a relational database management system is.
It's a database, because it stores data.
It's relational, because we can define relationships between data. What kind of relationships? Well, let's go with that auto parts inventory. Let's say we have part number 2356, a zerk fitting. (Yes, that really is a part name - I have no idea what it does, past the Google search results.) It might have an OEM part number, it might have been part of a recall, so it has a new part number (to make sure that you don't use the old, recalled part), it has a description, it has a list of vehicles in which it will work, possibly of different makes and models and years. You purchase it from a supplier, and you have a buddy across town who will sell you one at cost if your mechanics need one now, but you're out.
There's certain information about it that describe that one item - ideally, you make that the main Part database record, and you have a unique identifier to identify that part. That's called a "key". (It's kind of like a key that fits in a lock - the idea being that only one particular key will fit any given lock[1].
What makes a database engine "relational" is that you can use that key elsewhere. You can use it on an invoice, you can use it on a delivery, you can use it in your inventory records, you can search cars that use that part, you can use it on a voucher, or a sales report, and you can do all of this using that same key. Rather than having to store all of the information about the zerk fitting on each of those things, you only need to relate it back to the Parts database record, and we can get that information using the key.
So, that's it: a relational database management system is about storing data in a structured manner, in ways that you can relate items to each other.
Microsoft's SQL Server is named SQL Server after SQL, which stands for Structured Query Language. Per ANSI standards, it's pronounced Ess Cue Ell. I pronounce it like "sequel" because I don't think ANSI has any right to dictate how I act in my personal life. Yes, I'm a rebel - all of my actuary friends wish they could be so bold and reckless!
(I passed two actuarial exams. I'm allowed to make jokes. So, why am I a DBA and not an actuary? T-shirt and jeans, my friend... t-shirt and jeans. None of that suit and tie stuff for me, thank you very much.)
SQL is a standard way of querying a relational database engine. Since it's a standard, people can build a tool (like SQL Server, or Oracle, or MySQL) and people don't have to be locked in to using a particular one. Most (but certainly not all!) of the commands will work on any given SQL-using database engine. If you're going to manage SQL Server, you need to learn the basics of SQL commands[2]. Microsoft includes some excellent management tools with good GUIs, but most of the information about the server and all the various settings is stored in various system tables and views, which are accessed using standard SQL queries. While I hope to include some of my favorite queries on this blog, I don't intend to teach you SQL - there are excellent online resources to do that better than I can!
What else do you need to know about SQL Server? Well, it's intended for the enterprise. "Enterprise" is a bit of a question begging word[3], but it's intended to store data properly, or not at all (look up ACID to understand storing it properly), to have tools to alert you to, and try to deal with, or recover from, hardware and software problems, and to be scalable - meaning you can put it on a machine that's twice as powerful, and get almost twice the performance out of it. It has tools for high availability, and disaster recovery, and lots of bells and whistles that allow you to build very powerful, very sophisticated applications with a reliable engine backing them. It also has a great support team backing it - if you find yourself stumped, or are sure that something isn't working right, you can call Microsoft's CSS team for SQL, and open a new case (a "pro" case, for those without support contracts, is surprisingly cheap compared to pounding one's virtual head against a virtual brick wall), and they'll help you out. (Yes, that really is kind of necessary for the "enterprise" - every program has bugs and its rare for a program to work exactly the way you expect it to, even after you've read the documentation.)
So, that's the basics. SQL Server is an engine that is meant to be able to store and retrieve data quickly, that lets you relate one piece of data to others, and uses the SQL language. It's enterprise-ready - trustworthy storage and retrieval, scalable, and can be used in highly available applications, with good disaster recovery possibilities. It has a rich feature set, and good support. It's a fine choice for a database back end.
[1] Technically, a key can fit multiple locks, and a lock can have multiple keys that will turn it. Don't strain the analogy too hard - the important idea here is that a data "key" means it fits something specific (like a key fits a lock)
[2] SQL commands are called "queries". This makes sense when it's a SELECT - that's the basic command for "tell me what items have the qualities I'm about to request". It makes less sense when it's an INSERT or an UPDATE - that's not a query, that's a command. But "query" is what the Structured Query Language is about.
[3] "Question begging" means we're assuming another question has already been asked and answered first. In this case, that question is "what the heck does 'enterprise' (or 'enterprise ready') mean?"
If you know enough about computers that this doesn't go over your head, and can give some friendly advice about the writing, I'd like to know how it comes across. (If it's dead boring, and no, it's *not* that you don't understand it, I'd like to know that. I mean, I'd rather know that it's fascinating reading, but only if it's true :-) )
So - this is a blog dedicated to teaching folks about SQL Server, sharing my knowledge with others.
Why? Well - I'd like to learn to write better. And I'd like to learn to teach better. And I'd like to "pay forward" some of the people who've helped me learn SQL Server. And, of course, I like to show off, and love to hear myself talk :-).
Let's start with the very basics. What is SQL Server? If you're a Windows Sysadmin, and you've just been told you need to support a bit of SQL Server, what are you supporting? SQL Server is a relational database management system. So, what's that?
Okay: one of the things a great many programs will need to do is store information. Even a great many games need to save information - what stage of the game have you reached? What have you earned in the game so far? What is the game state (are there tasks you've started but haven't finished? Are there tasks you can no longer perform? What's different between now, and a brand new game?) These types of storage are usually easy to manage; you might need one or a few files, and with proper care and testing, a programmer can create the routines to manage that storage without any difficulties.
What happens when it's complicated? For example, what if it's an auto parts inventory and sales program? Now you have to track parts by a variety of factors (one air filter fits several cars, over certain model years, but might have multiple manufacturer identification codes). Here, things get complicated - when you start developing the system, you need to think about all the possible ways that the data might be used, and what people might need to use to search for parts or sales or deliveries. You might need to use the inventory program to interface with the bookkeeping program, and might need to calculate sales commissions or vendor discounts. Each time you think of new factors, you might need to change how you model the data, and how it gets stored. You also need to know a great deal about the computer operating system and the hardware, so you can figure out how to make sure the data gets written in a consistent manner, no matter what happens to the server, or the hardware. And you need consistent performance - you need to know how the program will work under expected, and higher than expected, workloads.
Let's face it; that gets hideously complicated, for any but the simplest of programs. It's easier to have someone else do all of that work for us, and then license that tool for building applications. That's what a relational database management system is.
It's a database, because it stores data.
It's relational, because we can define relationships between data. What kind of relationships? Well, let's go with that auto parts inventory. Let's say we have part number 2356, a zerk fitting. (Yes, that really is a part name - I have no idea what it does, past the Google search results.) It might have an OEM part number, it might have been part of a recall, so it has a new part number (to make sure that you don't use the old, recalled part), it has a description, it has a list of vehicles in which it will work, possibly of different makes and models and years. You purchase it from a supplier, and you have a buddy across town who will sell you one at cost if your mechanics need one now, but you're out.
There's certain information about it that describe that one item - ideally, you make that the main Part database record, and you have a unique identifier to identify that part. That's called a "key". (It's kind of like a key that fits in a lock - the idea being that only one particular key will fit any given lock[1].
What makes a database engine "relational" is that you can use that key elsewhere. You can use it on an invoice, you can use it on a delivery, you can use it in your inventory records, you can search cars that use that part, you can use it on a voucher, or a sales report, and you can do all of this using that same key. Rather than having to store all of the information about the zerk fitting on each of those things, you only need to relate it back to the Parts database record, and we can get that information using the key.
So, that's it: a relational database management system is about storing data in a structured manner, in ways that you can relate items to each other.
Microsoft's SQL Server is named SQL Server after SQL, which stands for Structured Query Language. Per ANSI standards, it's pronounced Ess Cue Ell. I pronounce it like "sequel" because I don't think ANSI has any right to dictate how I act in my personal life. Yes, I'm a rebel - all of my actuary friends wish they could be so bold and reckless!
(I passed two actuarial exams. I'm allowed to make jokes. So, why am I a DBA and not an actuary? T-shirt and jeans, my friend... t-shirt and jeans. None of that suit and tie stuff for me, thank you very much.)
SQL is a standard way of querying a relational database engine. Since it's a standard, people can build a tool (like SQL Server, or Oracle, or MySQL) and people don't have to be locked in to using a particular one. Most (but certainly not all!) of the commands will work on any given SQL-using database engine. If you're going to manage SQL Server, you need to learn the basics of SQL commands[2]. Microsoft includes some excellent management tools with good GUIs, but most of the information about the server and all the various settings is stored in various system tables and views, which are accessed using standard SQL queries. While I hope to include some of my favorite queries on this blog, I don't intend to teach you SQL - there are excellent online resources to do that better than I can!
What else do you need to know about SQL Server? Well, it's intended for the enterprise. "Enterprise" is a bit of a question begging word[3], but it's intended to store data properly, or not at all (look up ACID to understand storing it properly), to have tools to alert you to, and try to deal with, or recover from, hardware and software problems, and to be scalable - meaning you can put it on a machine that's twice as powerful, and get almost twice the performance out of it. It has tools for high availability, and disaster recovery, and lots of bells and whistles that allow you to build very powerful, very sophisticated applications with a reliable engine backing them. It also has a great support team backing it - if you find yourself stumped, or are sure that something isn't working right, you can call Microsoft's CSS team for SQL, and open a new case (a "pro" case, for those without support contracts, is surprisingly cheap compared to pounding one's virtual head against a virtual brick wall), and they'll help you out. (Yes, that really is kind of necessary for the "enterprise" - every program has bugs and its rare for a program to work exactly the way you expect it to, even after you've read the documentation.)
So, that's the basics. SQL Server is an engine that is meant to be able to store and retrieve data quickly, that lets you relate one piece of data to others, and uses the SQL language. It's enterprise-ready - trustworthy storage and retrieval, scalable, and can be used in highly available applications, with good disaster recovery possibilities. It has a rich feature set, and good support. It's a fine choice for a database back end.
[1] Technically, a key can fit multiple locks, and a lock can have multiple keys that will turn it. Don't strain the analogy too hard - the important idea here is that a data "key" means it fits something specific (like a key fits a lock)
[2] SQL commands are called "queries". This makes sense when it's a SELECT - that's the basic command for "tell me what items have the qualities I'm about to request". It makes less sense when it's an INSERT or an UPDATE - that's not a query, that's a command. But "query" is what the Structured Query Language is about.
[3] "Question begging" means we're assuming another question has already been asked and answered first. In this case, that question is "what the heck does 'enterprise' (or 'enterprise ready') mean?"
SQL commands are called "queries".
I'm not sure where something like COMMIT goes, but when you want to call something SQLish that ISN'T a query, there's a category for it.
Re: SQL commands are called "queries".
no subject
However. (You knew there would be a "However", didn't you?)
I actually can't commit to doing that any time in the very near future.
So....how long can I sit on this and still be helpful to you?
no subject
But what I posted back here is no longer my current draft - if you'd like revised copies, I'd send them.
(
no subject