SQL SERVER Interview Questions

1. What are the different types of joins and what dies each do?
2. What are the four main query statements?
3. What is a sub-query? When would you use one?
4. What is a NOLOCK?
5. What are three SQL keywords used to change or set someone’s permissions?
6. What is the difference between HAVING clause and the WHERE clause?
7. What is referential integrity? What are the advantages of it?
8. What is database normalization?
9. Which command using Query Analyzer will give you the version of SQL server and operating system?
10. Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table?
11. What is the purpose of using COLLATE in a query?
12. What is a trigger?
13. What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “a query that ran yesterday took 30 seconds, but today it takes 6 minutes”
14. What is an execution plan? When would you use it? How would you view the execution plan?
15. What is the STUFF function and how does it differ from the REPLACE function?
16. What does it mean to have quoted_identifier on? What are the implications of having it off?
17. What are the different types of replication? How are they used?
18. What is the difference between a local and a global variable?
19. What is the difference between a Local temporary table and a Global temporary table? How is each one used?
20. What are cursors? Name four types of cursors and when each one would be applied?
21. What is the purpose of UPDATE STATISTICS?
22. How do you use DBCC statements to monitor various aspects of a SQL server installation?
23. How do you load large data to the SQL server database?
24. How do you check the performance of a query and how do you optimize it?
25. How do SQL server 2000 and XML linked? Can XML be used to access data?
26. What is SQL server agent?
27. What is referential integrity and how is it achieved?
28. What is indexing?
29. What is normalization and what are the different forms of normalizations?
30. Difference between server.transfer and server.execute method?
31. What id de-normalization and when do you do it?
32. What is better - 2nd Normal form or 3rd normal form? Why?
33. Can we rewrite subqueries into simple select statements or with joins? Example?
34. What is a function? Give some example?
35. What is a stored procedure?
36. Difference between Function and Procedure-in general?
37. Difference between Function and Stored Procedure?
38. Can a stored procedure call another stored procedure. If yes what level and can it be controlled?
39. Can a stored procedure call itself(recursive). If yes what level and can it be controlled.?
40. How do you find the number of rows in a table?
41. Difference between Cluster and Non-cluster index?
42. What is a table called, if it does not have neither Cluster nor Non-cluster Index?
43. Explain DBMS, RDBMS?
44. Explain basic SQL queries with SELECT from where Order By, Group By-Having?
45. Explain the basic concepts of SQL server architecture?
46. Explain couple pf features of SQL server
47. Scalability, Availability, Integration with internet, etc.)?
48. Explain fundamentals of Data ware housing & OLAP?
49. Explain the new features of SQL server 2000?
50. How do we upgrade from SQL Server 6.5 to 7.0 and 7.0 to 2000?
51. What is data integrity? Explain constraints?
52. Explain some DBCC commands?
53. Explain sp_configure commands, set commands?
54. Explain what are db_options used for?
55. What is the basic functions for master, msdb, tempdb databases?
56. What is a job?
57. What are tasks?
58. What are primary keys and foreign keys?
59. How would you Update the rows which are divisible by 10, given a set of numbers in column?
60. If a stored procedure is taking a table data type, how it looks?
61. How m-m relationships are implemented?
62. How do you know which index a table is using?
63. How will oyu test the stored procedure taking two parameters namely first name and last name returning full name?
64. How do you find the error, how can you know the number of rows effected by last SQL statement?
65. How can you get @@error and @@rowcount at the same time?
66. What are sub-queries? Give example? In which case sub-queries are not feasible?
67. What are the type of joins? When do we use Outer and Self joins?
68. Which virtual table does a trigger use?
69. How do you measure the performance of a stored procedure?
70. Questions regarding Raiseerror?
71. Questions on identity?
72. If there is failure during updation of certain rows, what will be the state?

SSRS INTERVIEW QUESTIONS part-2

What is the report rendering?

Report rendering is to call the report from server to application. Report rendering can be possible through different ways Like

Excel, PDF, CSV,XML, TIFF, HTML Web Archive,DOC

 What is the report server?

Report server is the server where we deploy the report In Other way it’s a holding place for reports. Applications access report server to view the report.

All the reports are reside with Report Server All other activities pertaining to SSRS is done at Report Server.It acts like work station for reporting tool.

Can we use the page total in report body?

The built in field [&pagenumber] and [&pagetotal] cannot be used in report body as these are applicable in report header or report footer.

These built in field can be added in footer or header for better representation of the report.

What are the different types of report?

Using BIDS reports can be created in two ways. I.e. two ways to retrieve the data from SQL Server data source.

1. TSQL Reports: TSQL Reports are made up of plain SQL Query. Data source in this case is SQL server database engine.
2. MDX Reports: MDX reports are created through cubes. For this data source is analysis services cubes.

What is report subscription? 

Report subscription is to schedule the resource on particular time and to send a mailer to particular users. A subscription is a standing request to deliver a report at a specific time or in response to an event, and then to have that report presented in a way that you define. Subscriptions provide an alternative to running a report on demand. On-demand reporting requires that you actively select the report each time you want to view the report. In contrast, subscriptions can be used to schedule and then automate the delivery of a report. 

What is the RDL file?

      A report definition contains data retrieval and layout information for a report. Report Definition Language (RDL) is an XML representation of this report definition.

What are the Matrix reports and what are their uses?

Matrix reports are the reports which used to generate data dynamically i.e. table structure is not static and it can be changed at run time.

Can you use a stored procedure to provide data to an SSRS report?
Yes, you can use a stored procedure to provide data to an SSRS report by configuring the dataset to use a stored procedure command type. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.

What is the main purpose of a report parameter?
The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select. The report parameter option allows you to show different dimensions in a single report by selecting the options.

What is the main purpose of a query parameter?
The main purpose of a query parameter is to filter data in the data source.

DataFlow Task Facts

Basics of Data Flow Task:

* Streaming
* Unlink control flow, multiple components can process data at the same time
* Smallest unit of the data flow is a component
* Data flows move data, but are also tasks in the control flow, as such, their success or failure effects how your control flow operates
* Data is moved and manipulated through transformations
* Data is passed between each component in the data flow
* Data flow is made up of source(s), transformations, and destinations.


Some Facts about Data Flow:

* The data flow task in SSIS (SQL Server Integration Services) sends data in series of buffers.
* This is bounded by DefaultBufferMaxRows and DefaultBufferMaxSize, two Data Flow properties.
* They have default values of 10,000 and 10,485,760 (10 MB), respectively. That means, one buffer will contain either 10,000 rows or 10 MB of data, whichever is less.
* You can adjust these two properties based on your scenario. Setting them to a higher value can boost performance, but only as long as all buffers fit in memory.
* The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved.
* Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data.
* A data flow consists of at least one data flow component, but it is typically a set of connected data flow components: sources that extract data; transformations that modify, route, or summarize data; and destinations that load data. Components are connected in the data flow by paths.
* Each path specifies the two components that are the start and the end of the path. For more information, see Data Flow Elements.
* At run time, the Data Flow task builds an execution plan from the data flow, and the data flow engine executes the plan.
* We can create a Data Flow task that has no data flow, but the task executes only if it includes at least one data flow.

SSIS Performence Tuning Tip-2

Set IsSorted Property as true: 
                                                     

•         If you know that the source data is sorted in such scenarios set the IsSorted =true on   the  source   adapter output.
•         Go to the advanced editor by right clicking on source adapter.
•         Go to input and output properties there you will be found IsSorted option .set it is true.
•         By setting this value it does not perform a sort operation ,it only indicates that the data is sorted..
•         By following this tip we can save some time in real time..if our data base is so large then it will helps a lot..
you can see SSIS performance tuning tip 1