What is Stored Procedure?
A stored procedure is a set of SQL queries which can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
List the advantages of using Stored Procedures?
Advantages of using Stored procedures are:
Stored procedure boosts application performance.
Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
Stored procedures can be reused.
Stored procedures can encapsulate logic. You can change the stored procedure code without affecting clients.
Stored procedures provide better security for your data.
What is identity in SQL?
An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.
Why Would You Use Sql Agent?
SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.
What Happens On Checkpoint?
Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.
One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.
What Is Dbcc?
DBCC statements are Database Console Commands and come in four flavors
Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don’t fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.
How Can You Control The Amount Of Free Space In Your Index Pages?
You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.
What Types Of Replication Are Supported In Sql Server?
SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.
Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.
Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.
What Are The Different Sql Server Versions You Have Worked On?
The would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.
What Are The Different Types Of Indexes Available In Sql Server?
The simplest to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
What Is The Difference Between Clustered And Non-clustered Index?
In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
Why Would You Call Update Statistics?
Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently. Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not negate all performance gains you might have achieved by calling update statistics. In fact, it could have a negative impact on performance depending on the characteristics of the system.
What Is A Correlated Sub-query?
A correlated sub-query is a nested query that is linked to the outer query. For instance, say I wanted to find all the employees who have not entered their time for the week. I could query the Employee table to get their first and last name, but I need to look at the TimeEntry table to see if they’ve entered their time or not. I can’t do a straight join here because I’m looking for the absence of time data, so I’ll do a correlated sub-query similar to this
SELECT FirstName, LastName
FROM EMPLOYEE e
WHERE NOT EXISTS (SELECT 1 FROM TimeEntry te
WHERE te.EmpID = e.EmpID
AND te.WeekID = 35)
Notice that the inner query relates to the outer query on the employee ID, thus making it a correlated sub-query. The inner query will be evaluated once per outer query row.
What Authentication Modes Does Sql Server Support?
SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note that if you use Windows Authentication, you will not be able to log in as sa.
Explain About Your Sql Server Dba Experience?
This is a generic often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the that lets the interviewer know how suitable are you for the position to which you are being interviewed.
What Purpose Does The Model Database Server?
The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from model every time SQL Server starts up.
How Do You Trace The Traffic Hitting A Sql Server?
SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.
What Are The New Features In Sql Server 2005 When Compared To Sql Server 2000?
There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here
Database Partitioning
Dynamic Management Views
System Catalog Views
Resource Database
Database Snapshots
SQL Server Integration Services
Support for Analysis Services on a a Failover Cluster.
Profiler being able to trace the MDX queries of the Analysis Server.
Peer-toPeer Replication
Database Mirroring
What Are The High-availability Solutions In Sql Server And Differentiate Them Briefly?
Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features.
How Do You Troubleshoot Errors In A Sql Server Agent Job?
Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.
What Is The Default Port No On Which Sql Server Listens?
1433
What Is Transparent Data Encryption?
Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.
Does Transparent Data Encryption Provide Encryption When Transmitting Data Across Network?
No, Transparent Data Encryption (TDE) does not encrypt the data during transfer over a communication channel.
What Are The Operating Modes In Which Database Mirroring Runs?
Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
What Is The Difference Between The 2 Operating Modes Of Database Mirroring?
High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored database can be lagging behind (in terms being up to date with Principal database) if there is a heavy load on the Mirrored Server.
What Is Fill Factor?
Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.
A stored procedure is a set of SQL queries which can take input and send back output. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
List the advantages of using Stored Procedures?
Advantages of using Stored procedures are:
Stored procedure boosts application performance.
Stored procedure execution plans can be reused as they cached in SQL Server’s memory which reduces server overhead.
Stored procedures can be reused.
Stored procedures can encapsulate logic. You can change the stored procedure code without affecting clients.
Stored procedures provide better security for your data.
What is identity in SQL?
An identity column in the SQL automatically generates numeric values. We can be defined as a start and increment value of the identity column. Identity columns do not need to be indexed.
Why Would You Use Sql Agent?
SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs. Jobs can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.
What Happens On Checkpoint?
Checkpoints, whether scheduled or manually executed, cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). That is, the dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain for SQL Server. However, you do not want the transaction log to get too big because it might consume too many resources and, should your database fail, take too long to process to recover the database.
One important thing to note here is that SQL Server can only truncate up to the oldest open transaction. Therefore, if you are not seeing the expected relief from a checkpoint, it could very well be that someone forgot to commit or rollback their transaction. It is very important to finalize all transactions as soon as possible.
 |
| Infosys Frequently Asked SQL Server Interview Questions Answers |
What Is Dbcc?
DBCC statements are Database Console Commands and come in four flavors
Maintenance, Informational, Validation, and Miscellaneous. Maintenance commands are those commands that allow the DBA to perform maintenance activities on the database such as shrinking a file. Informational commands provide feedback regarding the database such as providing information about the procedure cache. Validation commands include commands that validate the database such as the ever-popular CHECKDB. Finally, miscellaneous commands are those that obviously don’t fit in the other three categories. This includes statements like DBCC HELP, which provides the syntax for a given DBCC command.
How Can You Control The Amount Of Free Space In Your Index Pages?
You can set the fill factor on your indexes. This tells SQL Server how much free space to leave in the index pages when re-indexing. The performance benefit here is fewer page splits (where SQL Server has to copy rows from one index page to another to make room for an inserted row) because there is room for growth built in to the index.
What Types Of Replication Are Supported In Sql Server?
SQL Server has three types of replication: Snapshot, Merge, and Transaction. Snapshot replication creates a snapshot of the data (point-in-time picture of the data) to deliver to the subscribers. This is a good type to use when the data changes infrequently, there is a small amount of data to replicate, or large changes occur over a small period of time.
Merge replication uses a snapshot to seed the replication. Changes on both sides of the publication are tracked so the subscriber can synchronize with the publisher when connected. A typical use for this type of replication is in a client and server scenario. A server would act as a central repository and multiple clients would independently update their copies of the data until connected. At which time, they would all send up their modifications to the central store.
Transaction replication also begins with a snapshot only this time changes are tracked as transactions (as the name implies). Changes are replicated from publisher to subscriber the same as they occurred on the publisher, in the same order as they occurred, and in near real time. This type of replication is useful when the subscriber needs to know every change that occurred to the data (not point-in-time), when the change volume is high, and when the subscriber needs near real-time access to the changes.
What Are The Different Sql Server Versions You Have Worked On?
The would be depending on the versions you have worked on, I would say I have experience working in SQL Server 7, SQL Server 2000, 2005 and 2008. If you have worked only the some version be honest in saying that, remember, no one would be working on all versions, it varies from individual to individual.
What Are The Different Types Of Indexes Available In Sql Server?
The simplest to this is “Clustered and Non-Clustered Indexes”. There are other types of Indexes what can be mentioned such as Unique, XML, Spatial and Filtered Indexes. More on these Indexes later.
What Is The Difference Between Clustered And Non-clustered Index?
In a clustered index, the leaf level pages are the actual data pages of the table. When a clustered index is created on a table, the data pages are arranged accordingly based on the clustered index key. There can only be one Clustered index on a table.
In a Non-Clustered index, the leaf level pages does not contain data pages instead it contains pointers to the data pages. There can multiple non-clustered indexes on a single table.
Why Would You Call Update Statistics?
Update Statistics is used to force a recalculation of query optimization statistics for a table or indexed view. Query optimization statistics are automatically recomputed, but in some cases, a query may benefit from updating those statistics more frequently. Beware though that re-computing the query statistics causes queries to be recompiled. This may or may not negate all performance gains you might have achieved by calling update statistics. In fact, it could have a negative impact on performance depending on the characteristics of the system.
What Is A Correlated Sub-query?
A correlated sub-query is a nested query that is linked to the outer query. For instance, say I wanted to find all the employees who have not entered their time for the week. I could query the Employee table to get their first and last name, but I need to look at the TimeEntry table to see if they’ve entered their time or not. I can’t do a straight join here because I’m looking for the absence of time data, so I’ll do a correlated sub-query similar to this
SELECT FirstName, LastName
FROM EMPLOYEE e
WHERE NOT EXISTS (SELECT 1 FROM TimeEntry te
WHERE te.EmpID = e.EmpID
AND te.WeekID = 35)
Notice that the inner query relates to the outer query on the employee ID, thus making it a correlated sub-query. The inner query will be evaluated once per outer query row.
What Authentication Modes Does Sql Server Support?
SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It’s important to note that if you use Windows Authentication, you will not be able to log in as sa.
Explain About Your Sql Server Dba Experience?
This is a generic often asked by many interviewers. Explain what are the different SQL Server Versions you have worked on, what kind of administration of those instances has been done by you. Your role and responsibilities carried out in your earlier projects that would be of significance to the potential employer. This is the that lets the interviewer know how suitable are you for the position to which you are being interviewed.
What Purpose Does The Model Database Server?
The model database, as its name implies, serves as the model (or template) for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from model every time SQL Server starts up.
How Do You Trace The Traffic Hitting A Sql Server?
SQL profiler is the SQL Server utility you can use to trace the traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reducing the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.
What Are The New Features In Sql Server 2005 When Compared To Sql Server 2000?
There are quite a lot of changes and enhancements in SQL Server 2005. Few of them are listed here
Database Partitioning
Dynamic Management Views
System Catalog Views
Resource Database
Database Snapshots
SQL Server Integration Services
Support for Analysis Services on a a Failover Cluster.
Profiler being able to trace the MDX queries of the Analysis Server.
Peer-toPeer Replication
Database Mirroring
What Are The High-availability Solutions In Sql Server And Differentiate Them Briefly?
Failover Clustering, Database Mirroring, Log Shipping and Replication are the High-Availability features available in SQL Server. I would recommend reading this blog of mine which explains the differences between these 4 features.
How Do You Troubleshoot Errors In A Sql Server Agent Job?
Inside SSMS, in Object explorer under SQL Server Agent look for Job Activity Monitor. The job activity monitor displays the current status of all the jobs on the instance. Choose the particular job which failed, right click and choose view history from the drop down menu. The execution history of the job is displayed and you may choose the execution time (if the job failed multiple times during the same day). There would information such as the time it took to execute that Job and details about the error occurred.
What Is The Default Port No On Which Sql Server Listens?
1433
What Is Transparent Data Encryption?
Introduced in SQL Server 2008 Transparent Data Encryption (TDE) is a mechanism through which you can protect the SQL Server Database files from unauthorized access through encryption. Also, TDE can protect the database backups of the instance on which TDE was setup.
Does Transparent Data Encryption Provide Encryption When Transmitting Data Across Network?
No, Transparent Data Encryption (TDE) does not encrypt the data during transfer over a communication channel.
What Are The Operating Modes In Which Database Mirroring Runs?
Database Mirroring runs in 2 operating modes High-Safety Mode and High-Performance Mode.
What Is The Difference Between The 2 Operating Modes Of Database Mirroring?
High-Safety Mode is to ensure that the Principal and Mirrored database are synchronized state, that is the transactions are committed at the same time on both servers to ensure consistency, but there is/might be a time lag.
High-Performance Mode is to ensure that the Principal database run faster, by not waiting for the Mirrored database to commit the transactions. There is a slight chance of data loss and also the Mirrored database can be lagging behind (in terms being up to date with Principal database) if there is a heavy load on the Mirrored Server.
What Is Fill Factor?
Fill Factor is a setting that is applicable to Indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created / rebuilt.
What Is The Default Fill Factor Value?
By default the fill factor value is set to 0.
Where Do You Find The Default Index Fill Factor And How To Change It?
The easiest way to find and change the default fill factor value is from Management Studio, right-click the SQL Server and choose properties. In the Server Properties, choose Database Settings, you should see the default fill factor value in the top section. You can change to a desired value there and click OK to save the changes. The other option of viewing and changing this value is using
What Is A System Database And What Is A User Database?
System databases are the default databases that are installed when the SQL Server is installed. Basically there are 4 system databases: Master, MSDB, TempDB and Model. It is highly recommended that these databases are not modified or altered for smooth functioning of the SQL System.
A user database is a database that we create to store data and start working with the data.
How Many Files Can A Database Contain In Sql Server?how Many Types Of Data Files Exists In Sql Server? How Many Of Those Files Can Exist For A Single Database?
A Database can contain a maximum of 32,767 files.
There are Primarily 2 types of data files Primary data file and Secondary data file(s)
There can be only one Primary data file and multiple secondary data files as long as thetotal # of files is less than 32,767 files
What Is Dcl?
DCL stands for Data Control Language.
What Are The Commands Used In Dcl?
GRANT, DENY and REVOKE.
What Are The Different Authentication Modes In Sql Server And How Can You Change Authentication Mode?
SQL Server has 2 Authentication modes: Windows Authentication and SQL Server and Windows Authentication mode also referred as Mixed Mode.
What Are The Differences In Clustering In Sql Server 2005 And 2008 Or 2008 R2?
On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on.
What Is Meant By Active – Passive And Active – Active Clustering Setup?
An Active – Passive cluster is a failover cluster configured in a way that only one cluster node is active at any given time. The other node, called as Passive node is always online but in an idle condition, waiting for a failure of the Active Node, upon which the Passive Node takes over the SQL Server Services and this becomes the Active Node, the previous Active Node now being a Passive Node.
An Active –Active cluster is a failover cluster configured in a way that both the cluster nodes are active at any given point of time. That is, one Instance of SQL Server is running on each of the nodes always; when one of the nodes has a failure, both the Instances run on the only one node until the failed node is brought up (after fixing the issue that caused the node failure). The instance is then failed over back to its designated node.
List Out Some Of The Requirements To Setup A Sql Server Failover Cluster.?
Virtual network name for the SQL Server, Virtual IP address for SQL Server, IP addresses for the Public Network and Private Network(also referred as Hearbeat) for each node in the failover cluster, shared drives for SQL Server Data and Log files, Quorum Disk and MSDTC Disk.
On A Windows Server 2003 Active – Passive Failover Cluster, How Do You Find The Node Which Is Active?
Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once you have selected the SQL Server group, in the right hand side of the console, the column“Owner” gives us the information of the node on which the SQL Server group is currently active.
How Do You Open A Cluster Administrator?
From Start -> Run and type CluAdmin (case insensitive) and the Cluster Administrator console is displayed OR you can also go to Start -> All programs -> Administrative Tools -> Cluster Administrator.
Post a Comment