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

Latest and authentic Interview questions. You can also post an interview question and win monthly prizes as well as gain community credit points.

1758 records found.

Post |

Interview Experiences |

Interview FAQs |

Online Interviews |

Exclusive Questions

Get 650+ Questpond's Interview videos on discount

Which of the following Transact-SQL statements do you use to create an indexed view?

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

You are developing a database that will be used for tracking products. An existing view returns a list of all product and the name of the customer who purchased them. You want to modify this view to ensure that only the most recent purchased information is returned by the view. Which of the following Transact-SQL statements can you use to accomplish this goal ?

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

You are creating an important view that a web application will use to extract data from a SQL Server 2012 database. You want to ensure that the underlying table structure referenced by the view cannot be altered in a way that would affect the view definition without someone first dropping the view. Which of the following options would you use with the CREATE VIEW statement?

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

You are creating several views to be used with a confidential database. This database will be replicated to another site, but you want to ensure that several of the views that you are creating are not replicated. Which of the following options would you include in the CREATE VIEW statement to ensure that this goal is accomplished?

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

You are creating several views for a temp database. You need to ensure that data within the temp database is modified in authorized ways only. When creating the view, you specify the limits to the ways that data can be modified. You want to ensure that all statements that modify data executed against the view follow these criteria. Which of the following options would you use when creating this view?

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

A view that you have created for the temp database currently returns information on the temp model and the product name. You have been asked to modify the view so that it also returns the product's registration data. Which of the following Transact-SQL statements can you use to accomplish this goal?

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

how to detect the product level,product version and edition in sql server 2008

select serverproperty('product level')
select serverproperty('productversion')
select serverproperty('edition')
By using three queries you can get the product level,product version and edition

what is the max size of rows in a particular table

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

How to do custom sorting in SQL Server?

By using CASE statement in the ORDER BY clause we can achieve Custom Sorting on result set....
For example,
If you want to give the 1st preference to Mango, 2nd for banana, 3rd for Apple and so on

CREATE TABLE UserPreferences(FruitId int identity, FruitName varchar(40))

GO

INSERT INTO UserPreferences VALUES( 'Apple'), ('Cherry'), ('Mango'), ('Banana')

SELECT * FROM UserPreferences 

/*Sample Data:  

FruitId    FruitName

1           Apple

2           Cherry

3           Mango

4           Banana

*/

Answer should be: 

SELECT *

FROM UserPreferences 

ORDER BY CASE WHEN FruitName = 'Mango' THEN 1

                      WHEN FruitName = 'Banana' THEN 2

                      WHEN FruitName = 'Apple' THEN 3

                      ELSE 4

               END

/*RESULT in custome order:

FruitId	FruitName

3	Mango

4	Banana

1	Apple

2	Cherry*/

What are the Different ways of importing data into SQL Server?

Many approaches are available to import data into SQL Server. Main approaches are:

1) Import and Export Data Wizard  

     1.1. Import and Export Wizard tool which comes with SQL Server

     1.2. Right-click on Database name --> Tasks --> Import Data option --> follow wizard

2) Using T-SQL script:  

    2.1.   BULK INSERT

    2.2.  bcp command

3) Hands-on approach 

    Direct approach from Excel: Place data(records) in the excel and generate INSERT statements by using CONCATENATE() function in excel then run the script in SSMS

    Ex: You can use CONCATENATE for the function 

   =CONCATENATE("INSERT INTO table VALUES(",A1,",",B1,")")

4) Using SSIS tasks
NOTE: Mostly I will use 3rd approach for master data INSERT script generation

How to join two tables without having common column between them?

Answer:
Using ON clause with true condition
i.e.

SELECT * 

FROM Table1

JOIN Table2 ON 1=1

Here 1=1 means always true condition for each row in table

Why sometimes "NOT IN condition" will return NO results even though condition satisfied?

SELECT * 

FROM Employees

WHERE DEPARTMENT_ID NOT IN (10, 20, NULL)

Here Department_id is nullable column and I have employees belongs to other departments 30, 40 and so on... Query is expected to return 30, 40 department's employee details... But the above query doesn't return even single row.. WHY?

Reason1: NOT IN operation is equivalent to "AND operation of multiple conditions"........
Condition != Value1 AND Condition != Value1 AND Condition != Value3

Reason2: Any condition checking against a NULL becomes FALSE.
As a result of above two reasons query doesn't return any record