SQL SEVER DB Objects - Create Syntax's

SQL SERVER

( Basic Syntax Questions & Answers)

Question 1: Create Function without Parameter?

Ans:      create function AddFun()

returns int

as begin

Declare @Result as int

Set @Result=2+3

return @Result

end

For O/P: Print test.dbo.AddFun() is 5

Question 2: Create Function with Parameter?

Ans:      create function AddFun1(@NumIst int,@NumpIInd int)

returns int

as begin

Declare @Result as int

Set @Result=@NumIst+@NumpIInd

return @Result

end

For O/P: Print test.dbo.AddFun1(10,20) is 30

Question 3: How to Create Procedure?

Ans:   A) for Insert

              create procedure sp_NameofProc

(

 @Id int,

 @Name varchar(50),

 @Age  Varchar(50),

 @City Varchar(50)

)

as begin

insert into tableName(Id,[Name],Age,City)values(@Id,@Name,@Age,@City)

end

      B) for Update

  

            create procedure sp_NameofProc1

(

 @Id int,

 @Name varchar(50),

 @Age  Varchar(50),

 @City Varchar(50)

)

as begin

update tableName set Name='Anil' where Id=@Id

end

C) For select

      create procedure sp_NameofProc2

as begin

select * from TableName

      end

Question 4: How to call a function in Procedure?

Ans:      Step1: create a function

              create function AddFun_sp(@NumIst int,@NumpIInd int)

returns int

as begin

Declare @Result as int

Set @Result=@NumIst+@NumpIInd

return @Result

end

               Step2: Call a function in store procedure

create procedure sp_CallAfunction

(

 @IstVar int,

 @IIndVar int

)

as begin

Declare @sp as int

set @sp=dbo.AddFun1(@IstVar,@IIndVar)

print @sp

end

               Step3: Execute Store procedure for Output

    

      execute sp_CallAfunction '10','20'

                  OR

execute dbo.sp_CallAfunction '10','20'

Calling one stored procedure from another stored procedure.

//Procedure 1

Create proc stp_Test1

as

begin

    select * from table

end

//Procedure 2

Create proc stp_Test2

as

begin

     // To call the Procedure stp_Test1 in this procedure

     // Its return that Procedure output.

     exec stp_Test1    

end

Question 5: How to Create Cursor in Sql Server?

Ans:    

Step1:  Declare a cursor

declare @EmpName varchar(50)

declare @EmpDepartment varchar(50)

Step2:  Define a cursor

declare Cursor_Emply cursor for

select EmpName,EmpDepartment from Emply

Step3: Open a cursor

open Cursor_Emply

Step4: fetch a cursor

fetch Cursor_Emply into @EmpName,@EmpDepartment

while(@@fetch_status=0)

begin

print '* EmpName= '+@EmpName

print '  EmpDepartment= '+@EmpDepartment

--fetch cursor for next rows

fetch Cursor_Emply into @EmpName,@EmpDepartment

end

Step5: Close a cursor

close Cursor_Emply

Step6: deallocate a cursor

deallocate Cursor_Emply

  

Question 6: How to Call a Cursor in Store procedure?

Ans:

create procedure SPCursor_Emply

as begin

declare @EmpName varchar(50)

declare @EmpDepartment varchar(50)

declare Cursor_Emply cursor for

select EmpName,EmpDepartment from Emply

open Cursor_Emply

fetch Cursor_Emply into @EmpName,@EmpDepartment

while(@@fetch_status=0)

begin

print '* EmpName= '+@EmpName

print '  EmpDepartment= '+@EmpDepartment

--fetch cursor for next rows

fetch Cursor_Emply into @EmpName,@EmpDepartment

end

close Cursor_Emply

deallocate Cursor_Emply

end

Question 7: How to Create trigger?

Ans:  ---> Example: view in details Trigger in sql server

 

CREATE TRIGGER myTrigger3

 ON employee

 FOR UPDATE, INSERT, DELETE

 AS

                 SELECT 'Before INSERT'

                 INSERT Employee (ID, Name) VALUES (31, 'Rick')

     go

                 SELECT 'Before UPDATE'

                 UPDATE Employee

                 SET Name = 'Test'

                 WHERE ID = 1

     go

                 SELECT 'Before DELETE'

                 DELETE FROM Employee WHERE ID = 1

Question 9: How to Create Index?

Ans:  create index index_Employee on Employee(Id, Name)

Question 10: How to Create Nonclustered Index?

Ans:

 CREATE NONCLUSTERED INDEX Indexname_Employee ON Employee

(

                [EmpName] ASC or desc,

                [EmpDepartment] ASC or desc

)

                                             OR

CREATE NONCLUSTERED INDEX [Indexname_Anil1] ON [dbo].[Emply]

(

                [EmpName] ASC or desc,

                [EmpDepartment] ASC or desc

)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

Question 11: How to Create Clustered Index?

Ans:

 CREATE CLUSTERED INDEX Indexname_EmlloyeeClust ON Employee

(

                [EmpName] ASC or desc,

                [EmpDepartment] ASC or desc

)

                                                         OR

CREATE CLUSTERED INDEX Indexname_EmlloyeeClust ON Employee

(

                [EmpName] ASC,

                [EmpDepartment] ASC

)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]

Question: How to Error Handling in StoreProcedure ?

Ans:1

create proc sp_emprecord

As begin

select * from Emp_Record1 //Emp_Record1 table name are not in database

end

begin try

execute sp_emprecord

end try

begin catch

 select

  ERROR_NUMBER() as ErrorNumber,

  ERROR_MESSAGE() as ErrorMessage,

  ERROR_STATE() as ErrorState,

  ERROR_PROCEDURE() as ErrorProcedure,

  ERROR_LINE() as ErrorLine;

end catch

Ans:2   

create Proc sp_ErrorInsert

(

                @Emp_id nvarchar(50),

                @Emp_name nvarchar(50),

                @Emp_salary int,

                @Emp_age  int,

                @Emp_qua nvarchar(50)

)

As begin

insert into Emp_Record

   (

                                Emp_id,

                                Emp_name ,

                                Emp_salary ,

                                Emp_age ,

                                Emp_qua

    )values

    (

                                @Emp_id,

                                @Emp_name ,

                                @Emp_salary ,

                                @Emp_age ,

                                @Emp_qua

   )

End

go

Begin Try

 execute sp_ErrorInsert

End Try

Begin Catch

SELECT 

  ERROR_MESSAGE() as ErrorMessage,

  ERROR_LINE() as ErreoLine,

  ERROR_STATE() as ErrorState,

  ERROR_PROCEDURE() as ErrorProcedure,

  ERROR_NUMBER() as ReeoeNumber;

      End Catch

Note:

Function Description  are:
ERROR_NUMBER()  Returns the number of the error
ERROR_SEVERITY()  Returns the severity
ERROR_STATE()  Returns the error state number
ERROR_PROCEDURE()  Returns the name of the stored procedure or trigger where the error occurred
ERROR_LINE()  Returns the line number inside the routine that caused the error
ERROR_MESSAGE()  Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Question: How to Error Handling in Store Procedure with @@Error &     @@ROWCOUNT ?

Ans:   Use Of @@ROWCOUNT

insert into Emp_Record values('ak04','aks',11111,26,'Master Degree')

if @@ROWCOUNT=0

print'Error !: No inserted record here..'

else

print'Sucsesfully Inserted Record !'

      OutPut:   (1 row(s) affected)

             Sucsesfully Inserted Record !

          Again Execute this query the Output is:

         Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK_Emp_Record'. Cannot insert duplicate key       in object 'dbo.Emp_Record'.

The statement has been terminated.

Error! : No inserted record here..

-- ============================================================

-- Author:                                Anil Singh

-- Create date: 1/10/2011

-- Description:         Returns [ListEvent]

-- Parameters:  FromDate datetime, ToDate datetime

-- =============================================================

ALTER PROCEDURE  [spx].[ListEvent] --'2011-09-26 20:17:20.357' ,'2011-10-06 20:17:20.357'

(

 @FromDate datetime,

 @ToDate datetime

)

AS BEGIN

                BEGIN TRY

                                SELECT    EventName,

                                                                EventDesc,

                                                                EventDate,

                                                                ProhibitEntriesTime,

                                                                ClaimTime,

                                                                RedrawUnclaimed

                                FROM pr.PromoEvents

                                WHERE EventDate = @FromDate AND EventDate = @ToDate

    

      

    END TRY

   

    BEGIN CATCH       

                declare @Error varchar(8000)

                                set @Error= Convert(varchar,ERROR_NUMBER()) + '*****' + Convert(varchar(4000),ERROR_MESSAGE()) + '*****' + isnull(Convert(varchar,ERROR_PROCEDURE()),'GetPromotionList') + '*****' + Convert(varchar,ERROR_LINE()) + '*****' + Convert(varchar,ERROR_SEVERITY()) + '*****' + Convert(varchar,ERROR_STATE())

                                RAISERROR (@Error, -- Message text.

                                                        16, -- Severity.

                                                        1 -- State.

                                                       );

    END CATCH

 END

  

Using RAISERROR

RAISERROR is used to return messages back to applications using the same format as a system error or warning message generated by the SQL Server Database Engine.

RAISERROR can return either:

• A user-defined error message that has been created using the sp_addmessage system stored procedure. These are messages with a message number greater than 50000 that can be viewed in the sys.messages catalog view.
• A message string specified in the RAISERROR statement.

RAISERROR can also:

• Assign a specific error number, severity, and state.
• Request that the error be logged in the Database Engine error log and the Microsoft Windows application log.
• Substitute argument values into the message text, much like the C language printf_s function.

Both RAISERROR and PRINT can be used to return informational or warning messages to an application.

When you use RAISERROR to return a user-defined error message, use a different state number in each RAISERROR that references that error. This can help in diagnosing the errors when they are raised..

Use of RAISERROR:

ü Help in troubleshooting Transact-SQL code.

ü  Check the values of data.

ü  Return messages that contain variable text.

ü  Cause execution to jump from a TRY block to the associated CATCH  block…

ü  Return error information from the CATCH block to the calling batch or application

Example: Use of RAISERROR

Begin Try

 execute sp_emprecord

End Try

BEGIN CATCH

    DECLARE @ErrorMessage NVARCHAR(4000);

    DECLARE @ErrorSeverity INT

    DECLARE @ErrorState INT

    SELECT

           @ErrorMessage = ERROR_MESSAGE(),

           @ErrorSeverity = ERROR_SEVERITY(),

           @ErrorState = ERROR_STATE();

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

END CATCH;

  

-- =============================================

-- Author:                                <Author,,Anil Singh>

-- Create date: <Create Date, ,01/12/2011>

-- Description:         <Description, ,Testing to function>

-- =============================================

CREATE FUNCTION sumofTwonum

(

                -- Add the parameters for the function here

                @Param1 int,

                @Param2 int

)

RETURNS int

AS

BEGIN

                -- Declare the return variable here

                DECLARE @Result int

                -- Add the T-SQL statements to compute the return value here

    SET @Result=@Param1*@Param2;

                -- Return the result of the function

                RETURN @Result

END

GO

-- =============================================

-- Author:                                <Author,,Anil Singh>

-- Create date: <Create Date,,01/12/2011>

-- Description:         <Description,,Call a function in procedure>

-- =============================================

CREATE PROCEDURE sp_callFunction

(

                -- Add the parameters for the stored procedure here

                @Param11 int,

                @Param22 int

)

AS

BEGIN

                -- SET NOCOUNT ON added to prevent extra result sets from

                -- interfering with SELECT statements.

                SET NOCOUNT ON;

    DECLARE @spResult as int --varchar(100);   

    BEGIN TRANSACTION

   

    -- Call the Function.

    SET @spResult=dbo.sumofTwonum(@Param11,@Param22);   

    COMMIT TRANSACTION

    print 'Call function in Procedure'+@spResult;                  

END

GO

-- EXAMPLE: WHILE Loop

DECLARE @intFlag INT

SET @intFlag = 1

WHILE (@intFlag <=5)

BEGIN

PRINT @intFlag

SET @intFlag = @intFlag + 1

END

--  Example of WHILE Loop with BREAK keyword

DECLARE @COUNT INT

SET @COUNT = 1

WHILE (@COUNT <=10)

BEGIN

PRINT @COUNT

SET @COUNT = @COUNT + 1

IF @COUNT = 5

BREAK;

END

-- Example of WHILE Loop with CONTINUE and BREAK keywords

DECLARE @Flag INT

SET @Flag = 1

WHILE (@Flag <=5)

BEGIN

PRINT @Flag

SET @Flag = @Flag + 1

CONTINUE;

IF @Flag = 4  -- This will never executed

BREAK;

END

--

DECLARE @intFlag1 INT

SET @intFlag1 = 1

PRINT @intFlag1

WHILE (@intFlag1 <=5) BEGIN

SET @intFlag1 = @intFlag1 + 1

IF @intFlag1 = 4 -- This will never executed

CONTINUE;

ELSE

PRINT @intFlag1

END

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

declare @i int

declare @j int

print  @j;

set @i=1

set @J=2

while (@i<=5)

begin

print @i;

set @j=@i*@j

print @j;

set @i=@i+1

PRINT @i;

end

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

declare @I1 int

declare @J2 int

print 'THIS IS INT NUMBER'+ CAST(@I1 AS VARCHAR)

set @I1=1

set @J2=2

while (@I1<=5)

begin

print @I1;

set @J2=@I1*@J2

print @J2;

set @I1=@I1+1

PRINT @I1;

end

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

• What is Transaction in SQL Server? 

Transaction groups a set of T-Sql Statements into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.by programmers to group together read and write operations. In Simple Either FULL or NULL i.e either all the statements executes successfully or all the execution will be rolled back.

• Types of Transactions ?

There are TWO forms of Transactions.

1. Implicit – Specifies any Single Insert,Update or Delete statement as Transaction Unit.  No need to specify Explicitly.(No Transactions applied)
2. Explicit – A group of T-Sql statements with the beginning and ending marked with Begin Transaction,Commit and RollBack. .(Transactions applied)

PFB an Example for Explicit transactions.

BEGIN TRANSACTION

Update Employee Set Emp_ID = 54321 where Emp_ID = 12345

If(@@Error <>0)

ROLLBACK

Update LEave_Details Set Emp_ID = 54321 where Emp_ID = 12345

If(@@Error <>0)

ROLLBACK

COMMIT

In the above example we are trying to update an EMPLOYEE ID from 12345 to 54321 in both the master table “Employee” and Transaction table “Leave_Details”. In this case either BOTH the tables will be updated with new EMPID or NONE.

• What is the Difference between the functions COUNT and COUNT_BIG ?

Both Count and Count_Big functions are used to count the number of rows in a table and the only difference is what it returns.

1. Count returns INT datatype value where as Count_Big returns BIGINT datatype value.
2. Count is used if the rows in a table are less where as Count_Big will be used when the number of records are in millions or above.

Syntax -

1. Count – Select count(*) from tablename
2. Count_Big – Select Count_Big(*) from tablename

• How to read the last record from a table with Identity Column ?

We can get the same using couple of ways and PFB the same.

First - 

SELECT *
FROM    TABLE
WHERE  ID = IDENT_CURRENT(‘TABLE’)

Second - 

SELECT *
FROM    TABLE
WHERE   ID = (SELECT MAX(ID)  FROM TABLE)

Third - 

select top 1 * from TABLE_NAME  order by ID desc

NAMES	SALARY	ROW_NUMBER	RANK	DENSE_RANK
F	10000	1	1	1
E	6000	2	2	2
A	5000	3	3	3
B	5000	4	3	3
D	4000	5	5	4
C	3000	6	6	5

What is HEAP table ?

A table with NO CLUSTERED INDEXES is called as HEAP table. The data rows of a heap table are not stored in any particular order or linked to the adjacent pages in the table. This unorganized structure of the heap table usually increases the overhead of accessing a large heap table, when compared to accessing a large nonheap table (a table with clustered index). So, prefer not to go with HEAP  tables ..

 What is Covering Index ?

A covering index is a non-clustered index built upon all the columns required to satisfy a SQL query without going to the base table. If a query encounters an index and does not need to refer to the underlying data table at all, then the index can be considered a covering index.  For Example:

Select col1,col2 from table
where col3 = Value
group by col4
order by col5

Now if you create a clustered index for all the columns used in Select statement then the SQL doesn’t need to go to base tables as everything required are available in index pages.

What is Indexed View?

A database view in SQL Server is like a virtual table that represents the output of a SELECT statement. A view is created using the CREATE VIEW statement, and it can be queried exactly like a table. In general, a view doesn’t store any data—only the SELECT statement associated with it. Every time a view is queried, it further queries the underlying tables by executing its associated SELECT statement.

A database view can be materialized on the disk by creating a unique clustered index on the view. Such a view is referred to as an indexed view. After a unique clustered index is created on the view, the view’s result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing costly operations during query execution. After the view is materialized, multiple non-clustered indexes can be created on the indexed view.

What is Log Shipping ?                                                                                                                Log Shipping is a basic level SQL Server high-availability technology that is part of SQL Server. It is an automated backup/restore process that allows you to create another copy of your database for failover.                                                                                                                                   Log shipping involves copying a database backup and subsequent transaction log backups from the primary (source) server and restoring the database and transaction log backups on one or more secondary (Stand By / Destination) servers. The Target Database is in a standby or no-recovery mode on the secondary server(s) which allows subsequent transaction logs to be backed up on the primary and shipped (or copied) to the secondary servers and then applied (restored) there.

WORKING with Stored Proc with Insert /Update:

use Working
go
Create proc InsertValues
(
@Studentid int,
@Firstname nvarchar(50),
@Lastname nvarchar(50),
@Email nvarchar(50)
)
as
begin

if @studentid =0   -- does not allow duplicates values
begin
UPDATE tbl_Students
   SET Firstname = @Firstname,
       Lastname = @Lastname,
       Email = @Email
    Where Studentid=@Studentid

end
else
begin


insert into tbl_Students(Studentid,Firstname,lastname,Email) values (@Studentid,@Firstname,@lastname,@Email)
    set @Studentid = @@IDENTITY;
    end
    end
    go
  
  

>execute InsertValues 5,'XHema','Xlatha','XHemalatha@hotmail.com'

 >SELECT * FROM [Working].[dbo].[tbl_Students]
 >Select @@rowcount