SET NOCOUNT

Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.

Syntax: SET NOCOUNT { ON | OFF } 

Remarks

When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.

The @@ROWCOUNT function is updated even when SET NOCOUNT is ON.

Examples

The following example prevents the message about the number of rows affected from being displayed.

USE AdventureWorks2012;

GO

SET NOCOUNT OFF;

GO

-- Display the count message.

SELECT TOP(5)LastName

FROM Person.Person

WHERE LastName LIKE 'A%';

GO

-- SET NOCOUNT to ON to no longer display the count message.

SET NOCOUNT ON;

GO

SELECT TOP(5) LastName

FROM Person.Person

WHERE LastName LIKE 'A%';

GO

-- Reset SET NOCOUNT to OFF

SET NOCOUNT OFF;

GO

Read More

Built-in Functions (Transact-SQL)

CHARINDEX - Searches an expression for another expression and returns its starting position if found.

DECLARE @FullName VARCHAR(100)

SET @FullName = 'Clark Kent'

select CHARINDEX(' ', @FullName)
Output – 6

STUFF - The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
--Syntax STUFF(character_expression , start , length , replaceWith_expression)

DECLARE @FullName VARCHAR(100)

DECLARE @Alias VARCHAR(20)

SET @FullName = 'Clark Kent'

SET @Alias = ' "Superman" '

SELECT STUFF(@FullName, CHARINDEX(' ', @FullName), 1, @Alias) AS [FullName]

Output - Clark "Superman" Kent

SELECT STUFF('abcdef', 2, 3, 'ijklmn');

Output - aijklmnef 

Replace -  Replaces all occurrences of a specified string value with another string value.

Syntax - REPLACE (string_expression , string_pattern , string_replacement)

SELECT REPLACE('ABCDEFGH','AB','K')

Output – KCDEFGH

REPLICATE - Repeats a string value a specified number of times.

Syntax- REPLICATE ( string_expression ,integer_expression ) 

SELECT 'ID'+ REPLICATE('0', 4) ;-- output:ID0000

Reverse - Returns the reverse order of a string value.

Syntax - REVERSE ( string_expression )

SELECT REVERSE('ABCD') 

Output – DCBA

ASCII – returns ASCII value of a specified string

SELECT ASCII('A')  

Output – 65

Concat – Returns a string that is the result of concatenating two or more string values.

select CONCAT('AB','C'),CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' )

Output- ABC   Happy Birthday 11/25

LEFT - Returns the left part of a character string with the specified number of characters.

SELECT LEFT('abcdefg',2)      Output - ab

SELECT RIGHT('abcdefg',2)      Output - fg

SELECT LEN('abcdefg')      Output – 7 

SELECT LOWER('ABCDEFG') AS Lower      Output - abcdefg

SELECT UPPER('abcdefg') as uppar;      Output – ABCDEFG

LTRIM - Returns a character expression after it removes leading blanks.

select LTRIM(' abcdefg dfj')     Output – abcdefg dfj

select RTRIM('abcdefg dfj ')     Output – abcdefg dfj

STR - Returns character data converted from numeric data.

Syntax - STR ( float_expression [ , length [ , decimal ] ] )

SELECT STR(123.45, 6, 1);

GO

Output - 123.5

SUBSTRING - Returns part of a character, binary, text, or image expression in SQL Server.

Syntax- SUBSTRING ( expression ,start , length )

SELECT x = SUBSTRING('abcdef', 2, 3);

Output -  bcd

CHAR – Converts an int ASCII code to a character.

SELECT char(65)

Output – A

SPACE – Returns a string of repeated spaces.

Syntax- SPACE ( integer_expression )

SELECT  SPACE(20)+'abcdefg'+ ','

Output -                      abcdefg,

Read More

Different Types of SQL Joins

S

ql joins are used to fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in Sql join. Basically data tables are related to each other with keys.

I have 2 Tables  in sql server,

Table 1 with Primary Key

Table2 with Foreign Key

Types of Joins

In Sql Server we have only three types of joins. Using these joins we fetch the data from multiple tables based on condition.

Inner Join

Inner join returns only those records/rows that match/exists in both the tables. Syntax for Inner Join is as

Outer Join

  

    refer the  following url : Click here

Cross Join

Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. Syntax for right outer Join is as :

Self Join 

Self join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself. Hence Self join is not a type of Sql join.

To understand Self Join, suppose we following two tables and data in these two tables is shown in figure.

CREATE TABLE emp

(

id int NOT NULL primary key,

name varchar(100) NULL,

designation varchar(50) NULL,

supid int foreign key references emp(id) ) -- In this table we have a Foreign key supid that references its own Primary Key id. We use it for Self Join

INSERT INTO emp(id,name,designation) VALUES(1,'mohan','Manger')

INSERT INTO emp(id,name,designation,supid) VALUES(2,'raj kumar','SE',1)

 INSERT INTO emp(id,name,designation) VALUES(3,'bipul kumar','Manager')

 INSERT INTO emp(id,name,designation,supid) VALUES(4,'mrinal kumar','SE',2)

 INSERT INTO emp(id,name,designation,supid) VALUES(5,'jitendra kumar','SE',2)

 select * from emp

Code for SelfJoin is : 

Regards: Suraj K Mad.

Read More

Basics of SQL Commands

SQL commands are a set of instructions that are used to interact with the database like Sql Server, MySql, Oracle etc. SQL commands are responsible to create and to do all the manipulation on the database. These are also responsible to give/take out access rights on a particular database

Sql Commands Category

We have different sql commands for different-different purpose. We can grouped Sql Commands into five major categories depending on their functionality.

01.       Data Definition Language (DDL)

These SQL commands are used to create, modify, and drop the structure of database objects like table, view, procedure, indexes etc. In this category we have CREATE, ALTER, DROP and TRUNCATE commands.

Note:-

1.            Only with DDL commands we need to write keyword (like table, procedure, view, index, function) with the syntax of command.

2.            These commands are used to create/modify the structure of the database object.

Example:

CREATE TABLE TABLE_NAME

(

COL1 VARCHAR(10),

COL2 VARCHAR(20),

);

--Here "TABLE" is a keyword that is used to create table "TABLE_NAME"

CREATE VIEW VIEW_NAME

AS

BEGIN

 SELECT * FROM EMP_TABLE

END

--Here "VIEW" is a keyword that is used to create VIEW "VIEW_NAME"

02.      Data Manipulation Language (DML)

These SQL commands are used to store, modify, and delete data from database tables. In this category we have INSERT, UPDATE, and DELETE commands.

03.     Data Query Language (DQL)

These SQL commands are used to fetch/retrieve data from database tables. In this category we have only SEELCT command.

04.     Transaction Control Language (TCL)

These SQL commands are used to handle changes which affect the data in database. Basically we use these commands with in the transaction or to make a stable point during changes in database at which we can rollback the database state if required. In this category we have SAVEPOINT, ROLLBACK and COMMIT commands.

05.      Data Control Language (DCL)

These SQL commands are used to implement security on database objects like table,view,stored procedure etc. In this category we have GRANT and REVOKE commands.

Note:-

Grant Command : This command is used to give permission to specific users on specific database objects like table, view etc.

Revoke Command : This command is used to take out permission from specific users on specific database objects like table, view etc.

Read More