SQL Server Functions Min() and Max() on table result set

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;

UserIdGiveNameMiddleNameFamilyNameAgeStatus
1Mohit Sharma 28New
2Rohit Kumar Jain 34Closed
3Snehashish Gupta 31Pending
4Rajeev Malhotra 34Closed
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


SQL Server SELECT, SELECT TOP , SELECT DISTINCT Queries

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
UserIdGiveNameMiddleNameFamilyNameAgeStatus
1Mohit Sharma 28New
2Rohit Kumar Jain 34Closed
3Snehashish Gupta 31Pending
4Rajeev Malhotra 34Closed
SELECT UserId, GivenName, FamilyName
FROM 
oracleappshelpUsers
UserIdGivenNameFamilyName
1Mohit Sharma
2Rohit Jain
3Snehashish Gupta
4Rajeev Malhotra

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
New
Closed
Pending

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;


SQL Server Auto Increment Column value in the table

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'
)


SQL Server Create and Drop Index on table

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; 

SQL Server Default Constraint Check

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'	
);


SQL Server Primary Key , Foreign Key Constraints in the table

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; 

SQL Server UNIQUE and NOT NULL Constraint in table

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; 


SQL Server Constraints in the table

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
S. NoSQL Server Constraint Table / Column LevelDescription
1NOT NULL Column Level Column value cannot be specified as NULL
2UNIQUE Column Levelall values in the column need to be UNIQUE
3CHECKColumn Level all values in a column satisfies a specific condition
4DEFAULTColumn Levelspecifies a DEFAULT value for the column
5INDEXTable Levelapply indexes make the data retrieval fast
6 PRIMARY KEY Column LevelUnique identifier for the column. Combination of NOT NULL and UNIQUE values
7 FOREIGN KEY Column LevelIdentifies the Column with Unique values in another table referenced in another table.

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


SQL Server Create Alter and Drop Table

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

SQLAlter 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	
)

SQL Server Single User Mode enablement

The Single user mode could be enabled for the user defined database. Lets see what could be the available options for you as a DBA Administrator for enabling single user mode.

While enabling this feature ensure that multiple users are not CONNECTED as the time of change, else all user connections gets closed without warning.

You should have DATABASE ALTER Permission to perform this activity.

Ensure that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF . If enabled , you will NOT be able to access the Database in Single User mode.

SQL Server Single User Mode using SQL Server Management Studio

Please find below the steps for enabling Single User Mode 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 Database oracleappshelpDB and select Properties
  4. Select Properties -> Options
  5. Select Restrict Access ->Single
  6. In case of multiple users connected to the Database Open Connection message will be shown. Click “Yes” to close all connections.

SQL Server Single User Mode using Transact SQL

Please find below the steps for enabling Single User Mode using SQL

USE master;
GO
ALTER DATABASE oracleappshelpDB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE oracleappshelpDB
SET READ_ONLY;
GO

SQL Server increase database size

There could be a requirement where as a DBA Adminitrator you have been asked to increase the size for the existing SQL Server Database. The below options can be used for increasing SQL Server Database size.

Increase SQL Server Database Size using SQL Server Management Studio

Please find below the steps for increasing SQL Server Database size 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 Database oracleappshelpDB and select Properties
  4. Select Properties -> File
  5. Update the value for the Column Initial Size (MB) to increase the existing Database File Size. Minimum 1 MB to update to get the change reflected.
  6. You can also create a new File for the existing Database to increase the size. Click Add and enter the File name and the value for Initial Size (MB)

Increase SQL Server Database Size using T-SQL

The below SQL Query can be executed for increasing SQL Server Database size.

USE master;
GO
ALTER DATABASE oracleappshelpDB 
MODIFY FILE
    (NAME = oracleappshelpDatFile,
    SIZE = 100MB);
GO

SQL Server Database Operations

The blog provides the how to Create SQL Server Database, how to Select Database, How to Drop created Database in the MS SQL Server.

SQL Server Create Database

MS SQL Server provides various Database operations for which a SQL Server Database creation is required as a pre-requisite. MS SQL Server Database can be divided into 2 categories

  1. System Database
  2. User Database

System Databases are provided by MS SQL Server. Below given are the list of System Databases automatically provided:

  • Master
  • Model
  • MSDB
  • Tempdb
  • Resource
  • Distribution

User Databases are created by Database Administrator as per the application need.

Let’s say we need to have a new Database OracleappshelpDB. Below are the possible options to do the same.

Option 1:  Executed command for creating database

Syntax: Create Database <DATABASE_NAME>

SQL: Create Database OracleappshelpDB

Option 2: Using SQL Server Management Studio for creating database

  1. Connect to MS SQL Server.
  2. Right click on Database Folder
  3. Click -> New Database
  4. Enter the Database Name:OracleappshelpDB
  5. Owner: <Default>

SQL Server Select Database Command

Let’s say we have the table – ms_oracleapps_users which provides the listing of oracleappshelp registered users in the DATABASE – OracleappshelpDB. Below given are the possible option of selecting the data from the ms_oracleapps_users  table.

Option 1: Using T-SQL script

The below command allows/ enables you to use the Database OracleappshelpDB for performing the select query.

Syntax:

Exec use <DATABASE_NAME>

SQL: Exec use OracleappshelpDB

The below SELECT Query can be performed now on the  ms_oracleapps_users  table

Option 2: Using SQL Server Management Studio

Syntax: Select * from <DATABASE_NAME>               

SQL: Select * from ms_oracleapps_users

SQL Server Drop Database Command

Now let’s consider that we might not require the above created Database OracleappshelpDB. In that case, below given are the possible option of selecting the data from the ms_oracleapps_users table

Option 1: Using T-SQL script

The below command allows/ enables you to use the Database OracleappshelpDB for performing the select query.

Syntax: Drop Database <DATABASE_NAME>

SQL: Drop Database OracleappshelpDB

Option 2: Using SQL Server Management Studio

  1. Connect to MS SQL Server.
  2. Right click on the available Database – OracleappshelpDB
  3. Click -> Delete
  4. Click -> OK

  The Database OracleappshelpDB is removed.