SQL Server Interview Day - 5

November 29, 2017 0 Comments A+ a-

1) How can you describe table structure in sql server ?

sp_help tablename

The above query is used for the check table structure in SQL Server.
Ex:-
sp_help Tbl_Emp

2) How can you see text of stored procedure in sql server ?

sp_helptext storedprocedurename

The above query is used for see the text of stored procedure in sql server.
Ex:-
sp_helptext sp_EmpDetails

3) How can you encrypt the text of stored procedure in sql server ?

WITH ENCRYPTION is used for encrypt the text of stored procedure in sql server.
Please follow the below stored procedure.
Example :-
Create procedure sp_Emp
@id int
with encryption
as
begin
select * from tblEmp
where ID =@id
end

4) Is It possible to view the text of encrypted stored procedure in sql server ?

No, It is not possible to view the text of encrypted stored procedure in sql server.
sp_helptext is not able view the text of encrypted stored procedure.

5) Write a dynamic SQL In a Stored Procedure ?

EXECUTE() is used for executing dynamic query in sql server.
Please follow the below stored procedure.
Example :-
Create procedure sp_Emp
@id varchar(20)
as
begin
declare @Query as nvarchar(max)
set @Query = 'select * from tblEmp where ID ='+@id
execute(@Query)
end

6) How many types of trigger in SQL Server?

There are mainly two type.

  • After
  • Instead of

7)What is the difference between Truncate and Delete?

Truncate:


  • Truncate command is used to delete all the rows from the table.
  • It will free the space containing the table.
  • We can't able to find description of table.

Delete:


  • Delete command is used to delete rows from a table.
  • It can't free the space containing the table.
  • We can able to find description of table.

8)What is a table scan?

If you are not using index on table then search query will search on every data.
This type of searching called Table scan.

9)What are the disadvantages of an Index?

please find the below disadvantages

  • Increased Disk Space
  • DML statements could be slow

10)What are the System Database in Sql server?

There 4 database are created automatically.

  • Master
  • Model
  • Msdb
  • Tempdb