MS-SQL interview question for experienced/MS-SQL Interview Questions and Answers for Freshers & Experienced

What is a User-Defined function in the SQL Server and what is its advantage?

User-Defined Function is a function that can be written as per the needs of the user by implementing your 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 requirement.

This returns Scalar value or a table.

What are the options which must be set to allow the usage of optimistic models?

The READ_COMMITED_SNAPSHOT option and the ALLOW_SNAPSHOT_ISOLATION option must be set to allow the usage of optimistic models.

<> The READ_COMMITTED_SNAPSHOT option is used for the read committed optimistic model.

<> ALLOW_SNAPSHOT_ISOLATION option is used for the snapshot isolation level.

Can you name a few encryption mechanisms in SQL server?

These are few encryption mechanisms in SQL Server to encrypt data in the database:

1. Transparent Data Encryption
2. Symmetric Keys
3. Asymmetric Keys
4. Transact SQL functions
5. Certificates

What do you understand by Hotfixes and Patches in SQL Server?

Hotfixes are single, cumulative software packages applied to live systems. This includes one or more files used to address a problem in a software product. Patches are a programs installed on the machines to rectify the problem occurred in the system and ensured the security of the system. So, basically hotfixes are a kind of patch offered by Microsoft SQL Server to address specific issues.

What are the various editions of SQL Server 2019 that are available in the market?

SQL Server 2019 is available in 5 editions. These are as follows:

* Enterprise: This delivers comprehensive high-end datacenter capabilities with blazing-fast performance, unlimited virtualization, and end-to-end business intelligence for mission-critical workloads and end-user access to data insights.
* Standard: This delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premises and cloud-enabling effective database management.
* Web: This edition is a low total-cost-of-ownership option for Web hosters and Web VAPs to provide scalability, affordability, and manageability capabilities for small to large-scale Web properties.
* Express: Express edition is the entry-level, free database and is ideal for learning and building desktop and small server data-driven applications.
* Developer: This edition lets developers build any kind of application on top of SQL Server. It includes all the functionality of Enterprise edition, but is licensed for use as a development and test system, not as a production server.

Mention the 3 ways to get a count of the number of records in a table.

The following are three ways to count the number of records in the table:

1. SELECT * FROM TableName;
2. SELECT COUNT(*) FROM TableName;
3. SELECT rows FROM indexes WHERE id = OBJECT_ID(TableName) AND
indexid< 2;

What is the command DBCC CHECKDB used for?

The command DBCC CHECKDB is used to check the physical and logical integrity of all the objects in the mentioned database. To do that, it performs the following operations:

* Runs DBCC CHECKALLOC on the mentioned database.
* On every table and view in the database, the DBCC CHECKTABLE command is executed.
* Runs DBCC CHECKCATALOG on the database.
* Then it validated the contents of every indexed view in the mentioned database.
* It also validates link-level consistency between file system directories and table metadata while storing varbinary(max) data in the file system using FILESTREAM.
* Finally, it validates the Service Broker data in the database.
So, you just have to execute the DBCC CHECKDB command, and automatically, DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands get executed.

Also, note that DBCC is supported on databases that contain memory-optimized tables but does not provide repair options. This implies that you must regularly back up databases and test those backups.

What are relationships and mention different types of relationships in the DBMS

A relationship in DBMS is the scenario where two entities are related to each other. In such a scenario, the table consisting of foreign key references to that of a primary key of the other table.

The different types of relationships in DBMS are as follows:

1. One-to-One Relationship – Used when a single row in Table A is related to a single row in Table B.
2. One-to-Many Relationship – Used when a single row in Table A is related to many rows in table B.
3. Many-to-Many Relationship – Used when many rows in table A can be related to many rows in table B.
4. Self -Referencing Relationship – Used when a record in table A is related to record in same table.

How can you ensure that the database and SQL Server based application perform well?

A developer must check type of information stored, volume of data and the data that will be accessed.

In a scenario, where you are upgrading an existing system, you should analyze the present data, existing data volumes occur, and check the method through which data is accessed, to help you understand the problem areas for design.

In a scenario, where you are using a new system, you have to keep the information about what data will be captured, and what are the constituents of data, and the relationship between the data items.

What do you understand by User-Defined function in the SQL Server and explain the steps to create and execute a user-defined function in the SQL Server?

A user-defined function is a function written as per the needs of the user by implementing logic. In these kinds of functions the user is not limited to pre-defined functions and simplify the complex code of predefined function by writing simple code. This function returns a scalar value or a table.

To create a user-defined function, refer to the following example:

1. CREATE FUNCTION samplefunc(@num INT)
2. RETURNS TABLE
3. AS
4. RETURN SELECT * FROM customers WHERE CustId=@num

To execute the above-created function, refer to the following command:

1. SELECT * FROM samplefunc(10)

Which is the latest version of SQL Server and when it is released?

SQL Server 2019 is the latest version of SQL Server that is available in the market and Microsoft launched this on November 4th, 2019 with the support of the Linux O/S.

What part does database design plays a role in performance of an SQL Server-based application?

The physical and logical design plays an important part in the performance of SQL Server-based applications. We need to ensure that correct data is captured in proper tables, the data items have proper relationships between them and data redundancy is reduced. I would also suggest that while you are designing a database, make sure that it is an iterative process to achieve all the required system goals and is under constant observation. Once the database design is set, it is very tough to change the design according to requirement. You can only add new relationships and data items.

When do you think a developer should use SQL Server-based cursors?

SQL Server-based cursors are used when you wish to work on a record at any instance of time, rather than taking all the data from a table as a bulk. However, cursors are not preferred to be used when large volumes of data is present as it affects performance. In a scenario, where it is not possible to avoid cursors, then try to reduce the number of records to process by using a temporary table and then eventually build the cursor from this.

What is a Linked Server?

Linked Server is a concept by which we can connect another SQL server to a Group and query both the SQL Servers database using T-SQL Statements sp_addlinkedsrvloginisssed to add link server.

What do you understand by Mirroring and mention the advantages of the Mirroring?

Mirroring in SQL Server is designed to maintain a hot standby server, that is consistent with the primary server in terms of a transaction. Also, the transaction log records are sent from the principal server to the secondary server.

Following are the advantages of Mirroring:

1. Consists of an automatic fail over mechanism.
2. More efficient than Log Shipping and is more robust.
3. The primary server is synced with the secondary server in real-time

Write a query to display the employee name who is teaching EDC?

SELECT E.Emp_Name
FROM Employee E
INNER JOIN Course Instructor Ci
ON E.Emp_No=Ci.Emp_No
INNER JOIN Course C
ON Ci.Course_ID=C.Course_ID
WHERE C.Course_Name=’EDC’

Write a Query to display employee details and whose age>20 & whose name starts with a?

SELECT * FROM Employee
WHERE Salary>20000 AND Age>20 AND Emp_Name LIKE ‘a%’

Write a Query to display employee details whose name starts with a?

SELECT * FROM Employee
WHERE Emp_Name LIKE ‘a%’
a% ----> starts with a
%a ----> ends with a

Write a Query to display employee details whose age is NOT BETWEEN 18 & 22?

SELECT * FROM Employee
WHERE Age NOT BETWEEN 18 AND 22;

What are trace flags and mention a few common trace flags used with SQL Server?

These flags are used to alter server behavior or set server characteristics. Few common trace flags used with SQL Server are as follows

<> 1204, 1205, 1222 – These flags are used for deadlock Information.
<> 174 – This trace flag increases the SQL Server Database Engine plan cache bucket count from 40,009 to 160,001 on 64-bit systems.
<> 1118 – Forces uniform extent allocations instead of mixed page allocations – (SQL 2005 and 2008) To reduces TempDB contention.
<> 652 – This trace flag disables page prefetching scans.
<> 2566 – Used to run the DBCC CHECKDB command without data purity check, unless the DATA_PURITY option is specified.

What are a Scheduled Jobs?

The scheduled job allows a user to run the scripts or SQL commands automatically on a scheduled basis. The user can determine the order in which command executes and the best time to run the job to avoid the load on the system.

What are the methods used to protect against SQL injection attack?

Following are the methods used to protect against SQL injection attack:

* Use Parameters for Stored Procedures
* Filtering input parameters
* Use Parameter collection with Dynamic SQL
* In like clause, user escape characters

What do mean by XML Datatype?

XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.

What is CDC in SQL Server?

CDC is termed as "Change Data Capture." It captures the recent activity of INSERT, DELETE, and UPDATE, which are applied to the SQL Server table. It records the changes made in the SQL server table in a compatible format.

How will you decide the active and passive nodes?

Open Cluster Administrator checks the SQL Server group where you can see the current owner. So the current owner is the active node, and other nodes are passive. Because at one time only one node can be active and must be in the passive mode in a two node.

Which command is used for user defined error messages?

RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.

What are the reporting services in SQL Server?

SQL Server Reporting Services is a comprehensive reporting platform that includes processing components. Processing components are the basis for the multilayered architecture of SQL Server Reporting Services. Processing components interact with each other to retrieve data and deliver a report.

SQL Server Reporting Services has the following two basic components.
Processors
Extensions
Architecture of SQL Server Reporting Services

The following diagram shows the architecture of SQL Server Reporting Services.

What is the difference between a Local and a Global temporary table?

If defined inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.

What are the Hotfixes and Patches in SQL Server?

Hotfixes are small software patches that are applied to live systems. A hotfix is a single, cumulative package that includes one or more files used to address a problem in a software product.

For example - a software bug

A patch is a program installed in the machine to rectify the problem occurred in the system and ensured the security of that system. The hotfix is a Kind of Patches provided by the Microsoft.

In Microsoft SQL Server, hotfixes are small patches designed to address specific issues, most commonly to freshly-discovered security holes. Hotfix response proactively against any bug

What is a performance monitor?

Windows performance monitor is a tool to capture metrics for the entire server. We can use this tool for capturing events of the SQL server also.
Some useful counters are – Disks, Memory, Processors, Network, etc.

What do you understand by INTENT locks?

Microsoft SQL Server uses a lock hierarchy whenever the data is read or something is changed in the data. Whenever a row is read, SQL Server acquires a shared lock. Similarly, as soon as we change a row, SQL Server acquires an Exclusive lock. These locks are incompatible with each other. So, the INTENT locks are used to indicate at a higher level which locks are applied within a lock hierarchy. There are mainly three kinds of INTENT locks:

1. Intent Shared Lock(IS): This lock is used when you have a shared lock at the row level.
2. Intent Update Lock(IU): The Intent update lock is used when you have an update lock at the row level.
3. Intext Exclusive Lock(IX): This lock is used when you have an exclusive lock at the row level.

What is the difference between GETDATE and SYSDATETIME?

Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.

Where are SQL Server user names and passwords stored in SQL Server?

User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.

How to delete duplicate rows in SQL Server?

Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.

What is faster? A table variable or a temporary table?

A table variable is faster in most cases since it is held in memory while a temporary table is stored on a disk. However, when the table variable’s size exceeds memory size the two table types tend to perform similarly.

What is the Log Shipping?

Log shipping is the process of automating the backup of a database and transaction log file on a primary database server and then restoring them on a standby server. Many servers support this technique for maintaining a backup server, such as Microsoft SQL Server, 4D server, MySQL, and PostgreSQL.

The primary purpose of log shipping is to increase database availability just like replication. On each of secondary database, log backups are applied particularly.

Steps for log shipping process:

* Firstly take a backup of transaction log file on Primary SQL server instance
* Copy the log file on secondary SQL server instance
* Restore the Log backup file onto secondary SQL Server instance

What is the difference between abstract and interface?

Abstract class:

* It provides a set of rules to implement next class. Rules are provided through abstract methods.
* An Abstract method does not contain any definition.
* When a class contains all functions without the body, it is called as Fully Abstract Class.
* Another class can inherit only one abstract class.

Interface:

* If a class contains all abstract methods, then that class is called Interface.
* Interface support like multiple inheritances.
* An Interface does not contain any implementation
* We can only use public or abstract modifiers.

What is the use of @@SPID?

A @@SPID returns the session ID of the current user process.

What is the difference between varchar and nvarchar types?

Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.

What is the difference between COMMIT and ROLLBACK?

Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.

What will be the maximum number of index per table?

For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.

Which SQL server table is used to hold the stored procedure scripts ?

Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure. Name of the stored procedure is saved in the table called Sys.Procedures.

What is faster? A table variable or a temporary table?

A table variable is faster in most cases since it is held in memory while a temporary table is stored on a disk. However, when the table variable’s size exceeds memory size the two table types tend to perform similarly.

New commands in SQL 2008?

Database encryption, CDCs tables – For on the fly auditing of tables, Merge operation, INSERT INTO – To bulk inserts into a table from another table, Hierarchy attributes, Filter indexes, C like operations for numbers, resource management, Intellisense – For making programming easier in SSMS, Execution Plan Freezing – To freeze in place how a query is executed.

What do you mean by CTEs? How will you use it?

CTEs also is known as common table expressions are used to create a temporary table that will only exist for the duration of a query. They are used to create a temporary table whose content you can reference to simplify a query structure.

Describe what you know about PK, FK, and the UK.

Primary keys – Unique clustered index by default, doesn’t accept null values, only one primary key per table.

Foreign Key – References a primary key column. It can have null values. Enforces referential integrity.

Unique key – can have more than one per table. It can have null values. It cannot have repeating values. Maximum of 999 clustered indexes per table.

What is the difference between a derived attribute, derived persistent attribute, and computed column?

A derived attribute is an attribute that is obtained from the values of other existing columns and does not exist on its own. A derived persistent attribute is a derived attribute that is stored. A computed attribute is an attribute that is computed from internal system values.

What is the use of SET NOCOUNT ON/OFF statement?

By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).

What are the differences between Stored Procedure and the dynamic SQL?

Stored Procedure is a set of statements which is stored in a compiled form. Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply execute during run time.

Search
R4R Team
R4R provides MS-SQL Freshers questions and answers (MS-SQL Interview Questions and Answers) .The questions on R4R.in website is done by expert team! Mock Tests and Practice Papers for prepare yourself.. Mock Tests, Practice Papers,MS-SQL interview question for experienced,MS-SQL Freshers & Experienced Interview Questions and Answers,MS-SQL Objetive choice questions and answers,MS-SQL Multiple choice questions and answers,MS-SQL objective, MS-SQL questions , MS-SQL answers,MS-SQL MCQs questions and answers Java, C ,C++, ASP, ASP.net C# ,Struts ,Questions & Answer, Struts2, Ajax, Hibernate, Swing ,JSP , Servlet, J2EE ,Core Java ,Stping, VC++, HTML, DHTML, JAVASCRIPT, VB ,CSS, interview ,questions, and answers, for,experienced, and fresher R4r provides Python,General knowledge(GK),Computer,PHP,SQL,Java,JSP,Android,CSS,Hibernate,Servlets,Spring etc Interview tips for Freshers and Experienced for MS-SQL fresher interview questions ,MS-SQL Experienced interview questions,MS-SQL fresher interview questions and answers ,MS-SQL Experienced interview questions and answers,tricky MS-SQL queries for interview pdf,complex MS-SQL for practice with answers,MS-SQL for practice with answers You can search job and get offer latters by studing r4r.in .learn in easy ways .