Explain SQL Server JOINs with examples?

I will explain types of SQL JOINs in in this article. JOINs in SQL Server can be classified as follows:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN

Each type is explained below with suitable examples:
Let’s consider two tables as Table1 & Table2 for our example.

– CREATE TEST TABLES
CREATE TABLE Table1(ID [int], Code [varchar](10));
GO
CREATE TABLE Table2(ID [int], Amount [int]);
GO


– INSERT DUMMY DATA
INSERT INTO Table1 VALUES
(1,’AAA’),(2,’BBB’),(3,’CCC’)
GO
INSERT INTO Table2 VALUES
(1,50),(2,30),(4,40)
GO

INNER JOIN:
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Below is the query for inner join:

SELECT *
FROM Table1 A
INNER JOIN Table2 B
   ON A.ID = B.ID
GO

LEFT OUTER JOIN
Left Outer joins return all rows from the left table referenced with a left outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Left Outer Join:

SELECT *
FROM Table1 A
LEFT OUTER JOIN Table2 B
   ON A.ID = B.ID
GO

RIGHT OUTER JOIN
Right Outer joins return all rows from the right table referenced with a right outer join and matching rows from other table. Unmatched records will be NULL. Below is the query for Right Outer Join:

SELECT *
FROM Table1 A
RIGHT OUTER JOIN Table2 B
ON A.ID = B.ID
GO

FULL OUTER JOIN
Full Outer joins return all rows from both the tables. Unmatched records will be NULL. Below is the query for Full Outer Join:

SELECT * FROM Table1 A
FULL OUTER JOIN Table2 B
ON A.ID = B.ID
GO

CROSS JOIN
In cross joins, each row from first table joins with all the rows of another table. If there are m rows from Table1 and n rows from Table2 then result set of these tables will have m*n rows. Below is the query for

SELECT * FROM Table1 A
CROSS JOIN Table2 B
GO

Adding a DEFAULT constraint to an existing column

• DEFAULT Constraint can be added when the table is created, as part of the table definition.
• DEFAULT Constraint can be added to an existing table

How to CREATE DEFAULT Constraint on existing table?

To explain this, First I will create a table and then inserts a record (value into the first column and NULL into second column).

CREATE TABLE dbo.TestDefault(

column1 [int],

column2 [int]
)
GO
INSERT INTO dbo.TestDefault (column1) VALUES (10)
GO

Now I will add a DEFAULT constraint to the second column using below T-SQL code:

ALTER TABLE dbo.TestDefault

ADD CONSTRAINT TestDefault_DC_Columns2

DEFAULT 99 FOR column2
GO

To verify whether the default is applied or not, another value is inserted into the first column and the table is queried:
INSERT INTO dbo.TestDefault (column1) VALUES (20)
GO
SELECT * FROM TestDefault
GO 

 

What is the Difference between TRUNCATE and DELETE commands?

DELETE & TRUNCATE both are intended to delete data from the database tables. Both the commands can only remove the data from the tables but can not remove the table from the database - in other words, can't drop the structure of the table. Since both commands are used only to delete the data from the table, there should be obvious differences between thsese commands in aspects like T-SQL syntax, behavious, performance etc. Below are the differences between DELETE & TRUNCATE commands:

DELETE

• T-SQL Command: 
DELETE FROM [TableName] WHERE [Condition]
• DELETE is DML command hence it can be rolled back.
• A WHERE clause can be used to provide the facility of conditional-based deletion
• Triggers can be fired
• When we use delete command on a table having IDENTITY column, it will not reset the counter of  identity column. Instead, it maintains the same counter for new rows.
• DELETE is slower than Truncate because it logs deleted data into transaction log files. DELETE command removes one row at a time and logged into transaction log files. 

TRUNCATE

• T-SQL Command:
TRUNCATE TABLE [TableName]
• TRUNCATE is DDL command hence it can't be rolled back.
• A WHERE clause can't be used for conditional-based deletion. It removes entire rows at once.
• Triggers will not be fired - because Truncate doesn't remove any row, it just deallocates the data pages
• When we use truncate command on a table having IDENTITY column, it will reset the counter used by identity column to the seed value defined for the column.
• TRUCATE is faster than DELETE because it removes data directly without copying into transaction log files. In fact, TRUNCATE is also a logged operation but it logs the deallocation of the data pages in which the data exists - means your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. Thats why it is faster than DELETE command.
• Can't use truncate command if table has foreign key constraint, even if the referring table has no records. You will get below error:
Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.
• Can't use truncate command if table has a column that participate in Indexed view.

How to ADD CHECK CONSTRAINTS on existing table?

Adding CHECK constraint to an existing column
The following T-SQL code adds a constraint to an existing column in the table. Option WITH CHECK is used to to apply a check against existing rows. WITH CHECK option is optional in the code because it is defalut check option.

ALTER TABLE TestPK
WITH CHECK --Optional
ADD CONSTRAINT TestPK_CHECK_DoB
CHECK (DoB < DateAdd(yy,-20,GetDate()))
 
If any existing value violates the constraint, we get following error:
Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the CHECK constraint "ConstraintName". The conflict occurred in database "DBName", table "dbo.TableName", column 'ColumnName'.
 
Adding CHECK constraint WITH NOCHECK
The following T-SQL code adds a constraint to an existing column in the table with nocheck option. I assume that the column has a value that violates the constraint. Therefore, WITH NOCHECK is used to prevent the constraint from being validated against existing rows, and to allow for the constraint to be added

ALTER TABLE TestPK WITH NOCHECK
ADD CONSTRAINT TestPK_CHECK_DoB
CHECK (DoB < DateAdd(yy,-20,GetDate()))

T-SQL Query to Calculate Month End Date

How to calculate Month End Date in one liner query?

Here is the easiest way to calculate Month End Date for any given date:

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)-1 AS MonthEndDate

Example:
If you replace GetDate() with any date, above query will return the Month End Date for that particular month.
If GetDate() value is '2010-01-25' then Output will be '2010-01-31'
If GetDate() value is '2010-02-20' then Output will be '2010-02-28'

T-SQL Query to Find Nth Largest number

How to find Nth Highest number using SQL query?

This is very simple to achieve by using Ranking Functions. Below is the answer of this query:

-- PREPARE TEST DATA
DECLARE @T TABLE (Amount int)
INSERT INTO @T VALUES
(101),(120),(14),(110),(930),(310),
(12),(104),(330),(423),(110),(10)


DECLARE @N int
SET @N = 5 -- SET Nth Number

-- ACTUAL QUERY
SELECT [Rank],Amount FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Amount DESC) [Rank]
    ,Amount FROM @T) AS Temp
WHERE [Rank] = @N

How to Create FOREIGN KEY Constraint by Using WITH NOCHECK

When a FOREIGN KEY constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure that all values, except NULL, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint.
Database Engine will throw below error if any value is missing in the referenced column:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "NameOfConstraint". The conflict occurred in database "DBName", table "dbo.TableName", column 'ColumnName'.

However, by specifying WITH NOCHECK, the Database Engine can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column. The WITH NOCHECK option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward.

ALTER TABLE TestFK WITH NOCHECK
ADD CONSTRAINT TestFK_IDFK FOREIGN KEY(IDFK)
REFERENCES TestPK (ID)

Note: You should be very careful while adding a constraint WITH NOCHECK option because this bypasses the controls in the Database Engine that enforce the data integrity of the table.

How to CREATE Foreign Key on existing table?

• We can create a FOREIGN KEY constraint as part of the table definition when we create a table.
• If a table already exists, we can add a FOREIGN KEY constraint, provided that the FOREIGN KEY constraint is linked to an existing PRIMARY KEY constraints or UNIQUE constraint in any another or the same table.

Only 253 FOREIGN KEY constraint can be added to a table in SQL Server 2008. Click here to see the maximum sizes and numbers of various Database Objects defined in SQL Server 2008.

Creating FOREIGN Key on existing table
To explain this, I'll create a table TestFK without any Foreign Key. I will use Table TestPK to reference the Foreign Key. Below is T-SQL to create a TestFK table:

IF OBJECT_ID('TestFK','U') IS NOT NULL
DROP TABLE TestFK
GO
CREATE TABLE TestFK (
   [IDFK] [int] NULL,
   [Date] [datetime] DEFAULT (GETDATE())
)
GO
INSERT INTO TestFK (IDFK) VALUES
(1),(2),(1),(NULL),(2),(NULL),(3)
GO
SELECT [IDFK],[Date] FROM TestFK (NOLOCK)

Now ALTER the table to ADD Foreign Key
Execute below T-SQL code to add foreign key:

ALTER TABLE TestFK
ADD CONSTRAINT TestFK_IDFK FOREIGN KEY (IDFK)
REFERENCES TestPK (ID)

How to CREATE Primary Key on existing table?

Only single PRIMARY KEY constraint can be added to a table.

• Primary Key can be added when the table is created, as part of the table definition.
• Primary Key can be added to an existing table, provided that no other PRIMARY KEY constraint already exists.

How to CREATE Primary Key on existing table?

To explain this, I'll create a table without Primary Key constraint. Below is T-SQL to create a TestPK table:

IF OBJECT_ID('TestPK','U') IS NOT NULL
DROP TABLE TestPK
GO
CREATE TABLE TestPK (
    [ID] [int] NOT NULL,
    [Name] [varchar](50),
    [DoB] [date],
    [DeptNo] [tinyint]
)
GO
INSERT INTO TestPK (ID,Name,DoB,DeptNo) VALUES
(1,'Name1','1983-04-07',1),
(2,'Name2','1992-10-21',3),
(3,'Name3','1981-02-17',1),
(4,'Name4','1975-11-23',2)
GO
SELECT ID,Name,DoB,DeptNo FROM TestPK (NOLOCK)


Now ALTER the table to ADD Primary Key
Execute below T-SQL code to add primary key:

ALTER TABLE TestPK
ADD CONSTRAINT TestPK_PrimaryKey_ID PRIMARY KEY (ID)

Note: Before adding a PRIMARY KEY constraint to an existing column(s) in the table, ensure that the existing data follows the rules for primary keys:
1. No NULL values  2. No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.

Maximum Capacity Specifications for Database Objects in SQL Server 2008 R2

The following table specify the maximum sizes and numbers of various Database Objects defined in SQL Server 2008 components:

Database object	Maximum sizes/numbers (32-bit)	Maximum sizes/numbers (64-bit)
Columns per index	16	16
Columns per Primary Key	16	16
Columns per Foreign Key	16	16
Columns per SELECT statement	4096	4096
Columns per INSERT statement	4096	4096
Database Size	524,272 TB	524,272 TB
No of Databases per SQL Server instance	32,767	32,767
Files per database	32,767	32,767
Maximum File size (data)	16 TB	16 TB
Maximum File size (log)	2 TB	2 TB
Foreign key references per table	253	253
Identifier length in characters	128	128
Nested stored procedure levels	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
REFERENCES per table	253	253
Nonclustered indexes per table	999	999
xml indexes per table	249	249
Parameters per stored procedure	2100	2100
Parameters per user-defined functions	2100	2100
Partitions per partitioned table or index	1000	1000
Btach Size	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per GROUP BY, ORDER BY	8,060	8,060
Bytes per key (PK, FK, Index)	900	900
Bytes per row	8,060	8,060
Bytes in source text of a stored procedure	lesser than batch size or 250 MB	lesser than batch size or 250 MB
Bytes per varchar(max), xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max) column	2^30-1	2^30-1

Reference: BOL, here is link

Explain Candidate Key, Alternate Key, and Composite Key

A Candidate Key is one that can uniquely identify each row of a table.
Generally a candidate key becomes the Primary Key of the table.

If the table has more than one Candidate Key, one of them will become the Primary Key, and the rest are called Alternate Keys.

A key formed by combining at least two or more columns is called Composite Key.

What's the difference between a primary key and a unique key constraints?

Both Primary Key and Unique Key enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default.

Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL.

Primary key:

1. Primary key used to uniqly identify each row in Table.
2. Primary key does not allow duplicate values and Null values.
3. Primary key is default Clustered Indexes
4. One table can have only one Primary key.

 Unique Key:

1. Unique Key is used to uniqly identify each row in Table.
2. Unique Key does not allow duplicate values but allowes only one Null value.
3. Unique Key is default Non- Clustered Indexe
4. One table can have more than one Unique Keys.

T-SQL command to CREATE a Table

Below is the CREATE TABLE - SQL Command:
CREATE TABLE [TableName]
(
   [Field Name1] [dataType] [(nFieldWidth [, nPrecision])] [NULL | NOT NULL]
   [CHECK Expression]
   [DEFAULT Expression]
   [PRIMARY KEY | UNIQUE]
   [REFERENCES TableName2 (FieldsName)]
   [, FieldName2 ... ]
) ON [PRIMARY]

Below is the example of Employee table:
CREATE TABLE [Employee]
(
   [EmployeeID] [int] NOT NULL PRIMARY KEY,
   [FirstName] [varchar](30) NOT NULL,
   [MiddleName] [varchar](30) NULL,
   [LastName] [varchar](30) NOT NULL,
   [DateOfBirth] [datetime] NOT NULL CHECK ([DateOfBirth]< DateAdd(yy,-20,GETDATE())),
   [DateOfJoining] [datetime] NOT NULL,
   [DeptNo] [smallint] NOT NULL REFERENCES [Department] ([DeptNo]),
   [Manager] [int] NULL REFERENCES [Employee] ([EmployeeID]),
   [Gender] [CHAR](1) NOT NULL DEFAULT ('M')
) ON [PRIMARY]

How to Create Database using T-SQL command?

Below are the T-SQL commands to create database

1. Default command:
This command will create database files in default directory.

USE [master]
GO
IF DB_ID(N'TestDB') IS NULL
CREATE DATABASE [TestDB]
GO

2. Command to specify File name, path, and size:
This command will create database files in the directory mentioned mentioned in the T-SQL command.


USE [master]
GO
IF DB_ID(N'TestDB') IS NULL
BEGIN
CREATE DATABASE [TestDB] ON PRIMARY
(
   NAME = N'TestDB'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.mdf'
   ,SIZE = 1024KB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 1024KB
)
LOG ON
(
   NAME = N'TestDB_log'
   ,FILENAME = N'C:\MSSQL\Data\TestDB.ldf'
   ,SIZE = 1024KB
   ,MAXSIZE = 2048GB
   ,FILEGROWTH = 10%
)
END
GO