SQL Server Architecture Interview Questions and Answers

SQL Server Architecture Interview Questions and Answers

SQL Server is based on client-server architectural model in which there is a Server (having the Database) which is shared or accessed by several Users with the help of client applications.SQL server could be defined as A client or server RDBMS (Relational Database Management System) based on a programming language known as Structured Query Language (SQL)

Can you explain SQL server?

What are the types of indexes available with SQL Server?

When do we use the UPDATE_STATISTICS command?

Which TCP/IP port does SQL Server run on?

From where can you change the default port?

What is the use of DBCC commands?

Can you give me some DBCC command options?

What command do we use to rename a db?

What is a Join in SQL Server?

Can you explain the types of Joins that we can have with Sql Server?

When do you use SQL Profiler?

Can you define Linked Server?

Can you link only other SQL Servers or any database servers such as Oracle?

Which stored procedure will you be running to add a linked server?

What are the OS services that the SQL Server installation adds?

Can you explain the role of each service?

How do you troubleshoot SQL Server if its running very slow?

How do you troubleshoot?

What are the authentication modes in SQL Server?

Where do you think the user’s names and passwords will be stored in sql server?

Can you define log shipping? Can we do log shipping with SQL Server 7.0

Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow?

Can you define BCP? When do we use it?

What should we do to copy the tables, schema and views from one SQL Server to another?

Can you define RAID and what are different types of RAID configurations?

Can you explain database architecture?

What are pages and Extents?

Can you explain system database?

What are Page Splits?

Can you explain what are the database objects?

Can you explain Illustrate physical database architecture?

Can you explain logical database components

Please illustrate physical database architecture?

Can you explain Scheduler, Request, Session, Connection in SQL Server architecture?

Can you define TEMPDB Database?

What is the difference between CUBE operator and ROLLUP operator?

Can you define MSDB Database?

What is SNI Protocol Layer?

Do you know what the restrictions applicable while creating views are?

Can you explain Master Database?