Difference between * & *

[ Where & Having ]

1 - Where clause can be used with select, update and delete statement but having can be used only with select statement.

2 - Having can be used only with Group By clause but where clause is applied to each row before they are part of Group By function in query.

3 - Aggregate functions can be used only with having clause not with where clause.

[ Truncate & Delete ]

1 - Delete is DML command and Truncate is a DDL command.

2 - Where clause can be used with the Delete command but can not be used with Truncate command.

3 - Identity is reset in case of Truncate but does not reset in the case of Delete.

4 - Delete command maintain the log in row by row manner but Truncate maintain the only one log for the datapage.

5 - Truncate is faster than Delete because of delete maintain the log in row basis.

6 - Truncate will not return the number of row deleted but Delete return the number of row deleted.

[Note : Both Delete & Truncate can be rollbacked.]

[ SP & Function(UDF) ]

1 - UDF is just like the parameter view and can be used any where as views but SP can not be used any where.

2 - You can not implement exception handlind in UDF but in SP you can implement.

3 - You can not be used transection in UDF but you can be used transection in SP.

4 - You can not use SP within the UDF but you can use the UDF within SP.

5 - You can use only select command in UDF but in SP you can also use the insert, update, delete command.

6 - Function always return the value but in SP is not necessary.

[ Normalization & De-Normalization ]

Normalization concept say that you should remove the redundancy of data
to better organize the data in database that is take less space in storage
But De-Normalization concept say that you should maintain the redundent data for faster search means minimum relation between data because relationship slow your query performance.

[Note : Normalization is used in OLTP system(RDBMS) and De-Normalization is uesd in OLAP system (DWH). OLTP use the Normal Form from 1 > 2 > 3 but OLAP use the Normal Form from 3 > 2 > 1].


[ Clustered & Non-Clustered Index ]

Index are created on the table for faster searching the data when you have large amount of data in your table. And Data is stored in B-Tree (Balanced Tree) formate

1 - In case clustred index the data is physically sorted and in case of non-clustered the data is logically sorted.

2 - In case clustred index the leaf node of index has the actual data but in case of non-clustered the leaf node has the index of data.

3 - Clustered index is only one in a table but many non-clustered may be in a table.(Reasone is : the leaf node of clustered index is data page which is physically sorted and data page can only one so a table can have only one clusterd index and many non-clustered index because it has the reference of data and reference may be any number of count.)

[ varchar & nvarchar ]

1 - nvarchar datatype is used to store the unicode data (multilanguage data : japanies, chainees etc) and varchar is used to store non-unicode data.

2 - varchar use 1 byte per char and nvarchar use 2 byte per char.(nvarchar consume double space than varchar)

[ charindex & patindex ]

Both are used to find the starting pasition og the given string in expression.

1 - Wildcard charectors can not be used in charindex but you can use wildcard charectors in patindex.

2 - % sign is needed on the front & back of the pattern in case of patindex.

3 - You can not define any pattern in case of charindex but you can define the pattern in patindex.

[ Cast & Convert ]

The goal of both are same but you should use the Cast() because this ANSI-92 syntax and Convert is used when you need a specific style of your data.
So moslty Convert() is used in case of DataTime convertion where you need
a specific style of your DataTime.

[ Replce & Stuff ]

Replace function is used to replace the all occurance a string into expression by give new string.

Replace(Expression,StringTobeReplaced,NewStringToReplace)

EX : select Replace('MS SQL Server','S','M')
EX : select Replace('MS SQL Server','SQL','NewSQL')

If you want to replace the first occurance of a string, then Replace will not work, since it always replaces all the occurrences of the string. But Stuff would, since it only replaces the string it finds at the starting location we tell it for the number of chars we want it to replace.

stuff(Expression,StartLocation,TillLocation,NewString)

EX : select stuff('Hi Manish',4, 6,'Mukesh')


[ Inline Table Valued Function & Multi-Statement Table Valued Function ]

Both are User defined function and return a table.

1 - In Inline table valued function, we need not to define the return table defination but in Multivalued table valued function, you need to defined the return table defination.

2 - In Inline table valued function, You need not to write the Begin and End Statement but in Multivalued table valued function, you need to write.

3 - In Inline table valued function, you can write only one select statement nothing else but in Multivalued table valued function, you can write any business logic and any number of select statement.

[ Temp Table & Table Varible ]

1 - Temp table is initially is stored in TempDB but Table variable is first stored in memory and after a specifc size it is also stored in TempDB.

2 - Transection logs are not recorded for table variable but are mentained for Temp table.

3 - Any procedure with Temp table can not be precomplied while an execution plan of procedure with table variable can be complied in advanced.

4 - Table variable exist only in the same scope as variable.

5 - Table variable can not have the non-clustered index but Temp table can have.

6 - You can not create the constraint in the table varible while you can create in Temp table.

7 - You can not create the default value on table variable column while you can create on Temp table column.

[ Table Scan & Index Scan & Index Seek ]

When you pass a SQL query to SQL Server then an execution plan is created for the query and data is search by either Table Scan or Index Scan or Index Seek.

Table Scan is performed when your table does not have any index or Clustered index.

Index Scan is performed when your table have the indexes but index is not used to search the data.

Index Seek is performed when data is searched by the indexes.