Saturday, 11 June 2016

Question and Answers in Sql Server - Part 4


In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.


16 What are types of stored procedures present in sqlserver and how to create it ?

      Pre defined and user defined , Pre defined means already present in SQL SERVER. User defined means user creating the stored procedure.


Pre Defined Stored Procedures :

  
sp_rename             -- use to rename a object
sp_stored_procedures  -- use to find the stored procedure objects
sp_tables             -- use to find the tables
sp_depends            -- use to find the dependency of a object
sp_helptext           -- use to get the text of a compiled object
sp_addlinkedserver    -- use to add the linked server
sp_addlinkedsrvlogin  -- use to add the login for linked server
sp_who                -- use to find the object running in database and there id

   

User  Defined : 


To exec the SP : Sometimes we dont need to specify the all input parameter just alone execute the stored procedure because it takes default value.


    EXEC sys.sp_tables @table_name = N'', -- nvarchar(384)
        @table_owner = N'', -- nvarchar(384)
        @table_qualifier = NULL, -- sysname
        @table_type = '', -- varchar(100)
        @fUsePattern = NULL -- bit
   
    EXEC sys.sp_tables

    EXEC sys.sp_addlinkedserver @server = 'lnksrv', -- sysname
        @srvproduct = N'', -- nvarchar(128)
        @provider = N'SQLNCLI', -- nvarchar(128)
        @datasrc = N'sqlexpress', -- nvarchar(4000)
        @location = N'', -- nvarchar(4000)
        @provstr = N'', -- nvarchar(4000)
        @catalog = NULL -- sysname
   
    EXEC sys.sp_addlinkedsrvlogin @rmtsrvname = 'lnksrv', -- sysname
        @useself = '', -- varchar(8)
        @locallogin = NULL, -- sysname
        @rmtuser = 'raj', -- sysname
        @rmtpassword = 'pwd' -- sysname
   




17.  What are types of Function present in sqlserver ?
          There are two category first  System- defined and user defined. In that we have three sub categories based on the return type. 
  1. Scalar value function
  2. Table value function - Inline Table value function , Multi statement Table value function
  3. Aggregate function


         
System defined function:
let we see some of the predefined functions 


   -- check value constraint
   SELECT COALESCE(NULL,NULL,2)  -- return the first non null value from the expression if any empty value present at first returns 0
   SELECT ISNULL('raj','rajesh')
   SELECT NULLIF(0,2)
  
   -- Convert function
   SELECT CONVERT(VARCHAR(10),GETDATE(),126)
   SELECT CAST('1' AS INT)
  
   -- string functions
   SELECT REPLACE('ramesh','m','j')
   SELECT SUBSTRING('rajesh',1,3)
   SELECT LEN('rajesh')
   SELECT UPPER('raj')
   SELECT LOWER('jak')  
  
   --trim function
   SELECT LTRIM(' rajesh')
   SELECT RTRIM('rajesh ')
  -- Error function
  SELECT ERROR_LINE() 
  SELECT ERROR_MESSAGE()
  SELECT ERROR_NUMBER()
  SELECT ERROR_PROCEDURE()
  SELECT ERROR_SEVERITY()
  SELECT ERROR_STATE()
   -- Date time function
   SELECT GETDATE()
   SELECT DATEADD(dd,2,GETDATE())
   SELECT DATEDIFF(dd,GETDATE(),GETDATE()+5)
   SELECT DATENAME(mm,GETDATE())
   SELECT DATEPART(mm,GETDATE())
   SELECT DAY(GETDATE())
  
   -- Aggregate functions
   SELECT COUNT(1) FROM employee
   SELECT MAX(salary)FROM employee
   SELECT MIN(salary)FROM employee
   SELECT AVG(salary)FROM employee
   

User defined Function :

  


18. What is a Trigger ?







No comments:

Post a Comment