Add minute, hour, day, month in date in sql server


By using DATEADD(<interval>,<increment int>,<expression smalldatetime>) function we can add or reduce hour, day etc..
For increment or decrement from the datetime the query are given below.

--For add minute
(MINUTE,20,GETDATE())
--For reduce minute
DATEADD(MINUTE,-20,GETDATE())
--For add hour
DATEADD(HOUR,20,GETDATE())
--For reduce hour
DATEADD(HOUR,-20,GETDATE())
--For add day
DATEADD(DAY,20,GETDATE())
--For reduce day
DATEADD(DAY,-20,GETDATE())
--For add month  
DATEADD(MONTH,20,GETDATE())
--For reduce month
DATEADD(MONTH,-20,GETDATE())
--For add year  
DATEADD(YEAR,20,GETDATE())
--For reduce year 
DATEADD(YEAR,-20,GETDATE())

Find tables without Indexes


Sometimes we need to analyze the performance of the query the first things comes in our mind is Index, the following query can be used to find those tables who don't have the indexes.

Run the following query
USE <your_database_name>; 
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name,name AS tbl_name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') = 0
ORDER BY schema_name, tbl_name;
GO

What is Entity Framework and ORM tools


According to Microsoft:
The Microsoft ADO.NET Entity Framework is an Object Relational Mapping that enables to .NET developers to work with relational data as domain-specific objects, eliminating the need for most of the data access plumbing code that developers usually need to write. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects. The Entity Framework's ORM implementation provides services like change tracking, identity resolution, lazy loading, and query translation so that developers can focus on their application-specific business logic rather than the data access fundamentals.
Simply you may say: Entity framework is an Object Relational Mapping tool. It does provide an automated mechanism for accessing & storing the data in the database, and for working with the results, in addition to DataReader and DataSet. 

What is ORM and its need  
ORM is a tool for storing data from domain objects to a relational database like MS SQL Server, in an automated way, without much programming. ORM includes three main parts: Domain class objects, Relational database objects and Mapping information on how domain objects map to relational database objects (tables, views & stored-procedures). ORM allows us to keep our database design separate from our domain class design. This makes the application maintainable and extendable. It also automates standard CRUD operation (Create, Read, Update, and Delete) so that the developer doesn't need to write it manually.


Select in SQL Server


Select keyword uses to fetch data from the database table.
A select query is given below
Select * from tbl
To select top 10 records the query is given bellow
Select top 10 *from tbl
Note: Top keyword use to fetch the top 10 records from the database table.

CHECK constraint in sql server


CHECK- The CHECK responsible for enables a condition to check the value being entered into a record

Example-
In the following case, we create a PRODUCT table it has a PRICE column and its value should be greater than 20.00,
so that you cannot have any product which price is less than or equal to 20.00

CREATE TABLE PRODUCT
(
ID INT NOT NULL IDENTITY(1,1),
SKU VARCHAR(20) NOT NULL,
TITLE VARCHAR(200) NOT NULL,
PRICE MONEY NOT NULL CHECK (PRICE>20.00),
DESCRIPTION VARCHAR(2000)  NULL,
DTCREATE DATETIME NULL
CONSTRAINT pk_ID PRIMARY KEY (ID)
)

If a table already exists, then use the following query to create check constraints.

ALTER TABLE PRODUCT
ADD CHECK (PRICE>20.00)

FOREIGN KEY constraint in sql server


FOREIGN KEY- This constraint responsible for uniquely identified rows or records to another table.

Example-

Following table structure can clear your concept of foreign key constraints.

Product Table

CREATE TABLE PRODUCT
(
ID INT NOT NULL IDENTITY(1,1),
SKU VARCHAR(20) NOT NULL,
TITLE VARCHAR(200) NOT NULL,
PRICE MONEY NOT NULL,
DESCRIPTION VARCHAR(2000)  NULL,
DTCREATE DATETIME NULL
CONSTRAINT pk_ID PRIMARY KEY (ID)
)

Product Order Table

CREATE TABLE PRODUCT_IMAGE
(
ID INT NOT NULL IDENTITY(1,1),
PRODUCT_ID INT NOT NULL REFERENCES PRODUCT(ID),
IMGURL VARCHAR(200) NOT NULL ,
DTCREATE DATETIME
)

To create a foreign key constraint to existing table use following query.

ALTER TABLE PRODUCT_IMAGE ADD FOREIGN KEY (PRODUCT_IDREFERENCESPRODUCT(ID)

PRIMARY KEY constraint in sql server


PRIMARY KEY- The primary key constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specific column for a table has a unique identity.

Example-
Following, we create a table PRODUCT in which column ID has primary key constraints.

CREATE TABLE PRODUCT
(
ID INT NOT NULL IDENTITY(1,1),
SKU VARCHAR(20) NOT NULL,
TITLE VARCHAR(200) NOT NULL,
PRICE MONEY NOT NULL,
DESCRIPTION VARCHAR(2000)  NULL,
DTCREATE DATETIME NULL
CONSTRAINT pk_ID PRIMARY KEY (ID)
)

If a table already exists, then use the following query.

ALTER TABLE PRODUCT ADD CONSTRAINT pk_products_pid PRIMARY KEY(ID)

UNIQUE KEY constraint in sql server


UNIQUE- This constraint ensures that each row for the column has a different value.

Example-
Following create a table PRODUCT, column SKU has unique key constraints.

CREATE TABLE PRODUCT
(
ID INT NOT NULL,
SKU VARCHAR(20) NOT NULL UNIQUE,
TITLE VARCHAR(200) NOT NULL,
PRICE MONEY NOT NULL,
DESCRIPTION VARCHAR(2000)  NULL,
DTCREATE DATETIME NULL
)

If a table has already been created and we want to apply unique constraints, then use the following query.

ALTER TABLE PRODUCT ADD UNIQUE (SKU)

You can also use the following syntax, which supports naming the constraint in multiple columns as well.

ALTER TABLE PRODUCT ADD UNIQUE (SKU, ID)

Related Posts

What is the Use of isNaN Function in JavaScript? A Comprehensive Explanation for Effective Input Validation

In the world of JavaScript, input validation is a critical aspect of ensuring that user-provided data is processed correctly. One indispensa...