Posts Tagged ‘Bhavesh Patel’

SQL Server – List all Constraints of Database or Table

List all Constraints of Database or Table

Many times when we are using DML commands like: INSERT, UPDATE, DELETE, we get errors because one or the other constraint is set on the table. It get really annoying when we are new to a Database and don’t know what constraints are set on the tables.

It would be of great help if we get the list of all the constraints in the database. Below are two methods to do the same.

Method 1

Use sys.objects to get the constraint information.

— To Display all the Constraint in the Database

SELECT * FROM sys.objects

WHERE type_desc LIKE ‘%CONSTRAINT’

The above query will display all the fields of sys.objects. We can refine the above query to display the result more tasteful.

— To Display all the Constraints in the Database

SELECT OBJECT_NAME(object_id) AS ConstraintName,

SCHEMA_NAME(schema_id) AS SchemaName,

OBJECT_NAME(parent_object_id) AS TableName,

type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc LIKE %CONSTRAINT’

— To Display all the Constraints in table ‘Employee’

SELECT OBJECT_NAME(object_id) AS ConstraintName,

SCHEMA_NAME(schema_id) AS SchemaName,

type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc LIKE ‘%CONSTRAINT’ AND OBJECT_NAME(parent_object_id)=‘Employee’

Method 2

Use INFORMATION_SCHEMA.TABLE_CONSTRAINTS to get the constraint information. This query has one shortcoming; it won’t display DEFAULT constraints of the database

— To Display all the Constraints in the Database

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

— To Display all the Constraints in table ‘Employee’

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME=‘Employee’

To display DEFAULT constraints in the database we can use following SQL Statement.

— To Display Default Constraints in Database

SELECT OBJECT_NAME(PARENT_OBJECT_ID) AS TABLE_NAME,

COL_NAME (PARENT_OBJECT_ID, PARENT_COLUMN_ID) AS COLUMN_NAME,

NAME AS DEFAULT_CONSTRAINT_NAME

FROM SYS.DEFAULT_CONSTRAINTS

See also:

SQL Server – Drop All Table Constraints

SQL Server – Stored Procedure to Drop All Database Constraints

Good Luck!

Advertisements

SQL Server – Find Nth Highest or Lowest Value in Table

Find Nth Highest or Lowest Value in Table

This one is a tricky question. We all assume that this is not a big deal, but when this question is asked in the interviews, we try all the permutations and combinations of SELECT statements, but are never close to the answer.

There are many solutions to this problem. I have listed down some of the simple methods to find out Nth highest and Nth Lowest values in the table.

Method 1

USE AdventureWorks;

— Finding unique third highest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC) AS A

ORDER BY UnitPrice;

— Finding unique second lowest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice) AS A

ORDER BY UnitPrice DESC;

Method 2

— Finding unique third highest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC)

ORDER BY UnitPrice;

— Finding unique second lowest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice)

ORDER BY UnitPrice DESC;

Method 3

— Finding unique third highest UnitPrice value from table

SELECT MIN(UnitPrice) FROM

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC) AS A;

— Finding unique second lowest UnitPrice value from table

SELECT MAX(UnitPrice) FROM

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice) AS A;

Method 4

— Finding unique third highest UnitPrice value from table

SELECT MIN(UnitPrice) FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC);

— Finding unique second lowest UnitPrice value from table

SELECT MAX(UnitPrice) FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice);

Method 5

— Finding unique third highest UnitPrice value from table

SELECT MAX(P1.UnitPrice) FROM Purchasing.PurchaseOrderDetail P1

WHERE 3 <= (SELECT COUNT(DISTINCT P2.UnitPrice)

FROM Purchasing.PurchaseOrderDetail P2 WHERE P1.UnitPrice <= P2.UnitPrice);

Method 6

— Finding unique third highest UnitPrice value from table

SELECT DISTINCT P1.UnitPrice FROM Purchasing.PurchaseOrderDetail P1

WHERE 3 = (SELECT COUNT(DISTINCT P2.UnitPrice)

FROM Purchasing.PurchaseOrderDetail P2 WHERE P1.UnitPrice<=P2.UnitPrice);

Hope you find this useful.

Good Luck!

SQL Server – How to Handle Single Quote in String

How to Handle Single Quote in String

This is the common one-time problem (not for all ;-)) faced by a new DBA Developer, and even I did.

The solution to this problem is rather simple. Use an extra single quote, with the quote character occurring in the string.

See the example below:

— The Insert Satement below will give and error

INSERT INTO Employee VALUES (1003, ‘Patrick’, ‘O’Brain’)

Error Message

Msg 105, Unclosed quotation mark after the character string

— Correct Statement

INSERT INTO Employee VALUES (1003, ‘Patrick’, ‘O”Brain’)

— Similarly you can use it in WHERE clause also

SELECT * FROM Employee WHERE LastName=’O”Brain’

See also:

Function to split comma delimited String or List

How to handle single quote in sqlquery string?

Good Luck!

SQL Server – Set Functions – COUNT, MAX, MIN, SUM and AVG

Set Functions – COUNT, MAX, MIN, SUM and AVG

Many times the information we require from table doesn’t relate to individual rows but to set of rows. In such case we can use Set functions provided by the SQL Server. These function are COUNT, MAX, MIN, SUM and AVG. Set functions operate on groups of rows.

1. COUNT

The COUNT function returns the rows count in the table.  When used with WHERE clause, only number of rows which satisfy the condition will be returned. The DISTINCT can be used to get the count of rows with unique values.

Example

USE AdventureWorks

— Calculates the row count of the table

SELECT COUNT(*) FROM Purchasing.PurchaseOrderDetail

— Calculates the row count which satisfy the condition

SELECT COUNT(*) FROM Purchasing.PurchaseOrderDetail WHERE PurchaseOrderID=18

— Calculates the number of non-null values for that column

SELECT COUNT(ProductID) FROM Purchasing.PurchaseOrderDetail

— Calculates the number of distinct non-null values for that column

SELECT COUNT(DISTINCT(ProductID)) FROM Purchasing.PurchaseOrderDetail

2. MAX

The MAX function returns the maximum value in the specified column. The MAX function can also be used as the part of sub-query.

Example

— Finds the maximum value for the column

SELECT MAX(OrderQty) FROM Purchasing.PurchaseOrderDetail

— It can be used as part of subquery

— Displays all the records having maximum OrderQty

SELECT * FROM Purchasing.PurchaseOrderDetail WHERE OrderQty =

(SELECT MAX(OrderQty) FROM Purchasing.PurchaseOrderDetail)

3. MIN

The MIN function returns the minimum value in the specified column. The MIN function can also be used as the part of sub-query.

Example

— Finds the minimum value for the column

SELECT MIN(OrderQty) FROM Purchasing.PurchaseOrderDetail

— It can be used as part of subquery

— Displays all the records having minimum OrderQty

SELECT * FROM Purchasing.PurchaseOrderDetail WHERE OrderQty =

(SELECT MIN(OrderQty) FROM Purchasing.PurchaseOrderDetail)

4. SUM

The SUM function adds up the values in a specified column. The column must be of one of numeric data types. The DISTINCT can be used to get the sum of only unique values.

Example

— Finds the total sum for the column

SELECT SUM(OrderQty) FROM Purchasing.PurchaseOrderDetail

— Finds the sum of distinct values for the column

SELECT SUM(DISTINCT(OrderQty)) FROM Purchasing.PurchaseOrderDetail

5. AVG

The AVG function returns the average of all the values in the specified column. As with SUM function, the column must be of one of numeric data types. The DISTINCT can be used to get the average of only unique values.

Example

— Finds the average for the column

SELECT AVG(OrderQty) FROM Purchasing.PurchaseOrderDetail

— Finds the average of distinct values for the column

SELECT AVG(DISTINCT(OrderQty)) FROM Purchasing.PurchaseOrderDetail

Good Luck!

SQL Server – Difference between Primary key and Unique Key

Difference between Primary key and Unique Key

I hope you know it, as this is one to most frequently asked question in interviews for DBA’s.

Both Primary Key and Unique Key constraints enforce uniqueness of the column (columns incase of composite key) on which they are defined. But there are key differences between them, see below:

Primary Key

  • Only one Primary key constrain per table
  • Primary Key is by default Clustered Indexed.
  • It does not allow NULL value.

— Create table Department with DeptID as Primary Key

CREATE TABLE Department(

DeptID INT PRIMARY KEY,

DeptName VARCHAR(25)

)

— OR make DeptID of existing table Department as Primary Key

ALTER TABLE Department ADD CONSTRAINT pk_Department PRIMARY KEY (DeptID)

Unique Key

  • Multiple Unique constrains can be defined on the table.
  • Unique Key is by default Non-Clustered Indexed.
  • Unique Key constraint allows only one NULL value.

— Create table Employee with ProjectCode as Unique

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DeptID INT,

ProjectCode INT UNIQUE,

)

— OR make ProjectCode of existing table Employee as Unique

ALTER TABLE Employee ADD CONSTRAINT IX_ProjectCode UNIQUE (ProjectCode)

SQL Server – Table Constraints

Table Constraints

We always want data in our database clean and in healthy state. To achieve this we need to use constraints. A constraint is high speed data validation check or business-logic check performed by database-engine level. There five type of constrains provided by SQL Server. We shall see each one of then in details.

1. Primary Key Constraint

A primary key is used to uniquely identify each row in a table. A primary key can consist of one or more then more columns in table. When they consist of more then one column they are called composite key. It ensures unique non-null key.

Example

— Create table Department with DeptID as Primary Key

CREATE TABLE Department(

DeptID INT PRIMARY KEY,

DeptName VARCHAR(25)

)

— OR make DeptID of existing table Department as Primary Key

ALTER TABLE Department ADD PRIMARY KEY (DeptID)

2. Foreign Key Constraint

A foreign key is a field in a table that matches the primary key column of another table. It establishes and enforces a link between data of two tables. It ensures that value points to a valid key.

Example

— Create table Employee with DeptID as Foreign Key

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DeptID INT FOREIGN KEY REFERENCES Department(DeptID),

Salary INT,

ProjectCode INT,

)

— OR make DeptID as Foreign Key in exiting table Employee

ALTER TABLE Employee ADD CONSTRAINT DeptID_FK

FOREIGN KEY (DeptID) REFERENCES Department(DeptID)

3. Unique Constraint

It is similar to Primary Key Constraint. It ensures that every value in the column is unique value. It allows one NULL value.

Example

— Create table Employee with ProjectCode as Unique

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DeptID INT,

Salary INT,

ProjectCode INT UNIQUE,

)

— OR make ProjectCode of existing table Employee as Unique

ALTER TABLE Employee ADD UNIQUE (ProjectCode)

4. Nullability Constraint

It defines whether a column will accept a NULL value or not. It is referred to as nullability of the column.

Example

— Create table Employee with FirstName as NOT NULL

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50),

DeptID INT,

Salary INT,

ProjectCode INT

)

— OR make FirstName of existing table Employee as NOT NULL

ALTER TABLE Employee ALTER COLUMN FirstName VARCHAR(50) NOT NULL;

5. Check Constraint

Check Constraint is extensively used to enforcement of general data-validation rules or simple business rules. Such as correct date range, max value that can be inserted into a table, etc.

Example

— Create table Employee with chk_salary constraint

CREATE TABLE Employee(

EmpID INT,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DeptID INT,

Salary INT,

ProjectCode INT,

CONSTRAINT chk_salary CHECK (Salary BETWEEN 1000 AND 25000)

)

— OR add constraint chk_salary on existing table Employee

ALTER TABLE Employee

ADD CONSTRAINT chk_salary CHECK (Salary BETWEEN 1000 AND 25000)

We can add all five constraints while creating the table also. See the example below:

Example

— Create table Employee with all 5 constraints

CREATE TABLE Employee(

EmpID INT PRIMARY KEY,

FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50),

DeptID INT FOREIGN KEY REFERENCES Department(DeptID),

Salary INT,

ProjectCode INT UNIQUE,

CONSTRAINT chk_salary CHECK (Salary BETWEEN 1000 AND 25000)

)

Good Luck!

Bhavesh Patel scores a Century

Hi All,

I just scored a century. Yes, my blog has just crossed the critical 100 hits mark. People may think – “what’s a big deal in that”. Well it is for me and I know any other blogger, who did not dream of such a positive response from Netizens.

I hope my posts are of some use to budding DBAs around the world. It just gives you great feeling. Most of the topics which I covered by my posts are basic DATABASE concepts, you can expect that from a Junior DBA.

Now I am much more confident and shall post little more technical posts in future. I am also shortly going to start writing about DOT NET concepts which I am going to jump into.

If you don’t mind I am promoting myself from Trainee DBA to Junior DBA :-). Still it’s a long journey to be a Master DBA, which is my ultimate career objective.

Thanks,
Bhavesh Patel