DATE FUNCTIONS
DATEPART VALUES
DATEPART
ABBREVIATIONS
YEAR
YY,YYYY
QUARTER
QQ
MONTH
MM
DAYOFYEAR
DY
DAY
DD
WEEK
WK
WEEKDAY
DW
HOUR
HH
MINUTE
MI
SECOND
SS
MILLISECOND
MS
PREPARE THE TABLE LIKE FOLLOWS
CREATE TABLE CSCAVN(NAME CHAR(15),DOJ SMALLDATETIME)
INSERT INTO CSCAVN VALUES("RAMESH","24-JUN-1988")
INSERT INTO CSCAVN VALUES("GANESH","14-MAR-1996")
INSERT INTO CSCAVN VALUES("RAMESH","4-APR-2001")
INSERT INTO CSCAVN VALUES("RAMESH","17-AUG-2002")
INSERT INTO CSCAVN VALUES("PRAMNESH","13-MAY-1999")
SELECT DATEADD(MM,3,DOJ)FROM CSCAVN WHERE NAME="PRAMNESH"
SELECT GETDATE()
SELECT DATEDIFF(YY,DOJ,GETDATE())"EXPERIENCE" FROM CSCAVN
SELECT DATEPART(YY,DOJ)FROM CSCAVN
SELECT DATENAME(MM,DOJ)FROM CSCAVN
SELECT DAY(DOJ) FROM CSCAVN
SELECT MONTH(DOJ) FROM CSCAVN
SELECT YEAR(DOJ) FROM CSCAVN
SELECT ISDATE("12-JUN-2007")
DATA TYPE CREATION
SP_ADDTYPE BB,'NUMERIC(3)'
CREATE TABLE SON(ID BB)
INDEX
AN INDEX IS A STRUCTURE THAT PROVIDES FOR LOCATING ONE OR MORE ROWS DIRECTLY.WITHOUT AN INDEX SQL SERVER HAS TO PERFORM A TABLE SCAN,WHICH INVOLVES SEARCHING THROUGH THE ENTIRE TABLE.
INDEX IS A STRUCTURE ASSOCIATED WITH A TABLE THAT SPEEDS RETRIEVAL OF THE ROWS IN THE TABLE . AN INDEX CONTAINS KEYS BUILT FROM ONE OR MORE COLUMNS IN THE TABLE. THESE KEYS ARE STORED IN A STRUCTURE THAT ALLOWS SQL SERVER TO FIND THE ROW OR ROWS ASSOCIATED WITH THE KEY VALUES QUICKLY AND EFFICIENTLY.
EXAMPLE:
PREPARE THE TABLE LIKE FOLLOWS
TABLENAME :CSCAVN
ID
NAME
8
MURUGAN
19
GANESH
3
JEGAN
78
KUMAR
19
VIJAY
SELECT ID FROM CSCAVN WHERE ID>1
OUTPUT
ID
8
19
3
78
19
CREATE INDEX CC ON CSCAVN(ID)
SELECT ID FROM CSCAVN WHERE ID>1
OUTPUT
ID
3
8
19
19
78
DROP INDEX CSCAVN.CC
SELECT ID FROM CSCAVN WHERE ID>1
OUTPUT
ID
8
19
3
78
19
IDENTITY
THE IDENTITY PROPERTY GENERATE VALUES FOR EXISTING ROWS BASED ON THE SEED AND INCREMENT PARAMETER IT USED.THE SEED VALUE WILL BE ASSIGN TO THE FIRST ROW IN THE TABLE AND EACH SUBSEQUENT ROW WILL RECIVE THE NEXT IDENTITY VALUES .
