Detach and Attach in SQL SERVER

Detach and Attach

Step 1: Attach and Detach database
Before proceeding need to know what is attach and detach!
While we took backup of a database, the .bak file saved in drive and also exists in server
when ever use a detach option this will takes backup of database and drops the database from server
While attach like a restore of a database which already backedup.

The following are the steps how we can attach and detach the database

Step 2: Click on database -->Tasks-->Detach

If you found following error, Set your database to Single User mode using SET operator

Now choose drop connections then click OK

Also the following is the SP also how to set database into single user mode

Attach:
Click on database-->Attach

Choose database which you want to restore

click ok

The database came to previous position

SQL SERVER 2014 Installation

SQL SERVER 2014 Installation

Step 1: Click on your set up file 

Step 2: Click on installation then select first one ( New SQL Server standard-alone installation or .... )

Step 3: Specify a free edition (it could be free for 180 days)
If you have a product key then choose enter product key

Step 4: Check the BOX accept the licence terms then click next

Step 5: After finished the Updates Checking click next

Step 6:If no errors you can click next

Step 7: Choose one from the following based on which type you need. All about headings given the features in description

Step 8: Select instance features and/or Shared features according to your work requirement click next

Step 9: Give the instance name may be default or Named

Step 10:  Choose the Service account Name and startup Type

Step 11: Server  configuration -->Authentication Mode
Here Mixed mode we can keep security for log-in. Windows authentication Mode required no security but another must--> Specify SQL Server administrator -->NEXT

and this is for Analysis Services

Step 12: This is about the reporting services choose install only or install and configure

This could be the final step

Import Data from EXCEL

How to Import Data from EXCEL to Destination

Import data from excel through wizard 

Step 1: Choose your database where to import data -->Tasks-->Import Data

Step 2: Click Next

Step 3: Choose the Data source as a Microsoft Excel and browse the file path as well as choose the Excel Version click next

Here we have the options to get the columns Dynamically (like First row has column names) or Manually we have to provide

Step 4:Now choose our destination and Authentication type also database -->click Next

Step 5: Choose either option click next

Step 6: Select the sheet where you have a required data then click next

Step 7:Here we have the options to choose either Package or run immediately 

Run immediately is a process which retrieves the data instantly 

If it is a package we can run package along with configuration file at any environment 

Step 8: Close 

Backup, Restore, and Move the SSIS Catalog

Backup, Restore, and Move the SSIS Catalog

For full details go through the web page

http://technet.microsoft.com/en-us/library/hh213291.aspx

Cannot drop database [SSISDB] because it is currently in use.

USE [master]
GO

/****** Object:  Database [SSISDB]    Script Date: --/--/20-- 12:53:55 PM ******/

alter database [SSISDB] set single_user with rollback immediate


 drop database [SSISDB]

Restore database [SSISDB] 

USE [master]
GO

/****** Object:  Database [SSISDB]    Script Date: 3/10/2014 12:53:46 PM ******/
CREATE DATABASE [SSISDB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.mdf' , SIZE = 40960KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SSISDB.ldf' , SIZE = 3456KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [SSISDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [SSISDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [SSISDB] SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_NULLS OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_PADDING OFF
GO

ALTER DATABASE [SSISDB] SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [SSISDB] SET ARITHABORT OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_CLOSE OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [SSISDB] SET AUTO_SHRINK OFF
GO

ALTER DATABASE [SSISDB] SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [SSISDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [SSISDB] SET CURSOR_DEFAULT  GLOBAL
GO

ALTER DATABASE [SSISDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [SSISDB] SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [SSISDB] SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [SSISDB] SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [SSISDB] SET  DISABLE_BROKER
GO

ALTER DATABASE [SSISDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [SSISDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [SSISDB] SET TRUSTWORTHY OFF
GO

ALTER DATABASE [SSISDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [SSISDB] SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [SSISDB] SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [SSISDB] SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [SSISDB] SET RECOVERY FULL
GO

ALTER DATABASE [SSISDB] SET  MULTI_USER
GO

ALTER DATABASE [SSISDB] SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [SSISDB] SET DB_CHAINING OFF
GO

ALTER DATABASE [SSISDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO

ALTER DATABASE [SSISDB] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO

ALTER DATABASE [SSISDB] SET  READ_WRITE
GO

MERGE JOIN

MERGE JOIN IN SQL SERVER

MERGE INTO EMPP as table1 USING (select * from EMP) as table2  ON table1.EMPNO=table2.EMPNO
WHEN MATCHED THEN UPDATE SET table1.ENAME=table2.ENAME,table1.JOB=table2.JOB,table1.MGR=table2.MGR,table1.HIREDATE=table2.HIREDATE,table1.SAL=table2.SAL,table1.COMM=table2.COMM,table1.DEPTNO=table2.DEPTNO
WHEN NOT MATCHED THEN
INSERT VALUES(table2.EMPNO,table2.ENAME,table2.JOB,table2.MGR,table2.HIREDATE,table2.SAL,table2.COMM,table2.DEPTNO);

/***PRINT THE DEPT WISE SALRIES WHOSE SALARY IS GRATER THAN DEPT WISE AVERAGE SALARY***/
WITH X AS
(
SELECT DEPTNO,AVG(SAL) AS AVGSAL FROM EMP GROUP BY DEPTNO
)SELECT * FROM X
INNER JOIN EMP ON X.DEPTNO=EMP.DEPTNO WHERE SAL>AVGSAL

SQL Server Interview Questions and Answers

1) Which data type not used in SQL Azure?

Ans: user-defined CLR data types, these are not supported in Windows Azure SQL Database

2)Differences Between Truncate and Delete ?

Ans:   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 removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

      DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

3)ACID properties?

 Ans:

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

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.

4) Explain one of the query optimization in u r project? 

Ans: Your  Explanation

5) Types of locks in sql?

Ans:  

* Shared (S) 
* Update (U) 
* Exclusive (X) 
* Intent 
* Schema 
* Bulk Update (BU) 
* Key-range

For more see below link 

http://www.dotnetfunda.com/forums/show/4658/what-are-the-different-types-of-locks-available-in-sql-server  

 For Examples see below link

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server 

6)What type of transactions used in your Project?

Ans: You can explain your self

Usually Begin Tran
              Commit Tran
              RollBack Tran
We must to say the specific location and situation in the project

7)Write a query for a Factorial?  

 declare @n int,@r int

set @r=1

set @n = 4;

WITH CTE (N) AS(

SELECT CASE WHEN @N<0 THEN NULL ELSE 1 END N

UNION ALL

SELECT (N+1)

FROM CTE

WHERE N < @n

)

SELECT @r = @r*n from cte

print @r

8) Difference Between Index Scan and Index Seek

Ans:

An index scan is a complete scan of all the pages in a non-clustered index.

A clustered index scan is a complete scan of all pages in a clustered index (i.e, the table itself)

Neither scan uses the b-tree structure of the index, but just reads the leaf pages in order, using each page's reference to the next in the chain.An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf. An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf. 

9)Is there any difference between table scan and index scan?  Ans:A table scan (only present when you don't have a clustered index) is a scan of the data pages of the table. An index scan reads the leaf pages of the index. If it's a nonclustered index, it'll be a lot fewer pages than the table. A clustered index scan (only present if there's a clustered index) reads the leaf pages of the clustered index, which are the data pages of the table. It's virtually the same same as a table scan. There are some minor differences as to how SQL does the scan, but it is the entire table, like with a table scan.  Full table scan has linear complexity, index seek has logarithmic complexity. 10. About the current project/ experience ? 11. What is index scan and index seek? What is the difference? Ans: Explained above 12. Merge Operation in sql server. How it internally works? Ans: MERGE is a new feature that provides an efficient way to do multiple DML operations. In earlier versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions; however, at present, by using the MERGE statement, we can include the logic of such data changes in one statement that even checks when the data is matched and then just update it, and similarly, when the data is unmatched, it is inserted. MERGE INTO tablename USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ... 13. What is Column store indexes, its usage?  --(new feature in sql 2012). Ans: https://www.simple-talk.com/sql/database-administration/columnstore-indexes-in-sql-server-2012/ The columnstore index in SQL Server 2012 stores columns instead of rows, and is designed to speed up analytical processing and data-warehouse queries. Whilst columnstore indexes certainly do that effectively, they are not a universal panacea since there are a number of limitations on them. When used appropriately, they can reduce disk I/O and use memory more efficiently.     14. Lookup and merge in SSIS ? 15. Rollup(tabular) and cube in sql server ? Ans:  ROLLUP ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query. Syntax  ROLLUP appears in the GROUP BY clause in a SELECT statement. Its form is: SELECT ... GROUP BY ROLLUP(grouping_column_reference_list) The CUBE and ROLLUP operators are useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause. Difference b/w CUBE and ROLLUP: - CUBE generates a result set that shows aggregates for all combinations of values in the selected columns. - ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns. 16. How to tune a sql procedure. Steps that are involved ? Ans: 1) Use SET NOCOUNT ON statement.While we execute the statement it counts the number of records 2)Use the DB name and schema name along with the table name 3) Use "usp_"instead of "sp_", Because while executing the stored procedure  if it initiated with "sp_" then the records fetching from system stored procedure. Here the system SPs initiated "with sp_" 4) Write the select statements using with(NOLOCK) 5)Try to avoid the count(*), prefer count(1) 6) Use the UNION ALL instead of UNION   7)Use the temp tables(#tables) instead of Cursor  8)Keep the transaction as short as possible 9) Rewrite sub queries using joins 10)Use the parameterized queries  11)Use indexes with filtered clause 12)Create multiple column indexes 13)Avoid indexes on small tables  14) Always avoid the use of substring function in Query 15)Use WITH READ UNCOMMITTED isolation 16)Avoid expensive operations such as NOT LIKE 17) The operators shown below are in their decreasing order of their performance.         =         >,>=,<, <=        LIKE       <>       i.e., better to go for '=' rather than 'LIKE' or '<>' 18)Use DMVs and DMFs to know the reads and writes First, we need to understand what a Read really is. Here is a quote from a Microsoft white paper about I/O architecture that clearly defines logical and physical reads: "The I/O from an instance of SQL Server is divided into logical and physical I/O. A logical read occurs every time the database engine requests a page from the buffer cache. If the page is not currently in the buffer cache, a physical read is then performed to read the page into the buffer cache. If the page is currently in the cache, no physical read is generated; the buffer cache simply uses the page already in memory." 17. Preparing reports from multiple datasources ? Ans: Using LOOKUP 18.Performance tuning and scalability 19. Isolations Ans:Refer Q-29 20. Pivoting 21.What is Page? Ans: The main disk allocation unit of database engines is called a page. The size of a page is typically some kilobytes. A page usually contains between dozens and hundreds of records. This is important to remember: sometimes you may think a query is optimal from the point of view of the record accesses, while it is not if you look at page accesses. 22.What is Execution plan? Ans: The Query Execution Plans describe the steps and the order used to access or modify data in the database.  Once you have this information you can identify what parts of the query are slow. SQL Server can create execution plans in two ways: Actual Execution Plan - (CTRL + M) - is created after execution of the query and contains the steps that were performed Estimated Execution Plan - (CTRL + L) - is created without executing the query and contains an approximate execution plan 23. Clustered and Non clustered Index? 24. What is SQL Profiler? Ans: Sql profiler is a tool(GUI) used to monitor the traced T-Sql queries which are running slowly and takes more time to run.The Events Selection gives us more options to choose choice of content in Database Engine may off line or online.We can cave the trace file and analyze later secession. 25. Write a query to retrieve the customers who has not placed any order? 26. What is Dirty Read and Phantom Reads ? Ans: A dirty read takes no notice of any lock taken by another process. The read is officially “dirty” when it reads data that is uncommitted. This can become problematic if the uncommitted transaction fails or for some other reason is rolled back. Phantom Reads – This is when data that you are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different. 27. What is Commit size in SSIS? Ans:  Rows per batch – The default value for this setting is -1 which specifies all incoming rows will be treated as a single batch. You can change this default behavior and break all incoming rows into multiple batches. The allowed value is only positive integer which specifies the maximum number of rows in a batch. Maximum insert commit size – The default value for this setting is '2147483647' (largest value for 4 byte integer type) which specifies all incoming rows will be committed once on successful completion. 28. What is Transaction? Ans; A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased. 29. Isolation Levels in SQL ? Ans:Isolation levels in SQL Server control the way locking works between transactions. SQL Server 2008 supports the following isolation levels Read Uncommitted Read Committed (The default) Repeatable Read Serializable Snapshot For Examples plz go through http://gavindraper.com/2012/02/18/sql-server-isolation-levels-by-example/ 30. What is Update Statistics? Ans: Updating statistics ensures that queries compile with up-to-date statistics. However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries. The specific tradeoffs depend on your application. UPDATE STATISTICS can use tempdb to sort the sample of rows for building statistics. 31. What is RAISE ERROR? & Throw? Ans: RaiseError is a system defined error. We can assign this message to a variable in catch block and display this to user.Both RAISERROR and THROW statements are used to raise an error in Sql Server. The journey of RAISERROR started from Sql Server 7.0, where as the journey of THROWstatement has just began with Sql Server 2012.  32. Response time and Total time Ans: Response time is the time it takes for a query to return the first record.  Total time is the time it takes for the query to return all records.

33. How could you know how many files processed in your package up to today?

Sql Server Profiler & Database Engine Tuning Adviser

Sql Server Profiler & Database Engine Tuning Adviser  

Step 1: Write a Query

(I have collected this from Microsoft web)