If SQL Server installation fails how you will find the problems ?

We can analyze the errors by viewing the details available in 'Summary.txt' file located in setup bootstrap folder in

'Drive:\Program Files \Microsoft SQL Server \90 \Setup Bootstrap \Log'

Understanding Security in SQL Server

SQL Server Security

        We required a mapping Login to connect to the server either from Person / Application .

In SQL Server Logins can be of 2 types .

                                            * Windows Authentication Logins

                                            * SQL Server Authentication Logins


Windows Authentication Logins

* These are created for the active directory users .

* Using a single sign on we can connect to Windows and SQL Server .

* Windows Password policies are applicable .

* Provides more security.

* The windows authentication logins information will not be maintained by SQL Server .

How Windows Authentication Works

* When a user is connected with an active directory user to the mechine then windows will verify the user name and password and allocated an Unique ID called TGT (Ticket Granting Ticket ) .

* When the same user connected to SQL Server with the TGT he will be allowed to connect SQL Server.


How to create user in Active Directory

Step-1 : Create an Active Directory User

Start -->Control Pannel --> Administrative Tools --> Active Directory Users & Computers
                                                  (or)
Start --> Run --> dsa.msc

Select the user folder right click and click on New --> User

Provide the first name , initial ,last name and user logon name and click on next

Provide the password confirm the password and click on next and click on finish

Step-2 : Creating windows login in SQL Server

In the object explorer under the server expand security folder

Select the login folder right click and click on new login

Click on search click on advanced and click on find now

select the required active directory user and click on ok and click on ok

we can also create window login with T-SQL command

User Master
go
Create Login <Login Name> from windows

Orphan Logins

When an active directory user is deleted then the mapping login in SQL Server is called as Orphan Login .

To see the list of Orphan Logins

sp_validatelogins

Note: To resolve the orphan logins create the active directory user

SQL Server Authentication Logins

* These logins are created for the non-active directory users.

* The users requires a separate login and password to connect with SQL Server.

* By default every instance contains a default SQL Login 'sa'

* The information about the SQL Logins and passwords will be maintained in SQL Server.

How SQL Server Authentication Works

* The user will connect to the local machine by using the local credentials .

* By using an SQL Server login and password the user connects to SQL Server.

* SQL Server verifies the validity of Login and Password.

Creating of SQL Server Logins

* In the object explorer under the server expand the security select the login folder right click and click on new login.

* Provide a login name select SQL Server authentication .

* Provide password and confirm password.

* Select the options to enforce the password policy.

* Click on OK

Note:

* By default every login can access master , msdb & tempdb databases .
* But user and model database are not accessible .

MySQL vs SQL Server

The below are the few point about differences between MY SQL and SQL Server

1. MySQL is an open source and now its owned by ORACLE where as SQL Server is not open source and payment has to be made to Microsoft to use SQL Server .

2. MySQL doesn't support XML data type where as SQL Server supports XML .

3. MySQL supports only table level security where as SQL Server supports table level ,column level and row level (from SQL Server 2014 ) .

4. MySQL supports triggers from 5.0 version on wards only where as SQL Server supports triggers from beginning versions itself.

5. Transaction Support, Replication Support  and online backup support is very limited in MySQL

6. Auto Tuning is not supported in MySQL .

7. Log Shipping , Clustering and Mirroring are not available in MySQL .

8. Reporting and OLAP services are not available from MySQL .

9. Job scheduling and profiling are not available in MySQL .

SQL vs Oracle

Difference between DBMS and RDBMS

Difference between AzureTable Storage and SQL Azure

SQL Server VS PostgreSQL

SQL Server - Difference between Check Point and Lazy Writer .

The below are the key differences between Check Point and Lazy Writer .

SQL Server - Difference between joins and subqueries

The below are the few points to identify the differences between Joins and Sub Queries .

1. Joins are column-wise operations where as Sub Queries are are query wise operations. i.e in sub queries one query output depends on another query .

2. Joining is possible when table have some internal relation where as for Sub Queries internal relation is not required .

3. Joins checks the conditions first and then put it to temp table and displays the data where as in case of Sub Queries separate temp table will be created for checking conditions .

4. Sub Queries will takes more time if amount of data in a table is huge but the amount of data doesn't matter for Joins

5. In Joins we can view all selected columns from all selected tables where as in Sub Queries we can view the columns of outer query only .

SQL Server - Difference between deterministic and non deterministic fucntions

The key difference is

Deterministic functions will returns same value by taking some input ex: select sin(30)
Where as non determininistic functions will returns different value by taking same values
 ex: select getdate()

SQL Server - Difference between DML Triggers and DDL Triggers

The below are the key differences between DML Triggers and DDL Triggers

S.No          DML Triggers                              DDL Triggers

1      DML Triggers will works on Insert    DDL Triggers will works on
        Update and Delete                               Create , Alter and Drop

2      DML Triggers will works on             DDL Triggers will works on
        Tables and Views                               Database and  and Servers

3     DML Triggers can be used as             DDL Triggers cannot be used
       Instead of Triggers                               as instead of triggers

4     DML Triggers will creates              
       Inserted and Deleted magic                     NA
       tables

5     DML Triggers runs either before       DDL Triggers run only after a
       or after a T-SQL statement is             T-SQL statement is completed .
       completed

SQL Server - Difference between Primary Key and Clustered Index

The below are the key differences between PK and CI

S.No               Primary Key                                        Clustered Index

1        PK Allows to establish relation                               NA

2        PK doesn't allow duplicate values                   CI allows duplicate values

SQL Server - Difference between Identity and Primary Key

The below are the key differences between Identity and Primary Key

S.No                          Identity                                                 Primary Key

1     Identity column values is auto incremented      PK value will be entered by the user

2    Only one identity column per table and             Can be created on more than one column
      created on only one column

SQL Server - Difference between ISNULL and COALESCE

The below are the key differences between ISNULL and COALESCE

S.No                 ISNULL                                           COALESCE

 1          Specific to SQL Server                             ANSI Standard

 2          Replaces NULL value with the                Re-tuns first non null value among the list of
             specified replacement value                     arguments

 3          Uses 2 arguments only                             Uses any no of arguments

 4          Returns the same data type as of              It is not guaranteed to return the same data type
             first argument                                           as of first argument .

             Ex:                                                            Ex:
                   Declare @Str varchar(3)                           Declare @Str varchar(3)      

                select isnull(@str,'abcdefgh') 'ISNULL Value' select coalesce(@str,'abcdefgh')                                                                                                                           'COALESCE Value'

                            ISNULL Value                                           COALESCE Value
                           -------------------                                           -----------------------
                                  abc                                                             abcdefgh

SQL Server - Difference between SQL and T-SQL

The below are the basic differences between SQL and T-SQL

S.No                                       SQL                                                T-SQL

1          SQL is ANSI / ISO standard database query       T_SQL is implemented in SQL Server
            Language

2         Set of queries sumitted individual to the server    T-SQL is a batch program and submit to the                                                                                             server in a single shot . We can run all the                                                                                                 programs at any time

3         Developed by IBM                                                T-SQL is extension of SQL in MSSQL

Drawbacks of SQL Server 2012.

The below are the few drawbacks of SQL Server 2012 .

1. SSMS will no longer run on XP.

2. AWE (Address Windowing Extensions ) is no longer supported so 32 bit SQL Server running on 32 GB of RAM machine is only going to be able to use 4 GB .

SQL Server - Difference between SQL Server 2008 and SQL Server 2008 R2

The below are the basic differences between SQL 08 and SQL 08 R2.

1.In SQL 2008 maximum logical processor support is 64 where as in SQL 2008 R2 maximum logical processor support is 256.

2.In SQL 2008 Master Data Services (MDS) are not part of BI where as in SQL 2008 R2 MDS is part of BI.

3.In SQL 2008 Power Pivot with in BI is  not implemented where as in SQL 2008 R2  Power Pivot in BI is implemented.

4.In SQL 2008 geo spatial data type is introduced with few common features for SSRS  where as in SQL 2008 R 2 additional features are added to geo spatial data types in SSRS such as mapping , routing and custom shapes.

5.In SQL 2008  maximum size of a database is 4 gb in express editions where as in SQL 2008 R2 database size limited is increase to 10 gb in express edition .

SQL Server - Difference between SQL Server 2005 and SQL Server 2008

The below are the basic differences between SQL 05 and SQL 08.

1.In SQL 2005 we can't encrypt the entire database where as in SQL 2008 we can encrypt entire db.

2.In SQL 2005 there is no backup encryption where as in SQL 2008 backup encryption is introduced

3.In SQL 2005 XML - Data Type is introduced where as in SQL 2008 XML - Data Type is implemented and used.

4.In SQL 2005 LINQ is not there where as in SQL 2008 LINQ is introduced for retrieving multiple types of data.

5.In SQL 2005 there is no table valued parameter concepts where as in SQL 2008 table valued parameter is introduced .

6.In SQL 2005 there is Merge statement concept where as in SQL 2008 Merge statement is introduced .

8.In SQL 2005 date time is introduced for date and time where as in SQL 2008 there were major changes in date time i.e 4 data types are introduced in date time they are DATE, TIME,DATETIMEOFFSET and DATETIME2.

9.In SQL 2005 Data Synchronization is not available where as in SQL 2008 Data Synchronization  is introduced

10. In SQL 2005 UDT's were restricted to a maximum of 8 KB where as in SQL 2008 restrictions has been removed for UDT's.

SQL Server - Difference between SQL Server 2000 and SQL Server 2005

The below are the basic differences between SQL Server 2000 and 2005.

1.In SQL 2000 Query Analyzer and Enterprise manager are separate where as in SQL 2005 both are combined as SSMS (SQL Server management Studio).

2.In SQL 2000 there is no XML data type where as in SQL 2005 XML data type is introduced .

3.In SQL 2000 exception Handling mechanism is not available where as in SQL 2005 exception handling mechanism is introduced.

4.In SQL 2000 there is no varchar(max) data type where as in SQL 2005 varchar(Max) data type is introduced.

5.In SQL 2000 there is no DDL Triggers where as in SQL 2005 DDL Triggers is introduced .

6.In SQL 2000 there is no Database Mirroring facility where as in SQL 2005 DataBase Mirroring facility is introduced .

7.In SQL 2000 there is no RowNumber function for paging where as in SQL 2005 Row Number function for paging is introduced .

8.In SQL 2000 Table fragmentation facility is not available where as in SQL 2005 table fragmentation facility is introduced .

9.In SQL 2000 Full Text Search facility is not available where as in SQL 2005 Full Text Search facility is introduced

10. In SQL 2000 Cannot compress the tables and indexes where as in SQL 2005 Can Compress tables and indexes.(Introduced in 2005 SP2)

11.In SQL 2000 No varchar(max) or varbinary(max) is available.where as in SQL 2005 Varchar(max) and varbinary(max) is used.

12.In SQL 2000  Data Transformation Services(DTS) is used as ETL tool where as in SQL 2005 SQL Server Integration Services(SSIS) is started used as ETL tool .

13.In SQL 2000 PIVOT and UNPIVOT functions are not available where as in SQL 2005 PIVOT and UNPIVOT functions are introduced.

14.In SQL 2000 bulk copy update is not possible where as in SQL 2005 bulk copy update is possible.

15.In SQL 2000 we can't encrypt the database where as in SQL 2005 we can encrypt the database.

SQL Server - Difference between Mirroring and Log Shipping

The below are the differences between Mirroring and Log Shipping.

1.To implement Mirroring database recovery model should be FULL and SQL Server 2005 SP1 or higher with Enterprise or Developer Edition required. where as to implement Log Shipping database recovery model can be either Full or Bulk-Logged and SQL Server 2000 and even standard edition for 2005 can works.

2. In Mirroring both Principal and Mirror Server should have same edition where as in Log Shipping
Primary and secondary server should be compatible for restore.

3. Mirroring is automatic / manual failover where as Log Shipping is only Manual failover.

4. In Mirroring secondary database should be restored in with no-recovery mode where as in Log Shipping secondary database can be use restoring with no-recovery / stand-by mode.

5. In Mirroring 2 or 3 seconds only required to failover mirror database where as in Log Shipping
around 30 minutes required to failover secondary database.

6. In mirroring only committed transactions are transferred where as in Log Shipping all committed
and un-committed transactions are transferred .

7. In Mirroring we can maintain only one mirror database where as in Log Shipping we can maintain 1 / more secondary databases.

8. In Mirroring PAGE repair is possible if principal database page gets corrupt where as in Log Shipping PAGE repair is not possible.

SQL Server - Difference between SQL Server 2008 R2 and SQL Server 2012

The below are the differences between SQL Server 2008 R2 and SQL Server 2012.

1.Code name for SQL 08 R2 is Kilimanjaro where as code name for SQL 2012 is Denali .

2.In 2008 R2 exceptions can be handled using TRY….CATCH where as in SQL 2012
exceptions can be handled with THROW too .

3.In 2008 R2 high availability features such as Log Shipping, Replication, Mirroring , Clustering
are available where as in 2012 new feature ALWAYS ON is introduced with addition of 2008 features.

4.In 2008 R2 there are no Columnstore Indexes where as in 2012 new Columnstore Indexes feature that is completely unique to SQL Server. They are special type of read-only index designed to be use with Data Warehouse queries. Basically, data is grouped and stored in a flat, compressed column index, greatly reducing I/O and memory utilization on large queries.

5.In 2008 R2 maximum numbers of concurrent connections are 32767 where as in 2012 unlimited
concurrent connections.

6.TRY_CONVERT() and FORMAT() functions are newly included in SQL Server 2012 only .

7.ORDER BY Clause have OFFSET / FETCH options for paging in SQL Server 2012 only .

8.In 2012 sys.dm_exec_query_stats added four columns to help troubleshoot long running queries. You can use the total_rows, min_rows, max_rows and last_rows aggregate row count columns to separate queries that are returning a large number of rows from problematic queries that may be missing an index or have a bad query plan.

9.In 2008 Data visualization is not supported in SQL Server 2008 R2 where as in 2012
Data visualization tool is available in SQL Server 2012.This allows snapshots of data.

10.In 2008 R2 query page splitting is not available where as in SQL 2012 query page splitting is implemented.

11.In 2008 R2 maximum partition support is 1000 where as in SQL 2012 maximum partition support is increase from 1000 to 15000.

12. BI Semantic model (BISM ) is introduced  for Analysis series in SQL 2012 with 3 layers that are Data Model , Business Logic and Data Access .

13. CONCATE and FORMAT functions are introduced in SQL 2012 .

14. Buffer rate is high in 2012 because of data compression .

SQL Server - Difference between Database Mail and SQL Mail

The below are the major differences between DB Mail and SQL Mail .

1. DB Mail in introduced in SQL Server 2005 where as SQL Mail is used prior versions of SQL             Server 2005 .

2. To work with DB Mail outlook is not required but to work with SQL Mail Outlook is mandatory .

3. DB Mail is more secure than SQL Mail .

4. DB Mail will works based on SMTP (Simple Mail Transfer Protocol ) where as SQL Mail will           works on MAPI (Messaging Application Programming Interface ) .

SQL Server - Differences between Triggers and Stored Procedures

The below are the differences between Triggers and Stored Procedures .

1.Triggers cannot return a value where as stored procedures may return a value.

2.We cannot pass parameters to the triggers where as in stored procedures we can pass parameters.

3. We can write a stored procedures with in a trigger where as in procedures we can't write a trigger.

4. Triggers are implicitly fired whenever insert , update or delete operations take place on a table where procedures needs to be explicitly called by the programmer .

5. We can implement triggers on tables or views where as stored procedures can be written on the database too.

6. We cannot automate / schedule a trigger where as SP can be scheduled to execute on a predefined time.

7.We cannot use print command inside a trigger where as in SP we can user print command for debugging purpose .

8.We cannot call a trigger from the application side or from front end where as SP can be called from front end application files such as .asp , ,aspx and ascx files etc .

9.Triggers will execute automatically when an event (Inser , Update and Delete) is fired where as SP can execute by using EXEC command.

SQL Server - Differences between Views and User-Defined Functions

The below are the basic differences between Views and UDF's.

1. Views cannot accept parameters where as UDF's can accept parameters.

2. Output of the views cannot be directly used in the Select clause where as UDF's out put can use directly in Select Clause.

SQL Server - Differences between Instead of Triggers and After Triggers

The below are the basic differences between Instead of Triggers and After Tirggers.

1.Instead of Triggers will fire before the triggering action (DML) and before constraints are processed where as after triggers will fire after the triggering action -DML and after any constraints are processed.

2. Each table or view can have one Instead of Trigger for each triggering action (DML) where as
After triggers can have several triggers for each triggering action .

SQL Server - Difference between Primary Key and Foreign Key

The below are the key differences between PK and FK.

S.No	Primary Key	Foreign Key
1	Primary key uniquely identify a record in the table.	Foreign key is a field in the table that is primary key in another table.
2	Primary Key cannot accept null values	Foreign key can accept multiple null values.
3	By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.	While Foreign key is non-clustered index.
4	We can have only one Primary key in a table.	We can have more than one foreign key in a table.

SQL Server - Difference between normal Select statement and a Cursor?

The below are the key difference between Select and Cursor.

S.No	Select	Cursor
1	Select statements are used for table-level processing	Cursors are used for row-level processing

SQL Server - Alias Names

Alias Names 

Alias is a duplicate name or an alternate name for the original base table or for the original expression

using alias names we can generate meaningful reports to the end-users

It is classified into 3 types

                                * Column level alias

                                * Expression level alias

                                * Table level alias

1. Column level alias

                Providing the alias names for the columns in the table is known as column level alias .

Syntax

             Select CN1 as "Column-name1",CN2 as "column-name2" from <T.N>

Note 

           In the above example the keyword 'as' is optional and also " " is optional

If alias name contains more than oneword then only we have to use " " or [ ]

2. Expression level alias

                Providing the alias name for the expression

select empid,sal,sal*12 annualsalary from emp;

Is it possible to apply an expression on alias column?

No , Not possible

Ex

     Select empid,sal salary,sal*12 annualsalary from emp where annualsalry>50000

Note

        In the above example we will get errormessage saying that invalid columname 'annualsalary'

To avoid the above problem use expression directly

select eid employeeid,sal salary,sal*12 from emp where sal*12>50000

SQL Server - TOP Clause

TOP

                * Introduced in SQLSERVER 2005 

                * It specifies the first 'n' rows of the query result that are to be retrieved

Top with Delete 

                             Delete top(4) from emp

Top with Update

                             Update top(3) dept set sal=sal*0.9 where deptno=10

Top with Percent 

                             Select top (5) percent ename,sal from emp

                             Select top 5 percent * from emp

Top with Order By 

                            Select top 6 * from emp order by ename desc

Write a query to find duplicate records

Select deptno,ename ,count(*) from emp group by deptno,ename having count(*)>1

                                               

Write a query to delete duplicate rows?

Delete TOP 1 from T1 where a=10 and b=20 and c=30

SQL Server - Compute ..By

Compute..By

                In general we are not allowed to use group or aggregate functions after table name except                     with having clause .

                By using this clause you can perform group or aggregate functions after table name .

Ex:

                * Select * from emp compute min(start_date)

                * Select * from emp order by region compute min(hire_date) by job

                * select * from emp compute sum(sal),avg(sal),min(sal)

Notes

                The data within anglerbrackets(<>) is compulsory , where as in square brackets [] is optional in database sub-language

                The query execution order is  FROM-->T.N-->*-->WHERE

                The Precedence of clauses is  Top > Group By> having > Order By > Compute By

SQL Server - Order By Clause

Order By

                 Order By clause is used to arrange the data either ascending (or) descending order

                * By default order by clause will arrange the data in ascending order only

                * We can apply order by clause on any of the column in a table

                * If you apply order by clause on character column then it will arrange the data in                                    alphabetical order

                * We can apply the order by clause on more than one column in the table

                * We can apply the order by clause only with SELECT command

                * We cannot apply order by clause on Insert, Delete,Update commands

                * If you want to arrange the data in descending order then we have to use desc option

Syntax 

            

             Select * from <T.N> order by <CN1,CN2....CNn>[asc\desc]

SQL Server - Having Clause

Having

 After group by clause we are not allowed to use where clause , in place of this one SQL provided having clause .

Syntax : Having <Search Condition >

Note 

                Generally if you want to restrict rows then we are using where clause where as if you want to restrict groups we are using having clause.

Write a query to display deptno's having more than 5 employees?

select deptno,count(*) from emp group by deptno having count(*)>5

SQL Server - Group By Clause

Group By Clause 

Used to group similar values existing in the column of the table

It should be second clause i.e you have to use this clause after where clause only

Group By clause Rule

If you specify other than group function columns after select statement those columns must use after group by function otherwise SQL Server violates an error such as not a group by expression .

Recommendations

 Whenever we are using group by clause in a query we are not allowed to use * after select

 In general group by clause is used with group or aggregate functions .

Write a query to display no of employees working in each department?

Select deptno,count(*) from emp group by deptno

Write a query to display the no.of employees jobwise

select job,count(*) from emp group by job

SQL Server - Where Clause

Where Clause :

                             Where clause is used to filter the rows based on the search condition .

                                     with where clause

                                                * We can retrieve or modify some specific records in the table

                                                * We can delete some specific records in the table

                                                * We cannot insert the records into the table but we can copy the data                                                      from one table to another table

Syntax:   Where < Search Condition >

    The search condition can be any of the following conditions .

                          

                               *  Where with AND condition

                               *  Where with OR condition

                               *  Where with combining  AND & OR conditions etc

                               

Examples :

Write a query to create a table from an existing table

Syntax

                 Select * into <NewTableName> from <OldTableName>

 Ex:

                 Select * into new_emp from emp

In the above case it creates a table new_emp by copying all the rows and columns of the emp table

Create a table from old table without any data in it 

Syntax: 

             Select * into <NTN> from <old-tablename> where <false-condition>

Ex:

             Select * into dummy_emp from emp where 1=2

Create a table from old table only with specified columns

 Select empno,ename into test_emp from emp

Write a query to copy the data from one table to another table

Syntax :

                Insert into <NTN> select * from <OTN> where <Condition>

Ex:  

                 Insert into dummy_emp select * from emp where deptno=10

Write a query create a table with only information of sales department from emp table

Select * into sales_emp from emp where deptno=(select deptno from dept where dname='sales')

SQL Server - Difference between Identity and Sequence in SQL Server 2012

The below mentioned points are the differences between Identity and Sequence .

S.No	Identity	Sequence
1	Dependant on table.	Independent from table.
2	Identity is a property in a table. Example : CREATE TABLE Table test_Identity ([ID] int Identity (1,1),[Product Name] varchar(50))	Sequence is an object. Example : CREATE SEQUENCE [dbo].[Sequence_ID] AS [int] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000 NO CYCLE NO CACHE
3	If we need a new ID from an identity column we need to insert and then get new ID. Example : Insert into [test_Identity] Values (‘SQL Server’) GO SELECT @@IDENTITY AS ‘Identity’ –OR Select SCOPE_IDENTITY() AS ‘Identity’	In the sequence, we do not need to insert new ID we can view the new ID directly. Example : SELECT NEXT VALUE FOR dbo.[Sequence_ID]
4	We cannot perform a cycle in identity column. i.e, we cannot restart the counter after a particular interval.	In the sequence, we can simply add one property to make it a cycle. Example : ALTER SEQUENCE [dbo].[Sequence_ID]  CYCLE;
5	We cannot cache Identity column property.	Sequence can be easily cached by just setting cache property of sequence. It also improves the performance. Example : ALTER SEQUENCE [dbo].[Sequence_ID]  CACHE 3;
6	We cannot remove the identity column from the table directly.	The sequence is not table dependent so we can easily remove it
7	We cannot define the maximum value in identity column it is based on the data type limit.	Here we can set up its maximum value. Example : ALTER SEQUENCE [dbo].[Sequence_ID] MAXVALUE 2000;
8	We can reseed it but cannot change the step size. Example : DBCC CHECKIDENT (test_Identity, RESEED, 4)	We can reseed as well as change the step size. Example : ALTER SEQUENCE [dbo].[Sequence_ID] RESTART WITH 7 INCREMENT BY 2;
9	We cannot generate range from identity.	We can generate a range of sequence values from a sequence object with the help of sp_sequence_get_range.

SQL Server - Difference between User -defined SP and System-defined SP

The below are the key differences between user-defined SP and system-defined SP .

S.No	User-defined SP	System-defined SP
1	Once we create User defined SP in one database i.e available to only that database directly.i.e we cannot call it from some other DB’s directly.	System defined sp are available in master db. These sp’s can be directly called from any db.
2	UDSP will be used to fulfill the user requirements	SDSP will be used for managing sql server

SQL Server - Difference between Index Rebuild and Index Reorganize

The below are the key differences between Index Rebuild and Reorganize .

S.No	Index Rebuild	Index Reorganize
1	Index Rebuild drops the existing Index and Recreates the index from scratch.	Index Reorganize physically reorganizes the leaf nodes of the index.
2	Rebuild the Index when an index is over 30% fragmented.	Reorganize the Index when an index is between 10% and 30% fragmented
3	Rebuilding takes more server resources and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions.	Always prefer to do Reorganize the Index
4	T-SQL for Rebuilding all Indexes of a particular table. USE AdventureWorks; GO ALTER INDEX ALL ON HumanResources.Employee REBUILD GO	T-SQL for Reorganize all Indexes of a particular table. USE AdventureWorks; GO ALTER INDEX ALL ON HumanResources.Employee REORGANIZE GO

SQL Server - Difference between IN and EXISTS

The below are the key differences between IN and EXISTS

S.No	IN	EXISTS
1	Returns true if specified value matches any value in the sub query or a list.	Return true if sub query contain any rows.
2	The sub query will run first and then only outer query.	The Outer query will ran first and then only sub query.
3	IN is slower than EXISTS. The IN is used in the widely For Static variables for eg: select name from table where ID in (Select ID from table2).	Exists is faster than IN.The Outer query will run first and then only inner query. So it will reduce the over head. The Exists is useful mostly in IF conditional statements.
	Example: SELECT id, [Name] FROM dbo.table a WHERE id IN (SELECT id FROM dbo.table b)	Example: SELECT id, [Name] FROM dbo.table a WHERE EXISTS (SELECT id FROM dbo.table b WHERE b.id = a.id)

SQL Server - Difference between View and Stored Procedure

The below mentioned points are key differences between View and SP .

S.No	View	Stored Procedure
1	View doesn't accepts parameters	SP accept parameters
2	View Can be used as a building block in large query.	SP Cannot be used as  building block in large query.
3	View can contain only one single Select query.	SP Can contain several statement like if, else, loop etc .
4	We cannot perform modification to any table using Views.	We can perform modification to one or several tables.
5	View can be used (sometimes) as the target for Insert, update, delete queries.	Cannot be used as the target for Insert, update, delete queries.

6       View is a stored query.                                            SP is reusable code component.

7.      Select is used to execute                                          EXEC is used to execute

SQL Server - Difference between Cross Join and Full Outer Join

The below are the key points between Cross Join and Full Outer Join .

S.No	Cross Join	Full Outer Join
1	No join conditions are specified.	A combination of both left and right outer joins.
2	Results in pairs of rows.	Results in every row from both of the tables , at least once.
3	Results in Cartesian product of two tables.	Assigns NULL for unmatched fields.

SQL Server - Difference between Local temporary table and Global temporary table .

The below are the few crucial differences between local and global temporary tables .

S.No	Local temporary table	Global temporary table
1	Denoted by # symbol.	Denoted by ## symbol.
2	Valid for the current connection only. and cleared as soon as the current connection closes	Available to all the connections once created. and cleared when the last connection is closed.
3.	Cannot be shared between multiple users.	Can be shared between multiple users.

SQL Server - Difference between Temp table and Table variable ?

The below are the few differences between Temp table and table variable .

1.We can create and backup data of temporary table but its not possible in case of table variable.

2.Temporary table structure can be modified after its creation i.e it will support DDL Statements
where as table variable doesn't support it.

3.Temp tables will be stored in the temp db and creates network traffic when its size increases due to this performance issues will occur where as table variables will be stored in physical memory later size increases it will be moved to temp db.

4.Temp tables will participate in the explicit transactions defined by the user where as table variables doesn't participate in the explicit transactions defined by the user.

5.Temporary tables are not allowed in user defined functions where as table variables can be used in user defined functions.

6.Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.
where as
Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration i.e we cannot create non-clustered indexes on table variables.

7.Scope of the temp table is either local or global depending on declaration where as table variable scope is with in the batch or stored procedure in which it is declared.

8.Temp tables can be dropped itself or by the user manually where as table variables will be dropped automatically when batch execution or stored procedure execution completes.

Where should we use MongoDB

   *  Mobile and Social Infrastructure.

   * Big Data.

   *  MongoDB has built in special functions, so finding relevant data from specific locations is fast            and  accurate.

   * Your Data Set is going to be big (starting from 1GB) and Schema is not stable.

    * You need to grow big i.e Databases scaling is hard (Ex In any RDBMS table performance

        will degrade when crossing the 10-15GB per table). If you need to partition and shard your 

        Database, MongoDB has a built in easy solution for this .

Why should use MongoDB

   * Rich queries

   * Document oriented database.

   * Index on any attribute.

   * Professional support by MongoDB.

SQL Server Backup Best Practices

          Best Practice recommendations for SQL Server Database Backups

       1.       All OLTP production databases should be set to use the full recovery model. This way, you can create transaction log backups on a periodic basis.

             2.       Whenever possible, perform a daily full backup of all system and user databases .

               

                     (Note : Recommended as per your infrastructure standards for the project )

             3.       For all OLTP production databases, perform regular transaction log backups, at least once an hour.

             4.       Perform full backups during periods of low user activity in order to minimize the impact of backups on users.

             5.       Periodically perform test backup restores to ensure that your backups are good and can be restored.

             6.       Encrypt your backups in case they should become “lost.”

             7.       Store backups offsite and in a secure location.

             8.       If using SQL Server encryption, be sure to back up the appropriate service master keys,database master keys, and certificates.

             9.       If you find that backup times take longer than your backup window, or if backup file sizes are taking up too much space on your storage device, consider a third-party backup program, such as Red Gate SQL Backup Pro and HP Data Protector etc.

             10.   Document, step-by-step, the process to restore system and user databases onto the same, or a different server. You don’t want to be looking this information up during an emergency and also it would be useful for upcoming DBA's in your environment. 

SQL Server - Differences between Union and UnionAll

The below are the differences between Union and Union ALL .

S.No	Union	UnionAll
1	This is used to eliminate duplicate rows	It will not eliminate duplicate rows
2	t can be used to combine any number of queries	It can be used to combine maximum of 2 queries
3	It cannot contain aggregate functions	It can contain aggregate functions
4	Union is slower than UnionAll	UnionAll is faster than Union
5	Output is in sorted order Example : SELECT Col FROM @Table1 UNION SELECT Col FROM @Table2 Result: 1 2 3 5	Output is not in sorted order Example : SELECT Col FROM @Table1 UNION ALL SELECT Col FROM @Table2 Result: 1 2 3 2 5

SQL Server - Differences between Where and Having clauses?

The below are the differences between Where and Having Clause.

S.No	Where	Having
1	It applies to individual rows	It applies to a group as a whole
2	It selects rows before grouping	It selects rows after grouping
3	It cannot contain aggregate functions	It can contain aggregate functions
4	It can be used in select, delete ,insert etc.	It is used only in select clause

SQL Server - Differences between Stored Procedures and User Defined Functions

The below are the key differences between SP and UDF's .

S.No	Stored Procedures	User Defined Functions
1	Stored Procedure cannot be used in a Select statement	User Defined Function can be used in a Select statement
2	Stored procedure supports Deferred Name Resolution	User Defined Function does not support Deferred Name Resolution
3	Stored Procedures are generally used for performing Business Logic	User Defined Functions are generally used for Computations
4	Stored Procedure need not return a value	User Defined Functions should return a value
5	Stored Procedures can return any datatype	User Defined Functions cannot return Image
6	Stored Procedures can accept more number of input parameters than User Defined Functions. Stored Procedures can have upto 21000 input parameters	User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters
7	Stored Procedures can use Temporary Tables	Temporary Tables cannot be used in a User Defined Function
8	Stored Procedures can execute Dynamic SQL	User Defined Functions cannot execute Dynamic SQL
9	Stored Procedure supports error handling	User Defined Function does not support error handling.  RAISEERROR or @@ERROR are not allowed in UDFs
10	Non-deterministic functions can be used in Stored Procedures.	Non-deterministic functions cannot be used in User Defined Functions (UDFs).  For example, GETDATE() cannot be used in User Defined Functions(UDFs)

11      We can implement transactions in SP                    We cannot implement transactions in UDF's.

12     We can use DML,DDL and DQL commands          We can use only DQL (Select) in UDF's

         in SP

13.    To execute SP we have to use EXEC                       To execute function we have to use 'Select'

14.    Procedure will return a value                                     Function returns a value if return type is 

         with OUTparameters                                                 INT and returns a row if returns type is                                                                                                     table