Difference between SQL Truncate & SQL Delete
1. TRUNCATE is a DDL (Data Definition Language) command and DELETE is a DML (Data Manipulation Language) command.
2. You can use WHERE clause with DELETE but not with TRUNCATE .
3. You cannot rollback data in TRUNCATE but in DELETE it is possible to rollback data.
4. A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in case of a DELETE command.
5. TRUNCATE is faster than DELETE. TRUNCATE is faster than DELETE due to the way TRUNCATE “removes” rows. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast.
6. TRUNCATE resets the Identity counter if there is any identity column present in the table where DELETE does not reset the identity counter.
7. You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.
8. DELETE and TRUNCATE operations are both logged. DELETE is a logged operation on a per row basis and TRUNCATE command logs the deallocation of the data pages in which the data exists.
SQL – Difference between TRUNCATE & DROP
DROP TABLE – deletes the table from the database. TRUNCATE TABLE – empties it, but leaves the structure for future data.
SQL – Difference between HAVING CLAUSE & WHERE CLAUSE
1. HAVING specifies a search condition for a group or an aggregate function used in SELECT statement. The WHERE clause specifies the criteria which individual records must meet to be selected by a query. It can be used without the GROUP BY clause. The HAVING clause cannot be used without the GROUP BY clause.
2. The WHERE clause selects rows before grouping. The HAVING clause selects rows after grouping.
3. The WHERE clause cannot contain aggregate functions. The HAVING clause can contain aggregate functions.
SQL Difference between CAST / CONVERT
CAST and CONVERT usually do the same job, but CAST is a part of the SQL-92 specification and CONVERT is not. If you want your SQL to be portable between databases, use CAST. The only difference between the two is that CAST does not accept the date conversion parameter that COVERT can use to format dates as strings. [Transact-SQL user-defined functions By Andrew Novick]
The CAST() and CONVERT() functions are used to explicitly convert the information in one data type to another specified data type. there is just one small difference between these tow functions: Convert() allows you to specify the format of the result, whereas Cast() does not.
Cast (expression AS data_type)
Convert (data_type[(length)], expression [, style])
In this case, expression is any value or expression that you want to convert, and data_type is the new data type.
CAST / CONVERT Example
Each example retrieves the name of the product for those products that have a 3 in the first digit of their list price and converts their ListPrice to int.
— Use CAST
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
WHERE CAST(ListPrice AS int) LIKE ‘3%’;
— Use CONVERT
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
WHERE CONVERT(int, ListPrice) LIKE ‘3%’;
SQL Difference between in / exists
Well, the two are processed very very differently.
Select * from T1 where x in ( select y from T2 )
is typically processed as:
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
The subquery is evaluated, distinct’ed, indexed (or hashed or sorted) and then joined to the original table — typically.
As opposed to
select * from t1 where exists ( select null from t2 where y = x )
That is processed more like:
for x in ( select * from t1 )
if ( exists ( select null from t2 where y = x.x )
OUTPUT THE RECORD
It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).
So, when is where exists appropriate and in appropriate?
Lets say the result of the subquery ( select y from T2 ) is “huge” and takes a long time.Better use EXIST
But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very very fast (nice index on t2(y)). Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery we need to distinct on.
Lets say the result of the subquery is small — then IN is typicaly more appropriate.
If both the subquery and the outer table are huge — either might work as well as the other — depends on the indexes and other factors.
Difference between Nested & Correlated Subqueries
There are two main types of subqueries – nested and correlated. Subqueries are nested, when the subquery is executed first,and its results are inserted into Where clause of the main query. Correlated subqueries are the opposite case, where the main query is executed first and the subquery is executed for every row returned by the main query.[Via: Sql By Leon]
A subquery is nested when you are having a subquery in the where or having clause of another subquery.
Get the result of all the students who are enrolled in the same course as the student with ROLLNO 12.
where rollno in (select rollno
where courseid = (select courseid
where rollno = 12));
The innermost subquery will be executed first and then based on its result the next subquery will be executed and based on that result the outer query will be executed. The levels to which you can do the nesting is implementation-dependent.
A Correlated Subquery is one that is executed after the outer query is executed. So correlated subqueries take an approach opposite to that of normal subqueries. The correlated subquery execution is as follows:
-The outer query receives a row.
-For each candidate row of the outer query, the subquery (the correlated subquery) is executed once.
-The results of the correlated subquery are used to determine whether the candidate row should be part of the result set.
-The process is repeated for all rows.
Correlated Subqueries differ from the normal subqueries in that the nested SELECT statement referes back to the table in the first SELECT statement.
To find out the names of all the students who appeared in more than three papers of their opted course, the SQL will be
from student A
Where 3 < (select count (*)
from result b
where b.rollno = a.rollno);
In other words, a correlated subquery is one whose value depends upon some variable that receives its value in some outer query. A non-correlated subquery as said before is evaluted in a bottom-to-up manner, i.e. the inner most query is evaluated first. But a correlated subquery is resolved in a top-to-bottom fashion. The top most query is analyzed and based on that result the next query is initiated. Such a subquery has to be evaluated repeatedly, once for each value of the variable in question, instead of once and for all.
Correlated subqueries improve the SQL performance when:
Only a few rows are retrieved through the outer query and especially when outer query’s WHERE clause, which limits the number of rows retrieved, can use an index.
The correlated inner queries are performed through an index scan. This is very important if the table or tables against which the inner query is performed is large and the index scan has to retrieve only a small percentage of its rows.
In other cases views and joins are likely to be more efficient. But the only sure way to say which method is efficient is to analyze the execution plan of the query and the resources used by it. [via:Introduction to Database Management Systems By Isrd Group]
Difference between Views & Materialized views
Materialized views are disk based and update periodically base upon the query definition.
Views are virtual only and run the query definition each time they are accessed.
Views evaluate the data in the tables underlying the view definition at the time the view is queried. It is a logical view of your tables, with no data stored anywhere else. The upside of a view is that it will always return the latest data to you. The downside of a view is that its performance depends on how good a select statement the view is based on. If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.
Materialized views are similar to regular views, in that they are a logical view of your data (based on a select statement), however, the underlying query resultset has been saved to a table. The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed. In addition, because all the joins have been resolved at materialized view refresh time, you pay the price of the join once (or as often as you refresh your materialized view), rather than each time you select from the materialized view. In addition, with query rewrite enabled, Oracle can optimize a query that selects from the source of your materialized view in such a way that it instead reads from your materialized view. In situations where you create materialized views as forms of aggregate tables, or as copies of frequently executed queries, this can greatly speed up the response time of your end user application. The downside though is that the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed.
Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables. Materialized views can be incrementally updated by combining them with materialized view logs, which act as change data capture sources on the underlying tables.
Materialized views are most often used in data warehousing / business intelligence applications where querying large fact tables with thousands of millions of rows would result in query response times that resulted in an unusable application.
Difference between Clustered indexes & Non-Clustered indexes
Clustered indexes & Non-Clustered indexes – All You Need to Know
Clustered indexes sort and store the data rows in the table based on their key values. There can only be one clustered index per table, because the data rows themselves can only be sorted in one order.
In SQL Server, indexes are organized as B-trees. Each page in an index B-tree is called an index node. The top node of the B-tree is called the root node. The bottom level of nodes in the index is called the leaf nodes. Any index levels between the root and the leaf nodes are collectively known as intermediate levels. In a clustered index, the leaf nodes contain the data pages of the underlying table. The root and intermediate level nodes contain index pages holding index rows. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree, or a data row in the leaf level of the index. The pages in each level of the index are linked in a doubly-linked list. [Source]
Nonclustered indexes have the same B-tree structure as clustered indexes, except for the following significant differences:
The data rows of the underlying table are not sorted and stored in order based on their nonclustered keys.
The leaf layer of a nonclustered index is made up of index pages instead of data pages.
Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. [Source]
Difference between Clustered and Non clustered index
The concept behind indexes is to change the order of the data (clustered index) or to add metadata (non-clustered index) for improving the performance of queries.
1. Physically stored in order (ascending or descending)
2. Only one per table
3. When a primary key is created a clustered index is automatically created as well.
4. If the table is under heavy data modifications or the primary key is used for searches, a clustered index on the primary key is recommended.
5. Columns with values that will not change at all or very seldom, are the best choices.
6. For use on columns that are frequently searched for ranges of data
7. For use on columns with low selectivity
1. Up to 249 nonclustered indexes are possible for each table or indexed view.
2. The clustered index keys are used for searching therefore clustered index keys should be chosen with a minimal length.
3. Covered queries (all the columns used for joining, sorting or filtering are indexed) should be non-clustered.
4. Foreign keys should be non-clustered.
5. If the table is under heavy data retrieval from fields other than the primary key, one clustered index and/or one or more non-clustered indexes should be created for the column(s) used to retrieve the data.
6. For use on columns that are searched for single values
7. For use on columns with high selectivity [Source]
What are the differences(pros/cons) between clustered and non-clustered indexes
Nonclustered index is preferred over a clustered index in the following situations:
When the index key size is large.
To avoid the overhead cost associated with a clustered index since rebuilding the clustered index rebuilds all the nonclustered indexes of the table.
To resolve blocking by having a database reader work on the pages of a nonclustered index, while a database writer modifies other columns (not included in the nonclustered index) in the data page. In this case, the writer working on the data page won’t block a reader that can get all the required column values from the nonclustered index without hitting the base table.
When all the columns (from a table) referred to by a query can be safely accommodated in the nonclustered index itself.
The data-retrieval performance when using a nonclustered index is generally poorer than that when using a clusterd index, because of the cost associated in jumping from the nonclustered index rows to the data rows in the base table. In cases where the jump to the data rows is not required, the performance of a nonclustered index should be just as good as – or even better than-a clustered index. This is possible if the nonclustered index key includes all the columns required from the table. [Source: SQL Server 2008 Query Performance Tuning Distilled By Grant Fritchey, Sajal Dam]
Clustered indexes are not a good choice for the following attributes:
Columns that undergo frequent changes: This causes in the whole row to move, because the Database Engine must keep the data values of a row in physical order. This is an important consideration in high-volume transaction processing systems in which data is typically volatile.
Wide keys: Wide keys are a composite of several columns or several large-size columns. The key values from the clustered index are used by all nonclustered indexes as lookup keys. Any nonclustered indexes defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key and also the key columns defined for that nonclustered index.
Difference Between Unique & Primary Key
You can have more than one UNIQUE constraint per table, but only one Primary key.
Some DBMS might automatically create an index (data structure to speed search) in response to PRIMARY KEY, but not UNIQUE.
SQL does not allow nulls in Primary key, but allows them in Unique columns (which may have two or more nulls, but not repeated non-null values).
Difference between Local & Global Temporary Tables
Difference between EXECUTE & EXECUTE()
The EXECUTE, or EXEC statement executes a stored procedure by following the command with the name of the stored procedure and any proper parameter arguments:
EXEC sp_Empfind @City=’DE’
The EXECUTE(character_string) function executes a character string containing a Transact-SQL command.
It can be written like EXEC(character_string) also. The EXECUTE(character_string) function is not compiled until run time and the generated plan is not cached and reused. The following example executes a dynamic string that concatenates literal strings with variable values:
DECLARE @mycol varchar(30)
DECLARE @mytable varchar(30)
SELECT @mycol = ‘EMPID’
SELECT @mytable = ‘Employee’
EXEC(‘SELECT ‘ + @mycol + ‘ FROM ‘ + @mytable)
Difference between Functions and Stored Procedures
Stored procedures have been available in SQL Server for much longer than user-defined functions. Both have similarities as well as differences. They save time and effort by providing re-usability. Both stored procedures and user defined functions can accept a maximum of 2100 parameters. Lets have a look at few important differences:
Functions Stored procedures
Functions must always return a value. Stored procedures do not have this requirement.
Functions cannot alter data or objects in a server. Stored procedures can alter data and objects in database and server.
You can embed functions within a SELECT statement. Stored procedures cannot be embedded within a SELECT statement.
Both functions and stored procedures can accept parameters. Functions can accept input parameters but can return only a single return value. Stored procedures can also accept OUTPUT parameters.
TEMP tables can’t be used in functions. Both TEMP tables and Table variables can be used in stored procedures.