Explore topic-wise InterviewSolutions in .

Push & Pull is a type of Replication configuration. It decides how data will be replicated to the SUBSCRIBER.

• Push – In Push subscription, the publisher is responsible for sending data. Publisher pushes data from the publisher to the subscriber. Distributor agent will run on the Distributor to push changes. The distributor can be CONFIGURED on the Publisher or separate server. Changes can be pushed to subscribers on demand, continuously, or on a scheduled basis.

Pull is best suited configured when the subscriber is connected all the time and need the latest data all the time accessed.

• Pull - In Pull subscription, Subscribers is responsible for FETCHING data. Subscribers requests changes from the Publisher. Distributor agent will run on the subscriber to Pull changes. The subscriber can pull data as & when needed.

Pull is best suited when subscribers are not online every time. The subscriber can allow data lag and can wait for delay in data REFRESH

• Snapshot replication – Snapshot replication works on a snapshot of the published objects. Snapshot agent takes care of it and applies it to a subscriber. Snapshot replication overwrites the data at the subscriber each time a snapshot is applied. It’s best suited for fairly static data or sync interval is not an issue. Subscriber does not always NEED to be connected.

• Transactional replication – Transactional replication replicates each transaction for the article being published. For initial setup, Snapshot or backup is required to copy article data at the subscriber. After that Log Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. It’s the most widely used replication.

• Merge replication – Merge replication is the most complex types of replication which allow changes to happen at both the PUBLISHER and subscriber. Changes happen at publisher & subscriber are merged to KEEP data consistency and a uniform SET of data. For an initial setup like transactional replication, Snapshot or backup is required to copy article data at the subscriber. After that Merge Reader Agent reads it from the transaction log and writes it to the distribution database and then to the subscriber. The merge agent is capable of resolving conflicts that occur during data synchronization.

• Collation of TempDB should be the same as the SQL Server INSTANCE collation.
• TempDB database should be sa.
• Guest user should not drop or revoke permissions from TempDB database
• Keep the recovery model SIMPLE only.
• Configure tempdb files to automatically grow as required.
• Ensure TempDB drives is with RAID PROTECTION in order to PREVENT a single disk failure from shutting down SQL Server
• Keep the TempDB database to separate set of disks
• TempDB database size should be according to server load.
• TempDB database data files should be configured as per available C.P.U. Cores
•  If no of cores < 8 then no. of data files  equals to no of logical processors
• If no. of cores between 8 to 32 inclusive then no. of data files  equals to 1/2 data files as logical processors
• If no. of cores > 32 then no. of data files  equals to 1/4 data files as logical processors
• Ensure each data file should be of the same size to allow optimal proportional-fill performance.
• Reside tempdb database on a fast I/O subsystem.
• Configure auto growth if tempDB data  Log files to a reasonable size to avoid the tempdb database files from growing by too small a VALUE.
• Guidelines for setting the FILEGROWTH increment for tempdb files
• If TempDB file size > 0 and < 100 MB then the recommended filegroup increment can be 10 MB
• If TempDB file size > 100 and < 200 MB then the recommended filegroup increment can be 20 MB
•  If TempDB file size >= 200 MB then the recommended filegroup increment can be 10% OR any fixed value depending on the requirement or on basis of I/O system capabilities
• Do not shrink TempDB unless necessary.
• Do not enable auto-create statistics & auto update statistics.
• Ensure to have auto close OFF

Each table can have only one Cluster & multiple nonclustered indexes. All nonclustered indexes use index KEY of cluster index or directly depends on the clustered index.

Because of this dependency, we usually got this question if REBUILDING the clustered index will rebuild the nonclustered index on a table or not.

The answer is NO. Cluster index rebuild will not rebuild or reorganize the nonclustered index. You need to PLAN a nonclustered index rebuild or reorganization separately.

Index Reorg

• The reorganization is an "online" operation.
• Reorganization only AFFECTS the LEAF LEVEL of an index
• Reorganization shuffle data place to rectify existing allocated pages of index
• The reorganization is always a fully-logged operation
• Reorganization can be stopped or killed any TIME, no need for a rollback

Index Rebuild

• Rebuild is an "offline" operation by default.
• Rebuild creates a completely new structure of index B-Tree
• Rebuild uses new pages/allocations
• Rebuild can be a minimally-logged operation
• Rebuild can be stopped or killer but it requires rollback to complete TRANSACTIONALLY

An SQL injection is a web hacking techniques done by unauthorized personnel or processes that might DESTROY your database.

An SQL injection is a web security vulnerability that allowed hackers to access application code and queries used to interact with the database. Hacker uses those methods to RETRIEVE data BELONGING to other USERS and data not authorized to them. The major challenge is SQL injection can cause a system crash, data stolen, data corruption, etc.

An SQL injection is not a TASK of one man or team. Complete support and architecture team work together to get it prevented to happen.Developers \ DBAs are responsible for DB security and proper SQL code. Applications developers are responsible for application code and Db access methods. Infra team is responsible for network, firewall & OS security.

Proper SQL instance, OS & Farwell security with a well-written application can help to reduce the risk of SQL injection.

The Guest user account is created by default with SQL server installation. Guest user is not MAPPED to any login but can be used by any login when explicit permissions are not granted to access an object. You can drop the guest user from all databases except Master and TempDB.

When any user login to the SQL Server, it has 2 parts Server & database. First, at the Server level, user authentication VERIFIES at the server level and User is able to login to SQL Servers. Second, Login and mapping to the database are verified. In case, Login is not mapped to any user but able to log in on SQL server. SQL automatically map that login to Guest and GRANT him database access.

One of the security recommendations of Microsoft is to Drop or disable a Guest user in every database except Master & TempDB database. By having Guest user, you are at risk of unauthorized access to data.

SQL Server agent has lots of features and sometimes you need to give rights on an agent to manage and view jobs. Allowing every one with sysadmin permission is not appropriate.

SQL Server has 3 fixed DB roles for permission on SQL Agent & Jobs.

• SQLAgentUserRole – Users with SQLAgentUserRole rights can manage Agent jobs CREATED by SELF. Self-owned jobs can be managed by this rights, he can not view other jobs AVAILABLE on the server.
• SQLAgentReaderRole – This is the 2nd level of permission on the agent. USER with SQLAgentReaderRole rights can only view job available on server but cannot change or modify any jobs. This roll gives the ability to review multi-server jobs, their configurations, and history with SQLAgentUserRole rights.
• SQLAgentOperatorRole - SQLAgentOperatorRole  roles gives you highest permission on agent. User with this role has the ability to review operators, proxies and alerts, execute, stop or start all local jobs, delete the job history for any local job as well as enable or disable all local jobs and schedules with AQLAgentReaderRole rights.

SQL Server Agent provides MULTIPLE functionalities LIKE:-

• SQL Server Agent is a Windows service come with all SQL editions other than express.
• SQL Server Agent is like task SCHEDULER of OS. It used to schedule jobs/PROCESS & reports.
• SQL Server Agent is also provided inbuilt features of Alerts like Blocking, deadlock, etc.
• SQL Server Agent support multiple types of process like T-SQL \ CMD  \PowerSheel \ SSAS \ SSIS for scheduled execution.
• SQL Server Agent provides the proxy option to secure and limit the direct user access on CRITICAL sub-systems.

You need to restore Last SUNDAY full BACKUP happen at 2 AM then Wednesday DIFFERENTIAL backup of 2 AM followed by all Transaction log backups after. Consider all backups are valid, in CASE of any bad backup file recovery path, will be changed.

Pseudo-simple Recovery Model is situational based recovery model. This is something not given by project but ones with CIRCUMSTANCES. It’s a situation where the DATABASE may be configured in the FULL recovery model but BEHAVES like a SIMPLE recovery model.

The database will behave like a SIMPLE recovery model until a full database backup is taken after switching recovery model to Full.

For example, You have a database in Simple Recovery Model and now you have switched it to the FULL recovery model. You NEED to perform a full backup after switching recovery model otherwise database will keep behaving like simple and keeps TRUNCATING Transaction log on commit.

With Compressed backups, SQL Server works on the pre-allocation algorithm to determine how much space the compressed backup will require and will allocate this space at the start of the backup PROCESS. At the time of backup completion, the backup process might expand or shrink the backup file on the basis of actual requirement.

Pre-Allocation helps SQL Server save the overhead of constantly GROWING the backup file.

Sometimes when we have a very large database and the difference in uncompressed and compressed backups is very high, preallocated and actual backup space has a significant difference. Trace FLAG 3042 promote better space management with little over-head to SQL server to grow backup file on a regular basis. This is useful with limited disk space.

Enabling trace flag 3014 may result in a little spike in CPU &AMP; memory but not significant to cause performance issues.

SQL Server supports the following types of database backups:

• Full Backup – It contains a COMPLETE backup of the database.
• Differential backup – Differential backup is also known as incremental backup. It contains changes happen after the last full backup.
• Log Backup – Log backup contains a backup of the transactional log after last log backup. It contains both committed &AMP; uncommitted transactions.
• Copy-Only Backup – SPECIAL backup type that does not IMPACT the sequence of regular backups and gives you a copy of the complete database.
• FILEGROUP Backup – SQL gives you the capability of one or more filegroup backup in place of complete database backup.
• Partial Backup – Partial backup helps you to take a backup of specific data from different files or filegroups.

SQL Server database backups are supported by all 3 recovery models:

• Simple
• Full
• Bulk-logged

• SQL Server Database Engine
• SQL Server Analysis Services (SSAS)
• SQL Server Reporting Services (SSRS)
• SQL Server Integration Services (SSIS)
• SQL Server MANAGEMENT Studio
• SQL Server Profiler
• SQL Server CONFIGURATION Manager
• SQL Server CEIP (TELEMETRY Services)
• SSIS CEIP (Telemetry Services)
• Database TUNING Advisor
• SQ Profiler
• R Service
• Connectivity Components
• Communication between clients and servers
• Network LIBRARIES for DB-Library, ODBC, and OLE DB.
• Documentation and Samples
• Books Online

Checkpoint

• Checkpoint occur on Recovery time interval, Backup/Detach Command & on the execution of DDL command.
• Checkpoint writes only dirty pages to the disk. Checkpoint does not release any memory.
• The checkpoint is responsible for DB recovery point.
• Checkpoint always mark entry in T-log before it executes either SQL engine or manually
• Checkpoint occurrence can be monitored using performance monitor “SQL Server BUFFER Manager Checkpoint/sec”.
• You need SYSADMIN, DB_OWNER and DB_BACKUPOPERATOR rights to execute checkpoint manually

Lazy Writer

• Lazy Writer release buffer pool memory when memory pressure occurs to ensure enough FREE memory.
• Lazy Writer looks for LRU, least recently used (“COLD” = least recently read or WRITTEN, not accessed in recent time) pages in the buffer pool and releases the memory taken by them. Lazy writer releases both dirty and clean pages. Clean pages can be released without writing to disk and dirty pages release after written to the disk.
• Lazy Writer is only responsible for managing free buffer SPACE. The lazy writer does not affect or manage database recovery.
• The lazy writer doesn’t mark any entry in T-log.
• Lazy writer occurrence can be monitored using performance monitor “SQL Server Buffer Manager Lazy writes/sec”.
• SQL server executes its own. User cannot run it manually

SQL Server supports DIFFERENT types of isolation level.

1. Read Uncommitted – Read Uncommitted is the lowest isolation level allow dirty reads. Here, CHANGES does by one TRANSACTION is visible to other transactions before committing.
2. Read Committed – Read Committed s isolation does not allow dirty read. Any data read by the transaction is 100% committed. Till the time transaction is updating the record, it holds exclusive locks on it.
3. Repeatable Read – Repeatable read is the most restrictive isolation level which holds locks on the table EVEN with read transactions. Table data cannot be modified from any other sessions until the transaction is completed.
4. Serializable – This is the Highest isolation level similar to repeatable read with the prevention of Phantom Read. It ensures transaction referencing same records must run in serially.
5. Snapshot – This also known as RCSI (Read Committed Snapshot Isolation). It’s similar to Serializable Isolation but the only difference is Snapshot does not hold a lock on the table during the transaction so that the table can be modified in other sessions. Snapshot isolation MAINTAINS versioning in Tempdb for old data called Row Versioning.

The DMV (Dynamic Management Views) is a set of system views introduced with SQL Server 2005. DMV’s are a new tool of DBA to get internal INFORMATION of the system and it’s working.

• sys.dm_exec_query_stats
• sys.dm_exec_sql_text
• sys.dm_os_buffer_descriptors
• sys.dm_tran_locks - Locking and blocking
• sys.dm_os_wait_stats - WAIT stats
• sys.dm_exec_requests Percentage – For Percentage COMPLETE for a process
• Sys.dm_exec_sessions

An IDENTITY column in the table has auto-generate & auto increase value with each new row insert. The user cannot insert value in the identity column.

The sequence is a new feature introduced with SQL Server 2012 similar to Oracle’s sequence objects.  A sequence object generates a sequence of unique numeric VALUES as per the specifications mentioned. Next VALUE for a SEQUENCE object can be generated using the NEXT VALUE FOR clause.

• IDENTITY is column level property & tied to a particular table. This cannot be SHARED among multiple TABLES.
• SEQUENCE is an object DEFINED by the user with specific details and can be shared by multiple tables. This is not tied to any particular table.
• IDENTITY property cannot be reset to its initial value but the SEQUENCE object can be reset to initial value any time.
• Maximum value cannot be defined for IDENTITY whereas this can be done for SEQUENCE object.

A page is a fundamental UNIT of storage that stores data. The page is the size of 8KB and we have 8 types of pages in SQL Server.

• Data
• Index
• Text/Image (LOB, ROW_OVERFLOW, XML)
• GAM (Global ALLOCATION Map)
• SGAM (Shared Global Allocation Map)
• PFS (Page Free Space)
• IAM (Index Allocation Map)
• BCM (Bulk CHANGE Map)
• DCM (Differential Change Map)

There are three methods.

1. Replication monitor
2. Replication commands
3. Tracer Tokens

• Replication Monitor: In the replication monitor from the list of all subscriptions just double click on the DESIRED subscription. There we find three tabs.
  • Publisher to Distributor History
  • Distributor to Subscriber History
  • Undistributed commands
• Replication Commands: 
  • Publisher.SP_ReplTran: Checks the pending transactions at p
  • Distributor.MSReplCommands and MSReplTransactions: Gives the transactions and commands details. Actual T_SQL data is in binary format. From the entry TIME, we can estimate the LATENCY.
  • Distributor.SP_BrowseReplCmds: It shows the eaxct_seqno along with the corresponding T-SQL command
  • sp_replmonitorsubscriptionpendingcmds: It shows the total number of pending commands to be applied at subscriber along with the estimated time.
• Tracer Tokens:

Available from Replication Monitor or via TSQL statements, Tracer Tokens are special timestamp transactions written to the Publisher’s Transaction Log and picked up by the Log Reader. They are then read by the Distribution Agent and written to the Subscriber. Timestamps for each step are recorded in tracking tables in the Distribution Database and can be displayed in Replication Monitor or via TSQL statements.

When Log Reader picks up Token it records time in MStracer_tokens table in the Distribution database. The Distribution Agent then picks up the Token and records Subscriber(s) write time in the MStracer_history tables ALSO in the Distribution database.

Below is the T-SQL code to use Tracer tokens to troubleshoot the latency issues.

Before choosing the RAID (Redundant Array of Independent DISKS) we should have a look into the USAGE of SQL Server files.

As a basic thumb rule “Data Files” need random access, “Log files” need sequential access and “TempDB” must be on the fastest drive and must be separated from data and log files.

We have to CONSIDER the below FACTORS while choosing the RAID level:

• Reliability
• Storage Efficiency
• Random Read
• Random Write
• Sequential Read
• Sequential Write
• Cost.

As an Admin, we have to consider all of these PARAMETERS in choosing the proper RAID level. Obviously, the choice is always between RAID-5 and RAID-10

Replication does not continue after a log shipping failover. If a failover occurs, replication agents do not connect to the secondary, so transactions are not replicated to Subscribers. If a fallback to the primary occurs, replication resumes. All transactions that log shipping COPIES from the secondary back to the primary are replicated to Subscribers.

For transactional replication, the behaviour of log shipping depends on the sync with a backup OPTION. This option can be set on the publication database and distribution database; in log shipping for the Publisher, only the setting on the publication database is relevant.

Setting this option on the publication database ensures that transactions are not delivered to the distribution database until they are BACKED up at the publication database. The last publication database backup can then be restored at the secondary server without any POSSIBILITY of the distribution database having transactions that the restored publication database does not have. This option guarantees that if the Publisher fails over to a secondary server, consistency is maintained between the Publisher, Distributor, and Subscribers. Latency and throughput are affected because transactions cannot be delivered to the distribution database until they have been backed up at the Publisher.

• SNAPSHOT Agent: Copy Schema+Data to snapshot folder on distributor. Used in all types of replication.
• Log reader Agent: Sends transactions from Publisher to Distributor. Used in transactional replication
• Distribution Agent: Applies Snapshots / Transactions to all subscribers’ runs at a distributor in PUSH and Runs at Subscriber in PULL. Used in transactional and transactional with updatable subscriptions.
• Queue reader Agent: Runs at distributor send back transactions from subscriber to publisher. Used in Transactional With updatable subscriptions.
• Merge Agent: Applies INITIAL snapshot to subscribers, from the next time SYNCHRONIZE by resolving the conflicts.

Events:

Event Group: Performance

• Event: ShowPlan_ALL (BinaryData COLUMN must be SELECTED)
• Event: ShowPlan_XML

Event Group: T-SQL

• Event: SQL:BatchStarted
• Event: SQL:BatchCompleted

Event Group: Stored Procedures

• Event: RPC:Completed

Event Group: Locks

• Event: Lock: Deadlock Graph
• Event: Lock: Lock Deadlock Chain (Series of events that leads to a deadlock)

Event Group: Sessions

• Event: Existing Connection

Event Group: Security Audit

• Event: Audit Login
• Event: Audit LOG Out

Columns:

Below are the most common columns that HELP us in understanding the trace file to troubleshoot the problems.

• TextData
• ApplicationName
• NTUserName
• LoginName
• CPU
• Reads
• Writes
• Duration
• SPID
• StartTime
• EndTime
• Database Name
• Error
• HostName
• LinkedServerName
• NTDomainName
• ServerName
• SQLHandle

All these columns need not be available for all of the events but depend on the event select we have to choose the appropriate columns.

Filters:

• ApplicationName
• DatabaseName
• DBUserName
• Error
• HostName
• NTUserName
• NTDomainName

1. Make a note of all node names (and/or IP addresses), SQL Server virtual names along with preferred nodes. If there are more than THREE nodes you may need to also take note of possible owners for each SQL resource group. For my example assume that I have a cluster with node1 and node2, SQL1 normally lives on node1 and SQL2 normally lives on node2.
2. To start with a clean slate and ensure any previous updates are completed both nodes should be restarted if possible. Choose the physical node that you want to patch second and restart that node (in my example node2).
3. Restart the node you want to patch first (node1). This will MEAN that both active SQL instances are now running on node2. Some restarts will be essential, but you could avoid the first two restarts if you need to keep downtime to a minimum and just fail SQL1 over to node2. The main point here is to always patch a passive node.
4. In a cluster, the administrator removes node1 from the possible owner's lists of SQL1 and SQL2. This means that neither SQL instance can fail over to node1 while it is being patched.
5. Run the service pack EXECUTABLE on node1.
6. Restart node1.
7. Add node1 back into the possible owner's lists of SQL1 and SQL2 and fail both instances over to node1.
8. Repeat steps 4 – 6 on node2.
9. Add node2 back into the possible owner's lists of SQL1 and SQL2 and fail both instances over to node2. Check that the building level is CORRECT and review the SQL Server error logs.
10. Fail SQL1 over to node1. Check build levels and SQL Server error logs

If you find that the product level is not consistent across all the nodes, you will need to fool the 2005 patch INSTALLER into only patching the nodes that need updating. To do so, you will have to perform the following STEPS:

1. Fail Instance, Cluster, and MSDTC groups to an UNPATCHED node
2. Remove any SUCCESSFULLY patched nodes from failover candidates of the SQL Server Service of the instance GROUP (do this using Cluster Admin tool)
3. Run the patch
4. After the patch installs successfully, add the Nodes removed in Step 2 back to the SQL Server Service of the Instance group

Why do you need to do this? Well when the patch installer determines that not all nodes in the cluster are at the same patch level, a passive node operation will fail and will prevent you from moving forward with any further patching.

The WHERE clause is the most widely used command in SQL and used for filtering records on the result set. The HAVING clause does the same THING but on the grouped result set. The WHERE clause will be executed FIRST before having a clause trigger. Let me try to explain the differences with examples. Any SQL statement FOLLOWS below syntax:

The order of execution of SQL statements follows from top to bottom. It implies that records are filtered first on the WHERE clause and once the result set is grouped, HAVING clause comes into the picture.

Let me list down some basic difference then we will try to understand with examples as well :

1. Truncate is lightning fast when we compare it with DELETE commands. The reason for the same is less usage of transaction logs in Truncate. DELETE erase records one by one and at the same time transaction logs get maintained for the get for every record. TRUNCATE  erases the records by deallocating space from PAGES and it makes a single entry in logs.
2. Triggers are not fired in case of TRUNCATE while delete command fires trigger.
3. The identity column values get reset in case of TRUNCATE while delete does not reset the identity column value.
4. In the case of foreign KEY constraint or tables used in REPLICATION, Truncation happen.
5. DELETE falls into the DML category while TRUNCATION falls into the DDL category of commands.

Please consider below example where EVEN after deleting records when transaction was rolled back, it reverted the changes :

UDF represents user-DEFINED represents which are designed to accept the parameter and does processing on parameter before returning a result set of processing or actions. The result could be either a scalar value or a complete result set. The UDF’s are widely used in scripts, stored procedures and within other’s UDF as well.        

There are several benefits of using UDF :

1. UDF is designed keeping in mind modular programming. Once you have created UDF, we can call the UDF multiple times and it is not dependent on source code so we can easily MODIFY it.
2. UDF is designed to reduce compilation cost by caching plan so we can REUSE them without compiling it.
3. The WHERE clause is considered as an expensive operation so if we have some filter on complex constraints we can easily create UDF for the same and same UDF can be replaced in WHERE clause.

There are two types of UDF based on the output value of UDF’s

• The scalar functions-The output of this function is scalar VALUES. It may accept zero or more parameters and can return any SQL defined data type other than text, ntext, image, cursor, and timestamp.

• Table values functions- This type of function return table as resultset. It can accept zero or more parameters but always return table.

The PRIMARY key is the single column or combination of the column which uniquely distinguishes individual rows in the table. It MUST be unique but can not have  NULL values which make it different then UNIQUE KEY. A table can have only a single primary key and contain a single column or combination of columns called a composite key.  The foreign key in the table is actually the to the primary key of another table. Please find below the list of differences between two Keys which look SIMILAR in nature :

1. Primary Key can not have NULL values while foreign key can have NULL values.
2. Be default primary key creates CLUSTERED Index on the table which physically sorts out data based on key while there is no index created on the foreign key. We need to explicitly create indexes on the foreign key.
3. We can have a single primary key in the table but we can have multiple foreign keys in the table.

A normalization which ensures a reduction in redundancy and dependability. It is a popular database breaking down large tables into smaller ones with the help of a relationship. Edgar code was the first one who coined the term normalization and came with three different forms of normalization(1NF,2NF,3NF). Raymond F. Boyce later added another form of normalization which has been NAMED after both inventors (Boyce-Codd NF). Till now we have below normalization forms available so far:

• 1st Normal Form
• 2nd  3rd
• Boyce-Codd NF
• 4th    5th 6th

But in most practical scenario database design is well supported till 3NF only. Now, let us understand the first four forms of normalization:

• 1NF:  1NF has the below characteristics :
  • Each ROW cell   should have a single value
  • Each row needs to be unique

• 2NF: The second normal form is an extension of 1NF. It has two rules:
  • It has to be 1NF
  • It should have a single column as Primary Key

The above 1NF table can be extended to 2NF by diving above tables into below two tables:

• 3 NF: This normalization form has also two rules:
  • It has to be in 2 NF
  • It should not have a structure in which changes in non-key column value changes another non-key column

For the above table structure, we can have below design to support 3 NF

• BC NF: This is required when we have more than one candidate key.

It is very critical for any database to maintain DATA integrity and having consistent data. For choosing any database this is one of the important CONSIDERATIONS. Data architects evaluate database on ACID  properties only. The ACID is an acronym and STANDS for Atomicity, Consistency Isolation, Durability.

Let me EXPLAIN to you four pillars of ACID property:

• Atomicity -These properties ensure that either all operation part of any database transaction will commit or none of the operations will be successful. This will ensure the consistency of data. Nothing partially gets committed in the database either all or none.
• Consistency-This properties ensure that data will never be in a half-finished state. It ensures changes in data always happen in a consistent way.
• Isolation-This ensure that all transactions run independently and without intervening one another and till the time each transaction is finished in its own way.
• Durability- This property is very critical as it ensures that even in case of any failover, there is enough mechanism to recover data in the system.

Both are a set of SQL STATEMENTS which are encapsulated inside the block to complete some task. But they are very different in so many ways.

• STORED Procedure- The set of SQL statements in Stored procedure is pre-compiled objects which get compiled for the very FIRST time only and afterward it gets saved and reused.
• Functions- These are executed and compiled every time it gets called. It is mandatory for the function to always return some value also it cannot do DML operations on data.

Let me list down some major differences for both :

1. It is mandatory in function to return value while it is optional in a stored procedure.
2. Function only support input parameters while stored procedures can have both input/output parameter.
3. A function can be called from a stored procedure but not vice versa.
4. The procedure allows SELECT as well DML operations as well but function allows only to SELECT.
5. There are RESTRICTIONS of using stored procedure in the WHERE/HAVING/SELECT section while NOTHING for function. We use function frequently in WHERE/HAVING/SELECT section.
6. The transaction is possible in stored procedures but not in function.
7. An exception can be taken care of in the stored procedure by using the TRY CATCH block but not in function.

One of the foremost common issues that occur when running parallel transactions is the Dirty read problem. A dirty read happens once when the transaction is permissible to read the INFORMATION that's being changed by ANOTHER one that is running at the same time HOWEVER which has not nevertheless committed itself.

If the transaction that modifies the information and does the commits itself, the dirty read problem never occurs. but if the transaction, that has triggered the changes in the information, is rolled back when the opposite transaction has read the data, the latter transaction has dirty information that doesn’t truly exist.

Let us try to understand the scenario when a user tries to buy a product. The transaction which does the ACQUISITION task for the user. the primary step within the transaction would be to update the Items in Stock.

Before the transaction, there are 12 items in the stock; the transaction can update this to 11 items. The transaction concurrently communicates with a third party payment gateway. If at this time in time, another transaction, let’s say Transaction 2, reads Items In Stock for laptops, it'll read 11. However, if after, the user who has triggered the FIRST transaction A, seems to possess light funds in his account, transaction A is rolled back and therefore  ItemsInStock column can revert to again 12. However, transaction B has 11 items because it read old data from ItemsInStock column. This is often dirty information and therefore the drawback is termed a dirty scan problem.

CTE is virtually created temporary storage which holds query output created by SQL SYNTAX SELECT, INSERT, UPDATE, DELETE OR CREATE VIEW STATEMENT. It holds the data until the time of the query session. It is similar to the derived table only. We can also  use the CTE in case of VIEW in some scenarios:

• expression_name: Is a valid identifier for the common table expression. It must be different from the others defined within the same WITH clause, but it can be the same as the name of a base table or view. Any REFERENCE to it in the query uses the common table expression and not the base object
• column_name: Specifies a unique column name in the common table expression. The number of column names specified must match the number of columns in CTE _query_definition

If the query definition supplies distinct names for all columns then the column names are optional

• CTE_query_definition: This is a SELECT statement whose result set populates the common table expression. This must meet the same requirements as for creating a view except that a CTE cannot define another CTE

Let me LIST down some benefits of using CTE :

• When we have a recursive query we can use CTE. It holds a query output in an area that is defined during CTE definition. It is more beneficial when we need to work on the queries query. It holds query data till the time the SQL query is running. It is beneficial for holding filter data which is needed for subsequent processing.
• It improves the readability of queries without affecting performance.
• It can be referenced multiple times in the SQL query.
• It can be used instead of a view where metadata information does not require to be stored.

There are two types of CTE :

• Recursive CTE: This type of reference itself within CTE. This is useful while working with hierarchical data SINCE it executes until the time the whole hierarchy gets returned. A recursive query must contain two SQL query statements joined by UNION ALL, UNION, INTERSECT, or EXCEPT operator. In the below example we have Employee table having hierarchical data (Employee and Manager relationship)

• Non-Recursive CTE: This type does not reference itself. This is much simpler than other types of CTE.

Self-join is the one in which the same table is joined by itself to GET the desired output. We can understand self join in case of the below SCENARIO :

Let's assume we have  below table structure for Employee :

1. EmpId- The key identifier for Employee
2. MgrID-The key identifier for MANAGER.which is mapped to EmpID
3. empname-The Employee name

If we need to extract employee and manager information from the above table then the only option we have is to using self join. We can make a self join on the Employee table on JOINING ManagerID with EmployeeID. Please find below query which explains self join clearly :

ALTHOUGH we can have other alternatives as well to get the same desired result set. It uses left outer Join:

There are several scenarios in which we use temp tables. In case we have no DDL or DML access to a table. You can utilize your current read access to maneuver the information into a SQL Server temp table and make modifications from that point. Or on the other hand, you don't have the authorization to make a table in the current database, you can make a SQL Server temp table that you can control. At long last, you may be in a circumstance where you need the information to be available just in the present session.

The temp tables have "HASHTAG" followed by the table name. For instance: #Table_name. SQL temp tables are made in the tempdb database. A local SQL Server temp table is just available to the present session. It can't be accessed or utilized by procedures or queries outside of the session. A standout frequently utilized situation for SQL Server temp tables is when we use a loop in the query. For instance, you need to process information for a SQL query and we need a spot to store information for our loop to peruse. It gives a speedy and productive WAY to do as such.  Another motivation to utilize SQL Server temp tables is when we have to execute some expensive processing. Suppose that we make a join, and each time we have to pull records from that result-set then it NEEDS to process this join again and again. Why not simply process this outcome set once and toss the records into a SQL temp table? At that point, you can have the remainder of the SQL query allude to the SQL temp table name. In addition to the fact that this saves costly processing, it might even make our code look a little cleaner.

The SQL temp table is dropped or demolished once the session drops. Most of the time we will see: DROP #tbl command but it is not mandatory. Temp tables are ALWAYS created in the tempdb database. It resolves name conflict by adding a suffix in case of a similar name for temp tables.

Global SQL temp tables are helpful when we need to reference tables in all sessions. Anybody can add, modify, or delete records from the table. Additionally, note that anybody can DROP the table. Like Local SQL Server temp tables, they are dropped once the session drops and there are never again any more references to the table. We can generally utilize the "DROP" command to tidy it up manually.

Table variables are more like some other variables. It is a common understanding that table variables exist just in memory, yet that is basically not correct. They live in the tempdb database just like local SQL Server temp tables. Additionally like local SQL temp tables, table variables are accessible just inside the session in which they are declared.  The table variable is just available inside the present batch.

If PERFORMANCE is criterion then table variables are helpful with modest quantities of data. Generally, a SQL Server temp table is helpful when filtering through a lot of information. So for most times, we will in all probability observe the utilization of a SQL Server temp table rather than a table variable.

SQL server favors all three relationships and is well supported by SQL server design. Let me explain to you each relationship one by one:

One to One – This type of relationship is supported by a single table and in some cases, we can have two tables as well. As the NAME suggests, we can have only a single record from each entity, primary and secondary. A person can have only one passport, he can not have more than one. In below example we have two tables Person and Passport having one to one relationship by leveraging foreign key and forcing unique key constraint on the foreign key. In this case, person ID which is the primary key in one table works as the foreign key.

One to Many – This type of relations is ALWAYS supported by two tables. This relationship focuses on at least one entry in the secondary table for each entry in the primary table. But in the primary table, we will always have a single entry for each record against each record of a secondary table. Let me try to understand you with below examples where we have two table book and AUTHOR. A book can have more than one writer so there will always be more than one entry of book in author table but there will always be a single entry of author in the author table. This type of relationship is supported by a primary key-foreign key relationship. Here Book Id is working as a foreign key in the Author table to support one to many.

Many to Many – This type of relationship is realized by more than two tables where one table works as Join table between the FIRST two tables. In the below example we have Students, Enrollments, and Classes as three tables where Enrollment table is working as a bridge between Students and classes. One student can enroll in multiple classes and one class can be MAPPED to multiple students.

The magic tables are an integral PART of Triggers. They facilitate Triggers and make the job easier.  There are two magic tables that exist, one is inserted one and the other one is called deleted one. These are built by SQL server itself to hold the data while PROCESSING gets triggered. They hold the inserted, deleted and UPDATED records while doing DML operations on a table. If we have triggers for INSERT operation then the newly inserted values are stored in the INSERTED magic table. If triggers get fired for UPDATE /DELETE operation then updated and deleted values of records are stored in DELETED magic tables.

• INSERTED Magic table

Let me explain the INSERTED magic table by looking at the below example. In the below screenshot we have triggers for INSERT operation and we can see two tables INSERTED/DELETED which we can use in the trigger for performing any manipulation.

• DELETED Magic table

The below example illustrates the use case of a DELETED magic table which COMES into the picture when we have UPDATE/DELETE trigger. This table holds the old record which was either updated/deleted.

The indexing is required to quicken search results in the database. If we compare INDEX in our real world then page number of books and keywords mostly on the back side of book work similar as Index. We can quickly go to respective pages if we know the page number and also if we have an IDEA of keywords, we are looking into the book then just visiting keywords section will make our job easier as keywords are linked with page numbers. There are two types of indexes that exist in SQL database. One is called clustered while other is called non-clustered. The page number of the book is similar to the clustered index while the keyword section of the book represents non-clustered indexes. They exist in the database as a B-Tree structure. Let's go into the details of each index.

• Clustered Index 

We can have only one clustered index per table. Once we create a primary key, by default it ends up CREATING a clustered index on that key. We can also specify the index type as well in case we WOULD like to have non clustered index as well. The table is called heap if it does not have any clustered index. As we said earlier that indexes are B-Tree structures. The leaf node is mapped to data pages of the table in case of a clustered index. The data is physically sorted based on the clustered index. This is the reason why it is quickest in two of the indexes.

• Non-clustered Index 

Earlier it was possible to have only 249 non clustered indexes on the table but after SQL server 2008 we can have 999 non clustered indexes. It gets created by default when we create UNIQUE key constraint. We can also decide index type as well while creating Unique constraint. It is not mandatory to have any non clustered index. The leaf nodes of non clustered index map to index pages having details of clustering key or RID to get the actual row. In case the table does not have any clustered index, leaf nodes are mapped to the physical location of the row which is called as RID and if present leaf nodes mapped to clustering key. It is much quicker for DML operations like adding /deleting and updating records.

Normalization and DENORMALIZATION are the strategies utilized in databases. The terms are differential where Normalization is a PROCEDURE of limiting the addition, removal and update peculiarities through DISPOSING of the redundant information. Then again, Denormalization is the reverse procedure of Normalization where the repetition is added to the information to improve the EXHIBITION of the PARTICULAR application and information integrity. Normalization prevents the disk space wastage by limiting or disposing of the redundancy.

• Master: It contains framework catalogs that keep data about disk space, record portions, use, system-wide setup settings, LOGIN accounts, the presence of other databases, and the presence of other SQL SERVERS (for appropriate activities). If this database does not exist or corrupted then the SQL Server instance cannot start. Although we user objects in the master database, it is not advised to do so. This database should always remain as static as possible. In the case of a master database being rebuilt, all user objects will be lost.
• Model: It is basically a template database. Each time you make another database, SQL Server makes a duplicate of a model to frame the premise of the NEW database. Any changes made to this database, related DB size, collation, recovery model, and any other configurations, are applied to any new database created afterward.
• Tempdb: Temporary database, tempdb, is a workspace. SQL Server tempdb database is ONE of a kind among every single other database since it is reproduced not recuperated each time SQL Server is started.
• Msdb: This database is utilized by the SQL Server Agent Service, which performs planned exercises, for example, backup and replication assignments.

• Inner Join- This is the simplest one and most widely used JOIN. This is default one if we do not specify any JOIN between tables. It returns all matching records from both tables where the matching condition is satisfied.
• LEFT JOIN- We call this LEFT OUTER JOIN as well. When we have situations LIKE we want all columns from one table and only matching records from another table, we go for this type of JOIN. There are two types of same. The LEFT one is the first type where we would like to have all records from LEFT table and only matching records from RIGHT one then we go for this type of JOIN. When we do not have any matching condition in the right table then all columns from the right one will be NULL while LEFT table will have all the records.
• RIGHT JOIN- We call this RIGHT OUTER JOIN as well. This is just the reverse of what we discussed for LEFT JOIN. The result set will be having all records from the right table but only matching records from the left one. Even if the ON CLAUSE which gives matching record fails, it will ensure rows are returned from the right table and corresponding columns from the LEFT table with NULL values.
• FULL JOIN- It is also CALLED FULL OUTER JOIN. It is having characteristics of both LEFT /RIGHT outer join. The result set will be having rows whenever we have the MATCH in any of the tables either LEFT or RIGHT one. We can also say that it gives the same result if we apply UNION on LEFT and RIGHT OUTER JOIN resultset.
• CROSS JOIN- This is a cartesian product of two tables where each ROW from the primary table is joined with each and every row of the secondary table. Even if we use SELECT statement on two tables separated by a comma and without any WHERE condition, it will give the same result as we get from applying CROSS JOIN.

UNION blends the contents of two structurally-compatible TABLES into a solitary joined table. The distinction among UNION and UNION ALL is that UNION will discard duplicate records through UNION ALL will incorporate duplicate records. Note that the presentation of UNION ALL will commonly be superior to UNION  since UNION requires the server to do the extra WORK of expelling any duplicate. In this way, in SITUATIONS where there is a SURETY that there won't be any copies, or where having copies isn't an issue, utilization of UNION ALL eventual suggested for performance reasons. Let's have a look at the below examples explaining the USAGE of both. In the first, we have used UNION and in the second we have explained UNION ALL.