Constraints in SQL Server

Constraints in SQL Server play a big role in keeping data accurate in a database.

Within SQL Server there are several methods of doing this and one way is using constraints

• NOT NULL - Indicates that a column cannot store NULL value
• UNIQUE - Ensures that each row for a column must have a unique value
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value when specified none for this column

This is the code to assign a NOT NULL constraint to a Column. Column1 doesn't allow NULLS, Column2 allows NULLS.

CREATE TABLE TestTable1

(

PKID int NOT NULL,

Column1 varchar(255) NOT NULL,

Column2 varchar(255)

)

This is the code to assign UNIQUE constraint to a Column

CREATE TABLE TestTable2

(

PKID int NOT NULL UNIQUE,

Column1 varchar(255) NOT NULL,

Column2 varchar(255)

)

This is the code to assign Primary Key constraint to a column

CREATE TABLE TestTable3

(

PKID int PRIMARY KEY,

Column1 varchar(255) NOT NULL,

Column2 varchar(255)

)

If you assign a primary key to a column, it automatically gains a unique constraint. You can have many unique constraints on a table, but you can only have 1 primary key. Each table should have a primary key and primary keys cant have null values.

This is the code to assign a foreign key constraint to a column

CREATE TABLE TestTable4

(

P_Id int PRIMARY KEY,

PKID2 int NOT NULL,

Column1 int NOT NULL,

CONSTRAINT FK_CON FOREIGN KEY (P_Id)

REFERENCES TestTable3(PKID)

)

A foreign key constraint is used to make sure that data can't be deleted if it matches data in another table.

This is the code to assign a CHECK constraint to a column

CREATE TABLE TestTable5
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL,
CONSTRAINT chk_Column1 CHECK (Column1> 0 AND Column2='Test')

)

This makes sure that the value in Column1 is greater than 0 and Column2 contains the word 'Test'

This is the code to assign DEFAULT constraint to a column

CREATE TABLE TestTable6
(
PKID int NOT NULL,
Column1 INT,
Column2 VARCHAR(50) NOT NULL DEFAULT ('Test'),

)

If a value is not entered in column2, the word 'Test' will be inserted.

CROSS APPLY vs INNER JOIN vs LEFT JOIN vs OUTER APPLY

Like Me on Facebook - https://www.facebook.com/sqlserverdev

So you want to improve your SQL skills and have come across some new operators and wondering when you should use them.

In this blog, I will focus on the following operators in the simplest terms and show you how to use them.

CROSS APPLY and INNER JOIN is very similar and
OUTER APPLY and LEFT JOIN is very similar.

In certain situations, you can write queries using CROSS APPLY or INNER JOIN and using OUTER APPLY and LEFT JOIN and they could bring back the exact results.

The only real difference that CROSS APPLY and OUTER APPLY is better than LEFT and INNER JOIN is when using a function or you want to write an advanced SQL Command to bring back certain results

Run the following script in a SQL Server Environment to see these operators in action.

You can examine the script to see how they differ and you should be able to play around with the script to fully understand what is going on.

create table Company (

    companyId int identity(1,1)

,   companyName varchar(100)

,   zipcode varchar(10) 

,   constraint PK_Company primary key (companyId)

)

GO

create table Person (

    personId int identity(1,1)

,   personName varchar(100)

,   companyId int

,   insertdate DATETIME

,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)

,   constraint PK_Person primary key (personId)

)

GO

insert Company

select 'ABC Company', '19808' union

select 'XYZ Company', '08534' union

select '123 Company', '10016' 

insert Company

select 'No Employees', '11111' 

insert Person

select 'Alan', 1, '20141011 09:00:00' union

select 'Bobby', 1,'20141011 10:00:00' union

select 'Chris', 1, '20141011 11:00:00' union

select 'Xavier', 2, '20141011 12:00:00' union

select 'Yoshi', 2, '20141011 13:00:00' union

select 'Zambrano', 2, '20141011 14:00:00' union

select 'Player 1', 3, '20141011 15:00:00' union

select 'Player 2', 3, '20141011 16:00:00' union

select 'Player 3', 3, '20141011 17:00:00'

/* using CROSS APPLY */

select *

from Person P

cross apply (

    select *

    from Company C

    where p.companyid = c.companyId

) CompanyPerson

/* the equivalent query using INNER JOIN */

select *

from Person p

inner join Company c on p.companyid = c.companyId

/* using CROSS APPLY - brings back the oldest inserted person by company*/

select *

from  Company c 

cross apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

order by insertdate

) CompanyPerson

/* using OUTER APPLY - brings back all the companies and the oldest inserted person by company*/

select *

from  Company c 

outer apply (

    select top (1) *

    from Person p

    where p.companyid = c.companyId

order by insertdate

) CompanyPerson

order by companyName desc

/*  brings back all the companies and the people */

select *

from company c

left join person p on p.companyid = c.companyId

order by companyName desc

The script produces the following results

Like Me on Facebook - https://www.facebook.com/sqlserverdev

COALESCE vs ISNULL

Like Me on Facebook - https://www.facebook.com/sqlserverdev

So you have come across a problem in a SQL Server query where you want to find out if a value is null and if it is, use another value. Which function should you use, ISNULL or COALESCE?

Well if you just want to do something simple and to evaluate a value to see if its NULL and use another value, then both commands would work if the other number is NOT NULL.

If you wanted to do something more advanced and compare a series of numbers and use the first number that is not null, then COALESCE is the function that you have to use.
Run the code into a SQL Server Environment to see how COALESCE works

SET NOCOUNT ON;
GO
USE master;
IF EXISTS (SELECT name FROM sys.tables
      WHERE name = 'wages')
   DROP TABLE wages;
GO
CREATE TABLE wages
(
   emp_id      tinyint    identity,
   hourly_wage   decimal   NULL,
   salary      decimal    NULL,
   commission   decimal   NULL,
   num_sales   tinyint   NULL
);
GO
INSERT wages VALUES(10.00, NULL, NULL, NULL);
INSERT wages VALUES(20.00, NULL, NULL, NULL);
INSERT wages VALUES(30.00, NULL, NULL, NULL);
INSERT wages VALUES(40.00, NULL, NULL, NULL);
INSERT wages VALUES(NULL, 10000.00, NULL, NULL);
INSERT wages VALUES(NULL, 20000.00, NULL, NULL);
INSERT wages VALUES(NULL, 30000.00, NULL, NULL);
INSERT wages VALUES(NULL, 40000.00, NULL, NULL);
INSERT wages VALUES(NULL, NULL, 15000, 3);
INSERT wages VALUES(NULL, NULL, 25000, 2);
INSERT wages VALUES(NULL, NULL, 20000, 6);
INSERT wages VALUES(NULL, NULL, 14000, 4);
GO
SET NOCOUNT OFF;
GO
SELECT CAST(COALESCE(hourly_wage * 10 * 10, salary, commission * num_sales) AS money) AS 'Total Salary'
FROM wages;

There are some things to think about when using COALESCE, if you want to evaluate a series of numbers and all the values are NULL, at least one has to be a typed NULL

e.g

SELECT COALESCE(NULL, NULL);

will result in 

Msg 4127, Level 16, State 1, Line 1
     At least one of the arguments to COALESCE must be an
     expression that is not the NULL constant.

but this will work

SELECT COALESCE(CAST(NULL AS INT), NULL);

Using ISNULL does not error the code so this will work
SELECT ISNULL(NULL, NULL);

There are some minor differences between ISNULL and COALESCE and can act differently in different scenarios that you have to think about.

Like Me on Facebook - https://www.facebook.com/sqlserverdev

SQL Server Development - Facebook Page

Hi,

You can follow more updates on this facebook page.

It a page about SQL Server Development. It will look at 2008, 2012 and 2014 development tips and tricks.

https://www.facebook.com/sqlserverdev

Installing SQL Server 2008 R2

If you have installed SQL Server 2008 before then the R2 version has the exact same process.

To download the Microsoft SQL Server 2008 R2 August CTP edition go here

http://technet.microsoft.com/en-us/evalcenter/ee315247.aspx

Beware a SQL Server 2008 R2 Management Studio is not included in the Express edition. To get the Management Studio, you need to download the the Enterpise Edition.

The system requirements to install the software are as follows
System Requirements

• Supported Operating Systems: Windows 7; Windows Server 2003 Service Pack 2; Windows Server 2008; Windows Server 2008 R2; Windows Vista Service Pack 2; Windows XP Service Pack 3
• 32-bit systems Computer with Intel or compatible 1GHz or faster processor (2 GHz or faster is recommended.)
• 64-bit systems 1.4 GHz or faster processor Minimum of 512 MB of RAM (2 GB or more is recommended.)
• 2.2 GB of available hard disk space

Awarded the MCITP Database Developer 2008 Certification

Been very busy on other stuff lately to update this blog and one of them was revising for the MCITP exam. I finally got round to taking and passing the MCITP Database Developer 2008 exam with a score of 96%.

Really pleased with this.

SQL Server Best Practises

If you are looking for a really good guide on SQL Server best practises, look here

http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx