SQL Server provides some SQL Functions to manipulate the table result data. SQL Server Min() Function allows to retrieve the table result set based on the condition of smallest / low value . SQL Server Max() Function allows to retrieve the table result set based on the condition of largest / high value.

The below given are the SQL Server Min() and Max () examples.

SQL Server Min () Syntax:

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SQL Server Max() Syntax:

SELECT MAX(column_name)
FROM table_name
WHERE condition;

SELECT MIN(age)
FROM oracleappshelpUsers;
Output :
Age 
28

SELECT MIN(age)
FROM oracleappshelpUsers 
WHERE Status ='Pending';
Output: 
Age 
31

SELECT MAX(age)
FROM oracleappshelpUsers;
Output: 
Age
34

SELECT MAX(age)
FROM oracleappshelpUsers
where status ='New';
Output: 
Age
28

SELECT SQL Query is used to retrieved the result data from the SQL Server Database Table stored in the form of rows and columns.

Lets see the different usage of SELECT SQL Query for data retrieval. Consider the below table – oracleappshelpUsers.

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL IDENTITY PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
	Age 				INT 				CHECK (Age>=18),
	Status 				VARCHAR(20)			NOT NULL	DEFAULT 'NEW'	
)

Below given is the result set data for the table.

SELECT * 
FROM 
oracleappshelpUsers

SELECT UserId, GivenName, FamilyName
FROM 
oracleappshelpUsers

SQL Server Select DISTINCT result data

The use of DISTINCT keyword is applicable when DUPLICATE data rows to be removed. Consider the first example where we have 4 rows and Status -‘Closed’ is repeated twice.

If we need to get the result set from the data table to identify the UNIQUE count of values, then DISTINCT is useful.

SELECT DISTINCT Status 
FROM 
oracleappshelpUsers

SELECT COUNT(DISTINCT Status) FROM oracleappshelpUsers;
         OR 
SELECT Count(*) AS DistinctStatus
FROM (SELECT DISTINCT Status FROM oracleappshelpUsers);
Ouput - 3

SQL Server TOP SQL Query

The data retrieval could be required in different forms . We might have a requirement to get the data in the specific limit range. SQL Serer TOP statement could be useful in such cases and limits the number of rows to be returned as the result set.

But this does not depict the order of rows stored in the database thus ORDER BY clause is used with the TOP SQL Statement

SELECT TOP 10
From 
oracleappshelpUsers
Order By UserId Desc;

We have learnt about the UNIQUE and Primary Key values for the columns in our previous blogs.

SQL Server Not Null Constraint in the table

SQL Server Primary Key Constraint in the table

The Primary Key which is a UNIQUE field value needs to be inserted every time when a record is inserted into the SQL Server table. To initiate the value to be generated automatically , we make the use of AUTO INCREMENT feature provided by SQL Server.

SQL Server Auto Increment Default value . The Automatic generation number will start with the value of 1 with the increment of 1 every time whenever a record is inserted.

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL IDENTITY PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
	Age 				INT 				CHECK (Age>=18),
	Status 				VARCHAR(20)			NOT NULL	DEFAULT 'NEW'	
)

SQL Server Auto Increment can be provided with the custom value for start of specified Sequence value and Increment value . The Automatic generation number will start with the value of 1000 with the increment of 1 every time whenever a record is inserted.

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL IDENTITY(1000,1) PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
	Age 				INT 				CHECK (Age>=18),
	Status 				VARCHAR(20)			NOT NULL	DEFAULT 'NEW'
)

We have gone through the blogs to understand the Creation of Tables , Constraints to be applied, data to be inserted but it is also important to understand how the data retrieval will happen when you have thousands of lakhs of records in the table.

For that we should ensure that the data retrieval should be fast and thus INDEX comes into picture.

INDEXES are the tables columns combined together to let the table use them to fetch the data faster.

CREATE INDEX index_name
ON table_name (column1, column2, ...); 

The below given syntax removes the duplicate values

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...); 

Let take example of table – oracleappshelpUsers and create Index on the table

 CREATE INDEX indx_uname
 ON oracleappshelpUsers (FamilyName, GivenName);  

The SQL Query should be like

DROP INDEX oracleappshelpUsers.indx_uname; 

The blog covers the topic on default constraint in SQL Server which can be applied on Table column for validations.

SQL Server Check Constraint

Lets consider we need to put some validation on some columns like records should be inserted

if the Age is >= 18

if City =’New Delhi’

if Status =’New’

The SQL Server Check Constraint allows you to add these condition on the specific column .

CREATE TABLE oracleappshelpUsers
(
    UserId			INT 			NOT NULL UNIQUE,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
    Age 			INT 			CHECK (Age>=18)				
);

SQL Server Default Constraint

It is sometimes required to setup the DEFAULT value for a particular Column .

CREATE TABLE oracleappshelpUsers
(
    UserId			INT 			NOT NULL UNIQUE,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10)					,
    FamilyName 			VARCHAR(128) 		NOT NULL,
    Age 			INT 			CHECK (Age>=18),
    Status 			VARCHAR(20)		NOT NULL	DEFAULT 'NEW'	
);

The SQL Server Table might contain multiple fields but for the data Retrieval we need to consider an UNIQUE key through which the complete record row(s) can be fetched. The SQL Server RDBMS provides the concept of Primary Key (Unique field). It also helps in executing the queries faster.

Refer SQL Server Unique and Not Null Constraints in the previous blog – http://oracleappshelp.com/2020/04/22/sql-server-unique-not-null-constraint-in-table/

SQL Server Primary Key – Points to Consider

1. Primary Key should have UNIQUE field data
2. Primary Key CANNOT be NULL
3. Each Table Contains one Primary Key
4. Primary Key could be based on single column or combination of multiple columns.

CREATE TABLE oracleappshelpUsers
(
    UserId			INT 			NOT NULL PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL
)

The below example shows creation of PRIMARY KEY which includes column combination of UserId + GivenName

CREATE TABLE oracleappshelpUsers
(
    UserId			INT 			NOT NULL ,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL,
    CONSTRAINT PK_Users PRIMARY KEY (UserId,GivenName)
);

SQL Server Drop Constraint

The SQL Server Primary Key Constraint can be removed using the below SQL Query

ALTER TABLE oracleappshelpUsers
DROP INDEX PK_Users; 

SQL Server Foreign Key – Points to Consider

Foreign Key concept is related to the Reference of another table where the reference column is the PRIMARY KEY.

Foreign Key – Points to Consider

1. Foreign Key should be the Primary Key of the another table
2. The table which actually contains the Foreign Key column as Primary Key is the Parent Table
3. Foreign Key applies the linkage between two tables
4. The table which has reference of Column as Foreign Key is the Child Table or Reference Table

Let’s discuss in detail

We consider another table for storing the students data which also includes the field “UserId” from the table – oracleappshelpUsers

CREATE TABLE oracleappshelpStudents
(
    StudentId 		INT 			IDENTITY PRIMARY KEY,
    UserId 		INT 			FOREIGN KEY REFERENCES oracleappshelpUsers (UserId),
    Email 		VARCHAR(256) ,
    CreatedBy 	        VARCHAR(128) 		NOT NULL,
    UpdatedBy 	        VARCHAR(128) 		NOT NULL,
    CreatedDate	        DATETIME		NOT NULL,
    UpdatedDate		DATETIME		NOT NULL
)

Here in this example UserId is the PRIMARY KEY for the table oracleappshelpUsers but it is being referenced in the New table oracleappshelpStudents and thus becomes the FOREIGN KEY.

SQL Server Alter table Add Foreign Key Constraint

The Foreign Key can be added to the existing table by using the SQL Server ALTER Table command.

ALTER TABLE oracleappshelpStudents
ADD CONSTRAINT FK_UserId
FOREIGN KEY (UserId) REFERENCES oracleappshelpUsers (UserId); 

SQL Server Drop Foreign Key Constraint

The below SQL Server Query can be used to Drop the Foreign Key Constraint

ALTER TABLE oracleappshelpStudents
DROP FOREIGN KEY FK_UserId; 

We have covered the overview related to SQL Server Constraint in the blog – SQL Server Constraint in the table

Lets discuss each constraint in Detail

SQL Server NOT NULL Constraint

The SQL Server NOT NULL Constraint is applied on the Column level in the table. It depicts that the particular column where NOT NULL is applied, that column CANNOT have the NULL values.

Whenever a record is being inserted or updated in the table, this specific column should have the value always.

In some cases when we do not get value in the record, we can impose DEFAULT value also to ensure that record is always inserted with the value.

SQL Server UNIQUE Constraint

As the name suggest UNIQUE constraint applied on the Table Column ensures that all values stored in the column with Constraint UNIQUE have different values.

Creating the UNIQUE Constraint on Table Creation by specifying at column level

CREATE TABLE oracleappshelpUsers
(
    UserId				INT 				NOT NULL UNIQUE,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL
);

Creating the UNIQUE Constraint on Table Creation by adding CONSTRAINT as part of Table

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 				NOT NULL ,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL,
	CONSTRAINT UC_Users UNIQUE (UserId,GivenName)
);

SQL Server DROP Constraint

The below SQL Query allows to DROP the applied constraint on the table

ALTER TABLE oracleappshelpUsers
DROP INDEX UC_Users; 

A SQL Server Database could have multiple tables and each table that has to be stored based on some rules. These rules are the possible checks & conditions which allows you to ensure the data validations before it is being inserted into a specific table.

SQL Server Constraints Types

SQL server constraints can be divided primarily into :

1. SQL Server Table level constraints
2. SQL Server Column level constraints

SQL Server Constraints can be applied :

1. Create Table
2. Alter Table

Syntax: SQL Server ADD Constraints in Table

CREATE TABLE table_name (
table_col1 datatype constraint,
table_col2 datatype constraint,
table_col3 datatype constraint,n
….

table_col3 datatype constraint
);

CREATE TABLE oracleappshelpStudents
( StudentId INT IDENTITY PRIMARY KEY,
UserId INT REFERENCES oracleappshelpUsers (UserId),
Email VARCHAR(256) ,
CreatedBy VARCHAR(128) NOT NULL,
UpdatedBy VARCHAR(128) NOT NULL,
CreatedDate DATETIME NOT NULL,
UpdatedDate DATETIME NOT NULL )

We will be discussing in separate blog for each Constraint . Please refer the below given links

SQL Server Not Null Constraint in the table

SQL Server Primary Key / Foreign Key Constraint in the table

SQL Server Default Constraint Check

We have learnt How to Create SQL Server Database in previous blog –SQL Server Database Operations .  

The blog covers the topic on  how we can create table in SQL Server, alter table in SQL Server and drop table in SQL Server.

CREATE TABLE in our user defined database oracleappshelpDB. Lets consider we need to store the online users for the website – http://oracleappshelp.com

SQL Server Create Table Syntax:

CREATE TABLE tableName
(
Table_col_1 datatype [ NULL | NOT NULL ],
Table_col_2 datatype [ NULL | NOT NULL ],
…
Table_col_n datatype [ NULL | NOT NULL ]
);

WHERE
Table_col_1, Table_col_2, …Table_col_n depicts the NAMES of the Column in the TABLE
datatype depicts the type of data to be stored in that column
[ NULL | NOT NULL ] depicts of the column value could be NULL or NOT NULL (mandate)

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 							IDENTITY PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL
)

Let’s enter the sample data using SQL Server Query. For that we need to use the INSERT SQL Query. Here is the syntax for that

INSERT INTO tableName
(Table_col_1, Table_col_2, …Table_col_n )
VALUES
(Table_col_value_1, Table_col_value_1, … Table_col_value_n),
(Table_col_value_1, Table_col_value_2, … Table_col_value_n),
…;

WHERE

Table_col_value_1 depicts the value for the first column of the table

Table_col_value_2 depicts the value for the second column of the table and so on..

Insert into oracleappshelpUsers values (1, 'Rohit', 'Kumar','Srivastava' );
Insert into oracleappshelpUsers values (2, 'Mohit', '','Sharma' );
Insert into oracleappshelpUsers values (3, 'Rajeev', 'Kumar','Jain' );
Insert into oracleappshelpUsers values (4, 'Snehashish', '','Nayak' );

Create New SQL Server Table from Existing Table

There could be need where you need to create a new SQL Server Table but it should have the same data elements / Columns. The below given SQL syntax can be used

Syntax – Create Table from existing Table

CREATE TABLE new_sql_server_table_name AS
SELECT Table_col_1, Table_col_2,…Table_col_n
FROM existing_table_name;

WHERE
Table_col_1, Table_col_2, …Table_col_n depicts the NAMES of the Column in the existing TABLE

CREATE TABLE oracleappshelpUsers_backup AS
SELECT UserId, GivenName,FamilyName
FROM oracleappshelpUsers;

New Table using SQL Server Management Studio

Please find below the steps for New Table using SQL Server Management Studio.

1. Connect to Microsoft SQL Server Instance
2. Expand the Object Explorer and select the Database oracleappshelpDB
3. Right click on Table -> New -> Table
4. Enter the Table Column Name , data type value
5. Select if column is to be allowed as NULL or NOT NULL
6. Save the table

SQL Server ALTER Table

This is again very common to add New Columns or to change the data type for the columns in the existing table. Below given is the syntax to ALTER the table as per need.

SQL Server ALTER Table – Add Column

Alter Table Syntax :

ALTER TABLE table_name
ADD column_name datatype [ NULL | NOT NULL ];

ALTER TABLE oracleappshelpUsers
ADD  DOB DATE  NOT NULL ;

Lets say if we need to have a column to store the Flag value for the users who are deleted or not . Then below SQL could be used by adding DEFAULT value.

ALTER TABLE oracleappshelpUsers
ADD  DELETED Varchar(1)  NOT NULL  DEFAULT 'N';

SQL Server Alter Table Drop Column

Lets say we added the column NICKNAME in the Table oracleappshelpUsers but it is not required as per the Database finalization .

Syntax – Alter Table Drop Column

ALTER TABLE table_name
DROP COLUMN column_name

SQL – Alter Table Drop Column

ALTER TABLE oracleappshelpUsers
DROP COLUMN  NICKNAME

SQL Server Drop Table

Below given is the syntax and SQL Query for dropping the SQL Server Table.

Syntax – SQL Server Drop Table

Drop Table <table_name>

 Drop Table oracleappshelpUsers

SQL Server Truncate Table

Below given is the syntax and SQL Query for truncating the SQL Server Table. This SQL command allows to DELETE the data from the Table but Table remains there.

Syntax – SQL Server Truncate Table

Truncate Table <table_name>

Truncate Table <table_name>

Truncate Table oracleappshelpUsers

Below given is the complete Table with all columns having CONSTRAINTS.

CREATE TABLE oracleappshelpUsers
(
    UserId				 INT 							IDENTITY PRIMARY KEY,
    GivenName 			VARCHAR(128) 		NOT NULL,
    MiddelName 			VARCHAR(10),
    FamilyName 			VARCHAR(128) 		NOT NULL,
    DOB 				DATE 				NOT NULL,
	Address1 			VARCHAR(255) 		NOT NULL,
	Address2 			VARCHAR(255) 		NOT NULL,
	City	 			VARCHAR(100) 		NOT NULL,
	State 	 			VARCHAR(100) 		NOT NULL,
	Pincode				INT							,
	CreatedBy 			VARCHAR(128) 		NOT NULL,
	UpdatedBy 			VARCHAR(128) 		NOT NULL,
	CreatedDate			DATETIME			NOT NULL,
	UpdatedDate			DATETIME			NOT NULL	
)