I think T-SQL is an odd language in many ways. One aspect of this is the looseness of the language. The best example of this is the way that statement terminators (i.e. the semicolons at the end of the statements) are optional.
Back in 2005, the optionality of the statement terminators was deprecated. The SQL Server team told us to start using them, because one day they’ll be required.
2019-04-08
The most basic form of SELECT statement that we used to get information out of a table is the word SELECT followed by a list of columns or expressions that we want, and then details of where we need to get them from. Typically, that’s a table.
The basic syntax of a SELECT statement in SQL Server T-SQL is as follows:
Committees are curious things, and I can’t say that I always agree with their outcomes. I suspect if I’d been designing this, I would have had:
2019-04-01
In the last post, when talking about querying a table, I mentioned that you should always include the name of the schema when you refer to a table. This is called using two-part names. But what are schemas in the first place?
In different database engines (e.g. Oracle, PostgreSQL, etc.), schemas are implemented and work differently i.e. there isn’t a consistent story across the industry. In SQL Server though, the closest analogy that most people would be familiar with, is a folder where you group things that are related in some way.
2019-03-25
In the last post, I described what tables were, and then showed how to view their contents using an option in Object Explorer in SQL Server Management Studio (SSMS).
If you’ve ever seen a SQL query of any type, you will have seen one like this:
It says to return all columns from the table, and to return all rows.
Fair enough.
But there are a few things to understand about this simple statement:
2019-03-19
I mentioned previously that databases hold collections of information about related things. But what are these “things”? Well, that’s what the tables are.
Tables are the most basic objects that live in a database. They hold information about one type of thing. You might call the things “entities” but it’s not 100% accurate. It’s the same problem if you call them “objects”. They aren’t really objects. They basically are just “things” that we’re storing information about, like employees, books, cinemas, products, and more. ANSI SQL called them “relations”.
2019-03-11
One concept that often confuses newcomers to SQL Server is the difference between a login and a user.
In an earlier post, I mentioned that being authenticated to a server is what’s called a login. In this example shown, I have a log in here called Malathi. But as I said, this means nothing about what databases Malathi then has access to.
The connection between a login and a database is what the concept of a user is about.
2019-03-04
You might be wondering what databases are.
A database is just a collection of information. Let’s take a look at an example:
On this system, I have a number of user databases (AdventureWorks, PopkornKraze, and others). There are also some system databases:
These are ones that are provided by SQL server itself, and mostly are used to hold SQL Server’s own configuration.
Now if we look inside any of the databases, we’ll see a list of tables, views, and programmable objects like stored procedures, and functions. Tables are where most of the information lives. In PopkornKraze, you can see the tables that are present:
2019-02-25
Before, you can access anything on SQL Server, apart from knowing what to call the server, the server itself needs to know who you are. This process is called authentication.
In SQL Server, a login is person that the server has identified.
Now access to the server by a login doesn’t mean that you can access anything much on the server, it’s just the first step where you get the server to trust who you are.
2019-02-18
Before you can start to execute queries against SQL Server, you need to connect to the server. The first concept that you need to understand is what a server instance is.
You need to know is what name you should use to refer to the server. As an example, if I have a computer called SDUPROD and it has a copy of SQL server installed on it, we could often just connect to the name SDUPROD. If we do that, then what we’re doing is connecting that what’s called the default instance of SQL Server.
2019-02-11
In earlier posts of this introductory series for learning T-SQL, I talked about what SQL is, then about what T-SQL is. There are three basic types of T-SQL statements: DML, DDL, and DCL.
The first of these are called DML. That’s data manipulation language. These are the main language statements that you’ll use and are about getting data into and out of tables. They include reading data by using SELECT, and modifying it by INSERT, UPDATE, DELETE, and MERGE.
2019-02-04
