I have created this blog, for people that want’s a small introduction to Microsoft’s querylanguage, T-SQL (Transact SQL). The blog is split into three parts:
- Introduction / getting started.
- DDL Queries (DDL stands for Data Definition Lanuage = We create tables e.g.)
- DML queries (DML stands for Data Manipulation Language = We extract or manipulate data)
As you can see from the bulletpoints, then the first learing is: What can we use TSQL for. Well, basically you can use TSQL for many purposes, but in general we split it into two types of work: working with the data and creating/dropping/altering objects, like tables, views, stored procedures e.g. (we get back to that later). The desire to work with data often comes from a need to extract data from some kind of system (e.g. your ERP-system).
For this blog, you must have a version of Microsoft SQL Server installed (2008, 2008R2 or 2012. I used 2008R2 and 2012).
We will need to start the Sql Server Management studio (SSMS), and you do that by going to Start –> Programs –> Microsoft SQL Server xxx (where xxx = your version).
enter your servername and either choose windows authentication or choose SQL Server authentication and then enter your credentials. TYPICAL SETTINGS are localhost as Servername (just enter .) and then Windows Authentication. Atleast that is typical if you run the database on your local machine where you have installed it to learn about SQL server. One detail: You can see that server type is Database Engine. Leave that for now, I will explain that in another post later.
Press connect, and you will se the following picture (or at least something that looks like it):
In the top left corner, there is a button called New Query. Click on that, and then you get a white screen:
Now you just need to learn one button: F5. F5 = Execute, and every time you want’s to run a query, then you mark the query and then run it by clicking F5. when you do that, then you get a screen that looks like this (may vary depending on user settings):
Why not end this first part by writting an SQL Statement. Please try to write:
When you have written that, then press F5 (please note!! If you have highlighted some text, then that is the only thing that will be executed, so either highlight everything or nothing).
You should get a resultpane at the bottom of the window:
Use Master is a simple TSQL statement that changes the execution context to a specific database. In this situation the Masterdatabase, that is a systemdatabase in SQL server. We will work in that database, but Please be careful, because if you make any wrong changes to that database, then you will be in serious problems.
GO is actually not a TSQL statement, but a statement that practically means: “OK, here is a go, please send everything above the go statement (from top of the query window or from the last GO-statement) to the SQL server and execute it.
SELECT * FROM SYS.DATABASES is a very simple T-SQL Statement, that Retrieves all rows and columns from a table called sys.databases.
Congratulation, now you have written and executed your first T-SQL Statement.
Before we move on to blog #2 in this short introduction to T-SQL, then I would point out, that if you are going to work a lot with the SQL server, then I recommend that you download the demo databases ADVENTUREWORKS from http://www.codeplex.com. Google Download Adventureworks xxxx, where xxxx is your version (e.g. 2008R2).