What are different type sql server backup?

There are three sql server backup method .
1.Full backup.
                           A full backup contains all the data in a specific database or set of file groups or files, and also enough log to allow for recovering that data. It is the base of both differential backup and transaction log backup.
2.Differential backup.
                        A differential backup is not independent and it must be based on the latest full backup of the data. That means there should have a full backup as a base. A differential backup contains only the data that has changed since the differential base. Typically, differential backups are smaller and faster to create than the base of a full backup and also require less disk space to store backup images.Therefore, using differential backups can save available space and speed up the process of making frequent backups to decrease the risk of data loss. At restore time, the full backup is restored first, followed by the most recent differential backup.
3.Transaction Log Backups (Full and Bulk-Logged Recovery Models Only).     
                                The transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up. With transaction log backups, you can recover the database to a specific point in time (for example, prior to entering unwanted data), or to the point of failure.The transaction log backups are only valuable under the full recovery model or bulk-logged recovery model. Each log backup covers the part of the transaction log that was active when the backup was created, and it includes all log records that were not backed up in a previous log backup. An uninterrupted sequence of log backups contains the complete log chain of the database, which is said to be unbroken. Under the full recovery model, and sometimes under the bulk-logged recovery model, an unbroken log chain lets you to restore the database to any point in time.Just like differential backup, transaction log backup is also based on full backup. Therefore, before you can create the first log backup, you must create a full backup, such as a database backup. Because it requires less disk space than full backup, you can create them more frequently than database backups.

SQL Statement Processing in SQL server

A SELECT statement is nonprocedural; it does not state the exact steps that the database server should use to retrieve the requested data. This means that the database server must analyze the statement to determine the most efficient way to extract the requested data. This is referred to as optimizing the SELECT statement. The component that does this is called the query optimizer. The input to the optimizer consists of the query, the database schema (table and index definitions), and the database statistics. The output of the optimizer is a query execution plan, sometimes referred to as a query plan or just a plan. The contents of a query plan are described in more detail later in this topic.

The inputs and outputs of the query optimizer during optimization of a single SELECT statement are illustrated in the following diagram:

A SELECT statement defines only the following:

• The format of the result set. This is specified mostly in the select list. However, other clauses such as ORDER BY and GROUP BY also affect the final form of the result set.
• The tables that contain the source data. This is specified in the FROM clause.
• How the tables are logically related for the purposes of the SELECT statement. This is defined in the join specifications, which may appear in the WHERE clause or in an ON clause following FROM.
• The conditions that the rows in the source tables must satisfy to qualify for the SELECT statement. These are specified in the WHERE and HAVING clauses.

                The process of selecting one execution plan from potentially many possible plans is referred to as optimization. The query optimizer is one of the most important components of a SQL database system. While some overhead is used by the query optimizer to analyze the query and select a plan, this overhead is typically saved several-fold when the query optimizer picks an efficient execution plan. 

              The SQL Server query optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The query optimizer must analyze the possible plans and choose the one with the lowest estimated cost. Some complex SELECT statements have thousands of possible execution plans. In these cases, the query optimizer does not analyze all possible combinations. Instead, it uses complex algorithms to find an execution plan that has a cost reasonably close to the minimum possible cost.

The SQL Server query optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster. The SQL Server optimizer will use a parallel execution plan to return results if the load on the server will not be adversely affected.

SQL Interview Question part 3

What is the difference between primary key and unique key?
1) By default Primary Key will generate Clustured Index whereas Unique Key will generate Non-Clustured Index.

2) Primary Key is a combination of Unique and NOT NULL Constraints so it can’t have duplicate values or any Null whereas SQL Server can have only one NULL.

3) A table can have only one PK but it can have any number of UNIQUE Keys.

4) Primary Key does not allow null values whereas unique constraint allow ‘single’ null value.

What is the difference between truncate table and delete table?
DELETE:  The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

TRUNCATE:  TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

What is the temp table and table variable?
In SQL Server we know 3 kinds of temporary tables: Local Temp Tables, Global Temp Tables and Table Variables.

Local Temp Tables

The local temp table is the most commonly used temp table.

CREATE TABLE #TempTable

(ID INT IDENTITY(1,1) NOT NULL,

Description VARCHAR(10) NULL)

Global Temp Tables

Global temporary tables work just like local temporary tables (stored in TempDB, less locking necessary).

CREATE TABLE ##TempTable

(ID INT IDENTITY(1,1) NOT NULL,

Description VARCHAR(10) NULL)

Table Variable

Table variables are cleared automatically when the procedure, function or query goes out of scope.

What is Cursor?
A cursor is a set of rows together with a pointer that identifies a current row. Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis. 
Cursors extend result processing by:

·         Allowing positioning at specific rows of the result set.

·         Retrieving one row or block of rows from the current position in the result set.

·         Supporting data modifications to the rows at the current position in the result set.

·         Supporting different levels of visibility to changes made by other users to the database data that is presented in the result set.

·         Providing Transact-SQL statements in scripts, stored procedures, and triggers access to the data in a result set.

Type of Cursors

Forward-only

·         A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor.

Static

·         The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. Static cursors detect few or no changes, but consume relatively few resources while scrolling.

·         The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set. A static cursor does not display new rows inserted in the database after the cursor was opened, even if they match the search conditions of the cursor SELECT statement

Keyset

·         The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers, keys, known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.

Dynamic

·         Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor.

SQL Interview question part 2

What is the difference between in and exist operator in sqlserver?
IN:  Returns true if a specified value matches any value in a sub-query or a list.

Exists:  Returns true if a sub-query contains any rows.

What is a function?  What are the different types of user defined functions?
Scalar function:  A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported.

Table-value function: User-defined table-valued functions return a table data type. For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement.

CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)

RETURNS TABLE

AS

RETURN

(

    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'

    FROM Production.Product AS P

      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID

      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID

    WHERE SH.CustomerID = @storeid

    GROUP BY P.ProductID, P.Name

);

GO

SELECT * FROM Sales.ufn_SalesByStore (602);

Built-in function:  Built-in functions are provided by SQL Server to help you perform a variety of operations. They cannot be modified. You can use built-in functions in Transact-SQL statements to:

·         Access information from SQL Server system tables without accessing the system tables directly.

·         Perform common tasks such as SUM, GETDATE, or IDENTITY.

What is a stored procedure and types of parameters in it?
Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result.

IN Parameter:

CREATE PROCEDURE uspGetAddress @City nvarchar(30)

AS

SELECT *

FROM AdventureWorks.Person.Address

WHERE City LIKE @City + '%'

GO

OUTPUT Parameter:

CREATE PROCEDURE uspGetAddressCount @City nvarchar(30), @AddressCount int OUTPUT

AS

SELECT @AddressCount = count(*)

FROM AdventureWorks.Person.Address

WHERE City = @City

How can you execute dynamic sql in sqlserver?
A dynamic SQL statement is constructed at execution time, for which different conditions generate different SQL statements.

CREATE PROCEDURE usp_GetSalesHistory

 (

         @WhereClause NVARCHAR(2000) = NULL

 )

 AS

 BEGIN

         DECLARE @SelectStatement NVARCHAR(2000)

         DECLARE @FullStatement NVARCHAR(4000)    

         SET @SelectStatement = 'SELECT TOP 5 * FROM SalesHistory '

         SET @FullStatement = @SelectStatement + ISNULL(@WhereClause,'')    

         PRINT @FullStatement

         EXECUTE sp_executesql @FullStatement    

                /*

  --can also execute the same statement using EXECUTE()

         EXECUTE (@FullStatement)     

         */

 END

What is un-updatable views?
Aggregate functions or Group By clause in the View to be non-updatable.  SQL Server would not be able to determine which of the summarized rows should be updated.

What is trigger?  How can you invoke trigger on demand?
A trigger is a special kind of stored procedure that is invoked whenever an attempt is made to modify the data in the table it protects. Modifications to the table are made using INSERT,UPDATE,OR DELETE statements.  Triggers are used to enforce data integrity and business rules such as automatically updating summary data.  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.

What is difference between Views and stored procedure?

Views	Stored Procedures
No option to return customized result set	Can be customized to handle many requirements in single SP
Views cannot be parameterized.	Can be parameterized. can return multiple result sets. Can return different result based on parameters
no control on the way end user manipulate Views	have control over final data
Bad usage* of view may kill production server performance(*non sargable conditions in view)	more control over the stored procedure usage
Internal data processing not possible	Data can be processed using programming constructs
Views can be used in SELECT commands and can be joined with other views or tables	Stored procedures usually won’t be part of SELECT statement.

SQL Interview Question - part 1

What is Schema? How can you provide security to schema?

A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects.

You can assign an user login permissions to a single schema so that the user can only access the objects they are authorized to access.  Schemas can be created and altered in a database, and users can be granted access to a schema. A schema can be owned by any user, and schema ownership is transferable.

What is the difference between rule and constraint?

Rules are used for backward compatibility.  One the most exclusive difference is that we a bind rules to a data types whereas constraints are bound only to columns.  So we can create our own data type with the help of Rules and get the input according to that.

Explain different types of constraints in SQL SERVER?
1) Entity Integrity:  Ensures each row in a table is a uniquely identifiable entity. You can apply entity integrity to a table by specifying a PRIMARY KEY constraint.

2) Referential Integrity:  Ensures the relationships between tables remain preserved as data is inserted, deleted, and modified. You can apply referential integrity using a FOREIGN KEY constraint.

3) Domain Integrity:  Ensures the data values inside a database follow defined rules for values, range, and format. A database can enforce these rules using a variety of techniques, including CHECK constraints, UNIQUE constraints, and DEFAULT constraints.

Unique: CREATE TABLE Products(

    ProductID int PRIMARY KEY,

    ProductName nvarchar (40) Constraint IX_ProductName UNIQUE)

Check:  CREATE TABLE Products_2(

    ProductID int PRIMARY KEY,

    UnitPrice money CHECK(UnitPrice > 0 AND UnitPrice < 100)   )

Default:  CREATE TABLE Product_3(

   ProductID int PRIMARY KEY,

    UnitPrice NULL DEFAULT (0) )

What is an @@IDENTITY?
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement of the Identity column.

Example: CREATE TABLE new_employees

        (

         id_num int IDENTITY(1,1),

         fname varchar (20),

         minit char(1),

         lname varchar(30)

        )

INSERT new_employees (fname, minit, lname)

VALUES ('Karin', 'F', 'Josephs')

SELECT @@IDENTITY AS 'Identity'

Output:  1

What is difference between user and login?
A "Login" grants the principal entry into the SERVER instance.

A "User" grants a login entry into a single DATABASE.

One "Login" can be associated with many users (one per database).

What is transaction?  What are the acid properties?

Atomicity is an all-or-none proposition.

Consistency guarantees that a transaction never leaves your database in a half-finished state.

Isolation keeps transactions separated from each other until they’re finished.

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.

        --Please feel free to comment.