Fitness Sql Server Dba Interview Questions Pdf


Sunday, May 26, 2019

Are you looking for SQL Server Dba Interview Questions for Fresher's or experienced? You are at accurate place. There are loads of chances from various. sql server dba interview questions and answers for freshers and experienced,sql server dba interview questions pdf download,Microsoft sql server dba interview. If you're looking for SQL Server DBA Interview Questions for Experienced or Freshers, you are at right place. There are a lot of opportunities from many reputed.

Sql Server Dba Interview Questions Pdf

Language:English, Spanish, Portuguese
Genre:Politics & Laws
Published (Last):20.09.2016
ePub File Size:30.84 MB
PDF File Size:15.70 MB
Distribution:Free* [*Regsitration Required]
Uploaded by: LAVONNA

SQL Server DBA Interview Questions. 1. What are the steps used to improve the performance of a poor query? Maximum use of indexes, stored procedures. 19 SQL Server DBA Interview Questions and Answers Top 50 SQL Interview Questions and Answers Pdf SQL Server Interview Questions. junior-sql-server-dba-interview-questions - Download as PDF File .pdf), Text File .txt) or read online.

Adding a log file on a different disk. Completing or killing a long-running transaction. Ans: No! Consider a situation where publisher database log file has been increasing and there there is just few MB available on disk. As an experienced professional how do you react to this situation?

Check all database statistics are up-to-date at distributer. Below are two methods. To shrink the transaction log file: 1.

If you find no difference in size repeat the above steps 1 and 2 To truncate the transaction log file: In any case we are not able to provide the solution against the increasing logfile the final solution is disable the replication, truncate the log and reinitialize the subscribers.

Disable replication jobs 2. Connect to replication monitor and reinitialize all subscriptions by generating a new snapshot. Enable all replication related jobs. Can we add an article to the existing publication without generating a snapshot with all articles? Ans: Yes! We can do that. Follow the below steps to publish a new article to the existing publication.

Immediate Sync and 2. Both the fields were set to ON by default. Steps: 1. Generate a new snapshot which only includes newly added article. This option determines the computing and threads resources that are used for the query plan operators that perform the work in parallel.

How distributed transactions works in SQL Server? Ans: Distributed transactions are the transactions that worked across the databases, instances in the given session.

Snapshot isolation level does not support distributed transactions. The stored procedure on ServerC executes a distributed query against ServerD, and then all four computers are involved in the distributed transaction. The instance of the Database Engine on ServerA is the originating controlling instance for the transaction. Where do we use it? Temporary setting of specific server characteristics is done by trace tags Read answer Describe how to use Linked Server.

Read answer Explain how to make remote connection in database Latest answer: The following is the process to make a remote connection in database: This persistent connection is referred as Linked Server Read answer Explain how to send email from database Read answer What is the order in which the SQL query is executed? The following is the order of executing SQL query: The query goes to the shared pool that has information like parse tree and execution plan for the corresponding statement.

Read answer What is lock escalation? Lock escalation from SQL Server Read answer What is user defined datatypes and when you should go for them?

User defined datatypes is created by using base SQL Server data type by providing a descriptive name.. It is. Create a Store Procedure with Encryption Read answer What is bit datatype? Bit datatype is used to store boolean information. SQL as the query language It uses MS. Users that have no access to system Read answer Features and concepts of Analysis Services Latest answer: What is Compound Operators in sql server ? Explain with an example SQL Server introduces automatic auditing Using this.

Read answer What is Analysis service repository? Each server running analysis service has a repository to store objects of the computer running Analysis Services an Analysis service repository stores the information about the A linked server allows remote access Microsoft SQL server is a relational database management system Explain its benefits Read answer SQL Server interview questions for freshers and experienced SQL Server interview questions Explain inline variable assignment in sql server with an example Read answer What is SQL service broker?

Once message is sent to the SQL server Data mining Services here refer to OLAP SQL Server offers a high level of security.. Analysis services assists in creating Read answer Explain the phases a transaction has to undergo. The several phases a transaction has to go through are listed here The built in Administrator Account is basically used during some setup to join some machine in the domain Read answer What is blocking? Blocking happens when one connection from an application holds a lock and a second.

One machine provides the basic services and the second is available to run The events are saved Read answer Failover clustering overview Latest answer: Failover clustering is mainly used for data availability Every database has a public role which holds all the default permissions for the users in a database This used when login in through the windows login credentials is not existent Typically in a failover cluster.

SQL Server has efficient ways to enhance scalability of the database system It is Read answer Explain in brief how SQL server enhances scalability of the database system Read answer What is XPath? XPath is a language defined by the W3C Read answer What is Log Shipping? UNION command selects distinct and related information from two tables Read answer Explain GO Command.

A file or file group can only be used by one database Data definition language is used to define and manage all attributes and properties of a database On the other hand SQL Server supports two security authentication modes On the other hand. Null means no entry has been made..

SQL DBA Interview Questions & Answers

UNION command selects distinct and related information from two tables. It implies that the value is either unknown or undefined GO Command is used to signal the end of a batch For example Read answer What is the difference between a Local and a Global temporary table? Read answer Explain Data Definition Language You can also add a gauge to the design surface to show a single relative value.

Adding sub-report. It has minimum of one row group and one column group.. Matrix — A matrix data region display data in pivot table format. Display your data in graphic format using Chart Region The calculation is performed on dataset by report server. July 7. A various chart types are available in SSRS namely line. MIN 7. The list can be used to display multiple table and matrix.. Matrix 3. In SSRS Sub-report is a stand-alone report which can be link to another report based on its content using parameter.

The following are various aggregate functions available: You can perform grouping. STUFF function is used to insert a string into another string by deleting some characters specified. There are various ways in which you can enhance your report: List 4. Using expression. It is the timing of the operation that differs from the alias columns.

Using custom code. A list data region is free layout.. This can be used in a table or matrix to show the relative value of a field in a range of values in the data region. COUNT 4. If couple of reports are related. AVG 3. May 7. Table 2. STDEV 8.

Post navigation

It is useful for displaying data grouped by row. Add custom fields. A local temporary table lives until the connection is valid or until the duration of a compound statement. Use sorting. Table Data region has fixed tabular structure i.

Sql Server interview.

Data regions are report items used to display data from a single dataset. You can have maximum of 1 report item per cell. Read answer Sql Server interview. MAX 6. What are various ways to enhance the SSRS report? SUM 2. The size of table depends on number of rows dataset fetches i.. It is useful for complex reporting resign.. Custom fields provide with same functionality as alias columns provide in SQL server query.

Chart — This data region is for displays the data graphically i.. Add document map navigational links to report item once report is rendered to report Each getting data from different dataset. The size of matrix data region depends on columns and rows fetched.

How do you integrate the SSRS reports in your application? There are 3 ways in which you can integrate reports into your application: VAR Browser control or Report Viewer Control — In this approach. No separate window opens. Context S. A separate login might be required since we are directly calling the report from report server. Address of report server gets expose to user.

July A user does not come to know that he has moved to different server. It is written in Visual basic and is used throughout the report to to retrieve. Navigating to URL i. Using IFrame. SUM is the aggregate function used for numeric data type. The differences are as follows: Expressions provide us with flexibility to customize our report.

Explain use of Expression builder. Month Today. Programmatically sending a web request using SOAP to report server. VARP By default. TotalPages placed in content. Both the drill down and drill through report provide interactive functionality to the SSRS report.

DateSerial Year Today. I want the dataset to display Customer Name etc. While the second method has advantage of being available for multiple reports but it has much of configuration overhead. Security of Data Manipulate data.

The advantage of first method is that it is simple and easy to use but disadvantage is that it is available for that report only.

The custom field is very useful for the data manipulation like adding some fields whose value can be calculated based on expression.

Custom fields can be defined as alias column of the report since the operation is performed on report server rather than on database server. The New field dialog box will open. Net and call it using property expression or you can write a custom class library and refer it in report server. We can add custom fields as right click on dataset. Why do we need different type of parameter? You can write the code directly into embedded VB.

To add custom class library. To write custom code. Note that you need to create class library and then compile it before referencing it in your SSRS report. SSRS allows developer to add custom code in your report.

If it is calculated. Can we use custom code in SSRS?

If so. System-level — Administer the report server globally 2. Item-level roles — User can use any of predefined item-level roles or create their own roles by using combination of predefined item-level roles. These commands are used to check the consistency of the database like validation tasks. For example — 1. Site User. Item-level — Security at report and dataset level System-level roles are: Browser — can navigate to report and run them.

Pre-defined Item-level roles are: If in the code behind the keyword is directly used into the SQL statement. SQL Server interview questions and answers. Full set of records is retrieved then filtered. When a user logs into reporting services. SQL Injection is an attack in which attacker take the advantage of insecure application over internet by running the SQL command against the database and to steal information from it that too using GUI of the website.

Report Manager manages the security at 2 levels — 1. The attacker tries to inject their own SQL into the statement that the application will use to query the database. System Administrator — can manage report server and report manager security 2. Reporting services maintain role based security.

Publisher — As name suggest. Content Manager — has all permission at item-level. This attack can happen with the applications in which SQL queries are generated in the code. The performance of the view depends on how good the selected statement the view has.

Whereas in case of non-clustered index the leaf level is a pointer to the data. Non-clustered index is faster than clustered index because when we use DML statement on clustered index. The Column List Optional. Materialized view is similar to regular views but the output of select query has been saved to a table.

If the select statement has too many joins then it the view will perform poorly. A table can have only one Clustered Index at a time which is generally created on primary key and can have more than one non clustered indexes maximum up to The leaf level of clustered index is actual data pages of the table.

SQL Server DBA Interview Q&A Part 2: The Answers

View shows the latest data all the time while the materialized view only shows the fresh data after its result table is updated either by setting a schedule or based on the change in the underlying tables. Consider the following SQL statement. EmpName This way the query can be made more readable and easy to understand.

While in the case of materialized view. Give a related name for the job. The Index Scan is preferred only when the table is small. Here the rows with equal salaries will not get same ranks. Index seek is preferred for highly sensitive queries. Click on the "Schedules" in the left menu. A New Job window will appear. You can specify recurring schedules also. For this function. A SQL job can contain one or more schedules. For using this function first specify the function name.

A schedule is basically the time at which sql job will run itself. Then specify the OVER function. So the cost of proportional is the number of rows of that table. Right click on jobs and choose Add New. Index Seek only touches the rows which qualify and the pages that contain that qualifying rows.

The clause specifies the column s that you are going to rank. Table Scan scans every record of the table. Click next on the "Steps" in the left menu. There you will find a Jobs folder.

Index Seek and Index Scan are operation for query tuning in execution plans. The drawback of sparse column is that it requires more space for the non null values. When we define a column as sparse it requires additional 4 Byte for not null values. Here the rows with equal salaries will get same ranks. When a column there is big number of null then by defining that column as spars column we can save a large amount of disk space.

Name varchar 'Name'. For example. Sparse column is a type of column which is used to optimize storage for null values. Explicit Cursor When the programmer wants to perform the row by row operations for the result set containing more than one row.

To start. That means it will not redirect your application to other server. Backing up. Similarity -These both command will only delete data of the specified table. To Back up the transaction log of the primary database 2. This has to be done manually. To create a trace 2. SQL Server opens a work area in memory which is called Cursor.

Top 50 Sql Server Dba Interview Questions And Answers Pdf

To watch the trace results when the trace runs 4. To replay the trace results 5. These cursors are called implicit cursors.

To Copy the transaction log backup to every secondary server 3. They are managed by OPEN. The main functions of Log Shipping are as follows: To store the trace results in a table. Restoring data.

Synchronize the database by copying Transaction logs. Reducing required storage space 4. Length of replacement string String2. Using Fill factor SQL will reserve some space on each index page. This function is used to replace the part of string with some another string.

Snapshot Replication.

Merge Replication Merge replication replicate data from multiple sources into a single central database. String to overwrite Example: This process is called page split. Starting Position for overwriting Length. String to be overwritten Position. No need to restructure existing tables for new data. This set of rules is called Normalization. The fill factor is a value from 1 through that indicates the percentage of the index page to be left empty.

The default value for fill factor is 0.If you don't have an instance of Reporting Services installed then the icon will be disabled. Unique keys also termed as alternate keys which prevent incorrect data from entering the table. What are the basic functions for master.. What are scheduled tasks in SQL Server? SVR Technologies give SQL Server training online at your registered time, you can choose classes where, when, and how it accommodates you the best.

STDEV 8. Click next on the "Steps" in the left menu. Values are atomic. Test1 is dropped.

ASHLYN from Roanoke
I fancy exploring ePub and PDF books upliftingly. Browse my other posts. I have always been a very creative person and find it relaxing to indulge in rugby sevens.