DDL

·  CREATE      : To create a new data structure.

·  ALTER        : To change an existing data structure.

·  DROP         : To remove an entire data structure.

·  TRUNCATE : To remove all rows from a table and resets table identity to initial value.

DML

·  INSERT         : To add records into the table.

·  UPDATE        : To change column value in the table.

·  DELETE        : To remove rows from the table.

Transaction Control Language:

·  COMMIT    : Save or enable DML changes to the database.

·  ROLLBACK :Restores database to original state since the last COMMIT command in transactions

• SAVE Transaction: Sets a save point within a transaction

Data Control Language

·  GRANT: Allow access privileges to users (e.g. select, insert, update, alter, delete, etc to nominated tables or attribute in tables).

·  REVOKE: Revoke or cancel access privileges.

·  DENY   : Creates an entry in the security system that denies permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships.

WHAT is Candidate key, Alternate key, Composite key

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

To find 2nd Max salary:

select max(E.Salary) from EmpInfo E where E.Salary in (select Salary from EmpInfo E2 where E2.Salary not in(select max(salary)from EmpInfo))

                                               (or)

select max(Salary) from  (select Salary from EmpInfo E2 where E2.Salary not in(select max(salary)from EmpInfo)) res

                                               (or)

select max(Salary) from EmpInfo where salary in (select Salary from EmpInfo where salary <(select max(salary)from EmpInfo))

select min(Salary) from EmpInfo where salary in (select Salary from EmpInfo where salary not in(select min(salary)from EmpInfo))

ROW_NUMBER() over (order by PurchaseOrderNum) as [RowNumber],

select * from sys.tables

select * from sys.procedures

select * from cgn_user for xml auto

select * from cgn_user for xml path

sp_depends sp_UserDetails_Add

sp_help sp_UserDetails

sp_helptext sp_UserDetails_Add

To find Nth Max salary by row count:

SELECT * FROM EmpInfo e WHERE

4=(SELECT COUNT(DISTINCT salary ) FROM EmpInfo WHERE e.salary<=salary)

Top RDMS Products with vendor name:

1. MySQL -> Oracle Corp

2. Oracle -> Oracle Corp

3. DB2 - > IBM

4. Teradata -> NCR

5. SQL Server -> Microsoft

The reason I picked MySQL as #1 was because the product is open source. SQL Server does not support unix/linux, that is why it's listed as the last.

http://www.indiabix.com/technical/dbms-basics/

http://www.indiastudychannel.com/resources/14173-RDBMS-interview-questions.aspx

What is a Database Schema?

In a relational database, the schema defines the tables, the fields in each table, and the relationships between fields and tables.

You can assign a 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.

SQL Server runs on port 1433

What are Primary Keys and Foreign Keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key.

Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.

What’s the Difference between a Primary Key and a Unique Key?

Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, whereas unique key creates a non-clustered index by default. Another major difference is that primary key doesn’t allow NULLs, but unique key allows one NULL only

What is a View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does the data in the view as views are the way to look at parts of the original table.( that does not really exist in its own right but is instead derived from one or more underlying base table)

CREATE view vw_user2 as SELECT * from SelfJoins where emp_manager_id=11

SELECT * FROM vw_user2

update vw_user2 SET emp_name='shaikk' WHERE emp_id=16

DELETE vw_user2 WHERE emp_id=16

What is Normalization?

In relational database design, the process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What is De-normalization?

De-normalization is the process of attempting to optimize the performance of a database by adding redundant data. It is sometimes necessary because current DBMSs implement the relational model poorly. 

What is a Stored Procedure?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

What is a Cursor?

A cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.

In order to work with a cursor, we need to perform some steps in the following order:

• ·          Declare cursor

• ·         Open cursor

• ·         Fetch row from the cursor

• ·         Process fetched row

• ·         Close cursor

• Deallocate cursor

DECLARE cur_main CURSOR FOR SELECT * FROM TABLE;

OPEN cur_main ;

FETCH cur_main INTO ---;

WHILE(@@FETCH_STATUS = 0)

BEGIN

        END

CLOSE cur_main;

DEALLOCATE cur_main;

DECLARE @name VARCHAR(50) -- database name  

DECLARE @path VARCHAR(256) -- path for backup files  

DECLARE @fileName VARCHAR(256) -- filename for backup  

DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  

SELECT name 

FROM MASTER.dbo.sysdatabases 

WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   

FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   

BEGIN   

       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  

       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   

END   

CLOSE db_cursor   

DEALLOCATE db_cursor

What is User-defined Functions?

User-defined Functions allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

What is the Difference between a Function and a Stored Procedure?

What are the Difference between Clustered and a Non-clustered Index?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore, the table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.A clustered index is automatically created when we create the primary key for the table

A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. (Read more here)

How Does a Non-Clustered Index Work?

A table can have more than one Non-Clustered index. But, it should have only one clustered index that works based on the Binary tree concept. Non-Clustered column always depends on the Clustered column on the database.

This can be easily explained with the concept of a book and its index page at the end. Let us assume that you are going to a bookshop and found a big 1500 pages of C# book that says all about C#. When you glanced at the book, it has all beautiful color pages and shiny papers. But, that is not only the eligibility for a good book right? One you are impressed, you want to see your favorite topic of Regular Expressions and how it is explained in the book. What will you do? I just peeped at you from behind and recorded what you did as below:

• 1. You went to the Index page (it has total 25 pages). It is already sorted and hence you easily picked up Regular Expression that comes on page Number 17.

  2. Next, you noted down the number displayed next to it which is 407, 816, 1200-1220.

  3. Your first target is Page 407. You opened a page in the middle, the page is greater than 500.

  4. Then you moved to a somewhat lower page. But it still reads 310.

  5. Then you moved to a higher page. You are very lucky you exactly got page 407. [Yes man you got it. Otherwise I need to write more. OK?]

  6. That’s all, you started exploring what is written about Regular expression on that page, keeping in mind that you need to find page 816 also.

In the above scenario, the Index page is Non-Clustered index and the page numbers are clustered index arranged in a binary tree. See how you came to the page 407 very quickly. Your mind actually traversed the binary tree way left and right to reach the page 407 quickly.

What are Different Types of Join?

Cross Join

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

Inner Join

A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.

Outer Join

A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:

·         Left Outer Join: In Left Outer Join, all the rows in the first-named table, i.e. “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.  

·         Right Outer Join: In Right Outer Join, all the rows in the second-named table, i.e. “right” table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

·         Full Outer Join: In Full Outer Join, all the rows in all joined tables are included, whether they are matched or not.

Self Join

This is a particular case when one table joins to itself with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is

rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to 

another. Self Join can be Outer Join or Inner Join.

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/ 

What’s the Difference between a Primary Key and a Unique Key?

Both primary key and unique key enforce uniqueness of the column on which they are defined. But by default, the primary key creates a clustered index on the column, whereas unique 

key creates a non-clustered index by default. Another major difference is that primary key doesn’t allow NULLs, but unique key allows one NULL only.

What are Primary Keys and Foreign Keys?

Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental aspect of all keys and constraints. A table can have only one primary key.

Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.

What is a Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs.

--------------------

create trigger tr_cgnUser on d

 for imnert,update,delete as

insert into tbl_cgnuser_log values(getdate())

Biggest difference between Temp tables and Table variables in Sql Server

Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.

• 1. There is no log for table variables

  2. Table variables have only local scope (you cannot access the same table variable from different procedures)

  3. Procedures with temporary tables cannot be pre-compiled

Temp tables behave same as normal tables and are bound by transactions.

BEGIN TRAN

insert into @var2

select 1, 'data 1' union all

select 2, 'data 2' union all

select 3, 'data 3'

SELECT * INTO  #temp3 from SelfJoins

select * from #temp3

select * from @var2

ROLLBACK

select * from @var2

if object_id('tempdb..#temp2') is null

    select '#temp does not exist outside the transaction

• Table variables (DECLARE @t TABLE) are visible only to the the connection that creates it, are stored in RAM, and are deleted when the batch or stored procedure ends.

• Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.

• Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when the connection that created it is closed.

• Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted.

TRUNCATE

• • TRUNCATE is a DDL command

  • TRUNCATE is executed using a table lock and whole table is locked for remove all records.

  • We cannot use Where clause with TRUNCATE.

  • TRUNCATE removes all rows from a table.

  • Minimal logging in transaction log, so it is performance wise faster.

  • TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.

  • Identify column is reset to its seed value if table contains any identity column.

  • To use Truncate on a table you need at least ALTER permission on the table.

  • Truncate uses the less transaction space than Delete statement.

  • Truncate cannot be used with indexed views.

DELETE

• • DELETE is a DML command.

  • DELETE is executed using a row lock, each row in the table is locked for deletion.

  • We can use where clause with DELETE to filter & delete specific records.

  • The DELETE command is used to remove rows from a table based on WHERE condition.

  • It maintain the log, so it slower than TRUNCATE.

  • The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

  • Identity of column keep DELETE retain the identity.

  • To use Delete you need DELETE permission on the table.

  • Delete uses the more transaction space than Truncate statement.

  • Delete can be used with indexed views.

DROP

• • The DROP command removes a table from the database.

  • All the tables' rows, indexes and privileges will also be removed.

  • No DML triggers will be fired.

  • The operation cannot be rolled back.

  • DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command.

  • DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

Query Optimization:

• • Table should have primary key

  • Table should have minimum of one clustered index

  • Table should have appropriate amount of non-clustered index

  • Non-clustered index should be created on columns of table based on query which is running

  • Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause

  • Do not to use Views or replace views with original source table

  • Triggers should not be used if possible, incorporate the logic of trigger in stored procedure

  • Remove any adhoc queries and use Stored Procedure instead

  • Check if there is atleast 30% HHD is empty – it improves the performance a bit

  • If possible move the logic of UDF to SP as well

  • Remove * from SELECT and use columns which are only necessary in code

  • Remove any unnecessary joins from table

  • If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

SQL SERVER 2005:

1.Both are combined as SSMS(Sql Server management Studio).

2.XML datatype is introduced.

3.We can create 2(pow(20))-1 databases.

4.Exception Handling

5.Varchar(Max) data type

6.DDL Triggers

7.DataBase Mirroring

8.RowNumber function for paging

9.Table fragmentation

10.Full Text Search

11.Bulk Copy Update

12.Cant encrypt

13.Can Compress tables and indexes.(Introduced in 2005 SP2)

14.Datetime is used for both date and time.

15.Varchar(max) and varbinary(max) is used.

16.No table datatype is included.

17.SSIS is started using.

18.CMS is not available.

19.PBM is not available.

SQL SERVER 2008:

1.Both are combined as SSMS(Sql Server management Studio).

2.XML datatype is used.

3.We can create 2(pow(20))-1 databases.

4.Exception Handling

5.Varchar(Max) data type

6.DDL Triggers

7.DataBase Mirroring

8.RowNumber function for paging

9.Table fragmentation

10.Full Text Search

11.Bulk Copy Update

12.Can encrypt the entire database introduced in 2008.

--check it(http://technet.microsoft.com/en-us/library/cc278098(SQL.100).aspx)

(http://www.sqlservercentral.com/articles/Administration/implementing_efs/870/)

(http://www.kodyaz.com/articles/sql-server-2005-database-encryption-step-by-step.aspx)

(http://www.sql-server-performance.com/articles/dev/encryption_2005_1_p1.aspx)

(http://geekswithblogs.net/chrisfalter/archive/2008/05/08/encrypt-documents-with-sql-server.aspx)

13.Can compress tables and indexes.

-http://www.mssqltips.com/tip.asp?tip=1582

14.Date and time are seperately used for date and time datatype,geospatial and timestamp with internal timezone 

is used.

15.Varchar(max) and varbinary(max) is used.

16.Table datatype introduced.

17.SSIS avails in this version.

18.Central Management Server(CMS) is Introduced.

-http://msdn.microsoft.com/en-us/library/bb934126.aspx

-http://www.sqlskills.com/BLOGS/KIMBERLY/post/SQL-Server-2008-Central-Management-Servers-have-you-seen-these.aspx

19.Policy based management(PBM) server is Introduced.

-http://www.mssqltips.com/tip.asp?tip=1492

-http://msdn.microsoft.com/en-us/library/bb510667.aspx

EXEC SP_XML_PREPAREDOCUMENT @hDoc OUTPUT, @XMLIn      

  SELECT @MapperId  = MapperId,  

      @FieldName = FieldName,  

      @FieldValue = FieldValue  

  FROM   OPENXML (@hDoc, 'TMIntegrationEAI/LastKey', 2)                                 

    WITH                                 

      (                                

      MapperId INT  

      ,FieldName VARCHAR(50)           

      ,FieldValue VARCHAR(255)  

      )XmlDoc    

EXEC SP_XML_PREPAREDOCUMENT @hDoc OUTPUT, @XMLData    

-- Select the EntityMapping node

INSERT INTO #TMP_HEADER(

[PurchaseOrderNum],

[PODate],

[CarrierSCAC],

[VendorNumber],

[SourceSystem],

[VendorMaterialNumber],

[LoadNumber],

[RequirementTrackingNumber],

[UserId],

[isValid]

)SELECT   

PurchaseOrderNum = [XMLDOC].[PurchaseOrderNum],

PODate = [XMLDOC].[PODate],

CarrierSCAC = [XMLDOC].[CarrierSCAC],

VendorNumber = [XMLDOC].[VendorNumber],

SourceSystem = [XMLDOC].[SourceSystem],

VendorMaterialNumber = [XMLDOC].[VendorMaterialNumber],

LoadNumber = [XMLDOC].[LoadNumber],

RequirementTrackingNumber = [XMLDOC].[RequirementTrackingNumber],

UserId = [XMLDOC].[UserId],

isValid =                   [XMLDOC].[isValid]      

FROM  OPENXML (@hDoc, 'DocumentRoot/Parameters', 2)

WITH ( 

[PurchaseOrderNum] Varchar(10),

[PODate] DateTime,

[CarrierSCAC] Varchar(80),

[VendorNumber] Varchar(16),

[SourceSystem] Char(3),

[VendorMaterialNumber] Varchar(35),

[LoadNumber] Varchar(50),

[RequirementTrackingNumber] Varchar(50),

[UserId] Varchar(50),

[isValid]                   BIT

) XmlDoc

datetime2 wins in most aspects except (old apps Compatibility)

• 1. larger range of values

  2. better Accuracy

  3. smaller storage space (if optional user-specified precision is specified)

ROW_NUMBER() Function with Partition By clause

If we want to add row number to each group, and it is reset for every group, let's take a look at the Employe table

Example

The following is the OUTPUT of above query

Rank() Function

This function will assign a unique value to each distinct Row, but it leaves a group between the groups.

Example

The following is the OUTPUT of the above query.

Gap represents number of occurrence example - EmpName="atul" is repeated 3 times and has rank "1" , the next rank will be 1+3=4 and same with the next value.

Dense_Rank() Funcation

Dense_Rank() Funcation is similar to Rank with only difference, this will not leave gaps between groups.

Example

The following is the OUTPUT of the above query:

SCOPE_IDENTITY is:

1. SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in the same scope.

2. SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

Difference between Sql Server Char and Varchar Data Type

CHAR Data Type is a Fixed Length Data Type. For example if you declare a variable/column of CHAR (10) data type, then it will always take 10 bytes irrespective of whether you are storing 1 character or 10 character in this variable or column. And in this example as we have declared this variable/column as CHAR(10), so we can store max 10 characters in this column.

On the other hand VARCHAR is a variable length Data Type. For example if you declare a variable/column of VARCHAR (10) data type, it will take the no. of bytes equal to the number of characters stored in this column. So, in this variable/column if you are storing only one character then it will take only one byte and if we are storing 10 characters then it will take 10 bytes. And in this example as we have declared this variable/column as VARCHAR (10), so we can store max 10 characters in this column.

Below table lists out the major difference between the VARCHAR and NVARCHAR Data Type in Sql Server:

Difference Between Sql Server VARCHAR and VARCHAR(MAX) Data Type

Difference Between Temporary Table and Table Variable – Summary

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

Remove Duplicate Rows from a Table

DELETEFROM MyDuplicateTable WHERE ID NOT IN(SELECT MAX(ID)FROM MyDuplicateTable GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MAX(AUTOID)   FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)