Biography Sql Server Interview Questions And Answers Pdf


Sunday, June 16, 2019

Download SQL Server interview questions and answers pdf. 3. What do you understand by a view? What does the WITH CHECK OPTION clause for a view do?. Top 50 Sql Server Dba Interview Questions And Answers you to crack your interview and to achieve your goal as an SQL Server Offers. 1. What are the two authentication modes in SQL Server?.

Sql Server Interview Questions And Answers Pdf

Language:English, Spanish, Japanese
Genre:Academic & Education
Published (Last):07.09.2016
ePub File Size:29.83 MB
PDF File Size:16.66 MB
Distribution:Free* [*Regsitration Required]
Uploaded by: GAYNELL

Question.1 Which TCP/IP port does SQL Server run on? How can it be changed? Answer: SQL Server runs on port It can be changed from the Network. SQL Server. Interview Questions and Answers. For All Database Developers and. Developers Administrators. Pinal Dave. Vinod Kumar. Check now updated SQL Server Interview Questions and Answers Get here Top 30 SQL Server Interview Question in PDF by hitting direct link.

There are two authentication modes — Windows Mode Mixed Mode Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page. This is mainly used to capture and save data about each event of a file or a table for analysis. What is recursive stored procedure?

SQL Server supports recursive stored procedure which calls by itself.

Related titles

Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels. What are the differences between local and global temporary tables? Local temporary tables are visible when there is a connection, and are deleted when the connection is closed. A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.

Can SQL servers linked to other servers? What is sub query and its properties? A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as A sub query should not have order by clause A sub query should be placed in the right hand side of the comparison operator of the main query A sub query should be enclosed in parenthesis because it needs to be executed first before the main query More than one sub query can be included 8.

What are the types of sub query? There are three types of sub query — Single row sub query which returns only one row Multiple row sub query which returns multiple rows Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed. What is SQL server agent?

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.

Top 50 SQL Server Interview Questions & Answers

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.

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.

SQL Server Interview Questions

This has to be done manually. To create a trace 2. SQL Server opens a work area in memory which is called Cursor. 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 the table contains the data which is not changed frequently then we can set the fill factor option to SQL Server will create new index page and transfer some rows from the previous index page to the new index page.

Reducing repetitive entries. The 'fill factor' option indicate how full SQL Server will create each index page.

We can specify the refresh of database either continuously or on periodic basis. The initial load will be same as in snapshot replication but later it allows change of data both on subscriber and publisher. String3 Example: Transactional Replication In transactional replication data will be same as in snapshot replication. If we want to reduce the number of page splits then we can use Fill factor option.

Increased speed and flexibility of queries. This function is used to replace all the occurrences of particular string by another string. In this replication data can be refreshed periodically and all data will be copied to another database every time the table is refreshed.

In Snapshot Replication snapshot of one database is transferred to another database. String2 String1. When the table's data is modified frequently. It is not possible to make permanent changes to server environment whereas stored procedure can change some of the server environment.

User defined functions do not return output parameters while stored procedure can return output parameters. In the case of failure only committed transaction will be recovered and uncommitted transaction will be rolled back. We want to know in which stored procedure s table EmpDetails is used. Isolation — This property says that one transaction can not retrive the data that has been modified by any other transaction until its completed. Consistency — This property says that the transaction should be always in consistent state.

But in the case of Stored procedure when an error occurs the execution will ignore the error and jumps to the next statement. The transaction follows all or nothing rule. Replace wouldn't work. The execution of User defined function will be stopped if any error occurred in it. If any of statement fails then the entire transaction fails.

Post navigation

Atomicity — Transactions may be set of SQL statements. It is not possible to change the table data with the use of User defined functions but you can do it by using stored procedure. Durability — When any transaction is committed then it must be persisted.

The msdb database contains data of database backups. Stored procedure helps in reducing network traffic and latency. The model is a template database which is used for creating a new user database. The Master database contains catalog and data for all databases of the SQL Server instance and it holds the engine together.

SQL Agent. So it helps to reduce the server overhead. List few advantages of Stored procedure.

SQL Server Interview Questions and Answers

Because SQL Server cannot start if the master database is not working. When the transaction is committed or rolled back then there is no need to use NOLOCK function because the data is already released by the committed transaction. SQL Server jobs. We can query on both servers using T-SQL. Stored procedures provide better security to your data. Test your sql server knowledge with our multiple choice questions! It also enhances application performance. The code of the stored procedure can be changed without affecting application.

The tempdb contains temporary objects like global and local temporary tables and stored procedures. When we want to query on remote database server along with the local database server then we can add the remote SQL server to local SQL server in a same group using the concept called Linked Server.

By using stored procedures we can reuse the code. DTS packages. Stored procedures help us in the encapsulation of the code. Level 6: Level 1: Lock escalation threshold is determined dynamically by SQL server. It is a storage technology that has one logical unit consisting of multiple disk drive components.

Level Due to this values in the unique key columns may or may not be NULL. Level 2: What is bit data type and what's the information that can be stored inside a bit column? It is used to store the boolean information of the form 1 true or 0 false. RAID S: Level 0: Primary can only be one in each table as it is one of the special cases of the unique key whereas a unique key can be many.

There are 12 Raid Levels which are as follows: Candidate Key is a key which provides the uniqueness of the column s. Level 7: It adds caching to Level 3 or 4. Lock escalation is the process of reducing the overhead of the system by converting many fine grain locks into fewer coarse grain locks.

Define candidate key. Multiple mirrors are created and then stripes over it. Bit data type is the smallest type used in a language. Primary key is a combination of columns which uniquely specify a row whereas a unique key is related to the superkey and can uniquely identify each row in the table. RAID stands for Redundant array of independent disks which was earlier called as Redundant array of inexpensive disks. Level 3: It identifies each row of a table as unique.

Level 4: It increases the performance by replicating and dividing the data through many levels between multiple physical drives. Level 5: This gives assurance of uniqueness of data when the columns are joined together.

Rohit Sharma Explain different forms of normalization? Normalization is a process of organizing the data to minimize the redundancy in the relational database management system RDBMS. These properties allow easy.

Composite Key is a special type of candidate key as it is formed by combining two or more columns. The use of normalization in database is to decompose the relations with anomalies to produce well structured and smaller relations. Unique keys also termed as alternate keys which prevent incorrect data from entering the table. Examples include the following: What is de-normalization and what are some of the examples of it? De-normalization is used to optimize the readability and performance of the database by adding redundant data.

Alternate Key is a type of candidate key which is formed when there are more than one candidate key and one of them is a primary key then other keys will act as an alternate keys. What is the difference between Locking and multi-versioning? Locking is a means of not allowing any other transaction to take place when one is already in progress. What are ACID properties? ACID is used in database and it includes the following properties such as atomicity.

Every tabular relationship will have atleast one candidate key. The lock has to be put up before the processing of the data whereas Multi-versioning is an alternate to locking to control the concurrency. It is used to manage the concurrency in the database table. Transfer of money from one bank account to another. De-normalization logical data design tend to improve the query responses by creating rules in the database which are called as constraints.

It covers the inefficiencies in the relational database software. It can become the primary key of the table as well. It allows two users to view and read the data till the transaction is in progress.

It provides easy way to view and modify the data. There are 6 forms of normalization which are as follows:. Linking attribute of one relation with other relations. To improve the performance and scalability of web applications Rohit Sharma It is used to copy huge amount of data from tables and views.

It does not copy the structures same as source to destination. Materialized views for implementation purpose such as: Flag for inappropriate content. Related titles. Jump to Page. Search inside document. Download SQL Server interview questions and answers pdf 3. Ram Yadav. Veeresh Kethari. Kishan Kumar Jha. Jigar Badgujar. Alejandro Valenzuela. Nag Dhall. Alok Kumar. Kasturava Das. More From Aneek Kumar.

Aneek Kumar. Alexandru Lucian. Kiran Kumar. Popular in Database Transaction. Narendra Sanisetty. Nguyen Ho Long. Surya Surendra Reddy Janga. Sureshkumar C. Danny Daniel. Anonymous kb1u6fsw. Ravi Birhman. Filma Fili. Abiye Asamenew. Naresh Vepuri. Aditya Mishra.Read answer What is Analysis service repository? The size of matrix data region depends on columns and rows fetched. For example. If Rowcount is checked after Error checking statement then it will have 0 as the value of Recordcount as it would have been reset.

Cursors are also costly because they require more resources and temporary storage results in more IO operations. This is mainly used to capture and save data about each event of a file or a table for analysis. What is difference between Index Seek vs. However, what I have found, to be rule number one, is get as little data in to your cursor as is needed.

Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem.