Home
MS SQL
Interview FAQs

FAQs of level

First the database recovery model is responsible for the retention of the transaction log entries.  So the setting determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution. Here are the SQL Server database recovery models:

Simple - Committed transactions are removed from the log when the check point process occurs.
Bulk Logged - Committed transactions are only removed when the transaction log backup process occurs.
Full - Committed transactions are only removed when the transaction log backup process occurs.

A trigger is a SQL procedure or SQLCLR Code that initiates an action when an event (like INSERT, DELETE or UPDATE) occurs on an object. Based on events which take place in your database, you can have SQL Server "listen" for just the ones that should signal when it is time for actions to run automatically.

Triggers are stored in and managed by the DBMS. Triggers can be used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed directly; DBMS automatically fires the trigger as a result of a data modification to the associated table or in the case of DDL triggers to a DDL event in the database. Triggers are similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-driven and are not attached to a specific table as most triggers are. Stored procedures are explicitly executed by invoking a call to the procedure while triggers are implicitly executed by events. In addition, triggers can also execute stored procedures.

Nested trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself; so when the trigger is fired because Execution plan retention, reuse and improved security from SQL injection are some of the advantages of using SPs. A trigger is called a nested trigger when it is fired off from another trigger.

SQL Server contains special triggers like login triggers and DDL triggers.

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users can see the index name but cannot see the indices themselves; they are just used to speed up queries.
Effective indices are one of the best ways to improve performance of a database application.An Index can give you improved query performance because a seek action occurs for retrieving records from your table in a query. A seek means you were able to locate record(s) without having to examine every row to locate those record(s).
A table scan occurs when there is no index available or when a poorly created index exists on the table for a query running against that table. In a table scan, SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a significant impact on performance.

A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. With a linked server, you can create very clean, easy–to-follow SQL statements that allow remote data to be retrieved, joined, and combined with local data. The ability to issue distributed queries and perform commands with transactions on heterogeneous sources is one of the benefits of using linked servers.

A cursor is a database object used by applications in the procedural logic to manipulate data in a row-by-row basis, instead of the typical SQL commands that operate on all or parts of rows as sets of data. In order to work with a cursor, we need to perform these steps in the following order:

  • Declare a cursor
  • Open the cursor
  • Fetch a row from the cursor
  • Process the fetched row
  • Close cursor
  • Deallocate the cursor

UDFs can be used in SQL statements anywhere in the WHERE/HAVING/SELECT section, whereas stored procedures cannot. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDFs can be thought of as views that take parameters and can be used in JOINs and other rowset operations.

A foreign key is a way to enforce referential integrity within your SQL Server database. A foreign key means that values in one table must also appear in another table.The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.
A foreign key can be created using either a CREATE TABLE statement or an ALTER TABLE statement.

Case sensitivity - A and a, B and b, etc.
Accent sensitivity - a and á, o and ó, etc.
Kana Sensitivity - When Japanese Kana, Hiragana, and Katakana characters are treated differently, it is called Kana sensitive.
Width sensitivity – When a single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently, it is width sensitive.

In OLTP (Online Transaction Processing systems), relational database design uses the discipline of data modeling and generally follows the Codd rules of data normalization in order to ensure absolute data integrity. Using these rules, complex information is broken down into its most simple structure (a table) where all of the individual atomic level elements relate to each other and satisfy the normalization rules.

Optimistic locking is a strategy where you read a record, take note of a version number and check that the version has not changed before you write the record back. If the record is changed (i.e. a different version to yours), then you abort the transaction and the user can re-start the transaction with the new data and update appropriately.
Pessimistic locking is when you lock the record for your exclusive use until you have finished with it. There are 4 levels of locking in the pessimistic isolation levels from lowest to highest. They are: read uncommitted, read committed, repeatable read, and serializable. At the serializable level (the highest locking and isolation level) it has much better integrity than optimistic locking but requires you to be careful with your application design to avoid deadlocks.

PreviousDisplaying 14 of 15Next
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*