SQL Interview question part 2

What is the difference between in and exist operator in sqlserver?
IN:  Returns true if a specified value matches any value in a sub-query or a list.

Exists:  Returns true if a sub-query contains any rows.

What is a function?  What are the different types of user defined functions?
Scalar function:  A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported.

Table-value function: User-defined table-valued functions return a table data type. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)

RETURNS TABLE

AS

RETURN

(

    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'

    FROM Production.Product AS P

      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID

      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID

    WHERE SH.CustomerID = @storeid

    GROUP BY P.ProductID, P.Name

);

GO

SELECT * FROM Sales.ufn_SalesByStore (602);

Built-in function:  Built-in functions are provided by SQL Server to help you perform a variety of operations. They cannot be modified. You can use built-in functions in Transact-SQL statements to:

·         Access information from SQL Server system tables without accessing the system tables directly.

·         Perform common tasks such as SUM, GETDATE, or IDENTITY.

What is a stored procedure and types of parameters in it?
Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result.

IN Parameter:

CREATE PROCEDURE uspGetAddress @City nvarchar(30)

AS

SELECT *

FROM AdventureWorks.Person.Address

WHERE City LIKE @City + '%'

GO

OUTPUT Parameter:

CREATE PROCEDURE uspGetAddressCount @City nvarchar(30), @AddressCount int OUTPUT

AS

SELECT @AddressCount = count(*)

FROM AdventureWorks.Person.Address

WHERE City = @City

How can you execute dynamic sql in sqlserver?
A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements.

CREATE PROCEDURE usp_GetSalesHistory

 (

         @WhereClause NVARCHAR(2000) = NULL

 )

 AS

 BEGIN

         DECLARE @SelectStatement NVARCHAR(2000)

         DECLARE @FullStatement NVARCHAR(4000)    

         SET @SelectStatement = 'SELECT TOP 5 * FROM SalesHistory '

         SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')    

         PRINT @FullStatement

         EXECUTE sp_executesql @FullStatement    

                /*

  --can also execute the same statement using EXECUTE()

         EXECUTE (@FullStatement)     

         */

 END

What is un-updatable views?
Aggregate functions or Group By clause in the View to be non-updatable.  SQL Server would not be able to determine which of the summarized rows should be updated.

What is trigger?  How can you invoke trigger on demand?
A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made using INSERT,UPDATE,OR DELETE statements.  Triggers are used to enforce data integrity and business rules such as automatically updating summary data.  A table can have up to 12 triggers defined on it.

Triggers can't be invoked on demand. They get triggered when the associated INSERT, DELETE or UPDATE is performed.