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')
   -- Convert function
   -- 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
   -- Date time function
   -- 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