SQL Server Interview Questions & Answers 3
In this post we will discuss some more SQL Server Interview Questions & Answers.
1. What is SQL Server Architecture?
Ans:
2. What is page?
Ans: The fundamental unit of data storage in SQL Server is the page. In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.
3. What is extent?
Ans: Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. SQL Server has two types of extents:
· Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
· Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.
4. What is fill factor?
Ans: Fill Factor is Percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth.
5. How to take backup of DB when the db is in Log shipping by taking that backup without changing LSN?
Ans: By taking copy only backups.
BACKUP DATABASE TEST TO DISK=’C:\BACKUP’ WITH COPY_ONLY
6. How to change port number for SQL Server?
Ans: To assign a TCP/IP port number to the SQL Server Database Engine
https://www.hightechnology.in/how-to-change-default-tcpip-port-of-sql-server/
7. Is it possible to change port for mirroring after configuring mirroring?
Ans: Yes it is possible, by following above method.
8. What is the major difference between Merge replication and Transactional replication?
Ans: In Merge replication the both publisher and subscriber can work independently
9. What is quorum in clustering?
Ans: Quorum is the cluster’s configuration file.This file (quorum.log) resides in the the quorum disk (one disk from shared disk array).Quorum is the main interpreter between all nodes. It stores latest cluster configuration and resource data. This helps the other nodes to take ownership when one node goes down.
In real life, it is like emergency contact number and other medical information in your wallet which help others to take action in case of emergency.
10. What happened when quorum gone?
Ans: There are cases where a cluster must be allowed to continue even if it does not have quorum. Consider the case of a geographically dispersed cluster with four nodes at the “primary” site and three nodes at the “secondary” site. While there are no failures, the cluster is a 7-node cluster where resources can be hosted on any node, on any site (depending on business needs). If there is a communications failure between the sites or if the secondary site is taken offline (or fails), the primary site can continue since it will still have quorum. All resources will be re-hosted and brought online at the primary site.
11. Is it possible to start service when quorum is gone?
Ans: -No quorum
12. How to take backup of 400GB DB with in less time?
Ans: Use stripped backup. Follow: https://www.hightechnology.in/striped-backup-sql-server/
13. How to find what are the driver’s available in our machine?
Ans: EXEC master.sys.xp_fixeddrives
14. What is check point?
Ans: A checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.
15. Is it posible to raise chaeckpoint our self?
Ans:Yes
USE AdventureWorks2012 ;
GO
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure ‘recovery interval’, 3 ;
GO
RECONFIGURE;
GO
16. What is the advantage of recompile statement in procedure?
Ans: I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles marks stored procedures to recompile when they execute next time.
17. How to know the current connected connections?
Ans:
select * from sys.dm_exec_connections
where session_id = @@SPID
18. How to set maximum connections?
Ans: SP_Configure
19. How to start sql service without raising checkpoint?
Ans: SHUTDOWN WITH NOWAIT
20. What is transaction?
Ans: IT is sequence of actions.
21. Can you say syntax of transaction?
Ans:
Begin Tran
Statements
Commit Tran
22. What is difference between cascade drop of table?
Ans: whenever you delete a parent row (a customer), the dependent (child) rows are deleted as well in cascade.
23. What happened if we issue drop table command?
Ans: If the tables are involved in foreign key relation, if we try to delete those tables. It will not allow deleting. If we delete under any circumstances we define that as cascade
24. What are the several recovery models?
Ans: Full, Simple, Bulk logged recovery model
25. Why log shipping is not supported in simple recovery model?
Ans: No log backups is done on simple recovery model. It will Automatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log space.
26. What is the default port for mirroring?
Ans: 5022
27. What is resource governor?
Ans: SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.
28. What is heart beating in clustering?
Ans: Each server needs at least two network cards. Typically, one is the public network and the other is a private network between the nodes. The private network is used for internal communication of cluster.This is referred as heart beat.
29. Which cable is used for heart beat, Is it cross cable or plain cable?
Ans: Cross cable
30. What are the different types of indexes?
Ans:
Clustered
A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.
Nonclustered
A nonclustered index can be defined on a table or view with a clustered index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.
Unique
A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique. Uniqueness can be a property of both clustered and nonclustered indexes.
Columnstore
An xVelocity memory optimized columnstore index based on vertical partitioning of the data by columns, stored as large objects (LOB).
Index with included columns
A nonclustered index that is extended to include nonkey columns in addition to the key columns.
Index on computed columns
An index on a column that is derived from the value of one or more other columns, or certain deterministic inputs.
Filtered
An optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
Spatial
A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometrydata type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.
XML
A shredded, and persisted, representation of the XML binary large objects (BLOBs) in the xml data type column.
Full-text
A special type of token-based functional index that is built and maintained by the Microsoft Full-Text Engine for SQL Server. It provides efficient support for sophisticated word searches in character string data.
31. What are the limits for cluster and non cluster indexes?
Ans: 1- 255
32. Why we have only one cluster index for table?
Ans: Clustered index defines the way in which data is ordered physically on the disk. And there can only be one way in which you can order the data physically. Hence there can only be one clustered index per table.
33. How to find sql server version?
Ans: select @@version
34. What is another port number for sqlserver?
Ans: for TCP/IP: 1433, UDP: 1434
35. I have backup and I want to restore upto particular time only?
Ans: Restore database wowzzy from disk=” with stopat=time stamp’
36. What is Super admin, who is having total rights in system level?
Ans: SA
37. What is full form of SA?
Ans: System Administrator