What is Trigger and types of a trigger?
The trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute a stored procedure.
2 types of triggers that are available in the SQL Server are as follows:
DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or on the view.
DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development database environments.
Logon Triggers: These are very special triggers which fire in case of the logon event of the SQL Server. This is fired before setup of a user session in the SQL Server.
What is the Subquery?
A Subquery is subset of select statements whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
Types of Sub-query:
Single-row sub-query: where the sub-query returns only one row
Multiple-row sub-query: where the subquery returns multiple rows, and
Multiple column sub-query: where the sub-query returns multiple columns
What is SQL Server used for?
SQL Server is one of the very popular Relational Database Management Systems. This is a product from the Microsoft to store and manage the information in the database.
Which language is supported by SQL Server?
SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.
Which is the latest version of SQL Server and when it is released?
SQL Server 2017 is the latest version of SQL Server that is available in the market and Microsoft launched this on 2 October 2017 with the support of the Linux O/S.
What is TCL in SQL Server?
TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.
Which TCL Commands are available on the SQL Server?
There are 3 TCL Commands in the SQL Server. These are as follows:
Commit: This command is used to save the transaction permanently in the database.
Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
Save Tran: This is used for saving the transaction so as to provide the convenience that the transaction can be rolled back to the point wherever required.
What are the 2 types of classifications of constraints in the SQL Server?
Constraints are classified into the following 2 types in the SQL Server:
Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. Definition of these can be given at the time of the creation of a table in the database.
Table Types Constraints: These constraints are applied on a table and these are defines after the creation of a table is completed. Alter command is used to apply the table type constraint.
How is table type constraint applied to a table?
The Table Type Constraint is applied in the following way:
Alter Table Name of the Constraint
Alter Table Constraint_1
What are the different types of Columns Types Constraints in the SQL Server?
SQL Server provides 6 types of Constraints. These are as follows:
Not Null Constraint: This puts a constraint that the value of a column cannot be null.
Default Constraint: This constraint provides some default value that can be inserted in the column if no value is specified for that column.
Check Constraint
Unique Constraint
Primary Key Constraint
Foreign Key Constraint
What are the various editions of SQL Server 2017 that are available in the market?
SQL Server 2017 is available in 4 editions. These are as follows:
Enterprise: This supports in leading the high performance for the Tier 1 database along with the capability of supporting business intelligence and workloads of advanced analytics.
Standard: This supports mid-tier applications to achieve fast performance. This can be easily upgraded to enterprise edition also without having any changes in the coding part.
Express: This is available for free and supports the building of web and mobile applications up to 10 GB in the size.
Developer: This supports building, testing and demonstrating applications in a non-production.
What are functions in the SQL Server?
Functions are the sequence of the statements which accepts inputs, process the inputs to perform some specific task and then provides the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.
What is a User-Defined function in the SQL Server and what is its advantage?
User-Defined Function is a function which can be written as per the needs of the user by implementing your own logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the needs.
This returns Scalar value or a table.
Explain creation and execution of a user-defined function in the SQL Server?
A User-Defined function can be created in the following way:
Create Function fun1(@num int)
returns table
as
return select * from employee where empid=@num
This function can be executed as follows:
select * from fun1(12)
So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of an employee having empid=12.
What are the Pre-Defined functions in the SQL Server?
These are Built-In functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.
What is SQL Server Agent?
SQL Server agent allows us to schedule the jobs and scripts. It helps is implementing the day to day DBA tasks by automatically executing them on a scheduled basis.
What is the PRIMARY KEY?
Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused.
What is a UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
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.
The trigger allows us to execute a batch of SQL code when a table event occurs (Insert, update or delete command executed against a specific table). Triggers are stored in and managed by DBMS. It can also execute a stored procedure.
2 types of triggers that are available in the SQL Server are as follows:
DML Triggers: DML or Data Manipulation Language triggers are invoked whenever any of the DML commands like INSERT, DELETE or UPDATE happens on the table or on the view.
DDL Triggers: DDL or Data Definition Language triggers are invoked whenever any changes occur in the definition of any of the database objects instead of actual data. These are very helpful to control the production and development database environments.
Logon Triggers: These are very special triggers which fire in case of the logon event of the SQL Server. This is fired before setup of a user session in the SQL Server.
What is the Subquery?
A Subquery is subset of select statements whose return values are used in filtering conditions of the main query. It can occur in a SELECT clause, FROM clause and WHERE clause. It nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.
Types of Sub-query:
Single-row sub-query: where the sub-query returns only one row
Multiple-row sub-query: where the subquery returns multiple rows, and
Multiple column sub-query: where the sub-query returns multiple columns
What is SQL Server used for?
SQL Server is one of the very popular Relational Database Management Systems. This is a product from the Microsoft to store and manage the information in the database.
Which language is supported by SQL Server?
SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database.
 |
| Daum Frequently Asked SQL Server Interview Questions Answers |
Which is the latest version of SQL Server and when it is released?
SQL Server 2017 is the latest version of SQL Server that is available in the market and Microsoft launched this on 2 October 2017 with the support of the Linux O/S.
What is TCL in SQL Server?
TCL is Transaction Control Language Commands which are used to manage the transactions in the SQL Server.
Which TCL Commands are available on the SQL Server?
There are 3 TCL Commands in the SQL Server. These are as follows:
Commit: This command is used to save the transaction permanently in the database.
Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.
Save Tran: This is used for saving the transaction so as to provide the convenience that the transaction can be rolled back to the point wherever required.
What are the 2 types of classifications of constraints in the SQL Server?
Constraints are classified into the following 2 types in the SQL Server:
Column Types Constraints: These constraints are applied to the columns of a table in the SQL Server. Definition of these can be given at the time of the creation of a table in the database.
Table Types Constraints: These constraints are applied on a table and these are defines after the creation of a table is completed. Alter command is used to apply the table type constraint.
How is table type constraint applied to a table?
The Table Type Constraint is applied in the following way:
Alter Table Name of the Constraint
Alter Table Constraint_1
What are the different types of Columns Types Constraints in the SQL Server?
SQL Server provides 6 types of Constraints. These are as follows:
Not Null Constraint: This puts a constraint that the value of a column cannot be null.
Default Constraint: This constraint provides some default value that can be inserted in the column if no value is specified for that column.
Check Constraint
Unique Constraint
Primary Key Constraint
Foreign Key Constraint
What are the various editions of SQL Server 2017 that are available in the market?
SQL Server 2017 is available in 4 editions. These are as follows:
Enterprise: This supports in leading the high performance for the Tier 1 database along with the capability of supporting business intelligence and workloads of advanced analytics.
Standard: This supports mid-tier applications to achieve fast performance. This can be easily upgraded to enterprise edition also without having any changes in the coding part.
Express: This is available for free and supports the building of web and mobile applications up to 10 GB in the size.
Developer: This supports building, testing and demonstrating applications in a non-production.
What are functions in the SQL Server?
Functions are the sequence of the statements which accepts inputs, process the inputs to perform some specific task and then provides the outputs. Functions should have some meaningful name but these should not start with a special character such as %,#,@, etc.
What is a User-Defined function in the SQL Server and what is its advantage?
User-Defined Function is a function which can be written as per the needs of the user by implementing your own logic. The biggest advantage of this function is that the user is not limited to pre-defined functions and can simplify the complex code of pre-defined function by writing a simple code as per the needs.
This returns Scalar value or a table.
Explain creation and execution of a user-defined function in the SQL Server?
A User-Defined function can be created in the following way:
Create Function fun1(@num int)
returns table
as
return select * from employee where empid=@num
This function can be executed as follows:
select * from fun1(12)
So, in the above case, a function with the name of ‘fun1’ is created to fetch employee details of an employee having empid=12.
What are the Pre-Defined functions in the SQL Server?
These are Built-In functions of the SQL Server like String functions which are provided by SQL Server like ASCII, CHAR, LEFT, etc. string functions.
What is SQL Server Agent?
SQL Server agent allows us to schedule the jobs and scripts. It helps is implementing the day to day DBA tasks by automatically executing them on a scheduled basis.
What is the PRIMARY KEY?
Primary key is column whose values uniquely identify every row in a table. Primary key values can never be reused.
What is a UNIQUE KEY constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.
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.