This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What is the difference between Varchar and NVarchar data type?
varchar: Variable-length, non-Unicode character data. The database collation determines which code page the data is stored using.
nvarchar: Variable-length Unicode character data. Dependent on the database collation for comparisons.
Essentially, NVARCHAR is nothing more than a VARCHAR that supports two-byte characters. The most common use for this sort of thing is to store character data that is a mixture of English and non-English symbols.
The key difference between the two data types is how they’re stored.
What is the difference between Varchar(max) and TEXT data type?
The VARCHAR(MAX) type is a replacement for TEXT. The basic difference is that a TEXT type will always store the data in a blob whereas the VARCHAR(MAX) type will attempt to store the data directly in the row unless it exceeds the 8k limitation and at that point it stores it in a blob.
The legacy types (TEXT, NTEXT, IMAGE) are deprecated and support for them will be dropped in a future version of SQL Server.
What is the difference between CHAR and VARCHAR?
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.
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.
What is the difference between Datetime and Datetime2?
DateTime2 is the new Data Type introduced in SQL Server 2008 for storing Date and Time value. Microsoft Suggests to use this new Data Type instead of DateTime that is mainly available for backwards compatibility.
Min and Max values:
- DateTime: BETWEEN ‘1753-01-01 00:00:00’ AND ‘’9999-12-31 23:59:59.997’
- DateTime2: BETWEEN ‘0001-01-01 00:00:00’ AND ‘9999-12-31 23:59:59.9999999’
Storage Size:
- DateTime: 8 Bytes
- DateTime2(n): 6 to 8 bytes
Precision:
- DateTime – only allows for precision up to every 3rd milisecond which essentially means that you can get rounding issues for times that are in fact different. The rounding is to increments of .000, .003, or .007 seconds.
- DateTime2(n) – The precision scale is 0 to 7 digits, with an accuracy of 100 nanoseconds . The default precision is 7 digits.
What is the difference between HAVING and WHERE Clause?
- HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.
- HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
- A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause, then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output.
What is the difference Between GETDATE and SYSDATETIME?
In case of GETDATE the precision is till miliseconds and in case of SYSDATETIME the precision is till nanoseconds.
What is the difference between THROW and RAISERROR?
When using RAISERROR function, it just returns the last (single) error message and its details, but the previous error message details are not returned by this function.
With the new THROW clause you won’t see any issue of omitting the previous errors. And it returns all error details as thrown by the SQL statement itself.
What is bit data type?
Bit datatype is used to store boolean information like 1 or 0 (true or false).
Bit datatype can represent a Null state.
What is the difference between SMALLINT and TINYINT?
The storage of TinyINT is 1 byte and can store range from 0 to 255.
The storage of SmallINT is 2 bytes and can store range from -32768 to 32767.
What is the Difference Between Cast and Convert?
- CAST and CONVERT are both used to convert data from one data type to another.
- CAST is part of the ANSI-SQL specification; whereas, CONVERT is not. In fact, CONVERT is SQL implementation specific.
- CONVERT differences lie in that it accepts an optional style parameter which is used for formatting.
- For example, when converting a DateTime datatype to Varchar, you can specify the resulting date’s format, such as YYYY/MM/DD or MM/DD/YYYY.
- SELECT CONVERT(VARCHAR,GETDATE(),101) as MMDDYYYY, CONVERT(VARCHAR,GETDATE(),111) as YYYYMMDD
Have you ever stored XML type of data into SQL Server?
Yes: Using XML Data type, we can store XML formatted data into SQL Server.
XML is a good choice if you want a platform-independent model in order to ensure portability of the data by using structural and semantic markup.
What is MSDTC?
The Microsoft Distributed Transaction Coordinator is a transaction manager that allows client applications to include several different sources of data in one transaction.
MSDTC coordinates committing the distributed transaction across all the servers enlisted in the transaction.
What is Distributed Queries in SQL Server?
Distributed query access data from multiple heterogeneous data sources. These data sources can be stored on either the same or different computers. It supports distributed queries by using OLEDB.
What is a Two-Phase Commit?
In transaction processing, databases, and computer networking, the two-phase commit protocol (2PC) is a type of atomic commitment protocol (ACP). It is a distributed algorithm that coordinates all the processes that participate in a distributed atomic transaction on whether to commit or abort (roll back) the transaction.
Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
Prepare Phase : Global coordinator asks participants to prepare
Commit Phase : Commit all participants to coordinator to Prepared, Read only or abort Reply.
What do you know about the SQL Server parameter sniffing?
How do we recompile a stored procedure at run time?
Using WITH RECOMPILE hint option, we can recompile stored procedure at run time.
What is a SQL_VARIANT?
- It is a data type that stores values of various SQL Server-supported data types.
- sql_variant can be used in columns, parameters, variables, and the return values of user-defined functions. sql_variant enables these database objects to support values of other data types.
- A column of type sql_variant may contain rows of different data types. For example, a column defined as sql_variant can store int, binary, and char values.
- sql_variant can have a maximum length of 8016 bytes.
What kind of data types which are not supported by SQL_VARIANT?
- varchar(max)
- varbinary(max)
- nvarchar(max)
- xml
- text
- ntext
- image
- rowversion (timestamp)
- sql_variant
- geography
- hierarchyid
- geometry
- User-defined types
- datetimeoffset
Would you please explain the logic to split a comma separated values?
SQL Server:Function to Split String Value Using Different Delimiters
