Sql Server Difference FAQs-9

1.Difference between VARCHAR and NVARCHAR in SQL Server

S.No	Varchar[(n)]	NVarchar[(n)]
1	Basic Definition: Non-Unicode Variable Length character data type. Example: DECLARE @FirstName AS VARCHAR(50) = ‘UMAR’ SELECT @FirstName	Basic Definition: UNicode Variable Length character data type. It can store both non-Unicode and Unicode (i.e. Japanese, Korean etc) characters. Example: DECLARE @FirstName AS NVARCHAR(50)= ‘UMAR’ SELECT @FirstName
2	No. of Bytes required for each character: It takes 1 byte per character Example: DECLARE @FirstName AS VARCHAR(50) = ‘UMAR’ SELECT @FirstName AS FirstName,DATALENGTH(@FirstName) AS Length Result: FirstName Length UMAR 4	No. of Bytes required for each character: It takes 2 bytes per Unicode/Non-Unicode character. Example: DECLARE @FirstName AS NVARCHAR(50)= ‘UMAR’ SELECT @FirstName AS FirstName,DATALENGTH(@FirstName) AS Length Result: FirstName Length UMAR 8
3	Optional Parameter n range: Optional Parameter n value can be from 1 to 8000.Can store maximum 8000 Non-Unicode characters.	Optional Parameter n range: Optional Parameter n value can be from 1 to 4000.Can store maximum 4000 Unicode/Non-Unicode characters
4	If Optional Parameter n is not specified in the variable declaration or column definition: If Optional parameter value is not specified in the variable declaration or column definition then it is considered as 1. Example: DECLARE @firstName VARCHAR =‘UMAR’ SELECT @firstName FirstName,DATALENGTH(@firstName) Length Result: FirstName Length U 1	If Optional Parameter n is not specified in the variable declaration or column definition: If Optional parameter value n is not specified in the variable declaration or column definition then it is considered as 2 Example: DECLARE @firstName NVARCHAR =‘UMAR’ SELECT @firstName FirstName,DATALENGTH(@firstName) Length Result: FirstName Length U 2
5	If Optional Parameter n is not specified in while using CAST/CONVERT functions:	If Optional Parameter n is not specified in while using CAST/CONVERT functions:
	When this optional parameter n is not specified while using the CAST/CONVERT functions, then it is considered as 30. Example: DECLARE @firstName VARCHAR(35) =‘UMAR ASIA INDIA TAMIL NADU CUDDALORE’ SELECT CAST(@firstName AS VARCHAR) FirstName,DATALENGTH(CAST(@firstName AS VARCHAR)) Length Result: FirstName Length UMAR ASIA INDIA TAMIL NADU CUD 30	When this optional parameter n is not specified while using the CAST CONVERT functions, then it is considered as 30. Example: DECLARE @firstName NVARCHAR(35) =‘UMAR ASIA INDIA TAMIL NADU CUDDALORE’ SELECT CAST(@firstName AS NVARCHAR) FirstName,DATALENGTH(CAST(@firstName AS NVARCHAR)) Length Result: FirstName Length UMAR ASIA INDIA TAMIL NADU CUD 60
7	Which one to use? If we know that data to be stored in the column or variable doesn’t have any Unicode characters.	Which one to use? If we know that data to be stored in the column or variable can have Unicode characters.
8	Storage Size: Takes no. of bytes equal to the no. of Characters entered plus two bytes extra for defining offset.	Storage Size: Takes no. of bytes equal to twice the no. of Characters entered plus two bytes extra for defining offset.

2.Difference between SQL Server and MySQL

S.No	SQL Server	MySQL
1	Current Date and Time: SELECT GETDATE()	Current Date and Time: SELECT NOW() Optionally: Use CURDATE() for the date only.
2	Limiting Results: SELECT TOP 10 * FROM table WHERE id = 1	Limiting Results: SELECT * FROM table WHERE id = 1 LIMIT 10
3	Date Field Default Value: DATETIME DEFAULT GETDATE()	Date Field Default Value: DATETIME fields cannot have a default value, i.e. "GETDATE()" We must use your INSERT statement to specify CURDATE() for the field. Optionally: Use datatype TIMESTAMP DEFAULT CURRENT_TIMESTAMP
4	Character Length: LEN()	Character Length: CHARACTER_LENGTH() Aliases: CHAR_LENGTH(), LENGTH()
5	Character Replace: REPLACE() works case insensitively	Character Replace: REPLACE() works case sensitively
6	Trim Functions: LTRIM() and RTRIM()	Trim Functions: TRIM()
7	String Concatenation: CONCATENATION USING + (Does not automatically cast operands to compatible types)	String Concatenation: CONCAT(string, string), which accepts two or more arguments. (Automatically casts values into types which can be concatenated)
8	Auto Increment Field Definition: tablename_id INT IDENTITY PRIMARY KEY	Auto Increment Field Definition: tablename_id INTEGER AUTO_INCREMENT PRIMARY KEY
9	Get a List of Tables: SP_TABLES	Get a List of Tables: SHOW TABLES
10	Get Table Properties: HELP tablename	Get Table Properties: DESCRIBE tablename
11	Get Database Version: SELECT @@VERSION	Get Database Version: SELECT VERSION()
12	Recordset Paging: Recordset paging done by client side-ADO (very involved)	Recordset Paging: Add to end of SQL: "LIMIT " & ((intCurrentPage-1)*intRecsPerPage) & ", " & intRecsPerPage LIMIT: The first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).
13	Get ID of Newest Inserted Record: SET NOCOUNT ON; INSERT INTO...; SELECT id=@@IDENTITY; SET NOCOUNT OFF;	Get ID of Newest Inserted Record: Two step process: 1. Execute your statement: objConn.Execute("INSERT INTO...") 2. Set objRS = objConn.Execute("SELECT LAST_INSERT_ID() AS ID")
14	Get a Random Record: SELECT TOP 1 * FROM Users ORDER BY NEWID()	Get a Random Record: SELECT * FROM Users ORDER BY RAND() LIMIT 1
15	Generate a Unique GUID: SELECT NEWID()	Generate a Unique GUID: SELECT UUID()
16	Licensing: SQL Server is not an open source and payment has to be made to use SQL Server.	Licensing: MySQL is available for free since MySQL is an open source.
17	View Support: SQL Server offers indexed views which are much more powerful, performance wise.	View Support: MySQL offers only updateable views.
18	XML Support: SQL Server supports XML.	XML Support: MySQL does not support XML.
19	Security: SQL Server provides column level security.	Security: MySQL provides only table level security.
20	Certiication for Security: SQL Server has C2 compliant certification. Database security is verified by third party.	Certiication for Security: MySQL does not offer any certification for security.
21	Support for Triggers: SQL Server provides triggers.	Support for Triggers: Earlier versionsof MySQL does not support triggers. Only MySQL 5.0 supports triggers.
22	Support for UDF: User defined functions are supported in SQL Server.	Support for UDF: User defined functions are not supported in MySQL.
23	Support for Cursors: Cursor feature is available in SQL Server.	Support for Cursors: Cursor feature is not available in MySQL.
24	Support for SPs and Joins: Stored procedures and full join facility is not offered in MySQL.	Support for SPs and Joins: Stored procedures and full join facility are offered in SQL Server.
25	Support for Import/Export Functions: Import and export are extensively supported in MySQL.	Support for Import/Export Functions: Import and Export functions have very limited support in MySQL.
26	Support for Transaction: Transaction support is extensively and fully offered in SQL Server.	Support for Transaction: Transaction support is very much limited in MySQL.
27	Support for Replication: Replication support is extensively and fully offered in SQL Server.	Support for Replication: Replication support is very much limited in MySQL.
28	Support for auto tuning: Auto tuning is supported in SQL Server.	Support for auto tuning: Auto tuning is not supported in MySQL.
29	Support for job scheduling and profiling: Job scheduling and profiling are available in SQL Server.	Support for job scheduling and profiling: Job scheduling and profiling are not available in MySQL.
30	Support for online backup and clustering: Online backup support and clustering support is extensive and complete in SQL Server.	Support for online backup and clustering: Online backup support and clustering support is limited in MySQL.
31	Support for Log shipping and SAN: Log Shipping and Storage Area Network support is available in SQL Server.	Support for Log shipping and SAN: Log Shipping and Storage Area Network support is not available in MySQL.
32	Support for OLAP Services, Data Reporting and Data Mining: OLAP Services, Data Reporting and Data Mining are supported in SQL Server.	Support for OLAP Services, Data Reporting and Data Mining: OLAP Services, Data Reporting and Data Mining are not supported in MySQL.

3.Difference between SET QUOTED_IDENTIFIER ON and SET QUOTED_IDENTIFIER OFF in SQL Server

S.No	SET QUOTED_IDENTIFIER ON	SET QUOTED_IDENTIFIER OFF
1	Characters Enclosed within double quotes: is treated as Identifier	Characters Enclosed within double quotes: is treated as Literal
2	Try using Characters Enclosed within double quotes as identifier: Works Example: Below statement to create a table with table name “Table” succeeds. SET QUOTED_IDENTIFIER ON GO CREATE TABLE dbo.”Table” (id int,”Function” VARCHAR(20)) GO	Try using Characters Enclosed within double quotes as identifier: Fails Example: Below statement to create a table with table name “Table” Fails. SET QUOTED_IDENTIFIER OFF GO CREATE TABLE dbo.”Table” (id int,”Function” VARCHAR(20)) GO Error Message: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ‘Table’.
3	Try using Characters Enclosed within double quotes as Literal: Fails Example: Below statement fails. SET QUOTED_IDENTIFIER ON GO SELECT “BIRADAR” Error Message: Msg 207, Level 16, State 1, Line 1 Invalid column name ‘UMAR’.	Try using Characters Enclosed within double quotes as Literal: Works Example: Below Statement Works. SET QUOTED_IDENTIFIER OFF GO SELECT “UMAR”
4	Characters Enclosed within single quotes: is treated as Literal Example: SET QUOTED_IDENTIFIER ON GO SELECT ‘UMAR’	Characters Enclosed within single quotes: is treated as Literal Example: SET QUOTED_IDENTIFIER ON GO SELECT ‘UMAR’
5	How to find all the objects which are created with SET QUTOED_IDENTIFIER ON/OFF: Below Statement can be used to find all the objects created with SET QUTOED_IDENTIFIER setting as ON: SELECT OBJECT_NAME (object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 1	How to find all the objects which are created with SET QUTOED_IDENTIFIER ON/OFF: Below Statement can be used to find all the objects created with SET QUTOED_IDENTIFIER setting as OFF: SELECT OBJECT_NAME (object_id) FROM sys.sql_modules WHERE uses_quoted_identifier = 0

4.Difference between DateTime and DateTime2 DataType

S.No	DateTime	DateTime2[(n)]
1	Min Value: 1753-01-01 00:00:00	Min Value: 0001-01-01 00:00:00
2	Max Value: 9999-12-31 23:59:59.997	Max Value: 9999-12-31 23:59:59.9999999
3	Storage Size: 8 Bytes	Storage Size: 6 to 8 bytes Note: Parameter n is optional and if it is not specified then fractional seconds precision is 7 digit and it can be from 0 to 7 digit. For fractional seconds precision <3 6="6" bytes="bytes" font="font" takes="takes"> For fractional seconds precision 3 or 4 it will take 7 bytes For fractional seconds precision >4 it will take 8 bytes
4	Usage: Declare @now datetime	Usage: Declare @now datetime2(7)
5	Current Date and Time function: GetDate() – It returns DB Current Date and Time of DateTime Data Type Example: SELECT GETDATE() Result: 2011-09-16 13:23:18.767	Current Date and Time function: SYSDATETIME()- It returns DB Current Date and Time of DateTime2 Data Type Example: SELECT SYSDATETIME() Result: 2011-09-16 13:23:18.7676720
6	+/- days: WORKS Example: DECLARE @nowDateTime DATETIME = GETDATE() SELECT @nowDateTime + 1 Result: 2011-09-17 13:44:31.247	+/- days: FAILS – Need to use only DateAdd function Example: DECLARE @nowDateTime2 DATETIME2= SYSDATETIME() SELECT @nowDateTime2+1 Result: Msg 206, Level 16, State 2, Line 2 Operand type clash: datetime2 is incompatible with int
7	Compliance: Is not an ANSI/ISO compliant	Compliance: Is an ANSI/ISO compliant