SQL -STRUCTURED QUERY LANGUAGE

SQL IS LANGUAGE TO ACCESS AND MANIPULATE THE DATA IN DATABASE.
MS SQL SERVER USES T-SQL(TRANSACT-SQL ) AND PL/SQL FOR ORACLE.
SQL IS CASE IN SENSITIVE LANGUAGE.
DmL=> DATA MANIPULATION LANGUAGE.

DML QUERIES IN SQL

SELECT

SELECT * FROM EMP

INSERT

INSERT INTO EMP (ID,NAME,CITY)VALUES(1,'PATIL','BANGALORE')

DELETE

DELETE FROM EMP WHERE ID=1

UPDATE

UPDATE EMP

SET NAME='MAHESH' WHERE ID=1

DDL=>DATA DEFINITION LANGUAGE.

DDL QUERIES

CREATE DB
DROP DB
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX

DCL=> DATA CONTROL LANGUAGE.

DCL QUERIES

GRANT
REVOKE

MSBI=> MICROSOFT BUSINESS INTELLIGENCE.

CONVERTING DATA INTO INFORMATION TO MAKE BETTER DECISIONS

SSIS=> SQL SERVER INTEGRATION SERVICES.

SSIS USED FOR ETL OPERATION AND DATA MIGRATION.

SSAS=> SQL SERVER ANALYSIS SERVICES.

SSAS IS USED FOR DATA ANALYSIS.

SSRS=> SQL SERVER REPORTING SERVICES.

SSRS IS REPORTING TOOL IS USED TO CREATE VERITY OF REPORTS.

#JOINS

JOINS ARE USED TO COMBINE ROWS FROM TWO OR MORE TABLES.

T-SQL SUPPORTS 4 TYPES OF JOINS

INNER JOIN

THE INNER JOIN SELECT ALL ROWS FROM BOTH TABLES ,
ONLY IF THEIR ARE MATCH IN BOTH TABLE.

EX: SELECT CUST.NAME,ORDERS.TOTALORDERS FROM CUST

INNER JOIN ORDERS

ON ORDERS.CID=CUST.CID

2. OUTER JOIN

LEFT OUTER JOIN

THE LEFT JOIN SELECT ALL ROWS FROM LEFT TABLE (FIRST

TABLE), WITH MATCHING ROWS IN THE RIGHT TABLE(SECOND

TABLE) AND NON MATCHING ROWS IN THE RIGHT TABLE ARE


NULL.

RIGHT OUTER JOIN

THE RIGHT JOIN SELECT ALL ROWS FROM RIGHT TABLE( 2ND

TABLE), WITH MATCHING ROWS IN THE LEFT TABLE(1ST

TABLE) AND NON MATCHING ROWS IN THE LEFT TABLE ARE

NULL.

FULL JOIN

THE FULL JOIN SELECT ALL ROWS FROM BOTH TABLES

WITH MATCHING ROWS IN BOTH TABLES

AND NON MATCHING ROWS IN BOTH TABLES ARE

NULL.

3. CROSS JOIN OR CARTESIAN JOIN

4. SELF JOIN.

#CONSTRAINTS

CONSTRAINTS AVOID TYPE OF WRONG DATA ENTER INTO TABLE.

SQL SUPPORTS FOLLOWING CONSTRAINTS

PRIMARY KEY

PRIMARY KEY IS CONSTRAINT ,IT AVOID DUPLICATE DATA ENTER INTO TABLE.
PRIMARY KEY IS UNIQUE ID FOR EACH ROW IN THE COLUMN.
PRIMARY KEY DOES NOT ALLOW NULL VALUES.
EX:

CREATE TABLE CUSTOMERS
(
ID INT PRIMARY KEY,
NAME VARCHAR(40)
)

TABLE CAN HAVE ONLY ONE PRIMARY KEY .

FOREIGN KEY

FOREIGN IS PRIMARY KEY FOR OTHER TABLE.
FOREIGN KEY IS USED TO CREATE RELATION BETWEEN THE TABLES.
EX:

CREATE TABLE CUSTOMERS
(
O_ID INT NOT NULL PRIMARY KEY,
ORDERNO INT NOT NULL,
C_ID INT FOREIGN KEY REFERENCES CUSTOMERS(C_ID)
)

UNIQUE KEY

UNIQUE IS SAME AS PRIMARY KEY BUT IT ALLOW ONE NULL VALUES.
UNIQUE KEY IS UNIQUE ID FOR EACH ROW IN THE TABLE.

WE CAN HAVE MORE THAN ONE UNIQUE KEY IN THE TABLE.

EX:

CREATE TABLE CUSTOMERS
(
ID INT UNIQUE ,
NAME VARCHAR(40)
)

NOT NULL

NOT NULL CONSTRAINT DONT NULL VALUES TO ENTER INTO TABLE.

CHECK
DEFAULT

#REPLICATION

SQL SUPPORTS 3 TYPES OF REPLICATIONS.

SNAPSHOT REPLICATION.
TRANSACTIONAL REPLICATION.
MERGE REPLICATION.

#VIEWS

VIEWS ARE MIRROR OF THE TABLE.
VIEWS NOT WORKS WHEN BASE TABLES ARE DELETED.

SYNTAX:

CREATE VIEW VIEW_NAME
AS
SQL STATEMENTS

ADVANTAGES OF VIEWS:

DATA PROTECTION OR SECURITY.
VIEWS HIDE SOME COLUMNS FROM USERS.
CAN NOT DELETE,INSERT AND UPDATE VALUES OF VIEW,IF VIEW IS CREATED WITH MULTIPLE TABLE.
IF VIEWS ARE CREATED WITH SINGLE TABLE THEN WE CAN DELETE,INSERT AND UPDATE VALUES OF VIEW AND DATA IS EFFECTED IN BASE TABLES.

EX:

CREATE VIEW VW_ORDERS
AS
SELECT O_ID,C_NAME,C_CITY,P_NAME FROM ORDERS
INNER JOIN CUSTOMERS
ON CUSTOMERS.C_ID=ORDERS.C_ID
INNER JOIN PROD
ON ORDERS.P_ID=PROD.ID
------------------------------------------------------------------------------------------

#STORED PROCEDURES
SQL SUPPORT TWO TYPES OF STORED PROCEDURE

SYSTEM STORED PROCEDURES

EX: "SP_RENAMEDB" ,"SP_HELP".

SP STANDS FOR STORED PROCEDURE. ALL SYSTEM STORED PROCEDURE

STARTS WITH "SP_".

USER DEFINED STORED PROCEDURES

STORED PROCEDURES ARE SET OF SQL STATEMENTS.
REPEATEDLY USING RELATED SQL STATEMENTS ARE COMBINED AND FORMED AS STORED PROCEDURE.
SYNTAX:

CREATE PROC PROC_NAME
(
//DECLARE PARAMETERS HERE
)
AS
BEGIN
//STATEMENTS HERE
END

ADVANTAGES OF STORED PROCEDURES

REDUCES NETWORK TRAFFICS.
RE-USABILITY OF CODE.
INCREASES THE PERFORMANCE .
DATA IS SECURE.
MAINTENANCE IS EASY.

#NORMALIZATION
NORMALIZATION IS PROCESS OF ORGANIZING DATA IN THE TABLE.
THEIR ARE 5 TYPES OF NORMALIZATION PROCESS.

1NF
2NF
3NF
4NF
5NF

#TABLE DESIGN RULES.

TABLE SHOULD CONTAINS PRIMARY KEY.
COLUMN NAME HEADER FORMAT SHOULD BE TABLE NAME_DATA TYPE_COLUMN NAME.
EX: TBLEMP_INT_EMPID,

TBLEMP_VARCHAR_CITY
TBLEMP_DATE_LOGINDATE

#TRIGGERS

TRIGGERS ARE SPECIAL KIND OF STORED PROCEDURES .TRIGGERS ARE FIRED WHEN EVENTS OCCURS IN THE TABLE.

THEIR ARE TWO TYPES OF TRIGGERS

INSTEAD OF TRIGGERS.

INSTEAD OF TRIGGERS FIRED BEFORE DML OPERATIONS SUCH AS INSERT, DELETE AND UPDATE.

EX:

CREATE TRIGGER TR_INSTEADOF_DELETE

ON TT

INSTEAD OF DELETE

BEGIN

PRINT 'CAN NOT DELETED DATA !'

END

--DELETE FROM TT WHERE ID=4

--SELECT * FROM TT

AFTER TRIGGERS/FOR TRIGGERS.

#EXCEPTIONS

EXCEPTIONS HANDLED IN SQL USING TRY CATCH BLOCK.
IF ANY ERROR OCCURS IN TRY BLOCK ,ERROR IS

EX:
BEGIN TRY

INSERT INTO EMP VALUES('A','AAA','AAS','ASS',GETDATE())

END TRY
BEGIN CATCH
--ERROR_MESSAGE()--Returns the complete description of the error message
SELECT 'ERROR:'+ ERROR_MESSAGE() AS ERROR
END CATCH
#FUNCTIONS
SQL SUPPORT TWO TYPES OF FUNCTIONS

SYSTEM FUNCTIONS

SCALAR FUNCTIONS

SELECT UPPER('upper case') as UPPER_CASE
SELECT LOWER('lower case') as LOWER_CASE
SELECT LEN('STRING') AS STRING_LENGTH
SELECT ROUND(10.77777,2) AS DECIMAL_ROUND_UP
SELECT CONVERT(VARCHAR(20),GETDATE(),102) AS DATE_FORMATE
SELECT GETDATE()

AGGREGATE FUNCTIONS.

USER DEFINED FUNCTION (UDF)

SCALAR FUNCTION

CREATE FUNCTION AGEs(@DOB Datetime)

RETURNS INT

BEGIN

DECLARE @AGE INT

SET @AGE= DATEDIFF(YEAR,@DOB,GETDATE())

RETURN @AGE

END

--SELECT DBO.AGEs('07-20-1991') AS AGE

INLINE TABLE VALUED FUNCTION
MULTI STATEMENT TABLE VALUED FUNCTION.

#ROWNUMBER,RANK AND DENSE RANK

#INDEX

INDEX HELPS QUERY TO FIND ROW QUICKLY.
INDEXES ARE SAME AS INDEX IN BOOK.
INDEX IMPROVE THE PERFORMANCE WITHOUT SCANNING ENTIRE TABLE DATA.
INDEX STORED IN THE FORM OF B-TREE STRUCTURE.
INDEX CAN BE CREATED ON SINGLE OR COMBINATION OF COLUMNS.

TYPES OF INDEX

CLUSTERED INDEX.

IN CLUSTERED INDEX DATA IN TABLE STORED AS PHYSICALLY STORED.
TABLE CAN HAVE ONLY ONE CLUSTERED INDEX.
CLUSTERED INDEX IS DEFAULT CREATED ON PRIMARY KEY.
THE LEAF NODE OF CLUSTERED INDEX CONTAINS DATA PAGES.
DATA IN C.I. ARE PHYSICALLY SORTED WHILE INSERTION OR UPDATING DATA.

NON CLUSTERED INDEX.

THE DATA STORED IN TABLE ARE NOT MATCHED WITH PHYSICALLY STORED IN NON-CLUSTERED INDEX.
DATA IS NOT SORTED IN NON-CLUSTERED INDEX.
TABLE CAN HAVE MORE THAN ON NON-CLUSTERED INDEX.
IN SQL SERVER 2008- TABLE CAN HAVE MAXIMUM 999 NON-CLUSTERED INDEX AND IN SQL SERVER 2005 ITS 249.
LEAF NODE OF NON-CLUSTERED INDEX CONTAINS INDEX ROWS.
WE CAN CREATE NON-CLUSTERED INDEX ON FOREIGN KEY AND ALTERNATIVE KEYS.

INDEX TUNING:

INDEX TUNING FIND RIGHT COLUMNS FOR NON-CLUSTERED INDEX.
SELECTING NON-CLUSTERED INDEX BASED ON THAT ARE FREQUENTLY REFERENCED IN WHERE CLAUSE , GROUP BY AND JOINS.

#UNION AND UNION ALL

UNION

UNION SELECT COLUMNS FROM TWO OR MORE TABLES.
SELECT STATEMENTS MUST HAVE SAME NUMBER OF COLUMNS AND OF SAME DATA TYPE IN UNION.
UNION SELECT DISTINCT RECORD FROM BOTH TABLE.

EX:
SELECT CITY FROM INDIA
UNION
SELECT CITY FROM US

UNION ALL

UNION SELECT ALL RECORDS FROM BOTH TABLE.

EX:
SELECT CITY FROM INDIA
UNION ALL
SELECT CITY FROM US

#DATE TIME
TO GET LOCAL SYSTEM DATE YOU HAVE TO USE GETDATE() FUNCTION

EX 1: SELECT GETDATE() AS LOCAL_DATE

RESULT:

EX 2:

DECLARE
@datetime datetime ='12-21-05';

SELECT @datetime AS 'DateTime'

#TRANSACTIONS

Transactions provide a mechanism for grouping a series of database changes into one logical operation. After changes are made to the database, these changes can be committed or canceled as a single unit.

TRANSACTIONS HAS 3 COMMANDS

TRANSACTION
COMMIT
ROLLBACK

EX:
BEGIN TRANSACTION T1
BEGIN TRY

INSERT INTO EMP VALUES(12,'AAA',1254,'SSS',GETDATE())

UPDATE EMP
SET SALARY=20222 WHERE ID='HHS'
COMMIT TRANSACTION
PRINT 'DATA COMMITED'
END TRY
BEGIN CATCH
--ERROR_MESSAGE()--Returns the complete description of the error message

ROLLBACK TRANSACTION
PRINT 'DATA ROLLED BACK'
SELECT 'ERROR:'+ ERROR_MESSAGE() AS ERROR
END CATCH

#BCP

#DBCC

#TEMPORARY TABLES.

TEMPORARY TABLE ARE SAME AS PERMANENT TABLES.
TEMPORARY TABLE ARE STORED IN "TEMPDB " (UNDER SYSTEM DATABASES).
THESE TABLES CAN BE CREATED AT RUN TIME.
TEMPORARY TABLE ARE USED TO HOLD RESULT SET IN STORED PRODUCER.
THEIR ARE TWO TYPES TEMPORARY TABLE.

LOCAL TEMPORARY TABLES

LOCAL TEMPORARY TABLES ARE CREATED SAME AS PERMANENT TABLE BUT STARTS WITH HASH(#).
EX:
CREATE TABLE #TEST
(
ID INT,
NAME VARCHAR(45)
)
.
LOCAL TEMPORARY TABLES ARE DROPPED WHEN CONNECTION IS ENDED. LOCAL TEMPORARY TABLES ARE AVAILABLE ONLY THAT CONNECTION WHO HAS CREATED TEMP TABLES.
DIFFERENT CONNECTIONS CAN CREATE LOCAL TEMPORARY TABLE WITH SAME NAME.
LOCAL TEMPORARY TABLE CAN ALLOW ALL DML QUERIES.

GLOBAL TEMPORARY TABLES

GLOBAL TEMPORARY TABLES ARE CREATED SAME AS PERMANENT TABLE BUT STARTS WITH HASH(##).
EX:
CREATE TABLE ##TEST_GLOBAL
(
ID INT,
NAME VARCHAR(45)
)
.
GLOBAL TEMPORARY TABLES ARE AVAILABLE FOR ALL CONNECTION AND DROPPED WHEN LAST CONNECTION ENDS.
GLOBAL TEMPORARY TABLES SHOULD BE UNIQUE NAME.

#CTE- COMMON TABLE EXPRESSION.

CTE IS TEMPORARY RESULT SET THAT CAN BE REFERENCED WITHIN DML STATEMENTS.
TO CREATE CTE WE USE "WITH" KEYWORD.

SYNTAX FOR CTE:

WITH CTE_NAME(COL1,COL2...)

(

CTE _QUERY

)

EX: HOW TO USE CTE .

WRITE A QUERY TO GET EMP_NAME AND MANAGER NAME

EMPLOY TABLE:

QUERY:

WITH TEMP( EMP_NAME, MG_ID)
AS
(
SELECT EMP_NAME,MG_ID FROM EMP WHERE MG_ID IS NOT NULL
)

SELECT TEMP.EMP_NAME,EMP.EMP_NAME AS MG_NAME FROM EMP
INNER JOIN TEMP
ON EMP.ID=TEMP.MG_ID

RESULT:

#IDENTITY

IDENTITY IS AUTO INCREMENT FOR ROW ID.
Auto-increment allows a unique number to be generated when a new record is inserted into a table.

EX:

CREATE TABLE EMP

(

ID INT PRIMARY KEY IDENTITY,

NAME VARCHAR(200)

)

#LOG SHIPPING

#CURSOR

#SQL PROFILER

#GROUP BY AND HAVING

#ORDER BY

ODER BY IS USED FOR SORTING THE DATA.
DEFAULT ITS ASCENDING ORDER EVEN IF YOU DONT USE ASC KEY.

EX:
SELECT * FROM EMP ORDER BY NAME ASC/DESC

#IF AND ELSE IN SQL

#SQL SERVER AGENT

#@ AND @@

#LOCK AND NO LOCK IN SQL

#WHILE IN SQL

#XML IN SQL

#OLTP AND OLAP

ONLINE TRANSACTION PROCESSING.
ONLINE ANALYTICAL PROCESSING.

#DELETE AND TRUNCATE

DELETE ALLOW WHERE CLAUSE TO USE. ALLOW TO DELETE SELECTED ROWS.
TRUNCATE DELETE ALL THE ROWS FROM TABLE AND DOES NOT ALLOW WHERE CLAUSE.
TRUNCATE IS FASTER COMPARED TO DELETE BECAUSE TRUNCATE USES LESS RESOURCE.
TRUNCATE RESETS IDENTITY.
SYNTAX:

DELETE FROM TABLE_NAME.

SYNTAX:

TRUNCATE TABLE TABLE_NAME.

#IS NULL

#FIND TABLE NAME FROM SYSOBJECTS

THIS BELOW SQL STATEMENT GIVES U TABLE NAME FROM DATABASE IF EXIST.

SELECT [NAME] FROM SYSOBJECTS WHERE TYPE = 'U' AND [NAME] = 'ACCOUNTS'

TYPE U STANDS FOR USER TABLE

O/P

NAME
ACCOUNTS

#IMPORT AND EXPORT IN SQL

#COMPLEX QUERIES IN T-SQL

#MDF AND LDF FILES IN MSSQL SERVER.

# GROUP BY ROLLUP AND CUBE

In my last two posts, I gave examples of aggregation WITH ROLLUP and CUBE. SQL Server 2008 continues to support this syntax, but also introduces new more powerful ANSI SQL 2006 compliant syntax. In this post, I'll give an overview of the changes.

First, let's see how we rewrite simple WITH ROLLUP and CUBE queries using the new syntax. I'll use the same schema and queries as in my previous posts:

CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH CUBE

We can rewrite these two queries using the new syntax as:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY ROLLUP(EmpId, Yr)
SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY CUBE(EmpId, Yr)

These new queries are semantically equivalent to and use the same query plans as the original queries. Note that the new ROLLUP and CUBE syntax is only available in compatibility level 100. The more general GROUPING SETS syntax, which I will discuss next, is also available in earlier compatibility levels.

The new GROUPING SETS syntax is considerably more powerful. It allows us to specify precisely which aggregations we want to compute. As the following table illustrates, our simple two dimensional schema has a total of only four possible aggregations:

		Yr
		2005	2006	2007	ALL
EmpId	1	GROUP BY (EmpId, Yr)			GROUP BY (EmpId)
	2
	3
	ALL	GROUP BY (Yr)			GROUP BY ()

ROLLUP and CUBE are just shorthand for two common usages of GROUPING SETS. We can express the above ROLLUP query as:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), ())

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
1           2006        18000.00
1           2007        25000.00
1           NULL        55000.00
2           2005        15000.00
2           2006        6000.00
2           NULL        21000.00
3           2006        20000.00
3           2007        24000.00
3           NULL        44000.00
NULL        NULL        120000.00

This query explicitly asks SQL Server to aggregate sales by employee and year, to aggregate by employee only, and to compute the total for all employees for all years. The () syntax with no GROUP BY columns denotes the total. Similarly, we can express the above CUBE query by asking SQL Server to compute all possible aggregate combinations:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), (Yr), ())

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
2           2005        15000.00
NULL        2005        27000.00
1           2006        18000.00
2           2006        6000.00
3           2006        20000.00
NULL        2006        44000.00
1           2007        25000.00
3           2007        24000.00
NULL        2007        49000.00
NULL        NULL        120000.00
1           NULL        55000.00
2           NULL        21000.00
3           NULL        44000.00

We can also use GROUPING SETS to compute other results. For example, we can perform a partial rollup aggregating sales by employee and year and by employee only but without computing the total for all employees for all years:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId))

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
1           2006        18000.00
1           2007        25000.00
1           NULL        55000.00
2           2005        15000.00
2           2006        6000.00
2           NULL        21000.00
3           2006        20000.00
3           2007        24000.00
3           NULL        44000.00

We can skip certain rollup levels. For example, we can compute the total sales by employee and year and the total sales for all employees and all years without computing any of the intermediate results:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), ())

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
1           2006        18000.00
1           2007        25000.00
2           2005        15000.00
2           2006        6000.00
3           2006        20000.00
3           2007        24000.00
NULL        NULL        120000.00

We can even compute multiple unrelated aggregations along disparate dimensions. For example, we can compute the total sales by employee and the total sales by year:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId), (Yr))

EmpId       Yr          Sales
----------- ----------- ---------------------
NULL        2005        27000.00
NULL        2006        44000.00
NULL        2007        49000.00
1           NULL        55000.00
2           NULL        21000.00
3           NULL        44000.00

Note that we could also write GROUPING SETS (EmpId, Yr) without the extra set of parenthesis, but the extra parenthesis make the intent of the query more explicit and clearly differentiate the previous query from the following query which just performs a normal aggregation by employee and year:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr))

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
2           2005        15000.00
1           2006        18000.00
2           2006        6000.00
3           2006        20000.00
1           2007        25000.00
3           2007        24000.00

Here are some additional points worth noting about the GROUPING SETS syntax:

As with any other aggregation query, if a column appears in the SELECT list and is not part of an aggregate function, it must appear somewhere in the GROUP BY clause. Thus, the following is not valid:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId), ())

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.Yr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The order of the columns within each GROUPING SET and the order of the GROUPING SETS does not matter. So both of the following queries compute the same CUBE although the order that the rows are output differs:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), (Yr), ())

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((), (Yr), (EmpId), (Yr, EmpId))

If the order that the rows are output matters, use an explicit ORDER BY clause to enforce that order.

We can nest CUBE and ROLLUP within a GROUPING SETS clause as shorthand for expressing more complex GROUPING SETS. This shorthand is most useful when we have more than three dimensions in our schema. For example, suppose we add a month column to our sales table:

CREATE TABLE Sales (EmpId INT, Month INT, Yr INT, Sales MONEY)

Now, suppose we want to compute sales for each employee by month and year, by year, and total. We could write out all of the GROUPING SETS explicitly:

SELECT EmpId, Month, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr, Month), (EmpId, Yr), (EmpId))

Or we can use ROLLUP to simplify the query:

SELECT EmpId, Month, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, ROLLUP(Yr, Month)))

Note that once again the correct use of parenthesis is critical. If we omit one set of parenthesis from the above query, the meaning changes significantly and we end up separately aggregating by employee and then computing the year and month ROLLUP for all employees.

The new GROUPING SETS syntax is available in all of SQL Server 2008 Community Technology Preview (CTP) releases.

Difference between MDF and LDF file

MDF file is the Primary database file used by MSSQL in which user data will be present.

For each MDF file there will be a LDF file associated, this is the Transaction log file used by MSSQL to store the user transactions. This file will be used by MSSQL to recover the Primary Database file if it is corrupted.

SQL PROFILER:

Tips to improve SQL Server database design and performance

Best performance is the main concern to develop a successful application. Like a coin database is the tail side (back-end) of an application. A good database design provides best performance during data manipulation which results into the best performance of an application.

During database designing and data manipulation we should consider the following key points:

Choose Appropriate Data Type

Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.
Avoid nchar and nvarchar

Does practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters etc.
Avoid NULL in fixed-length field

Does practice to avoid the insertion of NULL values in the fixed-length (char) field. Since, NULL takes the same space as desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.
Avoid * in SELECT statement

Does practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.
```
 -- Avoid
SELECT * FROM tblName
--Best practice 
SELECT col1,col2,col3 FROM tblName 
```

Use EXISTS instead of IN

Does practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.


 -- Avoid 
SELECT Name,Price FROM tblProduct 
where ProductID IN (Select distinct ProductID from tblOrder)
--Best practice 
SELECT Name,Price FROM tblProduct 
where ProductID EXISTS (Select distinct ProductID from tblOrder)

Avoid Having Clause

Does practice to avoid Having Clause since it acts as filter over selected rows. Having clause is required if you further wish to filter the result of an aggregations. Don't use HAVING clause for any other purpose.
Create Clustered and Non-Clustered Indexes

Does practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.
Keep clustered index small

Does practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly. Please refer the article Effective Clustered Indexes
Avoid Cursors

Does practice to avoid cursor since cursor are very slow in performance. Always try to use SQL Server cursor alternative. Please refer the article Cursor Alternative.
Use Table variable inplace of Temp table

Does practice to use Table varible in place of Temp table since Temp table resides in the TempDb database. Hence use of Temp tables required interaction with TempDb database that is a little bit time taking task.
Use UNION ALL inplace of UNION

Does practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.
Use Schema name before SQL objects name

Does practice to use schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.
```
 --Here dbo is schema name
SELECT col1,col2 from dbo.tblName
-- Avoid
SELECT col1,col2 from tblName 
```
Keep Transaction small

Does practice to keep transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may results into deadlocks. Please refer the article SQL Server Transactions Management
SET NOCOUNT ON

Does practice to set NOCOUNT ON since SQL Server returns number of rows effected by SELECT,INSERT,UPDATE and DELETE statement. We can stop this by setting NOCOUNT ON like as:
```
 CREATE PROCEDURE dbo.MyTestProc
AS
SET NOCOUNT ON
BEGIN
.
.
END 
```
Use TRY-Catch

Does practice to use TRY-CATCH for handling errors in T-SQL statements. Sometimes an error in a running transaction may cause deadlock if you have no handle error by using TRY-CATCH. Please refer the articleException Handling by TRY…CATCH
Use Stored Procedure for frequently used data and more complex queries

Does practice to create stored procedure for quaery that is required to access data frequently. We also created stored procedure for resolving more complex task.

Avoid prefix "sp_" with user defined stored procedure name

Does practice to avoid prefix "sp_" with user defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Hence SQL server first search the user defined procedure in the master database and after that in the current session database. This is time consuming and may give unexcepted result if system defined stored procedure have the same name as your defined procedure.

#SQL SERVER STRING FUNCTIONS

SQL string functions are used primarily for string manipulation. The following table details the important string functions:

Name	Description
ASCII()	Return numeric value of left-most character
BIN()	Return a string representation of the argument
BIT_LENGTH()	Return length of argument in bits
CHAR_LENGTH()	Return number of characters in argument
CHAR()	Return the character for each integer passed
CHARACTER_LENGTH()	A synonym for CHAR_LENGTH()
CONCAT_WS()	Return concatenate with separator
CONCAT()	Return concatenated string
CONV()	Convert numbers between different number bases
ELT()	Return string at index number
EXPORT_SET()	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD()	Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET()	Return the index position of the first argument within the second argument
FORMAT()	Return a number formatted to specified number of decimal places
HEX()	Return a string representation of a hex value
INSERT()	Insert a substring at the specified position up to the specified number of characters
INSTR()	Return the index of the first occurrence of substring
LCASE()	Synonym for LOWER()
LEFT()	Return the leftmost number of characters as specified
LENGTH()	Return the length of a string in bytes
LOAD_FILE()	Load the named file
LOCATE()	Return the position of the first occurrence of substring
LOWER()	Return the argument in lowercase
LPAD()	Return the string argument, left-padded with the specified string
LTRIM()	Remove leading spaces
MAKE_SET()	Return a set of comma-separated strings that have the corresponding bit in bits set
MID()	Return a substring starting from the specified position
OCT()	Return a string representation of the octal argument
OCTET_LENGTH()	A synonym for LENGTH()
ORD()	If the leftmost character of the argument is a multi-byte character, returns the code for that character
POSITION()	A synonym for LOCATE()
QUOTE()	Escape the argument for use in an SQL statement
REGEXP	Pattern matching using regular expressions
REPEAT()	Repeat a string the specified number of times
REPLACE()	Replace occurrences of a specified string
REVERSE()	Reverse the characters in a string
RIGHT()	Return the specified rightmost number of characters
RPAD()	Append string the specified number of times
RTRIM()	Remove trailing spaces
SOUNDEX()	Return a soundex string
SOUNDS LIKE	Compare sounds
SPACE()	Return a string of the specified number of spaces
STRCMP()	Compare two strings
SUBSTRING_INDEX()	Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING(), SUBSTR()	Return the substring as specified
TRIM()	Remove leading and trailing spaces
UCASE()	Synonym for UPPER()
UNHEX()	Convert each pair of hexadecimal digits to a character
UPPER()	Convert to uppercase

ASCII(str)

Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255.

SQL> SELECT ASCII('2');
+---------------------------------------------------------+
| ASCII('2')                                              |
+---------------------------------------------------------+
| 50                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL> SELECT ASCII('dx');
+---------------------------------------------------------+
| ASCII('dx')                                             |
+---------------------------------------------------------+
| 100                                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

BIN(N)

Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.

SQL> SELECT BIN(12);
+---------------------------------------------------------+
| BIN(12)                                                 |
+---------------------------------------------------------+
| 1100                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

BIT_LENGTH(str)

Returns the length of the string str in bits.

SQL> SELECT BIT_LENGTH('text');
+---------------------------------------------------------+
| BIT_LENGTH('text')                                      |
+---------------------------------------------------------+
| 32                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CHAR(N,... [USING charset_name])

CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.

SQL> SELECT CHAR(77,121,83,81,'76');
+---------------------------------------------------------+
| CHAR(77,121,83,81,'76')                                 |
+---------------------------------------------------------+
| SQL                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CHAR_LENGTH(str)

Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

SQL> SELECT CHAR_LENGTH("text");
+---------------------------------------------------------+
| CHAR_LENGTH("text")                                     |
+---------------------------------------------------------+
| 4                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CHARACTER_LENGTH(str)

CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:

SQL> SELECT CONCAT('My', 'S', 'QL');
+---------------------------------------------------------+
| CONCAT('My', 'S', 'QL')                                 |
+---------------------------------------------------------+
| SQL                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

SQL> SELECT CONCAT_WS(',','First name','Last Name' );
+---------------------------------------------------------+
| CONCAT_WS(',','First name','Last Name' )                |
+---------------------------------------------------------+
| First name, Last Name                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

CONV(N,from_base,to_base)

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.

SQL> SELECT CONV('a',16,2);
+---------------------------------------------------------+
| CONV('a',16,2)                                          |
+---------------------------------------------------------+
| 1010                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

ELT(N,str1,str2,str3,...)

Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().

SQL> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
+---------------------------------------------------------+
| ELT(1, 'ej', 'Heja', 'hej', 'foo')                      |
+---------------------------------------------------------+
| ej                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character .,.). The number of bits examined is given by number_of_bits (defaults to 64).

SQL> SELECT EXPORT_SET(5,'Y','N',',',4);
+---------------------------------------------------------+
| EXPORT_SET(5,'Y','N',',',4)                             |
+---------------------------------------------------------+
| Y,N,Y,N                                                 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

FIELD(str,str1,str2,str3,...)

Returns the index (position starting with 1) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

SQL> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
+---------------------------------------------------------+
| FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo')          |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.

SQL> SELECT FIND_IN_SET('b','a,b,c,d');
+---------------------------------------------------------+
| SELECT FIND_IN_SET('b','a,b,c,d')                       |
+---------------------------------------------------------+
| 2                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.

SQL> SELECT FORMAT(12332.123456, 4);
+---------------------------------------------------------+
| FORMAT(12332.123456, 4)                                 |
+---------------------------------------------------------+
| 12,332.1235                                             |
+---------------------------------------------------------+
1 row in set (0.00 sec)

HEX(N_or_S)

If N_or_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16).

If N_or_S is a string, returns a hexadecimal string representation of N_or_S where each character in N_or_S is converted to two hexadecimal digits.

SQL> SELECT HEX(255);
+---------------------------------------------------------+
| HEX(255)                                                |
+---------------------------------------------------------+
| FF                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL> SELECT 0x616263;
+---------------------------------------------------------+
| 0x616263                                                |
+---------------------------------------------------------+
| abc                                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

INSERT(str,pos,len,newstr)

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.

SQL> SELECT INSERT('Quadratic', 3, 4, 'What');
+---------------------------------------------------------+
| INSERT('Quadratic', 3, 4, 'What')                       |
+---------------------------------------------------------+
| QuWhattic                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

INSTR(str,substr)

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.

SQL> SELECT INSTR('foobarbar', 'bar');
+---------------------------------------------------------+
| INSTR('foobarbar', 'bar')                               |
+---------------------------------------------------------+
| 4                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LCASE(str)

LCASE() is a synonym for LOWER().

LEFT(str,len)

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

SQL> SELECT LEFT('foobarbar', 5);
+---------------------------------------------------------+
| LEFT('foobarbar', 5)                                    |
+---------------------------------------------------------+
| fooba                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LENGTH(str)

Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

SQL> SELECT LENGTH('text');
+---------------------------------------------------------+
| LENGTH('text')                                          |
+---------------------------------------------------------+
| 4                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes.

If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

As of SQL 5.0.19, the character_set_filesystem system variable controls interpretation of filenames that are given as literal strings.

SQL> UPDATE table_test
    -> SET blob_col=LOAD_FILE('/tmp/picture')
 -> WHERE id=1;
...........................................................

LOCATE(substr,str), LOCATE(substr,str,pos)

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.

SQL> SELECT LOCATE('bar', 'foobarbar');
+---------------------------------------------------------+
| LOCATE('bar', 'foobarbar')                              |
+---------------------------------------------------------+
| 4                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LOWER(str)

Returns the string str with all characters changed to lowercase according to the current character set mapping.

SQL> SELECT LOWER('QUADRATICALLY');
+---------------------------------------------------------+
| LOWER('QUADRATICALLY')                                  |
+---------------------------------------------------------+
| quadratically                                           |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LPAD(str,len,padstr)

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

SQL> SELECT LPAD('hi',4,'??');
+---------------------------------------------------------+
| LPAD('hi',4,'??')                                       |
+---------------------------------------------------------+
| ??hi                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

LTRIM(str)

Returns the string str with leading space characters removed.

SQL> SELECT LTRIM('  barbar');
+---------------------------------------------------------+
| LTRIM('  barbar')                                       |
+---------------------------------------------------------+
| barbar                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MAKE_SET(bits,str1,str2,...)

Returns a set value (a string containing substrings separated by .,. characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.

SQL> SELECT MAKE_SET(1,'a','b','c');
+---------------------------------------------------------+
| MAKE_SET(1,'a','b','c')                                 |
+---------------------------------------------------------+
| a                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MID(str,pos,len)

MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

OCT(N)

Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.

SQL> SELECT OCT(12);
+---------------------------------------------------------+
| OCT(12)                                                 |
+---------------------------------------------------------+
| 14                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

OCTET_LENGTH(str)

OCTET_LENGTH() is a synonym for LENGTH().

ORD(str)

If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:

  (1st byte code)
+ (2nd byte code . 256)
+ (3rd byte code . 2562) ...

If the leftmost character is not a multi-byte character, ORD() returns the same value as the ASCII() function.

SQL> SELECT ORD('2');
+---------------------------------------------------------+
| ORD('2')                                                |
+---------------------------------------------------------+
| 50                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

POSITION(substr IN str)

POSITION(substr IN str) is a synonym for LOCATE(substr,str).

QUOTE(str)

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotes and with each instance of single quote (.'.), backslash (.\.), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word .NULL. without enclosing single quotes.

SQL> SELECT QUOTE('Don\'t!');
+---------------------------------------------------------+
| QUOTE('Don\'t!')                                        |
+---------------------------------------------------------+
| 'Don\'t!'                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

NOTE: Please check if your installation has any bug with this function then don't use this function.

expr REGEXP pattern

This function performs a pattern match of expr against pattern. Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL. REGEXP is not case sensitive, except when used with binary strings.

SQL> SELECT 'ABCDEF' REGEXP 'A%C%%';
+---------------------------------------------------------+
| 'ABCDEF' REGEXP 'A%C%%'                                 |
+---------------------------------------------------------+
| 0                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Another example is:

SQL> SELECT 'ABCDE' REGEXP '.*';
+---------------------------------------------------------+
|  'ABCDE' REGEXP '.*'                                    |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Let's see one more example:

SQL> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line';
+---------------------------------------------------------+
| 'new*\n*line' REGEXP 'new\\*.\\*line'                   |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

REPEAT(str,count)

Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.

SQL> SELECT REPEAT('SQL', 3);
+---------------------------------------------------------+
| REPEAT('SQL', 3)                                      |
+---------------------------------------------------------+
| SQLSQLSQL                                         |
+---------------------------------------------------------+
1 row in set (0.00 sec)

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

SQL> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
+---------------------------------------------------------+
| REPLACE('www.mysql.com', 'w', 'Ww')                     |
+---------------------------------------------------------+
| WwWwWw.mysql.com                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)

REVERSE(str)

Returns the string str with the order of the characters reversed.

SQL> SELECT REVERSE('abcd');
+---------------------------------------------------------+
| REVERSE('abcd')                                         |
+---------------------------------------------------------+
| dcba                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

RIGHT(str,len)

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

SQL> SELECT RIGHT('foobarbar', 4);
+---------------------------------------------------------+
| RIGHT('foobarbar', 4)                                   |
+---------------------------------------------------------+
| rbar                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

RPAD(str,len,padstr)

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

SQL> SELECT RPAD('hi',5,'?');
+---------------------------------------------------------+
| RPAD('hi',5,'?')                                        |
+---------------------------------------------------------+
| hi???                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

RTRIM(str)

Returns the string str with trailing space characters removed.

SQL> SELECT RTRIM('barbar   ');
+---------------------------------------------------------+
| RTRIM('barbar   ')                                      |
+---------------------------------------------------------+
| barbar                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SOUNDEX(str)

Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

SQL> SELECT SOUNDEX('Hello');
+---------------------------------------------------------+
| SOUNDEX('Hello')                                        |
+---------------------------------------------------------+
| H400                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

expr1 SOUNDS LIKE expr2

This is the same as SOUNDEX(expr1) = SOUNDEX(expr2).

SPACE(N)

Returns a string consisting of N space characters.

SQL> SELECT SPACE(6);
+---------------------------------------------------------+
| SELECT SPACE(6)                                         |
+---------------------------------------------------------+
| '      '                                                |
+---------------------------------------------------------+
1 row in set (0.00 sec)

STRCMP(str1, str2)

Compares two strings and returns 0 if both strings are equal, it returns -1 if the first argument is smaller than the second according to the current sort order otherwise it returns 1.

SQL> SELECT STRCMP('MOHD', 'MOHD');
+---------------------------------------------------------+
| STRCMP('MOHD', 'MOHD')                                  |
+---------------------------------------------------------+
| 0                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Another example is:

SQL> SELECT STRCMP('AMOHD', 'MOHD');
+---------------------------------------------------------+
| STRCMP('AMOHD', 'MOHD')                                 |
+---------------------------------------------------------+
| -1                                                      |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Let's see one more example:

SQL> SELECT STRCMP('MOHD', 'AMOHD');
+---------------------------------------------------------+
| STRCMP('MOHD', 'AMOHD')                                 |
+---------------------------------------------------------+
| 1                                                       |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

SQL> SELECT SUBSTRING('Quadratically',5);
+---------------------------------------------------------+
| SSUBSTRING('Quadratically',5)                           |
+---------------------------------------------------------+
| ratically                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL> SELECT SUBSTRING('foobarbar' FROM 4);
+---------------------------------------------------------+
| SUBSTRING('foobarbar' FROM 4)                           |
+---------------------------------------------------------+
| barbar                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL> SELECT SUBSTRING('Quadratically',5,6);
+---------------------------------------------------------+
| SUBSTRING('Quadratically',5,6)                          |
+---------------------------------------------------------+
| ratica                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

SQL> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
+---------------------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', 2)                |
+---------------------------------------------------------+
| www.mysql                                               |
+---------------------------------------------------------+
1 row in set (0.00 sec)

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

TRIM([remstr FROM] str)

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

SQL> SELECT TRIM('  bar   ');
+---------------------------------------------------------+
| TRIM('  bar   ')                                        |
+---------------------------------------------------------+
| bar                                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
+---------------------------------------------------------+
| TRIM(LEADING 'x' FROM 'xxxbarxxx')                      |
+---------------------------------------------------------+
| barxxx                                                  |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
+---------------------------------------------------------+
| TRIM(BOTH 'x' FROM 'xxxbarxxx')                         |
+---------------------------------------------------------+
| bar                                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

SQL> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
+---------------------------------------------------------+
| TRIM(TRAILING 'xyz' FROM 'barxxyz')                     |
+---------------------------------------------------------+
| barx                                                    |
+---------------------------------------------------------+
1 row in set (0.00 sec)

UCASE(str)

UCASE() is a synonym for UPPER().

UNHEX(str)

Performs the inverse operation of HEX(str). That is, it interprets each pair of hexadecimal digits in the argument as a number and converts it to the character represented by the number. The resulting characters are returned as a binary string.

SQL> SELECT UNHEX('4D7953514C');
+---------------------------------------------------------+
| UNHEX('4D7953514C')                                     |
+---------------------------------------------------------+
| SQL                                                   |
+---------------------------------------------------------+
1 row in set (0.00 sec)

The characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If UNHEX() encounters any non-hexadecimal digits in the argument, it returns NULL.

UPPER(str)

Returns the string str with all characters changed to uppercase according to the current character set mapping.

SQL> SELECT UPPER('Allah-hus-samad');
+---------------------------------------------------------+
| UPPER('om nam shivay')                                |
+---------------------------------------------------------+
| OM NAM SHIVAY                                        |
+---------------------------------------------------------+
1 row in set (0.00 sec)


What is a Schema in SQL Server 2005?




What is a Schema?

In Microsoft SQL Server 2005, a schema is a collection of objects adhering to the ANSI SQL-92 standard.

The ANSI SQL-92 standard defines a schema as a collection of database objects that are owned by a single principal and form a single namespace.

All objects within a schema must be uniquely named and a schema must be uniquely named in the database catalog. SQL Server 2005 breaks the link between users and schemas, users do not own objects. Schemas own objects and principals own schemas.

A schema can be owned by either a primary or secondary principal, with the term “principal” meaning any SQL Server entity that can access securable objects.

Principle types that can own schemas:

Primary
SQL Server Login
Database User
Windows Login


Secondary
SQL Server Roles
Windows Groups
Default Schemas




Users can now have a default schema assigned using the DEFAULT_SCHEMA option of CREATE USER and ALTER USER commands. If no default schema is supplied for a user then DBO will be used as the default schema.

——————————————————————————–

SQL Server 2005 Schema Objects

Tables
Indexes
Views
Synonyms
Stored Procedures
Triggers
Functions
Assemblies
Types
Defaults

Pages

Sunday, April 27, 2014

MS SQL SEVER