Decimal Vs Numeric (Difference between Decimal and Numeric)

There is no difference between Decimal and Numeric.

Both datatypes perform same on the assigned column.

EX: 1

IFOBJECT_ID('dbo.Explanation')> 0

DROPTABLEdbo.Explanation

CREATETABLEdbo.Explanation

(

Decimalcolumndecimal(5,2)

,NumericColumnnumeric(5,2)

);

GO

INSERTINTOdbo.ExplanationVALUES (123, 123);

GO

SELECTDecimalcolumn,NumericColumn

FROMdbo.Explanation;

EX 2:

IFOBJECT_ID('dbo.Explanation')> 0

DROPTABLEdbo.Explanation

CREATETABLEdbo.Explanation

(

Decimalcolumndecimal(10,4)

,NumericColumnnumeric(10,4)

);

GO

INSERTINTOdbo.ExplanationVALUES (12345.67, 12345.67);

GO

SELECTDecimalcolumn,NumericColumn

FROMdbo.Explanation;

QUERY TO FIND LAST DATE OF THE CURRENT MONTH / PREVIOUS MONTH / NEXT MONTH

-----QUERY TO FIND LAST DATE OF THE CURRENT MONTH / PREVIOUS MONTH / NEXT MONTH

USE ROHAN

GO

SELECT DATEADD(DAY,-DAY(DATEADD(MONTH,1,CONVERT(DATE,GETDATE()))),DATEADD(MONTH,1,CONVERT(DATE,GETDATE()))) AS'LAST DAY OF CURRENT MONTH',

DATEADD(DAY,-DATEPART(DAY,CONVERT(date,GETDATE())),CONVERT(DATE,GETDATE())) AS 'LAST DAY OF PREVIOUS MONTH',

DATEADD(DAY,-DATEPART(DAY,DATEADD(MONTH,2,CONVERT(DATE,GETDATE()))),DATEADD(MONTH,2,CONVERT(DATE,GETDATE()))) 'LAST DAY OF NEXT MONTH'

---- DYNAMIC SET THE DATED

DECLARE @SETDATE DATE = GETDATE()

SELECT DATEADD(DAY,-DAY(DATEADD(MONTH,1,CONVERT(DATE,@SETDATE))),DATEADD(MONTH,1,CONVERT(DATE,@SETDATE))) AS 'LAST DAY OF GIVEN CURRENT MONTH',

DATEADD(DAY,-DATEPART(DAY,CONVERT(date,@SETDATE)),CONVERT(DATE,@SETDATE)) AS 'LAST DAY OF GIVEN PREVIOUS MONTH',

DATEADD(DAY,-DATEPART(DAY,DATEADD(MONTH,2,CONVERT(DATE,@SETDATE))),DATEADD(MONTH,2,CONVERT(DATE,@SETDATE)))'LAST DAY OF GIVEN NEXT MONTH'

Subtracting x days from given date

USE Rohan

GO

CREATE TABLE DATEFIELD(ID INT,NAME VARCHAR(10),DATEFIELD DATE)

INSERT INTO DATEFIELD VALUES(1,'TERMIN','2015-12-19'),(2,'ABORT','2015-12-29'),(3,'USER','2015-12-24'),(4,'SHUTDOWN','2015-12-27')

Ex 1: Here dateadd subtracts 7 days from the current date.

EX 2 : Date Functions in WHERE Clause



SELECT * FROM DATEFIELD

WHERE DATEFIELD >= DATEADD(DAY,-7,CONVERT(DATE,GETDATE()))  AND DATEFIELD <=CONVERT(DATE,GETDATE())

Here dateadd subtracts 7 days from the current date.

Check Constraint

Check:

Check limits the values that are accepted by one or more columns.

The Below code accepts only values A or k or R or S, it will not accept other than this.

USE Rohan

GO

CREATE TABLE CHEECK (ID INT, SHORT VARCHAR(2) CHECK (SHORT LIKE '[A,K,R,S]'))

What happens if we insert other than this?

INSERT INTO CHEECK(ID, SHORT) VALUES(1,'N')

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CK__CHEECK__SHORT__607251E5". The conflict occurred in database "Rohan", table "dbo.CHEECK", column 'SHORT'.

The statement has been terminated.

LIMITATION OF CHECK:

USE Rohan

GO

CREATE TABLE CHEECK(ID INT,SHORT VARCHAR(2) CHECK(SHORT LIKE '[A,K,R,S]'))

INSERT INTO CHEECK(ID) VALUES(1)

SELECT * FROM CHEECK

ID     SHORT

1      NULL

If there is no value the column accepts Null Values, even though there is Check Constraint.

Check Constraints works at the row level, checks whether the row is true, if there is no row for check column it will take default NULL values.

Example:

USE Rohan

GO

CREATE TABLE CHEECK (col1 int, col2 int);

GO

CREATE FUNCTION FOREXAMPLE()

RETURNS int

AS

BEGIN

   DECLARE @COUNT int

   SELECT @COUNT = COUNT(*) FROM CHEECK

   RETURN @COUNT

END;

GO

ALTER TABLE CHEECK

ADD CONSTRAINT chkRowCount CHECK (dbo.FOREXAMPLE() >= 1 );

GO

The check constraint says the row count should be greater than 1 ,

Check constraint does not work for DELETE Statements

INSERT INTO CHEECK VALUES (10, 10)

GO

DELETE CHEECK WHERE col1 = 10;

SELECT * from CHEECK

ONLY EMPTY TABLE RETURNS, Even though  the check constraint says the count of the table should be greater than 1.