Interview Questions → SQL SERVER → SQL Server Questionnaire IV

SQL Server Questionnaire IV

What is SQL server agent?

SQL Server Agent is a windows service that executes scheduled administrative tasks.

What is SQL Profiler?

SQL Profiler is a tool from which we can monitor the events in an instance of Microsoft SQL Server

What is a scalar valued function in SQL Server?

A function which accepts zero or more input parameters and returns a single data value as output is called scalar valued function.

See the code sample below

CREATE FUNCTION AddTwoNumbers
(
	@a int,
	@b int
)
RETURNS int
AS
BEGIN
	RETURN @a + @b
END

This function is accepting 2 parameters and return single data value i.e. int. So this function is scalar valued function.

What is inline table valued functions in SQL Server?

inline table valued functions have following characteristics

      1. inline table valued functions returns a table

      2. There is no function body in inline table valued function

      3. There is a single select statement in inline table valued functions

Consider the following sample

CREATE FUNCTION GetEmployeeFromDepartment
( @department_id int )
RETURNS table
AS
RETURN (
SELECT EmployeeId, EmployeeName
FROM Employee
WHERE DepartmentId = @department_id
)
GO

This method is inline table valued function.

Can we use Truncate command on a table which is referenced by foreign key?

No.

Consider the following table customer and order(both tables are having foreign key relationship)

customer_id	customer_name
1	Customer 1
2	Customer 2

order_id	order_details	customer_id
1	Order details1	1
2	Order details2	2

When you try to execute truncate command on customer table you will get following error

Msg 4712, Level 16, State 1, Line 1

Cannot truncate table 'Customer' because it is being referenced by a FOREIGN KEY constraint.

How to create the database back using script?

Use the following command

BACKUP DATABASE [DB_NAME] TO DISK = [PATH]

How to change the schema table of the table?

First of all create a new schema (if not exists)

CREATE SCHEMA [MySchema] AUTHORIZATION [dbo]

Then change the schema using the following command

ALTER SCHEMA [MySchema] TRANSFER dbo.Employee

where dbo is the existing schema and Employee is the table of which you want to change schema

Write a query to get the list of index which tables are using?

SELECT OBJECT_SCHEMA_NAME(T.[object_id],DB_ID()) AS [Schema],  
  T.[name] AS [table_name], I.[name] AS [index_name], AC.[name] AS [column_name],  
  I.[type_desc], I.[is_unique], I.[data_space_id], I.[ignore_dup_key], I.[is_primary_key], 
  I.[is_unique_constraint], I.[fill_factor],    I.[is_padded], I.[is_disabled], I.[is_hypothetical], 
  I.[allow_row_locks], I.[allow_page_locks], IC.[is_descending_key], IC.[is_included_column] 
FROM sys.[tables] AS T  
  INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]  
  INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] 
  INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id] 
WHERE T.[is_ms_shipped] = 0 AND I.[type_desc] <> 'HEAP' 
ORDER BY T.[name], I.[index_id], IC.[key_ordinal]

How to execute update statement from another table

UPDATE A 
SET A.Field1 = B.Field1 
FROM Table1 A, Table2 B 
WHERE A.Code = B.Code

What is the difference between count(*) and count(column_name) in SQL server?

count(column_name) doesn't consider null value in column. For example if a table Class contains 10 values(1 null and 9 not null) then count(*) will return 10 whereas count(column_name) will return 9.

Posted By -

Karan Gupta

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**