Thursday 31 December 2015

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'



Tuesday 29 December 2015

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.






Monday 28 December 2015

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.