Sql Server Interview Questions and Answers (1758) - Page 34

Column Store Index store columns in data pages as opposed to rows which was store in Row Store architecture. In ordinary index, rows are stored in disk pages but with column store index, columns are stored in separate set of the disk pages, and so it is faster. The query optimizer considers the column store index as a data source for accessing data just like it considers other indexes when creating a query
plan. It is optimized for the improved and fast warehouse queries processing. Since in this case there is no need to read all the columns of a table, hence it provides significant savings in disk I/O and more data can fit into memory.

In Column Store approach, data is analyzed by columns. So, the lower the data cardinality i.e. the more repeating values a column has, the higher its compression rate will be. It uses the Vertipaq compression engine technology (it is also the compression engine in Power Pivot) to store columns than traditional
indexes. In Row Store approach, all indexed data from each row is put together on a single page, and data in each column is spread across all pages in an index. In a column-store index, the data from each column is kept together so each data page contains data only from a single column.

DQS is a knowledge-driven data cleansing solution that ensures high quality data, improves accuracy, data consistency and resolve problems cause by bad data entry in BI or data warehouse or OLTP systems.

It helps business user or a non-database professional to create, maintain and execute their organization’s data quality operations with minimal setup or preparation time and with excellent quality.

It improves the data quality by creating a Knowledge Base (KB) about the data and then clean the data based on the knowledge in the knowledge base.

It has been introduce in SQL Server 2012

The DQS knowledge-driven solution uses two fundamental steps to cleanse data:

- Builds a Knowledge base through the knowledge management process

- Changes to be done (if needed for the Knowledge semantics to satisfy) in the source data based on the knowledge in the KB. These are done through a data quality project.

The DQS server is implemented as three SQL Server catalogs that we can manage and monitor in the SQL Server Management Studio. They are

1. DQS_MAIN
2. DQS_PROJECT
3. DQS_STAGING_DATA

DQS_MAIN includes DQS stored procedures, the DQS engine, and published knowledge bases.

DQS_PROJECT includes data that is required for knowledge base management and DQS project activities.

DQS_STAGING_DATA is the staging database where the source data is dumped for performing DQS operations and then export the processed data.

The source database that contains the data to be analyzed must also be in the same SQL Server instance as the DQS server.

It is a standalone application, designed for data stewards and DQS administrators that help to perform knowledge management, domain management, matching policy creation, data cleansing, matching, monitoring, data quality projects, and administration in one user interface.

The client application can install and run on the same computer as the DQS Server or remotely on a separate computer.

It is the central part of DQS client application. We can specify the rules that DQS will apply when validating data and the action taken when those rules are violated. As they capture the organizational knowledge, so they are term as knowledge base. Basically they are the rules define for the domains.

The KB contains Domains which is a component of Data Quality. For example a Country Domain can be such that it should not contain any abbreviated stuff and the length of the country must be greater than 3 letters. Now we can have this domain in our knowledge base say CountryKB. Typically, a single KB can have multiple domain values and for every domain we can have rules and validations apply for them. Domains can be either single valued or compound (consists of multiple fields).

It uses Knowledge Base (KB) for improving the quality of the source data by performing data cleansing and data matching activities and finally exports the resultant data to a SQL Server database or a .csv file

A Data quality project can be created either as a cleansing project or a matching project to perform respective activities and can perform the operation on the same KB.
Cleansing is that KB application that refines the source data in the KB.

Matching is that KB application that performs matching activity based on matching policy in a knowledge base to prevent data duplication by identifying exact and approximate matches, and thereby helps to remove duplicate data.

I. Helps us to perform data cleansing operation on the KB

II. Helps us to perform data matching operation on the source data by using the matching policy in a knowledge base.

III. Provides an interactive GUI for doing the above operations.

IV. Helps to export the resultant cleaned / matched data to the SQL Server database or to a .csv file.

V. Helpful for a data steward/non-data base user/IT professional

It covers usage and activities against Knowledge Bases, tells the status of the KB or Data Quality projects, the type of activities performed, the start and end time of the activities etc. There are also some filtering options by which we can filter the records.

I have post the interview QA for SQl

Answer :

Create Proc Sp_GenrateDMLinSQL

as

USE master

Create Database Temp_2012



--USE Temp_2012

Create Table Tbl_Test

(

Id int primary key identity(1,1),

Ename varchar(200)

)

USE Temp_2012

--insert Query

Insert into Tbl_Test values('Jehovah Jireh');

Insert into Tbl_Test values('Jehovah Ruffa');

Insert into Tbl_Test values('Jehovah Raffa');

Insert into Tbl_Test values('Jehovah Nissi');

Insert into Tbl_Test values('Jayakumar');

Insert into Tbl_Test values('Test');

USE Temp_2012

--Update Query

Update Tbl_Test set Ename='Jesus Never Fail' where Id=5;

USE Temp_2012

--Delete Query

Delete from Tbl_Test where Id=6;

select COUNT(e.empCity) as TotalNoofEmp,c.cityName from 

dbo.city c  left outer join dbo.Employees e

on e.empCity = c.cityID

group by c.cityID,c.cityName

order by COUNT(e.empCity) desc

Explanation:
----------------

The first point to be kept in mind is all cities should come, irrespective of employees.
So we should go for outer join. (Inner join is used only for matching records).
In our example the City Kokatta does not have any employees. As per left outer join concept, the all the rows from table which is in left hand side of keyword "left outer join " should come and the matching rows from right hand side of the keyword "left outer join " will come.

The second point is, we total number of employees for each city. To achieve this we must go for group by clause, so that we can get the group of employees with respect to city. The keyword count gives the total number of employees.

In this line I am using Left outer join between two tables based on EmpCity which is the common column between these two tables.

from dbo.city c  left outer join dbo.Employees e

on e.empCity = c.cityID

I have used alias name for City and Employees table to refer or get the columns of each table.

group by c.cityID,c.cityName

In above line I am grouping employees by citywise. Here the important point to be notes is I am grouping with cityId column of City table not with employee table. Because for non matching records of cityID of Employee's table will not have value.

In the following line I am soring by number of count using Orderby clause
order by COUNT(e.empCity) desc

Finally the select clause will return the result set.

select COUNT(e.empCity) as TotalNoofEmp,c.cityName 

The output will be

TotalNoofEmp	cityName

5	                New Delhi

4	                Chennai

3	                Mumbai

0	                Kolkatta

NOTE: This is objective type question, Please click question title for correct answer.

select * from tbl_StudentMarks where 

Stu_Marks = 

(select MIN(Stu_Marks) from (

select top 3 Stu_Marks from tbl_StudentMarks

order by Stu_Marks desc) AA)

At first we need to find the 3rd maximum value. So as per this table the thrid maximum value is 96. Two students have scored 96 Marks. So we need to print both rows.

I used two subqueries to obtain the result. In the below statement I am finding the first three top marks.

select top 3 Stu_Marks from tbl_StudentMarks

order by Stu_Marks desc

The above code pritns 98,97,96.
Now I am taking the minimum marks of the above three which is 96 by using the following query.

(select MIN(Stu_Marks) from (

select top 3 Stu_Marks from tbl_StudentMarks

order by Stu_Marks desc) AA

In the above code I used an alias name called "AA" for intermediate data operation purpose.
Now the minimum marks 96 will be checked with outermost query.

select * from tbl_StudentMarks where 

Stu_Marks = 96

At the end of the query we will be geting two rows that is of Vinod and Kellis who scored 96 Marks.

	  update tbl_EmployeeEdu set SGradeObt = case WHEN MarksGot >= 80  THEN 'Merit'

												  WHEN MarksGot >= 60 AND MarksGot < 80 THEN 'First Class' 

												  WHEN MarksGot >= 50 AND MarksGot < 60 THEN 'Second Class' 

												  WHEN MarksGot < 50 THEN 'Failure' END

In the above query I am using update statement with case when statements. Since I need to update all the column in table I didn't use where condition. Using Case When syntax of SQL Server I am assiging the conditions. When MarksGot >= 80 then I am setting the text "Merit" to sGradeObt column. Similarly for First class, Second class and failure conditions

The code is

  update b set b.StuMark = a.StuMark from tblSTUDENTMARKS a

  inner join tblSTUDENTMARKS b on a.SubID = b.SubID

  where  a.StuId = 1 and b.StuID = 2

Here I am using join based on SubjectID not with SlNo. Because SubjectID only same for both students. I am using alias name 'a' and 'b' for tblStudentMarks table.

If I replace the update statement of above query with Select statement,

  select * from tblSTUDENTMARKS a

  inner join tblSTUDENTMARKS b On a.SubID = b.SubID

  where a.StuId = 1 and b.StuID = 2

I will be getting the result as follows :

SlNo	StuID	SubID	StuMark	SlNo	StuID	SubID     StuMark

1	1	1	75	6	2	1	NULL

2	1	2	88	7	2	2	NULL

3	1	3	96	8	2	3	NULL

4	1	4	84	9	2	4	NULL

5	1	5	80	10	2	5	NULL

It clearly shows the table 'a' contains the details of stuent id 1 and table 'b' contains the details of student id 2.


Please note that I am checking table a.StuID = 1 and b.StuID = 2 in "where" condition. If we reverse the condition then marks of student-id 2 (here NULL) will be updated to marks of student-id 1 also.

So I am updating marks of the "b" table which of student id 2 with marks of "a" table which is of student id- 1.

NOTE: This is objective type question, Please click question title for correct answer.

There are three types of parameters can be passed to SQL Server Stored procedures. They are
Input parameter
Output parameter
InputOutput parameter.

CREATE PROC SPTYPEOFPARAMS 

(

	@PARAMINPUT VARCHAR(20) = 'Input Param', 

	@PARAMOUTPUT VARCHAR(50) OUTPUT,

	@PARAMINOUT VARCHAR(50) OUTPUT

)

AS

BEGIN

	SET @PARAMOUTPUT = @PARAMINPUT + ' Assigned to Output'

	SET @PARAMINOUT = @PARAMINOUT + ' Assigned to Input Output'

END

Note that there is no keyword for INPUTOUTPUT parameter. The default parameter is INPUT. Here we have three parameters namely @PARAMINPUT, @PARAMOUTPUT, @PARAMINOUT. @PARAMINPUT is used to pass the value to SP ,@PARAMOUTPUT is used to get the result value from SP and @PARAMINOUT is used to pass the value to SP and get the value from SP. If we execute the SP as follows :

Declare @OutVar1 as Varchar(50)

Declare @OutVar2 as Varchar(50)

SET @OutVar2 = 'Before SP'

Exec SPTYPEOFPARAMS 'Example',@OutVar1 OUTPUT,@OutVar2 OUTPUT

SELECT 'Output' =@OutVar1,'InputOutput' = @OutVar2

we get the following result :

Output	                                     InputOutput

Example Assigned to Output	Before SP Assigned to Input Output

1. Scalar Function:

User-defined scalar functions return a single data value of the type defined in the RETURNS clause. If RETURN type is Int, then function should return only Int value.
The return type should not be text, ntext, image, cursor, and timestamp.

2. Table-Valued Functions:


Table valued functions used to return the value in TABLE format.

There are two types: They are

(I) Inline table:

    CREATE FUNCTION FNC_INLINEGETEMPDET(@EMPID VARCHAR(10))

	    RETURNS TABLE

	    AS

	    RETURN

	    (

        	SELECT  EmpID, FirstName, LastName, Address

	        FROM    tbl_Employees

        	WHERE    EmpID = @EMPID 

    	    )

In the above code I am creating a function with return type 'Table' but I have not created any table explicitly, I am just using returning the output of the select query in table format using 'RETURN' statement. As per the above code if the employeeid is matched no rows will be returned


(II) Multistatement table :

  CREATE FUNCTION FNC_MULTIGETEMPDET(@EMPID VARCHAR(10))

	    RETURNS @tblEMPMaster TABLE

    	    (

    	        EMPID VARCHAR(10),

	        FirstName VARCHAR(50)	     	        

    	    )

	    AS

	    BEGIN	  	    

		  IF EXISTS(SELECT EmpID from tbl_EmployeePrim WHERE EmpId = @EMPID)

		  BEGIN

				INSERT INTO @tblEMPMaster

				SELECT EmpId, SFirstName

				FROM tbl_EmployeePrim

				WHERE EmpId = @EMPID

		  END

		  ELSE

		  BEGIN				

				INSERT INTO @tblEMPMaster				

				SELECT @EMPID, 'No Records found for the EmpID ' + ISNULL(@EMPID,'NULL')							

		  END

		  RETURN

	   END

In the above code I am creating a function with return type TABLE but I created table variable called @tblEMPMaster with two columns such as EMPID,FirstName.
In the body of the function I am checking if employeeid exists, if yes then it inserted the matching record to @tblEMPMaster else it will insert "No records found"


3. System Functions
SQL Server provides many system functions that can be used to perform a variety of operations. They cannot be modified.
Some example are :

COALESCE, ISNULL,CONVERT, ISNUMERIC 

etc.,

NOTE: This is objective type question, Please click question title for correct answer.

NOTE: This is objective type question, Please click question title for correct answer.