SQL SERVER TUTORIAL IN DEPTH

The blog is to help users about sql server

Others

LightBlog

Breaking

Wednesday 10 December 2014

By at No comments:

Friday 7 November 2014

Order By Surname in a Table
November 07, 2014

Order By Surname in a Table

select * from EmployeeDetail

 Id EmployeeName
1 Anurag Nayak
2  Ritika mohanty
3     Siba Dalai
4 Biswa Panda

Trick 1 ;with cte(id,name,finalIndex,startIndex)as
(
select id,
EmployeeName,
LEN(EmployeeName) as LengthName,
CHARINDEX(' ',EmployeeName) as StartIndex from dbo.EmployeeDetail
)
select id,name,SUBSTRING(name,startIndex+1,finalIndex) as final from cte 
order by final
id       name                               final
3 Siba Dalai                       Dalai
2 Ritika mohanty
1 Anurag Nayak                Nayak
4 Biswa Panda                   Panda

Trick 2

 select EmployeeName
from employeedetail
order by REVERSE(substring(reverse(EmployeeName),1,CHARINDEX(' ',reverse(EmployeeName))))

0
By at No comments:

Tuesday 29 July 2014

July 29, 2014

One tricky question asked in interview on Having and group by Clause

select * from EmployeeSalary order by id














--o/p [Need employee id and the count that crossed more than 5000]
--employeeid and count(> 5000)
--1 1
--2 1
--3 2

select id,COUNT(EmployeeSalary) from EmployeeSalary
where EmployeeSalary > 5000
group by id







--Now i want those employees id who has more than 1 count [> 5000] salary.

select id,COUNT(EmployeeSalary) from EmployeeSalary
where EmployeeSalary > 5000
group by id
having COUNT(EmployeeSalary) >1








--> After Group by No Where clause we know that.





0
By at No comments:
A nice way to truncate all the tables
July 29, 2014

A nice way to truncate all the tables


I wanted to truncate all tables in a cursor. But I was unable to do it as some table were being reference by foreign key constraint.

This is the way I got it. 

1)Alter table with no check  constraint
2)Delete 
3)Alter table with Check Constraint
4) Reseed it

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
EXEC sp_MSForEachTable "DELETE FROM ?"
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
declare @table varchar(100)
declare NfrTable_cursor cursor
for
select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME
open NfrTable_cursor
fetch next from NfrTable_cursor into @table
WHILE @@FETCH_STATUS = 0
BEGIN
 DBCC CHECKIDENT (@table, reseed, 0); 
fetch next from NfrTable_cursor into @table
end
close NfrTable_cursor
deallocate NfrTable_cursor
0
By at No comments:

Saturday 4 January 2014

January 04, 2014

Get double byte / Single byte data from the table

In NVARchar DataType column we can store both Single byte and Double byte data. Many a times we want to know how many records have Single byte or Double byte data. Let us understand this with an example.

--CREATE TABLE dbo.library
-- (libraryId int, username NVarchar(50))
--GO
--Populate Customer table with single byte and
--double byte CustomerName records
INSERT INTO dbo.library (libraryId, username)
VALUES (1, 'Anurag')

INSERT INTO dbo.library (libraryId, username)
VALUES (2, N'尊敬卿')

INSERT INTO dbo.library (libraryId, username)
VALUES (3, 'Siba')
GO


SELECT *
FROM dbo.library
WHERE username = CAST(username AS VARCHAR(50))








SELECT *
FROM dbo.library
WHERE username != CAST(username AS VARCHAR(50))






------------------------------------------------------------------------
Can we store double byte data type in varchar ?

Lets see
CREATE TABLE dbo.library1
 (library1Id int, username Varchar(50))
GO
--Populate Customer table with single byte and
--double byte CustomerName records
INSERT INTO dbo.library1 (library1Id, username)
VALUES (1, 'Anurag')

INSERT INTO dbo.library1 (library1Id, username)
VALUES (2, N'尊敬卿')

INSERT INTO dbo.library1 (library1Id, username)
VALUES (3, 'Siba')
GO


SELECT *
FROM dbo.library1


0
By at No comments:
January 04, 2014

To Search a text from Stored procedure


alter proc usp_find_word
as
begin

 select 'anuragggggggggggg'
declare @varrrrrrr varchar(50)

 end

 -------------------------

 SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'

 SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%@varrrrrrr%'







Try this too

 SELECT *, OBJECT_DEFINITION(object_id) ObjectDefinition
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'
0
By at No comments:
January 04, 2014

Joining Two Tables without any Common Column between them



create table #temp1
(
id int,
username varchar(50)
)

 create table #temp2
(
  
  age int,
  sex varchar(1)

 )

 insert into #temp1
select 1,'anurag'
union all select 2,'abhi'
union all select 3,'binda'
union all select 4,'asu'

 insert into #temp2
select 24,'m'
union all select 22,'m'
union all select 25,'m'

 select * from #temp1
select * from #temp2

 ----------------Now we have to combine the two table ..lets try..-----------------

 ;with cte(id,username,row1)
as
(
select id,username,row_number() over (order by id)
from #temp1
),
cte1(age,sex,row2)
as
(
select age,sex,row_number() over (order by age)
from #temp2
),
cte2
as
(
select c1.id,c1.username,c2.age,c2.sex
from cte c1
left join cte1 c2 on c1.row1=c2.row2
)
select * from cte2
-----------------------------------------------------------------------------------




0
By at No comments:
January 04, 2014

How to find the text and get to know the stored procedure if you have lot of stored procedures

I have create a stored Procedure. As you know there are number of stored procedure and its tough to search a text if you want to find by text to know which sp has that text. The small technique is mentioned below.
alter proc usp_find_word
as
begin
select 'anuragggggggggggg'
declare @varrrrrrr varchar(50)
end

 --------------------------------------------------------------------------------------------------------

 SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'

 SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%@varrrrrrr%'

 ---------------------------------------------------------------------------------------------------------




Try this too

 SELECT *, OBJECT_DEFINITION(object_id) ObjectDefinition
FROM sys.objects

WHERE OBJECT_DEFINITION(object_id) LIKE '%anuragggggggggggg%'


0
By at No comments:
January 04, 2014

Error Line,Error Severity, Error State, Error Number,Throw , Raise Error in Sql server

ERROR_LINE

The Error_Line function returns the error Line number from code. This function does not accept any parameters. The Error_Line() function is used to determine the error line which occurred in a try block. The following query returns the line number where the error occurred:


BEGIN TRY
Select 14/ 0
END TRY

BEGIN CATCH
SELECT 'An error has occurred at line ' + Cast(ERROR_line() as Varchar )
END CATCH





















ERROR NUMBER
Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run.
This function may be called anywhere within the scope of a CATCH block.
ERROR_NUMBER returns the error number regardless of how many times it is run, or where it is run within the scope of the CATCH block. 
BEGIN TRY

Select 11/ 0
END TRY

BEGIN CATCH

SELECT 'An error has occurred at line: ' + Cast(ERROR_Number() as Varchar )

END CATCH



















ERROR STATE
Returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.
When called in a CATCH block, returns the state number of the error message that caused the CATCH block to be run.
Returns NULL if called outside the scope of a CATCH block.
ERROR_STATE may be called anywhere within the scope of a CATCH block.
ERROR_STATE returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to functions like @@ERROR, which only returns the error number in the statement immediately after the one that causes an error, or in the first statement of a CATCH block

BEGIN TRY
Select 11/ 0
END TRY
BEGIN CATCH
SELECT 'An error has occurred at line: ' + Cast(ERROR_State() as Varchar )
END CATCH


















ERROR SEVERITY
0 to 10Messages with a severity level of 0 to 10 are informational messages and not actual errors.
11 to 16Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user. For example, the error message returned in the invalid update query, used earlier, had a severity level of 16.
17Severity level 17 indicates that SQL Server has run out of a configurable resource, such as locks. Severity error 17 can be corrected by the DBA, and in some cases, by the database owner.
18Severity level 18 messages indicate nonfatal internal software problems.
19Severity level 19 indicates that a nonconfigurable resource limit has been exceeded.
20Severity level 20 indicates a problem with a statement issued by the current process.
21Severity level 21 indicates that SQL Server has encountered a problem that affects all the processes in a database.
22Severity level 22 means a table or index has been damaged. To try to determine the extent of the problem, stop and restart SQL Server. If the problem is in the cache and not on the disk, the restart corrects the problem. Otherwise, use DBCC to determine the extent of the damage and the required action to take.
23Severity level 23 indicates a suspect database. To determine the extent of the damage and the proper action to take, use the DBCC commands.
24Severity level 24 indicates a hardware problem.
25Severity level 25 indicates some type of system error.
BEGIN TRY
Select 11/ 0
END TRY
BEGIN CATCH
SELECT 'An error has occurred at line: ' + Cast(ERROR_Severity() as Varchar )
END CATCH





















Severity levels 11 to 16 are generated as a result of user problems and can be fixed by the user.

--In SQL Server 2005/2008, if you want to re-throw an error in a catch block of a TRY CATCH statement, you have to use RAISERROR with ERROR_MESSAGE() and ERROR_SEVERITY(). But in SQL Server 2012 you can use only a THROW statement to re-throw an error in a catch block of a TRY CATCH statement.
--We divide a number by zero. A THROW statement is used to raise exceptions; see: 


BEGIN TRY DECLARE @VALUE INT SET @VALUE = 12/ 0
END TRY
BEGIN CATCH
throw
END CATCH
--Now press F5 to execute it; the results will be:








------------------------------------------------------------------------------------------------------------------------

Raise Error Vs Throw

Both RAISERROR and THROW statements are used to raise an error in Sql Server. The journey of RAISERROR started from Sql Server 2005, where as the journey of THROWstatement has just began with Sql Server 2012. obviously, Microsoft suggesting us to start using THROW statement instead of RAISERROR. THROW statement seems to be simple and easy to use than RAISERROR.

 RAISE ERROR
 THROW
Introduced in SQL SERVER 2005
Introduced in SQL SERVER 2012.
It always generates new exception and results in the loss of the original exception details. Below example demonstrates this:
BEGIN TRY
  DECLARE @result INT
--Generate divide-by-zero error
  SET @result = 55/0
END TRY
BEGIN CATCH
--Get the details of the error
--that invoked the CATCH block
 DECLARE
   @ErMessage NVARCHAR(2048),
   @ErSeverity INT,
   @ErState INT
  
 SELECT
   @ErMessage = ERROR_MESSAGE(),
   @ErSeverity = ERROR_SEVERITY(),
   @ErState = ERROR_STATE()
  
 RAISERROR (@ErMessage,
             @ErSeverity,
             @ErState )
END CATCH

RESULT:
Msg 50000, Level 16, State 1, Line 19
Divide by zero error encountered.
NOTE:The actual line number of the code which generated Divided By Zero error here is 4, but the exception message returned by RAISERROR is showiung it as 19. Also the error number corresponding to divide by zero error is 8134 in the SYS.Messages table, but the one returned by RAISERROR is 50000.
 To Re-THROW the original exception caught in the TRY Block, we can just specify the THROW statement without any parameters in the CATCH block. Below example demonstrates this:
BEGIN TRY
  DECLARE @result INT
--Generate divide-by-zero error
  SET @result = 55/0
END TRY
BEGIN CATCH
    THROW
END CATCH

RESULT:
RESULT:
Msg 8134, Level 16, State 1, Line 4
Divide by zero error encountered.
With above example it is clear that THROW statement is very simple for RE-THROWING the exception. And also it returns correct error number and line number.
 In the below Batch of statements the PRINT statement after RAISERROR statement will be executed.
BEGIN
 PRINT 'BEFORE RAISERROR'
 RAISERROR('RAISERROR TEST',16,1)
 PRINT 'AFTER RAISERROR'
END


RESULT:
BEFORE RAISERROR
Msg 50000, Level 16, State 1, Line 3
RAISERROR TEST
AFTER RAISERROR
  In the below Batch of statements the PRINT statement after THROW statement will not executed.
BEGIN
    PRINT 'BEFORE THROW';
    THROW 50000,'THROW TEST',1
    PRINT 'AFTER THROW'
END

RESULT:
BEFORE THROW
Msg 50000, Level 16, State 1, Line 3
THROW TEST
BEGIN TRY
  DECLARE @RESULT INT = 55/0   
END TRY
BEGIN CATCH
  PRINT 'BEFORE THROW';
  THROW;
  PRINT 'AFTER THROW'
END CATCH
  PRINT 'AFTER CATCH'
RESULT:
BEFORE THROW
Msg 8134, Level 16, State 1, Line 2
Divide by zero error encountered.
Can set the severity of error
 There is no severity parameter. The exception severity is always set to 16. (unless re-throwing in a CATCH block)
 The statement before the THROW statement is not  followed by the semicolon (;) statement terminator.
 The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

CREATE PROCEDURE dbo.TryCatchTest
AS
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage; 
END CATCH                                                
0
By at No comments:

 test