Index tuning & database partitioning

What is “Index Tuning Wizard”?

Index Tuning Wizard is a software application that identifies tables which have inefficient indexes. It makes recommendations on how indexes should be built on a database to optimize performance. The recommendations are based on T-SQL commands that the wizard analyzes.

List out what Index Tuning Wizard can do.

• It identifies tables in need of an index change
• Implements recommendations
• Determines how a proposed change might affect performance
• Has the capability to make indexes change immediately and schedule them
• Helps to tune indexes.

Explain how partitioning an important part of database optimization is.

Partitioning distributes database tables over different database which may reside on a different server. Vertical portioning helps to fit more rows on a database page to improve data access performance. Partitioning helps to do multiple tasks simultaneously. It increases performance. It helps to manage data.

SQL Server distributed queries

SQL Server distributed queries -

Answer
Distributed queries access data from multiple heterogeneous sources. These data sources may or may not be stored on the same computer. Distributed queries are most useful when there are multiple databases of varied nature. Hence, distributed queries can be executed over these databases in a secure manner.

Describe how Linked server is used to excess external data.

Answer
A linked server can be considered as another SQL server database running elsewhere. It can be a OLEDB or ODBC data source. They help in addressing distributed queries.

OPENQUERY function is used for such purposes. This function I is used to execute the specified query on the linked sever. OPENQUERY can be referenced in from the FROM clause just like a table name.

Example:
SELECT * FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')

Describe how OPENQUERY function is used to excess external data.

Answer
OPENQUERY is used to execute the specified query on the linked sever.

OPENQUERY can be referenced in from the FROM clause just like a table name.

Syntax:

OPENQUERY ( linked_server ,'query' )

OPENQUERY does not accept variables for its arguments.

Example:
SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name, id FROM joe.titles')

Describe how OPENROWSET and OPENDATASOURCE function is used to access external data.

OPENROWSET: Includes all connection information that is required to access remote data from an OLE DB data source. It can be references from a FROM clause and is used to access the tables on a linked server. It can also be referenced as a target table of INSERT, DELETE, and UPDATE statements.

Syntax:
OPENROWSET
( { 'provider_name' , { 'datasource' ; 'user_id' ; 'password'
| 'provider_string' }
, { [ catalog. ] [ schema. ] object
| 'query'
}
| BULK 'data_file' ,
{ FORMATFILE = 'format_file_path' [ ]
| SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
} )

Example
SELECT * FROM OPENROWSET('SQLNCLI',Server=Brighton1;Trusted_Connection=yes;', 'SELECT sal, employee_name FROM Company.employee.ID')

OPENDATASOURCE: Provides ad hoc connection information without a linked server name.

Syntax:
OPENDATASOURCE ( provider_name, init_string )

Example: The example cretes a ad hoc connection to the payroll instance of a SQL server in US. It queries the Company.HumanResources.Employee table.

SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=US\Payroll;Integrated Security=SSPI') . Company.HumanResources.Employee

SQL Server Distributed Queries

Distributed queries can access data from different data sources.
These sources can reside on the same server or a different server.
Theycan querymultiple databases. .

SQL Server method to insert data interview questions

Brief about Insert Statement with an example.

Answer
INSERT: The Insert statement is used to insert values as rows in a table.

Syntax:
INSERT INTO Table_name values (value1, value2,…);
INSERT INTO Table_name (column1, colum2…) values (value1, value2,…);

Example
A table customer has fields customer id, customer name, customer salary.
INSERT INTO customer (1,John,20000);

Brief about Select….into statement with an example.

Answer
Select into is used to create back up copies of tables. It selects data from one table and inserts into another.

Syntax:
Select * INTO dest_table_name FROM source_table_name

Example: Select data from customer table into customer_backup
Select * INTO customer_backup FROM customer;

It can also be used with WHERE clause for a condition.
Select * INTO customer_backup FROM customer WHERE cust_salary > 1000;

Brief about Bulk copy with an example.

Answer
Bulk copy utility of SQL allows data to be copied from one data file to another. The data is first exported from the source data file and then imported into the SQL server using the bcp command. It can also be used to transfer data from a table to a file.

Describe how bcp command prompt utility is used to import and export data.

Answer
The bcp utility is accessed from the command prompt.

Syntax:
bcp {dbtable | query} {in | out | queryout | format} datafile [-n native type] [-c character type] [-S server name] [-U username] [-P password] [-T trusted connection]

It starts with specifying the database or object name. In and out means whether the data needs to be copied into or out of the database. Hence in will be used for importing data while out will be used to export data. Remaining arguments are the basic command line arguments.

Example1: import data of Company database to the employee table
bcp Company.dbo.employee in c:\temp\employee.bcp -c -Sstevenw -Usa –P

Example2: Export data of Company database from the employee table
bcp Company.dbo.employee out c:\temp\employee.bcp

Describe how bulk insert statement is used to import data.

Answer
Bulk Insert is used to copy data from a file into a table or view in a format as specified by the user.

Syntax:
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE =
{ 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] FIRSTROW = first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ]
[ [ , ] MAXERRORS = max_errors ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
[ [ , ] TABLOCK ]
[ [ , ] ERRORFILE = 'file_name' ]
)]

Example:
BULK INSERT target.txt FROM ‘C:\Dest.txt’ WITH ( FIELDTERMINATOR = ‘ ” , ” ‘)
The FIELDTERMINATOR argument allows you to specify the delimiter used to discern column values.

SQL Server cube operator interview questions

Explain how to use Cube operator to summarize data.

Answer
A Cube operator summarizes data of group by. It helps in determining subtotals and grand totals.

Example: This will display a summary of employees with same first name.

Select first_name, last_name, AVG(salary) FROM employee GROUP BY first_name WITH CUBE

SQL Server Data manipulation language

Define Data manipulation language.

Answer
Data manipulation language is used for managing data. Managing data would mean retrieving, inserting, deleting or updating data. SQL is the most common data manipulation language. SELECT, INSERT, UPDATE keywords are a part of Data manipulation language. Data manipulation language can be procedural or declarative.

Explain the command Select, Insert, Update and Delete with syntax.

Answer
a. Select: Select statement is used to select data from a table.

Syntax:
Select column_name
From table_name
For selecting all columns
Select * from table_name

Example:
Select * from customer;

b. Insert: Insert statement is used to insert data (row) in a table.

Syntax:
Insert into table_name
Values (value1, 2 ..)

Example:
Insert into customer values (1,’steve’,’james’);

c. Update: Update statement is used to update existing data (row) in a table. It is used to update some column value by specifying a condition.

Syntax:
Update table_name Set column_name1= ‘value’, column_name2=’value2’ Where column_name3=’value3’

Example:
Update customer Set first_name=’john’ Where last_name=’james’;

SQL Server Accessing data interview questions

SQL Server data integrity

Explain the full syntax of Select Statement with examples.

Answer
SELECT is used to select a specific or ALL columns / rows from a table.

Syntax:
SELECT list_columns FROM table_name

List_columns are the columns to be selected to retrieve the rows. While table_name is the table from which these columns needs to be selected. Many clauses can be added to this SELECT statement.

Example 1: Displays rows with customers balance between 10 and 10000
SELECT first_name FROM Customer WHERE cust_bal BETEEN 10 and 10000;

Example 2: Sort the names in an ascending order
SELECT first_name FROM Customer ORDER BY first_name;

Explain some of the keywords of Select Statement like Distinct Keyword, Top n Keyword with an example for each of them.

Answer
DISTINCT: Distinct clause helps to remove duplicates and returns a result set.

Syntax: DISTINCT (set_expression)

Example:

Select DISTINCT company_name FROM Company;

TOP N: returns the top “n” records from a table.

Syntax : TOP N

Example:
Select TOP 10 Cust_name from Customer;

Describe the use of Into and From clause with examples.

Answer
INTO: The INTO keyword inserts data from one table into another. It is commonly used in SELECT statement. It is useful in creating back ups.

SYNTAX:
SELECT * INTO new_table FROM old_table

Example: Copy all columns from table customer into customer_backup
SELECT * INTO Customer_backup FROM customer

FROM: the FROM clause selects rows from the table specified.

Syntax:
SELECT * FROM table_name

Example: Select all columns from a table customer

SELECT * FROM Customer

Describe where, group by and having clause with examples for each of them.

Answer
WHERE: Where clause in a SELECT query is used to retrieve specific records that meet the specified criteria.

SYNTAX:
SELECT column_name FROM Table_name WHERE predicate

Example: to display customers with first name is john
Select first_name From customer Where first_name =’john’;

GROUP BY: Group By clause in select statement is used to group or collect data of multiple records of one ore more columns.

SYNTAX: SELECT column_name FROM table_name WHERE predicate GROUP BY column1, column2..;

Example: To Group records by city SELECT city_name FROM Customer GROUP BY city;

HAVING: The HAVING clause is used to filter records that have been grouped using GROUP BY clause. Hence HAVING clause is used with GROUP BY.

Syntax: SELECT column_name FROM table_name WHERE predicates GROUP BY column1,.. HAVING condition1…

Example: To Group records by employee with salary > 10000 SELECT emp_name FROM employee GROUP BY emp_name HAVING salary > 1000;

SQL Server data integrity -

What are ways to ensure integrity of data?

Answer
Data integrity ensures quality of data. It helps keep the data unchanged and unique.

• Data types ensure that the data accepted by the column is restricted to the type specified. For e.g. an integer data type cannot accept strings.
• NOT NULL definition ensures that the column does not accept a NULL value. This means that the data cannot be empty for this column.
• Default definition means that the value is set to a value by default unless specified. For e.g. ‘0’ is used for integer data types.
• Identity property is used to define a column as an identifier. This column contains system generated values that are sequential. They help to uniquely identify the row.
• A constraint helps invalid data to be entered. For e.g. a FOREIGN KEY. CHECK can be used to ensure the column accepts the specified data.
• Rules are similar to CHECK, but used for backward compatibility. They help to restrict acceptance of specified data.
• A trigger is a set of code which gets executed on an INSERT, UPDATE or DELETE on some table. Triggers can be used to enforce business rules automatically when data is modified.
• Constants ensure the data value does not change and thus maintaining data integrity.
• An index ensures that when rows are requested from an indexed table, the database searches the index to find a particular value and then follows the pointer to the row containing that value.

Explain the types of Data integrity.

Answer
a. Entity Integrity: It means that the row is kept as a unique entity. It ensures that the integrity of the identifier is maintained through PRIMARY KEYS, IDENTITY property etc

b. Domain Integrity: It validates the entries for a given column. Restricted data types can be given, FOREIGN KEY, CHECK and NOT NULL constraints can be specified.

c. Referential Integrity: it helps in maintaining the relationships between tables when a new row is inserted or deleted. Usually Foreign leys are used to define relationships between tables.

d. User-Defined Integrity: as the name suggests, user defined integrity is used to define business rules by the user. The rules are such that they don’t fall under the category of any type of integrity type. Procedures and triggers are examples of such types of user – defined integrity.

What are integrity constraints? Explain their types. i.e. column and table constraints.

Answer
Integrity constraints: Integrity constraints ensure data consistency. This is achieved by making sure that changes made to the database by authorized users keep the data consistent. These constraints allow data to be inserted /modified only if it meets specified criteria.

Column Level Constraints: These constraints are applied at the column level. NOT NULL constraint is an example of this constraint. This constraint means that the column specified as NOT NULL cannot accept an empty string as a value. E.g. Column_name NOT NULL

Table Level Constraints: These constraints are specified independently on more than TWO column of a table. If the need is such that TWO or more than TWO columns need to have a constraint; they fall under the table level constraints. For e.g a table can have more than two or more columns in the primary key.

Explain the classes of constraints. i.e. Primary key, Unique, Foreign Key and Check Constraints.

Answer
Primary Key: This constraint when applied to a column ensures that the row is unique. This means that only ONE row with this ID can exist. For e.g. a table SALES has its primary key as sales_id. The column is specified as primary key by :sales_id PRIMARY KEY

Unique: This constraint makes sure that the columns have unique values. This also means that no two rows can have the same value for the column specified as unique. They differ from primary keys by allowing NULL as one of the values. The keyword UNIQUE is used.

Foreign Key: This constraint helps to establish relationships between tables. When one key is being referenced in another table, the key is called as a foreign key. For e.g. Sales_id from SALES table can be referenced in ORDER table. E.g in table ORDER sales_id is written as: FOREIGN KEY REFERENCES SALES (sales_id)

Check constraints: This constraint restricts the value that can be inserted In a column. It specifies a BOOLEAN value. Values that can be accepted and meet the criteria are evaluated as TRUE while the ones that are rejected are evaluated as FALSE.

e.g. CONSTRAINT amt CHECK (amt BETWEEN 10 and 1000)

SQL Server Identifier

What is SQL Server Identifier?

Answer
Everything in a SQL server can have an identifier. Be it server, view, trigger, column etc. any database object name is an identifier. Identifiers may or may not be required for all objects.

Example:
CREATE TABLE table_name
(ID INT PRIMARY KEY
Note varchar(20));

Here, ID and Note are two identifiers for the columns. Constraints like Primary key need not have an identifier.

What are classes of identifier? Explain each class with an example i.e. regular and delimited identifier.

Answer
There are two classes of identifier. These identifiers must contain 1 to 128 characters.

Regular identifiers: they follow the rules for the format of identifiers. They are not delimited when used in transact SQL statements.

Example:
Select * from table_name Where ID =110

Delimited identifiers: they follow the rules for the format of identifiers. They may or may not be delimited when used in transact SQL statements. They are enclosed either in (“) or ([])

Example:
Select * from [table_name] Where [ID] =110

What are the Rules for Regular Identifiers?

Answer
Rules for Regular Identifiers:

These rules are dependant on database compatibility level. For a compatibility level 90, following rules may apply:

a. The first character must be of the following:

• Letters as defined by Unicode standard 2.0 including latin and other characters from other languages.
• Underscore (_), Hash (#) or at (@) symbol with each having a significant meaning.

b. Remaining characters can be letters (Unicode 2.0), decimal numbers, basic latin or other scripts, #, $,@ symbols.

c. Identifiers must not be keywords reserved by SQL.

d. Embedded spaces or special characters are not allowed.

SQL Server Server Data control

Define Data control language.

Answer
A data control language is a computer language used to control access data in a database. It is used to manipulate and control the data. Examples of commands that are a part of Data control language: SELECT, CONNECT, INSERT, UPDATE etc

Explain the command Grant, Revoke and Deny with syntax.

Answer
a. Grant: Grant is used to grant or give access permission to a user. These permissions can be given to a user or a role. Grant can be used to give system privileges or object privileges.

Syntax:
Grant ALL | system_privelege on object_name to user_name

Example:
Grant update on customer to steve

b. Revoke: Removes or takes away a previously assigned or granted privilege.

Syntax:
Revoke ALL | privilege_name
On object_name
From user_name

Example:
Revoke update
On customer
From steve

c. Deny: Used to deny permissions to a user.

Syntax:
Deny ALL | permission_name
On object_name
TO user_name

Example:
Deny update
On customer
To steve

SQL Server Data definition language

Define Data definition language.

Answer
A Data Definition language is a computer language. It defines how can data be stored efficiently. All the commands used to create, delete databases like CREATE or DELETE are a part of data definition language.

Explain the command Create Table, Alter Table and Drop Table with syntax.

Answer
Create table: Used to create a table in sql. Tables store the data. Each row in a table has a unique identifier called as a primary key.

Syntax:
CREATE TABLE table_name (
Column_name1 data_type,
Column_name2 data_type,
PRIMARY KEY (Column_name1));

Example:
CREATE TABLE customer (
Id Integer(10)
First_name Varchar(200)
Last name varchar(200)
PRIMARY KEY(id));

SQL Server views

Define Views.

• View can be created to retrieve data from one or more tables.
• Query used to create view can include other views of the database.
• We can also access remote data using distributed query in a view.

What is Indexed view? How to create it?

In an indexed view, the data is already computed and stored. Data can be accessed by a unique index. This index is a clustered index. In order to create an index the syntax is

CREATE [UNIQUE], [CLUSTERED | NONCLUSTERED] INDEX index_name
ON {view}
[WITH ]
[ON filegrp]

What are partitioned views and distributed partitioned views?

Partitioned views allow data in a large table to be split into smaller tables. These small tables are called as member tables. The split is done based on range of data values in one of the columns.

In a distributed portioned view, each member table is on a separate member server. This means that the member tables are distributed. To locate these tables easily, the database name on each server should be same.

What functions can a view be used to performed?

Functions of View:-

• Subset data of a table
• Can join multiple tables values into one
• They can act as aggregated tables. i.e. a view can be used to store Sum, average of values
• Views can be nested and can be used for abstraction

Define views.

Answer
A view can be considered as a virtual table. It does not physically exist. It is based on the result set of a SQL statement. A view contains rows and tables just like a real table.

Describe the functionalities that views support.

Answer

• Views can subset data in a table
• They can join multiple tables into one virtual table
• Views can provide security and decrease complexity
• They save space because only their definition is stored.
• They can also be used to create abstraction
• Materialized views are commonly used in data warehousing. They represent a snapshot of the data from remote sources.

Views can create other calculated fields based on values in the real underlying tables

Explain Indexed views and partitioned view with their syntax.

Answer
Indexed view:

An index view has a unique clustered index created on it. They exist as rows on the disk. Because they are saved on the disk, the response time to a query is fast at the cost of space consumption. They are more commonly used in scenarios when data modification is less.

Syntax:
Create Index CREATE [UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table_name

The view is created using the CREATE VIEW synatx

Partitioned view:

Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A portioned view can either be local or distributed. A local portioned view resides on the same instance of the SQL server while the distributed may reside on a different server.

Syntax:

The view is then created by UNIONing all the tables and an updateable partitioned View results

Server 1 :
CREATE TABLE Customer1 (CustomerID INTEGER PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999), ... -- Additional column definitions)

Similar tables created for Server 2 and 3

Partitioned view for server 1
CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3

What are the restrictions that views have to follow?

Answer

• Since a view is a virtual table – columns of the view cannot be renamed. To change anything in the view, the view must be dropped and create again.
• The select statement on the view cannot contain ORDER BY or INTO TEMP
• When a table or view is dropped, any views in the same database are also dropped.
• It is not possible to create an index on a view

It is not possible to use DELETE to update a view that is defined as a join

SQL Server triggers

Define Triggers.

A trigger is a special type of event driven stored procedure. It gets initiated when Insert, Delete or Update event occurs. It can be used to maintain referential integrity. A trigger can call stored procedure.
Executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
You can specify which trigger fires first or fires last using sp_settriggerorder.
Triggers can't be invoked on demand.
They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens.
Triggers are generally used to implement business rules, auditing.
Triggers can also be used to extend the referential integrity checks

SQL Server triggers

What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand?

Triggers are constructs in PL/SQL that need to be just created and associated with a table. Once they are created, when the table associated with it gets updated due to an UPDATE, INSERT or a DELETE, the triggers get implicitly fired depending upon the instructions passed to them.

A table can have up to 12 triggers defined on it.

Triggers can't be invoked on demand. They get triggered when the associated INSERT, DELETE or UPDATE is performed.

Triggers -

Describe triggers features and limitations.

Trigger features:-

1. Can execute a batch of SQL code for an insert, update or delete command is executed
2. Business rules can be enforced on modification of data

Trigger Limitations:-
1. Does not accept arguments or parameters
2. Cannot perform commit or rollback
3. Can cause table errors if poorly written

Syntax for viewing, dropping and disabling triggers

View trigger:

A trigger can be viewed by using sp_helptrigger syntax. This returns all the triggers defined in a table.

Sp_helptrigger table_name

Drop a trigger
Syntax: DROP TRIGGER Trigger_name

Disable a trigger:-
Syntax: DISABLE TRIGGER [schema name] trigger name
ON [object, database or ALL server ]

Determine how to use the inserted and deleted pseudo tables.

Inserted and deleted pseudo tables contain the New and Old values of the data that initiating the Trigger. These tables can be used for database maintenance and dynamic modification to data. These tables can be examined by the trigger itself. The tables themselves cannot be altered.

Explain how to apply cascading referential integrity in place of triggers.

Cascading referential integrity constraints are automatic updates and deletes on dependant objects. They define a set of actions that SQL server may need to take. The References clause of the CREATE TABLE and ALTER TABLE statements support ON DELETE and ON UPDATE clauses:

• [ON DELETE {NO ACTION} ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and DELETE is rolled back.
• [ON UPDATE {NO ACTION } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, an error is raised and UPDATE is rolled back.
• [ ON DELETE { CASCADE } ]: If an attempt to delete a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted
• [ ON UPDATE { CASCADE } ]: If an attempt to update a row is made, with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also Updated.

NO ACTION is default.

Explain trigger classes i.e. instead of and after trigger.

Answer
INSTEAD OF: Cause the trigger to fire instead of executing the triggering event or action. It prevents unnecessary changes to be made.

Example: Causes the trigger to fire instead of the update (action)

CREATE TRIGGER Employee_update ON Employee INSTEAD OF UPDATE AS { TRIGGER Definition }

AFTER: execute following the triggering action, such as an insert, update, or delete. These triggers are fired a little late in the process.

Example: Causes the trigger to fire instead of the update (action)

CREATE TRIGGER Employee_update ON Employee AFTER UPDATE AS { TRIGGER Definition }

What are the instances when triggers are appropriate?

Answer

• When security is the top most priority. i.e. to allow unauthorized access
• When backups are essential
• When Maintenance is desired. Triggers can be fired when any error message is logged

Keeping the database consistent.

SQL Server transactions Architecture -

What operations do SQL Server transaction logs support?

Operations supported by transaction Logs:-

Recovery of individual transactions
In case an operation fails, a transaction can be rolled back

Recovery of all Incomplete Transactions When SQL Server Is started
In case the SQL server fails, all transactions that were in a state of execution can be recovered. This operation helps to maintain integrity of the database.

Rolling a Restored Database, File, File group, or Page Forward to the Point of Failure
In case of a hardware or disk failure, if any database file is affected; it can be restored to the point after which the failure occurred.

Supporting Transactional Replication
Transactions can be replicated from the transaction log into the distribution database

Supporting Standby-Server Solutions
A primary server sends the active transaction log of the primary database to one or more destinations. This destination is a standby server.

Explain the purpose of check points in a transaction log.

Checkpoints are used for backup of transactions. When a checkpoint is introduced in a state and the next state is achieved; if something goes wrong in the new state, the transaction can be returned to the checkpoint. This means that checkpoints allow the transactions to be rolled back.

What is write-ahead transaction log?

A write-ahead transaction log on the disk where modifications are written first before the associated log record is written to disk. This is to maintain the ACID properties. Data modifications are not made to the disk directly. They are made to the buffer cache first. Because log records are always written ahead of the associated data pages, the log is called a write-ahead log.

SQL Server transactions

Explain the characteristics of a transaction. i.e. Atomicity, Consistency, Isolation, Durability.

Characteristics of a transaction:-

Atomicity
This characteristic of a transaction means that a transaction is performed completely not performed at all. I.e. all the tasks in a transaction are completed or none are completed. E.g. transferring money from one account to another involves credit from one account and debit to another.

Consistency
This characteristic means that the database should be consistent before and after the transaction. For a successful transaction database can move from one state to another. Both the states should abide by the same rules. For an unsuccessful transaction, if the transaction fails to abide by the rule and leads to an inconsistent state, the transaction should be rolled back.

Isolation
A transaction should be isolated. This means that that no other operation should be allowed to access or see the intermediate state data.

Durability
A transaction on completion must persist. It should withstand system failures and should not be undone.

Explain various types of SQL Server transactions. I.e. Explicit, Autocommit and Implicit Transactions.

Explicit transaction
A transaction which has START and END defined explicitly. The explicit transaction mode lasts until the transaction is over. When the transaction is over, the transaction is reverted back to the mode in which it started. This mode could either be implicit or autocommit.

Autocommit transactions
This is the default mode of transaction. In this mode, if the transaction is completed successfully, it is committed. If the transaction faces any error, it is rolled back.

Implicit transactions
In this mode, a new transaction is started when the current is committed or rolled back. Nothing is done to begin a transaction. One only needs to commit or roll back a transaction. Implicit transactions have a chain of continuous transactions.

What is distributed transactions in SQL Server? When are they used?

Distributed transactions involve two or more databases within a SQL server. The management of such transactions is done by a component called as transaction manager. Distributed transactions must be used when real time updates are required simultaneously on multiple databases.

SQL Server transactions -

Describe the characteristics of transactions i.e. atomicity, consistency, isolation.

Atomicity: This property of a transaction ensures that a transaction either completely or does not happen at all. E.g. transferring money from one account to another.

Consistency: This property ensures the data is consistent before the transaction and left in a consistent state after the transaction. If the transaction violates the rules, it must be rolled back.

Isolation: This property means that the transaction should be isolated. I.e. until the transaction is over other data or operations cannot access the transaction. This is to maintain the performance.

Explain the types of transaction i.e. explicit, autocommit and implicit

Explicit transaction: transactions that have a START and END explicitly written are called as an explicit transaction. They last only for the duration of the transaction. When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started

Auto commit: This is the default management mode. Every SQL statement is either committed or rolled back when complete. If it completes successfully it is committed else it is rolled back. Auto commit is default mode.

Implicit: when the transaction is in implicit mode, a new transaction starts automatically after the current transaction is committed or rolled back. Nothing needs to be done to define the start of the transaction. It generates continues chain of transactions.

What is Distributed transaction?

A distributed transaction is one in which it updates data present on two or more systems. They are useful in updating data that is distributed. They must be robust because they are subjected to failures very often. Failures like client server failure etc. interactions between these computers that are distributed is with the help of transaction managers.

What is nested transaction? Explain with an example.

A nested transaction is one in which a new transaction is started by an instruction that is already inside another transaction. This new transaction is said to be nested. The isolation property of transaction is obeyed here because the changes made by the nested transaction are not seen or interrupted by the host transaction.

BEGIN TRANSACTION trans_1
INSERT INTO TestTrans1 VALUES (1,’mark’)
COMMIT TRANSACTION trans_1;
GO
/* Start a transaction and execute trans_1. */
BEGIN TRANSACTION trans_2;
GO
EXEC trans_1 1, 'aaa'; //execute some procedure
GO
ROLLBACK TRANSACTION trans_2;
GO
EXECUTE TransProc 3,'bbb';
GO

SQL Server table

Define Table.

SQL Server database stores information in a two dimensional objects of rows and columns called table.

Define Local temporary table and global temporary table.

Local temporary table is created by prefixing name with pound sign like (#table_name). Global temporary table is created by prefixing name with Double pound sign like (##table_name). Local temporary table is dropped when the stored procedure completes. Global temporary tables are dropped when session that created the table ends

SQL Server table -

What is Cascade and restrict in Drop table SQL?

RESTRICT indicates that the table should not be dropped if any dependencies exist. If dependencies are found, an error is returned and the table isn't dropped.

CASCADE specifies that the dependencies be removed before the drop is performed

SQL Server table

Define SQL Server Tables. Explain Create Table syntax with an example.

Answer
SQL server tables are used to store data. Each table is divided into rows and columns. Each row has some data against the specified column. Each column has a data type thereby restricting the type of data it can accept.

Syntax:
CREATE TABLE "table_name" ("column 1" "data_type_for_column_1", "column 2" "data_type_for_column_2", ... )

Example:
CREATE TABLE Customer (ID Integer (10), First_name Varchar(20));

Explain how to determine column nullability of a table.

Answer
The column nullability can be determined if:

a. source table column is nullable: If any of the source columns are nullable, the result column is nullable

b. Source table column is not nullable; the column in the new table is defined as not null: If all the source columns in the expression are not nullable, the result column is not nullable.

Explain how to reference a table from another database in the same server.

Answer
A table can be referred from another database on the same server the following way:
Dbname.tablename

Example:
Select * from Dbtest.customer

Explain how to rename a table.

Answer
The ALTER table statement can be used to rename a table.
ALTER TABLE table_name
RENAME TO new_table_name
Example:
ALTER TABLE customer
RENAME TO cust_details;

Explain how to change the owner of a table.

Answer
Owner of a table can be changed by using:
Sp_changeobjectowner [obj_name] ‘object’, [ @newowner = ] 'owner'
Example:
Sp_changeobjectowner ‘customer’, ‘Mark’
Here, customer table is the obj_name while Mark is owner.

Explain autonumbering and identifier column of a table.

Answer
Each table can have a unique identifier that uniquely identifies the row. This identifier can be an auto incremented value generated by system. For e.g. Customer_id Identifier columns can contain values unique within the table on which they are defined. This means that other tables with an identifier column can have the same values. A single, unique identifier column can be created for every table. Such columns are typically used for billing system to avoid duplications.

Explain how to determine if a table has a primary key?

Answer
A primary key is usually identified by the keyword PRIMARY KEY. Any column that has the keyword (while creating the table) as PRIMARY KEY means the table has a primary key. The values of this column are always unique.

What is globally unique identifier?

Answer
A globally unique identifier is sued to provide a unique reference number. This GUID is unique across tables, servers and locations. The GUID values are unique and hence may be large in number. NEWID() can be used to create such unique values.

How can we determine if a column is an identity column?

Answer
Using OBJECTPROPERTY function, one can find if the table has an identity column.
Syntax:
OBJECTPROPERTY(object_id,proeprty)
Here the property will be TableHasIdentity. SQL Server returns 1 if the table has an identity column, 0 if it doesn't.

How can we determine if a column is a primary column?

Answer
Using OBJECTPROPERTY function, one can find if the table has an identity column.
Syntax:
OBJECTPROPERTY(object_id,proeprty)
Here the property will be TableHasPrimaryKey. SQL Server returns 1 if the table has a primary key, 0 if it doesn't.

What are the purposes of table variables?

Answer
a. Table variables can be used to store result set in a SQL serer. This result set can be used for processing later.

b. Table variables can be used in stored procedures, user defined functions.

c. Table variables give a better performance

d. Table variables can be used in a SELECT, INSERT, UPDATE statements.

e. Unlike temporary variables, they don’t require a declaration or cleaning up

What are some of the drawbacks of table variables?

Answer
a. SQL server does not maintain a statistics of table variables. This means that how and where these variables were used cannot be determined because they are washed out as soon as the stored procedure or function is finished execution.

b. Table definition of a table variable cannot be changed after a DECLARE statement. Hence, NO ALTER statement will work.

c. Table variables cannot be used In ROLLBACK of transactions