Difference Between Truncate and Delete
This is one of the most important questions generally asked in the interview.
Truncate releases allocated memory but Delete operation doesn't release allocated memory.
Truncate is faster then Delete because it perform the operation on Table but delete performs operation on each row.
1. First we understand what type of command they are. DELETE is the DML command and TRUNCATE is the DDL Command.
2. No WHERE condition is used with TRUNCATE, so it effects on entire Table Objects.
3. The TRUNCATE command NOT fire any TRIGGER related to Table, as it is not delete data row wise.
4. Drop all object's statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table
5. If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column
6. Restrictions on using Truncate Statement
a. Are referenced by a FOREIGN KEY constraint
b. Participate in an indexed view.
c. Are published by using transactional replication or merge replication.
Now What the Advantage of TRUNCATE rather than DELETE
1. It is Very FAST
2. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data.
Delete
Truncate
Command
DELETE FROM <<TableName>>
TRUNCATE TABLE <<TableName>>
Speed
Slower
Faster
WHERE
Can be applied
Cannot be applied
IDENTITY
Cannot reset
Will reset
TRIGGER
Will fire
Won’t fire
Transaction Log
Maintains record level log
Maintains page level log
Referential Integrity
If Child table doesn’t have corresponding record from master table then it will allow
Will never ever allow if any referential integrity exists; no matter child table has record or not
Table Variable
Can be deleted
Cannot be truncated
CDC
Will allow
Won’t allow if CDC is enabled on table
Rollback
Can be rolled back
Can be rolled back (yes, this is true, Truncate can be rolled back)
Difference Between Temp Table and Variable Table
This is one of the most important questions generally asked in the interview. Please give your valuable suggestions and feedback to improve this article.
There is one more difference, Table Variable will not work in if you use in Dynamic SQL.
Temporary table and Table variable
In this article I am trying to collects some facts related to temporary table and table variable. So let's start about it.
The concepts of the temporary tables, which help developer a great, relax to maintain T-SQL statements. This table is created on run time and can do the all kind of operations that normal table can do. But based on the type of table the scope is limited.
The temp tables are created in the tempdb database. So before starting about temp table, let's take a quick look about tempdb database.
Tempdb database
It is a system database global resource that is available to all users. Some facts are mentioned bellow.
1. Temporary use objects that are explicitly created such as, global and local
temporary tables, temporary stored procedure, table variables or cursors.
2. Internal objects created by SQL Server database engine. For example work
tables to store intermediate result for spools or sorting.
3. Row versions that re generated by data modifications transactions in a database
that use read-committed using row versioning isolation or snapshot isolation transaction.
4. Row version that are generated by data modification transactions for feature.
Type of temporary table
Based on behavior and scope the temporary table is divided into two categories.
1. Local Temp Table
2. Global Temp Table
Local temp table
Local temp tables are only available to the current connection for the user and automatically deleted when the user disconnects from instances. It is started with # sign.
Example:
CREATE TABLE #tbl_localTemp
(sRoll Int,
sName Varchar(50),
sClass Int)
GO
Global Temp table
Once the table has been created by a connection, like a permanent table it then available to any user by any connection. It is only be deleted when all connections have been closed. Global temp table name starts with ##.
Example:
CREATE TABLE ##tbl_localTemp
(sRoll Int,
sName Varchar(50),
sClass Int)
GO
Some points we must remember related to temporary table
1. As they are created in a separate database named tempdb, so additional overhead and can causes performance issue.
2. Number of rows and columns need to be minimised as we needed.
Table variable
Alternate of the temporary table is table variable. Which can do all kinds of operation we can do with the temporary table. It is always useful for less data. If result set returns large amount of data we use the temporary table.
Example:
DECLARE @tblvrbl_local TABLE
(sRoll Int,
sName Varchar(50),
sClass Int)
GO
1. Table variable are transaction neutral. They are variables and are not bound to a transaction
2. Temp table behave same as normal table and are bound by transaction.
Difference between Stored Procedure and Function
Differences between char and varchar
1. Data type char is example of fixed length memory allocation. For example:
DECLARE @Value AS CHAR(10) = 'Exact'
SELECT DATALENGTH(@Value)
Output: 10
So, Sql sever has allocated 10 byte memory spaces while total number of characters in the constant 'Exact' is only 5.
Data type varchar is example of variable length memory allocation. For example:
DECLARE @Value AS VARCHAR(10) = 'Exact'
SELECT DATALENGTH(@Value)
Output: 5
So in case of varchar sqlsever use dynamic allocation.
2. If total numbers of characters is less than size of char data type then sql sever embeds that number of space characters at the end but not in varchar data type. For example:
What will be output of following sql query ?
DECLARE @First AS CHAR(10) = 'Exact'
DECLARE @Last AS CHAR(10) = ' Help'
IF (@First + @Last = 'Exact Help')
SELECT 'Equal'
Differences between char and varchar
1. Data type char is example of fixed length memory allocation. For example:
DECLARE @Value AS CHAR(10) = 'Exact'
SELECT DATALENGTH(@Value)
Output: 10
So, Sql sever has allocated 10 byte memory spaces while total number of characters in the constant 'Exact' is only 5.
Data type varchar is example of variable length memory allocation. For example:
DECLARE @Value AS VARCHAR(10) = 'Exact'
SELECT DATALENGTH(@Value)
Output: 5
So in case of varchar sqlsever use dynamic allocation.
2. If total numbers of characters is less than size of char data type then sql sever embeds that number of space characters at the end but not in varchar data type. For example:
What will be output of following sql query ?
DECLARE @First AS CHAR(10) = 'Exact'
DECLARE @Last AS CHAR(10) = ' Help'
IF (@First + @Last = 'Exact Help')
SELECT 'Equal'
ELSE
SELECT 'Not Equal'
Output: Not Equal
Explanation: In memory variable @First has stored like 'Exact ' and variable @Second ' Help '
So, @First + @Last = 'Exact Help '
It is obvious that is not equal to 'Exact Help'
3. When ANSI_PADDING is off then a column of any table of type CHAR NULL is same as VARCHAR NULL. For example:
Example 1:
SET ANSI_PADDING OFF
CREATE TABLE CharTest_1(
Value CHAR(10) NULL
)
INSERT INTO CharTest_1 VALUES ('Exact'),('Help')
SELECT DATALENGTH(Value) FROM CharTest_1
Output:
5
4
Explanation: Here sql server is treating CHAR(10) NULL as VARCHAR(10) NULL.
Example 2:
SET ANSI_PADDING ON
CREATE TABLE CharTest_1(
Value CHAR(10) NULL
)
INSERT INTO CharTest_1 VALUES ('Exact'),('Help')
SELECT DATALENGTH(Value) FROM CharTest_1
Output:
10
10
Example 2:
SET ANSI_PADDING OFF
CREATE TABLE CharTest_2(
Value CHAR(10) NOT NULL
)
INSERT INTO CharTest_2 VALUES ('Exact'),('Help')
SELECT DATALENGTH(Value) FROM CharTest_2
Output:
SELECT 'Not Equal'
Output: Not Equal
Explanation: In memory variable @First has stored like 'Exact ' and variable @Second ' Help '
So, @First + @Last = 'Exact Help '
It is obvious that is not equal to 'Exact Help'
3. When ANSI_PADDING is off then a column of any table of type CHAR NULL is same as VARCHAR NULL. For example:
Example 1:
SET ANSI_PADDING OFF
CREATE TABLE CharTest_1(
Value CHAR(10) NULL
)
INSERT INTO CharTest_1 VALUES ('Exact'),('Help')
SELECT DATALENGTH(Value) FROM CharTest_1
Output:
5
4
Explanation: Here sql server is treating CHAR(10) NULL as VARCHAR(10) NULL.
Example 2:
SET ANSI_PADDING ON
CREATE TABLE CharTest_1(
Value CHAR(10) NULL
)
INSERT INTO CharTest_1 VALUES ('Exact'),('Help')
SELECT DATALENGTH(Value) FROM CharTest_1
Output:
10
10
Example 2:
SET ANSI_PADDING OFF
CREATE TABLE CharTest_2(
Value CHAR(10) NOT NULL
)
INSERT INTO CharTest_2 VALUES ('Exact'),('Help')
SELECT DATALENGTH(Value) FROM CharTest_2
Output:
10
10
4. VARCHAR keep additional two bytes at the end to keep the length of the string while CHAR is fixed length so it doesn't keep the length information.
5. When we perform update query on CHAR column is doesn't move that row since data always fits in the allocated space while when we update VARCHAR column then there is need to move the row.
Advantage of using CHAR data type in sql server:
1. Query performance is better since no need to move the column while updating.
2. No need to store the length of string in last two bytes.
Disadvantage of using CHAR data type in sql server:
1. It takes too much more spaces than varchar since it is fixed length and we don't know the length of string which to be store.
2. It is not good for compression operation since it embeds space characters at the end.
Advantage of using VARCHAR data type in sql server:
1. Since it is variable length so it takes less memory spaces.
Disadvantage of using VARCHAR data type in sql server:
1. Decrease the performance of some sql queries.
So, we should use CHAR data type of a column only if we known the length of the string is fixed otherwise we should always use varchar.
How to perform case sensitive comparison in sql server
Sql server string comparison is case sensitive or not
Insert default value when data or parameter is null in sql server
SQL SERVER PPT
Reference: http://www.exacthelp.com/2012/01/difference-between-char-and-varchar-in.html
What are differences between varchar and nvarchar or char and nchar in sql server?
1. Data type nvarchar always use 2 bytes to store a single character while varchar use one byte or two byte depending upon collation. For example:
DECLARE @Str1 VARCHAR(5) = 'A'
DECLARE @Str2 NVARCHAR(5) = N'A'
SELECT
DATALENGTH(@Str1) [Varchar],
DATALENGTH(@Str2) [NVarchar]
Output:
Varchar
NVarchar
2. Data type nvarchar stores the characters in Unicode which uses Unicode universal code page (UCS-2) while varchar uses many different types of code pages
3. In data type varchar(n)
n can be 1<= n <= 8000
While In data type nvarchar(n)
n can be 1<= n <= 4000
4. Data type nvarchar support all type of collation while varchar supports only those collations which use one byte to store a single character. For example:
It is right to declare:
DECLARE @Str2 NVARCHAR(5) = N'A' COLLATE Bengali_100_CI_AI
But not this:
DECLARE @Str1 VARCHAR(5) = 'A' COLLATE Bengali_100_CI_AI
Due to Collation 'Bengali_100_CI_AI' is supported on Unicode data types only and cannot be applied to char, varchar or text data types.
When we should use varchar and nvarchar data type for a column of a table in sql server
We should use varchar when there is no need to store the data of different languages that is collation which requires only one byte to store a single character since:
1. Varchar saves 50% memory space than nvarchar
2. Query execution with varchar is faster than nvachar since due to less size less number of pages will have to search.
We should use nvarchar only if there is need to store the data of different languages that is collation which requires two one bytes to store a single character.
Sql server temporary stored procedures
Foreign key constraint sql server | Referential constraint
IF statement or condition in WHERE clause of SELECT statement in sql server
Sql server create unique constraint which allow multiple NULL values
Dynamic ORDER BY clause in sql server
How to compile any query without executing it in sql server
Reference: http://www.exacthelp.com/2012/01/difference-between-varchar-and-nvarchar.html
IN Vs EXISTS
Every developer knows about IN and EXISTS clause. Depends on situation to situation we use them. Here in this article we are typing to make some differences between them
What to use IN or EXISTS
We recommended to use the EXISTS instead of IN as performance wise EXISTS gives us better output.
Syntax wise Differences
Syntax of EXISTS
SELECT *
FROM tbl_Customers
WHERE Exists (SELECT *
FROM tbl_Orders
WHERE Orders.CustomerID = Customers.ID)
Syntax of IN
SELECT *
FROM tbl_Customers
WHERE ID IN (SELECT CustomerID
FROM tbl_Orders)
We can Use Join
SELECT Customers.*
FROM Customers
INNER JOIN Orders ON Customers.ID = Orders.CustomerID
Here if we think about the performance factors in mind, we recommended the order of Using Join, Using EXISTS and Last Order is Using IN Clause.
Disadvantage of Using IN clause
As we all know that IN clause decrees the performance of query, beside this is another disadvantage we find in IN clause.
Generally we are writing IN clause in this fashion
WHERE id IN (1,2,3,4)
But there is a limitation of Number of item within IN Clause and that is 10000
WHERE id IN (1,2,3,4,.....10000)
We can use maximum of 10,000 items within IN clause.
