What's the difference between a covered query and a covering index?

                            Covered queries and covering indexes are different, yet closely related. A query is covered if all the columns it uses come from one or more indexes. These columns include the columns you want the query to return as well as columns in any JOIN, WHERE, HAVING, and ORDER BY clause. A covered query typically is considered advantageous because data access through indexes can be more efficient. However, the high-speed access that this kind of query facilitates can become costly when you update the table because you must maintain the indexes.

A covering index—which is used in covered queries—can provide some or all of the indexed columns that a covered query uses. If a covering index is also a composite index (i.e., it indexes more than one column in its base table or view), it might contain columns that aren't used in the covered query but are used instead in other queries that have overlapping columns.

Difference between CTE and Temp Table and Table Variable

        CTE

                          CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon.

       When to use CTE

1. This is used to store result of a complex sub query for further use.
2. This is also used to create a recursive query.
   

   Temporary Tables

                              In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside Tempdb database. Based on the scope and behavior temporary tables are of two types as given below-
   

   1.Local Temp Table

   Local temp tables are only available to the SQL Server session or connection (means single user) that created the tables. These are automatically deleted when the session that created the tables has been closed. Local temporary table name is stared with single hash ("#") sign.
   The scope of Local temp table exist to the current session of current user means to the current query window. If you will close the current query window or open a new query window and will try to find above created temp table, it will give you the error.
   

   2.Global Temp Table

   Global temp tables are available to all SQL Server sessions or connections (means all the user). These can be created by any SQL Server connection user and these are automatically deleted when all the SQL Server connections have been closed. Global temporary table name is stared with double hash ("##") sign.
     Global temporary tables are visible to all SQL Server connections while Local temporary tables are visible to only current SQL Server connection.
   

   Table Variable

   This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory. This also allows you to create primary key, identity at the time of Table variable declaration but not non-clustered index.
   

   Note

1. Temp Tables are physically created in the Tempdb database. These tables act as the normal table and also can have constraints, index like normal tables.
2. CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of statement. This is created in memory rather than Tempdb database. You cannot create any index on CTE.
3. Table Variable acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of batch. This is also created in the Tempdb database but not the memory.

Understanding OUTER JOIN in sql server

 
OUTER JOIN  :
    In comparison to an inner join, an outer join displays the result set containing all the rows from one table and the matching row from another table.
       Other words, Outer join extends the functionality of inner join. It returns following rows:
•    the same rows as inner join i.e. rows from both tables, which matches join condition and
•    rows from one or both tables, which do not match join condition along with NULL values in place of other table's columns.

Outer join Syntax is below.
     Select[column list]
from[ left joined table]                                       
Left! Right! Full [OUTER]  join  [right joined table]           
   on [join condition]

 NOTE: - Only one of the keywords left, right, full can be provided but exactly one is required. Keyword OUTER sometimes is avoided, but anyway keywords left, right or full indicate it is outer join. After the keyword ON join condition is written, generally it can contain many predicates connected with Boolean AND, OR, NOT. 


 Left outer join. 

A left outer join returns all rows from the table specified on the left side of the LEFT OUTER JOIN keyword and the matching rows from the table specified on the right side. The rows in the table specified on the left side for which matching rows are not found in table specified on the right side, NULL values are displayed in the columns that get data from the table specified on the right side.
                  Other words, Left outer join will output all rows from left input sets based on specified join predicate, even though rows from left input sets doesn’t necessarily have its match at right input sets.

Query of LEFT OUTER JOIN

CREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,
firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales

(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);

command in LEFT OUTER JOIN
 SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
LEFT OUTER JOIN sales AS s
ON c.custid = s.custid


 Right outer join.

          A right outer join returns all the rows from the table specified on the right side of the RIGHT OUTER JOIN keyword and the matching rows from the table specified on the left side.
               A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

Query of RIGHT OUTER JOINCREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,
 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales

(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);


command in RIGHT OUTER JOIN
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
RIGHT OUTER JOIN sales AS s
ON c.custid = s.custid


 Full Outer Join.

A full outer join is a combination of left outer join and right outer join. This join returns all the matching and non-matching row from both the tables. However, the matching records are displayed only once. In case of non-matching rows, a NULL value is displayed for the columns for which data is not available.
             A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Query of FULLOUTER JOINCREATE TABLE customer

(custid CHAR(3) NOT NULL PRIMARY KEY,
 firstname VARCHAR(10) NOT NULL);

INSERT INTO customer (custid, firstname) VALUES ('ant','anton');
INSERT INTO customer (custid, firstname) VALUES ('rng','rangga');
INSERT INTO customer (custid, firstname) VALUES ('joh','johan');
INSERT INTO customer (custid, firstname) VALUES ('Har','Harry');

CREATE TABLE sales

(salesID INT NOT NULL PRIMARY KEY,
custid CHAR(3) NULL,
qty    INT);

INSERT INTO sales (salesID, custid, qty) VALUES (1,'ant',10);
INSERT INTO sales (salesID, custid, qty) VALUES (2,'rng',20);
INSERT INTO sales (salesID, custid, qty) VALUES (3,'rng',25);
INSERT INTO sales (salesID, custid, qty) VALUES (4,'smi',10);


command in FULL OUTER JOIN
SELECT c.custid, c.firstname, s.salesID, s.custid, qty
FROM customer AS c
Full OUTER JOIN sales AS s
ON c.custid = s.custid

Merge Statement in SQL Server 2008

                                One of the fantastic new features of SQL Server 2008 is Merge Statement. Using a single statement, we can Add/Update records in our database table, without explicitly checking for the existence of records to perform operations like Insert or Update

Facts about Merge Statement

Here are a few facts that you must know before starting to use Merge Statement:

1. Atomic statement combining INSERT, UPDATE and DELETE operations based on conditional logic
2. Done as a set-based operation; more efficient than multiple separate operations
3. MERGE is defined by ANSI SQL; you will find it in other database p

 A typical merge statement looks like: 

MERGE [INTO] <target table>
USING <source table or table expression>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>

Using MERGE to perform INSERT and UPDATE operations on a table in a single statement

 USE AdventureWorks2012;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;
-- Update the row if it exists.    
    UPDATE Production.UnitMeasure
 SET Name = @Name
 WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed. 
 IF (@@ROWCOUNT = 0 )
 BEGIN
     INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
     VALUES (@UnitMeasureCode, @Name)
 END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the MERGE statement.
-- Create a temporary table to hold the updated or inserted values from the OUTPUT clause.
CREATE TABLE #MyTempTable
    (ExistingCode nchar(3),
     ExistingName nvarchar(50),
     ExistingDate datetime,
     ActionTaken nvarchar(10),
     NewCode nchar(3),
     NewName nvarchar(50),
     NewDate datetime
    );
GO
ALTER PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
 WHEN NOT MATCHED THEN 
     INSERT (UnitMeasureCode, Name)
     VALUES (source.UnitMeasureCode, source.Name)
     OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;
-- Cleanup 
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');
DROP TABLE #MyTempTable;
GO

What is the difference between lock, block and deadlock?

Lock: DB engine locks the rows/page/table to access the data which is worked upon according to the query.
Block: When one process blocks the resources of another process then blocking happens. Blocking can be identified by using
SELECT * FROM sys.dm_exec_requests where blocked <> 0
SELECT * FROM master..sysprocesses where blocked <> 0
Deadlock: When something happens as follows: Error 1205 is reported by SQL Server for deadlock.

Interview Questions for SQL Server

1. What are DMVs? - Dynamic Management Views (DMVs), are functions that give you information on the state of the server. DMVs, for the most part, are used to monitor the health of a server. They really just give you a snapshot of what’s going on inside the server. They let you monitor the health of a server instance, troubleshoot major problems and tune the server to increase performance.
2. Define a temp table - In a nutshell, a temp table is a temporary storage structure. What does that mean? Basically, you can use a temp table to store data temporarily so you can manipulate and change it before it reaches its destination format.
3. What’s the difference between a local  temp table and a global temp table? - Local tables are accessible to a current user connected to the server. These tables disappear once the user has disconnected from the server. Global temp tables, on the other hand, are available to all users regardless of the connection. These tables stay active until all the global connections are closed.
4. How do you use transactions? - In general, there are three types of transactions that you can use in the SQL Server environment: BEGIN TRANSACTION, ROLL BACK TRANSACTION and COMMIT TRANSACTION. The gist behind deploying transactions is that they allow you to group multiple SQL commands into a single unit. From there, each transaction begins with a certain task, and ends when all the tasks within the transaction are complete. BEGIN TRANSACTION gets the ball rolling. ROLLBACK TRANSACTION functions a lot like an “undo” command, and COMMIT TRANSACTION completes all of the tasks within that transaction.
5. What’s the difference between a clustered and a non-clustered index? - A clustered index directly affects the way tabled data is stored on a specific disk. This means that when a clustered index is used, data is stored in sequential rows based on the index column value. This is why a table can only contain a single clustered index. Non-clustered indexes directly affect the way physical data is stored and managed within SQL Server.
6. What are DBCC commands? - In very basic terms the Database Consistency Checker (DBCC) is used to aid in server maintenance. DBCC commands, many of which are completely undocumented, provide a set of commands that let you perform routing maintenance, status and validation checks. The most common DBCC commands are: DBCC CHECKALLOC (Lets you check disk allocation); DBCC OPENTRAN (Lets you check any open transactions); and DBCC HELP (shows a list of available DBCC commands to aid your server maintenance processes).
7. Describe the difference between truncate and delete - The difference between these two processes is fairly simple. Truncate means to simply empty out a table. On the other hand, the delete command lets you delete entire rows from within a table, but not all of the data within that table.
8. What is a view? - A view is simply a virtual table that is made up of elements of multiple physical or “real” tables. Views are most commonly used to join multiple tables together, or control access to any tables existing in background server processes.
9. What is a Query Execution Plan? - SQL Server has several built-in tools that optimize how queries are executed within their databases. A query execution plan is exactly what it sounds like – a snapshot of how the optimizing tools will execute and deploy specific queries within the database. This service helps you troubleshoot problems with jobs that don’t necessarily execute perfectly.
10. What is the default port number for SQL Server? - While this is kind of a softball question – if you know anything about SQL Server you should at least know the basic configuration options – it’s an important one to nail in the interview. Basically, when SQL Server is enabled the server instant listens to the TCP port 1433.

What is the difference between WHERE clause and HAVING clause?

    WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.
To understand this, consider this example.
Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:

SELECT * FROM DEPT WHERE ID > 3

ID	NAME
4	Sales
5	Logistics

Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:

SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
GROUP BY dept.name
HAVING AVG(emp.sal) > 80

DEPARTMENT	AVG_SAL
Engineering	90

As you see above, there is only one department (Engineering) where average salary of employees is greater than 80.

What is the difference between inner and outer join?

Inner Join

Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).

Inner join returns rows when there is at least one match in both tables

If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE emp.dept_id = dept.id

Department	Employee
HR	Inno
HR	Privy
Engineering	Robo
Engineering	Hash
Engineering	Anno
Engineering	Darl
Marketing	Pete
Marketing	Meme
Sales	Tomiti
Sales	Bhuti

Outer Join

Outer Join can be full outer or single outer

Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).
Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)

Department	Employee
HR	Inno
HR	Privy
Engineering	Robo
Engineering	Hash
Engineering	Anno
Engineering	Darl
Marketing	Pete
Marketing	Meme
Sales	Tomiti
Sales	Bhuti
Logistics

The (+) sign on the emp side of the predicate indicates that emp is the outer table here. The above SQL can be alternatively written as below (will yield the same result as above):

SELECT dept.name DEPARTMENT, emp.name EMPLOYEE 
FROM DEPT dept LEFT OUTER JOIN EMPLOYEE emp
ON dept.id = emp.dept_id  

What is SQL injection ?

Introduction

         SQL injection is a code injection technique, used to attack data driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker). SQL injection must exploit a security vulnerability in an application's software, for example, when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and unexpectedly executed. SQL injection is mostly known as an attack vector for websites but can be used to attack any type of SQL database.

Getting information on MS SQL Server objects .

 Tables and Views
                  To get all tables, views, and system tables, the following SQL Server system stored procedure can be executed.

exec sp_tables '%'

To filter by database for tables only, for example master:

exec sp_tables '%', '%', 'master', "'TABLE'"

To filter by database and owner / schema for tables only, for example, master and dbo:

exec sp_tables '%', 'dbo', 'master', "'TABLE'"

To return only views, replace "'TABLE'" with "'VIEW'". To return only system tables, replace "'TABLE'" with "'SYSTEM TABLE'".

Schemas / Owners

Here are two examples for queries to get schema / owner information.

select distinct SCHEMA_NAME from INFORMATION_SCHEMA.SCHEMATA order by SCHEMA_NAME

select name from dbo.sysusers where islogin = 1 order by name

Procedures

This is a query to get all MS SQL Server procedures.

exec sp_stored_procedures '%'

The query can be filtered to return procedures for specific schemas / owners and databases by appending more information onto the procedure call, such as the following:

exec sp_stored_procedures '%', 'dbo', 'master'

Procedure Parameter 

This is a system stored procedure call to get the columns in a SQL Server procedure.

exec sp_sproc_columns 'get_employee_names', 'dbo', 'sample'

Functions

This is a query to get all MS SQL Server functions.

select ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where upper(ROUTINE_TYPE) = 'FUNCTION'

Triggers

This is a query to get all MS SQL Server triggers.

select * from sysobjects where type = 'TR'

The query can be filtered to return triggers for a specific owner by appending a user_name call onto the where clause to the query.

select * from sysobjects where type = 'TR' and user_name(sysobjects.uid) = 'dbo'

Indexes

This is a query to get MS SQL Server indexes for a particular table. In this example, the table used is employee.

exec sp_helpindex 'employee'

Database Replication

                     Database replication is the frequent electronic copying data from a database in one computer or server to a database in another so that all users share the same level of information. The result is a distributed database in which users can access data relevant to their tasks without interfering with the work of others. The implementation of database replication for the purpose of eliminating data ambiguity or inconsistency among users is known as normalization.
Database replication can be done in at least three different ways:

• Snapshot replication: Data on one server is simply copied to another server, or to another database on the same server.
• Merging replication: Data from two or more databases is combined into a single database.
• Transactional replication: Users receive full initial copies of the database and then receive periodic updates as data changes.

A distributed database management system (DDBMS) ensures that changes, additions, and deletions performed on the data at any given location are automatically reflected in the data stored at all the other locations. Therefore, every user always sees data that is consistent with the data seen by all the other users.

What is Linked Servers or Database Links?

                  Don't be confused by the two terms; both are the same. In SQL Server it is called a Linked Server whereas in Oracle it's DBLinks (Database Links).

Linked Servers allows you to connect to other database instances on the same server or on another machine or remote servers. 

It allows SQL Server to execute SQL scripts against OLE DB data sources on remote servers using OLE DB providers. 

The remote servers can be SQL Server, Oracle etc. which means those databases tht support OLE DB can be used for linking servers.

               After setting up the Linked Servers we can easily access the other server tables, procedures etc. 

After establishing a connection we can even do CRUD operations. 

The advantage is about security; its works on Windows as well as SQL Server Authentications.

How to find information about existing Linked Servers?

We can get Linked Server basic information by executing the following stored procedure created in the master database or default system.

exec sp_linkedservers

  OR

using select * from sys.servers will return more information about servers.

 

 This will create a Linked Server; we can view this from Management Studio.

 

SQL database in Transition .Try statement later or Error no 952

                    This error can occur if a database has been taken offline and another statement of query is run against it. This is a reasonable common bug, in SQL 2005 and in the earliest versions of SQL 2008. I must say, I hav'nt seen it in SQL 2008 R2 yet. Hope it stays that way. Now lets start with the cause of this error. On purpose, or through lots of other reasons, this can happen with databases. There are allways reasons for taking a database offline, mostly before you can attach another copy, but when it becomes inaccessible then, you can get in serious troubles... The transition state normally takes not more then 20 seconds for a database. But this error is always the result of using the SSMS itself. Setting a database offline requires absolute exclusive access to the Database and if any connection is this open, this error will occur. But do not worry! Now I'm certainly not a database administrator, but I picked up a few possibilities on the way which I state here below, keeping the SQL instance as long as possible online. But first to the actual error message. Most of the times it will be something like the picture here on the right en the text will be:
  we cansolve this issue by many wys.But easiest solution  which worked for me.
A little more drastic now, create a new query... Something like the lines below,.... Replace the YOURDBNAME with the correct name of your troubling SQL-database.

USE MASTER
GOALTER DATABASE  [YOURDBNAME]SET OFFLINE WITH ROLLBACK IMMEDIATE

and if you want to make it offline

ALTER DATABASE YOURDBNAME SET ONLINE

This statement will absolutely take your database offline.