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

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.