When you run Data Manipulation Language (DML) statements such as an INSERT, UPDATE, or DELETE, in most cases you will receive a message similar to the one below. But what if you want to see what records had just been modified? You could run a SELECT statement, but that wouldn’t return…
One of the questions I get frequently is what is the difference between using DELETE versus using TRUNCATE versus using DROP when working with data in tables. It basically comes down to what do you want to remove and what do you want to keep. First of all the…
In this demonstration we will be showing how to use IDENTITY columns in a table. First of all, an IDENTITY field is very similar to an auto number field, where the number will automatically be added as each record is inserted. By default, the value will start at 1 and then…
For this demonstration we will use the HR.Employees table that we created in the post on Table Constraints. We will begin with just a simple INSERT statement that will add one row into the table. First you may notice that we are not including a value for the EmpID field. This is because it is…
For this demonstration on Data Manipulation Languare (DML) statements we will create a table named dbo.TestTable with four columns. Then we will be using the INSERT statement to add records to the table, the UPDATE statement to modify existing records, and finally the DELETE statement to remove records from the table While it is…
In this demonstration we are going to look at working with batches. To begin our example we will need to run both the CREATE SCHEMA and CREATE TABLE statements to hold sample data. Then we will use two INSERT statements to add data. Notice we do not have a GO statement on…
When pulling data from more than one table you will need to JOIN the tables together. The JOIN types will determine what records will be returned in the result set. INNER JOIN – This type of JOIN will return only results that have a matching record in both tables. (For example, this will…
There are two types of DML Triggers that can be written for each INSERT, UPDATE, or DELETE statement. Either AFTER triggers or INSTEAD OF triggers. The main difference between the two is when the triggers are fired. The AFTER trigger will fire after the INSERT, UPDATE, or DELETE statement has been…
In a previous post we discussed the four part name of a SQL Server object in 2005 and later that included the SCHEMA as part of its name. There are many advantages to using schemas, but mainly to allow better and more flexible organization of objects as well as allowing for…
When creating tables you would want to use constraints to enforce data integrity. In the picture above you can see a list of the constraints and why they are used in your tables. Next we will demonstrate how to use these constraints in our code and on our HR.Employees table….