SQL BASICS

What is a database table

A table is a structure that is used to store data (values) in an organized way using rows and columns. 
Let's say you want to store data about countries. Below is example of a country table:


And below you can see where are colums (ID and Country), rows (8 rows) and values (2 columns x 8 rows = 16 values)


For more information visit what is a database
Take care
Emil

Purpose of tables in a database

The main purpose of tables is to store data in an organized way that allows to achieve company objectives. One database almost always contains multiple tables that represent enity (for instance Customer, Product, Order) and they relate to each other, for instance Customer buys a product and receive unique Order Number.

In our definition we mentioned to achieve company objectives. This is quite important because it affects how tables are designed and there are different techniques depending on objective. 

Purpose of table is not limited to simply store data and it helps to achieve other objective like:

• Enforces uniqueness (for example one customer generally (exception is data warehouse) should be recorded only once in a customer table)
• Allow users to enter only valid values (using data types and constraints)
• Make the data sharable with others (database is generally on a server and can be used by multiple departments)
• Enforce referential integrity and that means that you cannot have order without a customer.
• Quick access to data. Tables or more precisely indexes (that are create for tables) allow quick access to data. 

What is SQL

You might be wondering what is SQL. Let's start with explaining what it stands for. SQL stands for Structure Query Language and it is a language that allows you to communicate with databases. In previous lesson we explained what is a database and we explained that you store data in tables that consist of columns, rows and data. You can have multiple tables related to each other so you need a way to retrieve data (from one or multiple related tables), insert, update or delete data in tables.
In order to do that you need language that your Database Management Software (DBMS) understand and can convert into actions. This language is SQL. To better understand what is SQL let's give you several simple examples of actions you might want to perform on your database.
Let's say you have been given a database and you have been given several tasks:
1) Show me all customers from our database
2) Insert new customers 
3) Update customer information
4) Delete customers that requested to be permanently removed from your database
SQL is a very simple and easy to understand language as it is very similar to questions you need to answer or actions you need to perform so let me give you SQL example for the first 4 points:
1)
SELECT FirstName, Surname, DOB
FROM Customer
-- as you can it to retrieve field from a particular table we just need to use SELECT and provide fields names (separated using comma) and extract data FROM a table which is our case is customer so we added FROM Customer
2) 
INSERT INTO Customer (FirstName, Surname, DOB)
VALUES ('Emil', 'Glownia','1983-08-19')
NOTE: single ' around values is needed to tell SQL that this is a string value as opposed to table name, field name, special keyword and so on. single quotes are also used for dates but are not used for numbers as number is not a string value.
-- second point asked us to insert new customer. This is simple with SQL you just type INSERT INTO Customer and specify field names and then put VALUES and provide the actual values. Strings are surrounded using single quotes. Isn't that simple?
3) 
UPDATE Customer
SET Surname = 'Glownia'
WHERE FirstName = 'Katie' AND Surname = 'Sliwinska' AND DOB = '1986-08-20'
Note: We use CAPITAL letters for UPDATE, SET and AND operator. This is considered by many as best practice as it easier to read it and distinguish between tables names (Proper case) and user values.
-- third point is to update customer details. Katie got married and changed her surname to Glownia. In order to update the record using SQL we just need to type UPDATE Customer then use word SET provide field name we want to update (Surname) and provide value (='Glownia') next we need to identify unique customer record and we do that using WHERE and we specify firstname, surname and DOB.
4) 
DELETE FROM Customer 
WHERE FirstName = 'John' AND Surname = 'Smith' and DOB = '1980-01-01'
-- John doesn't want to have his records in our database and requested to be permanently removed. We do that using DELETE FROM Customer and specifying WHERE condition that identifies John's record.
NOTE: In real life you would normally use Primary Key (PK) or Foreign Key (FK) to identify unique records which we discussed in 'what is a database' page.

When you use SQL you have to obey certain rules. Below I show you SQL Clauses list with order of execution or in other words sequence of clauses that you should follow in order to successfuly execute SQL statement.
SELECT -- which you use to specify the fields or calculated fields you want to return
FROM -- which you use to specify table or tables you want to use and usually includes joins.
WHERE -- which you use to filter rows 
GROUP BY -- used to grouped together values using field or expression you specified
HAVING -- filters group by results with aggregate function (doesn't work on row by row basis like WHERE)
ORDER BY -- used to specify results order

The above our basic SQL Clauses in order you should use them. You don't have to use all of them but there are certain rules you should also follow:
1) SELECT is mandatory
2) FROM is used in most cases (sometimes you can use only select with union without specific table)
3) WHERE you need SELECT & FROM to use WHERE (In SQL Server you can actually used WHERE with select without from but there is not much use of that)
4) GROUP BY -- You cannot used fields in SELECT that are not in group by unless you use aggregate function with them
5) HAVING -- working only if you use GROUP BY
6) ORDER BY -- You can use it at any time

Three reasons for normalization in database

Who is interested in Normalization?

Before we go into reasons for normalization let's make it very clear who is interested in normalization. This is important because there are different types of databases and two main kind of databases are:

• Operational Databases (Online Transaction Processing - OLTP) - These databases are used every day by employees or customers and generally they are updated on row by row basis for example: An Employee may retrieve one customer's details (one row) and update one customer details (one row). These databases are generally used by large number of users that may potentially want to update the same records. OLTP databases are the most widely used databases but bear in mind that the reason behind it, is it's purpose (operational activities) and therefore they will be often many purpose specific  databases. OLTP Databases are often critical from operations perspective (selling an air ticket) and are available for 99.9%-100% of the time. 

• Data Warehouses (Supports Online Analytical Processing - OLAP) - This kind of database is actually the opposite of OLTP in many aspects. Data Warehouses integrate data from multiple sources (usually many OLTP databases but also excel files etc) into one centralized location. During integration process (commonly known as ETL) the data is cleansed (remove duplicates, typos etc) and business rules are applied (reject/accept, calculations etc). Data Warehouses are usually used by decision makers and analysts and they access set of rows (rarely single row) and they typically ask questions like how much did the company (or department) sale last month and the same time a year before. Most companies should have only one data warehouse and the database is critical for supporting tactical and strategic decisions (sometimes operational)

NOTE: When we talk about Data warehouses we mean Kimball Data Warehousing methodology which is the most common one. There are different ones where our arguments may not be applicable.
IMPORTANT: Normalization supports OLTP characteristics and objectives and you should always bear this in mind because Normalization almost never is used in Data Warehouse databases. You may see normalized Data warehouses but that is only because someone was not aware of negative consequences of doing that.

Three reasons for Normalization

Below are three main reasons for normalization (from my point of view).
Reminder: These reasons are not applicable in Data Warehouses because they are handled differently and usage is different.

• Data Integrity
• Data in each table should contain unique instances of that entity (table) with valid attribute (column) values. That means in Customer Entity (table) you should have only one customer (not the same customer with misspelled surname, or changed surname and so on) also certain customer attributes should be valid for instance DOB should not be in the future or sometimes it should be enforce rule of at least 18 years. UK customer Post Code should also be valid and you can enforce UK post code pattern (that get's complicated with multiple countries)
• You can achieve that using table constraints like: Primary Key, Unique Key, Check constraint, default constraint.
• Related tables should contain valid values.This is also know as Referential Integrity which means that two related tables should have related keys. In order words when we break it down to Parent (Primary Key field) and Child table (Foreign Key) we should not have Child table Foreign Key values that do not exist in Parent table.
• We can achieve that using Foreign Key Constraints that will ensure you cannot delete (or insert) value into Foreign Key field that does not exist as Primary Key field value.
• Controlled Data Manipulation
• In OLTP many users may want to access and modify the same rows. After Normalization process you have many tables that contain usually one values in one table (one instance in one table to be more precise). This means that if you need to modify Product Category you don't lock product information and just lock one particular table which is used in many other tables (using Foreign Key). This means users conflicts (trying to modify the same row) is minimized to minimum. 
• Security of data is also important and with many tables you get greater control over data access.
• Performance
• If you break down table into multiple smaller tables than that means you will save space (note: this is not really a good argument in data warehouses due to different usage). When you save space that means you get better performance because accessing particular row requires less effort. 
• In databases you often improve retrieval performance by adding indexes and in OLTP choosing the right indexes in particularly important as there are large amount of request that usually affects individual rows or small set of rows and you want to ensure extract of one or small set of rows is very fast but at the same time you don't want to slow down updates, inserts and deletes and the more indexes you have to slower updates/inserts/delete are therefore choosing the right indexes is particularly important and usually have a big affect on users experience.

Article Version: SQL Select Statement

In this tutorial I will explain what a SQL SELECT statement is, when can you use it and I will give code examples how to use it.

A database stores data in tables. Data in tables is stored using columns and rows. You can retrieve data from tables using SQL SELECT Statement.

SELECT can be used to:

• Retrieve data from specific columns which exist in one or more tables.
• Create calculated fields that contains specific logic to product new results and return it to the user as extra column.
• Use Manipulation Functions (Text, Data and Time, Numeric). For instance retrieve on the fly today’s date.
• Use aggregate functions to summary the rows. For instance Sum sales for each country.
• Limit Results. For instance show only top 10 customers.
• Retrieve only distinct rows. For instance to get only a list of values that are used in a specific field.

In this tutorial we will cover the first two examples and the rest will be covered in separate tutorials that we will add to our SQL Tutorial page.

SQL Syntax and examples

Below is a very simple SQL SELECT syntax:

SELECT FieldName1, FieldName2, FieldName3
FROM tableName

I would like to show you now a few simple examples using Select.My table is called PersonDetails and contains only three fields (PersonID, PersonFirstName, PersonSurname). Based on this table I will present you how to retrieve only the fields you need and retrieve ALL rows from the table which is 4 in this case.

First example retrieves data from PersonName field which is taken from PersonDetails table :
SELECT PersonName
FROM PersonDetails

See below screenshot with our code sample and retrieved data.

In next example I will use exactly the same table as before (table PersonDetails) but this time I will retrieve two fields (PersonName, PersonSurname).Retrieving multiple columns is exactly the same as what I showed you on the first example, the only change we need to make is put comma (,) after the first field in select and then put the second field name.

Below is the code we use to retrieve rows (data) from multiple columns:
SELECT PersonName, PersonSurname
FROM PerosnDetails

See below the outcome of our code

As you can see retrieving fields from one table using SQL is very simple. In the next example we will show you how to retrieve all fields from one table without specifying the columns.

Using table PersonDetails I will retrieving all columns. There are two ways to retrieve data from all columns first one is to put all fields names in SELECT but there is an easier way to do that. We can use special character * which retrieved all fields from the specified table(s).

Below is the code sample:SELECT *
FROM PerosnDetails

Let’s check the output. As you can see the special character * retrieved data from all columns.

SQL Tip: Retrieving all columns can be very convenient but use only if you want to see what is in the table. If your code is going to be part of some kind of application/reporting system make sure you specify the fields which will make your queries run faster, will keep network data traffic to minimum and it is usually easier to understand your code (which fields are involved).

You may have noticed that our results pane column header is exactly the same as the field name in our select and sometimes you will want to rename it. This is easy to do with SQL. You can as an alias which is very simple in use; you just have put as NewFieldName after the column you want to rename.

Let’s see it in action:
SELECT PersonName as Name FROM PerosnDetails


As you can see our retrieved field in called ‘Name’ in the results pane and not ‘PersonName’ anymore.
 

SQL Select calculated column

You can retrieve field values using select but you can also use select to perform calculations and created calculated fields and below I will give several examples.

The simpliest form of calculated field (or calculated column) is below:

SELECT 2+2 AS CalculatedField



As you can see I provided two values and performed addition on them to get 4 as result. This is calculated field. Notice that I haven't used from as I don't need it for calculations.

In the following examples I will use calculated field as well but using more realistic scenarios.
 

SQL Select concatenate

Using Select you can concatenate values, fields (columns) that are strings but also you can convert data types and perform concatenation as well. See below examples:


In the example above we:
--> Concatenated two strings (I have placed a space at the end of  'word1 ' so it is displayed in the results)
SELECT ‘word1 ’ + ‘word 2’ as SimpleStrings
--> Concatenated a string with a field (I have placed a space after 'My name is ' to separate the words)
SELECT ‘My name is ‘ + FirstName as MyNameIs
FROM Customer

--> Concatenated two fields (notice how the space is added between the fields)
SELECT FirstName + ‘ ‘ + Surname as FullName
FROM Customer

--> Concatenated a string with an integer. In this case I used CAST to convert the year field which is an integer into a string (nvarchar with 4 characters)
SELECT ‘Born in ‘ + CAST(YearOfBirth AS NVARCHAR(4)) as BornInYear
FROM Customer

--> Concatenated a string (this could be any data type) with NULL. Be careful here as any concatenation with NULL (or a field containing NULL) will give you a NULL result.
SELECT ‘Problem with null’ + NULL as ProblemWithNULL
FROM Customer

--> Concatenated a string with NULL and handled NULL using the ISNULL function. In this case we changed NULL to an empty string by using 2 apostraphes(‘’)
SELECT ‘I am not NULL’ + ISNULL(NULL,’’) as NULLHandled
FROM Customer

Article Version: SQL SELECT CASE WHEN THEN ELSE

In this SQL tutorial I give you an example of how to use SQL SELECT with CASE WHEN THEN ELSE and for the purpose of this tutorial I will use SQL Server 2012. 
Very often you want to perform IF statement in SELECT and replace one value with another. In SQL we cannot use IF in SELECT clause however we can achieve the same result using CASE WHEN THEN ELSE.
In the below example I will use a very simple example where I will divide 7 days into 3 parts: Monday to Wednesday, Thursday to FrIday and Saturday to Sunday. Normally I would use today's date with GETDATE() function or Date Field and use DATEPART function with DW (day of week) to determine day of week and then replace with with my text... so my input value would be something like that DATEPART(DW,GETDATE()) but for simplicity reasons I will just hard code my input value and put just number 7 which is 'Saturday'.
NOTE: Sunday = 1 and Saturday = 7
SELECT
  CASE 7
      WHEN 2 THEN 'Monday to Wednesday'
      WHEN 3 THEN 'Monday to Wednesday'
      WHEN 4 THEN 'Monday to Wednesday'
      WHEN 5 THEN 'Thursday to Friday'
      WHEN 6 THEN 'Thursday to Friday'
      WHEN 7 THEN 'Saturday to Sunday'
      WHEN 1 THEN 'Saturday to Sunday'
      ELSE 'Unknown'
  END AS PartOfWeek

Below is result of the above SQL select statement
 

SQL FROM Syntax

SQL FROM clause allows us to specify table name (or view) that we want to use to retrieve certain fields. The most basic syntax is below:
SELECT *
FROM TableName

SQL Joins Examples

SQL JOINS Introduction 

We are going to show you how to use Joins in sql and this knowledge might help you to show related rows between two tables (inner join) for instance Customer and (Customer) Orders or show related rows between two tables and additionally show data that exists in first table but does not have related data in second table (LEFT OUTER JOIN) for instance show ALL customers and order details but show also customers who haven't placed an order yet.

SQL INNER JOIN EXAMPLE 

From first table dbo.DimCustomer I check primary key name and find this field in the dbo.FactInternetSales.



I can see that in the dbo.FactInernetSales CustomerKye Field exist as well.


In my script below I join dbo.DimCustomer with dbo.FactInternetSales On CustomerKey field.
You can notice that before each field name are aliases I put them to recognize which field come from which table. 

Below you can see when customers ordered something.

SQL LEFT OUTER JOIN EXAMPLE

From first table dbo.DimCustomer I check primary key name and find this field in the dbo.vwFactSalesOrder2005



I can see that in the dbo.vwFactSalesOrder2005 CustomerKye field exist as well.
NOTE: We used FactSalesOrder 2005 to filter data this way we can show customers who haven't placed an order in specific year for instance 2005


In my script below I join dbo.DimCustomer with dbo.vwFactSalesOrdr2005 On CustomerKey field.
You can notice that before each field name are aliases I put them to recognize which field come from which table. 

NOTE: I have used fso.CustomerKey is null to show only customers that haven't placed an order in 2005.
Below you can see all customers without orders placed in 2005.

SQL WHERE

WHERE is used to filter data that you extract based on the conditions your specify.

SQL WHERE Syntax

Below is the basic example of SQL Where syntax:

 SELECT Field1, Field2 

 FROM TableName

 WHERE FieldName = Value

Before we go into an example let's clarify condition in where clause. Condition is evaluated to either TRUE or FALSE. So when you retrieve the data SQL will evaluate condition and it will include only rows where condition is TRUE. Things are slightly more complicated when you use multiple conditions but the principle stays the same and I will go into more details with multiple conditions.

SQL WHERE Example Below are examples of simple condition: 2 = 2 is always TRUE so in our case our select will return all rows 1 = 2 is always FALSE so our SELECT will return 0 data rows. Now that I explain what a condition is let's have a look into real life example using one condition: In this case we have filtered the results and we shows only customers (records) with surname = 'Glownia'. See below more rules regarding data types:

 

Data Type Rules

String = if you field is string data type then you will have to enlose your values using single quotes like that 'value'. If you have values with single quote like surname al'jar then you will have to use double single quote for the quote inside like that 'al''jar' Numeric = for numeric values we don't need quotes so we can just do something like that Year = 2011 Date = Dates require quotest and you need to use specific format which is YYYY-MM-dd so if you want order that have been places on 1st of October 2011 then you can use OrderDate = '2011-10-01' but BEWARE! Field can contain time so you may lose rows! It is usually good idea to use function in this case like that DATEDIFF(d,'2011-10-06 08:00:00','2011-10-06 16:00:00') = 0 you can notice that the time is different but I use datediff function to ignore time. Boolean = Which is basically TRUE and FALSE in sql server we use 0 = FALSE and 1 = TRUE NULL = this is special value that represent non-existend value and we can filter it using IS NULL for instance email is null. Beware that you can also have empty string which different to NULL and you filter empty string (for string data type) using the following example email = ''

SQL WHERE examples

SQL GROUP BY

SQL Group By Syntax

Below is group by syntax in the simpliest form:
SELECT FieldName
FROM TableName
GROUP BY FieldName

SQL Group By Example

Group By allows us to group rows together and additionally perform aggregate calculation which works on a group of rows. Let me first give you a simple example of what group by does using one field. I will use Customer table and group all customers by Country in other words I want to show all unique values that exist in Country Field, that is how group by works. See below example code with results (before and after)
SELECT *
from [dbo].[CUSTOMER]

sql group by year

In this step by  step SQL Tutorial I will show you how to using SQL Group by Year from date field and use aggregate function.  For the purpose of this tutorial I will use SQL Server 2012.
Prior knowledge: It might be easier for you to follow this tutorial if you have knowledge of Aggregate functions and Group by for more information visit SQL Group By.
We are going to show you how to use Year and Sum aggregate function with Group by this knowledge might help with questions like
how to get Total sales amount for each year. In the following example I will show you how you could write the query.

Below I use Year function with ShipDate field and display new column as SalesYear in SELECT then I use group by with Year on ShipDate field.
NOTE: As you can see we "aggregated" date to Year level using Year function and placed it both in Group by clause and select clause.


Once I have Sales Year I added Sum aggregate function to select with SalesAmount field to get Total Sales Amount.

sql group by month and year

In this step by  step SQL Tutorial I will show you how to use SQL Group by clause with using Month, Year and aggregate functions.  For the purpose of this tutorial I will use SQL Server 2012.

Prior knowledge: It might be easier for you to follow this tutorial if you have knowledge of Aggregate functions and Group by for more information visit  SQL Group by.
We are going to show you how to use Month and Sum aggregate function with Group by this knowledge might help with questions like
how to get Total sales amount for each Month . In the fallowing example I will show you how you could write the query.

Below I used Month function to get only month from ShipDate and new column is displayed as SalesMonth. In Group by I use month function with ShipDate field.
 


Once I have data group by month I Sum SalesAount field to get Total sales amout.


I add some filter to show only months from 2006.

sql group by count

---

Introduction to SQL Group By with Count

In this step by step SQL tutorial I will show you how to use Group by clause witch count aggregate function. For the purpose of this tutorial I will use SQL Server 2008 R2.

Prior knowledge: It might be easier for you to follow this tutorial if you have knowledge of Group By clause
for more information visit SQL Group By tutorial

SQL Group By Count Rows

In our example we will use DimCustomer table which contains 18494 rows.

Let's check what we have in our table that we can Group and count. Gender seems to be a good choice. 

 
What we want to see is total customers grouped by Gender. 
In our SQL statement I will put Gender field in Group By clause and in select, additionally I will put COUNT(*) as TotalGender that will perform count on the group value. See below code and results.

Our results shows that there are approximately the same amount both Female and Male customers and we were able to find out that by using Group by and Count function.

using-not-operator-t-sql

Using the NOT operator
Not is used to negate a condition.
First sample shows Product Name, ProductNumber, ListPrice, SellEndDate
From Product table. I would like to see only those product which SellEndDate is not null.

Below you can see that my SellEndDate dysplays a lot of Null values .


In Were I filter the SellEndDate to dysplay all values where are not null. 

how-to-use-IN-operator-in-t-sql

How to use In operator in t-sql
In this step by step T-sql tutorial I will show you how to use IN operator.
For the purpose of this tutorial I will use SQL Server Menagment Studio 2008.

Why use In operator ?
The IN operator we  used to specify a rage of conditions, any of which can be mached.
The Select statement retrieves all Employees the IN operator is followed by a comma-delimited list of valid values and the list must be closed using parentheses.

First picture shows FirstName, LastName, and Year which I took from StartDate
 using Year() function and dysplayed as StartYear.


Into Were statement U used StartDate converted to Year and to get
all employees from 2000 and 2001 I used In() operator.

SQL Server Wildcards

In this article I will cover how to use wild cards in SQL Server. Wild cards are used when we want to filter data but not using exact match for instance a value should begin with specific characters or contain them or match certain pattern.
In this tutorial I will present you some samples witch wild cards.
In the sample below I used “%” wildcard to find any matching character.
In this case I want to find all missing data containing following text before ‘5@adventure-works.com’.
The sample below presents how to find Name containing Ball inside the percentage on both sides means that there can be missing text which will be finding by this wildcard.



Next wild card which I will present is “_” .
Underscore wildcard will much only one character.


The combination of “%” and “[]” gives us opportunity to fins all data ending on 1-9.


Next wildcards combination returns everything what starts from “a” to “c”.

SQL Server INTERSECT operator

INTERSECT  INTRODUCTION 

We are going to show you how to compare result of two queries using INTERSECT statement and return only distinct (unique) rows that exist in both queries.

To better understand our examples let's create a task that is close to real-life.

Imagine it's Christmas time and you have been asked to identify customers that might buy something this Christmas. These customers will be send "a gift" that will encourage them to buy something new. Budget is limited and you need to focus on the most promising customers. 

You decided that the best customers are the ones that bought something in December last year and the year before. You just need to write a SQL query to identify those customers.

In the  following example I will show you how you could solve your task.

SQL INTERSECT statement using SSMS 2012

Before we start let's briefly discuss INTERSECT behaviour and rules:

• INTERSECT returns DISTINCT (unique) rows that exists in both queries
• You need the same number of fields; in the same order
• Data type for each matching field should be the same

In our example we have to queries:

One returns Customers (CustomerKey field - we won't bother with joins to customer table) that bought something in December 2007.

Second T-SQL query returns Customers (CustomerKey field) that bought something in December 2006

Our task is to return Customers that bought something both in December 2007 and December 2006. In order to do that we will use INTERSECT.

See below example that returns 44 unique customers (CustomerKey) that are best candidates to receive a gift this Christmas!

sql server except example

Prior knowledge:
It might be easier for you to follow this tutorial if you have knowledge of SQL UNION Operator that generally most developer learn first and is very similar to EXCEPT operator in rules and structure. The only difference is end result.

How does EXCEPT work?
EXCEPT operator combines two queries that have the same number of fields and returns unique rows from first query that don't exist in second query.

EXCEPT operator has two basic rules:
- The number of  columns and order must be the same in all queries.
- The data types must be the same.

EXCEPT EXAMPLE

To better understand EXCEPT operator let's give you a real life example. In our building a data warehouse project I was given a task to see if we are getting website visits to pages that don't exist in our "master list". To do that I need to compare two different sources of information and find any values that exist in first source but don't exist in second source.
Below is a PAGE table that contains all pages that we created. In theory when someone visit our website it should go to a page that we created and our task is to check if that is what is happening and if not find out why that is not the case.


Our next source is TrackingSystem view that display information about each visit including our website page name. This data is gathered automatically and we do not have full control over it so it is important to cross reference data and see if we have "good quality" data.
See below view Tracking System that shows Page Name which in theory should match our Page Table from previous example.


Now that I have found fields from two different sources that I need to compare; I can do EXCEPT operator to compare the result of this two queries and get unique values of pages that are recorded during each visit and don't exist in our Page table that should contains all pages.
Below is our query using EXCEPT operator and uses two different databases.
NOTE: We got an error! If you would like to know more about Collation confict please follow this link (we will upload the link soon).

Our new query is exactly the same but uses COLLATE to resolve collation conflict. 
See below our query and results of EXCEPT operator.

As you can see we have a few page names that have been recorded and don't exist in our Page table. Looking at the data I can see that there are some special characters %5C that indicate that our internal links or external links that point to our pages are not fully correct. 
Thanks to EXCEPT operator we were able to quickly find a list of page name exception and using this information we can create suitable actions.

SQL UNION Operator

Article Version: SQL UNION Operator

In this Step by step SQL Tutorial I will show you a simple example using SQL Union.
Union is used to combine two queries together so they appear as one result set.

Below is a simple example where I combine one row with another row using union.

Below are several basic SQL UNION rules (not all):
- Union should have two or more SELECT statement.
- Queries columns must be listed in the same order.
- Column data types must be the same.

IMPORTANT: Union shows DISTINCT rows in the final result. This is not always desired behavior so if you need to return exactly the same number of rows from both queries than use UNION ALL
PERFORMANCE: UNION applies DISTINCT; that means it will be slower comparing to UNION ALL that doesn't do that however always ensure you get the expected result.

You can also visit SQL UNION ALL Multiple queries issues with last UNION

I hope this very simple example of SQL UNION will help you

VIDEO SQL Union All Basics

SQL UNION ALL multiple queries

Below is SQL that uses UNION ALL and is based on AdventureWorkdDW2012 database.
USE AdventureWorksDW2012

SELECT EmployeeNationalIDAlternateKey, Gender
FROM DimEmployee AS de
WHERE EmployeeNationalIDAlternateKey = 112457891

UNION ALL

SELECT EmployeeNationalIDAlternateKey, Gender
FROM DimEmployee AS de
WHERE EmployeeNationalIDAlternateKey = 309738752
and the result is below

I have used UNION ALL so I don't lose duplicated row (which is not really duplicated as has different surrogate key, those working on Data Warehouse project will now what I mean)
The issue I got is when I used another UNION. I tried to use add another row which would have some hard coded data for demo purposes so I added UNION (I forgot ALL) see below
UNION
SELECT 123, 'U'
So that should give me the result below right? WRONG.... as I have learned.

This will give this result

It seems that the last UNION is applied and overwrites UNION ALL used before... which is rather odd and therefore UNION means that DISTINCT is applied and duplicated rows are removed and we get 3 rows instead of 4 rows. This is was my typo I simply replaced last UNION with UNION ALL but it was very surprising and interesting for me see this.

SQL Functions

SQL Function List: String Functions Aggregate Functions String Functions Frequently Used String Function RIGHT Function LEFT Function SUBSTRING function UPPER Function LOWER Function RTRIM Function LTRIM Function LEN Function CHARINDEX Function REVERSE Function REPLACE function FORMAT Function Less Frequently Used String Functions STR Function SPACE Function CONCAT Function ASCII Function CHAR Function NCHAR Function UNICODE Function SOUNDEX Function DIFFERENCE Function PATINDEX Function QUOTENAME Function STUFF Function REPLICATE Function Aggregate Functions Frequently Used Aggregate Function COUNT Function SUM Function AVG Function MIN Function MAX Function Less Frequently Used Aggregate Functions CHECKSUM_AGG COUNT_BIG STDEV STDEVP GROUPING GROUPING_ID VAR VARP RIGHT Function Function Name: RIGHT Function - returns the specified number of characters from a given input value starting from the end (right side). Syntax:  RIGHT(<<Input Value>>, <<Number of Characters>>) Arguments: <<Input Value>> - Provide input value (string, field name or expression) that will be used as your input. <<Number of Characters>> Provide number of characters that you would like to receive from the <<Input Value>> you provided. Simple Example: SELECT RIGHT('ABC-123-BCD', 3) Result = 'BCD' Full Article: SQL Right Function - Article that describes the RIGHT function in details and provides examples. Right Function Microsoft Documentation LEFT Function Function Name: LEFT Function - returns the specified number of characters from a given input value starting from the very beginning (left side) Syntax:  LEFT(<<Input Value>>, <<Number of Characters>>) Arguments: <<Input Value>> - Provide input value (string, field name or expression) that will be used as your input. <<Number of Characters>> Provide number of characters that you would like to receive from the <<Input Value>> you provided. Simple Example: SELECT LEFT('ABC-123-BCD', 3) Result = 'ABC' Full Article: SQL LEFT Function - Article that describes the LEFT function in details and provides examples. LEFT Function Microsoft Documentation SUBSTRING Function Function Name: SUBSTRING Function - returns portion of the specified  input value based on starting position and number of characters to be returned. Syntax:  SUBSTRING(<<Input Value>>, <<Starting Position>>, <<Number of Characters>>) Arguments: <<Input Value>> - Provide input value (field name or expression) that will be used as your input. <<Starting Position>> - Provide starting position which indicates where the portion of input value will be extract from. <<Number of Characters>> - Provide number of characters that should be extracts (from Starting Position). Simple Example: SELECT  SUBSTRING ('ABC-123-BCD',3,5) Result = 'C-123' Full Article: SQL SUBSTRING Function - Article that describe the SUBSTRING function in details.. SUBSTRING Function Microsoft Documentation LOWER Function Function Name:  LOWER Function - returns a value after converting specified input value characters to lower case. Syntax:   LOWER (<<Input Value>>) Arguments: <<Input Value>> - Provide input value (string, field name or expression) that will be used as your input. Simple Example: SELECT LOWER ('Abcd') Result = 'abcd' Full Article: SQL LOWER Function - Article that describes the LOWER function in details.. LOWER Function Microsoft Documentation UPPER Function Function Name:  UPPER Function - returns a value after converting specified input value characters to upper case. Syntax:   UPPER (<<Input Value>>) Arguments: <<Input Value>> - Provide input value (field name or expression) that will be used as your input. Simple Example: SELECT UPPER ('abcd') Result = 'ABCD' Full Article: SQL UPPER Function - Article that describe the UPPER function in details.. UPPER Function Microsoft Documentation RTRIM Function Function Name:  RTRIM Function - returns a value after removing any spaces from the right side of the specified input value. Syntax:   RTRIM (<<Input Value>>) Arguments: <<Input Value>> - Provide input value (string, field name or expression) that will be used as your input. Simple Example:                        SELECT RTRIM ('  abc  ') Result =  '  abc' Full Article: SQL RTRIM Function - Article that describe the RTRIM function in details.. RTRIM Function Microsoft Documentation LTRIM Function Function Name:  LTRIM Function - returns a value after removed any spaces from the left side of the specified input value. Syntax:   LTRIM (<<Input Value>>) Arguments: <<Input Value>> - Provide input value (field name or expression) that will be used as your input. Simple Example: SELECT LTRIM ('  abc  ') Result =  'abc  ' Full Article: SQL LTRIM Function - Article that describe the LTRIM function in details.. LTRIM Function Microsoft Documentation LEN Function Function Name:  LEN Function - returns the number of characters of the specified input value. Syntax:   LEN (<<Input Value>>) Arguments: <<Input Value>> - Provide input value (field name or expression) that will be used as your input. Simple Example: SELECT LEN ('abc') Result =  3 Full Article: SQL LEN Function - Article that describe the LEN function in details.. LEN Function Microsoft Documentation CHARINDEX Function Function Name:  CHARINDEX Function - Is used to find position of a character(s) in a string (field). Syntax:   CHARINDEX (<<Value to Find>>, <<Input Value >>) or CHARINDEX (<<Value to Find>>, <<Input Value >>,<<Search Starting Position>>) Arguments: <<Value to Find>> - Provide value that you want to find in <<Input Value>>.  <<Input Value>> - Provide input value (field name or expression) that will be used as your input. Optional: <<Search Starting Position>> - Search of <<value to find>> starts from the beginning of <<input value>> however you can provide third argument that will specify starting position of the search. Simple Example:           SELECT CHARINDEX ('@', 'katie@katieandemil.co.uk' )            Result = 6 SELECT CHARINDEX ('@', 'katie@katieandemil.co.uk',6 ) Result = 1 Full Article: SQL CHARINDEX Function - Article that describe the CHARINDEX function in details.. CHARINDEX Function Microsoft Documentation REVERSE Function Function Name:  REVERSE Function - Is used to return the reverse order of a string value. Syntax:  REVERSE (<<Input Value>>) Arguments: <<Input Value>> - Provide string value (field name or expression) that will be used as your input. Simple Example: SELECT REVERSE ('abc') Result = 'cba' Full Article: SQL REVERSE Function - Article that describe the REVERSE function in details. REVERSE Function Microsoft Documentation Replace Function Function Name:  REPLACE Function - Is used to find value and replace it with specified replacement value. Syntax:  REPLACE ( <<Input Value>> ,<< ValueToFind>>, <<ReplacementValue>> ) Arguments: <<Input Value>> - Provide string value (field name or expression) that will be used as your input. <<ValueToFind>> -Provide substring to be found. <<ReplacementValue>> -Provide string to replace. Simple Example: SELECT Replace('Katie & Emil', '&', 'and') Result = 'Katie And Emil' Full Article: SQL Replace Function - Article that describe the REVERSE function in details. Replace Function Microsoft Documentation COUNT Function Function Name:  COUNT Function - Is used to count rows in a table. Syntax:  COUNT ( <<Input Value>> ) Arguments: <<Input Value>> - Provide input value (field name or expression) that will be used as your input.   Simple Example:               SELECT COUNT(*)             FROM [dbo].[DimCustomer]  Result = 18484   Full Article: SQL COUNT Function - Article that describe the COUNT function in details. COUNT Function Microsoft Documentation  SUM Function Function Name:  SUM Function - returns the total sum of a numeric column. Syntax:  SUM ( <<Input Value>> ) Arguments: <<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input. Simple Example:              SELECT Sum(SalesAmount) As TotalSalesAmount            FROM [dbo].[FactInternetSales]  Result =  29358677.2207   Full Article: SQL SUM Function - Article that describe the SUM function in details. SUM Function Microsoft Documentation AVG Function Function Name:  AVG Function - returns the average value of a numeric column. Syntax:  AVG ( <<Input Value>> ) Arguments: <<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input. Simple Example:                             SELECT Avg(SalesAmount) As AverageSalesAmount                FROM [dbo].[FactInternetSales]     Full Article: SQL AVG Function - Article that describe the AVG function in details. AVG Function Microsoft Documentation  MIN Function Function Name: MIN Function - returns the Minimum value of a numeric column. Syntax:  MIN ( <<Input Value>> ) Arguments: <<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input. Simple Example:               SELECT MIN(SalesAmount) As MinSalesAmount                            FROM [dbo].[FactInternetSales]                             Result = 2.29     Full Article: SQL MIN Function - Article that describe the MIN function in details. MIN Function Microsoft Documentation  MAX Function Function Name: MAX Function - returns the Maximum value of a numeric column. Syntax:  MAX ( <<Input Value>> ) Arguments: <<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input. Simple Example:               SELECT MAX(SalesAmount) As MinSalesAmount              FROM [dbo].[FactInternetSales]                          Result = 3578.27      Article Version: SQL RTRIM Function In this step by step SQL Tutorial I will show you how to use RTRIM function. For the purpose of this tutorial I will use SQL Server 2012. Other Versions of SQL Server: SQL Server 2012 This blog post has been written using SQL Server 2012 SQL Server 2008 / R2 The examples we used should work in SQL Server 2008 and R2 SQL Server 2005 We suspect that this will work in SQL Server 2005 as well Who is it for? People who would like to learn basics of using functions. Those who are preparing for Microsoft certifications and need to cover this topic. Contents SQL RTRIM function Introduction SQL RTRIM function Example SQL RTRIM function Introduction Function Name: RTRIM Function - returns a value after removing any spaces from the right side of the specified input value. Syntax: RTRIM (<<Input Value>>) Arguments: <<Input Value>> - Provide input value (string, field name or expression) that will be used as your input. SQL RTRIM function Example In this example I will show you how to use RTRIM to remove empty space from the right side of the string (input value). In first column you can see string with space at the beginning and at the end. The second column StringWithNoSpaceOnTheLeft you can see that i use function RTRIM to remove the space from the end of the string (input value). Below is result. You can see that space was only removed from the right side. SQL LEN Function SQL LEN function Introduction  Function Name: LEN Function - returns the number of characters of the specified string value. Syntax: LEN (<<String Value>>) Arguments: <<String Value>> - Provide string value (field name or expression) that will be used as your input. SQL LEN Function Example In this example I will use AdventureWorksDW2012 database dbo.DimCustomer table and check how long is the email address. Below is example of using LEN and you can see the results of LEN function using EmailAddress field. Exceptions in the LEN function The LEN () by default removes spaces from the right side but do not remove space from the left side. In the first example the length of string is 5 and we get result 5. In this example we have space at the beginning of the string and the results give us six as a length of string. In the last example we have a space at the end of string and the length of string is 6 but len function ignores spaces at the end so the result is 5 not 6. How to count space at the end Recently I noticed that someone visited this page by typing can sql len function detect space and we have already provided the answer above so LEN ignores spaces at the end so it cannot detect it but what if you want to count the space the end? One possibility is to use DATALENGTH function that returns 'size' of the string so if one character is 1byte than example below will give us 6 and it does take into account space at the end as it does take 1 byte. SELECT DATALENGTH('kasia ') NOTE: but be careful because this function returns size in bytes so if I provide unicode sting with N'kasia ' than each character is 2 bytes so result is 12 not 6. SELECT DATALENGTH(N'kasia ')   SQL LEFT function Introduction Function Name: LEFT Function - returns the specified number of characters from a given string starting from the very beginning (left side) Syntax:  LEFT(<<String Value>>, <<Number of Characters>>) Arguments: <<String Value>> - Provide string value (field name or expression) that will be used as your input. <<Number of Characters>> Provide number of characters that you would like to receive from the <<String Value>> you provided. Simple Example: SELECT LEFT('ABC-123-BCD', 3) Result = 'ABC' Microsoft Documentation: LEFT Function Microsoft Documentation SQL LEFT function Example In this example I will use AdventureWorks2012 database dbo.DimCustomer table. In this example I will show you how to use LEFT to return first two characters string of PostalCode. SQL RIGHT function Introduction Function Name: RIGHT Function - used to take specified number of characters from a string starting from the end (right side). Syntax:  RIGHT(<<String Value>>, <<Number of Characters>>) Arguments: <<String Value>> - Provide string value (field name or expression) that will be used as your input. <<Number of Characters>> Provide number of characters that you would like to receive from the <<String Value>> you provided. SQL RIGHT function example In this example I will use AdventureWorks2012 database dbo.DimCustomer table. In this example I will show you how to use Right to return a piece of character string of email address. In first column you can see EmailAddress and the second column TopLevelDomain (last part of address like .com) you can see that i use function Right() to extract '.com' from EmailAddress column by using 4 last characters. NOTE: In results below this function works however for non .com address this may produce incorrect results and genereally right is combined with other SQL Functions. SQL LOWER function Introduction Function Name:  LOWER Function - returns a string value after converting specified string value characters to lower case. Syntax:   LOWER (<<String Value>>) Arguments: <<String Value>> - Provide string value (field name or expression) that will be used as your input. Simple Example: SELECT LOWER ('Abcd') Result = 'abcd' Microsoft Documentation: LOWER Function Microsoft Documentation SQL LOWER function Example In this example I will use AdventureWorks2012 database dbo.Person.Person table. In this example I will show you how to use LOWER function to change first letter in a string to lower case.In first column you can see LastName and the second column LastNameLowerLetter you can see that last name starts with lower case.  SQL UPPER function Introduction Function Name:  UPPER Function - returns a string value after converting specified string value characters to upper case. Syntax:   UPPER (<<String Value>>) Arguments: <<String Value>> - Provide string value (field name or expression) that will be used as your input. Simple Example: SELECT UPPER ('abcd') Result = 'ABCD' Microsoft Documentation: UPPER Function Microsoft Documentation SQL UPPER function Example In this example I will use AdventureWorks2012 database dbo.Person.Person table. In this example I will show you how to use UPPER function to change letters in a string to upper case. In first column you can see LastName and the second column LastNameUpperLetter you can see that last name is return into upper case.  SQL REVERSE() function Introduction Returns the reverse order of a string value. For instance word 'cat' reversed becomes 'tac'.   Function Name: REVERSE Function - Is used to return the reverse order of a string value. Syntax:  REVERSE (<<Input Value>>) Arguments: <<Input Value>> - Provide string value (field name or expression) that will be used as your input. SQL REVERSE() function Example In this example I use REVERSE() function to reverse FirstName field. SQL SUBSTRING function Introduction Function Name: SUBSTRING Function - returns portion of the specified  string value based on starting position and number of characters to be returned. Syntax:  SUBSTRING(<<String Value>>, <<Starting Position>>, <<Number of Characters>>) Arguments: <<String Value>> - Provide string value (field name or expression) that will be used as your input. <<Starting Position>> - Provide starting position which indicates where the portion of string will be extract from. <<Number of Characters>> - Provide number of characters that should be extracts (from Starting Position). SQL SUBSTRING function Example In this example I will use AdventureWorks2012 database Sales.Customer table. In this example I will show you how to use SUBSTRING function to return a piece of character string of AccountNumber. In first column you can see AccountNumber and the second column AccountNumberMiddle7Character  you can see that i use function SUBSTRING to extract ewerything what starts from second character in value and rund through next severn letters in the string. SQL CHARINDEX() function Introduction We can use CHARINDEX() function when we need to find position of a character(s) in a string (field). Function Name:  CHARINDEX Function - Is used to find position of a character(s) in a string (field). Syntax:   CHARINDEX (<<ValueToFind>>, <<InputValue>>)            CHARINDEX (<<ValueToFind>>, <<InputValue >>,<<StartingPosition>>) Arguments: <<ValueToFind>> - Provide string value that contains the sequence to be found. String value which we want to find is limited to 8000 characters. <<InputValue>> - Provide string value (field name or expression) that will be used as your input. <<StartingPosition>> - String position where the search for <<StringToFind>> should start. SQL CHARINDEX() function Example In the example below I will show you how to find '@' in EmailAddress. In this example I omit starting position argument (3rd argument) so this function will start searching for the specified character from the very beginning. See below T-SQL code and results. In the second example I want to find '@' in the EmailAddress field and the starting position is 7 (let say first part of email address must be minimum 6 characters). So the first seven characters are omitted during search for '@' character and the result are below. In first email address we get 0 as @ does not exist AFTER 6th (starting 7th) character. NOTE:  Although we start search at 7th character the result of CHARINDEX is number of characters from the very beginning (not 7th position). SQL REPLACE function Introduction Function Name:  REPLACE Function - Is used to find value and replace it with specified replacement value. Syntax:  REPLACE ( <<Input Value>> ,<< ValueToFind>>, <<ReplacementValue>> ) Arguments: <<Input Value>> - Provide string value (field name or expression) that will be used as your input. <<ValueToFind>> -Provide substring to be found. <<ReplacementValue>> -Provide string to replace. SQL REPLACE function Example In this example I will show you how to use Replace function to. In first picture you can see that Katie&Emil company name contains '&' between names. I would like to replace '&' with 'And' you can see in second picture how I did it.  SQL Soundex What is soundex function? SQL Server Soundex is a function that converts string (words) into 4 characters code. The code represent "sound" of the word so you can use function on another word and see if they "sounds" the same. If 4 characters code is the same than it sounds the same. Let's show you a simple SQL query example: SELECT SOUNDEX('Katie') --returns K300 SELECT SOUNDEX('Emil') --returns E540 As you can see Katie & Emil don't sound the same. Let's give you another example. SELECT SOUNDEX('Katy') --returns K300 SELECT SOUNDEX('Katie') --returns K300 Katie & Katy sound similar so they have the same code K300. Actually this is nice example because when Katie introduces herself and than people type the name on a computer they sometimes type Katy instead of Katie because both names sound similar. What is the purpose of Soundex function? You can use SQL Soundex function to compare two words and find out if they sound the same. Remember we only check sounds, that means we check sound similarity but that doesn't mean the words are similar or the same. Katie & Katy sound similar BUT there are two different names so they are distinct which is important when you want to clean data. You don't want to change Katie to Katy because Katie is Katie ;) When to use SQL Server Soundex function? I must admit I don't often use Soundex function for data cleansing purposes myself as sound similarity is usually insufficient for most tasks I do; but there are exceptions when I could use this function. This function can be used when you have a list of values which give you unique sound code, so when you compare another word against he list you can find one match only (or no match). SQL Soundex Example Recently I have come across this question on Microsoft forum SQL Question and soundex as proposed solution the problem was that month is mispelled and contains odd numbers. The task was to get proper month out of the fields. Here are some example of incorrect input. Row 1: January300 Row 2: January398 Row 3: January Row 4: February40 Row 5: February20694 Row 6: Januuary 5454 One of the solution is to build a list of incorrect values and map it to correct month name which can be time consuming, another one is too take out number and spaces but it doesn't deal with mispelled months (row 6). So can we use soundex? Let's try it. First let's see if we have unique soundex code for all 12 months: SELECT SOUNDEX('January') as [Soundex month code] union all SELECT SOUNDEX('February') union all SELECT SOUNDEX('March') union all SELECT SOUNDEX('April') union all SELECT SOUNDEX('May') union all SELECT SOUNDEX('June') union all SELECT SOUNDEX('July') union all SELECT SOUNDEX('August') union all SELECT SOUNDEX('September') union all SELECT SOUNDEX('October') union all SELECT SOUNDEX('November') union all SELECT SOUNDEX('December') and the results are below. Left side is January to December, Right side is sorted by soundex code to see if we have the same soundex month codes. This looks good. So let's try to tidy up our list of incorrect values. SQL Soundex data cleansing Here is the code that we use to perform data cleansing using t-sql soundex function. select s.id, s.DirtyMonth,   case     when soundex('january') = soundex(s.DirtyMonth)       then 'January'     when soundex('feburary') = soundex(s.DirtyMonth)       then 'February'      -- and so on   else 'unknown'   end as CleanMonthName from (   Select 1 as ID, 'January300' as DirtyMonth Union All   Select 2, 'January398' Union All   Select 3, 'January' Union All   Select 4, 'February40' Union All   Select 5, 'February20694'Union All   Select 6, 'Januuary 5454') as s Below are results of the above SQL query: Success!! That worked fine.  IMPORTANT: Soundex worked fine in our case BUT be careful when you use it because it might not be so accurate with different examples of inaccurate data so ensure you test it on full set of invalid values. In our case anything that doesn't match month goes to 'unknown' so some kind of logic or process should be put in place to tidy up the exception. SQL Soundex final words: Soundex is not commonly used but sometimes it can help especially when we to do something quickly. Data cleansing in data warehouses project is generally done using fuzzy lookup and in SQL Server 2012 we have Master Data Services (master data management) and Data Quality Services which can help us with this kind of tasks.  If you want to use T-SQL for data cleansing check Beyond SoundEx - Functions for Fuzzy Searching in MS SQL Server SQL SUM Aggregate Function Introduction Function Name:  SUM Function - returns the total sum of a numeric column. Syntax:  SUM ( <<Input Value>> ) Arguments: <<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input.   SQL SUM Aggregate Function Example In this example I will use AdventureWorks2012 database dbo.FactInternetSales table. In this example I will show you how to use Sum function to return a TotalSalesAmount.   SQL COUNT Aggregate Function Introduction   Function Name:  COUNT Function - Is used to count rows in a table or count rows based using specific logic. Syntax:  COUNT (<<Input Field>>) Arguments: <<Input Field>> - Provide field name that will be used as your input. SQL COUNT Aggregate Function Example In this example I will use AdventureWorksDW2012 database, dbo.DimCustomer table and show you how to use COUNT aggregate function. In my select I put COUNT and inside the brackets I type asterisk (*) after the asterisk type new column name. COUNT(*) counts all rows in result set we get (which can be limited by WHERE clause) SQL MIN Aggregate Function Introduction Function Name: MIN Function - Returns the lowest available value based on the given set of rows. Syntax:  MIN ( <<Input Field>> ) Arguments:            <<Input Field>> - Provide Field name that will be used to perform the MIN function logic.   SQL MIN Aggregate Function Example In this example I will use AdventureWorks2012 database dbo.FactInternetSales table and show you how to use Min to display the smallest sales amount. You can see below that I used MIN function and provided SalesAmount field so my result is the smallest Sales Amount value. SQL MAX Aggregate Function Introduction Function Name: MAX Function - Returns the highest available value based on the given set of rows. Syntax:  MAX ( <<Input Field>> ) Arguments:            <<Input Field>> - Provide Field name that will be used to perform the MAX function logic.   SQL MAX Aggregate Function Example In this example I will use AdventureWorks2012 database dbo.FactInternetSales table and show you how to use Max to show the biggest sales amount. Below I used MAX function and used SalesAmount field so my result show the biggest Sales Amount. SQL AVG function Introduction Function Name:  AVG Function - returns the total sum of a numeric column. Syntax:  AVG ( <<Input Value>> ) Arguments: <<Input Value>> - Provide expression value, numeric or approximate value that will be used as your input.   SQL AVG function Example In this example I will use AdventureWorksDW2012 database, dbo.FactInternetSales table. In this example I will show you how to use AVG aggregate function. In a select I put Avg and inside the brackets I type star after the star I type new column name. T SQL DBA Create New Database INTRODUCTION  I Open Object Explorer and find Databases folder    CREATE NEW DATABASE using SSMS 2012  I click on Databses folder, and from drop down box I select New Databses. In the New Database window I type Database name in this case Test and click OK. I go back to the Databases folder and click Refresh. Now my Database Test is visible. SQL SERVER 2012 CREATE NWE DATABASE script Below I will show you how to create new database using script in SSMS 2012. I open Object Explorer,AdventureWorksDW2012 database and open new query. In the new query I type CREATE DATABASE DatabaseName statement. I click Execute to run the query In the Messages box I get information that my query was run successfully.  I go back to the Database folder to refreshed it. Now in the Object Explorer my new database Test is visible. sql server 2012 restore database SQL Server 2012 Editions: This tutorial is created using SQL Server 2012 Business Intelligence Edition but the steps should be the same in Enterprise edition and although we haven't tried it they should be the same (maybe with fewer options) in Express edition and standard edition of SQL Server 2012. SQL Server Versions: Please be aware that you can restore 2012 backup files and you should be able to restore previous versions (althought haven't tried yet) but what you cannot do is to use previous version of SQL for instance SQL Server 2008 and try to restore backup file that was created using SQL Server 2012. SQL Server 2012 Restore Backup file Open object explorer in the toolbar. Click connect icon. You will be presented with "connect to server" pop up. Ensure Server Type drop down box has "Database Engine" selected. Type server name whichin my case is localhost and I use shortcut dot "." Click Connect button. Now we can go to Databases folder and right click on it, from drop down box select Restore Database. ] Restore Database window should appear. I am in General section, In Source section I selected Device radio button and clicked on the icon with three dots. In the Select backup devices, click Add button  and now select Backup file location and file actual backup file. By default it should point to the correct path in most cases. NOTE: If you cannot see the .bak files it might be because they don't have .bak extension on your file. In such situations change bottom right drop down box from backup files to all files.  After you select the file; the file name appears in the File name text box at the bottom. Click OK  Once we selected backup file we can see the path in the Backup media section. Click OK In SQL Server 2012 after you click ok and certain fields are pre-populated. Please be aware that this new behaviour and you will most likely not see in previous version for example SQL Server 2008. Source // Database drop down box shows available databases in the backup file. Destination // Database drop down box shows database available in the server that you are connected to. You can type any database name and it will create it. NOTE: When you click Database drop down box in destination section. You will notice that the database name will automatically change and you won't be able to change it to original value using drop down box but you can still type it. Hopefully Microsoft will change this behaviour as it is not very intuitive. Backup sets to restore section contains all available backups. We have only full but sometimes you can have different backup types with multiple lines (we will try to write article and post link here. Remember to use search bar that will check hundreds of our articles). Click ok KNOWN ISSUES: If you don't see OK button; it might be because your screen resolution it too low. This issue seems to be more frequent with virtual machines that are usually on lower screen resolutions. To fix it try to increase the screen resolution. After we click Ok we get message box which in our case says "Database 'database name' restored successfully'" NOTE: When you perform the database restore and the database already exists than it cannot be in use, otherwise restore operation may fail. NOTE: You should see restored database under databases folder however if you use previous version of SQL Server you might have to right click databases folder and click refresh. sql server move database to a different drive Before we began let's clarify why I am doing this. In my case I have virtual drive that has 30GB drive and I am running low on disk space. My biggest files on C drive are SQL Server databases therefore I have decided to move them to F drive that has much more disk space. NOTE: This tutorial is applicable to certain development machines and should not be used on production servers as usually you require extra steps that involve "moving" folder permissions and all dependand processes. See below images that shows that I have 877MB left on my C drive and 17.7GB available on F Drive. My current database files (mdf = data & ldf = log file) are located in C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA which is default installation path and below you can see the biggest 3 databases that I will want to move from C drive to F drive. As you can see below my 3 biggest databases will give me extra 1GB free space on C drive. How to move databases to a different drive There are diffent ways to move a database from one drive to another one like backup / restore, T-SQL script but in our case we want the easiest and quickest one so we will use DETACH database method using SSMS and simply copy/paste files and ATTACH them back to the database instance. I opened SSMS, connected to my local instance of SQL Server right clicked one of the databases and selected TASKS and then clicked Detach. See below example. I got a detach database dialog box where I clicked ok.  NOTE: when you detach database you might noticed that the database does NOT disappear in solution explorer. This is because SSMS doesn't refresh the database list so you might click top level databases folder and click refresh and the detached database should disappear from the list. I have repeated DETACH operation for the remaining 2 database and now I'm ready to move my database files to new location which in my case is F:\Database\Data See below example that shows files in current C drive location and new location. Notice that one database has 2 kind of files mdf (=data) and ldf (=log file) so I will move all 6 files. I cut & pasted the files and below you can see that files in new F drive location. Now that we have files in new location it is time to ATTACH the database to server. To do that connect to your instance. Right click database folder and click attach. We are presented with attach database dialog box. I pressed Add button. Find my new F drive location with new files. Selected one database and clicked OK After clickng ok the dialog box was filled in with information. See example below. I have repeated the same ATTACH steps for the remaning files and below is screenshot of the disk space after I moved the database files to new drive. sql server shrink log file Usage: As a developer I often work with development environements and is it common task for me to either shrink a log after I perform database restore from live environement or just reduce my current log file. The common problem is that shrink command is not always shrinking the log file and and this tutorial I will cover this particular issue as well: NOTE: This method is used to permanently remove log data which is useful in development environment but it is not recommended in production environments. See below an example of a database with log file (ldf) that is slightly over 200MB.   In order to successfuly reduce the size of a log using shrink method we will have to make a change to database recovery model. To reduce log file to almost "zero" MB we need to change recovery model to SIMPLE. If you don't do that you might have an issue of shrink database not shrinking. I opened SSMS and connected to my local instance. I right clicked my database and selected properties. In Database properties I went to options page (on the left side) and you can see on the right side recovery model that in my case is set to FULL and which I will change to SIMPLE. See below example of my change. After the change I clicked ok. It's time to shrink our database. I right click the database go to Shrink and select files. See example below. A dialog box appeared and I changed File type to Log, select reorganize pages before releasing unsed space and shrink file to 0 mb. NOTE: Remember full shrink does not work when you are NOT in simple recovery mode so you might be left with much larger log file than 0 mb. Below you can see that my ldf which is log file was reduced to "zero" mb more precisely 1mb (1024KB). NOTE: Do you need script? Remember you can use SCRIPT option at the top of the dialog box to get SQL code. sql server 2012 add user login with sql server authentication n this step by step SQL Tutorial I will show you how to Add a new user login to SQL Server with SQL Server Authentication mode. For the purpose of this tutorial I will use SQL Server 2012. Prior knowledge: You should have some knowledge about security and difference between windows authentication vs SQL Server Authentication. We plan on writing blog posts about it and once we have it we will provide links in this tutorial. (If we forget use the search bar at the top). INTERNAL REVIEW: Katie please add a screenshot which shows that SQL SERVER is in Mixed Mode Authentication (create separate article) and add link here. Once SSMS is open I go to Object Explorer, Security, Logins folder. NOTE: There are two kinds of Security folders. Database level Security folder that exists in specific database under Databases Folder that we won't be using and Server Level Security folder that is located directly below "Databases" folder (see below).  To Add new user login right click Logins folder and click New Login... In the Login window I select SQL Server authentication. I type Login name and password and press OK. I get error message saying "Password validation failed. The password does not meet Windows policy requirements because it is not complex enough". NOTE: Password should be strong and there are a number of conditions you need to meet. One of them might be the need to use special characters like $. Avoid using generic passwords like Pa$$w0rd that unfortunatelly is used way too often! but when you don't have password management software or processes that often seems to be easiest option for some people...... until something bad happens ;). This practice is sometimes forgivable if you use it locally or on development box and the database is not exposed externally and in our case we will use "weak" password. To create "weak" password and avoid error message I untick Enforce password policy and click ok NOTE: Very often you will set some kind of permissions under Server Roles (data reader is popular), User Mapping or Securables. Once we write more tutorial we will provide links with more information (if we forget use the search bar). Now my new login is added and visible in the Logins folder. INTERNAL REVIEW: Katie please add screenshot how you can use the new login to log in. You can now try to connect to the server using SQL Server Authentication method and give it to users that require remote connection.  Problems with logging in are often related error 18456 for more information visit SQL Server Error 18456 sql server add linked server Note to myself.... Sometimes for development and testing purposes there is a need to add linked server using SQL Server that will actually point to itself so queries work properly with linked server name. Below is the quote that I always forget: EXEC sp_addlinkedserver @server=N'[LinkedServerName]', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'.'; SQL Server compressed backup tool MS SQL Compressed Backup tool Below is code that allows to perform restore of two compressed backups (same database) and moves it to new location: msbp.exe restore "local(path=Z:\DatabaseName.bak.gz;path=Z:\DatabaseName2.bak.gz;)" "gzip()" "db(database=DatabaseName;MOVE='DatabaseName'TO'C:\DatabaseName.mdf';MOVE='DatabaseName_log'TO'C:\DatabaseName.ldf';)" sql case sensitive string comparison In SQL Server when you compare strings sometimes 'A' equals 'a' and sometimes 'A' does not equal 'a'. This depends on collation setting. Collation decides how strings are compared. you can have many different collations but the most important factor is if collation you have is case sensitive or case insensitive. If one of the strings or fields is case sensitive than 'A' is not the same as 'a' but if both are case insensitive then 'A' equal 'a'. Below are several examples explaining case sensitivity strings comparisons and results. select CASE WHEN 'a'='a' THEN 'match' else 'doesn''t match' end The result of the above sql is always 'match' select CASE WHEN 'A'='a' THEN 'match' else 'doesn''t match' end The result of the above sql is 'match' if database collation set to case insensitive and 'doesn't match if collation is case sensitive. select CASE WHEN 'A' collate sql_latin1_General_CP1_cs_as ='a' THEN 'match' else 'doesn''t match' end If this case we force case sensitive collation using collate command and the results of the above query is always 'doesn't match' select CASE WHEN lower('A') collate sql_latin1_General_CP1_cs_as = lower('a') THEN 'match' else 'doesn''t match' end To compare two strings and ensure that are case insensitive I often use lower function on both sides. In the example above I enforced case sensitivity using collate command but I used lower on both sides hence made is case insensitive so sql result is 'match' You could use collate instead of lower function but I personally prefer lower function as it is easier to read. NOTE: If you use different collation you may suffer performance issues as query is not able to use index seek (based on my current knowledge). Bitmask in Sql Server - Explained In this tutorial I will explain what is a bitmask and how it can be used in SQL Server. For the purpose of this tutorial I will columns_updated (MSDN) function that can be used inside a trigger to detect if field was updated or not. Purpose of BitMask Before we delve into details let's discuss the purpose of BitMask. Imagine a situation where you have a number of values (or fields) and each of them is either true or false. For instance you can have: IsDelivered, IsShipped, IsProduced, IsTested. Values stored in these fields are very simple and are either true or false. BitMark is used to "combine" all of them into a single value and in order to do that in "encodes" the values which means you have to "decode" it to read it. Why would you do that? It is less readable! Yes but and it saves space and is more efficient and this is the main reason why you would use it. Another example I will give is a SQL Server trigger. Imagine a situation where you need to perform a certain action IF a certain field or fields have been updated. In order to do that you need to "detect" which fields have been updated. To do that you could check every single column (using update() function) however for applications that support high volume operations this might not be efficient and therefore doing a single comparison would be much more effective and this is where BitMask can help as it can provide you a single value that tells you which fields have been updated. BitMask basics To understand BitMask let's start with explaining the term BIT. BIT is a unit of information that can hold value either 0 or 1 (that's how computers work!). SQL Server will use stored bits in bytes. 1 byte = 8 bits below we have 8 bits with values: 1 bit 2 bit 3 bit 4 bit 5 bit 6 bit 7 bit 8 bit 0 0 1 0 1 0 0 1 1 0 0 1 1 0 1 0 So what does it mean? It means that in first example 3rd, 5th and 8th value is true and the rest is false. Can you "decode" last row? Let's go back to our example with detect which field is updated: 1 bit 2 bit 3 bit 4 bit 5 bit 6 bit 7 bit 8 bit  IsProduced  IsPaid  IsSent  IsCancelled  IsDelivered  IsReturned  IsRepaired  IsSatisfied 1 1 1 0 1 0 1 0 Can you see what 11101010 means, based on the above table? (Note 1 = True and 0 = false) BitMask Encoding 11101010 can be stored in field of varbinary datatype but additionally we need to assign a unique numeric value which will also be used to generate a combination which SUM result is always unique.  Below is a table that is used generate varbinary result and numbers that I assigned. Fields with value false are always - 00000000 Field 1 if true - 00000001 = 1 Field 2 if true - 00000010 = 2 Field 3 if true - 00000100 = 4 Field 4 if true - 00001000 = 8 Field 5 if true - 00010000 = 16 Field 6 if true - 00100000 = 32 Field 7 if true - 01000000 = 64 Field 8 if true - 10000000 = 128 To better understand it let's take our previous result which was 11101010 which I will reverse in accordance to the above table 01010111. so 01010111 = 1 + 2 + 4 + 16 + 64 = 87 87 is an important result as it can be ONLY created by the following combination of numbers 1 + 2 + 4 + 16 + 64 If SQL Server Columns_Updated() function returns 87 that means that field 1, 2, 3, 5, and 7 was updated. What if I have more than 8 values? In SQL Server 8 bits (values) = 1 Byte. If you need more than 8 values you can add extract bytes and when you extract data from varbinary field you can use substring function. First "character" is 1 byte (8 bits) and second character is field 9 to 16 and so on. SQL Server Compressed backup without enterprise edition Compressed backups (+ page, row compression) is part of sql server enterprise edition. To perform compressed backups (gzip) that saves a lot of space as it allows restore from gzip directly use the link below (non enterprise editions of sql server). SQL Server Compressed Backup Documentation Getting Started Examples Below are some examples to get you started using msbp.exe on the command line. msbp.exe must run on the same machine as SQL Server, due to how SQL Server dumps the data to msbp.exe. Basic Backup To backup to a standard SQL Server *.bak file, run the following command: msbp.exe backup "db(database=model)" "local(path=c:\model.bak)" Basic Restore To restore from a standard SQL Server *.bak file, run the following command: msbp.exe restore "local(path=c:\model.bak)" "db(database=model)" Compressed Backup Using the basic command above, you can add any number of plugins between the source and destination. For example, you may want to compress the data with the gzip plugin: msbp.exe backup "db(database=model)" "gzip()" "local(path=c:\model.bak.gz)" Compressed Restore And to restore compressed the file, insert "gzip()" in the middle again. Here the gzip plugin knows it is restoring the database, so it will uncompress the data: msbp.exe restore "local(path=c:\model.bak.gz)" "gzip()" "db(database=model)" Multithreaded Compressed Backup Using the compression command above, you can add any number of files. Since each file runs on its own thread, you can acheive multithreaded compression. For example: msbp.exe backup "db(database=model)" "gzip()" "local(path=c:\model1.bak.gz;path=c:\model2.bak.gz;)" T-SQL read registry Sometimes it is useful to read registry using t-sql from 'regedit' for instance to build dynamic values and SQL Server allows you to do that. See below an example EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', 'MSSQLServer' The extra step is to insert the values into variable and below I used temp table to do that declare @regValue as nvarchar(250) DROP TABLE #temp_table CREATE TABLE #temp_table (Value nvarchar(500), Data nvarchar(500)) INSERT INTO #temp_table EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', 'MSSQLServer' SELECT @regValue = Data FROM #temp_table select @regValue as MyData This example was created using SQL Server 2008 R2 and hopefully also works in 2008 and 2005 Microsoft sql server error 18456 login failed for user 18456 Error overview When connecting to Microsoft SQL Server and trying to use usually SQL Authentication method you may get event ID error 18456 login failed for user you provided. See below example. This usually means that your connection request was successfully received by the server name you specified but the server is not able to grant you access for a number of reasons and throws error: 18456. This eventID sometimes provides also state 1 which actually isn’t that useful as due to security reasons any error is converted to state 1 unless you can check logging on the server. Microsoft does not provide very useful message boxes so below are some explanations why you get the error. Below is a list of reasons and some brief explanation what to do: SQL Authentication not enabled: If you use SQL Login for the first time on SQL Server instance than very often error 18456 occurs because Windows Authentication (only) is set in Server properties (security section). To Access Server Properties, Open SSMS, go to Object Explorer pane (use view if you can't see it). Use the connect button to connect to database engine for your server. Once connected you will see it in object explorer. Right click server and click properties. Server Properties window will appear. See below screenshot that might be causing SQL login to fail You should set Server Authentication to SQL Server Windows Authentication Mode (see below). Once you select SQL Server Authentication you have to restart the server. Restart required: You have to restart the service when you change server authentication but bear in mind if someone hasn't restart the service this might be the reason why you get 18456. Remember service restart should be carefully planned on production servers as it will clear certain information (cache) and may impact performance of the server. NOTE: I recently tried to restart service using SSMS 2012 on Windows 7 and it didn't work.... actually the message below was hidden behind SSMS window and I just did not see it so ensure you get this message when you decide to restart the service.  Invalid userID: SQL Server is not able to find the specified UserID on the server you are trying to get. The most common cause is that this userID hasn’t been granted access on the server but this could be also a simple typo or you accidentally are trying to connect to different server (Typical if you use more than one server) Invalid password: Wrong password or just a typo. Remember that this username can have different passwords on different servers. less common errors: The userID might be disabled on the server. Windows login was provided for SQL Authentication (change to Windows Authentication. If you use SSMS you might have to run as different user to use this option). Password might have expired and probably several other reasons…. If you know of any other ones let me know. 18456 state 1 explanations: Usually Microsoft SQL Server will give you error state 1 which actually does not mean anything apart from that you have 18456 error. State 1 is used to hide actual state in order to protect the system, which to me makes sense. Below is a list with all different states and for more information about retrieving accurate states visit Understanding "login failed" (Error 18456) error messages in SQL Server 2005   ERROR STATE ERROR DESCRIPTION State 2 and State 5 Invalid userid State 6 Attempt to use a Windows login name with SQL Authentication State 7 Login disabled and password mismatch State 8 Password mismatch State 9 Invalid password State 11 and State 12     Valid login but server access failure State 13 SQL Server service paused State 18 Change password required SQL Server date and datetime error What is the issue? Try the following two statements. SELECT CAST('2012-09-04' AS DATETIME) SELECT CAST('2012-09-04' AS DATE) I would personally expect that they would produce the same date. In this case it is 2012 September 08 so I would expect SQL Server to convert the string to dates in exactly the same way. The result will actually depend on the Language your Login uses or you can set the language in current connection which I will use to demonstrate the issue. See below the same statement one executed using English another one executed using BRITISH language. SET LANGUAGE 'ENGLISH' SELECT CAST('2012-09-04' AS DATETIME) SELECT CAST('2012-09-04' AS DATE) SET LANGUAGE 'BRITISH' SELECT CAST('2012-09-04' AS DATETIME) SELECT CAST('2012-09-04' AS DATE) I would expect to get consistent results and 2012 September 04th. and the results are: Notice the red line. For language British and CAST as DATETIME we got 2012 April 09th! I classify it as a bug! So I provided exactly the same input string but for BRITISH (and some other European languages) behavior is different for CAST AS DATE vs CAST AS DATETIME. I classify it as a bug because there is no way we should get different date just because we cast it to DATETIME and not DATE. Obviously it might be difficult to fix it by Microsoft as it would potentially have negative impact on existing systems but I hope some kind of plan will be made to remove it (introduce new data type?) What are the risks? Risks are fairly high as your code might misbehave for instance if you use BRITISH and you use WHERE DateField = '2012-09-04' then that would actually return 2012 April 09! So depending on Login language you might get different result which means on Development box it might work. Code might have been tested and everything is ok and on Production it goes terrible wrong! just because login language is different. Date bug workaround Use YYYYMMDD instead of YYYY-MM-DD - That will actually work and is best practice when you provide hard coded values BUT in practice might be very difficult to monitor and ensure everyone uses this format. Make Login language consistent on each environment. This method ensure that you get exactly the same behavior so you can spot an error during testing on development environment (not only in production!) and you can do that by using Policy Management in SQL Server which I will describe in next article! A severe error occurred on the current command Hi My colleague Rob, come across recently a very odd sql server bug which gave him the following message A severe error occurred on the current command. The results, if any, should be discarded. He passed it to me and we managed to narrowed it down to this sql statement: SELECT cast('test' as nvarchar(10)) as MyColumn INTERSECT select 'test' ORDER BY MyColumn; The error occurs in SQL Server 2008 and SQL Server 2008 R2 build 10.50.1617.0 and it was already on Microsoft connect page raised as bug A severe error occurred on the current command. The results, if any, should be discarded. It seems this was fixed in SQL 11 which has not been released yet and potentially in the latest version of SQL Server 2008 R2. I'm going to install all updates to my R2 and see if I still get the severe error message and I will try it on SQL Server 2011 CTP3 as well (I will update the blog soon). Solution? Do not use cast in first query or use it in both cases or change the order! See below change of order which does work! SELECT cast('test' as nvarchar(10)) as MyColumn INTERSECT select 'test' ORDER BY MyColumn;